数据库（四）
前言
昨天学习了表与表之间的关系、多表关联、复制表、单表查询的知识，今天学习的内容是单表查询与关键词的执行顺序、正则表达式以及最重要的多表查询。
数据库增删改
增
insert [into] 表名[(可选字段名)] values(一组值1),(一组值2),...;
into 可以省略，表名后的字段可选，如果写了后面的 values 中的值必须与表名后的字段意义对应，如果没写后面的 values 中的值必须与表的所有字段一一对应，values后面可以给出多组值并用逗号隔开。
删
delete from 表名[where 条件];
如果条件不写的话是删除所有记录，不过这样删除表的效率很低，因为是一行行删除数据，自增的 id不会归零。使用truncate 是重建表，先记录表结构然后删除整个表再重新建表出来，自增的 id 会归零。
改
update 表名 set 字段名 = 值,[,字段2 = 值2],[where 条件];
可以一次性修改多个字段的值，值之间需要用逗号隔开；如果不写条件的话就是修改所有记录。
单表查询
不带关键字的查询
select (*|字段名|四则运算|聚合函数) from 表名 [where 条件];
准备数据
mysql> create table stu(id int primary key auto_increment,
name char(10),
math int,
english int);
mysql> insert into stu values(null,'赵云',90,30);
mysql> insert into stu values(null,'小乔',90,60);
mysql> insert into stu values(null,'小乔',90.60);
mysql> insert into stu values(null,'大乔',10,70);
mysql> insert into stu values(null,'李清照',100,100);
mysql> insert into stu values(null,'铁拐李',20,55);
mysql> insert into stu values(null,'小李子',20,55);
*** 表示查询所有字段**
mysql> select * from stu;
字段名 可以手动指定要查询的字段
mysql> select engish from stu;
字段的值可以进行加减乘除
统计总分
mysql> select math+ english from stu;
如果觉得 math+english 名字太长也可以取别名
mysql> select math+english [as] 总分 from stu;
as可以省略
给英语成绩加分
mysql> select english+10 from stu;
聚合函数 用于统计
什么是聚合函数，将多个数据进行计算，并得到一个结果，称为聚合
聚合函数：
注意不能再 where 后面使用聚合函数，因为 where 相当于打开文件然后读取文件中的数据，而使用聚合函数很显然需要多个值来聚合，那么没有读取完数据就使用不了聚合函数，所以这时候应该使用 on，所以聚合函数不能写在 where 后面,where会最先执行，它的作用是读取数据并过滤
- sum
mysql> select sum(salary) from emp;
- count
mysql> select count(*) from emp group by dept;
- avg
mysql> select avg(salary) from emp;
- max/min
mysql> select max(salary) from emp; mysql> select min(salary) from emp;
结合使用：
mysql> select dept,count(name) from emp group by dept;
where 是可选的
关键字的执行顺序
from
用于打开文件
distinct
去除重复数据，所有数据全都重复才算重复
mysql> select distinct * from stu;
where
对读取的数据进行过滤
where 后面跟的条件比较多：
between and
mysql> select * from stu where english between 70 and 80;
in
mysql> select * from stu where math in (89,90,91);
like
mysql> select * from stu where name like '李%';
and
mysql> select * from stu where math > 80 and english > 80;
mysql> select * from stu where math > 60 and english < 60;
group by
对数据进行分组，为了进行统计。group by 后面可以有多个分组依据，会按照顺序执行
准备数据
mysql> create table emp(
id int,
name char(10),
sex char,
dept char(10),
job char(10),
salary double);
mysql> insert into emp values(
(1,'刘备','男','市场','总监',5800),
(2,'张飞','男','市场','员工',3000),
(3,'关羽','男','市场','员工',4000),
(4,'孙权','男','行政','总监',6000),
(5,'周瑜','男','行政','员工',5000),
(6,'小乔','女','行政','员工',4000),
(7,'曹操','男','财务','总监',10000),
(8,'司马懿','男','财务','员工',6000)
);
查看所有部门
mysql> select dept from emp group by dept;
分组后，组里的详细记录就被隐藏起来了，不能直接查看，dept 分组就变成三条记录，每个组中会包含隐藏的记录，没办法显示，如果一定想显示的话，可以使用 group_concat(字段名)，可以将多个值拼接为一个值：
mysql> select dept,group_concat(name) from emp group by dept;
注意：只有出现在 group by 后面的字段，才可以通过 select 显示出来，其他的都被隐藏了。在 mysql5.6分组后会默认显示每组的第一条记录，5.7不显示，因为5.7中sql_mode中就是only_full_group_by
查看每个部分有多少人
mysql> select dept,count(name) from emp group by dept;
计算每个部门的平均薪资
mysql> select dept,avg(salary) from emp group by dept;
取别名
mysql> select dept,avg(salary) 平均工资 from emp group by dept;
计算每个岗位的平均工资
mysql> select job,avg(salary) from emp group by job;
计算每个部门每个岗位的平均工资
mysql> select dept,job,avg(salary) from emp group by dept,job;
查询平均工资大于5000的部门
mysql> select dept from emp where avg(salary) > 5000;
这样写是不行的，因为 where 相当于一条条从文件中读取数据，而数据没有读取完是不能做平均值计算的，这时候就需要用 having 了。
having
对分组后的数据进行过滤，作用与 where 相同，用于过滤。不通电在于，where 是从文件读取数据时的过滤条件，这导致了 where中不能使用聚合函数。
计算工资平均值大于5000的部门
mysql> select dept,avg(salary) from emp group by dept having avg(salary) > 5000;
总结：select 语法是有执行顺序的，按照从左到右的顺序执行，所以 where 会在执行完成之前用不了聚合函数
查询岗位平均薪资高于6000的岗位名称和平均薪资
mysql> select job,avg(salary) from emp group by job having avg(salary) > 6000;
查询部门人数少于3的部门名称人员名称和人员个数
mysql> select dept,group_concat(name),count(*) from emp group by dept having count(name) < 3;
order by
对结果排序
- asc 表示升序，是默认的
- desc 表示降序
- by 后面可以有多个排序
按照工资排序
mysql> select * from emp order by salary;
按照工资降序排序
mysql> select * from emp order by salary desc;
按照工资升序 id 降序排序
mysql> select * from emp order by salary,id desc;
按照工资升序 id 升序排序
mysql> select * from emp order by salary,id;
limit
指定获取数据条数
使用方法：
limit a,b;表示从 a 开始不包括 a，获取 b 个数据。
mysql> select * from emp limit a,b;
mysql> select * from emp limit 2,2;
分页查询计算页数
起始位置的算法
每页显示a条，现在是第 b 页，求起始位置
(b - 1) * a
字符串拼接
完整的select语句
mysql> select [distinct] * from 表名
[where
group by
having
order by
limit];
注意在书写时，必须按照这个顺序来写
正则表达式匹配
^ 匹配字段名称以'张'开头的数据
mysql> select * from emp where name regexp '^张';
$ 匹配字段名称以'飞'结尾的数据
mysql> select * from emp where name regexp '飞$';
. 匹配字段名称第二位后包含'飞'的数据，‘’.‘’表示任意字符
mysql> select * from emp where name regexp '.飞';
[abci] 匹配字段名称中含有指定集合内容的人员
mysql> select * from emp where name regexp '[张飞关羽刘备]';
[^alex] 匹配不符合集合中条件的内容，^表示取反
注意1：^只有在[]内才是取反的意思，在别的地方都是表示从开始处匹配
注意2：简单理解 name regexp '[^alex]' 等价于 name!='alex'
'a|x' 匹配条件中的任意值
mysql> select * from emp where name regexp '张飞|关羽';
查询以 张开头以飞结尾的数据
mysql> select * from emp where name regexp '^张.*飞$';
mysql 中使用 regexp 操作符来进行正则表达式匹配。
模式
^ 匹配输入字符串的开始位置
$ 匹配输入字符串的结束位置
. 匹配任何字符
[...] 字符集合。匹配所包含的任意一个字符。例如，'[abc]' 可以匹配 'plain' 中的 'a'
[^...] 负值字符集合。匹配未包含的任意字符。例如，'[^abc]' 可以匹配 'plain' 中的 'p'
p1|p2|p3 匹配 p1 或 p2 或 p3.例如，'z|food' 能匹配 'z' 或 'food'。'(z|f)ood' 则匹配 'zood' 或 'food'
多表查询
笛卡尔积查询
mysql> select * from 表1,...表n;
查询结果是将坐标中的每条记录与右表中的每条记录都关联以遍，假如 a 表有 m 条记录，b 表有 n 条记录，则笛卡尔积结果为 m*n。
数据准备
mysql> create table empl (id int,name char(10),sex char,dept_id int);
insert empl values(1,"大黄","m",1);
insert empl values(2,"老王","m",2);
insert empl values(3,"老李","w",30);
mysql> create table deptl (id int,name char(10));
insert deptl values(1,'市场');
insert deptl values(2,'财务');
insert deptl values(3,'行政');
mysql> select * from empl,deptl;
因为笛卡尔积查询会产生很多错误数据，所以需要经过筛选出正确的关联关系。
mysql> select * from empl,deptl where empl.dept_id = deptl.id;
内连接查询
就是笛卡尔积查询
mysql> select * from empl [inner] join deptl;
加上筛选条件
mysql> select * from empl [inner] join deptl where empl.dept_id = deptl.id;
左外连接查询
mysql> select * from empl left join deptl on empl.dept_id = deptl.id;
左表数据全部显示，右表只显示匹配上的数据。
on 关键词和 where 关键词都是用于条件过滤，没有本质区别。在单表中 where 的作用是筛选过滤条件；在多表中 where 连接多表，满足条件就连接，不满足就不连接。于是为了区分单表还是多表换用了 on 关键词。只要是连接多表的条件就使用 on。
右外连接查询
mysql> select * from empl right join deptl on empl.dept_id = deptl.id;
右表数据全部显示，左表只显示匹配上的数据。
内连接和外连接的理解：内指的是匹配上的数据，外指的是没匹配上的数据。
全外连接显示
mysql> select * from empl full join deptl on empl.dept_id = deptl.id; # mysql 不支持
合并查询结果(全外连接)
mysql> select * from empl left join deptl on empl.dept_id = deptl.id
union
select * from empl right join deptl on empl.dept_id = deptl.id;
union 会去除重复数据，且只能合并字段数量相同的表；如果不想去除重复数据，使用 union all
mysql> select * from empl left join deptl on empl.dept_id = deptl.id
union all
select * from empl right join deptl on empl.dept_id = deptl.id;
三表查询
数据准备
mysql> create table stul(id int primary key auto_increment,name char(10)); mysql> create table teal(id int primary key auto_increment,name char(10));
创建中间表
mysql> create table tsr(id int primary key auto_increment,
t_id int,s_id int,
foreign key(s_id) references stul(id),
foreign key(t_id) references teal(id));
mysql> insert into stul values(null,'张三'),(null,'李四');
mysql> insert into teal values(null,'musibii'),(null,'thales');
mysql> insert into tsr values(null,1,1),(null,1,2)(null,2,2);
查询 musibii 教过那些学生
mysql> select * from stul join teal join tsr
on stul.id = tsr.s_id and teal.id = tsr.t_id where teal.name = 'musibii';
多表查询总结：
- 把所有表连接起来
- 加上连接条件
- 如果有别的过滤条件，加上 where
子查询
当一个查询的结果是另一个查询的条件是，这个查询称之为子查询（内层查询）。
什么时候使用子查询？
当一次查询无法得到想要的结果时，需要多次查询。这样可以分多步查询减少查询的复杂度。
数据准备
mysql> create table emps(id int,name char(10),sex char,age int,dept_id int, job char(10),salary double);
mysql> insert into emps 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,'曹操','男',40,3,'总监',10000),
(8,'司马懿','男',46,3,'员工',6000);
mysql> create table depts(id int primary key,name char(10)); mysql> insert into depts values(1,'市场'),(2,'行政'),(3,'财务');
查询市场部人员
mysql> select * from emps where dept_id = (select id from depts where name = '市场');
子查询思路：
- 将一个复杂的问题 拆分为多个简单的问题
- 把一个复杂的查询 拆分为多个简单的查询
就比如查询部门人员：
- 查询部门 id
- 拿着 id 去员工表查询
mysql> select emps.name from emps join dept on dept.id = emps.dept_id where depts.name = '财务';
查询平均年龄大于26的部门名称
mysql> select * from depts where id in (select dept_id from emps group by dept_id having avg(age) > 26);
mysql> select depts.name from depts join emps on emps.dept_id = depts.id
group by depts.name having avg(age) > 26;
exists 关键词查询
exists 后跟子查询，子查询有结果时为 true，没有结果是为 false，为 true 时外层执行，为 false 时外层不执行
mysql> select * from emps where exists(select * from emps where salary > 1000);
综合练习
查询每个部门工资最高的员工信息
mysql> select * from emps join
# 使用子查询得到每个部门的 id 以及部门的最高工资，形成一个虚拟表把原始表和虚拟表连接在一起
(select dept_id,max(salary) as m from emps group by dept_id) as t1
# 如果这个人的部门编号等于虚拟表中的部门编号，并且这个人的工资等于虚拟表中的最高工资，就是要找的数据
on emps.dept_id = t1.dept_id and emps.salary = t1.m;
上一篇： 优酷ido是什么?优酷ido有哪些功能