# MySQL

[TOC]

# SQL 语句的分类

  • DQL (data query language): 查询语句，凡是 select 语句都是 DQL
  • DML(data manipulation language): insert delete udpate , 对表当中的数据进行增删改
  • DDL(data definition language)： create drop alter ，对表结构的增删改
  • TCL(transaction control language): commit 提交事务， rollback 回滚事务
  • DCL(data control language): grant 授权， revoke 撤销权限等

# MySQL commands

show databases; /*show all the databases*/

use ...; /*choose the database*/

show tables; /*show tables in the database*/

show tables from ...(another database name); /*show tables in another database*/

show create table ...(tablename); /*show the sql statements used to create the table*/

explain ...(任何SQL语句)； /*查看sql语句的执行计划*/

select database(); /*show current databse you are using*/

select version(); /*show current version of MySQL*/

exit;  /*exits MySQL*/

# 导入数据

  • 登录 mysql in cmd
  • choose a database by the use command in mysql
  • then “source …(directory of your sql file that you want to import)”
    • this file ending in .sql is a sql script containing sql statements, using source command runs the sql script

# SQL 语句

drop database ...(database name); /*deletes the database*/

# 注意：

接下来的具体 database，table，数据等都是由 oracle 官方网站下载的练习数据

  • 任何一条 sql 语句以 “;” 结尾
  • sql 语句不区分大小写，but the commands e.g. like select is in lower case
  • 字符串需要‘’扩起来

# DQL

# 查看表结构：

desc/describe dept; /*describes the structure(the column headings(字段名),数据结构等) of the "dept" table in database */

# 查询，计算以及命名字段名：

select ename,sal*12 as annualsalary from emp;
select ename,sal*12 as ’年薪‘ from emp; 
/*shows the ename and sal column with all data in sal column multiplied with 12 from emp, the sal column heading is named as annualsalary when shown*/

结果 ->sal column 里面的数据都 * 12:

image-20211215143141120

as 也可以省略：

select ename,sal*12 as ’年薪‘ from emp; /*still works*/
# 查看表的所有数据：
select * from emp; /*shows all data from the emp table*/

# 条件查询

select

​ …(column heading(s))

from

​ …(table name)

where

​ …(conditions);

# 找等于
select ename from emp where sal = 5000; /*show the ename data of the employee who has salary equal to 5000 from the emp table*/

select sal from emp where ename = 'SMITH'; /*show the ename data of the employee who has the name SMITH from the emp table*/

the ename column is defined to be varchar type (字符串), hence has to use single quotes

# 找不等于

find the name of employee who has salary not equal to 3000:

select ename from emp where sal<>3000;
/*selects the ename where sal is bigger of smaller than 3000, hence not equal to 3000*/

select ename from emp where sal ! = 3000;
# 找之间
select ename, sal from emp where sal> = 1000 and sal < = 3000；

select ename, sal where sal between 1100 and 3000;
/*"between...and..." is inclusive*/
/*"between...and..." 必须左小右大*/
# is null

在数据库里，NULL 不是一个值，代表什么都没有，为空。

空不是一个值，所以不能用等号衡量

必须使用 is null 或者 is not null :

select ename, sal, comm from emp where comm is null；
/*ename, sall and comm data correspondind to the employee who has null for comm*/
/*THIS DOES NOT INCLUDE 0 because 0 is not null*/

select ename, sal, comm from emp where comm is not null；
/*ename, sall and comm data correspondind to the employee who don't have null for comm*/
/*THIS DOES INCLUDE 0 because 0 is not null*/

0 是个值，只不过是 0。 Null 不是一个值。

所以去找哪些人没有津贴或者津贴为 0 的：

select ename, sal, comm from emp where comm is null or comm = 0;
# and , or

找到薪资大于 1000 的并且部门编号是 20 或者 30 的员工：

select ename,sal,deptno from emp where sal>1000 and deptno=20 or deptno=30; /*错误示范！！！ 这里会选薪资大于1000并且部门20号的员工，也会显示所有薪资的30号部门的员工*/

select ename,sal,deptno from emp where sal>1000 and (deptno=20 or deptno=30);/*正确示范*/

and takes priority before or，brackets take priority over all

记得不想出错，要加上 brackets。

# in , not in

in 的功能和 or 一样， 不是区间！是具体的值

select ename, job from emp where sal in(800,1000);
/*找出的数据对应的是sal=800和sal=1000,不是之间！！！*/

select ename from emp where job in('SALESMAN','MANAGER')；
/*找出Manager和salesman*/

not in 就是不在这几个值当中：

select ename from emp where job is not in('SALESMAN','MANAGER')；
/*找出不是Manager和salesman的*/
# 模糊查询 “like”

% 代表任意多个字符

_ 代表任意一个字符

找出第二个字母是 A 的：

select ename from emp where ename like '_A%';
/*remember single quotes!*/

找出名字中有下划线的：

select ename from emp where ename like '%\_%';

使用 \ 进行转义

# 排序

select

​ …(column heading(s))

from

​ …(table name)

order by

​ …(column heading (s) 以此用来决定排序，可用__数字或者字符串__)

​ asc/desc (什么都不填默认升序，否则 desc 代表降序);

# 升序
select ename, sal from emp order by sal; /*按照sal里面值升序排然后显示ename，sal column*/
select ename, sal from emp order by sal asc;/*升序*/
# 降序
select ename, sal from emp order by sal desc;/*降序*/
# 结合使用

按照工资降序排列，当工资一样再按照名字的升序排序：

select ename, sal from emp order by sal desc, ename asc;

前面的字段起到的主导作用更大，后面字段只有在前面字段的数值相等的时候才会被启用。

# 跟 where 结合使用的顺序

找出 salesman 的员工，然后按照薪资降序排列

select ename, job, sal from emp where job=’ALESMAN’ order by sal desc;

执行顺序是：

先执行 from 部分

再执行 where 部分

再执行 select 部分

然后再执行 order by 部分

select ename, sal as salary from emp order by salary;/*可以执行*/
/*这里证明了select部分是比order by部分先执行了，因为只有select部分先执行然后进行了别名“as”,salary这个别名已经起好了，之后order by才会识别salary这个别名，不然不会工作*/

# 分组函数 (group function)

分组函数只有接下来的五个

  • count 记数
  • sum 求和
  • avg 平均
  • max 最大值
  • min 最小值

所有分组函数都是对__某一组__数据进行操作

select count(ename) from emp; /*finds number of employees*/
select sum(sal) from emp; /*finds sum of salary*/
select avg(sal) from emp; /*finds average of salary*/
select max(sal) from emp; /*finds max of salary*/
select min(sal) from emp; /*finds min of salary*/

可以组合起来用：

select count(*), sum(sal), avg(sal), max(sal), min(sal) from emp;
image-20211215173736663

分组函数也可以被称为__多行处理函数__

多行处理函数的特点：输入多行，最终输出结果是一行

# 分组函数自动忽略 NULL:
select count(comm) from emp;
/*会忽略comm为null的数据，只count不是null的*/

image-20211215172023486

# 分组函数和单行处理函数区别

单行处理函数：输入一行，输出一行。输入多行，输出多行。

计算每个员工的年薪错误示范：

select ename, (sal+comm)*12 as yearsal from emp;

结果：

image-20211215161547038

_数据库当中：只要是数据表达当中有 NULL 出现，最后结果一定是 NULL!!!_

但如果是多行处理函数，会自动忽略 NULL，所以直接用也不用担心结果会时 NULL.

计算每个员工的年薪正确示范：

使用 if null(可能为null的数据，被当成什么处理) 空处理函数

select ename (sal + if null(comm,0))*12 from as yearsal emp;
/*this if null() method will change any NULL value in COMM to 0 which you have set*/

if null() 是单行处理函数

# 使用多行处理函数

找出工资高于平均工资的员工错误示范：

select ename, sal from emp where sal>avg(sal);
/*报错！！语法错误！*/

SQL 语句不允许分组函数出现在 where 部分

找出工资高于平均工资的员工正确示范：

select avg(sal) from emp; /*find average salary*/
select ename, sal from emp where sal>...(the average salary amount);

或者：

select ename, sal from emp where sal > (select avg(sal) from emp)；
/*also works, emphasis on the bracket, always executed first*/
# count(*) 和 count(字段名) 的区别
  • count(*) 表示你选的表的数据行数 (所以有没有 null 也不影响，一直都是全部行数，number of rows, 和某一个字段无关)
  • count(字段名) 表示你选的那个字段 (column) 有多少数据（不包括 NULL）

# group by 和 having

  • group by 是按照某_个_字段或者某_些_字段进行分组
  • having 是对分组之后的数据再次过滤

案例，找出每个工作岗位的最高薪资：

select max(sal),job from emp group by job;

这里 group by 先执行，然后按照你给的那个 column 而进行分组，然后再会算 max(sal)

注意：

  • 当一条 sql 语句没有 group by 的话，整个表的数据会自成一组

  • 分组函数一般都会和 group by 联合使用，这也是为什么他被成为分组函数的原因

  • 并且任何一个分组函数 (count sum avg max min) 都会在 group by 语句之后执行

group by 是在 where 执行之后才会执行的，所以需要在 where 之后进行分组，就算没有 group by 他还是会默认在 where 之后进行分组只不过只是所有的数据自成一组。 如果还没分组就用分组函数当然不行，所以这就是为什么分组函数不能用在 where 部分。

# 使用 group by

当一条语句中有 group by 的话，select 后面只能跟参加__分组的字段 (column) 以及__分组函数，其他的都不要跟！！！

select ename, max(sal), job from emp group by job;
/*这里MySQL会执行，但是加入ename完全没有意义*/
/*如果在Oracle运行这行代码会报错*/
# 多个字段联合使用分组

案例 -> 找出每个部门不同岗位的最高薪资：

select max(sal) from emp group by deptno, job;

这里会把每个部门每个岗位跟同一部门的同一岗位联合成为一组

# 使用 having

案例 -> 找出每个部门的最高薪资，要求显示薪资大于 2900 的数据：

select max(sal),deptno from emp group by deptno having max(sal) > 2900;

having 在 group by 之后进行再次挑选，所以可以使用分组函数

但是这里用 having 效率并不好，好不容易分组了，然后又要丢弃一些分好组的数据

更好的办法:

select deptno,max(sal) from emp where sal >2900 group by deptno;

where 先执行，过滤掉不想要的数据，然后进行 group by 分组

案例 -> 找出每个部门的平均薪资，要求显示薪资大于 2000 的数据：

select deptno, avg(sal) from emp group by deptno having avg(sal) >2000；

这里因为 where 部分不可以用分组函数，所以不可以使用 where ，只能用 having

注意！ having 部分只能用分组函数，因为已经分好组了，要是用某行数据 (比如说 sal) 完全没有意义。、

having 的作用就是用分组函数来给分好组的数据来进行再一次的过滤，跟之后想要 select 部分选择展示什么无关

having 是 group by 搭档，只有用了 group by 才可以用 having

# 总结完整的 DQL 语句以及顺序

select … from … where … group by … having … order by …

执行顺序是：

先执行 from 部分

再执行 where 部分

再执行 group by 部分

再执行 having 部分

再执行 select 部分

然后再执行 order by 部分

# 去除重复 (duplicates)

select distinct job from emp; /*use distinct*/

distinct 只能出现在所有字段的最前面，这个很 obvious，如果不是的话 makes no sense

比如说：

select ename. distinct job from emp;  /*错误！！！！*/

正确用法：

select distinct deptno,job from emp;  
/*distinct加在前面代表之后的字段会联合然后进行去重，每个部门里的每个岗位只会显示一个，可能会显示重复部门，可能会显示重复岗位，但不可能重复部门和岗位 */

结合函数使用：

select count(distinct job) from emp;/*统计不同岗位的数量 */

# 连接查询（SQL JOINS）

实际开发中，一般是多一个业务对应多张表，我们需要这些表 (tables) 联合查询出最终的结果

如果全都是存在一张表，有些数据会有大量的重复，会导致数据的冗余 (redundancy)，浪费空间。

所以需要 relational database, 数据库里的表 (tables) 之间互相都有关系。

# 连接查询的分类
  • 内连接（Inner Join）
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • 左外连接 (左连接)（Left (Outer) Join）
    • 右外连接 (右连接)（Right (Outer) Join）
  • 全连接image-20211215215102141
# 笛卡尔积现象 (Cross Join)

案例 -> 找出每一个员工的部门名称，要求显示员工名和部门名

注意：emp 表里有员工名称和部门编号，dept 表里有部门编号和部门名字等

​ image-20211215220358988 <-emp dept-> image-20211215220552118

select ename,dname from emp, dept;
/*dname is the column heading in dept table*/
/*this does not give expected result*/

以上方式是__错误的__，他显示的结果会是 56 个

每个 emp 里面的 ename 会跟 dept 表里的每个 dname 进行匹配，所以会是 14*4=56 条

这个就是__笛卡尔积现象__（两个表连接没有任何条件限制，最终查询结果会是两个表的记录条数的乘机）

以上代码结果：

image-20211215222134616

image-20211215221416571
# 多表时使用别名

我们一般也不会像上面写的那样，而是会用别名

比如说上方的错误代码:

select e.ename,d.dname from emp e, dept d;

这么做的好处是

  • 执行效率高：如果不加比如说 e.ename 而是用 ename，会去 emp 表里找，也会去 dept 表里找，这样效率低，而且两个表有重名会搞乱
  • 可读性好
# 解决笛卡尔积线程

加条件进行过滤，以此来避免笛卡尔积现象。

注意：虽然避免了笛卡尔积现象，但匹配次数还是一样 (像之前的例子，还会是 56 个结果)，只不过显示的是有效记录

案例 -> 找出每一个员工的部门名称，要求显示员工名和部门名正确示范，但是这个方法已经过时，现在不用！

select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;

最正确方法:

select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
# 内连接（Inner Join）
# 1. 等值连接

最大的特点是: 条件是等量关系

案例 -> 找出每一个员工的部门名称，要求显示员工名和部门名

select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno;

语法: …A (第一个表和起的别名) inner join B (第二个表和起的别名) on 连接条件 where …

在老版的方法中， e.deptno=d.deptno 被加到了 where 部分，容易和其他关于数据过滤的搞混

在这个方法中结构更清晰，表的连接条件跟 where 部分分离了。(两种办法结果都一样)

注意： inner 可以省略，带着 inner 是为了可读性好一些

select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
# 2. 非等值连接

最大的特点: 连接条件中的关系是非等量关系

案例 -> 找出每一个员工的工资等级，要求显示员工名、工资、工资等级

​ image-20211215234437757 <-emp salgrade-> image-20211215234505156

这里这两个表之间没有相同的字段，不加条件又会产生笛卡尔积现象。

所以需要加条件通过拿 sal 跟 LOSAL 和 HISAL 来比较决定属于哪个 GRADE。

select e.ename,e.sal,s.GRADE from emp e inner join salgrade s on e.sal between s.LOSAL and s.HISAL; 
/*ALWAYS REMEMBER TO ADD (e.g.)"e" and "s" etc for each column used to indicate which table the columns belong to hence to improve efficiency and readibility*/
/*inner 可以省略*/
# 3. 自连接

最大的特点: 一张表看做两张表，自己连自己

案例 -> 找出每一个员工的上级领导，要求显示员工名和对应的领导名

image-20211215235724180

目前 mgr 字段都是存的是每个员工对应的领导的 empno

这是一张员工表，也可以是一张领导表，因为领导也是员工。

所以在这里的表连接条件是 emp 的 mgr=emp 的 empno。

select e.ename as '员工名字', b.ename as '领导名字' from emp e inner join emp b on e.mgr = b.empno;
/*别名的重要性！！！如果没有别名肯定会搞混*/

以上代码结果会显示 13 个结果，不包括 king，因为 king 的 mgr 为 null，没有对应的 empno 所以 king 没有作为员工显示出来， inner join…on 基本上跟 where 一样，如果不对应都会过滤掉，所以不会显示

这就是内连接，如果找不到匹配的，就不会找了，结果就不会显示 king 在员工中

# 外连接（Outer Join）

外连接和内连接的区别:

image-20211216001431396

  • 左外连接

左边的这张表是主表

  • 右外连接

右边的这张表是主表

左连接有右连接的写法，右连接有左连接的写法

案例 -> 找出每一个员工的上级领导，并且可以也展示出 king (不像上面 inner join 那样不显示)

select e.ename as '员工', b.ename as '领导' from emp e left outer join emp b on e.mgr = b.empno;

这里使用 left join 就是左连接，这里会把左边这个 emp e 当做主表，主表当中的数据是无论如何都要查出来的，也就意味着 select 部分只要有 e.（字段名） 那这个字段中所有的数据就一定会显示出来 (按照主表 emp e 的样子)，就算表的连接并没有匹配到。 而且副表没匹配上的那个数据会自动以 NULL 进行匹配。(也就是会显示 null)

image-20211216003455001

当然，很明显，这个左连接也可以有右连接的写法:

select e.ename as '员工', b.ename as '领导' from emp b right outer join emp e on e.mgr = b.empno; /*改为right join，然后将emp e改到后面作为右表，这样子右连接跟之前都是一个道理*/

注意:outer 也可以省略！

但是 left 和 right 不可以，区分内连接和外连接靠 left 和 right 就足够了，所以 inner 和 outer 可以省略

select e.ename as '员工', b.ename as '领导' from emp e left join emp b on e.mgr = b.empno;

select e.ename as '员工', b.ename as '领导' from emp b right join emp e on e.mgr = b.empno;

实例 -> 找出哪个部门没有员工

select d.dname from emp e right join dept d on d.deptno=e.deptno where e.deptno is null;

这里，dept 作为主表，emp 作为副表，然后匹配他们两个的 deptno 的数据，结果就是:

image-20211216005745619

除了 OPERATIONS 部门，其他所有的都按照连接条件成功连接，每个主表 dept 的 deptno 都会匹配到__所有__对应的副表上的 deptno。

就算没有匹配对对应的，那就会让副表那一 row 所有数据都模拟出 NULL 跟他匹配。(就像上图的 OPERATIONS 部门)

这样子最后在 where 部分就可以测副表里面平时不可能为 null 的值是不是 null，如果不是 null 的就过滤掉。

然后 select 想看的内容。

这里必须使用外连接，因为使用内连接会排除掉匹配不上的，这样子之后也不会显示出来。

外连接最重要的特点就是: 主表的数据无条件的全部查询出来！

# 全连接

既有左连接又有右连接，左边也是主表，右边也是主表，两个表就算匹配不上也不能耽误彼此的数据查询，但是实际开发中几乎不用

# 个人总结 (废话连篇):

​ 有两张表 AB, 这两张表连接，A 表的第一行会跟 B 表的第一行，第二行，第。。。行匹配一直到 B 表结束。

接着 A 表的第二行也会进行同样的操作。 这里可以想象两张表的所有 columns 的在一起形成了一个大 table，然后 table 里就是 A 表第一行数据和 B 表第一行数据，A 表第一行数据和 B 表第二行数据，A 表第一行数据和 B 表第三行数据等。 这个匹配是必成的不管是用 where 还是内连接外连接，这就是笛卡尔积现象。

为了避免笛卡尔积现象 (虽说是避免但不管怎么样他还是已经匹配好了，我们能做的只是过滤不想要的数据)，我们使用 where 或者内连接外连接来从已经匹配好的所有数据中过滤掉不想要的，这里过滤规则就是连接规则，可以是任何样子的 (等值，非等值，自连接的等值和非等值)，自己决定。

这个过滤使用 where 和内外连接的 join 都一样的，只不过内外连接的 join 结构更清晰。

可以想象这个过滤过程就是看这个已经匹配好的大 table 然后每一行 (row) 每一行的看数据，然后如果这一行的数据根据你的连接规则是不对的，那么那一行就会过滤掉。

这里内连接就是看每一行然后根据你的连接规则如果那行数据不对就会过滤掉 (普通，跟使用 where 的道理一样)，所有符合连接规则的都会保留，这里可能会有一个表的数据重复比如说:

image-20211216013340414

但是绝对不会保留按照连接规则不对的那一行数据

而外连接就是会把你选的主表当做绝对要存在的，主表当中的所有 columns 的数据都需要存在，其中一些数据如果符合连接规则，就会保留 (不被过滤掉) 那一行匹配好的数据，跟内连接一样也可能是重复，完全看数据和连接规则

但是，如果主表里的一个数据按照连接规则并不能保留任何一行匹配好的数据 (全都被过滤掉了), 那么仍然会保留__一行__那个主表里的数据只不过对应的副表都会是 null 值。

然后就 select 从过滤好数据的表中选择想看的。

# 三张表的连接查询:

案例 -> 找出每一个员工的部门名称以及工资等级:

select e.ename, d.dname, s.GRADE from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.LOSAL and s.HISAL;

语法: select … from A join B on … join C on …

多个表，会先将 A 表和 B 表匹配然后按照第一个 on 的连接规则来从匹配好的数据里过滤掉不想要的，然后这个过滤好的结果会和 C 表结合，然后再按照第二个 on 的连接规则来从这个匹配好的数据里过滤掉不想要的，最后按照 select 部分展示。 当然这里的 join 可以是任何的 join。

案例 -> 找出每一个员工的部门名称，工资等级以及上级领导:

select e.ename,d.dname,s.GRADE,b.ename from emp e left outer join emp b on e.mgr = b.empno join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.LOSAL and s.HISAL;

注意这个 left outer join 必须给 emp b 用，因为要是不在这用 KING 就会被过滤掉 (像之前讲的那样)，之后再 join 其他表肯定 KING 相对应的数据都不会再有。

# 子查询（SQL subquery）

A sql subquery is a query that is nested inside a SELECT , INSERT , UPDATE , or DELETE statement, or inside another subquery

子查询可以出现的地方:

  • select …(select).
  • from …(select).
  • where …(select).
# where 字句中使用子查询

案例 -> 找出高于平均薪资的员工信息:

select * from emp where sal > (select avg(sal) from emp);
# from 字句中使用子查询

案例 -> 找出每个部门平均薪水的薪资等级:

select t.*,s.grade from (select deptno,avg(sal)as avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.LOSAL and s.HISAL;
/*注意别名和括号*/

可以把 select 查询出来的当做是一个表称为 t，然后就正常拿这张表进行操作，就用 t 代表这个表，然后之后把其他剩下的写完了就可以把 t 转换回去到做他的 select 语句 (记得最好把有分组函数是字段名的改掉换成别的不然不能用)，然后这个 t 可以继续当这个表的别名。

这个是最常用的！！！

# select 字句中使用子查询

案例 -> 找出每个员工所在的部门名称，要求显示员工名和部门名:

select e.ename, d.dname from emp e join dept d on e.deptno=d.deptno; /*之前展示的方式*/

/*也可以像下面这样做，但不经常这么做*/
select e.ename, (select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

这种方式是先让 emp 表取好了别名 e 然后 select 他的 e.ename 和那个括号中的 select 语句的结果。

那个括号就是从 dept 表取出 d.dname 按照 where 部分的规则，这里省掉用 join 因为已经知道了 emp 表的别名，可以直接用。这个其实就是比如说 emp 表的第一行是 SMITH，他的 deptno 是 20，那么在括号中的 select 语句中会跟 dept 表中 deptno 是 20 的那行数据匹配并保留，然后就可以直接读取 dept 表中的 dname.

以此往下类推。

# UNION

将查询结果集相加

案例 -> 找出工作岗位是 salesman 和 manager 的员工:

/*之前做法*/
select ename, job from emp where job in ('MANAGER','SALESMAN');
select ename, job from emp where job = 'MANAGER' or job ='SALESMAN';

/*使用UNION*/
select ename, job from emp where job = 'MANAGER' union select ename, job from emp where job = 'SALESMAN'

以上例子可能 union 用起来有些没必要，但有时比如说用两张不相关的表数据想一块显示那么 or 和 in 可能就不行，然后 union 却可以

image-20211216151538786 
select ename, sal from emp union select dname from dept; /*报错,两个的select语句最后显示的columns数量不相等*/

注意：如果 union 的两个有不同数量的 columns__结果会报错__

# Limit

注意: limit 是 mysql 特有的，其他数据库中没有，不通用。

作用: limit 取结果集中的部分数据

语法机制: limit startIndex/起始位置, length/取几个

案例 -> 取出工资前五名的员工 (思路：降序取五个)

select ename, sal from emp order by sal desc limit 0,5;
/*这个前面负责降序排好然后limit从0(第一条数据)取五个就是结果*/

select ename, sal from emp order by sal desc limit 5;
/*这样也行，如果limit后面只填写一个数字，那么那个数字前面默认就是0，也就是从第一条数据开始*/

limit 是 sql 语句最后执行的环节:

select(5)from(1)where(2)group by(3)having(4)order by(6)limit(7);

​ order by 在 select 之后执行， limit 在 order by 之后执行

# 通用的标准分页 sql

比如说一张网页每次只显示 3 条记录，那么 limit 的参数就是:

  • 第一页: 0,3
  • 第二页: 3,3
  • 第三页: 6,3
  • 第四页: 9,3
  • …

这其中有规则，那就是如果每页需要显示 pageSize 条记录，那么:

​ $$ 第 pageNo 页: (pageNo-1)* pageSize, pageSize$$

pageSize 是每页显示多少条记录

pageNo 是显示第几页

这么做就可以比如说在浏览器每页显示的数据条数是固定的，然后用户选择一个页面，那个页面的页数就会用来计算 limit 的 startIndex 参数

# DDL

# 创建表

# 语法格式

create table 表名 (

​ 字段名 1 数据类型 (可能还会有约束),

​ 字段名 2 数据类型 (可能还会有约束),

​ 字段名 3 数据类型 (可能还会有约束),

​ …

);

注意：表名一般建议以:t_或者 tbl_开始

# MySQL 当中字段的数据类型
  • int 整数型 ->(java 中的 int)
  • bigint 长整型 ->(java 中的 long)
  • float 浮点型 ->(java 中的 float double)
  • char 定长字符 ->(java 中的 String)
  • varchar 可变长字符串 ->(java 中的 StringBuffer/StringBuilder)
  • date 日期类型 ->(对应 java 中的 java.sql.Date 类型)
  • BLOB 二进制大对象 ->(存储图片、视频等流媒体信息) binary large object (对应 java 中的 Object)
  • CLOB 字符大对象 ->(存储较大文本，比如，可以存储 4G 的字符串) character large object (对应 java 中的 Object)(如果字符串太长，varchar/char 存不了就用这个)
    • BLOB 和 CLOB 不能靠 insert 插入而是需要 java 的 IO 流，但是一般情况下不会用 BLOB 和 CLOB，因为不会把太大的存进表里，一般操作就是把大的存进云盘里，然后存在表里的是 url 或者路径
  • …
# char 和 varchar 怎么选择？
  • char 在创建表的时候需要给个值作为 size，然后之后传进那个字段的字符串如果长度超过了 size 就会报错，如果小于 size 会存但是还是分配的空间还是按照那个 size
  • varchar 在创建表的时候需要给个值作为 size，然后之后传进那个字段的字符串如果长度超过了 size 就会报错，如果小于 size 会他会按照传进来的字符串的长度来分配长度

在实际开发中，如果某个字段的数据长度不发生该表的时候，是定长 (char) 的，例如：性别等–> 采用 char

如果某个字段的数据长度确定，例如：简介，姓名等–> 采用 varchar

实例 -> 创建学生表:

create table t_student(
	no double(7,2), /*7代表7个有效数字(有效数字,不为0的数字为有效数字),2代表小数位的个数*
	name varchar(255),
     sex char(1) default 1, /*这个default就是说如果之后insert数据这一个字段没给数据会给这个default值，如果这里不改default值，那么default值就是NULL*/
     classno char(1),
     birth char(10)
);
# create 和 as insert... 实现复制效果

语法: create table 表名 as DQL (select 语句)；

将查询结果当做表创建出来

create table emp1 as select * from emp;

这里的 select 会是 emp 所有的内容，然后把这所有内容复制给了创的新表 emp1

create table emp2 as select empno, ename from emp;

这里创的新表 emp2 内容会是复制来自 emp 表的字段 empno，ename

# 约束 (Constrain)

可以给任何一个字段添加任何约束，然后如果之后填入这个表的数据不符合这个约束的，那么就填入失败。这么做就可以确保表中数据的合法性，有效性，完整性。

常见的约束:

  • 非空约束 (not null) -> 约束的字段不能为 null
  • 唯一约束 (unique) -> 约束的字段不能重复
  • 主键约束 (primary key) -> 约束的字段__既不能为 null 也不能重复__
  • 外键约束 (foreign key) -> 约束的字段
  • 检查约束 (check): 注意 Oracle 数据库有 check 约束，但是 mysql 没有
# not null 约束
create table t_user(
	id int,
	username varchar(255) not null,
     password varchar(255)
); /*creates table with the username column as with "not null" constrain*/

insert into t_user(id,password) values(1,`123`);
/*这里插入会报错*/
/*ERROR 1364 (HY000): Field 'username' doesn't have a default value*/

这里在创建表的时候给 username 字段加了非空约束，所以之后要是

# unique 约束

唯一约束修饰的字段具有唯一性，不能重复，但可以是 NULL。(NULL 可以重复)

create table t_user(
	id int,
	username varchar(255) unique
);

username 字段给了唯一约束，之后插入只能给没有重复的值 (或者给 NULL，可以重复)，或者不给值默认是 NULL。NULL 值可以重复！

给多个字段唯一约束:

create table t_user(
	id int,
     usercode varchar(255) unique，
	username varchar(255) unique
);

usercode 和 username 字段不能有重复值，可以有 NULL，NULL 可以重复

多个字段的联合唯一约束:

create table t_user(
	id int,
	usercode varchar(255)，
	username varchar(255)，
     unique(usercode,username)
);

这么做的话 usercode 和 username 的__结合__不能为重复，可以是 NULL，NULL 可以重复。usercode 和 username 字段之间有一个字段有重复的值也可以，只要是 usercode 和 username 字段的__结合__不重复就行。

# primary key 约束 (主键约束)

主键约束的字段的值__不能为 NULL__，也不能重复

每个表只能有一个主键约束！！！！！！！！！！！！！！！！！

create table t_user(
	id int primary key,
	username varchar(255)
);

这里这个 id 字段里面存的值不可为 NULL 或者重复的

以上例子中

主键约束: primary key

主键字段: id 字段

主键值：所有插入 id 字段的值

主键的作用:

​ 放在表中可以当做每一行数据的标识，每一行数据都有自己独特的标识，就像身份证一样。

主键的分类:

  • 根据主键字段的字段数量类划分
    • 单一主键 -> 推荐的，一般都用这个
    • 复合主键 -> 多个字段联合起来添加一个主键约束 (还是一个主键约束)，不建议使用
  • 根据主键性质来划分
    • 自然主键 -> 推荐这种方法，用和业务没有任何关系的自然数，比如说就单纯 1,2,3,…
    • 业务主键 -> 主键值和系统的业务挂钩，比如说拿着银行卡的卡号做主键，拿着身份证号做主键。这种方式不建议用，因为以后一旦业务发生改变，主键值就随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

使用表级约束方式定义主键:

create table t_user(
	id int,
	username varchar(255)，
     primary key(id)
);

跟上方方式结果一样

MySQL 提供的主键值自增:

create table t_user(
	id int primary key auto_increment,/*让这个主键字段自增*/
	username varchar(255)
); 
insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('c');
insert into t_user(username) values('d');
insert into t_user(username) values('e');
insert into t_user(username) values('f');

id 字段设置为主键字段而且还自增，之后插入数据就不需要给 id 字段值了，会默认从 1 开始给你每行插入的数据的 id 字段给上一个值

image-20211219030800514

# foreign key 约束 (外键约束)

一般开发中都会是多表而不是所有数据存在一个表里 (会 get redundant)，而外键约束就是用来帮助多个表之间连接的那些字段。

语法 (create 语句中): foreign key(当前子表的外键字段) references 引用的父表(父表中引用的字段)

外键值只能是引用的字段里面有的值，或者是 NULL, 可以重复，没影响

外键字段引用的字段不需要是主键字段，⭐️但必须是有唯一性 (Unique)⭐️, 不然都不知道引用的是哪一个值。

外键约束: foreign key

外键字段：添加有外键约束的字段

外键值：外键字段中的每一个值

例子 -> 维护学生和班级的数据:

image-20211219032644769

t_student 中的 cno 字段 (可以是任何名不用特意取一样的) 引用 t_class 中的 cno 字段，此时__t_student 表叫做子表，t_class 表叫做父表。__

顺序要求:

  • 创建表 -> 先创建父表，再创建子表
  • 添加数据 -> 先添加父表，再添加子表
  • 删除数据 -> 先删除子表，再删除父表
  • 删除表 -> 先删除子表，再删除父表
drop table if exists t_student;
drop table if exists t_class;

create table t_class(
	cno int primary key,
	cname varchar(255)
);

create table t_student(
	sno int primary key,
	sname varchar(255),
     classno int,
     foreign key(classno) references t_class(cno)
);

这么做之后子表 t_student 里的 classno 字段里的值不能给父类 t_class 的 cno 字段里没有的值。插入其他值就会报错

​ ⭐️⭐️⭐️⭐️⭐️注意！外键值可以为 NULL!⭐️⭐️⭐️⭐️⭐️

# 删表

drop table if exists 表名；

如果这个表存在，就删掉

# 更改表结构

这个语法不会在这里讲，因为一般开发来说很难需要改表结构，就算要改也可以使用像 Navicat 这种工具。

注意我们学到的大部分 DQL 和 DML 都会在 java 代码中写，所以有必要学。而修改表结构的语句不会出现在 java 代码当中。

# DML

# 插入数据 insert

语法格式:

​ insert into 表名 (字段名 1，字段名 2，字段名 3，。。。) values (值 1，值 2，值 3，。。。)

要求：字段的数量和值得数量相同，并且数据类型要对应相同。

insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban','1950-10-12')；

/*顺序不是固定的，只需要确定字段名和输入的value同一位置*/
insert into t_student(name,sex,classno,birth，no) values('zhangsan','1','gaosan1ban','1950-10-12',1)；/*跟上面的一样*/

/*也可以只插入一部分*/
insert into t_student(name) values('zhangsan')； 
/*只不过这么干会让这一行(row)除了column name给了值'zhangsan'之外，其他所有的字段的值都会被设为默认值也就是NULL。          除非你把default值已经改成了其他的
注意如果想给这行其他字段再插入数据不能用insert，insert只会重新再给你来插入一行按照你填写的*/

当一条 insert 语句执行成功之后，表格当中必然会多一行记录。

即使多的这一行记录当中某些字段是 NULL, 后期也没有办法再执行 insert 语句插入数据了，只能使用 update 进行更新

# 如果不加括号和字段名
/*不加括号和字段名也可以，只不过需要values部分顺序和数量跟表里的所有字段的顺序和数量一致才行*/
insert into t_student values(1,'zhangsan','1','gaosan1ban','1950-10-12')；

/*如果数量不一致，就会报错！！！ 错误:number of columns does not match*/
insert into t_student values(1,'zhangsan')；/*错误的*/
# 一次插入多行数据
insert into t_student(no,name,sex,classno,birth) values(3,'rose','1','gaosi2ban','1925-12-13'), values(4,'dada','1','gaosi3ban','1953-12-20');
# insert into 和 select 联合使用

语法: insert into 表名 DQL (select 语句)；

将查询结果当做数据插入表里

create table dept1 as select * from dept; /*查询dept里的所有字段然后把这个查询结果当做表创建出来*/
insert into dept1 select * from dept; /*查询dept里的所有字段然后把这个查询结果当做数据插入dept1表里*/

很明显，如果查出来的字段与你想插入的表的字段数量或者类型不符就不行

# 修改数据 update

语法: update 表名 set 字段 1 = 值 1，字段 2 = 值 2…where 条件；

注意: 没有条件整张表数据全部更新

案例 -> 将部门 10 的 LOC 修改为 SHANGHAI，将部门名称修改为 RENSHIBU:

update deptno set loc = 'SHANGHAI', dname = 'RENSHIBU' where deptno = 10;
# 更新所有记录
update dept set loc = 'x', dname = 'y';

这里没有用 where 给条件，就会把 dept 表里的__loc 字段所有数据__改成 `x` 然后__dname 字段所有数据__改成 `y`

# 删除数据 delete

语法: delete from 表名 where 条件；

注意: 没有条件全部删除

案例 -> 删除 10 部门的数据:

delete from dept where deptno = 10;
# 删除所有记录
delete from dept;

这样所有表里的数据都会被删掉，但是表的结构字段名，数据类型等都不会被删掉，就像刚建好这个表的时候一样。

# 删除大表
truncate table emp;

一般使用 delete 删除特别大的表里的数据会很慢，而且并不是真正释放之前创建的时候 assign 表的空间，所以这时候，如果非常确定要删除，就可以用 truncate ，注意一旦用 truncate ，表会被阶段，不可回滚，永久丢失。

# 行级锁

# 悲观锁 (Pessimistic lock)

特点：事务必行排队执行。数据锁住了，不允许并发.

select ename, job, sal from emp where job = 'MANAGER' for update;

这个后面加上的 == for update == 会把前面这个__所 select 出来的这些记录 (包括那些记录的那一行的数据) 会在原来的表里锁住__, 这个样子，只要__当前锁住这些数据的事务没有结束的时候__, 别的事务无法对这写锁住的记录 (那一整行数据) 进行修改操作.

注意！就算 select 出来的只是 ename,job,sal 字段，但__选出来的数据的在原表的一整行都会锁住，而不是只是那一行显示出来的字段，(我的理解是) select 只是从原表过滤等等等然后选择显示展示出来的哪个字段.__

所以那整行 (row) 都有锁，所以成为__行级锁__

# 乐观锁 (Optimistic lock)

特点：事务支持并发，事务也不需要排队，只不过需要一个版本号.

多线程并发，都可以对乐观锁锁住的数据进行修改，只不过那些被乐观锁锁住的每一行数据都会有一个版本号，别的线程可以来改不过改了之后版本号也要改，这个样子别的线程需要用这行的数据一看版本号不是想要的或者不对，就可以回滚等

image-20211224035354454

# 存储引擎

描述的是表的存储方式，在 MySQL 里不同的存储引擎有不同的表的存储方式

完整的建表语句其实是这么写的:

create ...(
	...
) ENGINE=InnoDB DEFAULT CHARSET=utf8

一般建表时候不特意设置那么 InnoDB 是__默认的__存储引擎，utf8 是__默认的__字符集

也可以自己指定存储引擎，字符集

# MyISAM 存储引擎

这个引擎是 MySQL 最常用的，但不是默认的。

使用三个文件来表示每个表

  • 格式文件 - 存储表结构的定义 (xxx.frm)
  • 数据文件 - 存储表行的内容 (xxx.MYD)
  • 索引文件 - 存储表上索引 (xxx.MYI)

优点: 这么做可以被压缩，节省存储空间。并且可以转换为只读表，提高索引效率。

缺点: 不支持事务

# InnoDB 存储引擎

这种存储引擎在 MySQL 数据库崩溃之后提供自动恢复机制

InnoDB 还支持级联删除和级联更新

  • 表的结构存储在 xxx.frm 文件中

  • 数据存储在 tablespace 这样的表空间中，无法被压缩，无法转换成只读

优点: 支持事务，行级锁，外键等。这种存储引擎数据最安全

缺点: 无法被压缩，无法转换成只读

# MEMORY 存储引擎

优点: 查询速度最快

缺点: 不支持事务，数据容易丢失，因为所有数据和索引都存储在内存当中

# 事务 (Transaction)

一个事务是一个完整的业务逻辑单元，不可再分。

和事务相关的语句__只有 DML 语句 (insert, delete, update)__，其他都不相关，因为只有 DML 语句都是和数据库表当中的数据相关的。

事务的存在__是为了保证数据的完整性，安全性。__

比如说：银行账户转账，从 A 账户向 B 账户转账 10000, 需要执行两条 update 语句

update t_act set balance = balance - 10000 where actno = `A`;
update t_act set balance = balance + 10000 where actno = `B`;

以上两条 DML 语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。

为了确保两个 DML 语句同时成功，或者同时失败，就需要使用到数据库的 “事务机制”。

image-20211220000150330

# 事务的原理

image-20211220000540140

提交事务或者回滚事务都会导致事务的结束.

如果是__提交事务__ commit ，就会把这些操作给持久化到硬盘上，让硬盘上的__数据彻底改变__，然后以上的所有操作历史__都会被清空。__

如果是__回滚事务__ rollback , 并不会和硬盘上的文件打交道，但也会让以上的所有操作历史__都会被清空__

这么做就可以保证所有操作要么同时成功，要么同时失败。

# 事务的四大特性 (ACID)

  1. Atomicity -> 原子性：事务是最小的工作单元，不可再分
  2. Consistency -> 一致性：事务必须保证多条 DML 语句同时成功或者同时失败
  3. Isolation -> 隔离性：事务 A 与事务 B 之间具有隔离，为了保证数据安全
  4. Durability -> 持久性：最终数据必须持久化到硬盘文件中，事务才算成功的结束

# 隔离性

事务隔离性存在隔离级别，__理论上__隔离级别包括 4 个 (不过一般都是从第二级别起步的)：

  • 第一级别：读未提交 (read uncommitted)

    • 对方事务还没有提交，我们当前事务可以读取到对方未提交的数据

    • 读未提交存在脏读 (Dirty Read) 现象：表示读到了脏的数据

    ​

  • 第二级别：读已提交 (read committed)

    • 对方事务提交之后的数据我方可以读取到
    • 这种隔离级别解决了：脏读现象
    • 读已提交存在的问题是：不可重复读，对方一提交我们就读到新的，之后读到的跟之前我们读到的数据不一样了

  • 第三级别：可重复读 (repeatable read)

    • 不会读对方提交的，只要在一个 DOS 窗口，你之前用来查看数据和对方提交数据之后再在同一个 DOS 窗口查看，都会是一样的
    • 这种隔离级别解决了：不可重复读问题
    • 读已提交存在的问题是：读取到的数据是幻象，要是其他事务已经提交那么在硬盘的文件里数据就已经改了，就算你 DOS 窗口看还是跟之前一样】

  • 第四级别：序列号读 / 串行化读

    • 事务排队，一个事务没有结束时其他事务都必须排队
    • 这种隔离级别解决了：解决了以上问题
    • 读已提交存在的问题是：效率低

Oracle 数据默认的隔离级别是：读已提交

MySQL 数据默认的隔离级别是：可重复读

# 使用事务

MySQL 事务默认情况下是自动提交的，只要执行任意一条 DML 语句则提交一次

insert into t_user(username) values(‘zs’); /*插入数据*/
rollback; /*往回滚*/

这里显示的表示已经是带有这个插入的数据的， rollback 作用是显示最近一次事务提交的提交点，所以这里显示的还是带有插入这个数据就证明每个 DML 语句默认自动提交事务。

# 使用 start transaction; 关闭自动提交机制

继续以上的案例，现在表里的 username 字段已经有了‘zs’这个值。

start transaction;
insert ......（插入数据）
insert ......（插入数据）

insert ......（插入数据）
insert ......（插入数据）
insert ......（插入数据）
rollback；

因为最后回滚了，就会返回到上次提交事务的时候，这里有 start transaction (开启事务) 关闭了 DML 语句默认的事务自动提交，所以回滚就是回滚到还只是有‘zs’那一行数据的。

# 使用 commit; 提交事务:

start transaction;
insert ......（插入数据）
insert ......（插入数据）

insert ......（插入数据）
insert ......（插入数据）
insert ......（插入数据）
commit; (提交事务，让数据永久化到硬盘文件中)
rollback；

commit 就是提交事务，所以这里就算 rollback ，之后再用 select 来看这个表，显示的会是插入这些数据的结果

# 索引

添加索引是给某一个字段或者说某些字段添加索引

什么是索引？

​ 索引就像一本书的目录，通过目录可以快速找到对应的资源

在数据库方面，查询一张表的时候有两种检索方式:

  1. 全表扫描
select ename from emp where ename = 'SMITH';
/*ename字段没有添加索引，就会进行全表扫描，扫描ename字段所有的值*/
  1. 根据索引检索 (效率很高)

索引为什么可以提高检索效率？

​ 其实最根本的原理就是缩小了扫描的范围

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，需要数据库不断的维护。是有维护成本的

比如：表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护

所以什么时候给字段添加索引？

​ 1. 数据量庞大 (根据客户的需求，根据线上的环境)

​ 2. 该字段很少的 DML 操作 (因为字段进行修改操作，索引也需要维护，所以少点 DML 操作修改的好)

​ 3. 该字段经常出现在 where 子句中 (经常根据哪个字段查询)

# 添加，删除索引:

create index 索引名称 on 表名(字段名);

drop index 索引名称 on 表名;

注意: 主键和具有 unique 约束的字段会自动添加索引

所以根据主键查询效率较高，尽量根据主键检索。

create index emp_sal_index on emp(sal);
/*在emp表里的sal字段上创建了一个索引叫做emp_sal_index,之后就会按照索引在sal字段搜索*/

drop index emp_sal_index on emp;/*删除这个索引*/

# 索引实现原理

image-20211220172036101

索引底层实现逻辑是 B + 树

通过 B Tree 缩小扫描范围，底层索引进行了排序，分区，索引__会携带数据在表中的 “物理地址”__, 最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的

select ename from emp where ename = `SMITH`;

/*通过索引转换为*/
select ename from emp where 物理地址 = 0x3;

# 索引的分类

  • 单一索引：给单个字段添加索引
  • 复合索引：给多个字段来联合起来添加 1 个字段
  • 主键索引：主键上会自动添加索引
  • 唯一索引：有 unique 约束的字段会自动添加索引
  • …

# 索引什么时候失效

select ename from emp where ename like `%A%`;
/*这样根本不行,根本不知道第一个字母是谁,所以就算ename字段加了索引还是会做全表扫描*/

模糊查询的时候，第一个通配符使用的是 %, 这个时候索引是失效的.

(也有其他的失效时候…)

# 视图 (view)

视图是站在不同的角度去看到数据 (同一张表的数据，通过不同的角度去看待).

创建视图:

create view myview as select empno, ename from emp;

创建视图后面只能跟 select 语句，只有 DQL 语句才能以视图对象的方式创建出来

删除视图:

drop view myview;

使用视图:

select * from myview; /*就当正常表一样用*/
update...
delete...
/*以上操作都是通过视图改变原来表emp的数据,原表数据会被改!*/

对视图进行增删改查， 会影响到原表数据.(这样子是通过视图影响原表数据，不是直接操作的原表)

# 视图的作用

可以在 create 视图时候，在 create 视图后面需要的 select 语句里给字段名给上别名，这样之后你视图都是按照这别名操作的，就无法知道原本表里这个字段名是什么.

这么做视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，java 程序员只对视图对象进行 CRUD.

视图并不会提高效率.

# DBA 命令

将数据库当中的数据__导出__:

​ 在 windows 的 dos 命令窗口中执行 (别登录 mysql 之后执行):

/*导出整个数据库*/
mysqldump 数据库名 > 输出到的路径 -uroot(用户名) -pXXX(密码);

/*导出指定数据库当中的指定表*/
mysqldump 数据库名 表名 > 输出到的路径 -uroot(用户名) -pXXX(密码);

将数据库当中的数据__导入__:

create database 数据库名字;
user 数据库名字;
source 那个.sql(包括路径);

# 数据库设计三范式 (3 normal forms)

三范式设计表的依据，按照三范式设计的表不会出现数据冗余.

  • 第一范式: 任何一张表都应该有主键，并且每一个字段原子性不可再分 (就是每个字段都不能分成两个字段来存，如果一个字段很___明显___能分成两个字段存那就原子性可再分)

  • 第二范式：建立在第一范式的基础之上， 所有非主键字段完全依赖主键，不能产生部分依赖 (比如两个字段的联合被定为主键，然后有一个字段其实只依赖一个主键的字段，就是部分依赖). 部分依赖就意味着数据冗余，空间浪费。所以开发中不建议使用复合主键.

    可以考虑使用多个表，可以建多个表各自存相关的数据而且都有主键，然后建个关系表，里面存的就是一个表的数据关联其他表的数据，在关系表有对应的外键.

    多对多 (一个学生对应多个老师，一个老师对应多个学生), 三张表，关键表两个外键

    image-20211220183845271

​

  • 第三范式：建立在第二范式的基础上， 所有非主键字段直接依赖主键，不能产生传递依赖

​ 一对多 (一个教室对应多个学生), 两张表，多的表加外键

image-20211220184515984(如果把这些数据都存在了一个表里，那就违反了这个第三范式)

注意：实际开发中，可能会拿冗余换执行速度，比如说把这些数据都存在一个表里，这样会有很多冗余，但是表的查询速度等会更快.

# 一对一怎么设计？

一般是一张表存了，不过要是这个表太大可能会分为两个表，比如说一个存用用户登录信息，一个存用户其他的信息.

这样的话一对一有两种方案:

  1. 主键共享 (外键，然后主键不能重复)

image-20211220185735335

  1. 外键唯一 (外键，然后唯一不能重复)

image-20211220185921320

# 作业题

  1. 取得每个部门最高薪水的人员名称
select deptno, max(sal) as maxsal from emp group by deptno;/*找出每个部门最高薪水*/

/*用以上表当做临时表t，然后用来和emp e 表连接，连接条件为,deptno一样而且还sal等于maxsal*/

select e.ename from emp e join (select deptno, max(sal) as maxsal from emp group by deptno) t on e.deptno=t.deptno and e.sal = t.maxsal;


> 注意点:
>
> - 分组函数很多时候都是结合`group by`使用
> - 一步一步来，可以将一个先得出的表作为一个临时表，然后连接其他表
> - 连接时不加条件就会笛卡尔积，要加条件`join ... on ...`过滤掉不需要的

d


d


d


d


d


d


d


d