一、数据增删改
二、单表查询
三、正表达式匹配
四、多表查询
五、子查询
一、.数据增删改
增加
insert [into] 表名[(可选字段名)] values(一堆值1),(一堆值2),.....
into 可以省略
表名后的字段可以选
如果写了 后面的values中的值必须与表名后的字段一一对应
如果没写 后面的values中的值必须与表的所有字段一一对应
values后面可以给多组值 用逗号隔开
insert [into] 表名[(可选字段名)] values(一堆值1),(一堆值2),.....
into 可以省略
表名后的字段可以选
如果写了 后面的values中的值必须与表名后的字段一一对应
如果没写 后面的values中的值必须与表的所有字段一一对应
values后面可以给多组值 用逗号隔开
删除
delete from 表名[where 条件]
条件不写 是删除所有记录 是一行一行删除 注意自增id 不会归零
truncate 重建表 先记录表结构 删除整个表再重新建出来表 自增id 会归零
delete from 表名[where 条件]
条件不写 是删除所有记录 是一行一行删除 注意自增id 不会归零
truncate 重建表 先记录表结构 删除整个表再重新建出来表 自增id 会归零
更新数据
update 表名 set 字段名 = 值[,字段2 = 值2],[where 条件]
可以一次性修改多个字段的值用逗号隔开
条件如果不写 修改所有记录
update 表名 set 字段名 = 值[,字段2 = 值2],[where 条件]
可以一次性修改多个字段的值用逗号隔开
条件如果不写 修改所有记录
二.单表查询
不带关键字的查询
select {1.*|2.字段名|3.四则运行|4.聚合函数} from 表名 [where 条件]
1.* 表示查询所有字段
2.可以手动要查询的字段
3.字段的值可以进行加减乘除运算
4.聚合函数,用于统计
where 是可选的
不带关键字的查询
select {1.*|2.字段名|3.四则运行|4.聚合函数} from 表名 [where 条件]
1.* 表示查询所有字段
2.可以手动要查询的字段
3.字段的值可以进行加减乘除运算
4.聚合函数,用于统计
where 是可选的
select 的完整语法
数据准备
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);
数据准备
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);
关键字的作用
distinct 去除重复数据 所有数据全都重复才算重复
where 在逐行读取数据时的一个判断条件
group by 对数据分组
having 对分组后的数据进行过滤
order by 对结果排序
limit 指定获取数据条数
distinct 去除重复数据 所有数据全都重复才算重复
where 在逐行读取数据时的一个判断条件
group by 对数据分组
having 对分组后的数据进行过滤
order by 对结果排序
limit 指定获取数据条数
完整的select 语句 语法 *****
select [distinct] * from 表名
[where
group by
having
order by
limit
]
注意 在书写时 必须按照这个顺序来写 但是顺写顺写不代表执行顺序
数据库伪代码
def from():
打开文件
def where():
对读取的数据进行过滤
select [distinct] * from 表名
[where
group by
having
order by
limit
]
注意 在书写时 必须按照这个顺序来写 但是顺写顺写不代表执行顺序
数据库伪代码
def from():
打开文件
def where():
对读取的数据进行过滤
def group_by():
对数据分组
def having():
对分组后的数据进行过滤
def distinct():
去除重复数据
def order():
排序
def limit():
指定获取条数
对数据分组
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;
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; *完全不重要
指定显示格式:
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),job
char(10),salary double);
insert into emp values
(1,"刘备","男","市场","总监",5800),
(2,"张飞","男","市场","员工",3000),
(3,"关羽","男","市场","员工",4000),
(4,"孙权","男","行政","总监",6000),
(5,"周瑜","男","行政","员工",5000),
(6,"小乔","女","行政","员工",4000),
(7,"曹操","男","财务","总监",10000),
(8,"司马懿","男","财务","员工",6000);
create table emp (id int,name char(10),sex char,dept char(10),job
char(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 分组查询 *****
什么是分组
把一个整体 分割为多个部分
为什么分组
在数据库中分组为了统计 *****
分组后 组里的详细记录就被隐藏起来了 不能直接查看
dept 一分组 变成三条记录 每个组中却包含多条记录 没办法显示
一定要显示的话
可以使用group_concat(字段名)
可以将多个值拼接成一个字符串
什么是分组
把一个整体 分割为多个部分
为什么分组
在数据库中分组为了统计 *****
分组后 组里的详细记录就被隐藏起来了 不能直接查看
dept 一分组 变成三条记录 每个组中却包含多条记录 没办法显示
一定要显示的话
可以使用group_concat(字段名)
可以将多个值拼接成一个字符串
注意*****: 1.只有出现在group by 后面的字段 才可以被显示 其他都被影藏了
2.聚合函数不能写在where的后面 where最先执行 它的作用硬盘读取数据并
过滤 以为数据还没有读取完 此时不能进行统计
2.聚合函数不能写在where的后面 where最先执行 它的作用硬盘读取数据并
过滤 以为数据还没有读取完 此时不能进行统计
什么样的字段适合用于分组
重复性高的字段
重复性高的字段
请查询每种性别的平均工资
性别分组
请查询每种岗位的平均工资
岗位分组
每后面就是分组的依据
性别分组
请查询每种岗位的平均工资
岗位分组
每后面就是分组的依据
了解：
在mysql 5.6中 分组后会默认显示 每组的第一条记录 这是没有意义的
5.7不显示 因为5.7中 sql_mode中自带 ONLY_FULL_GROUP_BY
group by 后面可以有多个分组与依据 会按照顺序执行
在mysql 5.6中 分组后会默认显示 每组的第一条记录 这是没有意义的
5.7不显示 因为5.7中 sql_mode中自带 ONLY_FULL_GROUP_BY
group by 后面可以有多个分组与依据 会按照顺序执行
order by 排序用的
asc 表示升序 是默认的
desc 表示降序
by 后面可以有多个排序依据
asc 表示升序 是默认的
desc 表示降序
by 后面可以有多个排序依据
limit 限制显示条数
limit a,b
limit 1,5
从1开始 到5结束 错误
从1开始 不包含1 取5条
limit 1,5
从1开始 到5结束 错误
从1开始 不包含1 取5条
分页查询
每页显示3条 共有10条数据
if 10 % 3 == 0:
10 / 3
else:
10/3 +1
总页数4
每页显示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)
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
页数 - 1 * 条数
1 - 1 = 0 * 3 = 0
2 - 1 = 1 * 3 = 3
三、正表达式匹配
由于like只能使用% 和 _ 不太灵活
可以将like换为 regexp 来使用正则表达式
由于like只能使用% 和 _ 不太灵活
可以将like换为 regexp 来使用正则表达式
四、多表查询 ******
数据准备
create table emp (
id int,
name char(10),
sex char,
dept_id int
);
数据准备
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);
insert emp values(2,"老王","m",2);
insert emp values(3,"老李","w",30);
create table dept (
id int,
name char(10)
);
id int,
name char(10)
);
insert dept values(1,"市场");
insert dept values(2,"财务");
insert dept values(3,"行政");
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;
select *from emp,dept where emp.dept_id = dept.id;
2.内连接查询 就是笛卡尔积查询
select *from emp [inner] join dept;
select *from emp inner join dept where emp.dept_id = dept.id;
select *from emp [inner] join dept;
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;
左表数据全部显示 右表只显示匹配上的
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;
右表数据全部显示 左表只显示匹配上的
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不支持
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;
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 不会去除重复数据
union all 不会去除重复数据
on 关键字 where 都是用于条件过滤 没有本质区别
在单表中where的作用是筛选过滤条件
在多表中where 连接多表 满足条件就连接 不满足就不连接
为了区分是单表还是多表 搞个新的名字 就是 on
只要是连接多表的条件 就使用on
在单表中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,"yyh");
insert into tsr values(null,1,1),(null,1,2),(null,2,2);
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,"yyh");
insert into tsr values(null,1,1),(null,1,2),(null,2,2);
select *from stu1 join tea join tsr
on stu.id = tsr.s_id and tea.id = tsr.t_id
where tea.name = "egon";
on stu.id = tsr.s_id and tea.id = tsr.t_id
where tea.name = "egon";
多表查询套路
1.把所有表都连起来
2.加上连接条件
3.如果有别的过滤条件 加上where
1.把所有表都连起来
2.加上连接条件
3.如果有别的过滤条件 加上where
五、.子查询
条件当一个查询的结果是另一个查询的时 这个查询称之为子查询(内层查询)
条件当一个查询的结果是另一个查询的时 这个查询称之为子查询(内层查询)
什么时候使用子查询
当一次查询无法得到想要结果时 需要多次查询
解决问题的思路
是把一个复杂的问题 拆分为多个简单的问题
是把一个复杂的查询 拆分为多个简单的查询
in (1,2)
当一次查询无法得到想要结果时 需要多次查询
解决问题的思路
是把一个复杂的问题 拆分为多个简单的问题
是把一个复杂的查询 拆分为多个简单的查询
in (1,2)
给你部门的的名称
查部门有哪些人?
第一步查到部门的id
第二部 拿着id去员工表查询
select *from dept join emp on dept.id = emp.dept_id;
查部门有哪些人?
第一步查到部门的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;
# 使用子查询 得到 每个部门的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;
create table emp (id int,name char(10),sex char,age int,dept_id int,job
char(10),salary double);
insert into emp values
(1,"刘备","男",26,1,"总监",5800),
(2,"张⻜飞","男",24,1,"员⼯工",3000),
(3,"关⽻羽","男",30,1,"员⼯工",4000),
(4,"孙权","男",25,2,"总监",6000),
(5,"周瑜","男",22,2,"员⼯工",5000),
(6,"⼩小乔","女",31,2,"员⼯工",4000),
(7,"曹操","男",19,3,"总监",10000),
(8,"司⻢马懿","男",24,3,"员⼯工",6000);
char(10),salary double);
insert into emp values
(1,"刘备","男",26,1,"总监",5800),
(2,"张⻜飞","男",24,1,"员⼯工",3000),
(3,"关⽻羽","男",30,1,"员⼯工",4000),
(4,"孙权","男",25,2,"总监",6000),
(5,"周瑜","男",22,2,"员⼯工",5000),
(6,"⼩小乔","女",31,2,"员⼯工",4000),
(7,"曹操","男",19,3,"总监",10000),
(8,"司⻢马懿","男",24,3,"员⼯工",6000);
day_id 成功 失败
2018-10-01 2 2
2018-10-02 1 2
2018-10-01 2 2
2018-10-02 1 2
insert into emp2 values(1,"laowangba","男",26,"总监",5800);
insert into emp2 values(2,"laoliba","男",26,"总监",5800);
insert into emp2 values(3,"laocheng","男",26,"总监",5800);
insert into emp2 values(2,"laoliba","男",26,"总监",5800);
insert into emp2 values(3,"laocheng","男",26,"总监",5800);