Mysql学习(二)——进阶

kiwi.

已于 2022-10-18 22:07:29 修改

阅读量93

点赞数

分类专栏： Mysql数据库文章标签： mysql 学习数据库

于 2022-10-02 16:47:28 首次发布

本文链接：https://blog.csdn.net/JIE252520/article/details/127043872

版权

Mysql数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

文章目录

表约束
数据库的三范式
事务和视图
Mysql查询
Mysql函数
SQL优化

表约束

非空约束
数据库字段的某个值是否可以为空，NULL 字段值可以为空，NOT NULL 字段值不能为空。
当字段设置为非空时，添加数据就必须要插入值，否则就会报错。

 alter table student modify name varchar(20) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| sex   | varchar(5)  | YES  |     | NULL    |       |
| city  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

唯一约束
字段添加唯一约束之后，该字段的值不重复，也就是该字段的值在该表中唯一unique key。

alter table student add unique key(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  | UNI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| sex   | varchar(5)  | YES  |     | NULL    |       |
| city  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

主键约束
主键保证记录的唯一性，主键自动为 NOT NULL, 每张数据表只能存在一个主键。

alter table student add primary key(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

自增长约束
AUTO_INCREMENT 自动编号，且必须与主键组合使用，默认情况下，起始值为1，每次的增量为1。


insert into student(name,age,sex,city) values('xiaoming',20,'man','shanghai');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+----------+------+-------+----------+
| id | name     | age  | sex   | city     |
+----+----------+------+-------+----------+
|  1 | Jie      |   18 | women | Hefei    |
|  2 | xiaobai  |   20 | NULL  | NULL     |
|  3 | zhangsan |   25 | man   | changsha |
|  4 | lisi     |   30 | man   | shanghai |
|  5 | xiaoming |   20 | man   | shanghai |

外键约束
案例分析，有如下五张表，包括学院表、学生表、学生详情表、课程表和选课表，其中选课表是为了将课程表与学生表对应起来，同时为了保证学生不重复选课，引入了联合外键，学生详情表中的学生id具有唯一性。

按照要求建立表格，包括department，student，stu_detail，stu_course表。

mysql> create table department(
    -> d_id int primary key auto_increment,
    -> d_name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> desc department;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| d_id   | int         | NO   | PRI | NULL    | auto_increment |
| d_name | varchar(20) | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

建立学生表，并与学院表进行主外键关联。

mysql> create table student( s_id int primary key auto_increment, s_name varcha  r(20) not null,
    -> dept_id int,
    -> constraint d_s foreign key(dept_id) references department(d_id)
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> desc student;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| s_id    | int         | NO   | PRI | NULL    | auto_increment |
| s_name  | varchar(20) | NO   |     | NULL    |                |
| dept_id | int         | YES  | MUL | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

将学院表和学生表插入数据。

mysql> insert into department(d_name) values('音乐'),('英语'),('计算机'),('挖掘  机');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from department;
+------+-----------+
| d_id | d_name    |
+------+-----------+
|    1 | 音乐      |
|    2 | 英语      |
|    3 | 计算机    |
|    4 | 挖掘机    |
+------+-----------+
4 rows in set (0.00 sec)
mysql> insert into student(s_name,dept_id) value('mr',3);
Query OK, 1 row affected (0.02 sec)

mysql> insert into student(s_name,dept_id) values('wanzi',1),('xiaobai',2),('bd  ',4);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+------+---------+---------+
| s_id | s_name  | dept_id |
+------+---------+---------+
|    1 | mr      |       3 |
|    2 | wanzi   |       1 |
|    3 | xiaobai |       2 |
|    4 | bd      |       4 |
+------+---------+---------+
4 rows in set (0.00 sec)

构建学生详情表并插入数据。

mysql> create table stu_detail( sd_id int  primary key auto_increment, s_age in
t default 18, s_sex varchar(10), s_id int unique key, constraint s_sd foreign k  ey(s_id) references student(s_id) on delete cascade);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into stu_detail(s_sex,s_id) values('man',1),('woman',2),('man',3)  ;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from stu_detail;
+-------+-------+-------+------+
| sd_id | s_age | s_sex | s_id |
+-------+-------+-------+------+
|     1 |    18 | man   |    1 |
|     2 |    18 | woman |    2 |
|     3 |    18 | man   |    3 |
+-------+-------+-------+------+
3 rows in set (0.00 sec)

构建学生选课表，实现联合主键，保证选课的唯一性。

mysql> create table stu_course( s_id int, c_id int, primary key(s_id,c_id), con  straint s_sc foreign key(s_id) references student(s_id), constraint c_sc foreig  n key(c_id) references course(c_id) );
Query OK, 0 rows affected (0.19 sec)

mysql> desc stu_course;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| s_id  | int  | NO   | PRI | NULL    |       |
| c_id  | int  | NO   | PRI | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into course(c_name) values('python'),('web'),('html'),('java');
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0
insert into stu_course(s_id,c_id) values(1,1),(1,2),(1,3),(2,2),(2,3),(4  ,1),(4,4);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

数据库的三范式

第一范式（1NF）：符合1NF的关系中的每个属性都不可再分。1NF是所有关系型数据库的最基本要求。
简单来说，就是不能出现多个表头。
第二范式（2NF）：2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖。
简单来说，只能描述一个对象（主键），其它列名（副键）与对象之间相互完全依赖。
第三范式（3NF）：3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖
简单的说，所有的非主属性只在整个数据库里面出现一次，副键与副键之间，不能存在依赖关系。
范式的作用是尽可能避免数据的冗余和插入/删除/更新的异常。

事务和视图

事务要不不执行，要么全部执行，案例分析，比如数据库数据全部删除再恢复，事务回滚。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mr;
+------+-----------+------+--------+
| id   | name      | age  | idcard |
+------+-----------+------+--------+
| 1000 | zhangsan  |   18 | 22222  |
| 1001 | lisi      |   18 | 44444  |
| 1002 | wangwu    |   18 | 88888  |
| 1003 | xiaozhang |   18 | 6666   |
+------+-----------+------+--------+
4 rows in set (0.00 sec)

mysql> insert into mr(name,idcard) value('xiaobai','00000');
Query OK, 1 row affected (0.00 sec)

mysql> delete from mr;
Query OK, 5 rows affected (0.01 sec)

mysql> select * from mr;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from mr;
+------+-----------+------+--------+
| id   | name      | age  | idcard |
+------+-----------+------+--------+
| 1000 | zhangsan  |   18 | 22222  |
| 1001 | lisi      |   18 | 44444  |
| 1002 | wangwu    |   18 | 88888  |
| 1003 | xiaozhang |   18 | 6666   |
+------+-----------+------+--------+
4 rows in set (0.00 sec)

视图的作用：三范式让表查询变得复杂，对于常用的数据查询，反复写复杂的查询语句十分不方便，因此可以创建一个虚拟的表（不存数据），这个虚拟表的数据来源于数据库中存在的其他表，虚拟表的数据来源就在定义时给定。

##内连接
mysql> select * from student inner join stu_course on student.s_id=stu_course.s  _id;
+------+--------+---------+------+------+
| s_id | s_name | dept_id | s_id | c_id |
+------+--------+---------+------+------+
|    1 | mr     |       3 |    1 |    1 |
|    1 | mr     |       3 |    1 |    2 |
|    1 | mr     |       3 |    1 |    3 |
|    2 | wanzi  |       1 |    2 |    2 |
|    2 | wanzi  |       1 |    2 |    3 |
|    4 | bd     |    NULL |    4 |    1 |
|    4 | bd     |    NULL |    4 |    4 |
+------+--------+---------+------+------+
7 rows in set (0.00 sec)

mysql> select * from student inner join stu_course on student.s_id=stu_course.s  _id inner join course on stu_course.c_id=course.c_id;
+------+--------+---------+------+------+------+--------+
| s_id | s_name | dept_id | s_id | c_id | c_id | c_name |
+------+--------+---------+------+------+------+--------+
|    1 | mr     |       3 |    1 |    1 |    1 | python |
|    1 | mr     |       3 |    1 |    2 |    2 | web    |
|    1 | mr     |       3 |    1 |    3 |    3 | html   |
|    2 | wanzi  |       1 |    2 |    2 |    2 | web    |
|    2 | wanzi  |       1 |    2 |    3 |    3 | html   |
|    4 | bd     |    NULL |    4 |    1 |    1 | python |
|    4 | bd     |    NULL |    4 |    4 |    4 | java   |
+------+--------+---------+------+------+------+--------+
7 rows in set (0.00 sec)

mysql> select s_name,c_name from student inner join stu_course on student.s_id=  stu_course.s_id inner join course on stu_course.c_id=course.c_id;
+--------+--------+
| s_name | c_name |
+--------+--------+
| mr     | python |
| mr     | web    |
| mr     | html   |
| wanzi  | web    |
| wanzi  | html   |
| bd     | python |
| bd     | java   |
+--------+--------+
7 rows in set (0.01 sec)

mysql> create view stu_c as select s_name,c_name from student inner join stu_co  urse on student.s_id=stu_course.s_id inner join course on stu_course.c_id=cours  e.c_id;
Query OK, 0 rows affected (0.04 sec)

Mysql查询

1.带条件查询where。
2. 取别名 as。
3. 多表查询
（1）内连接
在这里插入图片描述
（2）外连接
左连接：A LEFT JOIN B 会以左边的表为主，展式左边表的所有数据，展式右边表中符合ON子句中条件的数据，没有为空。
右连接：右连接和左连接类似，只是作用相反。
全连接：UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。
4. 子表查询
5. 分组查询
分组是个常见的操作，常用于分组统计，使用GROUP BY后，会按照GROUP BY后面的字段进行分组，且必须是明确的字段，不能是*，因此SELECT后面也不能是*.其次可以使用 HAVING 可以对分组之后的结果进行筛选，注意：HAVING 后的字段必须是SELECT后出现过的。

mysql> select * from stu_c;
+--------+--------+
| s_name | c_name |
+--------+--------+
| mr     | python |
| mr     | web    |
| mr     | html   |
| wanzi  | web    |
| wanzi  | html   |
| bd     | python |
| bd     | java   |
+--------+--------+
7 rows in set (0.01 sec)

mysql> select * from mr where age>18;
Empty set (0.00 sec)
## %表示多个字符匹配
mysql> select * from mr where name like 't%';
Empty set (0.00 sec)

mysql> select * from mr where name like 'x%';
+------+-----------+------+--------+
| id   | name      | age  | idcard |
+------+-----------+------+--------+
| 1003 | xiaozhang |   18 | 6666   |
+------+-----------+------+--------+
1 row in set (0.00 sec)
##单字符匹配
mysql> select * from mr where name like 'x_';
Empty set (0.00 sec)

mysql> select mr.name from mr;
+-----------+
| name      |
+-----------+
| zhangsan  |
| lisi      |
| wangwu    |
| xiaozhang |
+-----------+
4 rows in set (0.01 sec)

mysql> select dt.d_name from department as dt;
+-----------+
| d_name    |
+-----------+
| 音乐      |
| 英语      |
| 计算机    |
+-----------+
3 rows in set (0.00 sec)

mysql> update student set dept_id=2 where s_id=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+------+---------+---------+
| s_id | s_name  | dept_id |
+------+---------+---------+
|    1 | mr      |       3 |
|    2 | wanzi   |       1 |
|    3 | xiaobai |       2 |
|    4 | bd      |       2 |
+------+---------+---------+
4 rows in set (0.01 sec)

mysql> select * from student inner join department;
+------+---------+---------+------+-----------+
| s_id | s_name  | dept_id | d_id | d_name    |
+------+---------+---------+------+-----------+
|    1 | mr      |       3 |    3 | 计算机    |
|    1 | mr      |       3 |    2 | 英语      |
|    1 | mr      |       3 |    1 | 音乐      |
|    2 | wanzi   |       1 |    3 | 计算机    |
|    2 | wanzi   |       1 |    2 | 英语      |
|    2 | wanzi   |       1 |    1 | 音乐      |
|    3 | xiaobai |       2 |    3 | 计算机    |
|    3 | xiaobai |       2 |    2 | 英语      |
|    3 | xiaobai |       2 |    1 | 音乐      |
|    4 | bd      |       2 |    3 | 计算机    |
|    4 | bd      |       2 |    2 | 英语      |
|    4 | bd      |       2 |    1 | 音乐      |
+------+---------+---------+------+-----------+
12 rows in set (0.00 sec)

mysql> select * from student,department;
+------+---------+---------+------+-----------+
| s_id | s_name  | dept_id | d_id | d_name    |
+------+---------+---------+------+-----------+
|    1 | mr      |       3 |    3 | 计算机    |
|    1 | mr      |       3 |    2 | 英语      |
|    1 | mr      |       3 |    1 | 音乐      |
|    2 | wanzi   |       1 |    3 | 计算机    |
|    2 | wanzi   |       1 |    2 | 英语      |
|    2 | wanzi   |       1 |    1 | 音乐      |
|    3 | xiaobai |       2 |    3 | 计算机    |
|    3 | xiaobai |       2 |    2 | 英语      |
|    3 | xiaobai |       2 |    1 | 音乐      |
|    4 | bd      |       2 |    3 | 计算机    |
|    4 | bd      |       2 |    2 | 英语      |
|    4 | bd      |       2 |    1 | 音乐      |
+------+---------+---------+------+-----------+
12 rows in set (0.00 sec)


mysql> select * from student inner join department on department.d_id=student.dept_id;
+------+---------+---------+------+-----------+
| s_id | s_name  | dept_id | d_id | d_name    |
+------+---------+---------+------+-----------+
|    2 | wanzi   |       1 |    1 | 音乐      |
|    3 | xiaobai |       2 |    2 | 英语      |
|    4 | bd      |       2 |    2 | 英语      |
|    1 | mr      |       3 |    3 | 计算机    |
+------+---------+---------+------+-----------+
4 rows in set (0.00 sec)

mysql> select * from student as s inner join department as dt on dt.d_id=s.dept_id;
+------+---------+---------+------+-----------+
| s_id | s_name  | dept_id | d_id | d_name    |
+------+---------+---------+------+-----------+
|    2 | wanzi   |       1 |    1 | 音乐      |
|    3 | xiaobai |       2 |    2 | 英语      |
|    4 | bd      |       2 |    2 | 英语      |
|    1 | mr      |       3 |    3 | 计算机    |
+------+---------+---------+------+-----------+
4 rows in set (0.00 sec)

mysql> select * from student as s,department as dt where s.dept_id = dt.d_id;
+------+---------+---------+------+-----------+
| s_id | s_name  | dept_id | d_id | d_name    |
+------+---------+---------+------+-----------+
|    2 | wanzi   |       1 |    1 | 音乐      |
|    3 | xiaobai |       2 |    2 | 英语      |
|    4 | bd      |       2 |    2 | 英语      |
|    1 | mr      |       3 |    3 | 计算机    |
+------+---------+---------+------+-----------+
4 rows in set (0.00 sec)

mysql> insert into student(s_name) value('test');
Query OK, 1 row affected (0.05 sec)

mysql> select * from student as s,department as dt where s.dept_id = dt.d_id;
+------+---------+---------+------+-----------+
| s_id | s_name  | dept_id | d_id | d_name    |
+------+---------+---------+------+-----------+
|    2 | wanzi   |       1 |    1 | 音乐      |
|    3 | xiaobai |       2 |    2 | 英语      |
|    4 | bd      |       2 |    2 | 英语      |
|    1 | mr      |       3 |    3 | 计算机    |
+------+---------+---------+------+-----------+
4 rows in set (0.00 sec)
##左连接
mysql> select * from student as s left join department as dt on dt.d_id=s.dept_id;
+------+---------+---------+------+-----------+
| s_id | s_name  | dept_id | d_id | d_name    |
+------+---------+---------+------+-----------+
|    1 | mr      |       3 |    3 | 计算机    |
|    2 | wanzi   |       1 |    1 | 音乐      |
|    3 | xiaobai |       2 |    2 | 英语      |
|    4 | bd      |       2 |    2 | 英语      |
|    5 | test    |    NULL | NULL | NULL      |
+------+---------+---------+------+-----------+
5 rows in set (0.02 sec)

mysql> insert into department(d_name) value('测试');
Query OK, 1 row affected (0.02 sec)

mysql> select * from student as s left join department as dt on dt.d_id=s.dept_id;
+------+---------+---------+------+-----------+
| s_id | s_name  | dept_id | d_id | d_name    |
+------+---------+---------+------+-----------+
|    1 | mr      |       3 |    3 | 计算机    |
|    2 | wanzi   |       1 |    1 | 音乐      |
|    3 | xiaobai |       2 |    2 | 英语      |
|    4 | bd      |       2 |    2 | 英语      |
|    5 | test    |    NULL | NULL | NULL      |
+------+---------+---------+------+-----------+
5 rows in set (0.01 sec)
##右连接
mysql> select * from student as s right join department as dt on dt.d_id=s.dept_id;
+------+---------+---------+------+-----------+
| s_id | s_name  | dept_id | d_id | d_name    |
+------+---------+---------+------+-----------+
|    2 | wanzi   |       1 |    1 | 音乐      |
|    3 | xiaobai |       2 |    2 | 英语      |
|    4 | bd      |       2 |    2 | 英语      |
|    1 | mr      |       3 |    3 | 计算机    |
| NULL | NULL    |    NULL |    5 | 测试      |
+------+---------+---------+------+-----------+
5 rows in set (0.01 sec)

mysql> select * from student as s left join stu_course as sc on s.s_id=sc.s_id;
+------+---------+---------+------+------+
| s_id | s_name  | dept_id | s_id | c_id |
+------+---------+---------+------+------+
|    1 | mr      |       3 |    1 |    1 |
|    1 | mr      |       3 |    1 |    2 |
|    1 | mr      |       3 |    1 |    3 |
|    2 | wanzi   |       1 |    2 |    2 |
|    2 | wanzi   |       1 |    2 |    3 |
|    3 | xiaobai |       2 | NULL | NULL |
|    4 | bd      |       2 |    4 |    1 |
|    4 | bd      |       2 |    4 |    4 |
|    5 | test    |    NULL | NULL | NULL |
+------+---------+---------+------+------+
9 rows in set (0.00 sec)

mysql> select * from student as s left join stu_course as sc on s.s_id=sc.s_id join course on sc.c_id=course.c_id;
+------+--------+---------+------+------+------+--------+
| s_id | s_name | dept_id | s_id | c_id | c_id | c_name |
+------+--------+---------+------+------+------+--------+
|    1 | mr     |       3 |    1 |    1 |    1 | python |
|    4 | bd     |       2 |    4 |    1 |    1 | python |
|    1 | mr     |       3 |    1 |    2 |    2 | web    |
|    2 | wanzi  |       1 |    2 |    2 |    2 | web    |
|    1 | mr     |       3 |    1 |    3 |    3 | html   |
|    2 | wanzi  |       1 |    2 |    3 |    3 | html   |
|    4 | bd     |       2 |    4 |    4 |    4 | java   |
+------+--------+---------+------+------+------+--------+
7 rows in set (0.00 sec)

mysql> select s_name,c_name from student as s left join stu_course as sc on s.s_i
d=sc.s_id join course on sc.c_id=course.c_id;
+--------+--------+
| s_name | c_name |
+--------+--------+
| mr     | python |
| bd     | python |
| mr     | web    |
| wanzi  | web    |
| mr     | html   |
| wanzi  | html   |
| bd     | java   |
+--------+--------+
7 rows in set (0.01 sec)

mysql> select * from course c inner join stu_course sc on c.c_id=sc.c_id;
+------+--------+------+------+
| c_id | c_name | s_id | c_id |
+------+--------+------+------+
|    1 | python |    1 |    1 |
|    1 | python |    4 |    1 |
|    2 | web    |    1 |    2 |
|    2 | web    |    2 |    2 |
|    3 | html   |    1 |    3 |
|    3 | html   |    2 |    3 |
|    4 | java   |    4 |    4 |
+------+--------+------+------+
7 rows in set (0.00 sec)
mysql> select * from student inner join(select c_name,s_id from course c inner jo
in stu_course sc on c.c_id=sc.c_id) e on student.s_id = e.s_id;
+------+--------+---------+--------+------+
| s_id | s_name | dept_id | c_name | s_id |
+------+--------+---------+--------+------+
|    1 | mr     |       3 | python |    1 |
|    4 | bd     |       2 | python |    4 |
|    1 | mr     |       3 | web    |    1 |
|    2 | wanzi  |       1 | web    |    2 |
|    1 | mr     |       3 | html   |    1 |
|    2 | wanzi  |       1 | html   |    2 |
|    4 | bd     |       2 | java   |    4 |
+------+--------+---------+--------+------+
7 rows in set (0.02 sec)
##子表查询，先查询出一个联合表，再查询另外的表
mysql> select * from student where dept_id = (select d_id from department where d_id);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select * from student where dept_id = (select d_id from department where d_name);
Empty set, 4 warnings (0.01 sec)

mysql> select * from student where dept_id = (select d_id from department where d_name='音乐');
+------+--------+---------+
| s_id | s_name | dept_id |
+------+--------+---------+
|    2 | wanzi  |       1 |
+------+--------+---------+
1 row in set (0.01 sec)
##排序
mysql> select * from mr order by age;
+------+-----------+------+--------+
| id   | name      | age  | idcard |
+------+-----------+------+--------+
| 1000 | zhangsan  |   18 | 22222  |
| 1001 | lisi      |   18 | 44444  |
| 1002 | wangwu    |   18 | 88888  |
| 1003 | xiaozhang |   18 | 6666   |
+------+-----------+------+--------+
4 rows in set (0.00 sec)

mysql> select * from mr order by age desc;
+------+-----------+------+--------+
| id   | name      | age  | idcard |
+------+-----------+------+--------+
| 1000 | zhangsan  |   18 | 22222  |
| 1001 | lisi      |   18 | 44444  |
| 1002 | wangwu    |   18 | 88888  |
| 1003 | xiaozhang |   18 | 6666   |
+------+-----------+------+--------+
4 rows in set (0.00 sec)

mysql> select * from mr order by age,idcard;
+------+-----------+------+--------+
| id   | name      | age  | idcard |
+------+-----------+------+--------+
| 1000 | zhangsan  |   18 | 22222  |
| 1001 | lisi      |   18 | 44444  |
| 1003 | xiaozhang |   18 | 6666   |
| 1002 | wangwu    |   18 | 88888  |
+------+-----------+------+--------+
4 rows in set (0.00 sec)
##限制行数limit
mysql> select * from mr order by age limit 3;
+------+----------+------+--------+
| id   | name     | age  | idcard |
+------+----------+------+--------+
| 1000 | zhangsan |   18 | 22222  |
| 1001 | lisi     |   18 | 44444  |
| 1002 | wangwu   |   18 | 88888  |
+------+----------+------+--------+
3 rows in set (0.01 sec)

mysql> select * from mr order by age limit 1,4;
+------+-----------+------+--------+
| id   | name      | age  | idcard |
+------+-----------+------+--------+
| 1001 | lisi      |   18 | 44444  |
| 1002 | wangwu    |   18 | 88888  |
| 1003 | xiaozhang |   18 | 6666   |
+------+-----------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from student;
+------+---------+---------+
| s_id | s_name  | dept_id |
+------+---------+---------+
|    1 | mr      |       3 |
|    2 | wanzi   |       1 |
|    3 | xiaobai |       2 |
|    4 | bd      |       2 |
|    5 | test    |    NULL |
+------+---------+---------+
5 rows in set (0.00 sec)

mysql> insert into student(s_name,dept_id) values('x1',1),('x2',1),('x3',2),('x4',3);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student;
+------+---------+---------+
| s_id | s_name  | dept_id |
+------+---------+---------+
|    1 | mr      |       3 |
|    2 | wanzi   |       1 |
|    3 | xiaobai |       2 |
|    4 | bd      |       2 |
|    5 | test    |    NULL |
|    6 | x1      |       1 |
|    7 | x2      |       1 |
|    8 | x3      |       2 |
|    9 | x4      |       3 |
+------+---------+---------+
9 rows in set (0.00 sec)
#分组查询
mysql> select d_name,count(*) from department as dt left join student as s on dt.
+-----------+----------+
| d_name    | count(*) |
+-----------+----------+
| 音乐      |        3 |
| 英语      |        3 |
| 计算机    |        2 |
| 测试      |        1 |
+-----------+----------+
4 rows in set (0.02 sec)

mysql> select d_name,count(*) from department as dt left join student as s on dt.d_id = s.dept_id group by d_id,d_name having count(*)>=2;
+-----------+----------+
| d_name    | count(*) |
+-----------+----------+
| 音乐      |        3 |
| 英语      |        3 |
| 计算机    |        2 |
+-----------+----------+
3 rows in set (0.01 sec)

Mysql函数

在这里插入图片描述

SQL优化

mysql执行顺序
1.FROM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1
2.ON: 对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。
3.JOIN：如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。
4.WHERE：对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。
5.GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.
6.CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.
7.HAVING：对虚拟表VT6应用having过滤，只有符合的记录才会被插入到虚拟表VT7中。
8.SELECT：执行select操作，选择指定的列，插入到虚拟表VT8中。
9.DISTINCT：对VT8中的记录进行去重。产生虚拟表VT9.
10.ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.
11.LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。
还有几点建议给大家：
1.尽量避免整表扫描，如SELECT *
2.建立合适的索引
3.使用合适的存储引擎
4.在JOIN中，尽量用小表LEFT JOIN 大表
5.除非十分必要，尽量不要使用ORDER BY,GROUP BY 和 DISTINCT(去重)，尽量用索引来代替