SQL约束

1. DQL查询语句

1.1 排序

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

单列排序

只按某一个字段进行排序，单列排序。

-- 查询所有数据,使用年龄降序排序
select * from student order by age desc;

组合排序

同时对多个字段进行排序，如果第 1 个字段相等，则按第 2 个字段排序，依次类推。

组合排序的语法：

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

1.2 聚合函数

使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值。聚合函数会忽略空值 NULL。

五个聚合函数

SQL中的聚合函数	作用
max(列名)	求这一列的最大值
min(列名)	求这一列的最小值
avg(列名)	求这一列的平均值
count(列名)	统计这一列有多少条记录
sum(列名)	对这一列求总和

语法：

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

我们发现对于 NULL 的记录不会统计，建议如果统计个数则不要使用有可能为 null 的列，

如果需要把 NULL则采用函数:

IFNULL(列名，默认值) -- 如果列名不为空，返回这列的值。如果为 NULL，则返回默认值。

1.3 分组

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

GROUP BY 怎么分组的？

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

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

having 与 where 的区别

子名	作用
where 子句	1) 对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，即先过滤再分组。 2) where 后面不可以使用聚合函数
having 子句	1) having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。 2) having 后面可以使用聚合函数

1.4 limit 语句

limit 的作用：

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

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

LIMIT 语法格式

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

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

LIMIT 的使用场景：

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

2. 数据库表的约束

2.1 数据库约束的概述

约束的作用：

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

约束种类：

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key
检查约束	check 注: mysql不支持

2.2 主键约束

主键的作用

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

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

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

如：身份证，学号不建议做成主键

创建主键

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

创建主键的方式:

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

字段名 字段类型 PRIMARY KEY

在已有表中添加主键

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

删除主键

ALTER TABLE 表名 drop PRIMARY KEY;

主键自增

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

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

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

默认地 AUTO_INCREMENT 的开始值是 1，

创建表时指定起始值

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

创建好以后修改起始值

ALTER TABLE 表名 AUTO_INCREMENT=起始值;

DELETE 和 TRUNCATE 对自增长的影响

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

2.3 唯一约束

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

唯一约束的基本格式

字段名 字段类型 UNIQUE

null 没有数据，不存在重复的问题

2.4 非空约束

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

非空约束的基本语法格式

字段名 字段类型 NOT NULL

默认值

字段名 字段类型 DEFAULT 默认值

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

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

2.5 外键约束

单表的缺点

创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id 主键并自动增长,添加 5 条数据

CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(30),
    age INT,
    dep_name VARCHAR(30),
    dep_location VARCHAR(30)
);
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');

以上数据表的缺点:

• 数据冗余
• 后期还会出现增删改的问题
  

解决方案:

-- 解决方案：分成 2 张表
-- 创建部门表(id,dep_name,dep_location)
-- 一方，主表
create table department(
    id int primary key auto_increment,
    dep_name varchar(20),
    dep_location varchar(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方，从表
create table employee(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int -- 外键对应主表的主键
)
-- 添加 2 个部门
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
select * from department;
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
select * from employee;

**问题：**当我们在 employee 的 dep_id 里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门，实际应用中不能出现这种情况。employee 的 dep_id 中的数据只能是 department 表中存在的 id

目标: 需要约束 dep_id 只能是 department 表中已经存在 id

解决方式: 使用外键约束

什么是外键约束

什么是外键：在从表中与主表主键对应的那一列，如：员工表中的 dep_id

主表： 一方，用来约束别人的表

从表： 多方，被别人约束的表

创建约束的语法

新建表时增加外键：

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

已有表增加外键：

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

删除外键

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);

外键的级联

出现新的问题：

select * from employee;
select * from department;
-- 要把部门表中的 id 值 2，改成 5，能不能直接更新呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
update department set id=5 where id=2;
-- 要删除部门 id 等于 1 的部门, 能不能直接删除呢？
-- Cannot delete or update a parent row: a foreign key constraint fails
delete from department where id=1;

什么是级联操作：

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

级联操作语法	描述
ON UPDATE CASCADE	级联更新,只能是创建表的时候创建级联关系,更新主表中的主键，从表中的外键 列也自动同步更新
ON DELETE CASCADE	级联删除

2.6 约束小结

约束名	关键字	说明
主键	primary key	1) 唯一 2) 非空
默认	default	如果一列没有值，使用默认值
非空	not null	这一列必须有值
唯一	unique	这一列不能有重复值
外键	foreign key	主表中主键列，在从表中外键列