MySQL-单表查询_多表查询_子查询_关键字in any all exists的使用

最新推荐文章于 2023-06-02 16:07:05 发布

I believe I can fly~

最新推荐文章于 2023-06-02 16:07:05 发布

阅读量700

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/qq_31455841/article/details/108685115

版权

MySQL 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

1. 单表查询

# sql 查询语句的完整语法
select .. from .. where .. group by .. having .. order by .. limit ..

1. where 条件的使用

# 功能: 对表中的数据进行过滤筛选
'''
1. 判断的符号
	= > >= < <= != <>
2. 拼接条件的关键字
	and or not
3. 查询范围区间 between
	between 小值 and 大值 [小值,大值] 查询两者之间的这个范围内所有数据
4. 查询某个值在具体某个范围内 in
	in(1,2,3,4)
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 进行判定,不能使用 =
select * from employee where post_comment = null;
select * from employee where post_comment = '';
select * from employee where post_comment is null;
select * from employee where post_comment is not null;

# 5. in 在 ... 之中  not .. in .. 不在 ... 之中
# 查询收入是 3000 ,4000,5000 ,8300所有原型的姓名和收入
select emp_name,salary from employee where salary in (3000,4000,5000,8300);
select emp_name,salary from employee where salary = 3000 or salary=4000 or salary=5000 or salary=8300;
select emp_name,salary from employee where salary not in (3000,4000,5000,8300);

# 6. 模糊匹配 like '%' '_' 通配符
# 匹配员工姓名 以on结尾的
select emp_name from employee where emp_name like '%on';
# "_"通配符 限定字符长度使用_
select emp_name from employee where emp_name like 'a_e_';

# 7. concat (as 起别名)
select concat('姓名: ',emp_name,'工资: ',salary) as ceshi from employee;
# concat_ws(拼接的符号, 参数1,参数2,参数3 .. .. )
select concat_ws(' : ',emp_name,salary) as ceshi from employee;
# 计算每个人的年薪 可以在mysql使用四则运算(+ - * /)
select concat_ws(' : ',emp_name,salary * 12) as ceshi001 from employee;

2. 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) from employee 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 post,group_concat(emp_name) from employee group by post;
# 6. 可以 group by 两个字段,搜索两个字段
select post,emp_name from employee group by post,emp_name;

3. 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) > 10000 and avg(salary) < 20000;

4. order by 排序

# 按照什么字段进行排序
# asc  升序: 从小到大 (默认)
# desc 降序: 从大到小
select * from employee order by age # (默认是 asc 升序)
select * from employee order by age desc # (默认是 desc 倒序)

# 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * 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);
# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;

5. limit 限制查询条数

# 可用于数据分页
# limit m,n m代表从第几条数据进行查询,0代表第一条,n代表的查询几条
select * from employee limit 0,5; # 从第一条数据开始搜索,搜5条数据
select * from employee limit 5,5; # 从第六条数据开始搜索,搜5条数据

# 只搜索一条数据
select * from employee limit 1;
# 只搜索三条数据
select * from employee limit 3;
# 搜索这个表中的最后一条数据
select * from employee order by id desc limit 1

6. 正则表达式查询数据

select * from employee where emp_name regexp '.*on$' # .*?这个 ? 号 mysql不识别
select * from employee where emp_name regexp '程';
select * from employee where emp_name regexp '程.*金';

2. 多表查询

# 内连接: (inner join) -> 两表或者多表满足条件的所有数据查询出来(两表之间的共有数据)
# 内连接: 保留两张表有对应关系的记录

'''
两表查询
select 字段 from 表1 inner join 表2 on 必要的关联条件
多表查询
select 字段 from 表1 inner join 表2 on 必要的关联条件1 inner join 表3 on 必要的关联条件2 ... 
'''

# 基本语法 inner join on + 条件
select * from employee inner join department on employee.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;

# 外连接: 

# 1. 左连接(左联查询 left join) 以左表为主,右表为辅,完整查询左表所有数据,右表没有的补 null
# 在内链接的基础上保留左表的记录
select * from employee left join department on employee.dep_id = department.id;
# 2. 右连接(右联查询 right join) 以右表为主,左表为辅,完整查询右表所有数据,左表没有的补 null
# 在内链接的基础上保留右表的记录
select * from employee right join department on employee.dep_id = department.id;
# 3. 全连接: 在内链接的基础上保留左右表的记录
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;

3. 子查询

1. 子查询: 嵌套查询

1. sql 语句当中又嵌套了另外一条 sql 语句 , 用 () 括号包起来 , 来表达一个整体

2. 一般应用在 from 字符后面 (表达一张表) , where 子句后面 (表达一个条件)

3. 查询速度从快到慢 : 单表查询 -> 联表速度 -> 子查询

# 一. 找出平均年龄大于25岁以上的部门
# 1. 普通 where
select 
	d.id,d.name
from 
	employee as e,department as d
where 
	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 as e inner join department as d on e.dep_id = d.id
group by
	d.id,d.name
having
	avg(e.age) > 25;
    
# 3. 子查询
# 先选出平均年龄大于25岁的部门id
select dep_id from employee group by dep_id having avg(age) > 25;
# 通过部门 id,找部门的名字
select name from department where id in (201,202);
# 综合拼接
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);

# 二. 查看技术部门员工姓名
# 1. 普通 where
select 
	e.name,d.name
from
	employee as e,department as d
where
	e.dep_id = d.id
	and
	d.name = '技术'

# 2. inner join 写法
select 
	e.name,d.name
from 
	employee as e inner join department as d on e.dep_id  = d.id
where
	# 非必要条件写在where子句中
	d.name = '技术'

# 3. 子查询
# 先找技术部门对应的id
select id from department where name = '技术'
# 通过id找员工姓名
select name from employee where dep_id = 200;
# 综合拼接
select name,dep_id from employee where dep_id = (select id from department where name = '技术');

# 三. 查看那个部门没员工
# 联表产生null值,谁是null谁就没有员工
select 
	d.id,d.name
from 
	department as d left join employee as e on e.dep_id = d.id
where
	e.id is null    
    
# 子查询
department
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+
employee
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |    18
|  2 | alex       | female |   48 |    201 |    43
|  3 | wupeiqi    | male   |   38 |    201 |    43
|  4 | yuanhao    | female |   28 |    202 |    28
|  5 | liwenzhou  | male   |   18 |    200 |    18
|  6 | jingliyang | female |   18 |    204 |    18
+----+------------+--------+------+--------+

# 1. 查询员工都在哪些部门 (200,201,202,204)
select dep_id from employee group by dep_id
# 2. 把不在这些部门的数据找出来
select id from department where id not in (200,201,202,204)
# 3. 综合拼接
select id,name from department where id not in (select dep_id from employee group by dep_id);

# 四. 查询大于平均年龄的员工名与年龄
# 如果平均年龄是25
select name,age from employee where age > 25
# 计算平均年龄
select avg(age) from employee
# 综合拼接
select name,age from employee where age > (select avg(age) from employee);

# 五. 把大于其本部门平均年龄的员工名和姓名查出来
# 先计算各部门平均年龄是多少
select dep_id,avg(age) as avg_age from employee group by dep_id # 一号数据
# 把查询各部门的平均年龄和过去employee做联表,变成更大的表方便后期做单表查询
select 
	*
from
	employee as t1 inner join (一号数据) as t2 on t1.dep_id = t2.dep_id
# 综合拼接
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
# 做最后的条件筛选
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
    
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | emp_name   | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id | max_date
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         |    7300.33 |    401 |         1 | 2038-1-1
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 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 |
|  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 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+

# 六.查询每个部门最新入职的那位员工  利用上一套数据表进行查询;
# 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
    
# 综合拼接
select
	t1.emp_name,t1.hire_date
from 
	employee as t1 inner join (select 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
  
# 七. 带EXISTS关键字的子查询
'''
EXISTS 关键字,表达存在
	如果内层sql 能够查询到数据 返回True  外层sql执行查询语句
	如果内层sql 不能查询到数据 返回False 外层sql不执行查询语句
'''

select * from employee where exists (select * from employee where id = 100);

'''
子查询总结:
	子查询可以单独作为一个临时数据表,临时数据,临时字段
	一般用在 from where select 子句后面
	可以通过查询出来的临时数据和另外的表做一次联表,变成更大的表,然后再做但标题查询,以得到想要的结果
'''

# 额外 distinct 去重[尝试操作]
select distinct depart_id from employee;

2. 关键字 in

# 1、in
select * from emp where age=18 or age=38 or age=28;
select * from emp where age in (18,38,28);

# 子查询方式
select 
	* 
from 
	emp 
where 
	dep_id in (select id from dep where name = '技术' or name = '销售');

# 联表方式
select 
	* 
from 
	emp inner join dep on emp.dep_id = dep.id 
where 
	dep.name in ('技术' , '销售');

# 练习
mysql> select * from dep;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技术         |
|  201 | 人力资源     |
|  202 | 销售         |
|  203 | 运营         |
+------+--------------+
4 rows in set (0.00 sec)
mysql> insert into emp values(7,'lili','female',48,null);
Query OK, 1 row affected (0.03 sec)

mysql> select * from emp
    -> ;
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  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 |
|  7 | lili       | female |   48 |   NULL |
+----+------------+--------+------+--------+

# 查询出有员工的部门
select * from dep where id in (select distinct dep_id from emp);

# 查询出没有员工的部门 not in 不支持 null
select * from dep where id not in (select distinct dep_id from emp);

select * from dep where id not in 
(select distinct dep_id from emp where dep_id is not null);

3. 关键字 any all

'''
any 与 in 的区别
	in (子查询语句)
	in (值1,值2,值3)
	any 只能跟子查询语句
	any 必须跟比较运算符配合使用
'''

# in 
select * from emp where dep_id in
(select id from dep where name in ("技术","人力资源"));

# any
select * from emp where dep_id = any
(select id from dep where name in ("技术","人力资源"));

# not in
select * from emp where dep_id not in
(select id from dep where name in ("技术","人力资源"));

# any 否定写法
select * from emp where 
! (dep_id = any(select id from dep where name in ("技术","人力资源")));

# 查询出那些薪资比所有部门的平均薪资都高的员工 => 薪资在所有部门平均线以上的狗币资本家
select * from employee where salary > all
(select avg(salary) from employee where depart_id is not null group by depart_id);

# 查询出那些薪资比所有部门的平均薪资都低的员工 => 薪资在所有部门平均线以下的无产阶级劳苦大众
select * from employee where salary < all
(select avg(salary) from employee where depart_id is not null group by depart_id);

# 查询出那些薪资比任意一个部门的平均薪资高的员工 => 薪资在任一部门平均线以上的员工
select * from employee where salary > any
(select avg(salary) from employee where depart_id is not null group by depart_id);

# 查询出那些薪资比任意一个部门的平均薪资低的员工 => 薪资在任一部门平均线以下的员工
select * from employee where salary < any
(select avg(salary) from employee where depart_id is not null group by depart_id);

4. 带比较运算符的子查询

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
2 rows in set (0.00 sec)

#查询大于部门内平均年龄的员工名、年龄
select 
	t1.name,t1.age 
from 
	emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2
	on t1.dep_id = t2.dep_id
where 
	t1.age > t2.avg_age;

5. 关键字 exists

'''
exists 关字键字表示存在
在使用 exists 关键字时，内层查询语句不返回查询的记录
而是返回一个真假值 True 或 False
当返回True时，外层查询语句将进行查询
当返回False时，外层查询语句不进行查询
'''

6. in 与 exists

# in的查询效率快于exists的查询效率

==============================exists==============================
# exists
exists后面一般都是子查询，后面的子查询被称做相关子查询（即与主语句相关），当子查询返回行数时，exists条件返回true，
否则返回false，exists是不返回列表的值的，exists只在乎括号里的数据能不能查找出来，是否存在这样的记录。

# 例
# 查询出那些班级里有学生的班级
select * from class where exists (select * from stu where stu.cid=class.id)

# exists的执行原理为：
1、依次执行外部查询：即select * from class 
2、然后为外部查询返回的每一行分别执行一次子查询：即(select * from stu where stu.cid=class.cid)
3、子查询如果返回行，则exists条件成立，条件成立则输出外部查询取出的那条记录

==============================in==============================
# in
in后跟的都是子查询，in()后面的子查询 是返回结果集的

# 例
查询和所有女生年龄相同的男生
select * from stu where sex='男' and age in(select age from stu where sex='女')

# in的执行原理为：
in()的执行次序和exists()不一样,in()的子查询会先产生结果集,
然后主查询再去结果集里去找符合要求的字段列表.符合要求的输出,反之则不输出.

7. not in 与 not exists

# not exists 查询的效率远远高于 not in 查询的效率

==============================not in============================
not in()子查询的执行顺序是：
为了证明not in成立，即找不到，需要一条一条地查询表，符合要求才返回子查询的结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完，只能查询全部记录才能证明，并没有用到索引。
                
============================not exists==========================
not exists：
如果主查询表中记录少，子查询表中记录多，并有索引。
例如:查询那些班级中没有学生的班级
select * from class

where not exists

(select * from student where student.cid = class.cid)

not exists的执行顺序是：
在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。

8. exists 练习

# 基本写法
select * from 表1 where exists (select * from 表2);

# 查询有员工的部门
select * from dep where exists (select * from emp where dep.id=emp.dep_id);

# not exists 的效果 高于 not in
select * from dep where not exists (select * from emp where dep.id=emp.dep_id);

# 1. 查询选修了所有课程的学生id、name
# 内联: 选修了三门课程的学生就是选修了所有课程的学生
select 
	s.id,s.name 
from 
	student as s inner join student2course as sc on s.id = sc.sid
group by 
	sc.sid
having 
	count(sc.cid) = (select count(id) from course);
    
# 子查询: 找到这样的学生，该学生不存在没有选修过的课程
select * from student as s where not exists (
	select * from course as c where not exists (
    	select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
    )
);

# 学生记录可以过滤出来,一定是子查询内没有记录
for 学生: # s.id=2
    for 课程: # c.id=1
        for 学生2课程: # sc.sid = 2 and sc.cid = 1
            pass

for sid in [1,2,3,4]:
    for cid in [1,2,3]:
        (sid,cid)
        
最外层循环一次
# (1,1)
# (1,2)
# (1,3)
最外层循环二次
# (2,1)
# (2,2)
# (2,3)
最外层循环三次
# (3,1)
# (3,2)
# (3,3)
最外层循环四次
# (4,1)
# (4,2)
# (4,3)  

# 2. 查询没有选修了所有课程的学生id、name => 没有全选的学生,存在没有选修的课程
select * from student as s where exists (
	select * from course as c where not exists (
    	select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
    )
);

# 3. 查询一门课也没有选的学生 => 找出这样的学生，不存在选修过的课程
select * from student as s where not exists (
	select * from course as c where exists (
    	select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
    )
);

# 4. 查询至少选修了一门课程的学生 => 找出这样的学生,存在选修过课程
select * from student as s where exists (
	select * from course as c where exists (
    	select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
    )
);