2.mysql约束与设计

1.DQL查询语句

1.1 排序

--通过 ORDER BY 子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名[ASC|DESC];
--ASC:升序，默认值
--DESC:降序

1.1.1单列排序

• 什么是单列排序：
  只按某一个字段进行排序，单列排序

1.1.3 组合排序

• 什么是组合排序?
  同时对多个字段进行排序，如果第1个字段相等，则按第2个字段排序，依次类推。
• 组合排序的语法：

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

--查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序
select * from student order by age desc,math asc;

1.2聚合函数

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

1.2.1五个聚合函数

1.2.2语法

SELECT 聚合函数(列名) FROM 表名;

--查询学生总数
select count(id) as 总人数 from student;
select count(*) as 总人数 from student;

我们发现对于NULL的记录不会统计，建议如果统计个数则不要使用有可能为null的列，但如果需要把NULL也统计进去呢？

IFNULL(列名，默认值) 如果列名不为空，返回这列的值。如果为NULL，则返回默认值。
--查询id字段，如果为null，则使用0代替
select ifnull(id,0) from student;

• 我们可以利用IFNULL()函数，如果记录为NULL，给个默认值，这样统计的数据就不会遗漏

select count(ifnull(id,0))from student;

1.3 分组

分组查询是指使用GROUPBY语句对查询信息进行分组，相同数据作为一组
SELECT 字段1,字段2...FROM 表名 GROUP BY 分组字段[HAVING条件];

• GROUPBY怎么分组的？

将分组字段结果中相同内容作为一组，如按性别将学生分成2组。

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

--按性别进行分组，求男生和女生数学的平均分
select sex,avg(math) from student3 group by sex;

实际上是将每组的math求了平均,返回每组统计的结果

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

1.3.1having与where的区别

1.4limit语句

1.4.1 limit的作用

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

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

1.4.2LIMIT语法格式

LIMIT offset,length;
offset：起始行数，从0开始计数，如果省略，默认就是0
length：返回的行数

--查询学生表中数据，从第3条开始显示，显示6条。
select * from student3 limit 2,6;

• 如果第一个参数是0可以省略写：

select * from student3 limit 5;

–最后如果不够5条，有多少显示多少

select * from student3 limit 10,5;

2、数据库表的约束

2.1数据库约束的概述

2.1.1约束的作用

• 对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

2.1.2约束种类

2.2主键约束

2.2.1主键的作用

• 用来唯一标识数据库中的每一条记录

2.2.2哪个字段应该作为表的主键？

• 通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。
  如：身份证，学号不建议做成主键

2.2.3创建主键

• 主键关键字：primary key
• 主键的特点：
  • 非空 not null
  • 唯一

2.2.4 创建主键方式

• 在创建表的时候给字段添加主键

字段名 字段类型 PRIMARY KEY

• 在已有表中添加主键

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

--创建表学生表st5,包含字段(id,name,age)将id做为主键
create table st5(
	id int primary key,
	name varchar(20),
	age int
)

2.2.4删除主键

--删除st5表的主键
alter table st5 drop primary key;

--添加主键
alter table st5 add primary key(id);

2.2.5主键自增

• 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值

AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

2.2.6修改自增长的默认值起始值

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

• 创建表时指定起始值

CREATE TABLE 表名(
列名 int primary key AUTO_INCREMENT ) AUTO_INCREMENT=起始值;


--指定起始值为1000
create table st4(
id int primary key auto_increment,
name varchar(20)
) auto_increment=1000;

• 创建好以后修改起始值

ALTER TABLE 表名 AUTO_INCREMENT = 起始值;
alter table st4 auto_increment = 2000;

2.2.7 DELETE和TRUNCATE对自增长的影响

• DELETE：删除所有的记录之后，自增长没有影响。
• TRUNCATE：删除以后，自增长又重新开始。

2.3 唯一约束

• 什么是唯一约束：表中某一列不能出现重复的值

2.3.1 唯一约束的基本格式

字段名 字段类型 UNIQUE

2.3.2 实现唯一约束

--创建学生表st7,包含字段(id,name),name这一列设置唯一约束,不能出现同名的学生
create table st7(
	id int,
	name varchar(20) unique
)

2.4非空约束

• 什么是非空约束：某一列不能为null。

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

字段名 字段类型 NOT NULL

--创建表学生表st8,包含字段(id,name,gender)其中name不能为NULL
create table st8(
	id int,
	name varchar(20) not null,
	gender char(1)
)

2.4.2默认值

字段名 字段类型 DEFAULT 默认值

--创建一个学生表st9，包含字段(id,name,address)，地址默认值是广州
create table st9(
	id int,
	name varchar(20),
	address varchar(20) default '广州'
)

• 疑问：如果一个字段设置了非空和唯一约束，该字段与主键的区别？

1.主键数在一个表中，只能有一个。不能出现多个主键。主键可以单列，也可以是多列
2.自增长只能用在主键上

2.5 外键约束

2.5.1 什么是外键约束

• 什么是外键：在从表中与主表主键对应的那一列，如：员工表中的dep_id
• 主表：一方，用来约束别人的表
• 从表：从方，被别人约束的表

2.5.2 创建约束的语法

• 新建表时增加外键

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

• 已有表增加外键

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

• 具体操作

--1)删除副表/从表employee
drop table employee;

--多方，从表
create table employee(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,--外键对应主表的主键--创建外键约束
	constraint emp_depid_fk foreign key (dep_id)references department(id)
)

2.5.3 删除外键

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

--删除employee表的emp_depid_fk外键
alter table employee drop foreign key emp_depid_fk;

--在employee表存在的情况下添加外键
alter table employee add constraint emp_depid_fk
foreign key (dep_id) references department(id);

2.5.6 外键的级联

• 什么是级联操作：
  在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作
  

--添加级联更新和级联删除
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,--外键对应主表的主键
--创建外键约束
constraint emp_depid_fk foreign key(dep_id)references
 department(id)on update cascade on delete cascade
)

2.6 数据约束小结

3、表与表之间的关系

3.1 表与表之间的三种关系

3.2 一对多

• 一对多（1:n）例如：班级和学生，部门和员工，客户和订单，分类和商品
• 一对多建表原则:在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键
  

2.3 多对多

• 多对多（m:n）例如：老师和学生，学生和课程，用户和角色
• 多对多关系建表原则:需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。
  

3.4 一对一

• 一对一（1:1）在实际的开发中应用不多.因为一对一可以创建成一张表。
• 两种建表原则：
  
  

3.7 表与表之间的关系小结

4、数据库设计

4.1 数据库规范化

4.1.1 什么是范式

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

4.1.2 三大范式：

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

4.2 1NF

4.2.1 概念

数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性。

4.2.2 班级表

4.3 2NF

4.3.1概念：

• 在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。
• 第二范式的特点：
  • 一张表只描述一件事情。
  • 表中的每一列都完全依赖于主键

4.3.2示例：

• 借书证表
  
• 分成两张表
  

4.4 3NF

4.4.1 概念

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

4.4.2 示例

• 学生信息表
  
• 存在传递的决定关系：
  学号 --> 所在学院–>学院地点
• 拆分成两张表
  

4.4.3三大范式小结