欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

数据库(四)

程序员文章站 2022-06-12 08:09:17
前言 昨天学习了表与表之间的关系、多表关联、复制表、单表查询的知识,今天学习的内容是单表查询与关键词的执行顺序、正则表达式以及最重要的多表查询。 数据库增删改 增 into 可以省略,表名后的字段可选,如果写了后面的 values 中的值必须与表名后的字段意义对应,如果没写后面的 values 中的 ......

前言

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

数据库增删改

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;

数据库(四)