MySQL查询与约束 数据库备份 表关系 三范式

一.DQL语句

1.排序

通过ORDER BY子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];

ASC: 升序, 默认是升序
DESC: 降序

1.1 单列排序

单列排序就是使用一个字段排序
例如:

SELECT * FROM student3 ORDER BY age DESC;

1.2 组合排序

组合排序就是先按第一个字段进行排序，如果第一个字段相同，才按第二个字段进行排序，依次类推。

SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];

2. 聚合函数

之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值。另外聚合函数会忽略空值

五个聚合函数：
count()： 统计指定列记录数，记录为NULL的不统计
sum()： 计算指定列的数值和，如果不是数值类型，那么计算结果为0
max()： 计算指定列的最大值
min()： 计算指定列的最小值
avg()： 计算指定列的平均值，如果不是数值类型，那么计算结果为0

注意: ifnull(expr1,expr2)假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为expr2

聚合函数的使用：写在 SQL语句SELECT后 字段名的地方

SELECT 字段名... FROM 表名;
SELECT COUNT(age) FROM 表名;

3. 分组

分组查询是指使用 GROUP BY语句对查询信息进行分组，相同数据作为一组

SELECT 字段1,字段2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];

SELECT 字段1,字段2... FROM 表名 where 条件 GROUP BY 分组字段 [HAVING 条件];

GROUP BY怎么分组的？
将分组字段结果中相同内容作为一组

GROUP BY将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

分组后聚合函数的作用？不是操作所有数据，而是操作一组数据。
例如:

SELECT SUM(math), sex FROM student3 GROUP BY sex;

注意事项：当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的

总结:

select 字段,聚合函数 from 表名 [where 条件] group by 条件[having 条件] [order by 条件;

4. limit语句

LIMIT是限制的意思，所以LIMIT的作用就是限制查询记录的条数。

SELECT *|字段列表 [as 别名] FROM 表名 [WHERE子句] [GROUP BY子句][HAVING子句][ORDER BY子句][LIMIT子句];

LIMIT语法格式：

LIMIT offset,length;   或者limit length;

offset是指偏移量，可以认为是跳过的记录数量，默认为0
length是指需要显示的总记录数

LIMIT的使用场景：分页
比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一页显示固定的条数。

5. 日期和字符串函数

日期函数

• now(); 获取年月日时分秒
• curdate() ; 获取年月日
• curtime(); 获取当前时分秒
• YEAR(date) ;返回date 对应的年份,范围是从1000到9999。
• MONTH(date) ;返回date 对应的月份，范围时从 1 到 12。
• DAYOFMONTH(date) 表示返回date是当月的第几天，1号就返回1，… ,31号就返回31)
• HOUR(time);返回该date或者time的hour值，值范围（0-23）
• MINUTE(time);返回该time的minute值，值范围（0-59）
• SECOND(time);返回该time的minute值，值范围（0-59）
• DATE_FORMAT(date，format);根据format 字符串安排date 值的格式。 将日期以字符串形式输出
• STR_TO_DATE(str,format);这是DATE_FORMAT() 函数的倒转 ,将字符串转换为日期输出

字符串函数

• LENGTH(str)：返回字符串的存储长度
• CONCAT(str1,str2,…) ;返回结果为连接参数产生的字符串
• SUBSTRING(str,pos) , SUBSTRING(str,pos,len) ,
  • 不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。
  • 带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。
• TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str); 默认去除字符串str中的remstr的前后缀
  • both 去前后缀
  • leading 去前缀
  • trailing 去后缀
• REPLACE(str,from_str,to_str) ;返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
• INSERT(str,pos,len,newstr);在指定位置,使用newstr替换指定长度的字符串

二.数据库备份

1. 备份的应用场景

在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

2.source命令备份与还原

备份格式：

mysqldump -u用户名 -p密码 数据库 > 文件的路径

还原格式：

SOURCE 导入文件的路径;

注意：还原的时候需要先登录MySQL,并选中对应的数据库

三.数据库约束

对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。
约束种类：

• PRIMARY KEY: 主键(唯一,非空)
• UNIQUE: 唯一(不能重复)
• NOT NULL: 非空(不能为null)
• DEFAULT: 默认
• FOREIGN KEY: 外键(约束多表情况)

1.主键

1.1 主键的作用

用来唯一标识一条记录，每个表都应该有一个主键，并且每个表只能有一个主键。
有些记录的 name,age,score 字段的值都一样时,那么就没法区分这些数据,造成数据库的记录不唯一,这样就不方便管理数据

主键的特点: 唯一(不能重复),非空(不能为null)

哪个字段应该作为表的主键？
通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

1.2 创建主键

主键：PRIMARY KEY

主键的特点：

• 主键必须包含唯一的值
• 主键列不能包含NULL值

创建主键方式：
在创建表的时候给字段添加主键

字段名 字段类型 PRIMARY KEY

在已有表中添加主键

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

1.3 删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

1.4 主键自增

主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

例如:

CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) ,
	age INT
);

默认地AUTO_INCREMENT 的开始值是1，如果希望修改起始值,请使用下列SQL语法

ALTER TABLE 表名 AUTO_INCREMENT=起始值;

DELETE和TRUNCATE的区别

• DELETE 删除表中的数据，但不重置AUTO_INCREMENT的值。

• TRUNCATE 摧毁表，重建表，AUTO_INCREMENT重置为1

2. 唯一

在这张表中这个字段的值不能重复

2.1 唯一约束的基本格式

字段名 字段类型 UNIQUE

2.2 实现唯一约束

CREATE TABLE student(
	id INT,
	NAME VARCHAR(20) UNIQUE
);

3. 非空

这个字段必须设置值,不能是NULL

3.1 非空约束的基本语法格式

字段名 字段类型 NOT NULL

4. 默认值

往表中添加数据时,如果不指定这个字段的数据,就使用默认值

默认值格式

字段名 字段类型 DEFAULT 默认值

如果一个字段设置了非空与唯一约束，该字段与主键的区别

• 一张表中只有一个字段可以设置为主键
• 一张表中可以多个字段非空与唯一约束
• 主键可以自动增长，非空与唯一约束的字段不能自动增长

create table 表名(
	字段 字段类型 primary key auto_increment,
	字段 字段类型 [约束],
	.......
);

5. 外键

5. 1 单表的缺点

缺点:表中出现了很多重复的数据(数据冗余)

5.2 什么是外键约束

一张表中的某个字段引用另一个表的主键
主表： 约束别人
副表/从表： 使用别人的数据，被别人约束

5.3 创建外键

1. 新建表时增加外键：

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

关键字解释：

CONSTRAINT – 约束关键字
FOREIGN KEY(外键字段名) –- 某个字段作为外键
REFERENCES – 主表名(主键字段名) 表示参照主表中的某个字段

2. 已有表增加外键：

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

5.4 删除外键

ALTER TABLE 从表 drop foreign key 外键名称;

5.5 外键的级联

什么是级联操作：
在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作
ON UPDATE CASCADE – 级联更新，主键发生更新时，外键也会更新
ON DELETE CASCADE – 级联删除，主键发生删除时，外键也会删除

四.表关系

1. 一对一

两种建表原则：

• 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一UNIQUE
• 外键是主键：主表的主键和从表的主键，形成主外键关系

2. 一对多

一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

3. 多对多

多对多关系建表原则:
需要创建第三张表(中间表)，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

五.三范式

1. 什么是范式

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

2. 范式的基本分类

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

3. 第一范式

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

第一范式：每一列不能再拆分

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

4. 第二范式

在满足第一范式的前提下，表中的每一个字段都完全依赖于主键,不能局部依赖主键

所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之，第二范式就是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况。比如有一个主键有两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不符合第二范式。

第二范式的特点：

1. 一张表只描述一件事情。
2. 表中的每一列都完全依赖于主键
   

总结：
如果不准守第二范式，数据冗余，相同数据无法区分。遵守第二范式减少数据冗余，通过主键区分相同数据。

5. 第三范式

在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键。

简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足2NF的基础上，任何非主列不得传递依赖于主键。所谓传递依赖，指的是如果存在"A → B → C"的决定关系，则C传递依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：主键列 → 非主键列x → 非主键列y

第三范式：从表的外键必须使用主表的主键

总结：
如果不遵守第三范式，可能会有相同数据无法区分，修改数据的时候多张表都需要修改（不方便修改）。遵守第三范式通过id可以区分相同数据，修改数据的时候只需要修改一张表（方便修改）。