MySql - 单表查询
单表查询
1.单表查询的语法
一、单表查询的语法 SELECT 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数 二、关键字的执行优先级（重点） 重点中的重点：关键字的执行优先级 from where group by having select distinct order by limit 1.找到表:from 2.拿着where指定的约束条件，去文件/表中取出一条条记录 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组 4.将分组的结果进行having过滤 5.执行select 6.去重 7.将结果按条件排序：order by 8.限制结果的显示条数
2.单表查询关键字
实例:
- 创建公司员工表,表的字段和数据类型
表的基本信息
company.employee 员工id id int 姓名 name varchar 性别 sex enum 年龄 age int 入职日期 hire_date date 岗位 post varchar 职位描述 post_comment varchar 薪水 salary double 办公室 office int 部门编号 depart_id int
- 数据创建
数据的创建
mysql> create table employee( -> id int primary key auto_increment, -> name varchar(20) not null, -> sex enum('male','female') not null default 'male', -> age int(3) unsigned not null default 28, -> hire_date date not null, -> post varchar(50), -> post_comment varchar(100), -> salary double(15,2), -> office int, -> depart_id int -> ); Query OK, 0 rows affected (0.46 sec) mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+ rows in set (0.01 sec) mysql> insert into employee(name ,sex,age,hire_date,post,salary,office,depart_id) values -> ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), -> ('alex','male',78,'20150302','teacher',1000000.31,401,1), -> ('wupeiqi','male',81,'20130305','teacher',8300,401,1), -> ('yuanhao','male',73,'20140701','teacher',3500,401,1), -> ('liwenzhou','male',28,'20121101','teacher',2100,401,1), -> ('jingliyang','female',18,'20110211','teacher',9000,401,1), -> ('jinxin','male',18,'19000301','teacher',30000,401,1), -> ('xiaomage','male',48,'20101111','teacher',10000,401,1), -> -> ('歪歪','female',48,'20150311','sale',3000.13,402,2), -> ('丫丫','female',38,'20101101','sale',2000.35,402,2), -> ('丁丁','female',18,'20110312','sale',1000.37,402,2), -> ('星星','female',18,'20160513','sale',3000.29,402,2), -> ('格格','female',28,'20170127','sale',4000.33,402,2), -> -> ('张野','male',28,'20160311','operation',10000.13,403,3), -> ('程咬金','male',18,'19970312','operation',20000,403,3), -> ('程咬银','female',18,'20130311','operation',19000,403,3), -> ('程咬铜','male',18,'20150411','operation',18000,403,3), -> ('程咬铁','female',18,'20140512','operation',17000,403,3) -> ; Query OK, 18 rows affected (0.42 se 数据创建
2.1 where 约束
where子句中可以使用 1.比较运算符：>、<、>=、<=、<>、!= 2.between 80 and 100 ：值在80到100之间 3.in(80,90,100)值是80或90或100 4.like 'xiaomagepattern': pattern可以是%或者_。%小时任意多字符，_表示一个字符(通配符) 5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
- 比较运算符
View Code
mysql> select id,name from employee where post='teacher' and salary>10000; +----+--------+ | id | name | +----+--------+ | 2 | alex | | 7 | jinxin | +----+--------+ 2 rows in set (0.36 sec
- between
View Code
mysql> select name,salary from employee where salary between 10000 and 20000; +-----------+----------+ | name | salary | +-----------+----------+ | xiaomage | 10000.00 | | 张野 | 10000.13 | | 程咬金 | 20000.00 | | 程咬银 | 19000.00 | | 程咬铜 | 18000.00 | | 程咬铁 | 17000.00 | +-----------+----------+ 6 rows in set (0.00 sec)
- 逻辑运算符
View Code
mysql> select name,salary from employee where salary=3000 or salary=3500 or salary=9000; +------------+---------+ | name | salary | +------------+---------+ | yuanhao | 3500.00 | | jingliyang | 9000.00 | +------------+---------+ 2 rows in set (0.34 sec)
- in
View Code
mysql> select name,salary from employee where salary in (3000,3500,4000,9000); +------------+---------+ | name | salary | +------------+---------+ | yuanhao | 3500.00 | | jingliyang | 9000.00 | +------------+---------+ 2 rows in set (0.00 sec) mysql> select name,salary from employee where salary not in (3000,3500,4000,9000); +-----------+------------+ | name | salary | +-----------+------------+ | egon | 7300.33 | | alex | 1000000.31 | | wupeiqi | 8300.00 | | liwenzhou | 2100.00 | | jinxin | 30000.00 | | xiaomage | 10000.00 | | 歪歪 | 3000.13 | | 丫丫 | 2000.35 | | 丁丁 | 1000.37 | | 星星 | 3000.29 | | 格格 | 4000.33 | | 张野 | 10000.13 | | 程咬金 | 20000.00 | | 程咬银 | 19000.00 | | 程咬铜 | 18000.00 | | 程咬铁 | 17000.00 | +-----------+------------+ 16 rows in set (0.00 sec)
- like
View Code
mysql> select * from employee where name like 'jin%'; +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 2 rows in set (0.10 sec)
- 通配符
View Code
mysql> select age from employee where name like 'ale_'; +-----+ | age | +-----+ | 78 | +-----+ 1 row in set (0.00 sec)
-
注: ' ' 是空字符串,不是null
View Codemysql> select name,post_comment from employee where post_comment=''; Empty set (0.00 sec) mysql> mysql> update employee set post_comment='' where id=2; Query OK, 1 row affected (0.11 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select name,post_comment from employee where post_comment=''; +------+--------------+ | name | post_comment | +------+--------------+ | alex | | +------+--------------+ 1 row in set (0.00 sec)
2.2 group by 分组查询
- 基本概述
View Code
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的 #2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等 #3、为何要分组呢？ 取每个部门的最高工资 取每个部门的员工数 取男人数和女人数 小窍门：‘每’这个字后面的字段，就是我们分组的依据 #4、大前提： 可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
- 关于ONLY_FULL_GROUP_BY
mysql> select * from employee group by post; +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 | +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 4 rows in set (0.00 sec)
解决方式#由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的 如果想分组，则必须要设置全局的sql的模式为ONLY_FULL_GROUP_BY mysql> set global sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) #查看MySQL 5.7默认的sql_mode如下： mysql> select @@global.sql_mode; +--------------------+ | @@global.sql_mode | +--------------------+ | ONLY_FULL_GROUP_BY | +--------------------+ row in set (0.00 sec) mysql> exit;#设置成功后，一定要退出，然后重新登录方可生效 # 继续验证通过 groupby分组之后,只能查看当前字段,如果要查看组内信息,需要借助聚合函数. mysql> select * from employee group by post; ERROR 1055 (42000): 'db15.employee.id' isn't in GROUP BY mysql> select post from employee group by post; +-----------------------------------------+ | post | +-----------------------------------------+ | operation | | sale | | teacher | | 老男孩驻沙河办事处外交大使 | +-----------------------------------------+ rows in set (0.00 sec)
2.3 聚合函数
max() 最大值 min() 最小值 sum() 求和 count() 求总个数 avg() 求平均值 #强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组 注意:是组内信息
练习1 :
mysql> select post,count(id) from employee group by post; +-----------------------------------------+-----------+ | post | count(id) | +-----------------------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩驻沙河办事处外交大使 | 1 | +-----------------------------------------+-----------+ 4 rows in set (0.37 sec) mysql> select post,max(salary) from employee group by post; +-----------------------------------------+-------------+ | post | max(salary) | +-----------------------------------------+-------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+-------------+ 4 rows in set (0.35 sec) mysql> select post,min(salary) from employee group by post; +-----------------------------------------+-------------+ | post | min(salary) | +-----------------------------------------+-------------+ | operation | 10000.13 | | sale | 1000.37 | | teacher | 2100.00 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+-------------+ 4 rows in set (0.00 sec) mysql> select post,avg(salary) from employee group by post; +-----------------------------------------+---------------+ | post | avg(salary) | +-----------------------------------------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 151842.901429 | | 老男孩驻沙河办事处外交大使 | 7300.330000 | +-----------------------------------------+---------------+ 4 rows in set (0.34 sec) mysql> select post,sum(salary) from employee group by post; +-----------------------------------------+-------------+ | post | sum(salary) | +-----------------------------------------+-------------+ | operation | 84000.13 | | sale | 13001.47 | | teacher | 1062900.31 | | 老男孩驻沙河办事处外交大使 | 7300.33 | +-----------------------------------------+-------------+ 4 rows in set (0.00 sec)
概念:虚拟表 (不是物理存在的,只是被构造出来的)
mysql> select id,name,1 from employee; +----+------------+---+ | id | name | 1 | +----+------------+---+ | 1 | egon | 1 | | 2 | alex | 1 | | 3 | wupeiqi | 1 | | 4 | yuanhao | 1 | | 5 | liwenzhou | 1 | | 6 | jingliyang | 1 | | 7 | jinxin | 1 | | 8 | xiaomage | 1 | | 9 | 歪歪 | 1 | | 10 | 丫丫 | 1 | | 11 | 丁丁 | 1 | | 12 | 星星 | 1 | | 13 | 格格 | 1 | | 14 | 张野 | 1 | | 15 | 程咬金 | 1 | | 16 | 程咬银 | 1 | | 17 | 程咬铜 | 1 | | 18 | 程咬铁 | 1 | +----+------------+---+ 18 rows in set (0.00 sec) mysql> select post,count(1) from employee group by post; +-----------------------------------------+----------+ | post | count(1) | +-----------------------------------------+----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩驻沙河办事处外交大使 | 1 | +-----------------------------------------+----------+ 4 rows in set (0.00 sec)
给虚拟表起别名
mysql> select A.a from (select post,count(1) as a from employee group by post) as A; +---+ | a | +---+ | 5 | | 5 | | 7 | | 1 | +---+ 4 rows in set (0.10 sec)
练习2:
1. 查询岗位名以及岗位包含的所有员工名字 2. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
mysql> select post,group_concat(name) from employee group by post; +-----------------------------------------+-----------------------------------------------------------+ | post | group_concat(name) | +-----------------------------------------+-----------------------------------------------------------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | | sale | 格格,星星,丁丁,丫丫,歪歪 | | teacher | xiaomage,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | | 老男孩驻沙河办事处外交大使 | egon | +-----------------------------------------+-----------------------------------------------------------+ 4 rows in set (0.38 sec) mysql> select sex,avg(salary) from employee group by sex; +--------+---------------+ | sex | avg(salary) | +--------+---------------+ | male | 110920.077000 | | female | 7250.183750 | +--------+---------------+ 2 rows in set (0.00 sec)
2.4 HAVING 过滤
where 和 having 的区别
- 执行优先级从高到低：where > group by > having
- Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数
mysql> select * from employee where avg(age)>20; ERROR 1111 (HY000): Invalid use of group function --->组功能的使用无效
- Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
mysql> select * from employee having salary>1000000; ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause # 必须分组之后才能使用having
这针对的是5.6版本 ,对于5.7 版本 (整体作为一组 可以执行sql)
------- 分组 和 having练习 --------
- 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
mysql> select post,group_concat(name),count(1) from employee group by post having count(1)<2; +-----------------------------------------+--------------------+----------+ | post | group_concat(name) | count(1) | +-----------------------------------------+--------------------+----------+ | 老男孩驻沙河办事处外交大使 | egon | 1 | +-----------------------------------------+--------------------+----------+ 1 row in set (0.00 sec)
- 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
mysql> select post,avg(salary) from employee group by post having avg(salary)>10000 and avg(salary)<20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | operation | 16800.026000 | +-----------+--------------+ 1 row in set (0.00 sec)
- 按职位分组后,组内平均年龄大于25岁的组的名称,人数,组内平均年龄
mysql> select post,count(1),avg(age) from employee group by post having avg(age)>25; +---------+----------+----------+ | post | count(1) | avg(age) | +---------+----------+----------+ | sale | 5 | 30.0000 | | teacher | 7 | 49.1429 | +---------+----------+----------+ 2 rows in set (0.00 sec)
2.5 order by
- 按照单列排序
mysql> select * from employee order by age; mysql> select * from employee order by age asc; mysql> select * from employee order by age desc;
- 按照多列排序(例: 先按照age升序排序,如果年纪相同,则按照id降序)
mysql> select * from employee order by age asc,id desc;
---- 小练习 ----
- 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
mysql> select * from employee order by age asc,hire_date desc;
- 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
mysql> select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) asc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ 2 rows in set (0.00 sec)
- 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
mysql> select post,avg(salary) from employee group by post having avg(salary)>10000 order by avg(salary) desc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | teacher | 151842.901429 | | operation | 16800.026000 | +-----------+---------------+ 2 rows in set (0.00 sec)
2.6 limit 限制
- 默认初始位置为0
mysql> select * from employee order by salary desc limit 3; +----+-----------+------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+------+-----+------------+-----------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | +----+-----------+------+-----+------------+-----------+--------------+------------+--------+-----------+ 3 rows in set (0.00 sec)
- 从第0开始,即先查出第一条,然后包含这一条在内往后查5条
mysql> select * from employee order by salary desc limit 0,5; +----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 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 | +----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+ 5 rows in set (0.00 sec)
- 从第5开始,即先查出第一条,然后包含这一条在内往后查5条
mysql> select * from employee order by salary desc limit 5,5; +----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 8 | xiaomage | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | +----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 5 rows in set (0.00 sec
