mysql常用操作：库级操作，表级操作，数据CRUD,分组操作，时间和日期，字符串函数，表的约束

本文为大家讲解的是mysql的常用操作：库级操作，表级操作，数据CRUD,分组操作，时间和日期，字符串函数，表的约束 等，感兴趣的同学参考下。 MySQL是一个开放源码的小型关联式数据库管理系统，开发者为瑞典MySQL AB公司

本文为大家讲解的是mysql的常用操作：库级操作，表级操作，数据CRUD,分组操作，时间和日期，字符串函数，表的约束 等，感兴趣的同学参考下。

MySQL是一个开放源码的小型关联式数据库管理系统，开发者为瑞典MySQL AB公司。MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

1 连接MYSQL服务器：mysql–uroot –p123456

1. 查看中文问题

show variables like 'character%';

 

2 修改mysql的字符集，退出mysql提示符界面：

mysql -uroot -p--default_character_set=gbk;

 

3  数据库的操作：创建，查看，修改，删除

   *创建：

       创建一个名称为mydb1的数据库。

                createdatabase mydb1;

       创建一个使用utf-8字符集的mydb2数据库。

                      create database mydb2character set utf8;

       创建一个使用utf-8字符集，并带校对规则的mydb3数据库。

                createdatabase mydb3 character set utf8 collate utf8_general_ci;

   *查看:

       显示所有数据库

                showdatabases;

       显示创建数据库的语句信息

                showcreate database mydb2;

   *修改:

       修改mydb1的字符集为gbk(不能修改数据库名)

                alterdatabase mydb1 character set utf8;     

   *删除:

       删除数据库mydb2

                dropdatabase mydb1;

 

4 表的操作：创建，查看，修改，删除

usemydb2;

   *创建：

       根据实体类Person创建表person

                Person{

                         intid;

                         Stringname;

                }

                createtable person(

                         idint,

                         namevarchar(20)

                );

       mysql中的数据类型：

                bit1位　但可以指定位数，如：bit<3>

                int2字节 可以指定最大位数，如：int<4>　最大为4位的整数

                float2个字节　可以指定最大的位数和最大的小数位数，如：float<5,2> 最大为一个5位的数，小数位最多2位

                double　4个字节　可以指定最大的位数和最大的小数位数，如：float<6,4> 最大为一个6位的数，小数位最多4位

                char　必须指定字符数,如char(5) 为不可变字符　即使存储的内容为'ab',也是用5个字符的空间存储这个数据

                varchar　必须指定字符数,如varchar(5) 为可变字符　如果存储的内容为'ab',占用2个字符的空间；如果为'abc',则占用3个字符的空间

                text:大文本(大字符串)

                blob：二进制大数据　如图片，音频文件，视频文件

                date:日期　如：'1921-01-02'

                datetime:日期时间　如：'1921-01-02 12:23:43'

                timeStamp:时间戳，自动赋值为当前日期时间

               

            创建一个员工表

                createtable employee(id int,name varchar(20),sex bit,birthday date,salarydouble,entry_date date,resume text);

   *查看：

       查看所有的表：

                showtables;

       查看指定表的创建语句

                showcreate table employee;

                mysql表 名称区分大小写

       显示指定表的结构：

                descemployee;

   *删除：

       删除employee表

       droptable employee;

   *修改表：

 create table worker(id int,name varchar(20),sex bit,birthday date,salarydouble,entry_date date,resume text);

       增加一个字段：altertable worker add column height double;

       修改一个字段：altertable worker modify column height float;

       删除一个字段：altertable worker drop column height;

       更改表名：renametable employee to worker;

       修改表的字符集:altertable worker character set gbk;

  

5 表数据的CRUD

   *C(create增加数据) Insert语句

       新建Employee表并表中添加一些记录

                createtable employee(

                         idint,

                         namevarchar(20),

                         sexbit,

                         birthdaydate,

                         salarydouble,

                         entry_datedate,

                         resumetext

                );

               

                insertinto employee(id,name,sex,birthday,salary,entry_date,resume) values(1,'张三',1,'1983-09-21',15000,'2012-06-24','一个大牛');

                insertinto employee(id,name,sex,birthday,salary,entry_date,resume) values(2,'李四',1,'1984-09-21',10000,'2012-07-24','一个中牛');

                insertinto employee(id,name,sex,birthday,salary,entry_date,resume) values(3,'王五',0,'1985-09-21',7000,'2012-08-24','一个小牛');

                deletefrom employee where id=1

 

                createtable employee(   id int,namevarchar(20),sex bit,birthday date,salary double,entry_date date,resume text);

  

   *U(update更新数据) Update语句

       将所有员工薪水都增加500元。

                updateemployee set salary=salary+500;

       将王五的员工薪水修改为10000元，resume改为也是一个中牛

                updateemployee set salary=10000,resume='也是一个中牛' where name='王五';

   *D(drop删除数据) Delete语句

       删除表中姓名为王五的记录。

                deletefrom employee where name='王五';

       删除表中所有记录。

                deletefrom employee; --可以有条件，但删除所有记录差了一点

       使用truncate删除表中记录。

                truncateemployee;--无条件效率高

  

   6  *R(Retrieve查找数据) Select语句 

       准备环境：

                createtable student(

                idint,

                namevarchar(20),

                chineseint,

                englishint,

                mathint

                );

 

                insertinto student(id,name,chinese,english,math) values(1,'何东',80,85,90);

                insertinto student(id,name,chinese,english,math) values(2,'权筝',90,95,95);

                insertinto student(id,name,chinese,english,math) values(3,'何南',80,96,96);

                insertinto student(id,name,chinese,english,math) values(4,'叶坦',81,97,85);

                insertinto student(id,name,chinese,english,math) values(5,'何西',85,84,90);

                insertinto student(id,name,chinese,english,math) values(6,'丁香',92,85,87);

                insertinto student(id,name,chinese,english,math) values(7,'何北',75,81,80);

                insertinto student(id,name,chinese,english,math) values(8,'唐娇',77,80,79);

                insertinto student(id,name,chinese,english,math) values(9,'任知了',95,85,85);

                insertinto student(id,name,chinese,english,math) values(10,'王越',94,85,84);

 

       查询表中所有学生的信息。

                select* from student;

       查询表中所有学生的姓名和对应的英语成绩。

                selectname,english from student;

       过滤表中重复数据。

                selectenglish from student;

                selectDISTINCT english from student;

                selectDISTINCT english,name from student;

 

                selectenglish+chinese+math from student;

                selectenglish+chinese+math as 总分 from student;

                      selectname,english+chinese+math as 总分 from student;

 

       在所有学生英语分数上加10分特长分。

                selectname,english+10 from student;

       统计每个学生的总分。

                selectenglish+chinese+math from student;

       使用别名表示学生分数

                selectname,english+chinese+math as 总分 from student;

                selectname,english+chinese+math 总分 from student;

 

       查询姓名为何东的学生成绩

                select* from student where name='何东';

       查询英语成绩大于90分的同学

                select* from student where english>90;

       查询总分大于250分的所有同学

                select* from student where english+chinese+math>250;

 

       查询英语分数在 85－95之间的同学。

                select* from student where english>=85 and english<=95;

                select* from student where english between 85 and 95;

       查询数学分数为84,90,91的同学。

                select* from student where math=84 or math=90 or math=91;

                select* from student where math in(84,90,91);

            查询所有姓何的学生成绩。

                select* from student where name like '何%';

       查询数学分>85，语文分>90的同学。

                select* from student where math>85 and chinese>90;

 

       对数学成绩排序后输出。

                select* from student order by math;

       对总分排序后输出，然后再按从高到低的顺序输出

                select* from student order by math+chinese+english desc;

       对姓何的学生成绩排序输出

                select* from student where name like '何%' order by math+chinese+english desc;

                selectname, math+chinese+english from student where name like '何%' order bymath+chinese+english desc;

 

       统计一个班级共有多少学生？

                selectcount(*) from student;

       统计数学成绩大于90的学生有多少个？

                selectcount(*) from student where math>90;

       统计总分大于250的人数有多少？

                selectcount(*) from student where math+chinese+english>250;

 

       统计一个班级数学总成绩？

                selectsum(math) from student;

       统计一个班级语文、英语、数学各科的总成绩

                selectsum(math), sum(chinese), sum(english) from student;

       统计一个班级语文、英语、数学的成绩总和

                selectsum(math+chinese+english)from student;

                selectsum(math)+sum(chinese)+sum(english) from student;

 

       求一个班级数学平均分？

                selectavg(math) from student;

       求一个班级总分平均分

                selectavg(math+chinese+english)from student;

                selectavg(math)+avg(chinese)+avg(english) from student;

 

       求班级最高分和最低分

                selectmax(math+chinese+english),min(math+chinese+english) from student;

  

7 综合性练习：为学生表，增加一个班级列，然后训练分组查询

       查出各个班的总分，最高分

                准备环境

                         给表添加一个字段：altertable student add column class_id int;

                         更新表：

                                   updatestudent set class_id=1 where id<=5;

                                   updatestudent set class_id=2 where id>5;

                selectsum(math+chinese+english),max(math+chinese+english) from student group byclass_id;

 

       查询出班级总分大于1300分的班级ID

                selectclass_id from student group by class_id havingsum(math+chinese+english)>1300;

                selectclass_id from student where sum(math+chinese+english)>1300 group by class_id;

       note:where和group区别: 在wehre子句中不能使用分组函数

      

      

 

8  时间和日期

       mysql>select year (now()), month(now()), day(now()) , date(now());

       +--------------+--------------+------------+-------------+

       |year (now()) | month(now()) | day(now()) | date(now()) |

       +--------------+--------------+------------+-------------+

       |         2014 |            9 |          7 | 2014-09-07  |

       +--------------+--------------+------------+-------------+

  

       selectdate_add(now(), INTERVAL 2 year) from dual;／／增加两年

       selectcharset('name')  employee;

       selectdate_add(now(), INTERVAL -1 day) 昨天, now() 今天, date_add(now(), INTERVAL +1 day) 明天;

 

9 字符串相关函数

   selectconcat( charset('name'), 'aaaa') 自定义 from dual;

 

 

10 表的约束　　

   *定义主键约束　primarykey:不允许为空，不允许重复

   *定义主键自动增长　auto_increment

   *定义唯一约束　unique

   *定义非空约束　notnull

   *定义外键约束　constraintordersid_FK foreign key(ordersid) references orders(id)

   *删除主键：altertable tablename drop primary key ;

 

   createtable myclass

   (

       idINT(11) primary key auto_increment,

       namevarchar(20) unique

   );

  

   createtable student(

       idINT(11) primary key auto_increment,

       namevarchar(20) unique,

       passwdvarchar(15) not null,

       classidINT(11),  #注意这个地方不要少逗号

       constraintstu_classid_FK  foreign key(classid)references myclass(id)

   );