MySQL_DQL操作

合集 - MySQL(3)

1.MySQL_DCL、TCL、索引、视图2023-09-07

2.MySQL_DQL操作2023-09-05

3.MySQL数据库2023-09-04

收起

一、DQL查询表中数据

select 字段 from 表名 [where 条件] [group by] [having 条件] [order by 字段] [limit offset,length]

执行顺序： from -> where 过滤-> group by 分组 -> having 过滤-> select -> order by 排序 -> limit 分页

1. 简单查询

查询不会对数据库中的数据进行修改, 只是一种显示数据的方式。

-- 语法格式：
select 字段名1,字段名2,... from 表名;

例：查询所有的员工信息, 将字段名显示为中文

select 
    eid as '编号',      -- as可以省略
    ename as '姓名',
    sex as '性别',
    salary as '薪资',
    hire_date as '入职日期',
    dept_name as '部门名称'
from emp;

查询有多少个部门（去重操作）

-- 使用去重查询 关键字 distinct
select distinct dept_name from emp;

2. 条件查询

• 在实际应用中, 通常会指定查询的条件, 对记录进行过滤

-- 语法格式：
select 列名 from 表名 where 条件;

注意：若判断某个字段是否为null  应该用 is(not) 不能用 =/!=

2.1 比较运算符

运算符	说明
> < <= >= = <> !=	大于 小于 小于等于 大于等于 等于 不等于
between...and...	显示在某一区间的值
in(集合)	集合表示多个值，使用逗号分隔，例如name in (悟空,八戒); in中的每个数据都会作为一次条件，只要满足条件就会显示
like '%张%'	模糊查询
is null	查询某一列为null的值，注意，不能写 = null

2.2 逻辑运算符

运算符	说明
and &&	多个条件同时成立
or ||	多个条件任一成立
not	取反

通配符

 

通配符	说明
%	表示匹配任意多个字符
_	表示匹配任意一个字符

3. 排序

关键字：order by

通过order by子语句, 可以将查询出的结果进行排序(排序只是显示效果, 并不会影响真实的数据）。

-- 语法格式：
select 字段名 from 表名 [where 条件] order by 字段名[asc | desc];
-- asc  升序(默认)
-- desc 降序

3.1 单列排序

只按照某一个字段进行排序。

3.2 组合排序

同时对多个字段进行排序, 如果第一个字段相同, 就按照第二个字段排序, 以此类推。

例：

　　 查询所有的员工信息, 使用salary升序排列,salary相同按照入职日期降序排序。

-- 组合排序
select * from emp order by salary, hire_date desc;

4. 函数

函数只是对查询结果中的数据进行处理，不会改变数据的值。

单行函数:

单行函数是指对每一条记录的值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。

常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数。

使用单行函数, 是对行中字段的操作, 操作多少行, 返回多少行数据。

多行函数:

我们之前做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用多行函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值。

• 多行函数会忽略null空值。

• 多行函数也称为分组函数, 聚合函数。

使用多行函数，是对某一列的值进行计算，然后返回一个单一的值

 4.1 单行函数

 

4.1.1 字符函数

 

大小写处理函数

函数	描述	实例
LOWER(s)|LCASE(s)	将字符串 s 转换为小写	将字符串 OLDLU转换为小写：SELECT LOWER("OLDLU"); -- oldlu
UPPER(s)|UCASE(s)	将字符串s转换为大写	将字符串 oldlu转换为大写：SELECT UPPER("oldlu"); -- OLDLU

字符处理函数

函数	描述	实例
LENGTH(s)	返回字符串 s 的长度	返回字符串oldlu的字符数SELECT LENGTH("oldlu"); -- 5;
CONCAT(s1,s2...sn)	字符串 s1,s2 等多个字符串合并为一个字符串	合并多个字符串SELECT CONCAT("sxt ", "teacher ", "oldlu"); -- sxt teacher oldlu;
LPAD(s1,len,s2)	在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len	将字符串 x 填充到 oldlu字符串的开始处：SELECT LPAD('oldlu',8,'x'); -- xxxoldlu
LTRIM(s)	去掉字符串 s 开始处的空格	去掉字符串 oldlu开始处的空格：SELECT LTRIM(" oldlu") ;-- oldlu
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1	将字符串 oldlu 中的字符 o 替换为字符 O：SELECT REPLACE('oldlu','o','O'); -- Oldlu
REVERSE(s)	将字符串s的顺序反过来	将字符串 abc 的顺序反过来：SELECT REVERSE('abc'); -- cba
RPAD(s1,len,s2)	在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len	将字符串 xx填充到 oldlu字符串的结尾处：SELECT RPAD('oldlu',8,'x'); -- oldluxxx
RTRIM(s)	去掉字符串 s 结尾处的空格	去掉字符串 oldlu 的末尾空格：SELECT RTRIM("oldlu "); -- oldlu
SUBSTR(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 OLDLU中的第 2 个位置截取 3个 字符：SELECT SUBSTR("OLDLU", 2, 3); -- LDL
SUBSTRING(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 OLDLU中的第 2 个位置截取 3个 字符：SELECT SUBSTRING("OLDLU", 2, 3); --LDL
TRIM(s)	去掉字符串 s 开始和结尾处的空格	去掉字符串 oldlu 的首尾空格：SELECT TRIM(' oldlu ');-- oldlu

#1 查询emp表所有数据, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x
select concat('编号:', eid), concat('姓名:', ename), concat('性别:', sex) from emp;

#2 查询emp表所有数据, 将ename第二个字符都换为 某
select eid, insert(ename, 2, 1, 某'), sex from emp;

#3 查询emp表所有数据, 显示ename的长度
select eid, ename, length(ename), sex from emp;

#4 查询emp表所有数据, 将 ename有英文的改为都是大写
select eid, ename, upper(ename), sex from emp;

#5 查询emp表所有数据, 将 ename有英文的改为都是小写
select eid, ename, lower(ename), sex from emp;

#6 查询emp表所有数据, ename只显示姓
select eid, ename, substring(ename, 1, 1), sex from emp; 

4.1.2 数值函数

函数	描述	实例
ABS(num)	返回num的绝对值	SELECT ABS(-1) -- 返回1
CEIL(num)	返回大于num的最小整数（向上取整）	SELECT CEIL(1.5) -- 返回2
FLOOR(num)	返回小于num的最大整数（向下取整）	SELECT FLOOR(1.5) -- 返回1
MOD(num1, num2)	返回num1/num2的余数（取模）	SELECT MOD(5,2) -- 1
PI()	返回圆周率的值	SELECT MOD(5,2) -- 1
POW(num,n)/POWER(num, n)	返回num的n次方	SELECT POW(2,3) -- 8
RAND(num)	返回0~1之间的随机数	SELECT RAND() --0.93099315644334
ROUND(num, n)	返回x四舍五入后的值，该值保留到小数点后n位	SELECT ROUND(1.23456,3) --1.235
TRUNCATE(num, n)	返回num被舍去至小数点后n位的值（与 ROUND 最大的区别是不会进行四舍五入）	SELECT TRUNCATE(1.23456,3) -- 1.234

MySQL提供了一张虚拟表，该表名为“dual”，是MySQL为了满足用“SELECT ••• from•••”的习惯而增设的一张虚拟表。

在使用dual表时，如果没有where子句，则可以省略“from dual”, 没有实际意义, 查询的字段不属于任何表的时候, 就可以使用dual这张虚拟表。

select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25) from dual;
select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25);

4.1.3 日期函数

 

函数	描述	实例
CURDATE()	返回当前日期	SELECT CURDATE(); -- 2022-07-20
CURTIME()	返回当前时间	SELECT CURTIME(); -> 19:59:02
NOW()	返回当前日期和时间	SELECT NOW() -> 2022-07-20 20:57:43
SYSDATE()	返回该函数执行时的日期和时间	SELECT SYSDATE() -> 2022-07-20 20:57:43
DAYOFYEAR(date)	返回日期date为一年中的第几天	SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315
WEEK(date)/WEEKOFYEAR(date)	返回日期date为一年中的第几周	SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6
DATEDIFF(date1, date2)	返回起始日期date1与结束日期date2之间的间隔天数	SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32
DATE()	从日期或日期时间表达式中提取日期值	SELECT DATE("2017-06-15"); -> 2017-06-15
DAY(d)	返回日期值 d 的日期部分	SELECT DAY("2017-06-15"); -> 15
MONTH(d)	返回日期d中的月份值，1 到 12	SELECT MONTH('2011-11-11 11:11:11') ->11
YEAR(d)	返回年份	SELECT YEAR("2017-06-15"); -> 2017

4.1.4 流程控制函数

间隔类型	描述
IF(condition, t, f)	如果条件condition为真，则返回t，否则返回f
IFNULL(value1, value2)	如果value1不为null，则返回value1，否则返回value2
NULLIF(value1, value2)	如果value1等于value2，则返回null，否则返回value1
CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 ...] [ELSE result] END	如果条件condition1为真，则返回result1，···，否则返回result

#1 查询emp表所有数据, 薪资 >= 10000 高工资  其他 低工资
select eid, ename, salary, if(salary >= 10000, '高工资', '低工资') from emp;

#2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k)
select eid, ename, salary, salary * 12+30000 '年薪' from emp;  -- 需要考虑null
select eid, ename, salary, ifnull(salary, 0) * 12+30000 '年薪' from emp;

#3 查询emp表所有数据, 薪资 >=3000 加把劲  >=5000 加油哦  >=9000 坚持住 >= 15000 优秀  其他 不及格
select eid, ename, salary, 
    case
        when salary >= 15000 then '优秀'
        when salary >= 9000 then '坚持住'
        when salary >= 5000 then '加油哦'
        when salary >= 3000 then '加把劲'
        else '努力奋斗吧骚年'
    end
from emp;

4.1.5 其他函数

函数	描述
DATABASE()	返回当前数据库名
VERSION()	返回当前MySQL的版本号
USER()	返回当前登录的用户名
INET_ATON(IP)	返回IP地址的数字表示
INET_NTOA	返回数字代表的IP地址
PASSWORD(str)	实现对字符串str的加密操作
FORMAT(num, n)	实现对数字num的格式化操作，保留n位小数
CONVERT(data, type)	实现将数据data转换成type类型的操作

4.2 多行函数

• 多行函数会忽略null空值。

• 多行函数也称为分组函数, 聚合函数。

多行函数	作用
count(字段)	统计指定列不为null的记录行数
sum(字段)	计算指定列的数值和
max(字段)	计算指定列的最大值
min(字段)	计算指定列的最小值
avg(字段)	计算指定列的平均值

4.3 经典面试题

需求：

把下面数据转换

 转换成下面的效果

 方式一：使用if(表达式，值1，值2)函数。如果表达式成立返回值1，否则返回值2。其中值可以是列。

select 
    max(if(subject='语文',score,0)) 语文,
    max(if(subject='数学',score,0)) 数学,
    max(if(subject='英语',score,0)) 英语,
    name 
from student group by name;

方式二：使用case 条件 when 条件取值 then 结果 ... else 结果 end

select 
    max(case subject when '语文' then score else 0 end) 语文,
    max(case subject when '数学' then score else 0 end)  数学,
    max(case subject when '英语' then score else 0 end)  英语,
    name 
from student group by name;

5. 分组

关键字：group by

分组查询指的是使用group by语句, 对查询的信息进行分组, 相同数据作为一组。

select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件];

分组的过程

注意事项:

• 分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作

• 查询其他字段没有意义

 若分组时需要进行条件过滤

　　可以通过where和having（where效率更高，但不是通用）

　　select语句的执行顺序 from -- where -- group by -- having –- select -- order by

过滤方式	特点
where	分组之前的过滤，后边不能写多行函数
having	分组之后的过滤，后边可以写多行函数

6. limit

作用:

• limit是限制的意思, 限制返回的查询结果的函数(通过limit函数，控制查询返回多少行数据)

• limit 语法是 MySql的方言, 用来完成分页

select 字段1, 字段2 ... from 表名 limit offset, length;

参数说明

1. offset 起始行数, 从0开始, 如果省略则默认从0开始, 0代表MySQL中第一条数据

2. length 返回的行数

二、SQL执行流程

 组件介绍

1. 连接管理与安全验证：MySQL有连接池（Connection Pool）管理客户端的连接。客户端连接后会验证用户名、密码、主机信息等

2. 缓存（Cache&Buffer）：缓存中存储了SQL命令的HASH，直接比对SQL命令的HASH和缓存中key是否对应，如果对应，直接返回结果，不再执行其他操作。由于缓存的是SQL的HASH，所以根据Hash特性SQL中空格等内容必须完全一样。缓存里面包含表缓存、记录缓存、权限缓存等。查询语句执行完成后会把查询结果缓存到缓存中。在MySQL中查询缓存默认不开启。考虑到查询缓存性能瓶颈问题，从MySQL8开始已经不支持查询缓存了。

3. 解析器（Parser）主要作用是解析SQL命令。将SQL命令分解成数据结构（解析树），后续的操作都是基于这个结构的。如果在分解过程中遇到错误，出现SQL解析错误。解析时主要检查SQL中关键字，检查关键字是否正确、SQL中关键字顺序是否正确、引号是否对应是否正确等。

4. 预处理器：根据解析器的解析树，进一步检查表是否存在、列是否存在、名字和别名是否有歧义等。

5. 优化器（Optimizer）：根据官网说明在执行SQL查询时会根据开销自动选择最优查询方案。采用“选择-投影-连接”的策略。先选择where中的行数。同时先选择要选择的列，而不是全部列，最后把内容合并到一起。

6. 执行器：包含执行SQL命令。获取返回结果。生成执行计划等。

7. 存储引擎：访问物理文件的媒介

详细说明

1. 客户端向服务器端发送SQL命令和连接参数

2. 服务器端连接模块连接并验证

3. 缓存模块解析SQL为Hash并与缓存中Hash表对应。如果有结果直接返回结果，如果没有对应继续向下执行。如果是MySQL 8 是没有查询缓存的。

4. 解析器解析SQL为解析树，检查关键字相关问题，如果出现错误，报SQL解析错误。如果正确，继续执行

5. 预处理器对解析树继续处理检查表、列别名等，处理成功后生成新的解析树。

6. 优化器根据开销自动选择最优执行计划，生成执行计划

7. 执行器执行执行计划，访问存储引擎接口

8. 存储引擎访问物理文件并返回结果

9. 如果开启查询缓存，缓存管理器把结果放入到查询缓存中。

10. 返回结果给客户端

三、多表

由于单表的限制，会存在冗余的代码，所以可以采用多表

表之前的关系：

表与表之间的3种关系	举例
一对多关系(最常见)	员工表和部门表 学生表和班级表
多对多关系	学生表和课程表 用户表和角色表
一对一关系(使用较少)	一对一的关系可以设计成一张表

1. 外键

外键指的是在从表中与主表的主键对应的的那个字段

作用：

　　使用外键约束可以让两张表之间产生一个对应关系, 从而保证主从表数据的完整性。

1.1 创建外键约束

1. 创建表时

constraint 外键名 foreign key(外键字段名) references 主表(主键字段名);

2.创建表后

alter table  从表名 add constraint 外键名 foreign key 外键字段名 references 主表名(主键字段名);

1.2 删除外键约束

语法格式

alter table 从表名 drop foreign key 外键约束名;

1.3 使用外键约束的注意事项

语法：constraint 外键名 foreign key 外键字段名 references 主表(主键字段名)

1. 外键的类型必须和主表主键类型一致；

2.操作从表时，添加或修改后的外键值必须在主表的主键中存在；

3.操作主表时，删除或修改主表的数据时

　　　　删除：从表中的外键关联值设置为null或直接删除关联值；

　　　　修改：从表中的外键关联值设置为null、或者直接删除关联数据、或者将从表中的关联值同步修改为主表中主键的新值。

1.4 设置外键约束的注意

级联操作

选项	作用
RESTRICT(默认)	如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
No action	同RESTRICT, 都是立即检查外键约束
CASCADE	在主表上update/delete记录时，同步update/delete掉从表匹配的记录
SET NULL	在主表上update/delete记录时，将子表上匹配记录的列设为null 注意: 子表的外键列不能为not null

①.删除时
　　1.RESTRICT(默认) No action：作用类似，都会检查外键约束；
　　2.setNull：删除主表中数据时，将关联从表中的外键值设置为null；
　　3.cascade：删除主表中数据时，将关联从表中的数据删除；
②.变更时
　　1.RESTRICT(默认) No action：作用类似，都会检查外键约束；
　　2.setNull：变更主表中主键值时，将关联从表中的外键值设置为null；
　　3.cascade：变更主表中主键值时，将关联从表中的外键值变为新的主键值。

四、多表查询

1. 笛卡尔积

交叉连接（CROSS JOIN）是对两个或者多个表进行笛卡儿积操作，所谓笛卡儿积就是关系代数里的一个概念，表示两个表中的每一行数据任意组合的结果。

笛卡尔积不管是否匹配，都连接。没有实际意义，有理论意义 。

select 字段名 from 表1 cross join 表2; 

2. 分类

大致分类两种连接：

1.内连接

2.外连接

3. 内连接

　　根据条件匹配两张表的数据，显示匹配到的数据，对于没有匹配到的数据不显示。

方式一：SQL92 隐式内连接

select 字段名 from 表1 [别名],表2 [别名] where 条件;

方式二：SQL99 显式内连接

select 字段名 from 表1 [别名] [inner] join 表 2 on 条件;

如果on  条件后面还有其他条件 就用where、and

非等值连接

  非等值连接就是两张表进行关联的时候不是使用关联的id相等进行连接的。

例：

slagrade表

 emp1表

 需求：查询每个员工工资及其对应的工资等级

select empno,ename,sal,grade from emp1,salgrade where sal between losal and hisal;

 

4. 外连接

通过指定的条件去匹配两张表中的数据, 匹配上就显示, 匹配不上也可以显示。

外连接分为 左外连接、右外连接。

4.1 左外连接

语法格式

select 字段 from 表1 left [outer] join 表2 on 条件;

outer可以省略

特点

1. 以左表为主, 左表中的数据全部显示；

2. 右表匹配到数据就显示匹配到的数据；

3. 右表没有匹配到的数据显示为null。

4.2 右外连接

 语法格式

select 字段 from 表1 right [outter] join 表2 on 条件;

特点

1. 以右表为主, 右表中的数据全部显示；

2. 左表匹配到数据就显示匹配到的数据；

3. 左表没有匹配的数据显示为null。

4.3 全外连接

 MySQL不支持FULL OUTER JOIN 连接

可以通过union实现全外连接

• UNION: 可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了 DISTINCT。

• UNION ALL: 只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据， 那么返回的结果集就会包含重复的数据了。

-- 语法结构
(SELECT  列名 FROM 表名 LEFT OUTER JOIN  表名 ON  连接条件) 
UNION 
(SELECT  列名 FROM 表名 RIGHT OUTER JOIN  表名 ON  连接条件)

5. 自连接

一张表，自己连接自己

上述的内外连接都是从表的连接方式出发的，而自连接侧重表的数量。

自连接可以通过内连接或者外连接实现。

例

emp表

需求：查询员工及其对应的领导

select e.ename, m.ename from emp e, emp m where e.mgr=m.empno;