数据库（四）

前言

昨天学习了表与表之间的关系、多表关联、复制表、单表查询的知识，今天学习的内容是单表查询与关键词的执行顺序、正则表达式以及最重要的多表查询。

数据库增删改

增

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';

多表查询总结：

1. 把所有表连接起来
2. 加上连接条件
3. 如果有别的过滤条件，加上 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 = '市场');

子查询思路：

1. 将一个复杂的问题 拆分为多个简单的问题
2. 把一个复杂的查询 拆分为多个简单的查询

就比如查询部门人员：

1. 查询部门 id
2. 拿着 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;