sql 最核心的查询语句!!!!
增删改
单表查询
select语句的完整写法
关键字的书写顺序
执行顺序
多表查询
笛卡尔积
内连接
左外连接
右外连接
全外连接
通过合并左外连接和右外连接
子查询
一个查询包含另一个查询 被包含的叫子查询
当一个查询的结果 是另一个查询的条件时 这个查询叫子查询
子查询需要用括号包起来
====================================
1.数据增删改
增加
insert [into] 表名[(可选字段名)] values(一堆值1),(一堆值2),.....
into 可以省略
表名后的字段可以选
如果写了 后面的values中的值必须与表名后的字段一一对应
如果没写 后面的values中的值必须与表的所有字段一一对应
values后面可以给多组值 用逗号隔开
删除
delete from 表名[where 条件]
条件不写 是删除所有记录 是一行一行删除 注意自增id 不会归零
truncate 重建表 先记录表结构 删除整个表再重新建出来表 自增id 会归零
更新数据
update 表名 set 字段名 = 值[,字段2 = 值2],[where 条件]
可以一次性修改多个字段的值用逗号隔开
条件如果不写 修改所有记录
2.单表查询
不带关键字的查询
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);
3 关键字的作用
distinct 去除重复数据 所有数据全都重复才算重复
where 在逐行读取数据时的一个判断条件
group by 对数据分组
having 对分组后的数据进行过滤
order by 对结果排序
limit 指定获取数据条数
完整的select 语句 语法,书写顺序是固定的 *****
select [distinct] * from 表名
[where
group by
having
order by
limit
]
注意 在书写时 必须按照这个顺序来写 !!! 但是顺写顺写不代表执行顺序
关键字的执行顺序 *****
from
where
group by
having
select
distinct
order by
limit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数
简单查询
定义显示格式:
concat()函数用于拼接字符串
# 补充concat（不分组时用）
select name as 姓名,salary as 薪资 from emp;
select concat("NAME: ",name) as 姓名,concat("SAL: ",salary) as 薪资 from emp;
通过四则运算查询
SELECT name, salary*12 FROM employee;
SELECT name, salary*12 AS Annual_salary FROM employee;
SELECT name, salary*12 Annual_salary FROM employee;
select
(case
when english + math > 120 then
concat(name," nice")
when english + math <= 130 then
concat(name," shit")
end
) ,english,math from stu;
A, distinct去重
当所有字段的值都相同才会去重
select distinct post,avg(salary) from emp
where age >= 30
group by post
having avg(salary) > 10000;
B, where 条件
where字句中可以使用：
1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是10或20或30
4. like 'egon%'
pattern可以是%或_，
%表示任意多字符
_表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
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);
where后可用:
四则运算+-*/
><=!
between and
in
not null
and or not
like
regexp
any/all where 10>any(select age from emp)
where 10>all(select age from emp)
select id,name from db39.emp where id >= 3 and id <= 6
select * from db39.emp where id between 3 and 6;
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000);
要求：查询员工姓名中包含i字母的员工姓名与其薪资
select name,salary from db39.emp where name like '%i%'
要求：查询员工姓名是由四个字符组成的的员工姓名与其薪资
select name,salary from db39.emp where name like '____';
select name,salary from db39.emp where char_length(name) = 4;
select * from db39.emp where id not between 3 and 6;
select * from emp where salary not in (20000,18000,17000);
要求：查询岗位描述为空的员工名与岗位名
select name,post from db39.emp where post_comment is NULL;
select name,post from db39.emp where post_comment is not NULL;
C, group by 分组查询 *****
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
#3、为何要分组呢？
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数
小窍门：‘每’这个字后面的字段，就是我们分组的依据
#4、大前提：
可以按照任意字段分组，但是分组完毕后，比如group by post，
只能查看post字段，如果想查看组内信息，需要借助于聚合函数
什么是分组
把一个整体 分割为多个部分
为什么分组
在数据库中分组为了统计 *****
分组后 组里的详细记录就被隐藏起来了 不能直接查看
dept 一分组 变成三条记录 每个组中却包含多条记录 没办法显示
一定要显示的话
可以使用group_concat(字段名)
可以将多个值拼接成一个字符串
注意*****: 1.只有查看出现在group by 后面的字段, 其他都被影藏了
2.聚合函数不能写在where的后面 where最先执行 它的作用硬盘读取数据并
过滤 以为数据还没有读取完 此时不能进行统计
聚合函数:count
max /min
avg
sum
了解：
在mysql 5.6中 分组后会默认显示 每组的第一条记录 这是没有意义的
5.7不显示 因为5.7中 sql_mode中自带 ONLY_FULL_GROUP_BY
group by 后面可以有多个分组与依据 会按照顺序执行
#设置sql_mode为only_full_group_by，意味着以后但凡分组，只能取到分组的依据
mysql> set global sql_mode="strict_trans_tables,only_full_group_by";
#每个部门的最高工资
select post,max(salary) from emp group by post;
select post,min(salary) from emp group by post;
select post,avg(salary) from emp group by post;
select post,sum(salary) from emp group by post;
select post,count(id) from emp group by post;
#group_concat（分组之后用）
select post,group_concat(name) from emp group by post;
select post,group_concat(name,"_SB") from emp group by post;
select post,group_concat(name,": ",salary) from emp group by post;
select post,group_concat(salary) from emp group by post;
# 补充as语法
mysql> select emp.id,emp.name from emp as t1; # 报错
mysql> select t1.id,t1.name from emp as t1;
# 查询四则运算
select name,salary*12 as annual_salary from emp;
分组练习
1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;
2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from emp group by post;
3. 查询公司内男员工和女员工的个数
select sex,count(id) from emp group by sex;
4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from emp group by sex;
8、统计各部门年龄在30岁以上的员工平均工资
select post,avg(salary) from emp where age >= 30 group by post;
聚合函数
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
示例：
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;
D, having过滤
HAVING与WHERE不一样的地方在于!!!!!!
#！！！执行优先级从高到低：where > group by > having
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
having的语法格式与where一模一样，只不过having是在分组之后进行的进一步过滤
即where不能用聚合函数，因为数据还没有全部读取,无法统计.而having是可以用聚合函数，这也是他们俩最大的区别
1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
select post,avg(salary) from emp
where age >= 30
group by post
having avg(salary) > 10000;
#强调：having必须在group by后面使用
select * from emp
having avg(salary) > 10000;
E, order by 排序用的
按单列排序
SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC;
SELECT * FROM employee ORDER BY salary DESC;
按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
SELECT * from employee
ORDER BY age,
salary DESC;
asc 表示升序 是默认的
desc 表示降序
by 后面可以有多个排序依据
select * from emp order by salary asc; #默认升序排
select * from emp order by salary desc; #降序排
select * from emp order by age desc; #降序排
select * from emp order by age desc,salary asc; #先按照age降序排，再按照薪资升序排
# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，
然后对平均工资进行排序
select post,avg(salary) from emp
where age > 10
group by post
having avg(salary) > 1000
order by avg(salary)
;
F, limit 限制显示条数
limit a,b
limit 1,5
从1开始 到5结束 错误
从1开始 不包含1 取5条 正确
select * from emp limit 3;
select * from emp order by salary desc limit 1;
分页查询
每页显示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)
正表达式匹配
由于like只能使用% 和 _ 不太灵活
可以将like换为 regexp 来使用正则表达式
select * from emp where name regexp '^jin.*(n|g)$';
4.多表查询 ******
数据准备
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
查询结果是
左表中的每条记录 与右表中的每条记录都关联一遍
因为 他不知道什么样的对应关系是正确 只能帮你都对一遍
会产生一堆垃圾数据,需要进行过滤 where 从表的外键 =主表的主键
a表有m条记录 b表有n条记录
笛卡尔积结果为m * n 记录
需要自己筛选出正确的关联关系
select *from emp,dept where emp.dept_id = dept.id;
2.内连接查询inner join
把两张表有对应关系的记录连接成一张虚拟表 (就是笛卡尔积查询)
内指的是能够匹配上的数据
select * from emp [inner] join dep on emp.dep_id = dep.id;
#应用：
select * from emp,dep where emp.dep_id = dep.id and dep.name = "技术"; # 不要用where做连表的活
select * from emp inner join dep on emp.dep_id = dep.id
where dep.name = "技术";
3.左外链接查询left join
在内连接的基础上，保留左边没有对应关系的记录
左表数据全部显示 右表只显示匹配上的
select *from emp left join dept on emp.dept_id = dept.id;
4.右外链接查询right join
在内连接的基础上，保留右边没有对应关系的记录
右表数据全部显示 左表只显示匹配上的
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 all 不会去除重复数据
#补充：多表连接可以不断地与虚拟表连接
查找各部门最高工资
select t1.* from emp as t1
inner join
(select post,max(salary) as ms from emp group by post) as t2
on t1.post = t2.post
where t1.salary = t2.ms
;
三表查询
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.子查询
子查询是将一个查询语句嵌套在另一个查询语句中。(内层查询)
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等
什么时候使用子查询
当一次查询无法得到想要结果时 需要多次查询
子查询原理,解决问题的思路
是把一个复杂的问题 拆分为多个简单的问题
是把一个复杂的查询 拆分为多个简单的查询
给你部门的的名称
查部门有哪些人?
第一步查到部门的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;
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);
select emp.name from emp inner join dep on emp.dep_id = dep.id where dep.name="技术";
select name from emp where dep_id =
(select id from dep where name="技术");
1 带IN关键字的子查询
大多数子查询都可以被连接查询代替
in (1,2)
#查询平均年龄在25岁以上的部门名
select name from dep where id in
(select dep_id from emp group by dep_id having avg(age) > 25);
2 带比较运算符的子查询
select dep.name from emp inner join dep on emp.dep_id = dep.id
group by dep.name
having avg(age) > 25;
3 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
#查看不足2人的部门名(子查询得到的是有人的部门id)
select * from emp where exists (
select id from dep where id > 3
);
#查询每个部门最新入职的那位员工
select t1.id,t1.name,t1.post,t1.hire_date,t2.post,t2.max_date from emp as t1 inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
on t1.post = t2.post
where t1.hire_date = t2.max_date
;