一、单表查询的语法与关键字的执行优先级
"""
# 单表查询
# 单标查询完整与法：
# select distinct(关键字，代表查询的意思，后面跟)字段1，字段2...(代表你查询结果要什么字段，可以写很多个字段，)from (指定)库名.表名 (哪个库哪个表)
指定完from之后还可以跟什么
where 约束条件 过滤的意思
group by 分组依据
having 过滤条件
order by 排序的字段
limit 限制显示的条数
；(分号结束)
distinct 去重的意思，加到select后面
语法级别关键字的排列顺序如下：
select distinct 字段1，字段2，... from 库名.表名
where
group by
having
order by
limit;
每一个关键字不一定都要有
这是简写版的：
select * from 表名；
执行顺序不一定是这样的
运行角度来说先运行from！！因为数据库本质就是操作文件。操作文件第一步是判断文件路径存不存在
在的话打开文件，进行约束条件(where)
查询的结果可能有还多种，可以进行分组归类(group by)
分完组后再进行过滤，组级别的筛选(having)
having运行过滤完后出现的一条条记录进行去重(distinct)，如果有重复的话
再进行排序 (order by)按照平均成绩，升序或降序
显示，可能只要头三条，其余的都不要
关键字执行的顺序：
from
where
group by
having
distinct
order by
limit
def from():
pass
def where():
for line in f:
def group by():
pass
def having():
pass
def distinct():
pass
def order by():
pass
def limit():
pass
上面这些都被封装到了：
def select():
f= from()
data = where(f)
data = group by(data)
data = having(data)
data = distinct(data)
data= order by(data)
date = limit(data)
return data
"""
二、简单查询
"""
详细讲解单表查询每部分的关键字
准备表跟记录：
#创建表
create table employee(
id int not null unique 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
);
#插入记录 #三个部门：教学，销售，运营
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),
('成龙','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)
;
改表名：
alter table 表名 rename 新表名;
mysql> alter table employee rename emp;
Query OK, 0 rows affected (0.03 sec)
删除字段
alter table emp drop 字段名；
mysql> alter table emp drop office;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
简单查询
select * from 表名
*代表所有字段都包含在内
select * from emp;
mysql> select * from emp;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 1 |
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 1 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 3 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+-----------+
18 rows in set (0.00 sec)
如果想指定字段就
select 字段1，字段2...from emp;
得到的是一张虚拟的表
mysql> select name,salary,id from emp;
+------------+------------+----+
| name | salary | id |
+------------+------------+----+
| egon | 7300.33 | 1 |
| alex | 1000000.31 | 2 |
| wupeiqi | 8300.00 | 3 |
| yuanhao | 3500.00 | 4 |
| liwenzhou | 2100.00 | 5 |
| jingliyang | 9000.00 | 6 |
| jinxin | 30000.00 | 7 |
| 成龙 | 10000.00 | 8 |
| 歪歪 | 3000.13 | 9 |
| 丫丫 | 2000.35 | 10 |
| 丁丁 | 1000.37 | 11 |
| 星星 | 3000.29 | 12 |
| 格格 | 4000.33 | 13 |
| 张野 | 10000.13 | 14 |
| 程咬金 | 20000.00 | 15 |
| 程咬银 | 19000.00 | 16 |
| 程咬铜 | 18000.00 | 17 |
| 程咬铁 | 17000.00 | 18 |
+------------+------------+----+
18 rows in set (0.00 sec)
去重，去的是记录的重复
select distinct 字段 from emp；
mysql> select distinct post from emp;
+-----------------------------------------+
| post |
+-----------------------------------------+
| 老男孩驻沙河办事处外交大使 |
| teacher |
| sale |
| operation |
+-----------------------------------------+
4 rows in set (0.01 sec)
distinct是在having运行完后才运行的
四则运算：
比方说查员工的姓名，以及月薪
mysql> select name,salary,id from emp;
+------------+------------+----+
| name | salary | id |
+------------+------------+----+
| egon | 7300.33 | 1 |
| alex | 1000000.31 | 2 |
| wupeiqi | 8300.00 | 3 |
| yuanhao | 3500.00 | 4 |
| liwenzhou | 2100.00 | 5 |
| jingliyang | 9000.00 | 6 |
| jinxin | 30000.00 | 7 |
| 成龙 | 10000.00 | 8 |
| 歪歪 | 3000.13 | 9 |
| 丫丫 | 2000.35 | 10 |
| 丁丁 | 1000.37 | 11 |
| 星星 | 3000.29 | 12 |
| 格格 | 4000.33 | 13 |
| 张野 | 10000.13 | 14 |
| 程咬金 | 20000.00 | 15 |
| 程咬银 | 19000.00 | 16 |
| 程咬铜 | 18000.00 | 17 |
| 程咬铁 | 17000.00 | 18 |
+------------+------------+----+
18 rows in set (0.00 sec)
年薪怎么查？
可以在字段的基础上进行四则运算
select name，salary*12 from emp；
mysql> select name,salary*12 as annual_salary from emp;
+------------+---------------+
| name | annual_salary |
+------------+---------------+
| egon | 87603.96 |
| alex | 12000003.72 |
| wupeiqi | 99600.00 |
| yuanhao | 42000.00 |
| liwenzhou | 25200.00 |
| jingliyang | 108000.00 |
| jinxin | 360000.00 |
| 成龙 | 120000.00 |
| 歪歪 | 36001.56 |
| 丫丫 | 24004.20 |
| 丁丁 | 12004.44 |
| 星星 | 36003.48 |
| 格格 | 48003.96 |
| 张野 | 120001.56 |
| 程咬金 | 240000.00 |
| 程咬银 | 228000.00 |
| 程咬铜 | 216000.00 |
| 程咬铁 | 204000.00 |
+------------+---------------+
18 rows in set (0.00 sec)
虚拟表也有标题一说，如果没有指定就默认以字段名代替
mysql> select name,salary*12 from emp;
+------------+-------------+
| name | salary*12 |
+------------+-------------+
| egon | 87603.96 |
| alex | 12000003.72 |
| wupeiqi | 99600.00 |
| yuanhao | 42000.00 |
| liwenzhou | 25200.00 |
| jingliyang | 108000.00 |
| jinxin | 360000.00 |
| 成龙 | 120000.00 |
| 歪歪 | 36001.56 |
| 丫丫 | 24004.20 |
| 丁丁 | 12004.44 |
| 星星 | 36003.48 |
| 格格 | 48003.96 |
| 张野 | 120001.56 |
| 程咬金 | 240000.00 |
| 程咬银 | 228000.00 |
| 程咬铜 | 216000.00 |
| 程咬铁 | 204000.00 |
+------------+-------------+
18 rows in set (0.00 sec)
但这么写不好看，可以为字段起别名，你想为哪个字段起别名，就在哪个字段后面来一个as 或者as省了也可以，完整的写法是加as
mysql> select name,salary*12 as annual_salary from emp;
+------------+---------------+
| name | annual_salary |
+------------+---------------+
| egon | 87603.96 |
| alex | 12000003.72 |
| wupeiqi | 99600.00 |
| yuanhao | 42000.00 |
| liwenzhou | 25200.00 |
| jingliyang | 108000.00 |
| jinxin | 360000.00 |
| 成龙 | 120000.00 |
| 歪歪 | 36001.56 |
| 丫丫 | 24004.20 |
| 丁丁 | 12004.44 |
| 星星 | 36003.48 |
| 格格 | 48003.96 |
| 张野 | 120001.56 |
| 程咬金 | 240000.00 |
| 程咬银 | 228000.00 |
| 程咬铜 | 216000.00 |
| 程咬铁 | 204000.00 |
+------------+---------------+
18 rows in set (0.00 sec)
定义显示格式：
字符串拼接 concat(‘名字：’，name)，concat(‘年龄：’，age)
mysql> select concat('名字:',name),concat('年龄:',age) from emp;
+------------------------+-----------------------+
| concat('名字:',name) | concat('年龄:',age) |
+------------------------+-----------------------+
| 名字:egon | 年龄:18 |
| 名字:alex | 年龄:78 |
| 名字:wupeiqi | 年龄:81 |
| 名字:yuanhao | 年龄:73 |
| 名字:liwenzhou | 年龄:28 |
| 名字:jingliyang | 年龄:18 |
| 名字:jinxin | 年龄:18 |
| 名字:成龙 | 年龄:48 |
| 名字:歪歪 | 年龄:48 |
| 名字:丫丫 | 年龄:38 |
| 名字:丁丁 | 年龄:18 |
| 名字:星星 | 年龄:18 |
| 名字:格格 | 年龄:28 |
| 名字:张野 | 年龄:28 |
| 名字:程咬金 | 年龄:18 |
| 名字:程咬银 | 年龄:18 |
| 名字:程咬铜 | 年龄:18 |
| 名字:程咬铁 | 年龄:18 |
+------------------------+-----------------------+
18 rows in set (0.00 sec)
不好看格式起个别名：
mysql> select concat('名字:',name) new_name,concat('年龄:',age) new_age from emp;
+-------------------+-----------+
| new_name | new_age |
+-------------------+-----------+
| 名字:egon | 年龄:18 |
| 名字:alex | 年龄:78 |
| 名字:wupeiqi | 年龄:81 |
| 名字:yuanhao | 年龄:73 |
| 名字:liwenzhou | 年龄:28 |
| 名字:jingliyang | 年龄:18 |
| 名字:jinxin | 年龄:18 |
| 名字:成龙 | 年龄:48 |
| 名字:歪歪 | 年龄:48 |
| 名字:丫丫 | 年龄:38 |
| 名字:丁丁 | 年龄:18 |
| 名字:星星 | 年龄:18 |
| 名字:格格 | 年龄:28 |
| 名字:张野 | 年龄:28 |
| 名字:程咬金 | 年龄:18 |
| 名字:程咬银 | 年龄:18 |
| 名字:程咬铜 | 年龄:18 |
| 名字:程咬铁 | 年龄:18 |
+-------------------+-----------+
18 rows in set (0.00 sec)
或者想这么写，查询结果当中也是名字跟年龄，就一列写名字跟年龄
mysql> select concat(name,':',age) from emp;
+----------------------+
| concat(name,':',age) |
+----------------------+
| egon:18 |
| alex:78 |
| wupeiqi:81 |
| yuanhao:73 |
| liwenzhou:28 |
| jingliyang:18 |
| jinxin:18 |
| 成龙:48 |
| 歪歪:48 |
| 丫丫:38 |
| 丁丁:18 |
| 星星:18 |
| 格格:28 |
| 张野:28 |
| 程咬金:18 |
| 程咬银:18 |
| 程咬铜:18 |
| 程咬铁:18 |
+----------------------+
起别名；
mysql> select concat(name,':',age) as new_info from emp;
+---------------+
| new_info |
+---------------+
| egon:18 |
| alex:78 |
| wupeiqi:81 |
| yuanhao:73 |
| liwenzhou:28 |
| jingliyang:18 |
| jinxin:18 |
| 成龙:48 |
| 歪歪:48 |
| 丫丫:38 |
| 丁丁:18 |
| 星星:18 |
| 格格:28 |
| 张野:28 |
| 程咬金:18 |
| 程咬银:18 |
| 程咬铜:18 |
| 程咬铁:18 |
+---------------+
加一些邮箱的结尾，数据库的迁移就用到这些。
拼名字后面跟年龄年龄后面跟性别可以实现
但是这种以冒号为分隔符的可以有一种简单的写法
concat_ws(':',字段1，字段2....)
在第一个参数指定一个：，后面的字段排着写就好了，会自动以冒号为分隔符来分割
mysql> select concat_ws(':',name,age,sex) as info from emp;
+----------------------+
| info |
+----------------------+
| egon:18:male |
| alex:78:male |
| wupeiqi:81:male |
| yuanhao:73:male |
| liwenzhou:28:male |
| jingliyang:18:female |
| jinxin:18:male |
| 成龙:48:male |
| 歪歪:48:female |
| 丫丫:38:female |
| 丁丁:18:female |
| 星星:18:female |
| 格格:28:female |
| 张野:28:male |
| 程咬金:18:male |
| 程咬银:18:female |
| 程咬铜:18:male |
| 程咬铁:18:female |
+----------------------+
查询过程中可以用到CASE语句
就是类似之前遇到的if判断多分支
满足某个条件干某事，满足某个条件干某事，mysql想要把数据方面的相关操作在数据库里都解决了，应用程序只负责你要调什么功能我数据库给你开个接口，给你接口名，你调我的接口名就可以了，至于数据怎么查你不用管了。
SELECT
(
CASE
WHEN NAME = 'egon' THEN
NAME
WHEN NAME = 'alex' THEN
CONCAT(name,'_BIGSB')
ELSE
concat(NAME, 'SB')
END
) as new_name
FROM
emp;
+--------------+
| new_name |
+--------------+
| egon |
| alex_BIGSB |
| wupeiqiSB |
| yuanhaoSB |
| liwenzhouSB |
| jingliyangSB |
| jinxinSB |
| 成龙SB |
| 歪歪SB |
| 丫丫SB |
| 丁丁SB |
| 星星SB |
| 格格SB |
| 张野SB |
| 程咬金SB |
| 程咬银SB |
| 程咬铜SB |
| 程咬铁SB |
+--------------+
"""
三、where 关键字
必须要有的关键字说完了，现在排着研究，from后面的where 约束条件
select distinct 字段1 from 库名.表名
where 约束条件
1.比较运算符：> < >= >= != 针对数字类型的判断，日期也可以
mysql> select * from emp where id >10 and id <15;
+----+--------+--------+-----+------------+-----------+--------------+----------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | depart_id |
+----+--------+--------+-----+------------+-----------+--------------+----------+-----------+
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 3 |
+----+--------+--------+-----+------------+-----------+--------------+----------+-----------+
2.between and 在什么和什么之间
什么意思呢？
mysql> select *from emp;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 1 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 1 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 1 |
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 1 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 3 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+-----------+
18 rows in set (0.00 sec)
找出来id大于10小于15的这些员工，怎么找？
mysql> select * from emp where id between 10 and 15;
+----+-----------+--------+-----+------------+-----------+--------------+----------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+-----------+
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+-----------+
between and 其实是大于等于10 and 小于等于15.，等于也包含在内
加了where整体怎么运行的呢？
先根据from定位到硬盘上文件在哪，文件的绝对路径，定位到文件的具体位置接下来，把表里所有的内容全都往内存里读，读到内存进行对比，where我要找的大于等于10小于等于15的结果，符合条件的留下，不符合的不要
3.in 在什么里面
找出来id=6 id=9 id=10的
mysql> select * from emp where id=6 or id=9 or id=10;
+----+------------+--------+-----+------------+---------+--------------+---------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | depart_id |
+----+------------+--------+-----+------------+---------+--------------+---------+-----------+
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 2 |
+----+------------+--------+-----+------------+---------+--------------+---------+-----------+
3 rows in set (0.00 sec)
但是这样写假如很多的话，有个简单的写法，可以id in()一个范围
select * from emp where id in(6，9，10)；
这个跟上面的意思是一样的。
mysql> select * from emp where id in(6,9,10);
+----+------------+--------+-----+------------+---------+--------------+---------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | depart_id |
+----+------------+--------+-----+------------+---------+--------------+---------+-----------+
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 2 |
+----+------------+--------+-----+------------+---------+--------------+---------+-----------+
3 rows in set (0.00 sec)
4.like 模糊匹配
% 任意一个字符
查询jin开头的
select *from emp where name like 'jin%';
mysql> select *from emp where name like 'jin%';
+----+------------+--------+-----+------------+---------+--------------+----------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+-----------+
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 1 |
+----+------------+--------+-----+------------+---------+--------------+----------+-----------+
2 rows in set (0.00 sec)
_ 任意单个字符
查询名字有两个字符的
select * from emp where name like '__';
mysql> select * from emp where name like '__';
+----+--------+--------+-----+------------+-----------+--------------+----------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | depart_id |
+----+--------+--------+-----+------------+-----------+--------------+----------+-----------+
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 2 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 2 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 2 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 3 |
+----+--------+--------+-----
5.逻辑运算：and or not
四、group by 关键字
分组
什么是分组？
分组就是按照某一个大家相同的属性进行分类
按照所有记录相同的部分进行归类，一定是区分度低的字段
可以按照性别，分为两类，按照年龄段，按照职位...
为什么要分组？
当我们要以组为单位进行统计时，就必须分组，分组的目的是为了以组为单位进行统计的，再去考虑单条记录毫无意义。
统计每个部门的最高工资，平均工资
有个窍门每后面那个字段就是分组的依据，但不是所有的话都这么说。
注意：分组之后，只能查到分组的字段以及组内多条记录聚合的成果。
聚合函数：
聚合函数一定是分完组才能用
where 里面不能写聚合函数，因为先执行where 那时候还没分组
max
mysql> select post,max(salary) from emp group by post;
+-----------------------------------------+-------------+
| post | max(salary) |
+-----------------------------------------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
+-----------------------------------------+-------------+
4 rows in set (0.00 sec)
min
avg
mysql> select post,avg(salary) from emp group by post;
+-----------------------------------------+---------------+
| post | avg(salary) |
+-----------------------------------------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
+-----------------------------------------+---------------+
4 rows in set (0.00 sec)
sum
mysql> select post,sum(salary) from emp group by post;
+-----------------------------------------+-------------+
| post | sum(salary) |
+-----------------------------------------+-------------+
| operation | 84000.13 |
| sale | 13001.47 |
| teacher | 1062900.31 |
| 老男孩驻沙河办事处外交大使 | 7300.33 |
+-----------------------------------------+-------------+
4 rows in set (0.00 sec)
count
mysql> select post,count(id) from emp group by post;
+-----------------------------------------+-----------+
| post | count(id) |
+-----------------------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+-----------------------------------------+-----------+
4 rows in set (0.00 sec)
mysql> select post,count(post) from emp group by post;
+-----------------------------------------+-------------+
| post | count(post) |
+-----------------------------------------+-------------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 老男孩驻沙河办事处外交大使 | 1 |
+-----------------------------------------+-------------+
4 rows in set (0.00 sec)
求男人数，女人数
mysql> select sex,count(sex) from emp group by sex;
+--------+------------+
| sex | count(sex) |
+--------+------------+
| male | 10 |
| female | 8 |
+--------+------------+
2 rows in set (0.00 sec)
统计出每个部门年龄在30岁以上员工的平均薪资
分析
给你任何的需求之前先去考虑这个查询语句最终是要什么的？---要员工的平均薪资--这个数据在哪些表里有？跟哪个表有关系？
先确定表在哪?
select * from emp ---先占位，至于要查什么不着急，以后慢慢填就好了，先分析
后面有没有where条件？有，每个部门都要过滤出30岁以上的，包括30岁
先做一次整体性的过滤
select * from emp where age >= 30
过滤完以后就剩下30岁以上的人，再分组
按照post分，取post，以及平均薪资avg(salary)
select post,avg(salary) from emp where age >= 30 group by post;
mysql> select post,avg(salary) from emp where age >= 30 group by post;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| sale | 2500.240000 |
| teacher | 255450.077500 |
+---------+---------------+
2 rows in set (0.00 sec)
group_concat
组相关的拼接
需求每个组以及每个组内员工的姓名怎么取？
先求组
select post from emp group by post；
mysql> select post from emp group by post;
+-----------------------------------------+
| post |
+-----------------------------------------+
| operation |
| sale |
| teacher |
| 老男孩驻沙河办事处外交大使 |
+-----------------------------------------+
逗号，不取最大值，平均值这些，取组内的人的姓名
select post，group_concat(name) from emp group by post；
mysql> select post, group_concat(name) from emp group by post;
+-----------------------------------------+---------------------------------------------------------+
| post | group_concat(name) |
+-----------------------------------------+---------------------------------------------------------+
| operation | 张野,程咬金,程咬银,程咬铜,程咬铁 |
| sale | 歪歪,丫丫,丁丁,星星,格格 |
| teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 |
| 老男孩驻沙河办事处外交大使 | egon |
+-----------------------------------------+---------------------------------------------------------+
4 rows in set (0.00 sec)
五、having关键字
where 是分组之前的过滤，即在分组之前做了一次整体性的筛选
having是在分组之后的过滤，即在分组之后专门针对聚合的结果进行进一步的筛选
where 后面不能跟聚合函数，而having可以
取出来平均薪资在10000以上的部门
select post,avg(salary) from emp group by post having avg(salary) >= 10000；
mysql> select post from emp group by post having avg(salary)>=10000;
+-----------+
| post |
+-----------+
| operation |
| teacher |
+-----------+
2 rows in set (0.00 sec)
简单拼接：
mysql> select post,avg(salary) from emp group by post having avg(salary) >= 10000;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------+---------------+
2 rows in set (0.00 sec)
查询各岗位内包含的员工个数小于2的岗位名，岗位内包含员工名字，个数
select post,group_concat(name),count(id) from emp group by post having count(id)<2;
mysql> select post,group_concat(name),count(id) from emp group by post having count(id)<2;
+-----------------------------------------+--------------------+-----------+
| post | group_concat(name) | count(id) |
+-----------------------------------------+--------------------+-----------+
| 老男孩驻沙河办事处外交大使 | egon | 1 |
+-----------------------------------------+--------------------+-----------+
1 row in set (0.00 sec)
查询各岗位平均薪资大于10000的岗位名、平均薪资
mysql> select post,avg(salary) from emp group by post having avg(salary) >= 10000;
+-----------+---------------+
| post | avg(salary) |
+-----------+---------------+
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------+---------------+
2 rows in set (0.00 sec)
查询各岗位平均薪资大于10000且小于20000的岗位名、平均薪资
mysql> select post , avg(salary) from emp group by post having avg(salary) between 10000 and 20000;
+-----------+--------------+
| post | avg(salary) |
+-----------+--------------+
| operation | 16800.026000 |
+-----------+--------------+
1 row in set (0.00 sec)
六、order by 关键字
运行 order by 一定是分完组之后的 可以用聚合函数
select * from emp order by age;
mysql> select * from emp order by age;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 1 |
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 1 |
| 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 2 |
| 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 2 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 3 |
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 1 |
| 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 2 |
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 3 |
| 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 2 |
| 8 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 1 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 2 |
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 1 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 1 |
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 1 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+-----------+
18 rows in set (0.00 sec)
默认是升序：从小到大
select * from emp order by age asc;
省略了
降序是select * from emp order by age desc;
从大到小
年龄重复的情况下升序排，在年龄分不出胜负的情况下逗号可以按照薪资降序或者升序排
by 后面可以有多个排序依据
select * from emp order by age asc,salary desc;
查出每个部门的平均工资，按照平均工资排序
mysql> select post ,avg(salary) from emp group by post order by avg(salary) asc;
+-----------------------------------------+---------------+
| post | avg(salary) |
+-----------------------------------------+---------------+
| sale | 2600.294000 |
| 老男孩驻沙河办事处外交大使 | 7300.330000 |
| operation | 16800.026000 |
| teacher | 151842.901429 |
+-----------------------------------------+---------------+
4 rows in set (0.00 sec)
七、limit 关键字
限制显示的条数
语法级别和执行角度都是最后
limit 3；表示只显示3条
求出薪资最高的那个人的详细信息
select * from emp order by salary desc limit 1;
mysql> select * from emp order by salary desc limit 1;
+----+------+------+-----+------------+---------+--------------+------------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+-----------+
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+-----------+
1 row in set (0.00 sec)
可以做分页显示
limit 0，5;从0开始，往后查5条
在往后查是
limit 5，10; 从5开始不包含5，往后取10条
起始位置公式 (第几页-1)*条数
八、正则表达式
之前模糊查询 like 比较局限 % _ 关于字符匹配，正则表达式最强大
怎么用正则？
select * from emp where name regexp；
代表调正则表达式，之前学过的正则表达式都可以用。
select * from emp where name regexp "^jin.*(g|n)$";
mysql> select * from emp where name regexp "^jin.*(g|n)$";
+----+------------+--------+-----+------------+---------+--------------+----------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+-----------+
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 1 |
+----+------------+--------+-----+------------+---------+--------------+----------+-----------+
2 rows in set (0.00 sec)
扫一扫
1044
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
