前言

参考视频：BV1Vy4y1z7EX

CSDN: https://blog.csdn.net/INFINITE_WAR/article/details/120408155

一些频繁出现的用例名字： emp 员工信息表 dept 部门信息表 ename 员工名 job 工作 sal 工资 deptno 部门编号

一、开始

SQL现为手动模式（cmd操作，要管理员模式）

net start MySQL80  //打开MySQL80服务
net stop MySQL80   //关闭	

需要提前注册mysql账号

xxxx.sql 为sql脚本文件，里面编写了大量的sql语句，在mysql中使用命令‘source (文件路径)’ 即可执行脚本文件里的所有语句。

开始会有一个基础数据库databases，里面嵌套着其他数据库。

mysql> show databases;
+-----------------------------+
| Database                    |
+-----------------------------+
| first_database              |   //自己测试用的表在这里。也可以另行开辟
| information_schema          |
| mysql                       |
| performance_schema          |
| sakila                      |
| sys                         |
| world                       |
+-----------------------------+
7 rows in set (0.00 sec)

通过 ‘use （数据库名）’可以打开指定数据库，可以在该数据库中建立表并继续操作。

打开first_database后使用show tables 命令显示所有的表。

mysql> show tables;
+--------------------------+
| Tables_in_first_database |
+--------------------------+
| score                    |
| student                  |
| tab1                     |
+--------------------------+
3 rows in set (0.00 sec)

student的创建：

mysql> CREATE TABLE student ( -> id INT(10) NOT NULL UNIQUE PRIMARY KEY , -> name VARCHAR(20) NOT NULL , -> sex VARCHAR(4) , -> birth YEAR, -> department VARCHAR(20) , -> address VARCHAR(50) -> ); Query OK, 0 rows affected, 1 warning (0.07 sec)

score的创建： mysql> CREATE TABLE score ( -> id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT , -> stu_id INT(10) NOT NULL , -> c_name VARCHAR(20) , -> grade INT(10) -> );

二、基本命令

part1

(命令不区分大小写)

exit//退出

（以下命令要分号结尾，否则会出现无限的“-->”符号让你输入）

show databases; //显示已有的数据库。 mysql默认自带了4个数据库。

use (数据库名); //表示正在使用对应的数据库

create database （数据库名）; //建立一个新的数据库

show tables; //查看某个数据库下有哪些表

part2

select version(); //查看当前mysql版本号

rowback //撤销一步

\c //强制停止当前语句（可以跳出“-->”死循环）

三、SQL语句分类

DOL

数据库查询语言(凡是带有select关键字的都是查询语句)

select .....;

注：select本身是不会对原本的数据库进行修改操作的。

DML

数据库操作语言(凡是对表当中的数据进行增删改的都是DML)

insert(增)

delete(删)

update(改)

注DML主要操作的是表中的数据

DDL

数据定义语言(凡是带有create、drop、alter的都是DDL)

注：DDL主要操作的是表的结构，而不是表中的数据。

create:新建

drop:删除

alter：修改

TCL

事务控制语言

commit; //事务提交

rollback; //事务回报

DCL

数据控制语言

grant; //授权

revoke; //撤销权限

四、导入数据

mysql> source 路径+文件名

注：路径不能用中文

查看表中数据： select * from 表名；

查看表的结构：desc 表名; //<---desc是describe的缩写，可以用原词作命令

五、查询(单表查询part1)

1.查询一个字段

select 字段名 from 表名; //select 和from都是关键字，字段名和表名都是标识符

2.查询多个字段

select 字段名1，字段名2，....，字段名n from 表名;

3.查询所有字段

方法一，把所有字段都写上：

​ select 字段名1，字段名2，....，字段名n from 表名;

方法二，使用*： //缺点：效率低，可读性差

​ select * from 表名; //回想一下查看表中数据的命令

4.查询所有字段并去重

select distinct 字段名 from 表名 //distinct关键字必须出现在所有字段名之前

5.给查询的列起别名

select (字段名 as 自定义新字段名) from 表名;

//只是修改名字后显示出来，原本的数据库没有改变。

//若字段名有空格，要以 ‘新字段名’ 的形式写入命令（字符串统一使用单引号）。as可以用空格代替。

可以对字段使用数学表达式，例如：

​

select  name ,  score*12  from student;       //可以用  as  给score*12换名

会显示：

+----------------------+-----------------------+

​ name | score*12

+----------------------+-----------------------+

| ................. ....................... |

6.条件查询

6.1关系运算符

=	等于
<> 或 !=	不等于
<、<=、>、>=	小(大)(等)于，
between (a) and (b)	>=a && <=b
is null	为空
and	与
or	或
in	包含,相当于多个or
not	非
like	模糊查询，支持 % 或 下划线 匹配
%	匹配任意字符
下划线（_）	匹配一个字符

6.2 条件查询语法格式

（为了直观，句子将不写在一行里）

select 

      字段1，字段2，字段3  ......

from

      表名

where

​      条件;

6.3 比较不熟悉的关系运算符

6.3.1 in 的用法

例：筛选出工资为5000 或 6000 或 7000的所有员工

不用in：select     num , name , job  from  tab1 

​			where  wage=5000  or  wage=6000   or  wage=7000;

使用in: select     num , name , job  from  tab1 

​			where  wage  in  (5000,6000,7000);

6.3.2 like 的用法

例：

select  name  from  table  where   name  like   "%A%";

筛选出姓名中含有“A”的姓名。

其他例：

找出名字以T结尾的：

​		select  name  from  table  where   name  like   "%T";

找出名字以K开始的：

​		select  name  from  table  where   name  like   "K%";

找出第二个字母是A的：

​		select  name  from  table  where   name  like   "_A%";

找出第三个字母是R的：

​		select  name  from  table  where   name  like   "__R%";

从上例可以看出，所谓的模糊查找本质上和正则表达式匹配类似。

六、排序(单表查询part2)

1.语法

​select 

​		字段名1，字段名2.......

​from

​		表名

​order by

​		指定字段名  ；    //==默认升序==

指定降序，则要写成 指定字段名 desc ;

指定升序，则要写成 指定字段名 asc ;

2.多字段排序

select 

​		字段名1，字段名2.......

​from

​		表名

​order by

​		指定字段名1   （指定顺序）  ，指定字段名2  （指定顺序），......  ；

从左到右优先排序；

3.根据字段位置排序

select    name   from   table   order  by    2

(指定字段名所在的列数，例句中指定第二列的字段) ;

4.综合案例

找出工资在1250和3000之间的员工信息，要求按照薪资降序排列。

select  

​		ename ,sal

from

​		emp

where

​		sal  between  1250  and  3000

order by

​		sal  desc  ; 

语句执行顺序：

​ step1：from

​ step2：where

​ step3：select

​ step4：order by

七、数据处理函数(单表查询part3)

1.单行处理函数

特点：输入一行，输出一行。

lower/upper	转换成小(大)写
substr	取子串
length	取长度
trim(字符串)	去空格
str_to_date	将字符串转换成日期
date_format	格式化日期
round(数值，保留几位小数(-1是保留到十位))	四舍五入
rand() (直接使用会生成[0,1)的随机数)	生成随机数
ifnull(数据，该数据的null情况用什么值取代)	将null转换成一个具体值
concat(字符串1，字符串2)	连接两个字符串

用法：select    函数名(参数1，参数2，......)   as   自定义列名     from   表名;

注：null加上数值的结果还是null，故要使用ifnull修改最初的null值。

分支处理(类似于编程里的if else语句)：

例：当员工的工作岗位时MANAGER时，工资上调10%；当工作岗位时SALESMAN时，工资上调50%；其他不变。

select

​		ename , job ,

​		==(case  job  when   'MANAGER'   then  sal * 1.1     when    'SALESMAN'    then   sal * 1.5  else sal  end )   as  newsal==

​from 

​		emp;

2.多行处理函数(分组函数)

特点：输入多行，输出一行。

count(字段名)	计数
sum(字段名)	求和
avg(字段名)	平均值
max(字段名)	最大值
min(字段名)	最小值

注：1.分组函数在使用的时候必须先进行分组，然后使用。

2.如果没有对数据进行分组，整张表默认为一组。

3.分组函数会自动处理null值。

4.count(具体字段)：表示统计该字段下所有不为null的元素的个数。

count(*):统计表当中的总行数。

5.分组函数不能中介用在 where 语句中。

八、分组查询(单表查询part4)

实际需求：先分组，再对每一组的数据进行操作。

例：计算每个部门的工资和

计算每个工作岗位的平均薪资

找出每个工作岗位的最高薪资

语法

select             //==若有 group   by，则应该只能写group  by   后面跟着的字段名==,或分组函数

​		.....

from

​		.....

(where  .....)

group  by       //后面只能跟  参加分组的字段，以及分组函数

​		（字段名）.....

(having......)    //作用和where 差不多，但是必须接在group  by后面

(order by .....)

执行顺序：

from --> where --> group by --> select --> order by

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

select

​		deptno,job,max(sal)

from

​		emp

group by

​		deptno  ,  job;

综合案例：

找出每个岗位的平均薪资，要求平均薪资大于1500的，除MANAGER岗位之外，要求按照平均薪资降序排序。

select

​		job  ,  avg(sal)  as  avgsal

from

​		emp

where

​		job  !=  'MANAGER'

group by

​		job

having

​		avg(sal)  >  1500

order by

​		avgsal  desc;

九、连接查询

注：可以先去了解一下关系数据库的连接运算。

连接查询的目的：多张表联合起来查询数据。

连接的底层原理是笛卡尔积。若连接查询不附带任何注释，则会返回一个数据库字段之间笛卡尔积的结果。

简单案例：查询每一个人所在的工作部门。

//SQL92的解法

select   

​		e.ename  ,  d.dname        //最好将字段所属的表名通过这种方式标出

from

​		emp  e,  dept  d            //表名空格后面为别名。

where

​	    emp.deptno  =  dept.deptno  ;          //表的连接条件，通过and可以写入进一步的过滤条件

9.1 内连接

9.1.1 等值连接

上例SQL99的语法

select

​		e.ename  ,  d.dname

from

​		emp e

join

​		dept d

on               //独立出来的连接条件

​		e.deptno  =  d.deptno ;

SQL92缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件都放在了where中。

SQL99的优点：表连接的条件是独立的，连接之后，若还要进一步筛选，再写where语句。

SQL99连接语法：

select

​		......

from

​		a

join

​		b

on

​		a和b的连接条件

where

​		筛选条件

9.1.2非等值连接

where 下的筛选条件不是一个等量关系

9.1.3自连接

样例表：

员工编号	姓名	上司编号
7369	SMITH	7902
7499	ALLEN	7698
7521	WARD	7698
7566	JONES	7839<==
7654	MARTIN	7698
7698	BLAKE	7839<==
7782	CLARK	7839<==
7788	SCOTT	7566
7839<==	KING	NULL
7844	TURNER	7698
7876	ANAMS	7788
7900	JAMES	7698
7902	FORD	7566
7934	MILLER	7782

内连接用法：查询员工的上司，要求显示员工名和对应的领导名。

注：同一个表用两个名称

select

​		a.姓名   as  '员工名'  ，  b.姓名   as   ‘上司名’

from

​		emp  a

join 

​		emp  b

on

​		a.上司编号  =  b.员工编号

9.2 外连接

例：给定一个存储员工信息的表emp ，一个存储部门信息的表dept。

要求连接员工姓名与他对应的部门，并将空的部门也一起显示。（因为有可能有的部门的人都不在表emp中）

select             （右外连接或右链接） （emp  和  dept之间存在主次关系。dept为主）

​		e.ename , d.dname

from

​		emp  e    right  join  dept  d       //right 表示 右边的表中没有匹配成功的记录也要																																显示

//(左外连接)		dept   d   **left**    emp   e            这句话与上句话相同

//关键字right表示右边的表一定要全部显示出，left同理。

on

​		e.deptno = d.deptno  ;

9.3 多表连接

语法：

select

​	  .............

from

​		 a

join     b

on

​		 a 和 b 的连接条件

join     c

on

​		 a 和 c 的连接条件

。

。

。

join     n

on

​		 a  和  n  的连接条件  ;

注：内连接和外连接可以混合使用。

例：找出每个员工的部门名称以及工资等级，要求：显示出员工名、部门名、薪资、薪资等级。

select

​		e.ename , e.sal , 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  ;

9.4 union 关键字

数据量大的情况下union比普通连接效率高

前提：连接的两个表选出的列数相同

例：

select ename, job from  emp  where  job='MANAGER'

union

select  ename ,  job from emp  where  job='SALESMAN';

十、子查询

select语句中嵌套select语句，嵌入的select语句称为子查询

如：

select

​		.....（select）.....

from

​		.....（select）.....

where

​		.....（select）.....

10.1 where中的子查询

例：找出比最低工资高的员工姓名和工资。

错误方法：

select：

​		ename  ,  sal

from

​		emp

where

​		sal > min(sal) ;           //此句错误，min这种多组函数使用前必须先分组。

思路：

//step1：查询最低工资

select    min(sal)   from   emp ;    //假设查出为800

//step2：找出大于最小工资的员工

select    ename , sal   from   emp   where   sal>800 ;

//step3：合并前两步

select    ename , sal   from   emp   where   sal> <u>(select  min(sal)   from  emp )</u> ;

10.2 from中的子查询

这种子查询可以将子查询的查询结果当成一个临时表。（技巧）

例：找出每个岗位的平均工资的薪资等级。

思路：

//step1：找出每个岗位的平均工资（按照岗位分组求平均值）

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

//step2：第一步得到了临时表 t 。现有薪资评级表 salgrade。

t 和 salgrade 连接

select

​		t.* , s.grade

from

​		t

join

​		salgrade s

on

​		t.avg(sal)       between   s.losal   ans  s.hisal ;

//step3：合并前两步

select

​		t.* , s.grade

from

​		(select  job , <u>avg(sal)  as  avgsal</u>  from  emp  group  by  job)  t

join

​		salgrade s

on

​		<u>t.avgsal</u>       between   s.losal   ans  s.hisal ;

10.3 select中的子查询

例：找出每个员工的部门名称，要求显示员工名，部门名。

select

​		e.ename , (select  d.dname  from   dept  d    where    e.deptno = d.deptno) as 			dname

from

​		emp;

注：对于每一条记录，若子查询超过一条就会报错。

十一、分页查询

11.1 limit关键字

目的：将查询结果集的一部分取出来。（搜索引擎搜索结果的分页）

用法：

limit    startindex，length       //写在order  by  后面

例：按照薪资排序，取出排名前5的员工。

select  

​		ename , sal

from

​		emp

order by 

​		sal desc

limit    0,5        //也可以写成  limit   5。表中的记录从0开始。

11.2 分页

每页显示pageSize条记录

​ 第pageNo页：limit （pageNo - 1）* pageSize ， pageSize

如： 第1页：limit 0，3 [0 1 2]

第2页：limit 3，3 [3 4 5]

第3页：limit 6，3 [6 7 8]

第4页：limit 9，3 [0 1 2]

十二、建表

创建表格式：create  table  表名（字段名1  数据类型，字段名2  数据类型，。。）;

删除表格式：drop  table  if   exists    (要删除的表名) ;

添加记录：insert  into   表名  values(字段名和值一一对应)  ;

常见数据类型：varchar(动态分配字符串内存)，char(静态分配)，int，bigint，float，double，date(短日期)，datetime（长日期），clob(字符大对象)，blob(二进制大对象)

clob：可以存储最多4G 的大字符串（如文章）。

blob：存储图片、声音、视频等流媒体数据。需要使用IO流

12.1修改（update）

update  

​		表名  

set  		//修改具体值

​		字段名1=值1，字段名2=值2，。。。

where... ;//指定特定条件的记录，不写where默认修改所有记录

12.2删除记录delete（DML）

delete   from   表名   where  ......；//不写where会删除所有数据

12.3插入多条记录

insert  into  表名（字段名1，字段名2。。。）   values

（元组一），

（元组二），

。。。，

（元组n）;   //元组数据类型必须与第一行的字段名一一对应

12.4快捷命令

12.4.1 快速创建表

create    table   新表名   as   select   *  from   原表名;   //select语句可以按需求扩充内容（如where，order by等）

12.4.2 快速删除数据

truncate  table  表名 ;   //与‘delete   from   表名’     相同

truncate和delete的区别

truncate	delete
删除效率相对比较高，表被依次截断，物理删除	表中的数据被删除了，但是删除的数据在硬盘上的真实存储空间不会被释放
缺点：不支持撤销操作	缺点：删除效率较低（一亿条记录时要1小时才能删完）
有点：快速（数据量非常大的时候推荐使用。一亿条记录是删除时间不到1秒）	优点：支持撤销操作
是DDL语句	是DML语句

十三、约束

目的：确保数据的完整性、有效性。

常见约束：非空约束(not null)、唯一性约束(unique)、主键约束(primary key ，PK)、外键约束(foreign key，FK)、检查约束(check)

13.1 非空约束

not null 约束的字段不能为NULL。

drop  table  if  exist  t_vip;

create  table  t_vip(

    id  int,

    name   carchar(255)   **not  null**

);

13.2 唯一性约束

unique约束的字段不能重复，但是可以为NULL。

使用例：

drop  table  if  exist  t_vip;

create  table  t_vip(

    id  int,

    name   carchar(255)   **unique**,            //该字段的内容不能重复

    email  varchar(255)

);

推广：不同字段在特定条件下可以联合设置为唯一性约束。（如：两个人姓名相同，但是电话号码不同。或者说一个人有多个电话号码记录）

drop  table  if  exist  t_vip;

create  table  t_vip(

	id  int,

	name   carchar(255) ，   

	email  varchar(255)  ，

	unique(name , email)      //联合唯一性

);

下例为unique和not null 联合使用

drop  table  if  exist  t_vip;

create  table  t_vip(

	id  int,

	name   carchar(255)   **not  null  unique**,   //该字段的内容不能重复也不能为空

	email  varchar(255)

);

这种联合的效果就是主键约束，也就是说，可以直接用PK约束实现前两种约束的联合。

13.3 主键约束

术语：主键约束、主键字段、主键值

主键例：学号、身份证......

建议设为主键的类型：int、bigint、char等。（varchar不建议）

注：一个表中只能设置一个主键

特征：not null 、 unique

13.3.1 单一主键和复合主键（分类一）

使用例：

drop  table  if  exist  t_vip;

create  table  t_vip(

	id  int,

	name   carchar(255)   **primary  key **   ， 

	email  varchar(255)

);

复合主键使用例：

drop  table  if  exist  t_vip;

create  table  t_vip(

	id  int,

	name   carchar(255) ，    

	email  varchar(255)，

	primary  key（id，name）     //号码、姓名同时相同时才算重复。

);

13.3.2 自然主键和业务主键（分类二）

自然主键：主键值是一个自然数，和业务无关。<==使用较多

业务主键：主键值和业务关联，例如银行卡号。

自动维护主键值使用例：

drop  table  if  exist  t_vip;

create  table  t_vip(

	id  int    **primary  key   auto_increment**  

	name   carchar(255) ，

	email  varchar(255)

);

13.4 外键约束

相关术语：外键约束、外键字段(添加了外键约束的字段)、外键值(可以为null)。

应用场景：处理学生和班级时，应当把班级额外设置为一个表再对学生进行外键约束。（t_class , t_student）

t_class

classno	classname
100	一班
101	二班

t_student

no	name	cno
1	jack	100
2	lucy	100
3	lilei	100
4	bob	100
5	ben	101
6	alice	101
7	alex	100
8	zhangsan	101

t_class 是父表，t_student是子表。

删除表的顺序：先删子，再删父。

创建表的顺序：先创建父，再创建子。

删除数据的顺序：先删子，再删父。

插入数据的顺序：先插入父，再插入子。

drop  table  if  exist   t_student;

drop  table  if  exist   t_class;



create  table  t_class(

​		classno  int  primary  key,

​		classname   varchar(255)

)



create  table  t_student(

​		no  int  primary   key  auto_increment,

​		name  varchar(255),

​		cno  int,

​		**foreign  key(cno)  references  t_class(classno)**  

)

十四、存储引擎

注：存储引擎是mysql的特有术语。

存储引擎即数据内容的存储方式，如：ANSI,UTF-8等。

查看mysql支持哪些存储引擎：show engines

展示新建表的存储引擎：show create table (表名)

指定引擎使用例：

create  table  t_product(

​		id  int  primary  key ,

​		name  varchar(255)

)engine=InnoDB  default  charset=utf8;

14.1 My ISAM存储引擎

使用三个文件表示每一个表：

​ 格式文件：存储表结构的定义 xxx.frm

​ 数据文件：存储表行的内容 xxx.MYD

​ 索引(目录)文件：存储表上索引 xxx.MYI

特点：可以转换为压缩、只读表来节省空间。

14.2 InnoDB存储引擎（默认引擎）

InnoDB支持事务，支持数据库崩溃后自动恢复机制

特点：非常安全，效率不高，不能压缩或转换为只读。

InnoDB管理的表的特征：

​ 每个InnoDB表再数据库目录中以 .frm 格式文件表示

​ InnoDB表空间tablespace被用于存储表的内容

​ 提供一组用来记录事务性活动的日志文件

​ 用COMMIT(提交)，SAVEPOINT以及ROOLBACK支持事务处理

​ 提供全ACID兼容

​ 在mysql服务器崩溃后会自动回复

​ 多版本(MVCC)和行级锁定

​ 支持外键及引用的完整性，包括级联删除和更新

14.3 MEMORY存储引擎

数据存储在内存中，且行的长度固定

特点：查询速度快，不安全

MEMORY管理的表的特征：

​ 在数据库目录内，每个表均以 .frm 格式的文件表示。

​ 表数据及索引被存储在内存中

​ 表级锁机制

​ 不能包含TEXT 或 BLOB字段

十五、事务(DML语句)

定义：一个事务是一个完整的业务逻辑。

事务特性：

​ A(原子性)：事务是最小的工作单元，不可再分。

​ C(一致性)：一系列操作必须同时成功或同时失败。

​ I(隔离性)：不同事务之间不影响

​ D(持久性)：保存内容，提供保障。

完整的业务逻辑例：

​ 业务：A转账10000元给B

​ 步骤：A的账户-10000，B的账户+10000。

这一系列操作必须同时成功或同时失败。

即，一个事务就是一系列的DML语句。

注：InnoDB会提供一组用来记录事务性活动的日志文件

如：

事务开启，写入日志

insert...

insert...

.

.

.

事务结束，写入日志          //提交事务



提交事务：commit ；语句

回滚事务：rollback ；语句     //回到上一次提交的位置

关闭自动提交操作：start    transaction；

15.1 事务隔离

级别：

​ 读未提交：read uncommitted(不提交也读到) 最低级

​ 事务A读到事务B未提交的数据。易产生脏数据(Dirty Read)。

​ 读已提交：read committed (提交了才能读到)

​ 事务A只能读到事务B提交之后的数据。

​ 不可重复读数据。如：事务开启后，第一次读到的数据是3条，当前事务还没有结束，可能第二次在读取的时候，读到的数据是4条，3不等于4。

​ 可重复读：repeatable read (提交了也读不到) //mysql默认级别

​ 读取的是数据的幻象，读到的都是事务刚开始时的数据。

​ 序列化：serializable 最高级

​ 效率低，但是解决了所有的问题

​ 这种级别表示事务排队，不能并发。

十六、索引

类似于书的目录，提高搜索效率。

mysql查询的方式：1.全表扫描。2.根据索引检索

索引必须排序。索引和TreeSet(底层是AVL)数据结构相同。

在mysql中，索引是一个B树。

复合索引：两个及以上字段联合起来添加一个索引。

16.1 语法

创建索引

​

create  index  emp_ename_index  on  emp(ename) ;

​		给emp表的ename字段添加索引，命名：emp_ename_index

删除索引

​

drop  index  emp_ename_index  on  emp;

​		将emp表上的emp_ename_index索引对象删除

检查是否有索引

​

explain  *  from   emp   where   ename  =‘XXX’;

16.2 索引失效

1.尽量避免模糊查询时以“%”开始。

2.使用 or 关键字时两边的字段都要有索引，否则不会调用索引。

3.使用复合索引的时候，没有使用左侧的列查找，索引会失效

4.在where中索引列若参与的运算，索引会失效。

5.在where中索引列使用了函数，索引会失效。

......

16.3索引分类

索引是各种数据库进行优化的重要手段，优化的时候优先考虑的就是索引。

分类一：单一索引、复合索引。

分类二：主键索引、唯一性索引(在有unique约束的字段上添加索引)

17、视图(view)

创建视图对象：create view dept2_view as select * from dept2;

注：上面as关键字后的语句必须是DQL语句。

删除视图：drop view dept2_view;

对视图对象的增删改查，会导致原表被操作。

视图本质就是将一块SQL语句进行封装，以便复用时节省空间。

如：

create  view	emp_dept_view

as

​		select

​			e.ename ,  e.sal , d.dname

​		from

​			emp e

​		join

​			dept d

​		on

​			e.deptno = d.deptno ;

as 后面的整块SQL语句被封装进了emp_dept_view中

18、数据库设计范式

目的：设计合理的数据库表，避免数据冗余、空间浪费。

分类：

​ 第一范式：要求任何一张表必须有主键，每一个字段的原子性不可再分。

​ 第二范式：建立在第一范式之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。

​ 第三范式：建立在第二范式之上，要求所有非主关键字直接依赖主键，不要产生传递依赖。

18.1 第一范式

最核心最重要的范式，所有表的设计都要满足。

有主键，且字段内容不可再分的表满足第一范式。

18.2 第二范式

学生编号(复合主键)	教师编号(复合主键)	学生姓名	教师姓名
1001	001	张三	王老师
1002	002	李四	赵老师
1003	001	王五	王老师
1001	002	张三	赵老师

这种表不符合第二范式，因为学生只依赖学生编号，老师只依赖教师编号，数据易冗余。

为了满足第二范式，要将上面的表分解成多个子表(如下)。

学生表

学生编号(pk)	学生名字
1001	张三
1002	李四
1003	王五

教师表

教师编号(pk)	教师姓名
001	王老师
002	赵老师

学生教师关系表

id(pk)	学生编号(fk)	教师编号(fk)
1	1001	001
2	1002	002
3	1003	001
4	1001	002

多对多设计技巧：多对多，三张表，关系表两个外键。

18.3 第三范式

学生编号(pk)	学生姓名	班级编号	班级名称
1001	张三	01	一班
1002	李四	02	二班
1003	王五	03	三班
1004	赵六	04	四班

上标满足第一、二范式。

不满足第三范式，因为班级名称依赖班级编号，而班级编号又依赖学生编号，这就产生了传递依赖，会有数据冗余。

为满足第三范式，可以将上标拆成两张表(如下)。

班级表

班级编号(pk)	班级名称
01	一班
02	二班
03	三班
04	四班

学生表

学生编号(pk)	学生姓名	班级编号(fk)
1001	张三	01
1002	李四	02
1003	王五	03
1004	赵六	04

第三范式设计技巧：一对多，两张表，多的表，加外键。

18.4 总结表的设计

一对多：

​ 一对多，两张表，多的表加外键。

多对多：

​ 多对多，三张表，关系表两个外键。

一对一：

​ 若字段过多，则要考虑拆成多个更小的表，并用外键加以联系。

​ 一对一，外键唯一。

不过实践和理论之间存在偏差。

从实践上来说，设计表的最终目的都是为了满足客户的要求，有时会拿冗余换执行速度。因为在sql中，表和表之间连接次数越多，效率越低。(笛卡尔积)

可参考资料：

数据库范式：https://www.zhihu.com/question/24696366/answer/29189700 (opens new window)