Let's embrace 2020 for realizing our dreams and living a better year. 更好2020，让梦想落地，让更好发生。
数据准备
建表
代码语言：javascript复制mysql> create table employee(
-> id int primary key auto_increment,
-> name char(40),
-> age int default 18,
-> sex enum("male", "female") not null default "male",
-> position char(20),
-> salary float default 3000,
-> dep_id int
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc employee;
---------- ----------------------- ------ ----- --------- ----------------
| Field | Type | Null | Key | Default | Extra |
---------- ----------------------- ------ ----- --------- ----------------
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(40) | YES | | NULL | |
| age | int(11) | YES | | 18 | |
| sex | enum('male','female') | NO | | male | |
| position | char(20) | YES | | NULL | |
| salary | float | YES | | 3000 | |
| dep_id | int(11) | YES | | NULL | |
---------- ----------------------- ------ ----- --------- ----------------
7 rows in set (0.00 sec)插入数据
代码语言：javascript复制mysql> insert into employee(name, age, sex, position, salary, dep_id) values
-> ("jack", 20, "male", "lawyer", 888888.8, 3),
-> ("mark", 22, "male", "lawyer", 888888.8, 3),
-> ("hank", 25, "male", "lawyer", 7777.8, 3),
-> ("nick", 39, "male", "lawyer", 4438888.8, 3),
-> ("jenny", 26, "female", "lawyer", 10000.8, 3),
-> ("tony", 35, "male", "RD", 99999999, 1),
-> ("emmy", 27, "female", "RD", 9999, 1),
-> ("emmy", 23, "female", "finance", 5000, 2),
-> ("lucy", 45, "female", "finance", 10000, 2)
-> ;
Query OK, 9 rows affected (0.01 sec)
Records: 9 Duplicates: 0 Warnings: 0Where 查询
代码语言：javascript复制mysql> select name from employee where age > 30;
------
| name |
------
| nick |
| tony |
| lucy |
------
3 rows in set (0.01 sec)group by查询
代码语言：javascript复制mysql> select * from employee group by dep_id;ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'alpha.employee.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
原因：因为group by分组之后不能访问分组字段之外的字段，所以以下的select * 会报错. 换成 select dep_id from employee group by dep_id;
但是又无意义，所以此时需要用到聚合函数或者group_concat()函数
聚合函数
聚合函数聚合的是组的内容，若是没有分组，则默认是一组。聚合函数有： count()：取个数 max()：取最大值 min()：取最小值 avg()：取平均值 sum()：求和 例如：
代码语言：javascript复制 SELECT COUNT(*) FROM employee;
SELECT COUNT(*) FROM employee WHERE depart_id=1;
SELECT MAX(salary) FROM employee;
SELECT MIN(salary) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT SUM(salary) FROM employee WHERE depart_id=3;</pre>mysql> select count(dep_id), dep_id from employee group by dep_id;
--------------- --------
| count(dep_id) | dep_id |
--------------- --------
| 5 | 3 |
| 2 | 1 |
| 2 | 2 |
--------------- --------
3 rows in set (0.00 sec)如果觉得count(dep_id)展示不友好，可以使用as关键字给该字段起别名
代码语言：javascript复制mysql> select count(dep_id) as dep_id_count, dep_id from employee group by dep_id;
-------------- --------
| dep_id_count | dep_id |
-------------- --------
| 5 | 3 |
| 2 | 1 |
| 2 | 2 |
-------------- --------
3 rows in set (0.00 sec)Where group by查询
代码语言：javascript复制mysql> select count(name), dep_id from employee where salary > 5000 group by dep_id;
------------- --------
| count(name) | dep_id |
------------- --------
| 5 | 3 |
| 2 | 1 |
| 1 | 2 |
------------- --------
3 rows in set (0.00 sec)Having查询
查询各部门员工个数小于3的部门id、部门员工姓名、员工个数
代码语言：javascript复制mysql> select dep_id, group_concat(name), count(id) from employee group by dep_id having count(id) < 3;
-------- -------------------- -----------
| dep_id | group_concat(name) | count(id) |
-------- -------------------- -----------
| 1 | tony,emmy | 2 |
| 2 | emmy,lucy | 2 |
-------- -------------------- -----------
2 rows in set (0.00 sec)查询各部门平均工资大于10000的部门id、部门平均工资
代码语言：javascript复制mysql> select group_concat(dep_id), avg(salary) from employee group by dep_id having avg(salary) > 10000;
---------------------- -------------------
| group_concat(dep_id) | avg(salary) |
---------------------- -------------------
| 1,1 | 50004999.5 |
| 3,3,3,3,3 | 1246889.044921875 |
---------------------- -------------------
2 rows in set (0.00 sec)查询各部门平局工资大于10000且小于10000000的部门id、部门平均工资
代码语言：javascript复制mysql> select group_concat(dep_id), avg(salary) from employee group by dep_id having avg(salary) > 10000 and avg(salary) < 10000000;
---------------------- -------------------
| group_concat(dep_id) | avg(salary) |
---------------------- -------------------
| 3,3,3,3,3 | 1246889.044921875 |
---------------------- -------------------
1 row in set (0.00 sec)HAVING与WHERE不一样的地方在于!!!!!!
！！！执行优先级从高到低：where > group by > having
1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
order by查询
排序分为升序ASC和降序DESC
代码语言：javascript复制mysql> select * from employee order by salary ASC;
---- ------- ------ -------- ---------- ----------- --------
| id | name | age | sex | position | salary | dep_id |
---- ------- ------ -------- ---------- ----------- --------
| 8 | emmy | 23 | female | finance | 5000 | 2 |
| 3 | hank | 25 | male | lawyer | 7777.8 | 3 |
| 7 | emmy | 27 | female | RD | 9999 | 1 |
| 9 | lucy | 45 | female | finance | 10000 | 2 |
| 5 | jenny | 26 | female | lawyer | 10000.8 | 3 |
| 1 | jack | 20 | male | lawyer | 888889 | 3 |
| 2 | mark | 22 | male | lawyer | 888889 | 3 |
| 4 | nick | 39 | male | lawyer | 4438890 | 3 |
| 6 | tony | 35 | male | RD | 100000000 | 1 |
---- ------- ------ -------- ---------- ----------- --------
9 rows in set (0.00 sec)mysql> select * from employee order by salary DESC;
---- ------- ------ -------- ---------- ----------- --------
| id | name | age | sex | position | salary | dep_id |
---- ------- ------ -------- ---------- ----------- --------
| 6 | tony | 35 | male | RD | 100000000 | 1 |
| 4 | nick | 39 | male | lawyer | 4438890 | 3 |
| 1 | jack | 20 | male | lawyer | 888889 | 3 |
| 2 | mark | 22 | male | lawyer | 888889 | 3 |
| 5 | jenny | 26 | female | lawyer | 10000.8 | 3 |
| 9 | lucy | 45 | female | finance | 10000 | 2 |
| 7 | emmy | 27 | female | RD | 9999 | 1 |
| 3 | hank | 25 | male | lawyer | 7777.8 | 3 |
| 8 | emmy | 23 | female | finance | 5000 | 2 |
---- ------- ------ -------- ---------- ----------- --------
9 rows in set (0.00 sec)查询所有员工信息，先按照部门id升序，如果id相同则按照年龄降序
代码语言：javascript复制mysql> select * from employee order by dep_id ASC, age DESC;
---- ------- ------ -------- ---------- ----------- --------
| id | name | age | sex | position | salary | dep_id |
---- ------- ------ -------- ---------- ----------- --------
| 6 | tony | 35 | male | RD | 100000000 | 1 |
| 7 | emmy | 27 | female | RD | 9999 | 1 |
| 9 | lucy | 45 | female | finance | 10000 | 2 |
| 8 | emmy | 23 | female | finance | 5000 | 2 |
| 4 | nick | 39 | male | lawyer | 4438890 | 3 |
| 5 | jenny | 26 | female | lawyer | 10000.8 | 3 |
| 3 | hank | 25 | male | lawyer | 7777.8 | 3 |
| 2 | mark | 22 | male | lawyer | 888889 | 3 |
| 1 | jack | 20 | male | lawyer | 888889 | 3 |
---- ------- ------ -------- ---------- ----------- --------
9 rows in set (0.00 sec)查询各部门平均工资大于100000的部门id、平均工资，结果按平均工资升序
代码语言：javascript复制mysql> select dep_id, avg(salary) from employee group by dep_id having avg(salary) > 100000 order by avg(salary) ASC;
-------- -------------------
| dep_id | avg(salary) |
-------- -------------------
| 3 | 1246889.044921875 |
| 1 | 50004999.5 |
-------- -------------------
2 rows in set (0.00 sec)limit查询
limit限制查询的记录条数
查询工资大于10000的 的前三名员工信息，并按降序排列
代码语言：javascript复制mysql> select * from employee where salary > 10000 order by salary DESC limit 3;
---- ------ ------ ------ ---------- ----------- --------
| id | name | age | sex | position | salary | dep_id |
---- ------ ------ ------ ---------- ----------- --------
| 6 | tony | 35 | male | RD | 100000000 | 1 |
| 4 | nick | 39 | male | lawyer | 4438890 | 3 |
| 1 | jack | 20 | male | lawyer | 888889 | 3 |
---- ------ ------ ------ ---------- ----------- --------
3 rows in set (0.00 sec)limit分页查询
每页3条，查询第一页：
代码语言：javascript复制mysql> select * from employee order by salary limit 0, 3;
---- ------ ------ -------- ---------- -------- --------
| id | name | age | sex | position | salary | dep_id |
---- ------ ------ -------- ---------- -------- --------
| 8 | emmy | 23 | female | finance | 5000 | 2 |
| 3 | hank | 25 | male | lawyer | 7777.8 | 3 |
| 7 | emmy | 27 | female | RD | 9999 | 1 |
---- ------ ------ -------- ---------- -------- --------
3 rows in set (0.00 sec)每页三条，查询第二页：
代码语言：javascript复制mysql> select * from employee order by salary limit 3, 3;
---- ------- ------ -------- ---------- --------- --------
| id | name | age | sex | position | salary | dep_id |
---- ------- ------ -------- ---------- --------- --------
| 9 | lucy | 45 | female | finance | 10000 | 2 |
| 5 | jenny | 26 | female | lawyer | 10000.8 | 3 |
| 1 | jack | 20 | male | lawyer | 888889 | 3 |
---- ------- ------ -------- ---------- --------- --------
3 rows in set (0.01 sec)每页3条，查询第三页：
代码语言：javascript复制mysql> select * from employee order by salary limit 6, 3;
---- ------ ------ ------ ---------- ----------- --------
| id | name | age | sex | position | salary | dep_id |
---- ------ ------ ------ ---------- ----------- --------
| 2 | mark | 22 | male | lawyer | 888889 | 3 |
| 4 | nick | 39 | male | lawyer | 4438890 | 3 |
| 6 | tony | 35 | male | RD | 100000000 | 1 |
---- ------ ------ ------ ---------- ----------- --------
3 rows in set (0.00 sec)正则表达式查询
查询所有员工中以em开头的员工信息：
^代表开头
代码语言：javascript复制mysql> select * from employee where name REGEXP '^em';
---- ------ ------ -------- ---------- -------- --------
| id | name | age | sex | position | salary | dep_id |
---- ------ ------ -------- ---------- -------- --------
| 7 | emmy | 27 | female | RD | 9999 | 1 |
| 8 | emmy | 23 | female | finance | 5000 | 2 |
---- ------ ------ -------- ---------- -------- --------
2 rows in set (0.00 sec)查询所有员工中以ck结尾的员工信息:
$代表结尾
代码语言：javascript复制mysql> select * from employee where name REGEXP 'ck$';
---- ------ ------ ------ ---------- --------- --------
| id | name | age | sex | position | salary | dep_id |
---- ------ ------ ------ ---------- --------- --------
| 1 | jack | 20 | male | lawyer | 888889 | 3 |
| 4 | nick | 39 | male | lawyer | 4438890 | 3 |
---- ------ ------ ------ ---------- --------- --------
2 rows in set (0.00 sec)查询所有员工姓名包含2个连续m的员工信息：
代码语言：javascript复制mysql> SELECT * FROM employee WHERE name REGEXP 'm{2}';
---- ------ ------ -------- ---------- -------- --------
| id | name | age | sex | position | salary | dep_id |
---- ------ ------ -------- ---------- -------- --------
| 7 | emmy | 27 | female | RD | 9999 | 1 |
| 8 | emmy | 23 | female | finance | 5000 | 2 |
---- ------ ------ -------- ---------- -------- --------
2 rows in set (0.00 sec)查询所有员工中姓名以emm开头且已y结尾的员工信息：
代码语言：javascript复制mysql> select * from employee where name regexp '^emm.*[y]$’;
---- ------ ------ -------- ---------- -------- --------
| id | name | age | sex | position | salary | dep_id |
---- ------ ------ -------- ---------- -------- --------
| 7 | emmy | 27 | female | RD | 9999 | 1 |
| 8 | emmy | 23 | female | finance | 5000 | 2 |
---- ------ ------ -------- ---------- -------- --------
2 rows in set (0.00 sec)查询所有员工中姓名以emm开头且已i或y结尾的员工信息：
代码语言：javascript复制mysql> select * from employee where name regexp '^emm.*[iy]$';
---- ------ ------ -------- ---------- -------- --------
| id | name | age | sex | position | salary | dep_id |
---- ------ ------ -------- ---------- -------- --------
| 7 | emmy | 27 | female | RD | 9999 | 1 |
| 8 | emmy | 23 | female | finance | 5000 | 2 |
| 10 | emmi | 20 | female | finance | 20000 | 2 |
---- ------ ------ -------- ---------- -------- --------
3 rows in set (0.00 sec)另外还有一个模糊查询：like 但是like只有下划线_和百分号%
Like关键字模糊匹配姓名以emm开头的记录
代码语言：javascript复制mysql> select * from employee where name like 'emm%';
---- ------ ------ -------- ---------- -------- --------
| id | name | age | sex | position | salary | dep_id |
---- ------ ------ -------- ---------- -------- --------
| 7 | emmy | 27 | female | RD | 9999 | 1 |
| 8 | emmy | 23 | female | finance | 5000 | 2 |
| 10 | emmi | 20 | female | finance | 20000 | 2 |
---- ------ ------ -------- ---------- -------- --------
3 rows in set (0.00 sec)关键字执行顺序
重点中的重点：单表查询关键字的执行顺序（优先级）
from
where
group by
having
select
distinct
order by
limit
image.png
