MySQL数据库

MySQL

一、什么是MySQL

    MySQL是一个关系型数据库管理系统，由Oracle公司开发、发布和支持。

1. MySQL是一个数据库管理系统
    数据库是结构化数据的集合，可以是简单的购物清单的图片或者是公司网络中的大量信息，为了添加、访问和处理存储在公司数据库中的数据，你需要一个数据库管理系统，比如MySQL server。由于计算机非常擅长处理大量的数据，作为独立的实用程序或者其他应用的一部分，数据库管理系统在计算中扮演着非常核心的角色。

2. MySQL数据库是关系型的
    一个关系型数据库将数据存储在不同的表中，而不是将所有的数据存储在一个大的存储区域中。为了提高存储速度，结构化数据是有组织的存放在物理文件中。数据库、表格、视图、行和列等对象的逻辑模型提供了灵活的编程环境。你可以在指向不同表格的不同数据字段中设置对应关系的规则，如一对一、一对多、唯一、必须和可选。数据库强制执行这些规则，因此，在一个设计良好的数据库中，应用程序永远不会看到不一致的、重复的或者孤立的、过期或者丢失的数据。
    MySQL的SQL部分对应结构化查询语言，SQL是最常见的用于访问数据库的标准化语言。根据你的编程环境，你可以直接输入SQL（如生成报告），或者将SQL语言嵌入到其他语言中，或者使用特殊语言的API隐藏SQL语法。
    SQL是根据ANSI/ISO SQL标准定义的。1986年以来，SQL标准一直不断发展，到现在已经有好几个版本，如92版的标准、99版的标准和2003版的，目前基本都使用2003版的最新标准。
3. MySQL软件是开源的
    开源就意味着每个人都可以使用和修改软件。每个人都可以免费在网络上下载和使用MySQL。你可以根据个人需求学习和更改源码，MySQL软件使用的是GPL协议。
4. MySQL数据库服务器速度快、可靠性高，扩展性强，且易于使用
    MySQL服务器可以在桌面或者笔记本电脑上与其他应用程序、web服务器等等一起轻松运行，不需要特别关注。如果要将整个机器用于MySQL，可以调整MySQL设置，以充分利用所有的内存、CPU能力和可用的输入输出能力。MySQL还可以扩展到一组机器，并将它们联网。
5. MySQL服务器在客户端、服务器或者嵌入式系统中工作
     MySQL数据库软件是一个客户端/服务器系统，由一个多线程的SQL服务组成，支持不同的后端、多个不同的客户程序和库、管理工具和广泛的应用程序接口。MySQL服务器也可以作为嵌入式多线程库，可以将其连接到应用程序，以获得更小、更快，更易于管理的独立产品。
6. 提供大量的MySQL软件支持
    MySQL支持大量的语言，大部分应用程序语言均支持MySQL软件。

二、三大类型

1.数据类型

    数据类型决定了数据在计算机中的存储格式，代表不同的信息类型。

2.日期和时间类型

    表示时间值的日期和时间类型

3.字符串类型

    字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

三、主键

主键：（Primary Key）又称主码，用于唯一的标识表中的每一条记录，可以定义表中的一列或多列为主键，主键上既不能有两行相同的值，也不能为空值。所以在这里就定义了主键里面包含着非空和唯一的约束。

四、数据库技术构成

数据库系统有3个主要的组成部分：

• 数据库：用于存储数据的地方
• 数据库管理系统：用于管理数据库的软件
• 数据库应用程序：为了提高数据库系统的处理能力所使用的管理数据库的软件补充

五、SQL语言

SQL语言包含以下4部分：

（1）数据定义语言（Data Definition Language DDL）:drop、create、alter

（2）数据操作语言（Data Manipulation Language DML）:insert、update、delete

（3）数据查询语言（Data Query Language DQL）:select

（4）数据控制语言（Data Control Language DCL）：grant、revoke、commit、rollback

六、数据库操作

数据库的相关操作：

显示所有的库：

show databases;

创建库：

create database 库名；

查看数据库的结构

show create database 库名;

删除库：

drop databases 库名;

七、数据表操作

数据表的相关操作：

创建数据表：

create table 表名(
		字段1 类型 约束 默认值,
		字段2 类型 约束 默认值,
		...........
		字段n 类型 约束 默认值
)

例如：

create table employee(
		emp_id int,
		emp_name varchar(50),
		emp_sex char(3),
		emp_salary float,
		emp_phone varchar(50),
		emp_birth date,
		dept_id int
)

查看该库下的所有表：

show tables;

在表中还有一些默认值，主键，非空等约束。

表约束

表约束：

数据完整性：数据的可靠性和准确性

五类完整性约束：

（1）not null 非空约束

（2）unique 唯一约束

（3）primary key 主键约束

（4）foreign key 外键约束

（5）checked 检查（mysql不支持的）

主键约束：

-- 定义列的同时指定主键
字段名 字段类型  primary key;
-- 定义表之后指定主键
alter table 表名 add constraint 主键名 primary key(字段);
-- 主键自增
字段名 字段类型  primary key auto_increment;

非空约束：

-- 定义列的同时指定非空
字段名 数据类型 not null;

唯一约束：

-- 定义列的同时指定唯一
字段名 数据类型 unique;
-- 定义表之后指定唯一
alter table 表名 add constraint 约束名 unique(约束字段);
-- 删除唯一索引的语法：
alter table 表名 drop index 约束名;

默认值约束：

-- 定义列的同时指定默认值
字段名 字段类型 default 默认值;

外键约束：

常用的表关系：一对一，一对多（自关联），多对多

一对一：人与身份证

-- 表之间的关系
/*
一对一
    人   身份证
*/
create table person(
		pid int primary key auto_increment,
		pnname varchar(50) not null,
		psex char(3) default '男',
		pphone varchar(50) unique
);

create table idcard(
		card_id int,
		card_number int unique not null,
		card_office varchar(50) not null,
		card_starttime datetime,
		card_endtime datetime
);

alter table idcard add constraint fk_card_id foreign key(card_id) references person(pid);


/*

一对多：部门与员工

/*
一对多
部门  员工
  1    N
	1    1
*/
create table department(
		did int primary key auto_increment,
		dname varchar(50) not null unique,
		ddesc varchar(200),
		dnum int
);
create table employee(
		emp_name varchar(50) not null,
		emp_sex char(3) default '男',
		emp_phone varchar(50) unique,
		emp_birth date,
		dept_id int
);

alter table employee add constraint fk_emp_dept foreign key(dept_id) references department(did);

/*

多对多：用户与角色

/*
多对多
账号  角色
 N      N
中间有一个表
*/
create table users(
		uid int primary key auto_increment,
		uname varchar(50) not null unique,
		upwd varchar(50),
		ustatus int,
		uloginip varchar(50),
		ulasttime datetime
);
create table roles(
		rid int primary key auto_increment,
		rname varchar(50) not null unique,
		rdesc varchar(200)
);
-- 中间表
create table userroles(
		uid int,
		rid int,
		primary key(uid,rid) -- 两列是一个整体的主键
); 
alter table userroles add constraint fk_ur_user foreign key(uid)
references users(uid);
alter table userroles add constraint fk_ur_role foreign key(rid)
references roles(rid);

自关联：

/*
自关联
*/
create table cities(
		cid int primary key auto_increment,
		cname varchar(50) not null,
		cpid int
);

alter table cities add constraint fk_cities foreign key cities(cpid) references cities(cid);

表结构操作

查看表结构：

desc 表名;
show create table 表名;

修改表结构：

-- 修改表名
alter table 旧表名 rename to 新表名;
-- 修改字段的数据类型
alter table 表名 modify 字段名 数据类型;
-- 修改字段名
alter table 表名 change 旧字段名 新字段名 新数据类型;
-- 添加字段
alter table 表名 add 新字段名 新字段类型 约束条件 first|after 已存在字段名;
-- 删除字段
alter table 表名 drop 字段名;
-- 修改字段的排序位置
alter table 表名 modify 字段1 数据类型 first|after 字段2;
-- 删除表的外键约束
alter table 表名 drop foreign key 外键约束名;
-- 更改表的存储引擎
alter table 表名 engine=更改后的存储引擎;
-- 删除数据表
drop table 表1.....;

八、数据CRUD操作

插入数据

-- 向表中所有的列赋值
insert into 表名 values();
-- 给指定列插入指定列的值
-- 值列表和列的列表保持一致
insert into 表名(列的列表) values(值列表);
-- 插入多条记录
insert into 表名(列的列表) values(值列表),(值列表),(值列表);

备份数据

-- 如果表不存在
create table 表名 as select ....
-- 如果表存在
insert into 表名() select ...from....

更新数据

update 表名  set 列=值,列=值,... where 条件

删除数据

delete from 表名 where 条件

    如果删除语句没where,此时会将数据表中的记录全部删除，类似truncate table。

    truncate 将直接删除原来的表，并重新创建一个表，其语法结构为 truncate table 表名。

    truncate 直接删除表而不是删除记录，因此执行速度比 delete 快。而且不能用在有主外键关系的主表中。

​ mysql为什么不建议delete数据?

​ delete对性能会有影响，一般不建议硬性delete数据，而是标记deleted = 1这种软删除，为什么？

​ 根据之前了解的mysql底层存储原理最小存储单元page页，无论是非叶子节点page存的是索引key和指

针，还是叶子节点存的是行数据.

1.当删除大量数据时

​ MySQL内部不会真正删除空间，而且做标记删除，即将delflag:N修改为delflag:Y，commit之后会

会被purge进入删除链表，如果下一次insert更大的记录，delete之后的空间不会被重用，如果插入

的记录小于等于delete的记录空会被重用。

2.当少量删除中间数据时

​ 你在中间删了某些个值，实际上只是找到那个page页找到对应的数据做删除标记，并不会实际影响page

页已经占的大小，这块空间可能也永远不会被利用，产生了内存碎片导致索引频繁分裂，影响SQL执行计

划的稳定性。

正确姿势：

​ 使用deleted = 1 字段来软删除，保证索引连续性，

​ 必要时，可将deleted = 0的字段完整迁移到新表，解决碎片问题

查询数据

-- 查询所有的行与列
select * from 表名;
-- 查询部分列
select 列名1,列名2 from 表名;
-- 起别名
select 列名 from 表名 别名;
-- 例如
select emp_id as 员工编号,emp_name  "员工 姓名",emp_sex 员工性别 from employee;
select e.emp_id as 员工编号,e.emp_name  "员工 姓名",e.emp_sex 员工性别 from employee e;
-- 查询部分行
select * from 表名 where 条件;
-- 带IN关键字的查询:查询满足指定范围内的条件的记录
-- 查询编号 为 1 4 7 的员工信息;
select * from emp_bak01 where emp_id =1 or emp_id=4 or emp_id=7;
select * from emp_bak01 where emp_id in (1,4,7);
-- 带BETWEEN AND的范围查询:查询某个范围内的值
-- 查询工资在10000-20000
select * from employees where salary>=10000 and salary<=20000;
select * from employees where salary between 10000 and 20000;
-- 模糊查询  %:匹配任意字符   _:匹配一个字符
select * from employees where first_name like 'e%'; -- 以e开头
select * from employees where first_name like 'e___';-- 以e结尾
select * from employees where first_name like '%e%';-- 包含e
-- 空的查询：查看email为空的信息
select * from employees where email is null;
select * from employees where email='';
-- 非空查询：email不为空的查询
select * from employees where email is not  null and email !='';
-- 带OR的多条件查询：表示只需要满足其中一个条件的记录即可返回
select * from employees where email is null or email='';
-- 带AND的多条件查询：可以使用AND连接两个甚至多个查询条件，多个条件表达式之间用AND分开
select * from employees where email is not  null and email !='';
-- 查询结果不重复： distinct 去重复
select distinct first_name,job_id from employees;
-- 排序 order by  默认是从小到大排序，desc从大到小排序
select * from employees order by salary desc;-- 从小到大
select * from employees order by department_id desc;-- 从大到小
-- 按照部门排序 ，同部门的在按照工资排序 
select * from employees order by department_id desc ,salary desc;

聚合函数

    MySQL提供一些查询功能，可以对获取的数据进行分析和报告。这些函数 的功能有：计算数据表中记录行数的总数、计算某个字段列下数据的总和， 以及计算表中某个字段下的最大值、最小值或者平均值。常用的聚合函数 MAX()、MIN()、COUNT()、SUM()、AVG()

1）count()函数统计数据表中包含的记录行的总数，或者根据查询结果 返回列中包含的数据行数。其
使用方法有两种：
 count(*)计算表中总的行数，不管某列是否有数值或者为空值。
 count(字段名)计算指定列下总的行数，计算时将忽略空值的行。
2）sum()是一个求总和的函数，返回指定列值的总和。 sum()函数在计算时，忽略列值为null的行。
3）avg()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值。
4）max()函数返回指定列中的最大值。

-- 例如：
-- 统计的 ，多少人 count，平均工资 avg，最高工资 max，最低工资 min，总工资 sum   忽略null值,一行一列的结果。聚合函数不能随便加入其他的列，如果要加入，一定是出现在group by 子句中的列。
select count(*) from employees;
select count(1) from employees;
select count(employee_id) from employees;
select count(e.commission_pct) from employees e;
select avg(salary) 平均工资,count(1) 总人数,max(salary),min(salary),sum(salary) from employees;

分组查询

    group by 关键字通常和集合函数一起使用。

-- 分组查询
select * from employees;
select count(1) from employees;
-- 精确的统计 group by 只有出现在 group by 子句中的列才能加入select 查询结果的后面，有可能多列分组
-- 每个部门多少人
select department_id 部门编号,(select d.department_name from departments d where d.department_id = e.department_id) 部门名字,count(1) 人数 from employees e group by department_id;
-- 统计每个部门下每个managerid有多少人
select manager_id 管理员编号,department_id 部门编号,count(1) 人数 from employees group by manager_id,department_id order by manager_id;-- 分组后的结果再次排序
-- 每个班的男女各多少人
select gradeid,sex,count(1) from student group by gradeid,sex;

使用HAVING过滤分组

    group by 可以和 having 一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示。

-- 统计每个部门下每个managerid的人数大于4的记录
select manager_id 管理员编号,department_id 部门编号,count(1) 人数 from employees group by manager_id,department_id having count(1) > 4;
-- 统计每个部门下每个managerid的人数大于4的记录,并按照人数由高到低排序
select   manager_id 管理者编号,department_id 部门编号,count(1) 人数 from employees group by manager_id,department_id having count(1)>4 order by count(1) desc;

这里注意 having 和 where 的区别：

• having是在分组后对已经过滤的数据再进行进行过滤；
• where是在分组前对数据表中的数据进行过滤；
• having后面可以使用聚合函数；
• where后面不可以使用聚合。

举例：

-- 只可以用where，不可以用having的情况
-- 查询成绩大于80的学生的学号和姓名
select studentno,student_name from student where student_grade > 80;
select studentno,student_name from student having student_grade > 80;-- 报错！！！因为前面并没有筛选出student_grade 字段

-- 只可以用having，不可以用where情况
-- 查询这个学生的平均分，获得平均分大于60的学生信息
select studentno,student_name,avg(student_grade) as ag from student group by studentno having ag > 60;
select studentno,student_name,avg(student_grade) as ag from student where ag > 60 group by studentno; -- 报错！！！因为 from student 这张数据表里面没有ag这个字段
-- 注意:where 后面要跟的是数据表里的字段，如果我把ag换成avg(student_grade)也是错误的！因为表里没有该字段。而having只是根据前面查询出来的是什么就可以后面接什么。

limit

    SELECT返回所有匹配的行，有可能是表中所有的行，若仅仅需要返回 第一行或者前几行，可使用LIMIT关键字，limit 0,1：零到一行，limit 2.8：二到八行…

-- 查询课时最多的科目名称及课时
select subjectname,classhour from subjects order by classhour desc limit 0,1;
-- 查询年龄最小的学生所在的年级及姓名。
select studentname,gradeid from student order by born desc limit 0,1;

连接查询

内连接

    内连接（inner join）使用比较运算符进行表间某（些）列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。

select ... from table1 inner join table2 on 关联条件;

-- 内连接:两张表相关联的数据
-- 内连接，找到两个表相关联的记录 如果俩表中的关联字段名不同，可以直接写的，如果相同，必须通过别名引入
select * from employee inner join department on employee.dept_id=department.did;

外连接

outer join

• 左外连接：left join:以左边的表为基准，找到左边表中所有的数据

select ... from table1 left join table2 on 关联条件;

• 右外连接：right join：以右边的表为基准，找到右边表中所有的数据

select ... from table1 right join table2 on 关联条件;

练习：

select * from employee left join department on employee.dept_id=department.did;
-- 等价
select employee.*,department.* from department right join employee on employee.dept_id=department.did;

子查询

    把一步一步查询出来的表当做一个小表，继续在之后的查询里面用，查询表里面还有一个表。

练习01：

-- 子查询
-- 查询成绩最高的学生信息
-- 最高分
select max(studentscore) 成绩最高 from mark;
-- 对应的学号
select studentno from mark where studentscore = (select max(studentscore) 成绩最高 from mark);
select * from student s where studentno in (select studentno from mark where studentscore = (select max(studentscore) 成绩最高 from mark));

-- 查询考试没有及格的学生信息
select studentno, count(1) from mark where studentscore < 60 group by studentno;
select * from student where studentno in (select studentno from mark where studentscore < 60);

--
select studentno, count(1) from mark where studentscore < 60 group by studentno having count(1) > 2;
select * from student where studentno in (select studentno from mark where studentscore < 60 group by studentno having count(1) > 2);

-- 查询平均分低于总平均的记录
select * from mark;
select avg(studentscore) from mark;
select studentno, avg(studentscore) from mark group by studentno having avg(studentscore) < (select avg(studentscore) from mark);
select * from student where studentno in (select studentno from mark group by studentno having avg(studentscore) < (select avg(studentscore) from mark)); 

-- 查询所有科目中成绩最低的学生信息
select min(studentscore) from mark;
select studentno from mark where studentscore = (select min(studentscore) from mark);
select * from student where studentno in (select studentno from mark where studentscore = (select min(studentscore) from mark));

-- 查询每门科目中成绩最低的学生信息
select subjectid,min(studentscore) from mark group by subjectid;

select * from mark m, (select subjectid,min(studentscore) min from mark group by subjectid) s where  m.subjectid = s.subjectid and m.studentscore = s.min;

select * from student where studentno in (select studentno from mark m, (select subjectid,min(studentscore) min from mark group by subjectid) s where  m.subjectid = s.subjectid and m.studentscore = s.min);

select a.*,b.*,(select c.subjectname from subjects c where c.subjectid = b.subjectid) from student a,(select m.studentno,m.subjectid,m.studentscore  from mark m, (select subjectid,min(studentscore) min from mark group by subjectid) s where m.subjectid = s.subjectid and m.studentscore = s.min) b where a.studentno = b.studentno;

练习02：

-- 1.查询 最低工资 大于 50号部门最低工资 的 部门id 和 其最低工资
select min(salary) from employees where department_id = 50; -- 50号部门最低工资
select department_id,min(salary) from employees group by department_id;  -- 分组每个部门的最低工资
select department_id,min(salary) from employees group by department_id having min(salary) > (select min(salary) from employees where department_id = 50); -- 加过滤器：最低工资 大于 50号部门最低工资

-- 2.查询 各部门中的工资比本部门平均工资高 的 员工的员工号, 姓名和工资
select department_id,avg(salary) from employees group by department_id; -- 分组查询各部门的本部门的平均工资，虚拟表
select e.employee_id,e.first_name,e.last_name,e.salary,e.department_id from employees e 
inner join (select department_id de,avg(salary) ag from employees group by department_id) avs on e.department_id = avs.de -- 进行内连接，是部门ID号相等，对应起来
where e.salary > avs.ag; -- 加条件：各部门中的工资比本部门平均工资高

-- 3.查询 在部门的location_id为1700的部门工作的 员工的员工号
select * from departments;
select department_id from departments where location_id = 1700; -- 先查询location_id为1700的部门ID
select employee_id from employees where department_id = any (select department_id from departments where location_id = 1700); -- 只要员工表里的部门ID额查询出来的ID对应一个即可 any

-- 4.查询 在名字中包含字母u的员工 其相同部门下的员工的 员工号和名字
select employee_id,last_name,department_id from employees where last_name like '%u%'; -- 先查询名字中有u的员工号，名字，部门号
select distinct department_id from employees where last_name like '%u%'; -- 去掉重复的部门号，只要一个对应的部门号
select employee_id,last_name from employees where department_id in (select distinct department_id from employees where last_name like '%u%'); -- 只要满足部门号在里面即可 in

-- 5.查询 每个部门的 平均工资的 工资等级
select department_id,avg(salary) from employees group by department_id -- 分组查询各部门的本部门的平均工资，虚拟表
select * from job_grade; -- 等级表
select avs.*, g.job_level from (select department_id de,avg(salary) ag from employees group by department_id) avs -- 要查询的是每个部门的id和平均工资，对应的等级
inner join job_grade g on avs.ag between lowest_sal and highest_sal; -- 内连接，使平均工资对应到等级表里面的最低和最高上，划分等级

any，in，some，all：

1. in: 在范围内的值，只要有就true；

2. all: 与子查询返回的所有值比较为true 则返回true；

3. any: 与子查询返回的任何值比较为true 则返回true；

4. some: 是any的别称,很少用。

    all，any，some 关键字必须与一个比较操作符一起使用。

    all可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

    any可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。

    语句 in 与“=any”是相同的

exists：

    exists关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么exists的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么exists返回的结果是false，此时外层语句将不进行查询。

SELECT * FROM tb1 WHERE EXISTS (SELECT * FROM tb2 WHERE num2>10) AND num1>10;
--  如果 SELECT * FROM tb2 WHERE num2>10 有返回结果，则执行外部查询，此时会查询tb1表，并且要过来num1的值大于10的。如果将num2大于100的没有结果，则外部查询不执行。

    NOT EXISTS 与 EXISTS相反。

union：

    将两个表的数据放一起，union all：合并两个表里的数据，包括重复的；union：合并两个表里面的数据，将重复的去掉。

SELECT * FROM tb1 UNION ALL SELECT * FROM tb2;

SELECT * FROM tb1 UNION SELECT * FROM tb2;

正则查询

    正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据 指定的匹配模式匹配文本中符合要求的特殊字符串。。例如，从一个文本文件 中提取电话号码，查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，可以应用于非常复杂的查询。

-- 以字母a开头 
SELECT * FROM employees WHERE first_name REGEXP '^a'; 
-- $结尾 
SELECT * FROM employees WHERE first_name REGEXP 'a$'; 
-- b中间是任意字符c b与c之间得有一个任意字符， 
SELECT * FROM employees WHERE first_name REGEXP 'b.c';
-- +b必须得出现到n次 
SELECT * FROM employees WHERE first_name REGEXP 'b+c'; 
-- * 必须得0到n次 
SELECT * FROM employees WHERE first_name REGEXP 'b*c';
SELECT * FROM employees WHERE first_name REGEXP 'b{2,}c'; 
-- {2,5} 2到5次 
SELECT * FROM employees WHERE first_name REGEXP 'b{2,4}c';

九、系统函数

数学函数

1. 绝对值函数ABS(x)和返回圆周率的函数PI()

2. 平方根函数SQRT(x)和求余函数MOD(x,y)

3. 获取整数的函数CEIL(x)、CEILING(x)和FLOOR(x)

   CEIL(x)和CEILING(x)的意义相同，返回不小于x的最小整数值，返回 值转化为一个BIGINT。

   FLOOR(x)返回不大于x的最大整数值，返回值转化为一个BIGINT。

4. 获取随机数的函数RAND()和RAND(x)

   RAND(x)返回一个随机浮点值v，范围在0到1之间（0 ≤ v ≤ 1.0）。若已指定一个整数参数x，则它被用作种子值，用来产生重复列。

5. 函数ROUND(x)、ROUND(x,y)和TRUNCATE(x,y)

   ROUND(x)返回最接近于参数x的整数，对x值进行四舍五入。

   ROUND(x,y)返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位。

   TRUNCATE(x,y)返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。若y设为负数，则截（归零）x小数点左起第y位开始后面所有低位的值。

6. 符号函数SIGN(x)

   SIGN(x)返回参数的符号，x的值为负、零或正时返回结果依次为-1、0或1。

7. 幂运算函数POW(x,y)、POWER(x,y)和EXP(x)

   POW(x,y)或者POWER(x,y)函数返回x的y次乘方的结果值。

   EXP(x)返回e的x乘方后的值。

-- 绝对值函数ABS(x)
select abs(-9); -- 9
-- 返回圆周率的函数PI()
select pi(); -- 3.141593
-- 平方根函数SQRT(x)
select sqrt(4); -- 2
-- 求余函数MOD(x,y)
select mod(5,2); -- 1
-- CEIL(x)和CEILING(x)返回不小于x的最小整数值
select ceil(2.3); -- 3
select ceiling(2.3); -- 3
-- FLOOR(x)返回不大于x的最大整数值
select floor(2.3); -- 2
-- 获取随机数的函数RAND()和RAND(x)
select rand(); -- 0 ≤ v ≤ 1.0
select rand(2); -- 0 ≤ v ≤ 1.0
-- ROUND(x)返回最接近于参数x的整数，对x值进行四舍五入
select round(2.5); -- 3
-- ROUND(x,y)返回最接近于参数x的数，其值保留到小数点后面y位，若y为负值，则将保留x值到小数点左边y位
select round(2.7456,2); -- 2.75
-- TRUNCATE(x,y)返回被舍去至小数点后y位的数字x
select truncate(2.535,2); -- 2.53
-- 符号函数SIGN(x),x的值为负、零或正时返回结果依次为-1、0或1
select sign(-9); -- -1
-- 幂运算函数POW(x,y),POWER(x,y)
select pow(2,3); -- 8
select power(2,3); -- 8
-- EXP(x)返回e的x乘方后的值
select exp(2); -- 7.389056....

字符串函数

1. 计算字符串字符数的函数和字符串长度的函数

   CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。

2. 合并字符串函数CONCAT(s1,s2,…)、 CONCAT_WS(x,s1,s2,…)

3. 替换字符串的函数INSERT(s1,x,len,s2)

4. 字母大小写转换函数 LOWER (str)或者LCASE (str)可以将字符串str中的字母字符全部转换成 小写字母。

   UPPER(str)或者UCASE(str可以将字符串str中的字母字符全部转换成大写字母。

5. 获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)

6. 填充字符串的函数LPAD(s1,len,s2)和RP

7. 删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)

8. 重复生成字符串的函数REPEAT(s,n)

9. 空格函数**SPACE(n)和替换函数REPLACE(s,s1,s2)**SPACE(n)返回一个由n个空格组成的字符串。

10. 比较字符串大小的函数STRCMP(s1,s2)

11. 获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len)

12. 匹配子串开始位置的函数

    LOCATE(str1,str)、POSITION(str1 IN str)和INSTR(str, str1)3个函数的作

13. 字符串逆序的函数REVERSE(s)

    REVERSE(s)将字符串s反转，返回的字符串的顺序和s字符串顺序相 反。

14. 返回指定字符串位置的函数FIELD(s,s1,s2,…,sn)

    FIELD(s,s1,s2,…,sn)返回字符串s在列表s1,s2,…,sn中第一次出现的位置，在找不到s的情况下，返回值为0。如果s为NULL，则返回值为0，原因 是NULL不能同任何值进行同等比较。

15. 返回子串位置的函数FIND_IN_SET(s1,s2)

    FIND_IN_SET(s1,s2)返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号‘,’分开的字符串组成的列表。如果s1不在s2或s2为空字符串，则返回值为0。如果任意一个参数为NULL，则返回值为NULL。 这个函数在第一个参数包含一个逗号‘,’时将无法正常运行。

-- 字符串长度
SELECT CHAR_LENGTH('helloworld');
-- 字符串拼接 
SELECT CONCAT('hello','-','world','-','你好'); 
-- 以*来拼接后面的字符串 
SELECT CONCAT_WS('*','hello','world','你好'); 
-- 查找hello,再后面的字符串列表中的位置 从 1 开始
SELECT FIELD('hello','你好','延安','helloworld'); 
SELECT FIND_IN_SET('hello','你好,延安,hello,world'); 
SELECT empid,empname,empsex FROM emp; 
SELECT CONCAT_WS('-',empid,empname,empsex) FROM emp; 
SELECT FORMAT("256998.855",1); 
-- 从第6个开始4个长度替换为ab 
SELECT INSERT('helloworld',6,4,'ab'); 
-- 检索world再helloworld中出现的位置 
SELECT LOCATE('world','helloworld'); 
-- 从左边截取 
SELECT LEFT('helloworld',8); 
-- 从右边截取 
SELECT RIGHT('helloworld',8); 
-- 从第6个位置开始截取到最后 
SELECT SUBSTR('helloworld',6); 
-- 从第6个位置开始截取三个长度 
SELECT SUBSTR('helloworld',6,3);

json

    JSON是一种取代XML的数据结构，和xml相比，它更小巧但描述能力却不差,由于它的小巧所以网络传输数据将减少更多流量从而加快速度。

那么，JSON到底是什么？

JSON就是一串字符串 只不过元素会使用特定的符号标注。

• {} 双括号表示对象

• [] 中括号表示数组

• “” 双引号内是属性或值

• : 冒号表示后者是前者的值(这个值可以是字符串、数字、也可以是另一个数组或对象)

    所以 {“name”: “Michael”} 可以理解为是一个包含name为Michael的对象；
    而[{“name”: “Michael”},{“name”: “Jerry”}]就表示包含两个对象的数组；
    当然了，你也可以使用{“name”:[“Michael”,“Jerry”]}来简化上面一部,这是一个拥有一个name数组的对象。

日期和时间函数

系统当前时间

-- 获取系统当前的时间
select now();
select sysdate();
-- 年月日
select current_date();
select curdate();
 -- 时分秒
select current_time();
select curtime();
-- 默认时间
select current_timestamp();

addXXX() 加上

-- addXXX()加上
select hiredate,year(now()) - year(hiredate) from employees;
-- 加上50天
select now(),adddate(now(),50);
-- 加上5分钟
select now(),adddate(now(),interval 5 minute);
-- 加上5小时
select now(),adddate(now(),interval 5 hour);
-- 加上5月
select now(),adddate(now(),interval 5 month);
-- 加上5年
select now(),adddate(now(),interval 5 year);

-- 工作时间不满足两年的员工信息
select * from employees where adddate(hiredate,365*2) > now();

select hiredate,adddate(hiredate,interval 2 year) from employees;
select * from employees where adddate(hiredate,interval 2 year) > now();

-- 加20秒
select now(),addtime(now(),20);

DATE_SUB(date,INTERVAL expr type) 减去多少天

-- 减去5天
select now(),date_sub(now(),interval 5 day);
-- 减去5年
select now(),date_sub(now(),interval 5 year);

-- 入职二十年以上的员工信息
select * from employees where date_sub(now(),interval 20 year) > hiredate;
select * from employees where date_add(hiredate,interval 20 year) < now();

DATEDIFF() 两个时间相差天数

-- DATEDIFF() 两个时间相差天数
select * from employees;
select datediff(now(),'2020-11-06');
select hiredate,datediff(now(),hiredate) from employees;
-- 小于1年的员工信息
select hiredate from employees where datediff(now(),hiredate) < 365;

DATE_FORMAT 日期格式化

简写	含义
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

-- DATE_FORMAT日期格式化（符号表）
select date_format(now(),'%y年%m月%d日'),date_format(now(),'%u周');
-- 求本周过生日的学生信息|员工信息
select hiredate,date_format(hiredate,'%u') from employees;
-- 年要用的是今年
select * from employees where date_format(now(),'%m%d') = date_format(hiredate,'%m%d');
-- 把年替换为2020年
select date_format(hiredate,'%m-%d') from employees;
-- 合并字符串函数CONCAT(s1,s2,…),今年
select concat('2020','-',date_format(hiredate,'%m-%d')) from employees;
-- 换成系统当前的年
select date_format(now(),'%y'),date_format(hiredate,'%m-%d') from employees;
select concat(date_format(now(),'%y'),'-',date_format(hiredate,'%m-%d')) from employees;
-- 取到改时间的周数
select date_format(concat(date_format(now(),'%y'),'-',date_format(hiredate,'%m-%d')),'%u') from employees;
-- 本周加入的员工信息
select * from employees where date_format(concat(date_format(now(),'%y'),'-',date_format(hiredate,'%m-%d')),'%u') = date_format(now(),'%u');

YEAR()，QUARTER()，MONTH()，MONTHNAME()，WEEK()，WEEKOFYEAR()，DAYNAME()，DAYOFWEEK()，WEEKDAY()，MINUTE()，SECOND()：

• YEAR(date)返回date对应的年份，范围是1970~2069.

• QUARTER(date)返回date对应的一年中的季度值，范围是1~4。

• MONTH(date),MONTHNAME(date)返回对应的月份，范围1-12。

• WEEK(date)返回当前在这个月是第几周。

• WEEKOFYEAR(date)返回当前在这一年是第几周。

• DAYNAME(date)返回对应的是本周的星期几（英文）。

• DAYOFWEEK(date)返回对应的是本周第几天（数字），按照周天为0开始算。

• WEEKDAY(date)返回对应的是本周第几天（数字），按照周一为0开始算。

• MINUTE(time)返回time对应的分钟数，范围是0~59。

• SECOND(time)返回time对应的秒数，范围是0~59。

-- 查询出本年销售记录	
select * from 销售表 where year(now()) = year(销售时间);
select * from 销售表 where date_format(now(),'%y') = date_format(销售时间,'%y');
-- 本周的销售记录(年，周)
select * from 销售表 where date_format(now,'%y') = date_format(销售时间,'%y') and date_format(now(),'%u') = date_format(销售时间,'%u');
-- 聚合函数综合
select sum(金额) from 销售表 where date_format(now,'%y') = date_format(销售时间,'%y') and date_format(now(),'%u') = date_format(销售时间,'%u');
-- MONTH(),MONTHNAME() 获取月份函数
select year(now()),month(now()),day(now());
select monthname(now());
-- DAYNAME(d)、DAYOFWEEK(d)和WEEKDAY(d)
select dayofweek(now()),weekday(now());
-- WEEK(d)和 WEEKOFYEAR(d):计算某天位于一年中的第几周
select week(now()),week(date_add(now(),interval 1 day),0),week(date_add(now(),interval 1 day),3);
select weekofyear(now());
-- 计算某天位于一年中的哪个季度
select quarter(now());
-- 查询销售记录中每年每个季度销售总金额
select year(hiredate),quarter(hiredate),count(1) from employees group by year(hiredate),quarter(hiredate);

EXTRACT(type FROM date) 函数所使用的时间间隔类型说明符。

select extract(year from now()),extract(month from now()),date_format(now(),'%y');

条件判断函数

1. IF(expr,v1,v2) 函数

   IF(expr, v1, v2)：如果表达式expr是TRUE(expr <> 0 and expr <> NULL)，则返回值为v1；否则返回值为v2.IF()的返回值为数字值或字符串值，具体情况视其所在语境而定。

   -- if(exp,v1,v2); exp为true,执行的是v1,否则是 v2
   select salary,if(salary > 13000,'高工资','低工资') from employees; 
   select salary,if(salary > 13000,if(salary > 20000,'高工资','中等'),'低工资') from employees;
   -- 性别 保存的值是 0 1 2 :备注 0:未知  1:男  2:女
   select if(sex = 0,'未知',if(sex = 1,'男','女')) from employees;
   

2. IFNULL(v1,v2) 函数

   IFNULL(v1,v2)：假如v1不为NULL，则IFNULL()的返回值为v1；否则其返回值为v2。

   -- IFNULL(v1,v2)：假如v1不为NULL，则IFNULL()的返回值为v1；否则其返回值为v2。
   select emali,ifnull(email,'无电子邮箱') from employees;
   

3. CASE函数

   CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2]…[ELSE rn+1] END：如果expr值等于某个vn，则返回对应位置THEN后面的结果；如果与所有值都不相等，则返回ELSE后面的rn+1。

   -- case等值判断:CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2]…[ELSE rn+1] END：如果expr值等于某个vn，则返回对应位置THEN后面的结果；如果与所有值都不相等，则返回ELSE后面的rn+1
   select * from employees;
   select employee_id,first_name,job_id,
   		case job_id when 'IT_PROG' then '程序员' 
   					when 'AD_PRES' then 'CEO' 
   					when 'PU_CLERK' then '销售人员' 
   		end 
   from employees;
   

系统信息函数

-- 系统信息函数
-- 版本
select version();
-- 查看连接详细内容
show processlist;-- 前100条
show full processlist; -- 所以信息
-- 获取用户名函数
select user(),system_user(),session_user(),current_user();

十三、索引

​ **索引用于快速找出在某个列中有一特定值的行。**不使用索引，MySQL 必须从第1条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达某个位置去搜寻数据文件，而不必查看所有数据。索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。

索引的含义和特点：

• 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。
• 使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。

例如：MySQL中索引的存储类型有两种；BTREE 和 HASH，具体和表的存储引擎相关：

• MyISAM 和 InnoDB存储引擎只支持BTREE索引；
• MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

索引的优点：

（1）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。

（2）可以大大加快数据的查询速度，这也是创建索引的主要原因。

（3）在实现数据的参考完整性方面，可以加速表和表之间的连接。

（4）在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

索引的缺点：

（1）**创建索引和维护索引要耗费时间，**并且随着数据量的增加所耗费的时间也会增加。

（2）索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

（3）当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

索引的设计原则：

​ 索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。设计索引时，应该考虑以下准则：

（1）**索引并非越多越好，**一个表中如有大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改时，索引也会进行调整和更新。

（2）**避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。**应该经常用于查询的字段创建索引，但要避免添加不必要的字段。

（3）数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

（4）**在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。**比如在学生表的“性别”字段上只 有“男”与“女”两个不同值，因此就无须建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。

（5）**当唯一性是某种数据本身的特征时，指定唯一索引。**使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。

（6）**在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，**如果待排序的列有多个，可以在这些列上建立组合索引。

索引分类：

1.普通索引和唯一索引

• 普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
• 唯一索引(unique)要求索引列的值必须唯一，但允许有空值。如果是组合索引， 则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

2.单列索引和组合索引

• 单列索引即一个索引只包含单个列，一个表可以有多个单列索引。

• 组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。

3.全文索引

• **全文索引(fulltext)**类型为FULLTEXT，在定义索引的列上支持值的全文查找， 允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、 VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。

4．空间索引

• **空间索引(spatial)**是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。 MySQL使用SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

索引创建：1.创建普通索引，最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。

create table dept_dak2(
	d_id int primary key auto_increment,
	d_name varchar(50),
	d_start year,
	index(d_start) //创建索引
)

show create table dept_dak2;
explain select * from dept_dak2 where d_start = 2019;

如果表已经存在，创建索引的语法有两种：

1.CREATE INDEX直接创建：

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

例如，在student表name字段上创建索引：

• 普通索引：

  CREATE INDEX index_name ON student (name)
  

• 唯一索引：

  CREATE UNIQUE index_name ON student (name)
  

2.ALTER TABLE时创建

alter table tabname add [unique|fulltext|spatial] index indexname(colname[length]);

• 主键索引：

  ALTER TABLE student ADD PRIMARY KEY (name);
  

• 唯一索引：

  ALTER TABLE student ADD UNIQUE INDEX index_name(name);
  

• 普通索引：

  ALTER TABLE student ADD INDEX index_name(name);
  

删除索引的语法：

alter table tabname drop index indexname;

创建组合索引：组合索引是在多个字段上创建一个索引。

CREATE TABLE test_db( 
    vid INT , 
    vname VARCHAR(50), 
    vage INT, 
    vinfo VARCHAR(200), 
    INDEX mul (vid,vname,vage) //创建组合索引
); 

由结果可以看到，vid、vname和vage字段上已经成功建立了一个名为 mul的组合索引。组合索引可起几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是遵从“最左前缀”：利用索引中最左边的列集来匹配行,这样的列集称为最左前缀。

删除索引：

alter table tabname drop index indexname

十四、事务

​ **数据库事务(transaction)**是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。 **事务由事务开始与事务结束之间执行的全部数据库操作组成。**事务是由一组SQL语句组成的逻辑处理单元，它的ACID特性如下：

（1）原子性（Atomicity）：事务具有原子不可分割的特性，要么一起执行，要么都不执行。

（2）一致性（Consistency）：在事务开始和事务结束时，数据都保持一致状态。

（3）隔离性（Isolation）：在事务开始和结束过程中，事务保持着一定的隔离特性，保证事务不受外部并发数据操作的影响。

（4）持久性（Durability）：事务完成后，数据将会被持久化到数据库中。

事务有两状态一个是提交【commit】，一个是回滚【rollback】。

begin; -- 开启事务
update account set abalance = abalance - 2000 where aid = 1;
update account set abalance = abalance + 2000 where aid = 2;
commit; -- 提交
或者
rollback; -- 回滚

还可以设置打点事务，即在保存点之前的会提交，保存点之后的会回滚。

start transaction;
update account set abalance = abalance - 10 where aid = 1;
savepoint A;
update account set abalance = abalance - 1000 where aid = 1;
update account set abalance = abalance + 1000 where aid = 2;
rollback to A;

事务隔离级别：一个事务对数据库修改与并行的另一个事务的**隔离程度。**两个并发事务同时访问数据库表相同的行时，可能存在以下三个问题：

1、幻想读，2、不可重复读取 ，3、脏读

为了处理这些问题，SQL标准定义了以下几种事务隔离级别:

脏读：事务A读取到事务B未提交的数据。脏数据所指的就是未提交的数据

**不可重复读：一个事务先后读取同一条记录，而事务在两次读取之间该数据被其它事务所修改，则两次读取的数据不同，我们称之为不可重复读。**例如，事务A查看1账号的金额是2000，此时想再账号上减2000.但是还没操作的时候，事务B将数据更改了，并在事务A之前提交了事务。此时事务A再次读取该数据的时候，已经改变了。

**幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为幻读。**例如，事务A查看第一次查看记录的时候为80。此时，事务B对该记录做了更改，事务A再看的时候，发现不是80，事务A就像出现环境一样。将隔离级别提高到Serializable就可以避免幻读。

查看mysql的事务隔离级别：

SELECT @@transaction_isolation; 【 select @@tx_isolation;】

设置mysql的事务隔离级别：

set session transaction isolation level repeatable read;

十五、数据库三大范式

​ 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。**范式是符合某一种设计要求的总结。**要想设计一个结构合理的关系型数据库，必须满足一定的范式。

在实际开发中最为常见的设计范式有三个：

1. 第一范式(确保每列保持原子性)

​ **第一范式是最基本的范式。**如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

​ **第一范式的合理遵循需要根据系统的实际需求来定。**比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

2. 第二范式(确保表中的每列都和主键相关)

​ 第二范式在第一范式的基础之上更进一层。 **第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。**也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

​ 比如要设计一个订单信息表，因为订单中可能会有多种商品，**所以要将订单编号和商品编号作为数据库表的联合主键，**如下表所示。

​ 这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。**这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。**所以在这里违反了第二范式的设计原则。

​ 而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

​ 这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。

3. 第三范式(确保每列都和主键列直接相关，而不是间接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

​ 比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

​ 这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。