目录
一、补充（添加数据）
将一个查询的结果插入到另一张表中
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;