mysql单多条件查询
# ### part1 单表查询
# sql 查询语句的完整语法
''' select .. from .. where .. group by .. having .. order by .. limit .. '''
# ⼀.where 条件的使⽤
"""功能：对表中的数据进⾏筛选过滤"""
"""
语法:
1.判断的符号:
= > < >= <= != <> 不等于
2.拼接条件的关键字
and or not
3.查询的区间范围值 between
between ⼩值 and ⼤值 [⼩值,⼤值] 查询两者之间这个范围的所有数据
4.查询具体某个值的范围 in
in(1,-9,-10,"a") 指定范围
5.模糊查询 like "%" 通配符
like "%a" 匹配以a结尾的任意长度的字符串
like "a%" 匹配以a开头的任意长度的字符串
like "%a%" 匹配含有a字母的任意长度字符串
like "_a" 个数⼀共2个字符，必须以a结尾，前⾯这个字符随意
like "a__" 个数⼀共3个字符，必须以a开头，后⾯这个两字符随意
"""
# (1) 单条件的查询
# 查询部门是sale的所有员⼯姓名:
select emp_name from employee where post = "sale";
# (2) 多条件的查询
# 部门是teacher，收⼊⼤于10000的所有数据
select * from employee where post = "teacher" and salary > 10000;
# (3) 关键字between .. and
# 收⼊在1万到2万之间的所有员⼯姓名和收⼊
select emp_name,salary from employee where salary between 10000 and 20000;
# 收⼊不在1万到2万之间的所有员⼯姓名和收⼊
select emp_name,salary from employee where salary not between 10000 and 20000;
# (4) null关键字在搜索的时候，要⽤is进⾏判定，不能⽤=
# 查询 post_comment 是空的NULL 所有数据
select * from employee where post_comment = NULL 数据是空，搜索不到
select * from employee where post_comment is NULL
select * from employee where post_comment is not NULL
update employee set post_comment = "" where id = 1
select * from employee where post_comment = '';
# (5) 关键字 in 的查询
# 查询收⼊是 3000 或 5000 或者 4000 或者 8000 所有员⼯姓名和收⼊
select emp_name,salary from employee where salary=3500 or salary=5000 or salary=8300 or salary=4000; # ⽤in优化，在⼩括号⾥⾯写上可能的值
select emp_name,salary from employee where salary in (3500,5000,8300,4000);
# 不在括号中的值，搜索出来
select emp_name,salary from employee where salary not in (3500,5000,8300,4000);
# (6) 关键字 like 模糊查询
# (1) % 通配符
select emp_name,age,post from employee where emp_name like "%on";
# (2) _ 通配符
select emp_name,age,post from employee where emp_name like "a_e_";
# (7) concat
select concat("姓名:",emp_name,"薪资:",salary) as aaa from employee;
# concat_ws(拼接的符号，参数1，参数2，参数3 ... )
select concat_ws(" : ",emp_name,salary) as bbb from employee;
# 可以在sql中使⽤四则运算(+ - * /)
select concat_ws(" : ",emp_name, salary * 12 ) as bbb from employee;
# ⼆.group by ⼦句分组，分类
"""group by 对数据进⾏分类, by 后⾯接的字段，就是select要搜索的字段"""
select sex from employee group by sex;
select post from employee group by post;
# group_concat 按照分组形式进⾏字段的拼接
select group_concat(emp_name),post from employee where id>1 group by post;
# 聚合函数
# 统计总数 count *所有
select count(*) from employee
# 统计最⼤值 max
select max(salary) from employee
# 统计最⼩值 min
select min(salary) from employee
# 统计平均值 avg
select avg(salary) from employee
# 统计总和 sum
select sum(salary) from employee
# ⼀般来说使⽤时分组 + 聚合函数配合使⽤
# 1. 查询部门名以及各部门的平均薪资
select post , avg(salary) from employee group by post;
# 2. 查询部门名以及各部门的最⾼薪资
select post , max(salary) from employee group by post;
# 3. 查询部门名以及各部门的最低薪资
select post , min(salary) from employee group by post;
# 4. 查询公司内男员⼯和⼥员⼯的个数
select sex,count(*) from employee group by sex
# 5. 查询部门名以及部门包含的所有员⼯名字
select group_concat(emp_name) , post from employee group by post
select emp_name,post from employee group by post,emp_name
# 三.having 查询数据之后在进⾏过滤,⼀般是配合group by使⽤, 主要⽤分组后过滤
# 出各部门的平均薪资，并且⼤于10000以上的所有部门
select post,avg(salary) from employee group by post having avg(salary) > 10000;
# 1.查询各岗位内包含的员⼯个数⼩于2的岗位名，员⼯名，个数
select post,group_concat(emp_name),count(*) from employee group by post having count(*) < 2
# 2.查询各岗位平均薪资⼩于10000的岗位名、平均⼯资
select post,avg(salary) from employee group by post having avg(salary) < 10000
# 3.查询各岗位平均薪资⼤于10000且⼩于20000的岗位名、平均⼯资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000 select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000 # 四.order by 排序 , 按照什么字段进⾏排序
# 默认值asc 升序排序
# 按照desc 降序排序
select * from employee order by age (默认升序)
select * from employee order by age desc (降序)
# 1. 查询所有员⼯信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select emp_name,sex,age,hire_date,post from employee order by age,hire_date desc
# 2. 查询各岗位平均薪资⼤于10000的岗位名、平均⼯资，结果按平均薪资降序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc # 3. 查询各岗位平均薪资⼤于10000的岗位名、平均⼯资，结果按平均薪资升序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc # 五.limit 限制查询的条数 (数据分页)
limit m,n m代表从第⼏条开始查询,n代表查询⼏条 m=0 代表的是第⼀条
select * from employee limit 0,5 从第⼀条开始查，查5条
select * from employee limit 5,5 从第六条开始查，查5条
# 只查询⼀条数据
select * from employee limit 1
# 想要瞬间得到数据表中，最后⼀条数据
select * from employee order by id desc limit 1
# 拿到最后三条数据
select * from employee order by id desc limit 3
# 六.(了解) 可以使⽤正则表达式查询数据 (不推荐使⽤，不好⽤效率不⾼)
select * from employee where emp_name regexp ".*on$" # .*? 的?号不识别
select * from employee where emp_name regexp "^程";
select * from employee where emp_name regexp "^程.*⾦";
# ### part2 多表查询
# 内连接:(内联查询 inner join ) : 两表或者多表满⾜条件的所有数据查询出来[两个表之间共同具有的数据] """
# 两表查询
select 字段 from 表1 inner join 表2 on 条件
# 多表查询
select 字段 from 表1 inner join 表2 on 条件 inner join 表3 on 条件
"""
# 基本语法 inner join on 接的表与表之间的必要连接条件
select * from employee inner join department on emplinoyee.dep_id = department.id
# ⽤as 起别名 (推荐)
select * from employee as e inner join department as d on e.dep_id = d.id
# 可以省略as
select * from employee e inner join department d on e.dep_id = d.id
# where 实现的就是内联查询
select * from employee,department where employee.dep_id = department.id
select * from employee as e,department as d where e.dep_id = d.id
sql需要考证吗# 外连接
# (1) 左连接 (左联查询 left join ) : 以左表为主，右表为辅，完整查询左表所有数据，右表没有的数据补NULL """ select 字段 from 表1 left join 表2 on 条件 """
select * from employee left join department on employee.dep_id = department.id
# (2) 右连接 (右联查询 right join) : 以右表为主，左表为辅，完整查询右表所有数据，左表没有的数据补NULL """ select 字段 from 表1 right join 表2 on 条件 """
select * from employee right join department on employee.dep_id = department.id
常用数据库软件# (3) 全连接 (union) 所有数据全都合并起来
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
# ### part3 ⼦查询
"""
⼦查询: 嵌套查询
(1) ⼦查询是查询的语句当中⼜嵌套的另外⼀条sql语句,⽤括号()抱起来，表达⼀个整体
(2) ⼀般应⽤在from ⼦句后⾯表达⼀张表，或者 where ⼦句后⾯表达⼀个条件
(3) 速度从快到慢单表查询速度最快 -> 联表查询 -> ⼦查询
"""
# (1)出平均年龄⼤于25岁以上的部门
# 普通的where 相当于内联查询
select
d.id,d.name
from
employee e,department d
where
mysql语句多表查询e.dep_id = d.id
group by
d.id,d.name
having
avg(e.age) > 25;
# (2) inner join
select
咨询论坛网站源码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
刀客面试源码大全
select dep_id from employee group by dep_id having avg(age) > 25;
# 2.通过部门id，部门名字
select name from department where id in (201,202)
# 3.综合拼接:
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25) # (2)查看技术部门员⼯姓名
# 1.普通where查询
select
e.name
from
employee e ,department d
where
e.dep_id = d.id and d.name = "技术"
# 2.inner join 实现
select
e.name
from
java语言的主要贡献者是employee e inner join department d on e.dep_id = d.id
where
d.name = "技术"
# 3.⼦查询
# 1.技术部门对应id
select id from department where name = "技术"
# 2.通过id员⼯姓名
select name from employee where employee.dep_id = ?
# 3.综合拼接
select name from employee where employee.dep_id = (select id from department where name = "技术")
# (3)查看哪个部门没员⼯
# 联表写法
select
d.id,d.name
from
employee e right join department d on e.dep_id = d.id
where
e.dep_id is NULL
# ⼦查询
# 1.先查询，员⼯都在哪些部门
select dep_id from employee group by dep_id => (200,201,202,204)
# 2.把不在部门列表中的数据出来
select from department where id not in (1)
# 3.综合拼接
select id,name from department where id not in (select dep_id from employee group by dep_id)
# (4)查询⼤于平均年龄的员⼯名与年龄
# 假设平均年龄是18岁
select name,age from employee where age > ?
# 平均年龄
select avg(age) from employee
# 综合拼装
select name,age from employee where age > (select avg(age) from employee)
# (5)把⼤于其本部门平均年龄的员⼯名和姓名查出来
# employee
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id || dep_id | avg(age) |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
# department
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | ⼈⼒资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
# 1.先计算平均年龄
select dep_id,avg(age) from employee group by dep_id
+--------+----------+
| dep_id | avg(age) |
+--------+----------+
| 200 | 18.0000 |
| 201 | 43.0000 |
| 202 | 28.0000 |
| 204 | 18.0000 |
+--------+----------+
# 2.把⼦查询查出来的数据和employee作拼接，联合成⼀张更⼤的表，做⼀次单表查询;
select
*
from
employee as t1 inner join (1) as t2 on t1.dep_id = t2.dep_id
# 3.综合拼接
select
*
from
employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id # 4.把额外的⽐较的条件加进去
select
*
from
employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id where
t1.age > t2.avg_age
# (6)查询每个部门最新⼊职的那位员⼯ # 利⽤上⼀套数据表进⾏查询;
# 1.每个部门最⼤的⼊职时间
select post,max(hire_date) as max_date from employee group by post
# 2.把⼦查询查出来的数据和employee联合成⼀张更⼤的表，做⼀次单表查询
select
from
employee as t1 inner join (1) as t2 on t1.post = t2.post
where
t1.hire_date = t2.max_date
# 3.综合拼接
select

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。