本文共 7363 字，大约阅读时间需要 24 分钟。

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 张三   男    十五中  上海     2104. '码' 解释:        (学生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.idunion  -- 去重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)。

1. A语句可以是select ,update,delete等语句,其中最常见的是select语句
2. 如果A语句也是select语句, 称之为主查询,外层查询(main query)
3. 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. 行子查询（一行） 少见

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 '长度';--执行结果为: 7SELECT CONCAT('I','love','you');--执行结果为: IloveyouSELECT LOWER('ITTEST');--执行结果为: ittestSELECT UPPER("ittest ");--执行结果为: ITTESTSELECT SUBSTR("ittest ",1,2);--执行结果为: itSELECT TRIM(' ittest ');--执行结果为: ittest

5.2 数字函数

示例：

SELECT RAND(); -- 返回0-1之间的随机数 0.21809973867433122SELECT ROUND(3.1415926,2) ; -- 执行结果: 3.14SELECT LEAST(13, 14, 521, 74, 1); -- 执行结果: 1SELECT 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()); -- 返回当前日期中的日

5.4 高级函数

转载地址：http://zfowb.baihongyu.com/