第六十一篇 SQL查询语句

目录

• 一、补充（添加数据）
• 二、SQL查询方法
  • 1.指定字段
  • 2.distinct
  • 3.聚合函数（又称多行函数/统计函数）
  • 4.where
  • 5.group by
  • 6.having
  • 7.order by
  • 8.limit
• 三、多表查询
  • 1.笛卡尔积查询
  • 2.链接查询
    • 2.1 内链接查询
    • 2.2 外链接查询
      • 2.2.1 左外链接查询
      • 2.2.2 右外链接查询
      • 2.2.3 全外链接查询
  • 3.子查询
    • 1.子查询介绍
    • 2.in 关键字查询
    • 3.exists关键字查询
  • 4.三表联查
  • 重点

一、补充（添加数据）

将一个查询的结果插入到另一张表中

create table person(name char(10), gender enum('male', 'female') default 'male');

insert into person values('king', 'male'),('jojo', 'male'),('siri', 'female');

create table man(name char(10), gender char(10));

# 将在一个表中查询的结果插入到另一个表中
# 语法：
insert into 表名2 select * from 表名1 where 条件;
# 示例：
insert into man select * from person where gender = 'male';

二、SQL查询方法

1.指定字段

1.1 星号（*）表示所有字段：

select * from 表名;

1.2 手动指定需要查询的字段：

select 字段名 from 表名; 
# 可以用逗号隔开多个字段来查询记录中的多个字段信息
# 示例：
select name, gender, salary from emp;

1.3 可以使用四则运算（MySQL5.6版本中可以 +、 -、 * 、/ 、%)

# 示例：
select name, (math + english) / 2 as 平均分 from student where english > 100;

1.4 取别名（as）

# 在字段后面加 as（不加也可以），可以将别名用引号括起来（不用也行）
# 示例：
select name as "姓名" from emp where salary > 10000;

1.5 单行函数（lower、upper）

可以将输出的值小写/大写

# 示例：
select upper(name) from emp where salary > 10000;

2.distinct

去除重复记录，一般放在查询字段的前面

# 示例：
select distinct name from emp;

# 当查询字段为*时，仅当查询结果中所有字段全都相同时，才算重复的记录
select distinct * from emp;

3.聚合函数（又称多行函数/统计函数）

统计多行返回一个值（由于MySQL5.6版本没有报错，所以当聚合函数得到一个值，但是查询语句中包含有多个值的字段时，将会输出该字段的首个数据）

# 1.求和   
sum(字段名)

# 2.平均数
avg(字段名)

# 3.最大值  
max(字段名)

# 4.最小值  
min(字段名)

# 5.个数    
count(字段名)    # 当指定字段时，如果字段为空不会被计入；字段名称可以使用*代替，可以得到完整的个数

# 这些聚合函数可以用在字段的位置，或是分组的后面   
# 例如: 查询所有人的平均工资  
select avg(salary) from emp

# 错误案例1: 查询工资最高的人的姓名 
select name,max(salary) from emp; 
# 分析：将默认显示第一个name。因为name有很多行，而max(salary) 只有一行，两列的行数不匹配

# 错误案例2：
select name from emp where salary = max(salary);
#逻辑错误，分析：where读取满足条件的一行，max()要先拿到所有数据 才能求最大值，但后面的max函数由于没有数据可读（缺少查询语句），所以无法去求出最大值，因而where也无法拿到条件去前面查询数据

# 结论：where 后面不能使用聚合函数 

4.where

比较常见的查询过滤方法，通过在where后面接条件来查询想要的数据

# 语法：
select 字段名（可以多个或*） from 表名 where 字段名 比较运算符/成员运算符/逻辑运算符 值;

# 1.比较运算符（<、 >、 <=、 >=、 =、 !=）
# 示例：
select name from emp where salary < 12000;

# 2.成员运算符（in、not in）
# 成员运算符后面一般是一个集合
# 示例：
select name from emp where salary in (3000, 4000);

# 3.逻辑运算符（and、 or、 not）
# not 必须放在表达式前面；and和or 放在两个表达式中间
# 示例：
select name from emp where not salary = 8000;
select name from emp where salary = 6000 or salary = 5000  -- 打印工资为6000或工资为5000的姓名;

# 4.模糊查询（like：一般与通配符联用）
# 4.1 % 表示任意个数的任意字符
# 示例：
select * from emp where name like 'k%'  -- 打印姓名首字母为k的信息;
select * from emp where name like '%k'  -- 打印姓名尾字母为k的信息;
select * from emp where name like '%k%'  -- 只要姓名中含有k就打印;

# 4.2 _ 表示一个任意字符
# 示例：
select * from emp where name like '_k' -- 打印姓名中第二个字母为k的信息;

5.group by

group是分组的意思，group by是将一个整体按照字段来划分为多个组，进而用来统计按相同属性分组后的其他数据

# 语法：
select 字段名 from 表名 group by 字段名;
# 示例：
select gender, count(*) from emp group by gender;

# 不规范示例：
select name,sex,count(*) from emp group by sex;
# mysql5.6版本及以下，用上述语句查询的name的结果仅显示该分组下的第一个字段值  
# 5.7版本及以上则直接报错 
# 5.6也可以手动开启这个功能：我们可以添加 ONLY_FULL_GROUP_BY 到sql_mode中避免这个问题（ sql_mode = ONLY_FULL_GROUP_BY ）
# 我们可以用group_concat 将分组之外的字段 做一个拼接 ,但是这是没有意义
# 如果要查询某个性别下的所有信息 直接使用where 即可  

#结论: 只有出现在了group by 后面得字段才能出现在select的后面

6.having

用于对分组后的数据进行筛选过滤，与where不同的是，where是从文件读取时的过滤条件，这导致了where无法使用聚合函数（因为数据读取工作都没有完成 不可能统计出数据），having是在分组后进行的过滤条件，所以可以使用聚合函数进行统计数据

# 语法：
select 字段名 from 表名 group by 字段名 having 条件（可以使用聚合函数）;
# 示例：
select count(*) from emp group by deptno having count(name) < 3;

# 也可以在组中再分组（以最后一次分组的字段为准）
select count(*) from emp group by deptno, job;

7.order by

用于对记录排序

# asc是升序，desc是降序
# 语法：
select 字段名 from 表名 order by 字段名；（默认是升序）

# 示例：
select * from emp order by deptno, salary desc;

8.limit

1.用于限制显示的记录数，用法：limit [start,] count;

2.start：开始位置

3.count：显示条数

4.注意：不指定start 时，则从第一条开始显示

# 查看前三人
select *from emp limit 3;

# 查看工资最高的那个人信息
select *from emp order by salary desc limit 1;

# 指定起始位置
# 查看id为3-6的人的记录
select *from emp limit 2,4;  # limit默认从0开始，但我们设置的id都是从1开始，所以0到2有3个数，因此id就是3开始


# limit：可用于分页
# 分页原理:
# 1.先查询总数据条数 设为a
# 2.确定每页数量b
# 3.总页数为c = a / b 
# 4.如果除不尽则需要加1，例如 10 / 3 正确页数为4
# 5.查询语句的起始位置为 s = （当前页数 d 减去1）乘以每页数量
# 6.即  s =  (d - 1) * b
# 7.语句为:select*from table_name limit s,b

三、多表查询

# 不存在外键关联的两张表

# 员工表（存在一些不正确的部门id）
create table emp (id int,name char(10),sex char,dept_id int);
insert emp values(1,"大黄","m",1);
insert emp values(2,"老王","m",2);
insert emp values(3,"老李","w",30);

# 部门表（存在一些没有员工的的部门）
create table dept (id int,name char(10));
insert dept values(1,"市场");
insert dept values(2,"财务");
insert dept values(3,"行政");

1.笛卡尔积查询

是两张表相乘的结果,若左边有m条 右边有n条 查询结果为m*n条; 往往包含大量错误数据

select *from dept,emp;  
select *from dept,emp where dept.id = dept_id;  

2.链接查询

2.1 内链接查询

1.本质上就是笛卡尔积查询

2.查询出两张表都有匹配关系的记录

select *from dept,emp where dept.id=emp.dept_id;  
 #where用于筛选数据,而在多多表查询中要筛选的是两边的关系 on用于过滤关联关系
 #而where单独做条件过滤，这样sql看起来可以更清晰明确，当然where依然可以代替on  

select *from dept join emp on dept.id=emp.dept_id;
inner可以省略

2.2 外链接查询

2.2.1 左外链接查询

左表中记录的无论是否有匹配关系都全部显示，右表中仅显示匹配成功的记录

select *from dept left join emp on dept.id=emp.dept_id;

2.2.2 右外链接查询

右表中记录的无论是否有匹配关系都全部显示，左表中仅显示匹配成功的记录

select *from dept right join emp on dept.id=emp.dept_id;

2.2.3 全外链接查询

1.无论是否匹配成功，两边表中的记录都要全部显示

2.union 只能用于字段数量相同的两个表 ，会自动去除重复的记录

3.union all 则保留所有记录

#mysql 不支持
select *from dept full join emp on dept.id = emp.dept_id;  

#mysql中可以使用合并查询结果 在所有语句最后写分号
select *from dept left join emp on dept.id=emp.dept_id
union
select *from dept right join emp on dept.id=emp.dept_id;

3.子查询

1.子查询介绍

1.当一个查询是另一个查询的条件时,这个查询称之为子查询(内层查询)

2.当查询需求比较复杂,一次性查询无法得到结果,需要多次查询时，就可以使用子查询

3.把一个复杂的问题拆分为若干个简单的问题

4.首先明确子查询就是一个普通的查询,当一个查询需要作为子查询使用时,用括号包裹即可

#第一步 需要知道财务部的id 
select id from dept where  name = "财务";
#第二步 用查询的到的id作为判断条件查询emp
select name from emp where dept_id = 3;

# 子查询：不能写死，条件或数据源是上一个查询的结果，所以直接写在后面  加上括号就好了
select name from emp where dept_id = (select id from dept where  name = "财务");

2.in 关键字查询

"查询平均年龄大于25的部门名称
1.子查询方式:
平均年龄大于25的部门id有哪些?
先要求出每个部门的平年龄! 筛选出平均年龄大于25的部门id
拿着部门id 去查询部门表查询"
select name from dept where id in  (select dept_id from emp group by dept_id having avg(age) > 25);


"
2.多表查询方式:
先把数据拼接到一起 在加以筛选"
select dept.name from emp inner join dept
on emp.dept_id = dept.id 
group by dept.name
having avg(age) >25;

3.exists关键字查询

1.exists 后跟子查询，子查询有结果是为True，没有结果时为False

2.为true时外层执行，为false外层不执行

# 查看exists的返回结果: 只有 0 和 1
select * from emp where exists (select *from emp where salary > 1000);
# ?
select (exists (select *from emp where salary > 10000));


#综合练习:
"查询每个部门工资最高的员工信息
先查询每个部门的最高工资
将查询结果与员工表联合起来
在加条件判断部门id相同并且 最高工资相同 则显示"
#一个查询结果也是一个表 既然是表就能链接起来
select * from emp  inner join  
(select dept_id, max(salary) m from emp group by dept_id)  t2
on emp.dept_id = t2.dept_id 
where emp.salary = t2.m; 

4.三表联查

create table tsr(id int primary key auto_increment,t_id int,s_id int,
foreign key(s_id) references stu(id),
foreign key(t_id) references tea(id));

insert into stu values(null,"张三"),(null,"李四");
insert into tea values(null,"egon"),(null,"wer");
insert into tsr values(null,1,1),(null,1,2),(null,2,2);



#egon老师教过哪些人?
select *from stu join tea join tsr
on stu.id = tsr.s_id and tea.id = tsr.t_id
where tea.name = "egon";


'tea表中查得egon的 id 为1
关系表中查的 123和egon有关系
学生表中查得 123的名字为abc'

重点

#综合练习:
"查询每个部门工资最高的员工信息
先查询每个部门的最高工资
将查询结果与员工表联合起来
在加条件判断部门id相同并且 最高工资相同 则显示"

#一个查询结果也是一个表 既然是表就能链接起来
select * from emp  inner join  
(select dept_id, max(salary) m from emp group by dept_id)  t2
on emp.dept_id = t2.dept_id 
where emp.salary = t2.m;