mysql中运⽤条件判断筛选来获取数据
### part1 单表查询
sql查询完整语法:
lect .. from .. where .. group by .. having .. order by .. limit ..⼀.where 条件的使⽤
"""功能：对表中的数据进⾏过滤筛选"""
"""
语法:
1.判断条件的符号
= > < >= <= != <>(不等于)
2.拼接条件关键字
and or not
3.查询区间范围值
between ⼩值 and ⼤值 [⼩值,⼤值] 查找两者之间的范围
4.查找某个具体范围值
in(值1，值2，值3) 在括号⾥这个范围内查询
5.模糊查询 like '%' 通配符
like '%a' 匹配以a结尾的任意长度的字符串
like 'a%' 匹配以a开头的任意长度的字符串
like '%a%' 匹配字符串中含有a字符的字符串
like '_a' ⼀共是2个长度，以a结尾，前⾯那个字符是什么⽆所谓
like 'a__' ⼀共是3个长度，以a开头，后⾯是什么字符⽆所谓
"""
# (1) 单条件查询:
# 查询部门是sale的所有员⼯姓名:
lect emp_name from employee where post = 'sale';
# (2) 多条件查询
# 部门是teacher，并且收⼊⼤于10000的所有数据
lect * from employee where post = "teacher" and salary > 10000 ;
# (3) 关键 between .. and
# 收⼊在1万到2万之间的所有姓名和收⼊
lect emp_name,salary from employee where salary between 10000 and 20000;
# 收⼊不在1万到2万之间的所有姓名和收⼊
lect emp_name,salary from employee where salary not between 10000 and 20000;
# (4) 关键字is null (判断某个字段是不是null , 不能⽤等号, 只能⽤is)
# 查询 post_comment 是空的 null
lect emp_name,salary,post_comment from employee where post_comment is null;
# 表达 post_comment 不是空的 is not null
lect emp_name,salary,post_comment from employee where post_comment is not null;
lect emp_name,salary,post_comment from employee where post_comment = null;
# 设置⼀个值是空的
update employee t post_comment = '' where id = 3;
lect emp_name,salary,post_comment from employee where post_comment = '';
# (5) 关键字in的查询
# 查收⼊是 3000 或者 2500 或者 4000 或者9000的所有员⼯和收⼊.
lect emp_name,salary from employee where salary=3000 or salary=2500 or salary=4000 or salary=9000;
# 优化: 在当前括号⾥⾯查找
lect emp_name,salary from employee where salary in(3000,2500,4000,9000);
# 在这个范围⽤in ，不在这个范围⽤not in
lect emp_name,salary from employee where salary not in(3000,2500,4000,9000,3000.13);
# (6) 关键字like 模糊查询
# 1. 通配符 %
lect * from employee where emp_name like "%on";
# 2. 通配符 _
lect * from employee where emp_name like "a_e_";
# (7) concat sql内置函数 concat(参数1，参数2，参数3) 把所有参数拼接在⼀起
# as ⽤来起别名
lect emp_name,concat("姓名:",emp_name,"薪⽔:",salary) as ss from employee;
# concat_ws("符号"，参数1，参数2，参数3) 第⼀个参数是分隔符，后⾯写上要拼接的参数
lect emp_name,concat_ws(" : ",emp_name,salary) as aa from employee;
# 在sql中可以做四则运算(+ - * /)
lect emp_name,concat_ws(" : ",emp_name,salary*12) as aa from employee;
⼆.group by 分组:
"""group by 分组分类 by 后⾯的字段⼀般是lect 后⾯要搜索的字段"""
lect post from employee where depart_id > 1 group by post
# group_concat 按照分组的形式拼接字段
lect group_concat(emp_name),post from employee where depart_id > 1 group by post;
# 聚合函数:
# 统计总数 count *代表所有.
lect count(*) from employee;
# 统计最⼤值 max
lect max(salary) from employee;
# 统计最⼩值 min
lect min(salary) from employee;
# 统计平均值 avg
lect avg(salary) from employee;
# 统计总和sum
lect sum(salary) from employee;
# ⼀般来说分组 + 聚合函数在⼀起使⽤
# 求各部门的平均⼯资
lect post,avg(salary) from employee group by post
lect depart_id,avg(salary) from employee group by depart_id
# 查询部门名以及各部门的最⾼薪资
lect post,max(salary) from employee group by post
# 查询公司内男员⼯和⼥员⼯的个数
lect x,count(*) from employee group by x
# 查询部门名以及部门包含的所有员⼯名字
lect post,emp_name from employee group by post,emp_name
lect group_concat(emp_name),post from employee group by post;
三.having 查询数据之后在进⾏过滤 , ⼀般是配合group by 使⽤，主要⽤于分组之后在过滤
# ⽐如：求各个部门平均薪资，找出平均薪资⼤于10000以上的所有部门
lect post,avg(salary) from employee group by post having avg(salary) > 10000;
# 1.查询各岗位内包含的员⼯个数⼩于2的岗位名、岗位内包含员⼯名字、个数
lect post,count(*),group_concat(emp_name) from employee group by post having count(*) < 2
# 2.查询各岗位平均薪资⼩于10000的岗位名、平均⼯资
lect post,avg(salary) from employee group by post having avg(salary) < 10000;
# 3.查询各岗位平均薪资⼤于10000且⼩于20000的岗位名、平均⼯资
(1)lect post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000
(2)lect post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
四.order by 按照什么字段排序
# 默认升序asc 从⼩到⼤排序
lect emp_name,age,post from employee order by age
lect emp_name,age,post from employee order by age asc
# 倒序 desc 从⼤到⼩排序
lect emp_name from employee where post ="teacher" order by age desc
# 1. 查询所有员⼯信息，先按照age升序排序，如果age相同则按照hire_date降序排序
lect * from employee order by age asc , hire_date desc
# 2. 查询各岗位平均薪资⼤于10000的岗位名、平均⼯资，结果按平均薪资升序排列
lect post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;
# 3. 查询各岗位平均薪资⼤于10000的岗位名、平均⼯资，结果按平均薪资降序排列
lect post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
五.limit 限制查询的条数[⽤于做数据分页]
# limit m,n 默认m值是0 代表第⼀条数据,n所代表的是查询⼏条，从m+1条件开始，查询n条数据
lect * from employee limit 0,5
# 从第6条，继续往下搜，搜5条数据.
lect * from employee limit 5,5
# 从第11条，继续往下搜，搜5条数据.
lect * from employee limit 10,5
# 查询最后⼀条数据 limit ⼀个参数，就是查询⼏条的意思.
lect * from employee order by id desc limit 1
lect * from employee order by id desc limit 3
六.使⽤正则表达式查询数据(了解，不好⽤，查询速度慢，部分结果
与python 不⼀致)
lect * from employee where emp_name regexp 'on$';
lect * from employee where emp_name regexp '^程';
lect * from employee where emp_name regexp '程.*⾦'; # .*? 这⾥的?号识别不了
### part2 多表查询
# 内连接(内联查询 inner join ) : 两表或者多表满⾜条件的数据查询出来 [表与表之间都有的部分会查出来] """
语法:
双表的内联:lect 字段 from 表1 inner join 表2 on 条件
多表的内联:lect 字段 from 表1 inner join 表2 on 条件 inner join 表3 on 条件 ... ...
"""
# 基本写法:
lect * from employee inner join department on employee.dep_id = department.id
# ⽤as 起别名
lect * from employee as e inner join department as d on e.dep_id = d.id
# as 也可以省略
lect * from employee e inner join department d on e.dep_id = d.id
# ⽤普通的where 条件来进⾏查询默认使⽤的内联⽅式
lect * from employee,department where employee.dep_id = department.id
lect * from employee as e,department as d where e.dep_id = d.id
# 外连接
#(1)左连接(左联查询 left join): 以左表为主，右表为辅，完整查询左表数据，右表没有的数据补null """lect 字段 from 表1 left join 表2 on 条件"""
lect * from employee left join department on employee.dep_id = department.id
#(2)右链接(右联查询 right join)：以右表为主，左表为辅，完整查询右表数据，左表没有的数据补null """lect 字段 from 表1 right join 表2 on 条件"""
lect * from employee right join department on employee.dep_id = department.id
#(3)全连接(union)
lect * from employee left join department on employee.dep_id = department.id
union
lect * from employee right join department on employee.dep_id = department.id
# 例⼦1：找出年龄⼤于25岁的员⼯姓名及所在部门名字
# 内联:
lect
employee.id , employee.name as en , department.name as dn
from
employee inner join department on employee.dep_id = department.id
where
age > 25
# 例⼦2：查询employee 和 department 关联数据，以age字段升序排序
# 内联
lect
*
from
employee inner join department on employee.dep_id = department.id
order by
age desc
# where写法:
lect *
from
employee,department
where
employee.dep_id = department.id
order by
age desc
### part3 ⼦查询
"""
⼦查询：嵌套查询
1.⼦查询是讲⼀个查询语句嵌套在另外⼀个查询语句之中,⽤括号()抱起来，表达⼀个整体
2.⼀般应⽤在from 或者 where 或者字段中 .⼦查询这个整体可以作为表，也可以作为where 后⾯条件表达式
3. 速度从快到慢: 单表查询速度最快 > 其他是联表操作 > ⼦查询
"""
# (1)找出平均年龄⼤于25岁以上的部门
# 1.普通的where 联表查询
lect
d.name ,d.id
from
employee e ,department d
where
e.dep_id = d.id
group by
d.id,d.name
having
avg(e.age) > 25
# 2.内联查询
lect
d.id,d.name
from
employee e inner join department d on e.dep_id = d.id
group by
d.id,d.name
having
avg(e.age) > 25
# 3.⼦查询
# 1.先选出平均年龄⼤于25岁的部门id
lect dep_id from employee group by dep_id having avg(age) > 25;
# 2.根据结果，选出在这个范围中的数据
lect name from department where id in(201,202)
# 综合拼接:
lect
name
from
department
where
id in(lect dep_id from employee group by dep_id having avg(age) > 25)
# (2)查看技术部门员⼯姓名
# 1.普通的where 联表查询
lect
e.name
from
employee e ,department d
where
e.dep_id = d.id and d.name = "技术"
# 2.内联查询
lect
e.name
from
employee e inner join department d on e.dep_id = d.id
where
d.name = "技术";
# 3.⼦查询
# 1.找技术部门对应的id是谁
lect id from department where name = "技术"
# 2.通过id找员⼯姓名
lect name from employee dep_id = ?
# 3.综合拼接
lect name from employee where dep_id = (lect id from department where name = "技术") # (3)查看哪个部门没员⼯
# 右联⽅法:
lect
d.name
from
employee e right join department d on e.dep_id = d.id
where
e.dep_id is null
# ⼦查询:
# (1) 先查询员⼯都在哪些部门
lect dep_id from employee group by dep_id
# (2) 把不在部门列表中的这个部门找出来
lect id from department where id not in ?
# 综合拼接
lect id from department where id not in(lect dep_id from employee group by dep_id)
# (4)查询⼤于平均年龄的员⼯名与年龄
# 假如平均年龄是28岁
lect * from 表 where age > 28
# 找平均年龄
lect avg(age) from employee
# 综合拼接
lect name,age from employee where age > (lect avg(age) from employee)
# (5)把⼤于其本部门平均年龄的员⼯名和姓名查出来
lect * from employee
+----+------------+--------+------+--------+
| id | name | x | age | dep_id | avg(age)
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 | 10
| 2 | alex | female | 48 | 201 | 11
| 3 | wupeiqi | male | 38 | 201 | 11
| 4 | yuanhao | female | 28 | 202 | 13
| 5 | liwenzhou | male | 18 | 200 | 10
| 6 | jingliyang | female | 18 | 204 | 15
+----+------------+--------+------+--------+
#(1) 先计算各个部门平均年龄
lect dep_id,avg(age) from employee group by dep_id;
#(2) 让⼦查询单独作为⼀张临时表和employee联表变成⼀张更⼤的表
lect *
from
employee t1 inner join
(lect dep_id,avg(age) age from employee group by dep_id) as t2
on t1.dep_id = t2.dep_id
#(3) 让这张⼤表，当成⼀次单表查询;
lect *
from
employee t1 inner join
(lect dep_id,avg(age) as age from employee group by dep_id) as t2
on t1.dep_id = t2.dep_id
where
t1.age > t2.age
# (6)查询每个部门最新⼊职的那位员⼯ # 利⽤上⼀套数据表进⾏查询;
"""
# 每个部门都对应很员⼯，每个员⼯都对应⼀个⼊职时间，如果这时间最⼤就代表放⼊职
"""
# 找每个部门的最⼤⼊职时间
lect post,max(hire_date) from employee group by post;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | emp_name | x | age | hire_date | post | post_comment | salary | office | depart_id | max时间
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 1 | egon | male | 18 | 2017-03-01 | ⽼男孩驻沙河办事处外交⼤使 | NULL | 7300.33 | 401 | 1 | 1
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | 2
| 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | | 8300.00 | 401 | 1 | 3
| 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | 4
| 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | 5
| 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 | 成龙 | 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 |
| 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 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ """
先把需要连接的字段查出来(最⼤值)，然后在和旧表连接成想要的新表数据，从这个新的⼤表当中，搜索想要的字段. """
lect
*
from
employee as t1 inner join
(lect post,max(hire_date) as max_date from employee group by post) as t2
on t1.post = t2.post
where
t1.hire_date = t2.max_date
