MySQL学习总结（三）

星灭233

于 2021-06-27 22:36:10 发布

阅读量202

点赞数

分类专栏： # MySQL 文章标签：后端 mysql 数据库

本文链接：https://blog.youkuaiyun.com/ksx2333/article/details/118229424

版权

MySQL 专栏收录该内容

3 篇文章

订阅专栏

1 数据库范式

1.1 什么是范式

范式是指：设计数据库表的规则(Normal Form)。

好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储。

满足这些规范的数据库是简洁的,结构清晰的, 在增删改查的时候,不容易发生数据异常。

1.2 范式的基本分类

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

1.3 第一范式

即数据库表的每一列都是不可分割的原子数据项，而不能是集合、数组、记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中每个列的值只能是表的一个属性或一个属性的一部分。简而言之，第一范式每一列不可再拆分，称为原子性。
第一范式：每一列不能再拆分(原子性: 不可分割)
例：学习时间（开始，结束）拆分为：开始学习时间，结束学习时间。才满足第一范式。

总结：如果不遵守第一范式，查询出数据还需要进一步处理（查询不方便）。遵守第一范式，需要什么字段的数据就查询什么数据（方便查询）。

1.4 第二范式

# 第二范式(2nf)
1. 在满足第一范式的前提下, 才能继续满足第二范式
2. 第二范式含义:
        表中非主属性不能部分函数依赖于码,必须要完全函数依赖
        作用: 避免数据冗余
3. 表的举例
        学生id, 姓名, 性别, 学校, 学校地址, 高考分数
        350001 张三   男    一中   福州     430
        350002 李四   女    一中   福州     350
        021001 王五   男    十五中  上海     200
        350001 张三   男    十五中  上海     210
4. '码' 解释:
        (学生id) -> 得出 姓名,性别,学生,学生地址
        (学生id,学校) -> 得出 高考分数
        在这里,码有(学生id,学校)
5. '非主属性' 解释:
        不是'码'的字段: 姓名,性别,学生,学生地址,高考分数
6. '函数依赖'
    a. 函数依赖
        要知道高考分数, 必须要知道学生id和学校
        高考分数 函数依赖于 学生id和学校
    a. 完全函数依赖
        码(学生id,学校), 要知道高考分数,必须要知道学生id和学校高考分数
           高考分数完全函数依赖于码
    b. 部分函数依赖
           码(学生id,学校), 姓名 只依赖于 学生id
           姓名部分函数依赖于码
# 解决: 拆成两张表
       (学生表:
           码(学生id), 其他非主属性完全函数依赖于码
       )
        学生id, 姓名, 性别, 学校, 学校地址
        350001 张三 男 一中 福州
        350002 李四 女 一中 福州
        021001 王五 男 十五中 上海
        350001 张三 男 十五中 上海
        (成绩表:
            码(学生id,学校), 非主属性(高考分数)完全函数依赖于码
         ) 
        学生id, 学校, 高考分数
        350001 一中 430
        350002 一中 350
        021001 十五中 200
        350001 十五中 210

1.5 第三范式

在满足第二范式的前提下, 才能继续满足第三范式。
第三范式含义：
在第二范式的前提下,消除传递依赖
作用: 消除数据冗余

1.6 反三范式

反三范式相对于三范式的，没有冗余的数据库未必是最好的数据库，有时为了提高数据库的运行效率，就必须降低范式标准，适当保留冗余数据。

具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加适当的字段，减少了查询时的关联，提高查询效率，因为在数据库的操作中查询的比例要远远大于DML的比例。但是反范式化一定要适度，并且在原本已满足三范式的基础上再做调整。

2 多表_连接查询

同时查询多张表获取到需要的数据。

2.1 笛卡尔积

概念：左表的每条数据和右表的每条数据组合成新的数据。（两张表记录的乘积）

-- 查询2张表结果是2张表记录的乘积，称为笛卡尔积
select * from emp,dept;

这样就会有没用的数据。需要消除笛卡尔积。
消除笛卡尔积：条件是从表.外键=主表.主键
消除笛卡尔积，确定连接条件 emp.dept_id = dept.id
两张表只需要一行连接条件，三张表需要两个连接条件，四张表需要三个连接条件…

例：查询员工和对应的部门
select * from emp,dept where emp.dept_id = dept.id;
-- 这就是隐式内连接，使用where，没有用到join...on

-- 给表起别名
select * from emp e ,dept d where e.dept_id = d.id;
-- 4. 查询员工孙悟空和对应的部门名字
select * from emp e ,dept d where e.dept_id = d.id and e.name = '孙悟空';
-- 只查询孙悟空的员工名字和部门名字，并指定别名：员工名、部门名
select e.name 员工名,d.name 部门名 from emp e ,dept d where e.dept_id = d.id and e.name = '孙悟空';

2.2 内连接

（求两张表的交集）
语法格式：

	-- 效果 : 求两张表的交集
	-- 隐式内连接语法
		-- select 列名 from 左表,右表 where 从表.外键=主表.主键

	-- 显示内连接, on后面就是表连接的条件  更加推荐使用显示内连接（理由未知...）
		-- select 列名 from 左表 inner join 右表 on 从表.外键=主表.主键

2.3 外连接

外连接分类：

2.3.1 左外连接

左外连接： 左表不动，并上右表与之交集的部分，如果右表没有对应的数据，显示null。
左外连接的结果 = 内连接结果（交集） + 左表特有的内容（差集）。
左表中所有的记录都出现在结果中，并上右表与之对应的部分, 如果右表没有匹配的记录，使用NULL填充。

语法格式：select 列名 from 左表 left join 右表 on 从表.外键=主表.主键

2.3.2 右外连接

右外连接： 右表不动，并上左表与之交集的部分，如果左表没有对应的数据，显示null。
右外连接的结果 = 内连接结果（交集） + 右表特有的内容（差集）。
右表中所有的记录都出现在结果中，并上左表与之对应的部分, 如果左表没有对应的记录，使用NULL填充。

语法格式：`select 列名 from 左表 right join 右表 on 从表.外键=主表.主键`

如果A表和B表中都没有特殊的数据，那么内连接和外连接的效果是一样的。

2.3.3 全外连接（扩展）

-- 左右表的数据都不动, 并上交集数据
	-- 语法: 表a(左表) full outer join 表b(右表) on 条件 -- mysql不支持  oracle支持
	-- mysql中实现：左外 + 右外  去重

例：

select * from dept d
	left join emp e on e.dept_id = d.id
union  -- 去重
select * from dept d
	right join emp e on e.dept_id = d.id

– 总结:
– 1. 如果两张表完全交集, 内连和外连效果是一样的
– 2. 一张表有另一张不交集的数据(差集), 用外连可以体现这些数据

select查询顺序

    select 查询列表 七
    from 表1 别名   一
    连接类型 join 表2 二
    on 连接条件       三
    where 筛选        四
    group by 分组列表  五 -- 如果是聚合函数的条件 需要having
    having 筛选        六
    order by排序列表     八
    limit 起始条目索引，条目数; 九

3 子查询

什么是子查询

子查询定义：B语句作为A语句的一部分,B语句是select查询语句,那么B语句称之为子查询,内层查询(子集,subquery)。

A语句可以是select ,update,delete等语句,其中最常见的是select语句
如果A语句也是select语句, 称之为主查询,外层查询(main query)
B语句可以写在 select,from,where/having,exists 后面,其中最常见是where

注意点：
1）子查询必须写在()中
2）子查询一般放在 = 右边
3）如果是标量子查询，一般会使用 > < >= <= = <>
4）如果是列子查询一般会使用 in any/some all

select name from emp where salary = (select max(salary) from emp); -- 推荐
select name from emp where (select max(salary) from emp) = salary; -- 不推荐

子查询分类

按结果集的行列数不同

    1. 标量子查询: 返回的结果是一个数据(单行单列)
    2. 列子查询: 返回的结果是一列(多行单列)
    3. 行子查询: 返回的结果是一行(单行多列)
    4. 表子查询: 返回的结果是一张表(多行多列)

按子查询出现的位置

   1. select 后面: (少见)
       a. 仅支持标量子查询
           子查询的结果直接出现在结果集中
   2. from 后面:(有用)
       a. 支持表子查询
   3. where或having后面: (重要)
       a. 标量子查询(单行单列) 常见
       b. 列子查询(多行单列) 常见
       c. 行子查询
   4. exists后面(相关子查询: 有用)
       都支持, 一般是表子查询

按关联性分(扩展)

   1. 非相关子查询
       a. 含义: 独立于外部查询的子查询 (子查询可以独立运行)
       b. 执行: 子查询的执行优先于主查询执行,并且只执行一次，执行完将结果传递给外部查询
       c. 效率: 较高
           举例: select * from A where A.id in （select id from B）
   2. 相关子查询
       a. 含义: 依赖于外部查询的数据的子查询
       b. 执行: 子查询的执行的次数依赖于外部查询，外部查询每执行一行，子查询执行一次
           解释: 子查询中查询条件依赖于外层查询中的某个值，所以子查询的处理不只一次，要反复求值，以供外层查询使用. 所以相关子查询执行时, 子查询的执行和外层查询的执行是相互交叉的.
       c. 效率: 较低
           举例: select * from emp e1 where exists (select * from emp e2 where e1.empno=e2.mgr);

3.1 where或having之后`（重要）`

支持的子查询类型：

标量子查询（一个数据）常见
列子查询（一列）常见
行子查询（一行）少见

    1. 子查询放在小括号内
    2. 子查询一般放在条件的右侧
    3. 使用注意点
        a. 标量子查询，一般搭配着单行操作符使用
            > < >= <= = <>
        b. 列子查询，一般搭配着多行操作符使用
            in、any/some、all

3.2 select之后`（了解）`

支持的子查询类型：仅支持标量子查询(一个数据)。

3.3 from之后

支持的子查询类型：支持表子查询(一张表)
特点：
1）子查询要求必须起别名(相当于把子查询的结果当成一张表,取个名字,方便被引用)
2）若子查询中使用了聚合函数,必须取别名, 外部语句引用时会报错
例：

1. 查询每个部门的id,name和对应的员工个数(需求同上,换种写法)
-- a. 从员工表按dept_id分组得到对应的员工个数
-- b. 把上一次查询结果当成一张表进行连接查询,得出结果
select dept_id,count(dept_id) as 人数
   from emp
      where
         group by dept_id;
-- 注意: 这里count(dept_id) 必须取别名
-- 如果不取别名, temp.count(dept_id) 这样的写法是错误的
select dept.*,temp.人数
      from (select dept_id,count(dept_id) as 人数 from emp group by dept_id) as temp
         inner join dept
            on temp.dept_id = dept.id;

3.4 exists之后

语法格式：exists(完整的查询语句)
特点：exists子查询往往属于相关子查询。
结果：返回1或0 (true或false)。
结果解释：其实可以把exists看成一个if判断, 判断的是子查询是否有结果,有结果返回1,没有结果返回0。
举例:

-- 子查询有结果返回1(相当于true)
select exists(select * from emp where salary > 1000);
-- 子查询没有结果返回0(相当于false)
select exists(select * from emp where salary > 10000);

应用 : 这里的案例没有实际意义,就是让大家掌握语法和产生的结.果。

select * from emp where 0; -- 当条件为0的时候,不返回查询结果
select * from emp where exists(select * from emp where salary > 10000);
select * from emp where 1; -- 当条件为1的时候,返回查询结果
select * from emp where exists(select * from emp where salary > 1000);

4 多表查询总结

不管我们查询几张表，表连接查询会产出笛卡尔积，我们需要消除笛卡尔积，拿到正确的数据。我们需要找到表与表之间通过哪个字段关联起来的（通常是外键=主键）。

消除笛卡尔积规律：2张表需要1个条件，3张表需要2个条件，4张表需要3个条件。（条件数量=表的数量-1），每张表都要参与进来。

多表连接查询步骤：
确定要查询哪些表
确定表连接条件
确定查询字段

5 MySQL常用函数

5.1 字符串函数

在这里插入图片描述
示例：

SELECT CHAR_LENGTH('ittest ') AS '长度';
--执行结果为: 7
SELECT CONCAT('I','love','you');
--执行结果为: Iloveyou
SELECT LOWER('ITTEST');
--执行结果为: ittest
SELECT UPPER("ittest ");
--执行结果为: ITTEST
SELECT SUBSTR("ittest ",1,2);
--执行结果为: it
SELECT TRIM(' ittest ');
--执行结果为: ittest

5.2 数字函数

在这里插入图片描述
示例：

SELECT RAND(); -- 返回0-1之间的随机数 0.21809973867433122
SELECT ROUND(3.1415926,2) ; -- 执行结果: 3.14
SELECT LEAST(13, 14, 521, 74, 1); -- 执行结果: 1
SELECT GREATEST(13, 14, 521, 74, 1); -- 执行结果: 521

5.3 日期函数

在这里插入图片描述
示例：

SELECT NOW(); 或 SELECT SYSDATE(); -- 返回系统的当前时间: 年-月-日 时:分:秒
SELECT CURDATE(); -- 返回系统当前日期: 年-月-日
SELECT CURTIME(); -- 返回系统当前时间: 时:分:秒
SELECT YEAR(NOW()); -- 返回当前日期中的年份
SELECT MONTH(NOW()); -- 返回当前日期中的月份
SELECT DAY(NOW()); -- 返回当前日期中的日