1.DQL查询语句的使用
1)排序查询
语法:order by 字句
order by 排序字段1 排序方式1,排序字段2 排序方式2......
排序方式:
ASC:升序,默认的
DESC:降序
注意:如果有多个排序条件,则当前面的条件值一样时,才会判断第二条件
按照数学成绩排名(默认升序)
SELECT * FROM student ORDER BY math;
SELECT * FROM student ORDER BY math ASC;
按照数学成绩降序排名
SELECT * FROM student ORDER BY math DESC;
按照数学成绩升序排名,数学成绩一样的,按照英语成绩的降序排名
SELECT * FROM student ORDER BY math ASC,english DESC;
2)聚合查询
将一列数据作为一个整体,进行纵向的计算
count:计算个数
一般选择非空的列:选择主键
count(*):只要这一列数据有一个不为null,就能够算一个记录
max:计算最大值
min:计算最小值
sum:计算和
avg:计算平均值
注意:聚合函数的计算,会排除非null值
解决方案:1.选择不包含非空的列进行计算
2.使用IFNULL函数
计算学生总人数
SELECT COUNT(NAME) FROM student;
计算英语学科人数(非空被排除)
SELECT COUNT(english) FROM student;
解决办法:
SELECT COUNT(IFNULL(english,0)) FROM student;
计算数学成绩的最大值
SELECT MAX(math) FROM student;
计算数学成绩的最小值
SELECT MIN(math) FROM student;
计算数学成绩的和
SELECT SUM(math) FROM student;
计算数学和英语的总和
SELECT SUM(math+english) FROM student;
计算数学的平均分
SELECT AVG(math) FROM student;
3)分组查询
语法:group by 分组字段;
注意:
1.分组之后只能查询的字段:分组字段、聚合函数
2.where和having的区别
1.where 在分组之前限定,如果不满足条件,则不参与分组,having在分组之后进行限定,如果不满足条件,则不会被查询出来
2.where后不可以跟聚合函数,having可以进行聚合函数的判断
按照性别分组,分别查询男、女同学的平均分
SELECT sex ,AVG(math) FROM student GROUP BY sex;
按照性别分组,分别查询男、女同学数学的平均分,人数
SELECT sex ,AVG(math),COUNT(id) FROM student GROUP BY sex;
按照性别分组,分别查询男、女同学数学的平均分、人数,要求分数低于70分的人不参与分组
SELECT sex ,AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
按照性别分组,分别查询男、女同学数学的平均分,人数,要求,分数低于70分的人不参与分组,分组后人数大于两个(分完组后对结果集,再限定一次)
SELECT sex ,AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;
按照性别分组,分别查询男、女同学数学的平均分,人数,要求,分数低于70分的人不参与分组,分组后人数大于两个,人数是起别名方式,AS省略
SELECT sex ,AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
4)分页查询
1.语法:limit 开始的索引,每页查询的条数
2.公式:开始的索引 =(想看的页码 - 1)* 每页显示的条数
每页显示三条记录,查看第一页内容
SELECT * FROM student LIMIT 0,3;
查看第二页内容
SELECT * FROM student LIMIT 3,3;
3.limit语法 是一个MySQL “方言”,既limit只在MySQL中是分页的语法
2.约束
1)概念:
对表中的数据进行限定,从而保证数据的正确性、有效性和完整性
例如上面的学生表写入数据可以不写姓名,而写入其他内容,但是查询时是没有此人的,要对此进行约束
约束分类:
主键约束:primary key
非空约束:not null
唯一约束:unique
外键约束:foreign key
2)非空约束:not null
1.创建表时添加非空约束
CREATE TABLE stu (
id INT,
NAME VARCHAR(20) NOT NULL -- name为非空约束
);
mysql> INSERT INTO stu(id,name) VALUES(1,'zhangsan');# 向表中写入数据 Query OK, 1 row affected (0.01 sec) mysql> select * from stu;# 正常写入 +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | +------+----------+ 1 row in set (0.00 sec) mysql> INSERT INTO stu(id) VALUES(2);# 当向表中写入id数据而不写入name时报错 ERROR 1364 (HY000): Field 'name' doesn't have a default value mysql> select * from stu;# 数据没有写入 +------+----------+ | id | name | +------+----------+ | 1 | zhangsan | +------+----------+ 1 row in set (0.00 sec)
2.删除非空约束
ALTER TABLE 表名 MODIFY 列号 类型;
mysql> ALTER TABLE stu MODIFY NAME VARCHAR(20);# 修改约束 Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> INSERT INTO stu(id) VALUES(2);# 写入数据只有id未报警 Query OK, 1 row affected (0.00 sec) mysql> select * from stu;# 写入到表中 +------+----------+ | id | NAME | +------+----------+ | 1 | zhangsan | | 2 | NULL | +------+----------+ 2 rows in set (0.00 sec) mysql> desc stu;# 查看规则,name列是可以为空的 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | NAME | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
3.创建表完后添加非空约束
mysql> ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;# 对name添加约束条件 ERROR 1265 (01000): Data truncated for column 'NAME' at row 2 mysql> select * from stu;# 报错是因为将要约束非空的name列有一空行 +------+----------+ | id | NAME | +------+----------+ | 1 | zhangsan | | 2 | NULL | +------+----------+ 3 rows in set (0.00 sec) mysql> DELETE FROM stu WHERE id=2;# 删除name列为空的行 Query OK, 2 rows affected (0.01 sec) mysql> select * from stu; +------+----------+ | id | NAME | +------+----------+ | 1 | zhangsan | +------+----------+ 1 row in set (0.00 sec) mysql> ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;# 再次添加约束规则,添加成功 Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> desc stu;# 查看规则,name列不能为空 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | NAME | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
3)唯一约束:unique
1.创建表时添加唯一约束
CREATE TABLE stu(
id int,
phone_number varchar(20) unique
);
mysql> desc stu;# 唯一约束下面phone_number有显示 +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | phone_number | varchar(20) | YES | UNI | NULL | | +--------------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> INSERT INTO stu(id,phone_number) VALUES(1,1111);# 写入一条数据 Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO stu(id,phone_number) VALUES(2,1111);# 再写入一行数据phone_number相同,出现唯一报错 ERROR 1062 (23000): Duplicate entry '1111' for key 'phone_number' mysql> SELECT * FROM STU;# 第二次数据也没有写入 +------+--------------+ | id | phone_number | +------+--------------+ | 1 | 1111 | +------+--------------+ 1 row in set (0.00 sec) mysql> INSERT INTO stu(id,phone_number) VALUES(2,1112);# 把phone_number号更改后可以写入 Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM STU; +------+--------------+ | id | phone_number | +------+--------------+ | 1 | 1111 | | 2 | 1112 | +------+--------------+ 2 rows in set (0.00 sec) mysql> INSERT INTO stu(id) VALUES(3);# id为3,没有phone_number Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO stu(id) VALUES(4);# id为4,也没有phone_number,但可以写入,可以理解为null代表着不确定性,两个null都不确定,互相之间不重复,所以与唯一性不冲突 Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM STU; +------+--------------+ | id | phone_number | +------+--------------+ | 1 | 1111 | | 2 | 1112 | | 3 | NULL | | 4 | NULL | +------+--------------+ 4 rows in set (0.00 sec) mysql> desc stu;# 唯一约束被删除 +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | phone_number | varchar(20) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
2.删除唯一约束(唯一约束也叫索引约束)
ALTER TABLE 表名 drop index 列号;
mysql> ALTER TABLE stu MODIFY phone_number VARCHAR(20);# 使用非空约束的修改方式,显示成功了 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> INSERT INTO stu(id,phone_number) VALUES(5,1111);# 但是写入数据,仍然出现报错 ERROR 1062 (23000): Duplicate entry '1111' for key 'phone_number' mysql> SELECT * FROM STU;# 未写入数据 +------+--------------+ | id | phone_number | +------+--------------+ | 1 | 1111 | | 2 | 1112 | | 3 | NULL | | 4 | NULL | +------+--------------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE stu drop index phone_number;# 删除索引约束,显示成功 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> INSERT INTO stu(id,phone_number) VALUES(5,1111);# 重复数据可以写入,唯一约束被删除 Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM STU; +------+--------------+ | id | phone_number | +------+--------------+ | 1 | 1111 | | 2 | 1112 | | 3 | NULL | | 4 | NULL | | 5 | 1111 | +------+--------------+ 5 rows in set (0.00 sec)
3.创建表完后添加唯一约束
mysql> ALTER TABLE stu MODIFY phone_number VARCHAR(20) unique;# 添加规则,发现报错,与非空约束一样,原表中有重复数据111 ERROR 1062 (23000): Duplicate entry '1111' for key 'phone_number' mysql> SELECT * FROM STU; +------+--------------+ | id | phone_number | +------+--------------+ | 1 | 1111 | | 2 | 1112 | | 3 | NULL | | 4 | NULL | | 5 | 1111 | +------+--------------+ 5 rows in set (0.00 sec) mysql> DELETE FROM stu WHERE id=5;# 删除重复数据 Query OK, 1 row affected (0.01 sec) mysql> ALTER TABLE stu MODIFY phone_number VARCHAR(20) unique;# 创建表后添加唯一约束 Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> INSERT INTO stu(id,phone_number) VALUES(5,1111);# 测试,重复数据无法写入,唯一约束添加成功 ERROR 1062 (23000): Duplicate entry '1111' for key 'phone_number'
4)主键约束:primary key
1.注意:
1.含义:非空且唯一(上面非空约束唯一约束的集合)
2.一张表只能有一个字段为主键
3.主键就是表中的唯一标识
2.在创建表时,添加主键约束
CREATE TABLE stu(
id int primary key ,-- 给ID添加主键约束
name varchar(20)
);
mysql> use db1 Database changed mysql> mysql> select database(); +------------+ | database() | +------------+ | db1 | +------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE stu(id int primary key, -- 给ID添加主键约束 -> name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT stu(id,name) VALUES(1,'zhangsan');# 添加一条数据 Query OK, 1 row affected (0.00 sec) mysql> select * from stu;# 数据添加成功 +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | +----+----------+ 1 row in set (0.00 sec) mysql> INSERT stu(id,name) VALUES(1,'lisi');# 测试id是否唯一约束,id=1已有,添加不成功 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> INSERT stu(name) VALUES('wangwu');# 测试id是否非空约束,id不得为空,添加不成功 ERROR 1364 (HY000): Field 'id' doesn't have a default value mysql> INSERT stu(id,name) VALUES(5,'maliu');# 唯一的id,并且不为空可以添加成功 Query OK, 1 row affected (0.01 sec) mysql> select * from stu; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 5 | maliu | +----+----------+ 2 rows in set (0.00 sec) mysql> desc stu;# 查看key约束为PRI +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
3.删除主键约束
ALTER TABLE 表名 DROP primary key;
mysql> ALTER TABLE stu MODIFY id int;# MODIFY连唯一约束都删除不了,自然更删除不了主键约束 Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> INSERT stu(id,name) VALUES(1,'lisi');# 仍然无法写入数据 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> desc stu;# 主键约束依然存在 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE stu DROP primary key;# 删除不需要指定列名,是因为主键约束在一张表中只能有一个,所以不用指定列名 Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT stu(id,name) VALUES(1,'lisi');# 可以写入重复数据 Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM stu;# ID=1为重复数据 +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 5 | maliu | | 1 | lisi | +----+----------+ 3 rows in set (0.00 sec) mysql> desc stu;# 主键约束已被删除 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
4.创建完表后添加主键约束
mysql> SELECT * FROM stu;# 原表数据 +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 5 | maliu | | 1 | lisi | +----+----------+ 3 rows in set (0.00 sec) mysql> ALTER TABLE stu MODIFY id int primary key;# 添加主键约束,发现添加不成功,因为id不唯一 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> delete from stu where name='lisi';# 删除其中一个id,使id=1,成为唯一值 Query OK, 1 row affected (0.00 sec) mysql> INSERT stu(id,name) VALUES(1,'lisi');# 添加id=1的值,无法添加 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> desc stu;# 主键约束又添加成功 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
5.自动增长auto_increment
1.概念:如果某一列是数值类型的,使用auto_increment 可以来完成自动增长
比如输入id,让他完成自增长,而不是每次都去查询,然后写入,并且自增长只和上一条数据有关系。
2.在创建表时,添加主键约束,并且完成主键自增长
CREATE TABLE stu(id int primary key auto_increment,name varchar(20) ); -- 给ID添加主键约束以及自增长
mysql> CREATE TABLE stu(id int primary key auto_increment,name varchar(20) ); Query OK, 0 rows affected (0.01 sec) mysql> INSERT stu(id,name) VALUES(1,'zhangsan');# 添加第一行数据 Query OK, 1 row affected (0.01 sec) mysql> INSERT stu(id,name) VALUES(null,'zhangsan');# 第二行不输入id,未报错 Query OK, 1 row affected (0.01 sec) mysql> INSERT stu(id,name) VALUES(null,'lisi');# 第三行也为输入id Query OK, 1 row affected (0.01 sec) mysql> INSERT stu(id,name) VALUES(9,'wangwu');# 直接输入id=9 Query OK, 1 row affected (0.01 sec) mysql> INSERT stu(name) VALUES('liiuliu');# 直接输入数据,未写id,未写null Query OK, 1 row affected (0.01 sec) mysql> select * from stu;# null和不写,都会自动自增1生成ID号,自增是在上一个id基础上自增的。 +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | zhangsan | | 3 | lisi | | 9 | wangwu | | 10 | liiuliu | +----+----------+ 5 rows in set (0.00 sec)
3.删除自增长,再删除主键约束
ALTER TABLE stu MODIFY id int;
ALTER TABLE stu DROP primary key;
4.创建完表后添加自增长
ALTER TABLE stu MODIFY id int auto_increment; # 一般自增长和主键约束一起使用
5)外键约束:foreign key
1.外键约束介绍
CREATE TABLE emp ( -- 创建 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,'销售部','深圳');
上表中发现重复的数据有很多,比如广州、深圳,我们将重复的数据称为冗余数据
当要修改部门或者地点一条数据的时候,要将所有冗余数据都修改,非常不方便
解决方法:做一个表的拆分,一张表存放员工的信息,另外一张表专门存放部门的信息,然后让员工表和部门表相关联
创建部门表(id,dep_name,dep_location)
一方,主表
CREATE TABLE department ( id INT PRIMARY KEY AUTO_INCREMENT, dep_name VARCHAR(20), dep_location VARCHAR(30) );
创建员工表(id,name,dep_id)
多方,从表
CREATE TABLE employee ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), age int, dep_id INT -- 外键对应主表的主键 );
添加两个部门
INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');
添加员工,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; SELECT * FROM department;
我们可以通过两个表知道对应信息,但是当开发项目完毕后,要删除开发部门时,直接就可以删除,张三李四王五还在开发部门,但开发部删除后已经看不到他们属于哪个部门了,所以我们要设定先把这个部门的人删除或转移后,才能删除部门,这就需要用到外键约束了。
2.外键约束:
foreign key:让表与表产生关系,从而保证数据的正确性。
1.在创建表时,可以添加外键
语法:
create table 表名( ...... # 内容 外键列 constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) );
先删除原先的表
DROP TABLE employee; DROP TABLE emp; DROP TABLE department;
CREATE TABLE department ( id INT PRIMARY KEY AUTO_INCREMENT, dep_name VARCHAR(20), dep_location VARCHAR(30) ); # 因为外键约束是employee表添加到department表中,所以要先创建department表 CREATE TABLE employee ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT, dep_id INT, -- 外键对应主表的主键(注意:因为加了一行,所以此行不是最后一行,要加,) CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ); INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳'); 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);
mysql> DELETE FROM department WHERE ID=1;# 再次删除,发现报警,报警内容是这一行被外键引用 ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (db1.employee, CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department (id)) mysql> INSERT INTO employee(NAME,age,dep_id) VALUES('wang',22,5);# 主表也不能添加除外键约束的其他内容,比如:id=5就想添加但是添加不成功 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`employee`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`)) mysql> INSERT INTO employee(NAME,age,dep_id) VALUES('wang',22,2);# 当添加主表有的约束的数据,就可以添加成功了 Query OK, 1 row affected (0.01 sec) mysql> desc employee; +--------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | | dep_id | int(11) | YES | MUL | NULL | | +--------+-------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)
2.删除外键
ALTER TABLE 主表 DROP FOREIGN KEY 列名;
mysql> ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;# 删除外键约束 Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> INSERT INTO employee(NAME,age,dep_id) VALUES('wang',22,5);# 添加内容,id号不被约束 Query OK, 1 row affected (0.01 sec)
3.创建表后,添加外键
ALTER TABLE 主表 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名) REFERENCES 主表(列名);
mysql> ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);# 因为dep_id=5,副表中没有,所以报错 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (db1.#sql-4b4c_4, CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department (id)) mysql> select * from employee;# 查看一下确实dep_id中有5 +----+--------+------+--------+ | id | name | age | dep_id | +----+--------+------+--------+ | 1 | 寮犱笁 | 20 | 1 | | 2 | 鏉庡洓 | 21 | 1 | | 3 | 鐜嬩簲 | 20 | 1 | | 4 | 鑰佺帇 | 20 | 2 | | 5 | 澶х帇 | 22 | 2 | | 6 | 灏忕帇 | 18 | 2 | | 8 | wang | 22 | 2 | | 9 | wang | 22 | 5 | +----+--------+------+--------+ 8 rows in set (0.00 sec) mysql> DELETE FROM employee WHERE ID=9;# 删除dep_id=5的数据 Query OK, 1 row affected (0.01 sec) mysql> ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> INSERT INTO employee(NAME,age,dep_id) VALUES('wang',22,5);# 再次添加出现报错,证明外键约束添加成功 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1`.`employee`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))
3.级联操作
级联操作就是一些联动的操作,可以在已添加外键约束的情况下,添加级联操作,上面中当设置外键约束后,id=5就不能够添加到附表中了,但是又有一些需要更改id=5的操作,所以可以设置级联,可以在添加外键时设置级联,也可以创建表后更新级联
1.以下是未使用级联操作时,更改数字的操作
mysql> SELECT * FROM employee; +----+--------+------+--------+ | id | name | age | dep_id | +----+--------+------+--------+ | 1 | 寮犱笁 | 20 | 1 | | 2 | 鏉庡洓 | 21 | 1 | | 3 | 鐜嬩簲 | 20 | 1 | | 4 | 鑰佺帇 | 20 | 2 | | 6 | 灏忕帇 | 18 | 2 | +----+--------+------+--------+ 5 rows in set (0.00 sec) mysql> UPDATE employee SET dep_id = NULL WHERE dep_id = 1; Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * FROM employee;# 外键约束后,可以为null,但不可以为外键约束值外的其他值 +----+--------+------+--------+ | id | name | age | dep_id | +----+--------+------+--------+ | 1 | 寮犱笁 | 20 | NULL | | 2 | 鏉庡洓 | 21 | NULL | | 3 | 鐜嬩簲 | 20 | NULL | | 4 | 鑰佺帇 | 20 | 2 | | 6 | 灏忕帇 | 18 | 2 | +----+--------+------+--------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM department;# 此时外键约束1和2还在约束主表的1,2,但不约束null +----+-----------+--------------+ | id | dep_name | dep_location | +----+-----------+--------------+ | 1 | 研发部 |广州 | | 2 | 销售部 | 深圳 | +----+-----------+--------------+ 2 rows in set (0.00 sec) mysql> UPDATE department SET id = 5 WHERE id = 1;# 因为主表没有1了,把副表约束id=1改为id=5 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM department;# 当附表约束id=5后,主表就可以更改null为5了 +----+-----------+--------------+ | id | dep_name | dep_location | +----+-----------+--------------+ | 2 | 销售部 | 深圳 | | 5 | 研发部 |广州 | +----+-----------+--------------+ 2 rows in set (0.00 sec) mysql> UPDATE employee SET dep_id = 5 WHERE dep_id IS NULL; Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * FROM employee;# 先更改主表为null,再改副表为值,最后再把主表由null改为附表的值 +----+--------+------+--------+ | id | name | age | dep_id | +----+--------+------+--------+ | 1 | 寮犱笁 | 20 | 5 | | 2 | 鏉庡洓 | 21 | 5 | | 3 | 鐜嬩簲 | 20 | 5 | | 4 | 鑰佺帇 | 20 | 2 | | 6 | 灏忕帇 | 18 | 2 | +----+--------+------+--------+ 5 rows in set (0.00 sec)
2.级联操作
1.在添加外键时设置级联更新
mysql> ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;# 删除外键约束 Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE;# 添加外键约束+级联更新 Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM employee;# 原主表 +----+--------+------+--------+ | id | name | age | dep_id | +----+--------+------+--------+ | 1 | 寮犱笁 | 20 | 5 | | 2 | 鏉庡洓 | 21 | 5 | | 3 | 鐜嬩簲 | 20 | 5 | | 4 | 鑰佺帇 | 20 | 2 | | 6 | 灏忕帇 | 18 | 2 | +----+--------+------+--------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM department;# 原副表 +----+-----------+--------------+ | id | dep_name | dep_location | +----+-----------+--------------+ | 2 | 销售部 | 深圳 | | 5 | 研发部 |广州 | +----+-----------+--------------+ 2 rows in set (0.00 sec) mysql> UPDATE department SET dep_id = 1 WHERE dep_id = 5;# 当对副表添加外键约束和级联更新后 ERROR 1054 (42S22): Unknown column 'dep_id' in 'where clause' mysql> UPDATE department SET id = 1 WHERE id = 5;# 更新副表 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM department;# 副表id=5发生更改id=1 +----+-----------+--------------+ | id | dep_name | dep_location | +----+-----------+--------------+ | 1 | 研发部 |广州 | | 2 | 销售部 | 深圳 | +----+-----------+--------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM employee;# 同时,未对主表id进行任何操作,主表对应的副表中约束内容也变成了副表更改后的内容 +----+--------+------+--------+ | id | name | age | dep_id | +----+--------+------+--------+ | 1 | 寮犱笁 | 20 | 1 | | 2 | 鏉庡洓 | 21 | 1 | | 3 | 鐜嬩簲 | 20 | 1 | | 4 | 鑰佺帇 | 20 | 2 | | 6 | 灏忕帇 | 18 | 2 | +----+--------+------+--------+ 5 rows in set (0.00 sec)
2.级联删除,当副表中id=1的行删除,相对应的主表中对应关联的行也会被删除
mysql> ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;# 删除外键约束 Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;# 添加级联删除 Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM employee; +----+--------+------+--------+ | id | name | age | dep_id | +----+--------+------+--------+ | 1 | 寮犱笁 | 20 | 1 | | 2 | 鏉庡洓 | 21 | 1 | | 3 | 鐜嬩簲 | 20 | 1 | | 4 | 鑰佺帇 | 20 | 2 | | 6 | 灏忕帇 | 18 | 2 | +----+--------+------+--------+ 5 rows in set (0.00 sec) mysql> SELECT * FROM department; +----+-----------+--------------+ | id | dep_name | dep_location | +----+-----------+--------------+ | 1 | 研发部 |广州 | | 2 | 销售部 | 深圳 | +----+-----------+--------------+ 2 rows in set (0.00 sec) mysql> DELETE FROM department WHERE id = 1;# 当删除副表中id=1行时 Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM department;# 副表中id=1的行已删除 +----+-----------+--------------+ | id | dep_name | dep_location | +----+-----------+--------------+ | 2 | 销售部 | 深圳 | +----+-----------+--------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM employee;# 主表中id=1的行也被删除 +----+--------+------+--------+ | id | name | age | dep_id | +----+--------+------+--------+ | 4 | 鑰佺帇 | 20 | 2 | | 6 | 灏忕帇 | 18 | 2 | +----+--------+------+--------+ 2 rows in set (0.00 sec)
4.级联操作总结
1.添加级联操作
语法:ALTER TABLE 主表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (主表关联列名) REFERENCES 副表(关联列名) ON UPDATE CASCADE ON DELETE CASCADE;
2.分类
1.级联更新:ON UPDATE CASCADE
2.级联删除:ON DELETE CASCADE
优势与弊端:1.方便,2.当多表相关联时,删除其中一个表,其他相关联表中对应的数据也会被删除
3.数据库的设计
设计数据库,会间接影响软件开发的难易程度,数据库设计的越合理,软件开发速度越快
1.多表之间的关系
1)分类:
1.一对一的关系:
如:人和身份证的关系
分析:一个人只有一张身份证,一张身份证只能对应一个人
2.一对多(或多对一)的关系
如:部门和员工
分析:一个部门有多个员工,一个员工只能对应一个部门
3.多对多的关系
如:学生和课程
分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择
2)实现关系:
1.一对多(多对一):
如:部门和员工
实现方式:在多的一方建立外键,指向一的一方的主键
![](https://img2022.cnblogs.com/blog/2469482/202211/2469482-20221116145003652-1461065753.png)
2.多对多:多对多关系实现需要借助第三张中间表
实现方式:中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张主表的主键。
3.一对多的关系:
如:人和身份证的关系
实现方式:一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键(若不是唯一外键,就变成了一对多情况)
3)案例
例如途牛旅游,一个旅游路线分类(起点、终点)可以对应多个旅游线路(水、陆、空),所以是一对多的关系,多条旅游线路可以被多个用户收藏,所以是多对多的关系,多对多要借助中间表,至少要对两张主表的主键列为中间表的列,再进行多多对应。
-- 创建旅游线路分类表,tab_category -- cid 路由路线分类主键约束,自动增长 -- cname 旅游路线分类名称非空,唯一,字符串 100 CREATE TABLE tab_category ( cid INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(100) NOT NULL UNIQUE ); -- 添加旅游线路分类数据 INSERT INTO tab_category (cname) VALUES ('周边游'),('出境游'),('国内游'),('港澳游'); SELECT * FROM tab_category; -- 创建旅游线路表 tab_category /* rid 旅游线路主键约束,自动增长 rname 旅游线路名称非空,唯一,字符串100 price 价格 rdate 上架时间,日期类型 cid 外键,所属分类(对上面表cid的外键约束)(下面未指定外键名称,但会默认生成唯一的一个外键名称) */ CREATE TABLE tab_route( rid int primary key auto_increment, rname varchar(100) not null unique, price double, rdate date, cid int, foreign key (cid) references tab_category(cid) ); -- 创建用户表tab_user /* uid 用户主键,自增长 username 用户长度100,唯一,非空 password 密码长度30,非空 name 真实姓名,长度100 birthday 生日 sex 性别,定长字符串1 telephone 手机号,字符串11 email 邮箱,字符串长度200 */ CREATE TABLE tab_user ( uid int primary key auto_increment, username varchar(100) not null unique, password varchar(30) not null, name varchar(100), birthday date, sex char(1) default '男', telephone varchar(11), email varchar(100) ); /* 创建收藏表tab_favorite rid 旅游路线id,外键 date 收藏时间 uid 用户id,外键 rid和uid不能重复,设置复合主键,同一个用户不能收藏同一个线路两次 */ CREAT date datetime, uid int, -- 用户id -- 创建复合主键 PRIMARY KEY(rid,uid), -- 联合主键的操作 foreign key (rid) references tab_route(rid), foreign key (uid) references tab_user(uid), );
2.数据库设计的范式
1)概念
设计数据库时需要遵循的一些规范,要遵循后边的范式,必须先遵循前边的所有范式要求
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小
关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)
2)分类
类似布置作业,第一擦桌子,第二擦桌子+扫地,第三擦桌子+扫地+擦黑板,这就是前面所说的递次规范,要干后面的,前面比如要先干完
第一范式(1NF):每一列都是不可分割的原子数据项
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
概念:
a.函数依赖:A--->B,如果通过A属性(属性组)的值,可以确定唯一B属性的值,则称B依赖于A
例如:学号-->姓名(通过学号就可以确定姓名)(属性组,例如通过学号没办法确定分数,因为学号和分数的对应不唯一,例如张无忌有三个分数,但可以通过学号和课程名称确定分数,所以就称学号和课程名称为属性组)
b.完全函数依赖:A--->B,如果A是一个属性组,则B属性值得确定需要依赖于A属性组中所有的属性值
例如:(学号,课程名称)---> 分数
c.部分函数依赖:如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中某一些值既可
例如:(学号或课程名称)---> 姓名
d.传递函数依赖:A--->B,B--->C,如果通过A属性(属性组)的值,可以确定唯一B属性的值,再通过B属性(属性组)的值,可以确定唯一C属性的值,则称C传递函数依赖与A
例如:由学号查找到系名,可以说系名依赖了学号,再由系名可以查找到系主任,就可以说系主任传递依赖于学号
e.码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码
例如:该表中码为:属性组(学号和课程名称)
主属性:码属性组中的所有属性
非主属性:除过码属性组的属性
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
3)详细介绍
1.与第一范式进行对照,以上是一张很常见的表,我们与第一范式进行对照,发现不符合第一范式,因为系的列又进行了分割,把表进行调整,变成第一范式类型的表
但是上表还存在问题:
1.存在非常严重数据冗余,学号,姓名,系名,系主任等过多重复
2.数据添加存在问题,例如新开设的系和系主任时,还没有学生,添加数据不合法
3.数据删除也存在问题,例如张无忌毕业了,删除数据,会将系名、系主任的数据一起删除
2.第二范式
上表解决了数据冗余问题,但还存在问题:
1.数据添加存在问题,例如新开设的系和系主任时,还没有学生,添加数据不合法
2.数据删除也存在问题,例如张无忌毕业了,删除数据,会将系名、系主任的数据一起删除
3.第二范式
上表解决了数据添加和数据删除的问题
4.数据库的备份和还原
1)命令行的方式
备份语法:mysqldump -u用户名 -p密码 数据库的名称 > 保存的路径/文件的自定义名称
还原语法:
a.登录数据库
b.创建数据库
c.使用数据库
d.执行文件,source 文件路径
a.备份过程
C:\Users\马俊南>mysql -uroot -proot Welcome to the MySQL monitor. Commands end with ; or \g. ........ # 此处登录信息 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases;# 查看当前数据库有多少库 +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.01 sec) mysql> exit Bye C:\Users\马俊南>mysqldump -uroot -proot db1 > d:/a.sql
导出的备份文件都是使用过的SQL语句,如下部分SQL展示:
-- MySQL dump 10.13 Distrib 5.5.40, for Win64 (x86) # 安装的系统 -- -- Host: localhost Database: db1 # 本地安装、数据库名称 -- Server version5.5.40 # 数据库版本号 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /; /!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /; /!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /; /!40101 SET NAMES utf8 */; ....... # 此处省略SQL语句 -- Dump completed on 2022-11-13 23:59:46 # 导出备份文件的时间
b.还原过程
C:\Users\马俊南>mysql -uroot -proot Welcome to the MySQL monitor. Commands end with ; or \g. ........ # 此处登录信息 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db1 | | db2 | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) mysql> drop database db1; # 删除DB1数据库 Query OK, 4 rows affected (0.02 sec) mysql> show databases; # 确认已删除DB1数据库 +--------------------+ | Database | +--------------------+ | information_schema | | db2 | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> create database db1; # 先创建DB1数据库 Query OK, 1 row affected (0.00 sec) mysql> use db1; Database changed mysql> source d:/a.sql; # 使用备份文件,source还原数据DB1 Query OK, 0 rows affected (0.00 sec) ...... # 中间多条SQL执行成功信息 Query OK, 0 rows affected (0.00 sec) mysql> show tables; # 之前数据库中删除的文件已还原 +---------------+ | Tables_in_db1 | +---------------+ | department | | employee | | stu | | student | +---------------+ 4 rows in set (0.00 sec)
2)图形化工具的方式
a.备份过程
选择要备份的数据库,点击右键,选择备份/导出,备份数据库转储到SQL,选择存放的位置和备份文件的名称,点击导出,点击完成
b.还原过程
选择要还原的数据库区域,点击右键,选择执行SQL脚本,选择存放的SQL脚本,点击执行,执行成功后显示绿色进度条,点击完成,刷新界面
本篇单词:
order:顺序
primary:主要的
unique:唯一的
foreign:对外的
department:部门
employee:员工
constraint:约束
references:参考文献