1、单表查询的语法
SELECT 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数。
2、关键字的执行优先级(重点)
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.限制结果的显示条数
创建公司员工表,表的字段和数据类型
company.employee
员工id idint姓名 name varchar
性别 sexenum年龄 ageint入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salarydouble办公室 officeint部门编号 depart_idint
View Code
create table employee(
idintprimary key auto_increment,
name varchar(20) not null,
sexenum('male','female') not null default 'male', #大部分是男的
ageint(3) unsigned not null default 28,
hire_date date notnull,
post varchar(50),
post_comment varchar(100),
salarydouble(15,2),
officeint,#一个部门一个屋
depart_idint);
# 查看表结构
mysql>desc employee;+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| emp_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 | |
| salart | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.08sec)
#插入记录
#三个部门:教学,销售,运营
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)
;
View Code
3、where约束
where子句中可以使用
1.比较运算符:>、=、<=、<>、!=;
2.between 80 and 100 :值在80到100之间;
3.in(80,90,100)值是10或20或30;
4.like 'xiaomagepattern': pattern可以是%或者_。%小时任意多字符,_表示一个字符;
5.逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not。
(1)单条件查询
查询id大于5的员工名和员工id。
mysql> select id,emp_name from employee where id > 5;+----+------------+
| id | emp_name |
+----+------------+
| 6 | jingliyang |
| 7 | jinxin |
| 8 | xiaomage |
| 9 | 歪歪 |
| 10 | 丫丫 |
| 11 | 丁丁 |
| 12 | 星星 |
| 13 | 格格 |
| 14 | 张野 |
| 15 | 程咬金 |
| 16 | 程咬银 |
| 17 | 程咬铜 |
| 18 | 程咬铁 |
View Code
(2)多条件查询
查询部门是老师且工资大于10000的员工名。
mysql> select emp_name from employee where post='teacher' and salary>10000;+----------+
| emp_name |
+----------+
| alex |
| jinxin |
+----------+
(3)关键字between and
查找工资在10000到20000之间的员工姓名和工资
查找工资不在10000到20000之间的员工姓名和工资
SELECT name,salary FROM employee
WHERE salary BETWEEN10000 AND 20000;
SELECT name,salary FROM employee
WHERE salary NOT BETWEEN10000 AND 20000;
(4)关键字in集合查询
查询工资是3000、3500、4000、9000的员工姓名和工资。
mysql> SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ;+------------+---------+
| name | salary |
+------------+---------+
| yuanhao | 3500.00 |
| jingliyang | 9000.00 |
+------------+---------+
查询工资不是3000、3500、4000、9000的员工姓名和工资。
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)
(5)关键字like模糊查询
查找名字以**开头的员工所有信息。
通配符’%’
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.00 sec)
通配符'_'mysql> SELECT age FROM employee WHERE name LIKE 'ale_';+-----+
| age |
+-----+
| 78 |
+-----+
1 row in set (0.00 sec)
练习:
1. 查看岗位是teacher的员工姓名、年龄2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资4. 查看岗位描述不为NULL的员工信息5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
答案:
select name,age from employee where post = 'teacher';select name,age from employee where post='teacher' and age > 30;select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;select * from employee where post_comment is not null;select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);select name,salary*12 from employee where post='teacher' and name like 'jin%';
2、group by分组查询
分组指的是:将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等,
可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,如果想查看组内信息,需要借助于聚合函数。
假如要分组查询工资的员工名字,直接select name,salary from employee group by salary;只能查询出每中工资第一个员工的信息,没有任何意义。
这个时候需要group_concat().
mysql> select sex,group_concat(name) fromemployee group by sex;+--------+-----------------------+
| sex | group_concat(name) |
+--------+-----------------------+
| male | egon,wupeiqi,nvshen |
| female | alex,yuanhao,xiaomage |
+--------+-----------------------+
2 rows in set (0.00 sec)
3、聚合函数
max()求最大值
min()求最小值
avg()求平均值
sum() 求和
count() 求总个数
强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组
# 每个部门有多少个员工select post,count(id) fromemployee group by post;
# 每个部门的最高薪水select post,max(salary) fromemployee group by post;
# 每个部门的最低薪水select post,min(salary) fromemployee group by post;
# 每个部门的平均薪水select post,avg(salary) fromemployee group by post;
# 每个部门的所有薪水select post,sum(age) from employee group by post;
小练习:
1. 查询岗位名以及岗位包含的所有员工名字2. 查询岗位名以及各岗位内包含的员工个数3. 查询公司内男员工和女员工的个数4. 查询岗位名以及各岗位的平均薪资5. 查询岗位名以及各岗位的最高薪资6. 查询岗位名以及各岗位的最低薪资7. 查询男员工与男员工的平均薪资,女员工与女员工的平均薪资
View Code
4、having过滤
HAVING与WHERE不一样的地方在于,
执行优先级从高到低:where > group by > having ,
Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。
Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数。
练习题:
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数2. 查询各岗位平均薪资大于10000的岗位名、平均工资3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
View Code
# 题1:
mysql> select post,group_concat(name),count(id) fromemployee group by post;+-----------------------------------------+-----------------------------------------------------------+-----------+
| post | group_concat(name) | count(id) |
+-----------------------------------------+-----------------------------------------------------------+-----------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | 5 |
| sale | 格格,星星,丁丁,丫丫,歪歪 | 5 |
| teacher | xiaomage,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | 7 |
| 老男孩驻沙河办事处外交大使 | egon | 1 |
+-----------------------------------------+-----------------------------------------------------------+-----------+
4 rows in set (0.00sec)
mysql> select post,group_concat(name),count(id) from employee group by post having count(id)<2;+-----------------------------------------+--------------------+-----------+
| post | group_concat(name) | count(id) |
+-----------------------------------------+--------------------+-----------+
| 老男孩驻沙河办事处外交大使 | egon | 1 |
+-----------------------------------------+--------------------+-----------+
1 row in set (0.00sec)
#题2:
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------+---------------+
2 rows in set (0.00sec)
#题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 |
+-----------+--------------+
1 row in set (0.00 sec)
View Code
5、order by查询顺序
按单列排序
SELECT*FROM employee ORDER BY age;
SELECT*FROM employee ORDER BY age ASC;#正序
SELECT* FROM employee ORDER BY age DESC;#逆序
按多列排序:先按照age升序排序,如果年纪相同,则按照id降序
SELECT* fromemployee
ORDER BY age ASC,
id DESC;
小练习:
1. 查询所有员工信息,先按照age升序排序,如果age相同则按照hire_date降序排序2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
View Code
答案;
# 题目1select * fromemployee ORDER BY age asc,hire_date desc;
# 题目2
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000order by avg(salary) asc;+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------+---------------+
2 rows in set (0.00sec)
# 题目3
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000order by avg(salary) desc;+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| teacher | 151842.901429 |
| operation | 16800.026000 |
+-----------+---------------+
2 rows in set (0.00 sec)
View Code
6、limit限制查询的记录数
示例:
SELECT*FROM employee ORDER BY salary DESC
LIMIT3; #默认初始位置为0
SELECT*FROM employee ORDER BY salary DESC
LIMIT0,5; #从第0开始,即先查询出第一条,然后包含这一条在内往后查5条
SELECT*FROM employee ORDER BY salary DESC
LIMIT5,5; #从第5开始,即先查询出第6条,然后包含这一条在内往后查5条
小练习:分页显示,每页5条
# 第1页数据
mysql> select * from employee limit 0,5;+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 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 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
5 rows in set (0.00sec)
# 第2页数据
mysql> select * from employee limit 5,5;+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 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 |
| 8 | xiaomage | 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 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
5 rows in set (0.00sec)
# 第3页数据
mysql> select * from employee limit 10,5;+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | 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 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)
View Code