七 .数据库(表的查询)
一 .表的查询
1. 表单查询及关键字和查询优先级
语法:
SELECT DISTINCT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
优先级
from：找到表 where：拿着where指定的约束条件，去文件/表中取出一条条记录 group by：将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组 select：执行select distinct：去重 having：将分组的结果进行having过滤 order by：将结果按条件排序：order by limit：限制结果的显示条数
mysql> select * from cc; +----+-----------+-----+-----+------------+--------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+-----+-----+------------+--------------------------------+--------------+------------+--------+-----------+ | 19 | nick | 男 | 18 | 2017-03-01 | 老男孩驻上海虹桥最帅 | NULL | 7300.33 | 401 | 1 | | 20 | jason | 男 | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 21 | sean | 女 | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 22 | tank | 男 | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 23 | oscar | 女 | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 24 | mac | 男 | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 25 | rocky | 女 | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 26 | 成龙 | 男 | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 27 | 歪歪 | 女 | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 28 | 丫丫 | 男 | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 29 | 丁丁 | 女 | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 30 | 星星 | 男 | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 31 | 格格 | 男 | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 32 | 张野 | 男 | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 33 | 程咬金 | 女 | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 34 | 程咬银 | 男 | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 35 | 程咬铜 | 男 | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 36 | 程咬铁 | 女 | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+-----------+-----+-----+------------+--------------------------------+--------------+------------+--------+-----------+
1.简单查询
mysql> select sex,age from cc;
+-----+-----+
| sex | age |
+-----+-----+
| 男 | 18 |
| 男 | 78 |
| 女 | 81 |
| 男 | 73 |
| 女 | 28 |
| 男 | 18 |
| 女 | 18 |
| 男 | 48 |
| 女 | 48 |
| 男 | 38 |
| 女 | 18 |
| 男 | 18 |
| 男 | 28 |
| 男 | 28 |
| 女 | 18 |
| 男 | 18 |
| 男 | 18 |
| 女 | 18 |
+-----+-----+
mysql> select distinct age from cc; distinct避免重复
+-----+
| age |
+-----+
| 18 |
| 78 |
| 81 |
| 73 |
| 28 |
| 48 |
| 38 |
+-----+
mysql> select distinct post from cc; 查出所有的岗位（去掉重复 disinct）
+--------------------------------+
| post |
+--------------------------------+
| 老男孩驻上海虹桥最帅 |
| teacher |
| sale |
| operation |
+--------------------------------+
4 rows in set (0.00 sec)
2.通过四则运算查询
mysql> select name,salary*2 from cc;
+-----------+------------+
| name | salary*2 |
+-----------+------------+
| nick | 14600.66 |
| jason | 2000000.62 |
| sean | 16600.00 |
| tank | 7000.00 |
| oscar | 4200.00 |
| mac | 18000.00 |
| rocky | 60000.00 |
| 成龙 | 20000.00 |
| 歪歪 | 6000.26 |
| 丫丫 | 4000.70 |
| 丁丁 | 2000.74 |
| 星星 | 6000.58 |
| 格格 | 8000.66 |
| 张野 | 20000.26 |
| 程咬金 | 40000.00 |
| 程咬银 | 38000.00 |
| 程咬铜 | 36000.00 |
| 程咬铁 | 34000.00 |
+-----------+------------+
mysql> select name,salary*2 as bb from cc;
+-----------+------------+
| name | bb |
+-----------+------------+
| nick | 14600.66 |
| jason | 2000000.62 |
| sean | 16600.00 |
| tank | 7000.00 |
| oscar | 4200.00 |
| mac | 18000.00 |
| rocky | 60000.00 |
| 成龙 | 20000.00 |
| 歪歪 | 6000.26 |
| 丫丫 | 4000.70 |
| 丁丁 | 2000.74 |
| 星星 | 6000.58 |
| 格格 | 8000.66 |
| 张野 | 20000.26 |
| 程咬金 | 40000.00 |
| 程咬银 | 38000.00 |
| 程咬铜 | 36000.00 |
| 程咬铁 | 34000.00 |
+-----------+------------+
18 rows in set (0.00 sec
mysql> select name ,salary*2 bb from cc;
+-----------+------------+
| name | bb |
+-----------+------------+
| nick | 14600.66 |
| jason | 2000000.62 |
| sean | 16600.00 |
| tank | 7000.00 |
| oscar | 4200.00 |
| mac | 18000.00 |
| rocky | 60000.00 |
| 成龙 | 20000.00 |
| 歪歪 | 6000.26 |
| 丫丫 | 4000.70 |
| 丁丁 | 2000.74 |
| 星星 | 6000.58 |
| 格格 | 8000.66 |
| 张野 | 20000.26 |
| 程咬金 | 40000.00 |
| 程咬银 | 38000.00 |
| 程咬铜 | 36000.00 |
| 程咬铁 | 34000.00 |
+-----------+------------+
mysql> select name ,salary*2 bb from cc; 查出所有员工名字，以及他们的年薪，年薪的字段名为bb
+-----------+------------+
| name | bb |
+-----------+------------+
| nick | 14600.66 |
| jason | 2000000.62 |
| sean | 16600.00 |
| tank | 7000.00 |
| oscar | 4200.00 |
| mac | 18000.00 |
| rocky | 60000.00 |
| 成龙 | 20000.00 |
| 歪歪 | 6000.26 |
| 丫丫 | 4000.70 |
| 丁丁 | 2000.74 |
| 星星 | 6000.58 |
| 格格 | 8000.66 |
| 张野 | 20000.26 |
| 程咬金 | 40000.00 |
| 程咬银 | 38000.00 |
| 程咬铜 | 36000.00 |
| 程咬铁 | 34000.00 |
+-----------+------------+
3.定义显示格式查询
mysql> select concat("姓名"'name',"年薪","salary") as vv from cc; CONCAT() 函数用于连接字符串
+------------------------+
| vv |
+------------------------+
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
| 年薪姓名namesalary |
+------------------------+
mysql> select concat('<名字:',name,'> ','<薪资:',salary,'>') from cc; 查出所有员工的名字，薪资，格式为<名字:nick> <薪资:3000>
+-------------------------------------------------------+
| concat('<名字:',name,'> ','<薪资:',salary,'>') |
+-------------------------------------------------------+
| <名字:nick> <薪资:7300.33> |
| <名字:jason> <薪资:1000000.31> |
| <名字:sean> <薪资:8300.00> |
| <名字:tank> <薪资:3500.00> |
| <名字:oscar> <薪资:2100.00> |
| <名字:mac> <薪资:9000.00> |
| <名字:rocky> <薪资:30000.00> |
| <名字:成龙> <薪资:10000.00> |
| <名字:歪歪> <薪资:3000.13> |
| <名字:丫丫> <薪资:2000.35> |
| <名字:丁丁> <薪资:1000.37> |
| <名字:星星> <薪资:3000.29> |
| <名字:格格> <薪资:4000.33> |
| <名字:张野> <薪资:10000.13> |
| <名字:程咬金> <薪资:20000.00> |
| <名字:程咬银> <薪资:19000.00> |
| <名字:程咬铜> <薪资:18000.00> |
| <名字:程咬铁> <薪资:17000.00> |
+-------------------------------------------------------+
2.条件约束查询（where）
比较运算符：> < >= <= <> !=
between 80 and 100 值在80到100之间
in(80,90,100) 值是80或90或100
like 'n%'
通配符可以是%或_，
%表示任意多字符
_表示一个字符
逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
+----+-----------+-----+-----+------------+--------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+-----+-----+------------+--------------------------------+--------------+------------+--------+-----------+ | 19 | nick | 男 | 18 | 2017-03-01 | 老男孩驻上海虹桥最帅 | NULL | 7300.33 | 401 | 1 | | 20 | jason | 男 | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 21 | sean | 女 | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 22 | tank | 男 | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 23 | oscar | 女 | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | | 24 | mac | 男 | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 25 | rocky | 女 | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 26 | 成龙 | 男 | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 27 | 歪歪 | 女 | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 28 | 丫丫 | 男 | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | | 29 | 丁丁 | 女 | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 30 | 星星 | 男 | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 31 | 格格 | 男 | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 32 | 张野 | 男 | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 33 | 程咬金 | 女 | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | | 34 | 程咬银 | 男 | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 | | 35 | 程咬铜 | 男 | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 | | 36 | 程咬铁 | 女 | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | +----+-----------+-----+-----+------------+--------------------------------+--------------+------------+--------+-----------+
1.单条件查询
mysql> select name from cc where post='sale'; 查询工作对应的老师名 +--------+ | name | +--------+ | 歪歪 | | 丫丫 | | 丁丁 | | 星星 | | 格格 | +--------+
2.多条件查询
mysql> select name,salary from cc where post="teacher" and salary>10000; 查看薪水大于10000 并且职业为老师 对应的名字 和薪水
+-------+------------+
| name | salary |
+-------+------------+
| jason | 1000000.31 |
| rocky | 30000.00 |
+-------+------------+
3.关键字查询(between and)
mysql> select name,salary from cc where salary between 10000 and 20000; 查看薪水在10000----20000之间 对应的名字
+-----------+----------+
| name | salary |
+-----------+----------+
| 成龙 | 10000.00 |
| 张野 | 10000.13 |
| 程咬金 | 20000.00 |
| 程咬银 | 19000.00 |
| 程咬铜 | 18000.00 |
| 程咬铁 | 17000.00 |
+-----------+----------+
6 rows in set (0.28 sec)
4. 关键字is null(判断某个字段是否为null不能用等号，需要用is)
mysql> select name ,post_comment from cc where post_comment is null; 查看post_comment为空对应的名字
+-----------+--------------+
| name | post_comment |
+-----------+--------------+
| nick | NULL |
| jason | NULL |
| sean | NULL |
| tank | NULL |
| oscar | NULL |
| mac | NULL |
| rocky | NULL |
| 成龙 | NULL |
| 歪歪 | NULL |
| 丫丫 | NULL |
| 丁丁 | NULL |
| 星星 | NULL |
| 格格 | NULL |
| 张野 | NULL |
| 程咬金 | NULL |
| 程咬银 | NULL |
| 程咬铜 | NULL |
| 程咬铁 | NULL |
+-----------+--------------+
18 rows in set (0.00 sec)
5.关键字in集合查询
mysql> select name,salary from cc where salary=3000 or salary=3500;
+------+---------+
| name | salary |
+------+---------+
| tank | 3500.00 |
+------+---------+
1 row in set (0.00 sec)
mysql> select name,salary from cc where salary in (3000,4000,9000,12000); 查看in集合里面又没符合表里面的薪水 所对应的老师
+------+---------+
| name | salary |
+------+---------+
| mac | 9000.00 |
+------+---------+
1 row in set (0.29 sec)
6. 关键字like模糊查询 % __
mysql> select * from cc where name like 'ni%'; 查看名字中含有ni对应的老师
+----+------+-----+-----+------------+--------------------------------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+-----+-----+------------+--------------------------------+--------------+---------+--------+-----------+
| 19 | nick | 男 | 18 | 2017-03-01 | 老男孩驻上海虹桥最帅 | NULL | 7300.33 | 401 | 1 |
+----+------+-----+-----+------------+--------------------------------+--------------+---------+--------+-----------+
1 row in set (0.30 sec)
通配符’_’
SELECT * FROM employee
WHERE emp_name LIKE 'ja__';
通配符’%’ SELECT * FROM employee WHERE emp_name LIKE 'ni%';
7. 练习
select emp_name,age from employee where post = 'teacher'; 查看岗位是teacher的员工姓名、年龄
select emp_name,age from employee where post='teacher' and age > 30; 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
select emp_name,age,salary from employee where post='teacher' and salary between 9000 and 10000; 查看岗位是teacher且薪资在9000-1000范员工姓名、年龄、薪资
select * from employee where post_comment is not null;查看岗位描述不为NULL的员工信息
select emp_name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select emp_name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select emp_name,salary*12 from employee where post='teacher' and emp_name like 'mac%';查看岗位是teacher且名字是jin开头的员工姓名、年薪
3. 分组查询（group by）
1.单独使用GROUP BY关键字分组
mysql> select post from cc group by post; 意思把所以职业分为一组
+--------------------------------+
| post |
+--------------------------------+
| 老男孩驻上海虹桥最帅 |
| teacher |
| sale |
| operation |
+--------------------------------+
4 rows in set (0.00 sec)
2.GROUP BY关键字和GROUP_CONCAT()函数一起使用
select post,group_concat(name) from cc group by post; 并按照组分 查看里面所有成员
+--------------------------------+------------------------------------------------+
| post | group_concat(name) |
+--------------------------------+------------------------------------------------+
| operation | 张野,程咬金,程咬银,程咬铜,程咬铁 |
| sale | 歪歪,丫丫,丁丁,星星,格格 |
| teacher | jason,sean,tank,oscar,mac,rocky,成龙 |
| 老男孩驻上海虹桥最帅 | nick |
+--------------------------------+------------------------------------------------+
4 rows in set (0.31 sec)
mysql> select post,group_concat(name) as lover from cc group by post; # 按照岗位分组，并查看组内成员名
+--------------------------------+------------------------------------------------+
| post | lover |
+--------------------------------+------------------------------------------------+
| operation | 张野,程咬金,程咬银,程咬铜,程咬铁 |
| sale | 歪歪,丫丫,丁丁,星星,格格 |
| teacher | jason,sean,tank,oscar,mac,rocky,成龙 |
| 老男孩驻上海虹桥最帅 | nick |
+--------------------------------+------------------------------------------------+
4 rows in set (0.00 sec)
3. GROUP BY与聚合函数一起使用
mysql> select post,count(id) as count from cc group by post;# 按照岗位分组，并查看每个组有多少人
+--------------------------------+-------+
| post | count |
+--------------------------------+-------+
| 老男孩驻上海虹桥最帅 | 1 |
| teacher | 7 |
| sale | 5 |
| operation | 5 |
+--------------------------------+-------+
4 rows in set (0.28 sec)
注意：如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义；多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
5.聚合函数
强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
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;
1.练习聚合函数
mysql> select post,group_concat(name) from cc group by post; 查询岗位名以及岗位包含的所有员工名字
+--------------------------------+------------------------------------------------+
| post | group_concat(name) |
+--------------------------------+------------------------------------------------+
| operation | 张野,程咬金,程咬银,程咬铜,程咬铁 |
| sale | 歪歪,丫丫,丁丁,星星,格格 |
| teacher | jason,sean,tank,oscar,mac,rocky,成龙 |
| 老男孩驻上海虹桥最帅 | nick |
+--------------------------------+------------------------------------------------+
4 rows in set (0.00 sec)
mysql> select post,count(id) from cc group by post;查询岗位名以及各岗位内包含的员工个数
+--------------------------------+-----------+
| post | count(id) |
+--------------------------------+-----------+
| 老男孩驻上海虹桥最帅 | 1 |
| teacher | 7 |
| sale | 5 |
| operation | 5 |
+--------------------------------+-----------+
4 rows in set (0.00 sec)
mysql> select sex,count(id) from cc group by sex;查询公司内男员工和女员工的个数
+-----+-----------+
| sex | count(id) |
+-----+-----------+
| 男 | 11 |
| 女 | 7 |
+-----+-----------+
2 rows in set (0.00 sec)
mysql> select post,avg(salary) from cc group by post; 查询岗位名以及各岗位的平均薪资 avg平均
+--------------------------------+---------------+
| post | avg(salary) |
+--------------------------------+---------------+
| 老男孩驻上海虹桥最帅 | 7300.330000 |
| teacher | 151842.901429 |
| sale | 2600.294000 |
| operation | 16800.026000 |
+--------------------------------+---------------+
4 rows in set (0.00 sec)
mysql> select post,max(salary) from cc group by post;查询岗位名以及各岗位的最高薪资
+--------------------------------+-------------+
| post | max(salary) |
+--------------------------------+-------------+
| 老男孩驻上海虹桥最帅 | 7300.33 |
| teacher | 1000000.31 |
| sale | 4000.33 |
| operation | 20000.00 |
+--------------------------------+-------------+
4 rows in set (0.00 sec)
mysql> select post,min(salary) from cc group by post;查询岗位名以及各岗位的最低薪资
+--------------------------------+-------------+
| post | min(salary) |
+--------------------------------+-------------+
| 老男孩驻上海虹桥最帅 | 7300.33 |
| teacher | 2100.00 |
| sale | 1000.37 |
| operation | 10000.13 |
+--------------------------------+-------------+
4 rows in set (0.00 sec)
mysql> select sex,avg(salary) from cc group by sex; 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
+-----+--------------+
| sex | avg(salary) |
+-----+--------------+
| 男 | 98709.249091 |
| 女 | 11628.642857 |
+-----+--------------+
6.过滤(where having)
执行优先级从高到低：where > group by > having
Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
1.简单过滤查询
mysql> select * from cc where salary>10000; 查询薪水大于10000的
+----+-----------+-----+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+-----+-----+------------+-----------+--------------+------------+--------+-----------+
| 20 | jason | 男 | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 25 | rocky | 女 | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 32 | 张野 | 男 | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 33 | 程咬金 | 女 | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 34 | 程咬银 | 男 | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 35 | 程咬铜 | 男 | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 36 | 程咬铁 | 女 | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+-----------+-----+-----+------------+-----------+--------------+------------+--------+-----------+
7 rows in set (0.00 sec)
mysql> select @@sql_mode; +--------------------+ | @@sql_mode | +--------------------+ | ONLY_FULL_GROUP_BY | +--------------------+ row in set (0.00 sec) mysql> select * from emp where salary > 100000; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | jason | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ row in set (0.00 sec) mysql> select post,group_concat(emp_name) from emp group by post having salary > 10000;#错误，分组后无法直接取到salary字段 ERROR 1054 (42S22): Unknown column 'salary' in 'having clause' mysql> select post,group_concat(emp_name) from emp group by post having avg(salary) > 10000; +-----------+-------------------------------------------------------+ | post | group_concat(emp_name) | +-----------+-------------------------------------------------------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | | teacher | 成龙,rocky,mac,oscar,tank,sean,jason | +-----------+-------------------------------------------------------+ rows in set (0.00 sec)
查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 查询各岗位平均薪资大于10000的岗位名、平均工资 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 题目1 mysql> select post,group_concat(emp_name),count(id) from employee group by post having count(id) < 2; +-----------------------------------------+--------------------+-----------+ | post | group_concat(emp_name) | count(id) | +-----------------------------------------+--------------------+-----------+ | 老男孩驻上海虹桥最帅 | nick | 1 | +-----------------------------------------+--------------------+-----------+ 题目2 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ 题目3 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | operation | 16800.026000 |
7. 排序查询（order by）
按单列排序 SELECT * FROM employee ORDER BY salary; 默认是升序 SELECT * FROM employee ORDER BY salary ASC; 升序 SELECT * FROM employee ORDER BY salary DESC; 降序 按多列排序:先按照age排序，如果年纪相同，则按照薪资排序 SELECT * from employee ORDER BY age, salary DESC;
查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列 题目1 mysql> select * from employee ORDER BY age asc,hire_date desc; 题目2 mysql> select post,avg(salary) from cc group by post having avg(salary) > 10000 order by avg(salary) asc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ 题目3 mysql> select post,avg(salary) from cc group by post having avg(salary) > 10000 order by avg(salary) desc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | teacher | 151842.901429 | | operation | 16800.026000 | +-----------+---------------+
8.限制条数查询（limit）
SELECT * FROM employee ORDER BY salary DESC LIMIT 3; #默认初始位置为0 SELECT * FROM employee ORDER BY salary DESC LIMIT 0,5; #从第0开始，即先出第一条，然后包含这一条在内往后查5条 SELECT * FROM employee ORDER BY salary DESC LIMIT 5,5; #从第5开始，即先出第6条，然后包含这一条在内往后查5条
mysql> select * from cc limit 3; 查看3条数据 +----+-------+-----+-----+------------+--------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-------+-----+-----+------------+--------------------------------+--------------+------------+--------+-----------+ | 19 | nick | 男 | 18 | 2017-03-01 | 老男孩驻上海虹桥最帅 | NULL | 7300.33 | 401 | 1 | | 20 | jason | 男 | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 21 | sean | 女 | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | +----+-------+-----+-----+------------+--------------------------------+--------------+------------+--------+-----------+ 3 rows in set (0.00 sec)
mysql> select * from cc limit 0,5; 查看0到五条数据 +----+-------+-----+-----+------------+--------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-------+-----+-----+------------+--------------------------------+--------------+------------+--------+-----------+ | 19 | nick | 男 | 18 | 2017-03-01 | 老男孩驻上海虹桥最帅 | NULL | 7300.33 | 401 | 1 | | 20 | jason | 男 | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 21 | sean | 女 | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 22 | tank | 男 | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 23 | oscar | 女 | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | +----+-------+-----+-----+------------+--------------------------------+--------------+------------+--------+-----------+ 5 rows in set (0.00 sec
mysql> select * from employee limit 5,5; 从第五个位置显示五条数据 +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | mac | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | rocky | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ rows in set (0.00 sec)
mysql> select * from employee limit 10,5; 从第10条数 据显示5条数据 +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ rows in set (0.00 sec)
9. 正则查询
SELECT * FROM employee WHERE emp_name REGEXP '^jas'; SELECT * FROM employee WHERE emp_name REGEXP 'on$'; SELECT * FROM employee WHERE emp_name REGEXP 'm{2}'; 小结：对字符串匹配的方式 WHERE emp_name = 'nick'; WHERE emp_name LIKE 'sea%'; WHERE emp_name REGEXP 'on$';
