day46 记录/数据的增删改， 查询数据/记录（单表查询、多表查询），子查询
中括号内的可写也可以不写。
mysql 最核心的就是数据查询语句 ，所以 今天都必须掌握。
1.表的增删改查
2.单表查询
select查询语句的完整写法 = 关键字的书写顺序
关键字的执行顺序
3.多表查询
笛卡尔积
内连接
左外连接
右外连接
全外连接：就是通过合并左外连接和右外连接。
4.子查询
一个查询包含另一个查询 被包含的叫子查询
当一个查询的结果 是另一个查询的条件时 ，这个查询叫子查询。
子查询需要用括号包起来。
一。表的增删改
增加
insert [into] 表名[(可选字段名)] values(一堆值1),(一堆值2),.....
注意：1.into 可以省略；
2.表名后的字段可以选，
如果写了 后面的values中的值必须与表名后的字段一一对应；
如果没写 后面的values中的值必须与表的所有字段一一对应。
3.values后面可以给一组数据值 或者 多组数据值 用逗号隔开。
删除
delete from 表名[where 条件]
条件不写时， 是删除所有记录， 是一行一行删除 注意：这样的删除自增主键id 不会归零。
truncate 重建表： 先记录表结构， 删除整个表再重新建出来表， 自增主键id 会归零。 -----------运用？？
更新数据
update 表名 set 字段名 = 值[,字段2 = 值2],[where 条件]
可以一次性修改多个字段的值用逗号隔开
条件如果不写， 修改所有记录。
二.单表查询
简单查询：不带关键字的查询。语法如下：
select {1.*/2.字段名/3.四则运算/4.聚合函数} from 表名
select义查询
1.* 表示查询所有字段；
2.可以手动要查询的字段；
3.字段的值可以进行加减乘除运算；
4.聚合函数,用于统计；
5.where 是可选的。
数据准备
create table stu(id int primary key auto_increment,name
char(10),math float,english float);
insert into stu values(null,"赵云",90,30);
insert into stu values(null,"小乔",90,60);
insert into stu values(null,"小乔",90,60);
insert into stu values(null,"大乔",10,70);
insert into stu values(null,"李清照",100,100);
insert into stu values(null,"铁拐李",20,55);
insert into stu values(null,"小李子",20,55);
完整的select 语句 语法 *****=关键字的书写顺序如下：
select [distinct] * from 表名
[where +条件
group by +字段
having +条件
order by
limit
]
关键字的作用：
distinct 去除重复数据：所有数据全都重复才算重复。
where 在逐行读取数据时的一个判断条件。
group by 对数据分组。
having 对分组后的数据进行过滤。
order by 对结果排序。
limit 指定获取数据记录的条数。
注意： 在书写时，必须按照这个顺序来写，但是书写顺序不代表执行顺序。
数据库伪代码
def from():
打开文件
def where():
对读取的数据进行过滤
def group_by():
对数据分组
def having():
对分组后的数据进行过滤
def distinct():
去除重复数据
def order():
排序
def limit():
指定获取条数
select 关键字的执行顺序 *****
def select(sql):
data = from()
data = where(data)
data = group by(data)
data = having(data)
data = distinct(data)
data = orderby(data)
data = limit(data)
return data;
简单查询
指定显示格式:
concat()函数：用于拼接字符串。 ？？
select
(
case
when english + math > 120 then
concat(name," nice")
when english + math <= 130 then
concat(name," shit")
end
) ,english,math from stu; *完全不重要
where 条件
create table emp (id int,name char(10),sex char,dept char(10),jobchar(10),salary double);
insert into emp values
(1,"刘备","男","市场","总监",5800),
(2,"张飞","男","市场","员工",3000),
(3,"关羽","男","市场","员工",4000),
(4,"孙权","男","行政","总监",6000),
(5,"周瑜","男","行政","员工",5000),
(6,"小乔","女","行政","员工",4000),
(7,"曹操","男","财务","总监",10000),
(8,"司马懿","男","财务","员工",6000);
group by 分组查询 *****
什么是分组？
把一个字段作为分组依据，把一个整体 分割为多个部分。
为什么分组？
在数据库中分组为了易于统计 *****
了解：
在mysql 5.6中 分组后会默认显示 每组的第一条记录 ，但这是没有意义的；
5.7不显示 因为5.7中 sql_mode中自带 ONLY_FULL_GROUP_BY（义只有分组的字段数据可显示）。
语法：select *from emp group by dept;
group by 后面可以有多个分组与依据 ，会按照从左到右顺序依次执行。
分组后的特点：1.组里的详细记录就被隐藏起来了 ，不能直接查看。
eg：dept 一分组 变成三条记录 每个组中却包含多条记录 没办法显示
2.一定要显示的话：可以使用group_concat(字段名)，它可以将多个值拼接成一个字符串。
注意*****: 1.只有出现在group by 后面的字段 才可以被显示，其他都被隐藏了。
3.可以通过聚合函数统计，来显示组内记录的统计结果。
注意：聚合函数不能写在where的后面 where最先执行 它的作用是从硬盘读取数据并过滤 ，以为数据还没有读取完 ，所以此时不能进行统计。
sum作用：求数据的和。 avg：求数据的平均数。
count作用：统计字段下的数据个数。 max/min：求数据的最大值最小值。
示例： SELECT COUNT(*) FROM employee; SELECT COUNT(*) FROM employee WHERE depart_id=1; SELECT MAX(salary) FROM employee; SELECT MIN(salary) FROM employee; SELECT AVG(salary) FROM employee; SELECT SUM(salary) FROM employee; SELECT SUM(salary) FROM employee WHERE depart_id=3;
什么样的字段适合用于分组？
重复性高的字段
eg：请查询每种性别的平均工资
性别分组
请查询每种岗位的平均工资
岗位分组
小窍门：每后面就是分组的依据。
HAVING
常用于分组后的筛选
但是having和where不一样的地方在于以下几点！！！
#！！！执行优先级从高到低：where > group by > 聚合函数 > having >order by
1.where和having的区别
1. Where 是一个约束声明，使用Where约束来自数据库的数据，Where是在结果返回之前起作用的
（先找到表，按照where的约束条件，从表(文件)中取出数据），Where中不能使用聚合函数
2.Having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作
（先找到表，按照where的约束条件，从表(文件)中取出数据，然后group by分组，
如果没有group by则所有记录整体为一组，然后执行聚合函数，然后使用having对聚合的结果进行过滤），
在Having中可以使用聚合函数。
3.where的优先级比having的优先级高
4.having可以放到group by之后，而where只能放到group by 之前。
验证不同之处：
1.查看员工的id>15的有多少个 select count(id) from employee where id>15;#正确，分析：where先执行，后执行聚合count(id), 然后select出结果 select count(id) from employee having id>15; #报错，分析：先执行聚合count(id)，后执行having过滤， #无法对id进行id>15的过滤 #以上两条sql的顺序是 1:找到表employee--->用where过滤---->没有分组则默认一组执行聚合count(id)--->select执行查看组内id数目 2:找到表employee--->没有分组则默认一组执行聚合count(id)---->having 基于上一步聚合的结果（此时只有count(id)字段了） 进行id>15的过滤，很明显，根本无法获取到id字段
order by 排序用的
asc 表示升序 是默认的
desc 表示降序
by 后面可以有多个排序依据
limit 限制显示条数
limit a,b
limit 1,5
从1开始 到5结束 错误
含义是：从1开始 不包含1，取5条记录。
分页查询
每页显示3条 共有10条数据
if 10 % 3 == 0:
10 / 3
else:
10/3 +1
总页数4
第一页
select *from emp limit(0,3)
第二页
select *from emp limit(3,3)
第二页
select *from emp limit(6,3)
起始位置的算法
页数 - 1 * 条数
1 - 1 = 0 * 3 = 0
2 - 1 = 1 * 3 = 3
三。正表达式匹配
由于like只能使用% 和 _ 不太灵活，
可以将like换为 regexp 来使用正则表达式。
四。多表查询 ******
数据准备
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.交叉连接查询、笛卡尔积查询
select *from 表1,表n
查询结果是：将左表中的每条记录 与右表中的每条记录都关联一遍。
因为 他不知道什么样的对应关系是正确 只能帮你都对一遍。
a表有m条记录 b表有n条记录，笛卡尔积结果为m * n 记录。
需要自己筛选出正确的关联关系
select *from emp,dept where emp.dept_id = dept.id;
2.内连接查询 就是笛卡尔积查询
select *from emp [inner] join dept;等同于select *from 表1,表n。
select *from emp inner join dept where emp.dept_id = dept.id;
3.左外连接查询
select *from emp left join dept on emp.dept_id = dept.id;
左表数据全部显示 右表只显示匹配上的
4.右外连接查询
select *from emp right join dept on emp.dept_id = dept.id;
右表数据全部显示 左表只显示匹配上的
内和外的理解 内指的是匹配上的数据 外指的是没匹配上的数据
5.全外连接
select *from emp full join dept on emp.dept_id = dept.id; ##mysql不支持
union 合并查询结果
select *from emp left join dept on emp.dept_id = dept.id
union
select *from emp right join dept on emp.dept_id = dept.id;
union 去除重复数据 只能合并字段数量相同的表
union all 不会去除重复数据
on 关键字 where 都是用于条件过滤 没有本质区别
在单表中where的作用是筛选过滤条件
在多表中where 连接多表 满足条件就连接 不满足就不连接
为了区分是单表还是多表 搞个新的名字 就是 on
只要是连接多表的条件 就使用on
三表查询
create table stu(id int primary key auto_increment,name char(10));
create table tea(id int primary key auto_increment,name char(10));
create table tsr(id int primary key auto_increment,t_id int,s_id int,
foreign key(s_id) references stu(id),
foreign key(s_id) references stu(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);
select *from stu join tea join tsr
on stu.id = tsr.s_id and tea.id = tsr.t_id
where tea.name = "egon";
记：多表查询套路：******
1.把所有表都连接起来；
2.加上连接条件；
3.如果有别的过滤条件，加上where。
5.子查询
什么是子查询？
条件当一个查询a的结果是另一个查询b的条件时， 这个查询a称之为子查询(内层查询)。
什么时候使用子查询？
当一次查询无法得到想要结果时 需要多次查询。多表连接查询可分解为多步子查询！！。
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字。
#4：还可以包含比较运算符：= 、 !=、> 、<等。
记：子查询解决查询问题的思路：******
是把一个复杂的问题 拆分为多个简单的问题，
是把一个复杂的查询 拆分为多个简单的查询。
in (1,2)
给你部门的的名称
查部门有哪些人?
第一步查到部门的id
第二部 拿着id去员工表查询
select *from dept join emp on dept.id = emp.dept_id;
select *from emp join
# 使用子查询 得到 每个部门的id 以及部门的 最高工资 形成一个虚拟表 把原始表和 虚拟表连接在一起
(select dept_id,max(salary)as m from emp group by dept_id) as t1
# 如果这个人的部门编号 等于 虚拟表中的部门编号
on emp.dept_id = t1.dept_id
and
# 并且 如果这个人的工资 等于 虚拟表中的最高工资 就是你要找的人
emp.salary = t1.m;
浙公网安备 33010602011771号