MySQL（二、约束与设计）

MySQL约束与设计

DQL查询语句

排序

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

单列排序：

-- 按年龄进行升序排列
select * from students order by age;

组合排序：

同时对多个字段进行排序，如果第一个字段相等，则按第二个字段排序，以此类推。
SELECT 字段名 FROM 表名 ORDER BY 字段1 [asc|desc],字段2 [asc|desc];

-- 按年龄降序排序，年龄相同的再根据id进行升序排列
select * from students order by age desc,id;

聚合函数

我们之前查询时都是进行的横向查询，即根据条件，一行一行的进行判断，返回符合条件的结果，

而聚合函数则是纵向查询，对一列字段的值进行计算，然后返回一个结果值，聚合函数会忽略NULL值

聚合函数	说明
max(列名)	求出此列的最大值
min(列名)	求出此列的最小值
avg(列名)	求出此列的平均值
count(列名)	求出此列所有值的个数（行数）
sum(列名)	求出此列所有值的总和

• SELECT 聚合函数(列名) DROM 表名

-- 求出所有学生的平均成绩
select avg(score) from students;

聚合函数不会对NULL进行统计，如果我们不想忽略这一个为NULL的行，就要使用IFNULL(列名,默认值)如果为空就使用默认值

-- 如果成绩为空，就使用默认值0为它的分数
select ifnull(score,0) from students;
-- 将成绩为空的成绩设为0分加入平均分统计
select avg(ifnull(score,0)) from students; 

分组

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

-- 按年龄分组，得到表中每个年龄的人数
select age,count(age) from students group by age;
-- 按年龄分组，并且只要分组后组中人数至少为2的
select age,count(age) from students group by age having count(age)>1;

having和where的区别

• having：分组后筛选满足条件的组（在分组后过滤数据），后面可以使用聚合函数
• where：筛选出满足条件的数据后分组（先过滤数据后分组），后面不可以使用聚合函数

limit

• limit的作用是限制查询记录的条数
• SELECT * FROM 表名 LIMIT 起始行数 ,（省略就默认从0开始）返回的行数

-- 返回表第3，4，5条数据
select * from students limit 2,3;
-- 返回表中年龄大于18的前十条数据
select * from students where age>18 limit 10;

（limit会在分页查询中经常用到）

数据库备份和还原

备份

DOS下，不用登录MySQL，执行

mysqldump -u用户名 -p 数据库>保存的路径/保存的文件名.sql

然后输入mysql登录密码完成导出

还原

在指定的库中，执行

source 保存的路径;

数据库表的约束

为表设置约束，保证数据的正确性、有效性和完整性，一个表如果添加了约束，不正确的数据无法插入到表中。

（约束最好在创建表时添加。因为当表中有数据时再进行约束，如果有不符合的数据，那么添加约束就不会成功）

约束的分类：

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key
检查约束	check

主键约束

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

（通常不使用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键，主键是给数据库和程序使用的，主键没有什么含义，只是一个唯一的标识，只要不重复、非空即可）

特点：唯一、非空

1、在创建表的时候，准备一个字段，设置为主键

• 字段名 字段类型 primary key

2、 在已有表中添加主键

• alter table 表名 add primary key(字段名)

-- 创建表时
create table stu(
	id int primary key,
    name varchar(10),
    score int
);
-- 已有表中添加字段名为id的主键
alter table stu add primary key(id);
-- 删除主键约束
alter table stu drop primary key;

主键自增

如果主键让我们自己添加，当表中数据很多时，我们也不能保证不发生重复，所以我们每次插入数据时，让数据库自动生成主键字段的值。

在创建表时，我们可以通过auto_increment指定主键自增（默认时，主键值从1开始自增）

create table stu(
	id int primary key auto_increment,
    name varchar(10)
)auto_increment=1001;-- 设置主键从1001开始自增
-- 创建表后修改自增初始值
alter table stu auto_increment = 1001;

delete和truncate对自增的影响：

• delete删除一条记录后，对自增没有影响（删除id=10的行，下一个添加进来行id=11，删除所有行也一样）
• truncate删除表中所有数据以后，自增会重新开始

唯一约束

指定表中的一列不能出现重复的值

在创建表时设置唯一约束

• 字段名 字段类型 unique

-- 创建表设置name唯一，不能重复
create table stu (
	id int primary key auto_increment,
    name varchar(10) unique,
    age int
);

非空约束

指定表中一列值不能为null

在创建表时为字段设置非空约束

• 字段名 字段类型 not null

create table stu(
	id int primary key auto_increment,
    name varchar(10) unique,
    age int not null
)

设置非空后，添加行时，如果没有添加非空列，那么就会报错，因此，我们可以为字段设置默认值。如果插入数据时，没有指定值，那么就使用默认值

• 字段名 字段类型 default 默认值

create table stu(
	id int primary key auto_increment,
    name varchar(10) unique,
    age int not null default 0
)

外键约束

从表中一列值必须与主表相对应，该列值不能随意写

外键：从表中与主表相对应的一列

（主表：用来约束别人的表；从表：被约束的表）

创建时添加外键

• [constraint] 外键约束名称 foreign key(外键字段名) references 主表名(主键字段名)

在已有表中添加外键

• alter table 从表 add [constraint] 外键约束名称 foreign key(外键字段名) references 主表名(主键字段名)

create table class(
	id int primary key auto_increment,
    class_name varchar(20)
)
create table stu(
	id int primary key auto_increment,
    name varchar(10) unique,
    age int not null default 0,
    constraint stu_fk_class foreign key(class_id) references class(id)
)

删除外键约束

• alter table 从表 drop foreign key 外键约束名称

-- 删除表中的外键约束
alter table stu drop foreign key stu_fk_class;
-- 在已存在的表中添加外键约束
alter table stu add constraint stu_fk_class foreign key(class_id) references calss(id);

外键的级联

在修改和删除主表中的主键时，同时会更新或删除从表中的外键值，这就是级联操作

• on update cascade:级联更新，只能是创建表的时候创建级联关系，更新主表中的主键，从表中的外键列也自动同步更新

• on delete casade：级联删除，当删除主表时，自动删除从表中相关数据

表与表之间的关系

表和表的三中关系：

• 一对多：一个班级可以对应多个学生
  • 主外键关系
• 多对多：一个学生可以选择多个课程，一个课程也可以有多个学生学习
  • 借助中间表，分别一对多
• 一对一：一个人只有一个身份证号码，一个身份证号码只对应一个人
  • 比较特殊，从表中的外键设置唯一，而且从表中的主键又是外键

数据库设计规范

好的数据设计对数据的存储性能和后期的程序开发都会产生至关重要的影响。

• 建立一个科学的、规范化的数据库就需要一些规则来优化数据的设计和存储，这些规则就成为范式

三大范式

目前关系型数据库有六种范式：第一范式、第二范式、第三范式、巴斯-科德范式、第四范式、第五范式（又称为完美范式）

1、1NF

数据表中的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据。

即每一列都是不可再拆分的列，就是第一范式

例如一个地址字段，不能是地址：山东省济南市历下区，而是省份：山东省，城市：济南市，区：历下区

2、2NF

在满足第一范式的前提下，每一个字段都完全依赖于主键。

即一个表中只保存一种数据，就是第二范式

例如一个订单列表，所有字段都是依赖于这个订单编号的（订单编号、客户名、联系方式、订单日期、商品名、价格），而（商品名、价格）就不能在此表中出现，因为它们和订单信息没有关系，应该创建一个商品表与其匹配

3、3NF

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

即一个表中的数据都是和主键直接相关值，就是第三范式

例如订单表中，（订单编号、客户名、联系方式、订单日期）它是依赖于客户的，间接依赖于订单，所以，不符合第三范式，应该将客户名和联系方式再组成一个客户表，使用客户编号再与订单像关联