MySQL约束

最新推荐文章于 2024-02-24 17:21:54 发布

localhost-9527

最新推荐文章于 2024-02-24 17:21:54 发布

阅读量823

点赞数 6

分类专栏： MySQL数据库文章标签： MySQL约束默认值非空约束唯一外 DQL查询语句-条件查询-limit语句聚合函数模糊查询lik 外键的级联

本文链接：https://blog.csdn.net/weixin_40001125/article/details/88249382

版权

MySQL数据库专栏收录该内容

11 篇文章 9 订阅

订阅专栏

DQL语句

DQL查询语句-条件查询

讲解

前面我们的查询都是将所有数据都查询出来，但是有时候我们只想获取到满足条件的数据
语法格式：

SELECT 字段名... FROM 表名 WHERE 条件;

流程：取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回

准备数据

CREATE TABLE student3 (
  id int,
  name varchar(20),
  age int,
  sex varchar(5),
  address varchar(100),
  math int,
  english int
);

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

比较运算符

>大于
<小于
<=小于等于
>=大于等于
=等于
<>、!=不等于

具体操作：

查询math分数大于80分的学生

SELECT * FROM student3 WHERE math>80;

在这里插入图片描述

查询english分数小于或等于80分的学生

SELECT * FROM student3 WHERE english<=80;

在这里插入图片描述

查询age等于20岁的学生

SELECT * FROM student3 WHERE age=20;

在这里插入图片描述

查询age不等于20岁的学生

SELECT * FROM student3 WHERE age!=20;
SELECT * FROM student3 WHERE age<>20;

在这里插入图片描述

逻辑运算符

and(&&) 多个条件同时满足
or(||) 多个条件其中一个满足
not(!) 不满足

具体操作：

查询age大于35且性别为男的学生(两个条件同时满足)

SELECT * FROM student3 WHERE  age>35 AND sex='男';

在这里插入图片描述

查询age大于35或性别为男的学生(两个条件其中一个满足)

SELECT * FROM student333 WHERE age>35 OR sex='男';

在这里插入图片描述

查询id是1或3或5的学生

SELECT * FROM student3 WHERE id=1 OR id=3 OR id=5;

在这里插入图片描述
in关键字
语法格式：

SELECT * FROM 表名 WHERE 字段 in (值1, 值2, 值3);

in里面的每个数据都会作为一次条件，只要满足条件的就会显示

具体操作：

查询id是1或3或5的学生

SELECT * FROM student3 WHERE id IN (1,3,5);

在这里插入图片描述

查询id不是1或3或5的学生

SELECT * FROM student3 WHERE id NOT IN (1,3,5);

在这里插入图片描述

范围

BETWEEN 值1 AND 值2 -- 表示从值1到值2范围，包头又包尾

比如：age BETWEEN 80 AND 100
相当于： age>=80 && age<=100

具体操作：

查询english成绩大于等于75，且小于等于90的学生

SELECT * FROM student3 WHERE english>=75 AND english<=90;
SELECT * FROM student3 WHERE english BETWEEN 75 AND 90;

在这里插入图片描述

模糊查询like

目标

能够掌握模糊查询语法格式
在这里插入图片描述

讲解

LIKE表示模糊查询

SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';

满足通配符字符串规则的数据就会显示出来
所谓的通配符字符串就是含有通配符的字符串
MySQL通配符有两个：
%: 表示零个一个多个字符(任意多个字符)
_: 表示一个字符

具体操作：

查询姓马的学生

SELECT * FROM student3 WHERE NAME LIKE '马%';

在这里插入图片描述

查询姓名中包含’德’字的学生

SELECT * FROM student3 WHERE NAME LIKE '%德%';

在这里插入图片描述

查询姓马，且姓名有三个字的学生

SELECT * FROM student3 WHERE NAME LIKE '马__';

在这里插入图片描述

DQL查询语句-排序

讲解

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

SELECT 字段 FROM 表名 ORDER BY 排序的字段 [ASC|DESC];

ASC: 升序排序(默认)
DESC: 降序排序

1.2.1 单列排序

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

具体操作：

查询所有数据,使用年龄降序排序

SELECT * FROM student3 ORDER BY age DESC;

在这里插入图片描述
1.2.2 组合排序

组合排序就是先按第一个字段进行排序，如果第一个字段相同，才按第二个字段进行排序，依次类推。
上面的例子中，年龄是有相同的。当年龄相同再使用math进行排序

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

具体操作：

查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序

SELECT * FROM student3 ORDER BY age DESC, math DESC;

在这里插入图片描述

小结

排序的关键字：ORDER BY 字段名
升序：ASC
降序：DESC

DQL查询语句-聚合函数

目标

能够掌握五个聚合函数的使用

讲解

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

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

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

SELECT 字段名... FROM 表名;
SELECT 聚合函数(字段) FROM 表名;

具体操作：

查询学生总数

SELECT COUNT(english) FROM student3;

在这里插入图片描述
我们发现对于NULL的记录不会统计
IFNULL(expr1, expr2)的用法：假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为expr2

统计数量常用:

SELECT COUNT(*) FROM student3;

在这里插入图片描述

查询年龄大于40的总数

SELECT COUNT(*) FROM student3 WHERE age>40;

在这里插入图片描述

查询数学成绩总分

SELECT SUM(math) FROM student3;

在这里插入图片描述

查询数学成绩最高分

SELECT MAX(math) FROM student3;

在这里插入图片描述

查询数学成绩最低分

SELECT MIN(math) FROM student3;

在这里插入图片描述

查询数学成绩平均分

SELECT AVG(math) FROM student3;

在这里插入图片描述

小结

sum(列名)	求和
count(列名)	统计数量
max(列名)	最大值
min(列名)	最小值
avg(列名)	平均值

DQL查询语句-分组

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

SELECT 字段 FROM 表名 WHERE 条件 GROUP BY 字段名;

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

SELECT * FROM student3 GROUP BY sex;

这句话会将sex相同的数据作为一组
在这里插入图片描述
GROUP BY将分组字段的相同值作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

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

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

效果如下：
在这里插入图片描述
实际上是将每组的math进行求和,返回每组统计的结果

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

查询的时候没有查询出分组字段

具体步骤：
按性别分组

SELECT sex FROM student3 GROUP BY sex;

在这里插入图片描述

查询男女各多少人

1.查询所有数据,按性别分组。 
2.统计每组人数
SELECT sex, COUNT(*) FROM student3 GROUP BY sex;

在这里插入图片描述

查询年龄大于25岁的人,按性别分组,统计每组的人数

1.先过滤掉年龄小于25岁的人。
2.再分组。
3.最后统计每组的人数
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex;

在这里插入图片描述

查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
有很多同学可能会将SQL语句写出这样:
```
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex WHERE COUNT(*) >2;
```

注意: 并只显示性别人数>2的数据属于分组后的条件,对于分组后的条件需要使用having子句

SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex HAVING COUNT(*) >2;
只有分组后人数大于2的`男`这组数据显示出来

在这里插入图片描述

having与where的区别

having是在分组后对数据进行过滤.
where是在分组前对数据进行过滤
having后面可以使用聚合函数
where后面不可以使用聚合函数

小结

分组的语法格式？SELECT 字段 FROM 表名 WHERE 条件 GROUP BY 字段 HAVING 条件;
分组的原理？先将相同数据作为一组,返回每组的第一条数据,单独分组没有意义,分组后跟聚合函数操作
where和having的区别？
having是在分组后对数据进行过滤.
where是在分组前对数据进行过滤
having后面可以使用聚合函数
where后面不可以使用聚合函数

DQL查询语句-limit语句

准备数据：

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES 
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);

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

LIMIT语句格式:

SELECT 字段 FROM 表名 WHERE 条件 LIMIT offset, length;

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

具体步骤：

查询学生表中数据，跳过前面2条，显示6条

我们可以认为跳过前面2条，取6条数据
SELECT * FROM student3 LIMIT 2,6;

在这里插入图片描述
LIMIT的使用场景：分页

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

在这里插入图片描述
假设我们一每页显示5条记录的方式来分页，SQL语句如下：

-- 每页显示5条
-- 第一页： LIMIT 0,5;	跳过0条，显示5条
-- 第二页： LIMIT 5,5;  跳过5条，显示5条
-- 第三页： LIMIT 10,5; 跳过10条，显示5条
SELECT * FROM student3 LIMIT 0,5;
SELECT * FROM student3 LIMIT 5,5;
SELECT * FROM student3 LIMIT 10,5;

在这里插入图片描述

注意：

如果第一个参数是0可以简写：
SELECT * FROM student3 LIMIT 0,5;
SELECT * FROM student3 LIMIT 5;
LIMIT 10，5; – 不够5条，有多少显示多少

小结

LIMIT语句的使用格式？

SELECT 字段 FROM 表名 LIMIT 跳过的条数, 显示条数;

SELECT 字段名 FROM 表名 WHERE 条件 GROUP BY 分组列名 HAVING 条件 ORDER BY 排序列名 LIMIT 跳过行数, 返回行数;

数据库约束的概述

数据库约束的作用

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

约束种类

PRIMARY KEY: 主键约束
UNIQUE: 唯一约束
NOT NULL: 非空约束
DEFAULT: 默认值
FOREIGN KEY: 外键约束

主键约束

主键的作用

用来唯一标识一条记录。

为什么需要主键约束

有些记录的 name,age,score 字段的值都一样时,那么就没法区分这些数据,造成数据库的记录不唯一,这样就不方便管理数据。
在这里插入图片描述
每张表都应该有一个主键，并且每张表只能有一个主键。

哪个字段作为表的主键

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

创建主键

主键：PRIMARY KEY
主键的特点：

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

创建主键方式：

在创建表的时候给字段添加主键
```
字段名 字段类型 PRIMARY KEY
```

在已有表中添加主键

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

具体操作：

创建表学生表st5, 包含字段(id, name, age)将id做为主键具体操作：
创建表学生表st5, 包含字段(id, name, age)将id做为主键

CREATE TABLE st5 (
	id INT PRIMARY KEY, -- id是主键
	NAME VARCHAR(20),
	age INT
);

在这里插入图片描述

添加数据

INSERT INTO st5 (id, NAME) VALUES (1, '唐伯虎');
INSERT INTO st5 (id, NAME) VALUES (2, '周文宾');
INSERT INTO st5 (id, NAME) VALUES (3, '祝枝山');
INSERT INTO st5 (id, NAME) VALUES (4, '文征明');

插入重复的主键值

-- 主键是唯一的不能重复：Duplicate entry '1' for key 'PRIMARY'
INSERT INTO st5 (id, NAME) VALUES (1, '文征明2');

插入NULL的主键值

-- 主键是不能为空的：Column 'id' cannot be null
INSERT INTO st5 (id, NAME) VALUES (NULL, '文征明3');

删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;

具体操作：

删除st5表的主键

ALTER TABLE st5 DROP PRIMARY KEY;

在这里插入图片描述

主键自增

讲解

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

字段名 字段类型 PRIMARY KEY AUTO_INCREMENT

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

具体操作：

创建学生表st6, 包含字段(id, name, age)将id做为主键并自动增长

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

插入数据

-- 主键默认从1开始自动增长
INSERT INTO st6 (NAME, age) VALUES ('唐僧', 22);
INSERT INTO st6 (NAME, age) VALUES ('孙悟空', 26);
INSERT INTO st6 (NAME, age) VALUES ('猪八戒', 25);
INSERT INTO st6 (NAME, age) VALUES ('沙僧', 20);

在这里插入图片描述
扩展
默认地AUTO_INCREMENT 的开始值是1，如果希望修改起始值,请使用下列SQL语法

ALTER TABLE 表名 AUTO_INCREMENT=起始值;

DELETE和TRUNCATE的区别

DELETE 删除表中的数据，但不重置AUTO_INCREMENT的值。
TRUNCATE 摧毁表，重建表，AUTO_INCREMENT重置为1

唯一约束

唯一约束的作用

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

唯一约束的基本格式

字段名 字段类型 UNIQUE

具体步骤：

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

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

添加一些学生

INSERT INTO st7 VALUES (1, '貂蝉');
INSERT INTO st7 VALUES (2, '西施');
INSERT INTO st7 VALUES (3, '王昭君');
INSERT INTO st7 VALUES (4, '杨玉环');

-- 插入相同的名字出现name重复: Duplicate entry '貂蝉' for key 'name'
INSERT INTO st7 VALUES (5, '貂蝉');

-- 出现多个null的时候会怎样？因为null是没有值，所以不存在重复的问题
INSERT INTO st3 VALUES (5, NULL);
INSERT INTO st3 VALUES (6, NULL);

非空约束

非空约束的作用

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

非空约束的基本语法格式

字段名 字段类型 NOT NULL

具体操作：

创建表学生表st8, 包含字段(id,name,gender)其中name不能为NULL

CREATE TABLE st8 (
	id INT,
	NAME VARCHAR(20) NOT NULL,
	gender CHAR(2)
);

添加一些完整的记录

INSERT INTO st8 VALUES (1, '郭富城', '男');
INSERT INTO st8 VALUES (2, '黎明', '男');
INSERT INTO st8 VALUES (3, '张学友', '男');
INSERT INTO st8 VALUES (4, '刘德华', '男');

-- 姓名不赋值出现姓名不能为null: Column 'name' cannot be null
INSERT INTO st8 VALUES (5, NULL, '男');

默认值

默认值的作用

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

默认值格式

字段名 字段类型 DEFAULT 默认值

具体步骤：

创建一个学生表 st9，包含字段(id,name,address)，地址默认值是广州

CREATE TABLE st9 (
	id INT,
	NAME VARCHAR(20),
	address VARCHAR(50) DEFAULT '广州'
);

添加一条记录,使用默认地址

INSERT INTO st9 (id, NAME) VALUES (1, '刘德华');

在这里插入图片描述

添加一条记录,不使用默认地址

INSERT INTO st9 VALUES (2, '张学友', '香港');

面试题:

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

1.一张表只有一个主键

2.一张表可以多个字段添加非空与唯一约束

3.主键可以自动增长,自己添加的非空与唯一约束字段无法自动增长

小结

说出默认值的作用？不添加这个字段,就会使用默认值
给字段添加默认值格式？字段名字段类型 DEFAULT 默认值

外键约束

单表的缺点

创建一个员工表包含如下列(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, '销售部', '深圳');

缺点:表中出现了很多重复的数据(数据冗余)，如果要修改研发部的地址需要修改3个地方
在这里插入图片描述
解决方案:将一张表分成2张表(员工表和部门表)

-- 创建部门表
CREATE TABLE department (
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);

-- 创建员工表
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT
);

-- 添加2个部门
INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州'), ('销售部', '深圳');

-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES 
('张三', 20, 1), 
('李四', 21, 1), 
('王五', 20, 1), 
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

问题：
当我们在employee的dep_id里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门，不能出现这种情况。employee的dep_id中的内容只能是department表中存在的id
在这里插入图片描述
需要达到目的:需要约束dep_id只能是department表中已经存在id
解决方式:使用外键约束

什么是外键约束

一张中的某个字段引用另一张表的主键
主表：约束别人的表,把数据给别人使用功能
副表/从表：被别人约束的表,使用别人的数据
在这里插入图片描述

创建外键

新建表时增加外键：
```
CONSTRAINT [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
```
关键字解释：
CONSTRAINT: 添加约束
FOREIGN KEY(外键字段名): 将某个字段作为外键
REFERENCES 主表名(主键字段名) : 外键引用主表的主键

已有表增加外键：

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

具体操作：

副表/从表: 被别人约束,表结构添加外键约束
删除副表/从表 employee
创建从表 employee 并添加外键约束

CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,
	-- 添加一个外键
	-- 外键取名公司要求,一般fk结尾
	CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)
);

正常添加数据

INSERT INTO employee (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

部门错误的数据添加失败

INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);

删除外键

ALTER TABLE 表名 DROP FROEIGN KEY 外键名称;

具体操作：

删除employee表的emp_depid_ref_dep_id_fk外键

ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;

在employee表情存在况下添加外键

ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);

外键的级联

目标

了解外键的级联操作

讲解

要把部门表中的id值2，改成5，能不能直接修改呢？

UPDATE department SET id=5 WHERE id=2;

不能直接修改:Cannot delete or update a parent row: a foreign key constraint fails 如果副表(员工表)中有引用的数据,不能直接修改主表(部门表)主键

要删除部门id等于1的部门, 能不能直接删除呢？

DELETE FROM department WHERE id = 1;

不能直接删除:Cannot delete or update a parent row: a foreign key constraint fails 如果副表(员工表)中有引用的数据,不能直接删除主表(部门表)数据

什么是级联操作

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

具体操作：

删除employee表
重新创建employee表，添加级联更新和级联删除

CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT,
	dep_id INT,
	-- 添加外键约束,并且添加级联更新和级联删除
	CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
);

再次添加数据到员工表和部门表

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