一、MySQL单表查询
1.1、关键字的执行顺序
- from
- where
- group by
- having
- select
- distinct
- order by
- limit
依照上面所示，从上到下即为执行顺序！先是用from找到表，接着用where的约束条件来筛选数据；将筛选后的数据进行分组group by，如没有group by，默认整体作为一组；而后将分组的结果进行having过滤，然后执行select、去重、排序、限制显示条数
1.2、where约束条件
针对null不用等号用is，太简单了，不想一一演示！
1.2.1、比较运算符
MySQL支持比较运算符，如：< >= <= <> !=
1.2.2、逻辑运算符
and or not
1.2.3、like模糊匹配
1.2.4、between
where id between 3 and 8,即3到8之间
1.2.5、in not in
where salary in (2000,18000,17000)，是否是这三个的其中一个
1.2.6、定义显示格式
CONCAT() 函数用于连接字符串
SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM employee;
1.3、group by分组
1.3.1、什么是分组？
根据执行顺序可知，分组在where之后执行！分组指的是将所有数据按照某个相同字段进行归类，比如按照职位字段或者性别字段进行分组等！
1.3.2、为何用分组？
分组对一些数据的集合展示非常适合，比如说一个部门的平均薪资、每个部门的总人数、最高薪资之类的最、每、平均等关键字眼！可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数！
注意：如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义！多条记录之间的某个字段值相同，该字段通常用来作为分组的依据！
1.3.4、如何使用分组？
单独使用分组
按照字段进行分组，那么select查询的字段只能是该字段的内容，想要获取组内的其他相关信息，需要借助函数！
MariaDB [study]> SELECT post FROM employee GROUP BY post;
+----------------------------+
| post |
+----------------------------+
| operation |
| sale |
| teacher |
| 老男孩驻沙河办事处外交大使 |
+----------------------------+
4 rows in set (0.006 sec)
group_concat
group_concat不单单支持获取分组后的其它字段值，还支持拼接操作，多个字段拼接！concat则是不分组的时候用！
MariaDB [study]> SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;
+----------------------------+-------------------------------------------------------+
| post | GROUP_CONCAT(name) |
+----------------------------+-------------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| sale | 格格,星星,丁丁,丫丫,歪歪 |
| teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |
| 老男孩驻沙河办事处外交大使 | egon |
+----------------------------+-------------------------------------------------------+
4 rows in set (0.003 sec)
1.3.5、聚合函数
where先对整体数据进行过滤后再进行分组，因此聚合函数只能在分组之后使用
max
分组内最大值
MariaDB [study]> SELECT MAX(salary) FROM employee;
+-------------+
| MAX(salary) |
+-------------+
| 1000000.31 |
+-------------+
1 row in set (0.004 sec)
min
组内最小值
MariaDB [study]> SELECT min(salary) FROM employee;
+-------------+
| min(salary) |
+-------------+
| 1000.37 |
+-------------+
1 row in set (0.000 sec)
count
组内总数据个数
MariaDB [study]> SELECT COUNT(id) FROM employee;
+-----------+
| COUNT(id) |
+-----------+
| 18 |
+-----------+
1 row in set (0.000 sec)
avg
组内平均值
MariaDB [study]> SELECT AVG(salary) FROM employee;
+--------------+
| AVG(salary) |
+--------------+
| 64844.568889 |
+--------------+
1 row in set (0.000 sec)
sum
组内总和
MariaDB [study]> SELECT SUM(salary) FROM employee;
+-------------+
| SUM(salary) |
+-------------+
| 1167202.24 |
+-------------+
1 row in set (0.000 sec)
1.4、having
having是分组之后的筛选，它的语法跟where一样，只是having是在分组之后进行的过滤操作，即having可以使用聚合函数！
先来看看where的结果
MariaDB [study]> SELECT * FROM employee WHERE salary>10000;
+----+--------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+--------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
7 rows in set (0.004 sec)
再来看看having用法，显示职位与其所对应部门所有人，并计算出薪资最高者
MariaDB [study]> SELECT post,salary,GROUP_CONCAT(name) FROM employee GROUP BY post HAVING max(salary);
+----------------------------+----------+-------------------------------------------------------+
| post | salary | GROUP_CONCAT(name) |
+----------------------------+----------+-------------------------------------------------------+
| operation | 17000.00 | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| sale | 4000.33 | 格格,星星,丁丁,丫丫,歪歪 |
| teacher | 10000.00 | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |
| 老男孩驻沙河办事处外交大使 | 7300.33 | egon |
+----------------------------+----------+-------------------------------------------------------+
4 rows in set (0.000 sec)
1.5、distinct去重
一定要注意，必须是完全一样的数据才可以去重！一定要注意主键，有主键在是不可能去重的！
我先造两条除了ID，别的一模一样的数据
MariaDB [study]> SELECT * FROM employee WHERE name='egon';
+----+------+------+-----+------------+----------------------------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+----------------------------+--------------+---------+--------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
| 19 | egon | male | 18 | 2017-03-01 | NULL | NULL | NULL | NULL | NULL |
+----+------+------+-----+------------+----------------------------+--------------+---------+--------+-----------+
2 rows in set (0.000 sec)
去重后就剩一条了，记得distinct要放在select后面
MariaDB [study]> SELECT DISTINCT name,sex,age,hire_date FROM employee WHERE name='egon';
+------+------+-----+------------+
| name | sex | age | hire_date |
+------+------+-----+------------+
| egon | male | 18 | 2017-03-01 |
+------+------+-----+------------+
1 row in set (0.000 sec)
1.6、order by排序
默认是升序asc，所以asc可以省略不写，降序是desc！也可以排序多个字段，当相同时先排哪个！
MariaDB [study]> SELECT name,age,post,salary FROM employee order by salary;
+------------+-----+----------------------------+------------+
| name | age | post | salary |
+------------+-----+----------------------------+------------+
| egon | 18 | NULL | NULL |
| 丁丁 | 18 | sale | 1000.37 |
| 丫丫 | 38 | sale | 2000.35 |
| liwenzhou | 28 | teacher | 2100.00 |
| 歪歪 | 48 | sale | 3000.13 |
| 星星 | 18 | sale | 3000.29 |
| yuanhao | 73 | teacher | 3500.00 |
| 格格 | 28 | sale | 4000.33 |
| egon | 18 | 老男孩驻沙河办事处外交大使 | 7300.33 |
| wupeiqi | 81 | teacher | 8300.00 |
| jingliyang | 18 | teacher | 9000.00 |
| 成龙 | 48 | teacher | 10000.00 |
| 张野 | 28 | operation | 10000.13 |
| 程咬铁 | 18 | operation | 17000.00 |
| 程咬铜 | 18 | operation | 18000.00 |
| 程咬银 | 18 | operation | 19000.00 |
| 程咬金 | 18 | operation | 20000.00 |
| jinxin | 18 | teacher | 30000.00 |
| alex | 78 | teacher | 1000000.31 |
+------------+-----+----------------------------+------------+
19 rows in set (0.000 sec)
1.7、limit
限制展示条数，可直接跟限制条数，也可传两个参数，第一个是起始位置，第二个是展示条数。
MariaDB [study]> SELECT name,age,post,salary FROM employee order by salary LIMIT 5,5;
+---------+-----+----------------------------+---------+
| name | age | post | salary |
+---------+-----+----------------------------+---------+
| 星星 | 18 | sale | 3000.29 |
| yuanhao | 73 | teacher | 3500.00 |
| 格格 | 28 | sale | 4000.33 |
| egon | 18 | 老男孩驻沙河办事处外交大使 | 7300.33 |
| wupeiqi | 81 | teacher | 8300.00 |
+---------+-----+----------------------------+---------+
5 rows in set (0.001 sec)
二、多表查询
2.1、准备工作
2.1.1、建立表结构
MariaDB [study]> create table class(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.034 sec)
MariaDB [study]> create table student(
-> id int primary key auto_increment,
-> name varchar(20),
-> gender enum('male','female') not null default 'male',
-> age int,
-> class_id int
-> );
Query OK, 0 rows affected (0.031 sec)
2.1.2、插入数据
MariaDB [study]> insert into class values
-> (200,'Python'),
-> (201,'Linux'),
-> (202,'MySQL'),
-> (203,'Java');
Query OK, 4 rows affected (0.008 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [study]> insert into student(name,gender,age,class_id) values
-> ('sanxi','male',18,200),
-> ('chrystal','female',19,201),
-> ('andy','male',20,201),
-> ('ha','female',28,202),
-> ('xin','male',22,200),
-> ('boy','male',24,204)
-> ;
Query OK, 6 rows affected (0.013 sec)
Records: 6 Duplicates: 0 Warnings: 0
2.2、多表连接查询
MySQL提供了四种联表连接查询的方式
inner join(内连接)
内连接，只拼接两张表中共有的数据部分
MariaDB [study]> SELECT * FROM class INNER JOIN student ON student.class_id = class.id ;
+------+--------+----+----------+--------+------+----------+
| id | name | id | name | gender | age | class_id |
+------+--------+----+----------+--------+------+----------+
| 200 | Python | 1 | sanxi | male | 18 | 200 |
| 201 | Linux | 2 | chrystal | female | 19 | 201 |
| 201 | Linux | 3 | andy | male | 20 | 201 |
| 202 | MySQL | 4 | ha | female | 28 | 202 |
| 200 | Python | 5 | xin | male | 22 | 200 |
+------+--------+----+----------+--------+------+----------+
5 rows in set (0.000 sec)
left join(左连接)
左连接，左边的表数据全都展示出来，右边没有对应的就用null代替
MariaDB [study]> SELECT * FROM class LEFT JOIN student ON student.class_id = class.id;
+------+--------+------+----------+--------+------+----------+
| id | name | id | name | gender | age | class_id |
+------+--------+------+----------+--------+------+----------+
| 200 | Python | 1 | sanxi | male | 18 | 200 |
| 201 | Linux | 2 | chrystal | female | 19 | 201 |
| 201 | Linux | 3 | andy | male | 20 | 201 |
| 202 | MySQL | 4 | ha | female | 28 | 202 |
| 200 | Python | 5 | xin | male | 22 | 200 |
| 203 | Java | NULL | NULL | NULL | NULL | NULL |
+------+--------+------+----------+--------+------+----------+
6 rows in set (0.000 sec)
right join(右连接)
右连接，与上相反。
MariaDB [study]> SELECT * FROM class RIGHT JOIN student ON student.class_id = class.id;
+------+--------+----+----------+--------+------+----------+
| id | name | id | name | gender | age | class_id |
+------+--------+----+----------+--------+------+----------+
| 200 | Python | 1 | sanxi | male | 18 | 200 |
| 200 | Python | 5 | xin | male | 22 | 200 |
| 201 | Linux | 2 | chrystal | female | 19 | 201 |
| 201 | Linux | 3 | andy | male | 20 | 201 |
| 202 | MySQL | 4 | ha | female | 28 | 202 |
| NULL | NULL | 6 | boy | male | 24 | 204 |
+------+--------+----+----------+--------+------+----------+
6 rows in set (0.000 sec)
union(全连接)
全连接，左右两表所有的数据都展示出来;其实就是left+right集合体！
MariaDB [study]> SELECT * FROM class LEFT JOIN student ON student.class_id = class.id UNION SELECT * FROM class RIGHT JOIN student ON student.class_id=class.id;
+------+--------+------+----------+--------+------+----------+
| id | name | id | name | gender | age | class_id |
+------+--------+------+----------+--------+------+----------+
| 200 | Python | 1 | sanxi | male | 18 | 200 |
| 201 | Linux | 2 | chrystal | female | 19 | 201 |
| 201 | Linux | 3 | andy | male | 20 | 201 |
| 202 | MySQL | 4 | ha | female | 28 | 202 |
| 200 | Python | 5 | xin | male | 22 | 200 |
| 203 | Java | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | 6 | boy | male | 24 | 204 |
+------+--------+------+----------+--------+------+----------+
7 rows in set (0.001 sec)
三、子查询概念
子查询就是解决问题的思路，将一个复杂问题拆分开来，分步骤解决！它的核心思路就是将一个查询语句的结果当作另外一个查询语句的条件去用，类似Python嵌套；它还支持IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字，还有比较运算符！
3.1、IN
题目：查询平均年龄在20岁以上的班级名
先拿到平均年龄在20岁以上的班级的ID
MariaDB [study]> SELECT class_id FROM student group by class_id HAVING AVG(age) > 20;
+----------+
| class_id |
+----------+
| 202 |
| 204 |
+----------+
2 rows in set (0.001 sec)
再去班级表根据ID找出信息
MariaDB [study]> SELECT * FROM class WHERE id in (SELECT class_id FROM student group by class_id HAVING AVG(age) > 20);
+------+-------+
| id | name |
+------+-------+
| 202 | MySQL |
+------+-------+
1 row in set (0.005 sec)
3.2、EXISTS
EXISTS关键字返回布尔值结果来判断真假，真则执行外层SQL语句，假则不执行！
直接把上面的SQL稍微改改，因为没有年龄大于30的，所以外层SQL语句不执行！
MariaDB [study]> SELECT * FROM class WHERE EXISTS (SELECT class_id FROM student group by class_id HAVING AVG(age) > 30);
Empty set (0.001 sec)
四、总结
1、表的查询结果可以作为其它表的查询条件，也可以通过起别名的方式把它作为一张虚拟表跟其他表关联
2、多表查询就两种方式
- 先拼接表再查询
- 子查询，一步一步来