一、外键:Foreign key,简称FK(约束中的一种)
- 外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
- 在创建外键约束时,如果不给外键约束名称,默认名不是列名,而是自动产生一个外键名(例如 student_ibfk_1;),也可以指定外键约束名。
- 当创建外键约束时,系统默认会在所在的列上建立对应的普通****索引。但是索引名是列名,不是外键的约束名。
- 删除外键时,关于外键列上的普通索引需要单独删除。
注意:
- 在从表上建立外键,而且主表要先存在。
- 从表的外键列,在主表中引用的只能是键列(主键,唯一键,外键)。
- 从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样
- 一个表可以建立多个外键约束
- 从表的外键值必须"在主表中能找到"或者为空,从而约束了从表的外键列的值的添加和修改。
- 当主表的记录被从表参照时,主表中被参考记录的删除和更新也会受到限制。
n (1)默认情况下,主表和从表是严格依赖关系RESTRICT。当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据。
n (2)但是有一种是级联“修改、删除”:
n ON DELETE SET NULL(级联置空):当外键设置了SET NULL,当主表的相关记录删除时,从表对应的字段改为NULL。注意从表外键字段得允许为空才行
n ON DELETE CASCADE(级联删除):当外键设置了CASCADE(级联),当主表的相关记录删除时,从表对应的行都删除了。
n 对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式
l 如果要删除表,需要先删除从表,才能删除主表
外键:Foreign key,简称FK
- 外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
- 在创建外键约束时,如果不给外键约束名称,默认名不是列名,而是自动产生一个外键名(例如 student_ibfk_1;),也可以指定外键约束名。
- 当创建外键约束时,系统默认会在所在的列上建立对应的**普通**索引。但是索引名是列名,不是外键的约束名。
- 删除外键时,关于外键列上的普通索引需要单独删除。
注意:
- 在从表上建立外键,而且主表要先存在。
- 从表的外键列,在主表中引用的只能是键列(主键,唯一键,外键)。
- 从表的外键列与主表被参照的列名字可以不相同,但是数据类型必须一样
- 一个表可以建立多个外键约束
- 从表的外键值必须"在主表中能找到"或者为空,从而约束了从表的外键列的值的添加和修改。
- 当主表的记录被从表参照时,主表中被参考记录的删除和更新也会受到限制。
n (1)默认情况下,主表和从表是严格依赖关系RESTRICT。当主表的记录被从表参照时,主表的记录将不允许删除,如果要删除数据,需要先删除从表中依赖该记录的数据,然后才可以删除主表的数据。
n (2)但是有一种是级联“修改、删除”:
n ON DELETE SET NULL(级联置空):当外键设置了SET NULL,当主表的相关记录删除时,从表对应的字段改为NULL。注意从表外键字段得允许为空才行
n ON DELETE CASCADE(级联删除):当外键设置了CASCADE(级联),当主表的相关记录删除时,从表对应的行都删除了。
n 对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式
l 如果要删除表,需要先删除从表,才能删除主表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VVxkc02R-1603422436091)(/Users/yrd/Desktop/八维学习/深度学习2(专高3)]/mysql课件/pic/mysql23.png)
建立外键约束:
创建外键 | CREATE TABLE t_department( did INT PRIMARY KEY, dname VARCHAR(100) NOT NULL UNIQUE, description VARCHAR(200) NOT NULL);CREATE TABLE t_employee( eid INT PRIMARY KEY, ename VARCHAR(100) NOT NULL, dept_id INT, CONSTRAINT fk_emp_dept_did FOREIGN KEY(dept_id) REFERENCES t_department(did) ON UPDATE CASCADE ON DELETE RESTRICT);#其中CONSTRAINT fk_emp_dept_did可以省略#ON UPDATE CASCADE ON DELETE RESTRICT如果省略表示都是RESTRICT |
---|---|
一个表可以有多个外键,而且主表和从表可以是一张表 | CREATE TABLE t_emp( eid INT PRIMARY KEY, ename VARCHAR(100) NOT NULL, manager_id INT, dept_id INT, CONSTRAINT fk_emp_dept_did FOREIGN KEY(dept_id) REFERENCES t_department(did) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_emp_mid_eid FOREIGN KEY(manager_id) REFERENCES t_emp(eid) ON UPDATE CASCADE ON DELETE RESTRICT);#其中CONSTRAINT fk_emp_dept_did可以省略#ON UPDATE CASCADE ON DELETE RESTRICT如果省略表示都是RESTRICT |
建表后创建外键 | alter table表名称 add 【constraint 约束名】 foreign key (从表字段名) references 主表名(主表被参照字段名);ALTER TABLE t_emp ADD CONSTRAINT fk_emp_dept_did FOREIGN KEY(dept_id) REFERENCES t_department(did) ON UPDATE CASCADE ON DELETE RESTRICT;#其中CONSTRAINT fk_emp_dept_did可以省略#ON UPDATE CASCADE ON DELETE RESTRICT如果省略表示都是RESTRICT |
删除外键约束:
ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;ALTER TABLE t_emp DROP FOREIGN KEY fk_emp_dept_did; |
---|
查看约束名SELECT * FROM information_schema.table_constraints WHERE table_name = ‘表名称’; |
删除外键列上的索引,需要单独删除:
ALTER TABLE 表名称 DROP INDEX 外键列索引名; ALTER TABLE t_emp DROP INDEX dept_id; |
---|
查看索引名show index from 表名称; |
二、DML操作
数据操纵语言(DML)DML用于插入、修改、删除数据记录,包括如下SQL语句:
INSERT:添加数据到数据库中
UPDATE:修改数据库中的数据
DELETE:删除数据库中的数据
1. 插入数据
(1)语法
INSERT INTO 表名称 VALUES(值1,值2,…); |
---|
INSERT INTO 表名称 VALUES(值1,值2,…),(值1,值2,…),…; |
INSERT INTO 表名称 (字段1,字段2,…) VALUES(值1,值2,…); |
INSERT INTO 表名称 (字段1,字段2,…) VALUES(值1,值2,…),(值1,值2,…),…; |
(2)说明
- 值列表(值1,值2,…)的顺序、个数与字段列表(字段1,字段2,…) 中字段的顺序、个数一致
- 如果个数少了就报Column count doesn’t match value count
- 如果VALUES前面的()中没有列出字段,那么默认就是为表中的所有字段赋值,那么个数与顺序与表结构中字段定义的一致
- 关于自增长列、默认值列、允许为NULL列的赋值
-
如果字段列表列出了字段名,那么值列表中就要为其赋值,哪怕它是自增长列,有默认值列,可以为NULL值的列。
-
InnoDB表的自动增长列可以手动插入合适的值,但是插入的值如果是NULL或者0,则实际插入的将是自动增长后的值;
-
如果列声明了“默认约束”那么对应的位置可以赋值具体的值,也可以使用“DEFAULT”,表示使用默认值;
-
如果列允许了NULL值,那么可以为对应的字段可以赋值为具体值也可以赋值为NULL
-
-
对于没有列出的字段,像自增列就自动赋值,像默认值列就自动赋默认值,像允许NULL的列就自动赋NULL值,但是非空列又没有提供默认值会自动赋值为对应数据类型的默认值,例如字符串赋值为空字符串,int赋值为0;
-
VALUES也可以写成VALUE,但是VALUES是标准写法
-
可以同时插入多行
-
如果插入从表的数据,要注意查看主表参照字段的值是否存在
-
值的位置可以是常量值、表达式、函数
示例一:
建表
CREATE TABLE t_stu(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(100) NOT NULL,
gender CHAR NOT NULL DEFAULT '男',
card_id CHAR(18) NOT NULL UNIQUE,
birthday DATE,
address VARCHAR(200)
);
插入数据
INSERT INTO t_stu VALUES(1,'张三',DEFAULT,'123456789012345678','1989-09-09',NULL);
INSERT INTO t_stu VALUES(2,'李四','女','123456789012345677','1988-09-09','尚硅谷');
INSERT INTO t_stu VALUES(0,'王五','男','123456789012345676','1987-09-09','尚硅谷');
INSERT INTO t_stu VALUES(NULL,'赵六','男','123456789012345675','1987-09-09','尚硅谷');
INSERT INTO t_stu VALUES
(NULL,'冰冰','女','123456789012345674','1988-09-09','尚硅谷'),
(NULL,'小丽','女','123456789012345673','1988-09-09','尚硅谷');
INSERT INTO t_stu (sname,card_id,birthday)
VALUES('小薇','123456199012045672',STR_TO_DATE(SUBSTRING(card_id,7,8),'%Y%m%d'));
INSERT INTO t_stu (sname,card_id,birthday)VALUES
('小红','123456789012345671','1990-09-09'),
('小紫','123456789012345670','1990-09-09');
示例二:
建表
CREATE TABLE t_department(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(100) NOT NULL,
description VARCHAR(200),
manager_id INT
);
INSERT INTO t_department(dname,description)
VALUES('教学部','技术培训'),
('咨询部','课程咨询服务');
CREATE TABLE t_job (
job_id INT(11) PRIMARY KEY AUTO_INCREMENT,
job_name VARCHAR(100) DEFAULT NULL,
description VARCHAR(200) DEFAULT NULL
);
INSERT INTO t_job VALUES
(NULL,'JavaSE讲师','Java基础'),
(NULL,'Web讲师','Web基础'),
(NULL,'JavaEE框架','框架讲解'),
(NULL,'课程顾问','课程咨询');
CREATE TABLE t_employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(100) NOT NULL,
gender CHAR NOT NULL DEFAULT '男',
card_id CHAR(18) UNIQUE,
tel CHAR(11),
job_id INT,
mid INT,
birthday DATE,
hiredate DATE,
address VARCHAR(100),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES t_department(did),
FOREIGN KEY (job_id) REFERENCES t_job(job_id)
);
INSERT INTO t_employee(eid,ename,gender,card_id,tel,job_id,mid,birthday,hiredate,address,dept_id)
VALUES (1,'孙红雷','男','123456789012345678','12345678901',1,NULL,'1990-01-01','2015-01-01','白庙',1),
(2,'张亮','男','123456789012345677','12345678902',2,NULL,'1990-01-02','2015-01-02','天通苑北',1),
(3,'鹿晗','男','123456789012345676','12345678903',3,NULL,'1990-01-03','2015-01-03','北苑',1),
(4,'邓超','男','123456789012345675','12345678904',2,NULL,'1990-01-04','2015-01-04','和谐家园',1),
(5,'孙俪','女','123456789012345674','12345678905',3,NULL,'1990-01-05','2015-01-05','霍营',1),
(6,'Angelababy','女','123456789012345673','12345678906',4,NULL,'1990-01-06','2015-01-06','回龙观',2);
CREATE TABLE t_salary(
eid INT PRIMARY KEY,
basic_salary DECIMAL(10,2),
performance_salary DECIMAL(10,2),
commission_pct DECIMAL(10,2),
deduct_wages DECIMAL(10,2),
FOREIGN KEY (eid) REFERENCES t_employee(eid)
);
INSERT INTO t_salary(eid,basic_salary,performance_salary,commission_pct,deduct_wages)
VALUES (1,'12000.00','6000.00','0.40','0.00'),
(2,'9000.00','5000.00','0.20',NULL),
(3,'11000.00','8000.00',NULL,NULL),
(4,'13000.00','5000.00',NULL,NULL),
(5,'8000.00','8000.00','0.30',NULL),
(6,'15000.00','6000.00',NULL,NULL);
2. 修改数据
(1)语法
UPDATE 表名称 SET 字段名1 = 值1, 字段名2=值2,… 【WHERE 条件】; |
---|
UPDATE 表1,表2,… SET 表1.字段名1 = 值1, 表1.字段名2=值2,表2.字段1 = 值1, 表2.字段2=值2… 【WHERE 条件】; |
(2)说明
- 如果不写where条件,会修改所有行
- 值可以是常量、表达式、函数
- 可以同时更新多张表
如果两个表没有建立外键,但逻辑上有外键关系
-
如果修改从表外键字段的数据,要注意查看主表参照字段的值是否存在
-
如果修改如果修改主表的被参考的字段的值,要注意查看从表的外键是否有依赖该值,如果有
-
如果外键是on update RESTRICT或on update NO ACTION,那么要先处理从表的数据,才能修改
-
如果外键是on update SET NULL 或 on update CASCADE,那么直接修改,从表的外键字段会自动处理
-
示例:
#修改所有人的基本工资,涨薪5%
UPDATE t_salary SET basic_salary = basic_salary * 1.05;
#修改"孙俪"的手机号码为"13709098765",生日为"1982-09-26"
UPDATE t_employee SET tel = '13709098765',birthday = '1982-09-26' WHERE ename = '孙俪';
#修改"邓超"的入职日期为今天
UPDATE t_employee SET hiredate = CURDATE() WHERE ename ='邓超';
#修改"咨询部"的主管id为6
UPDATE t_department SET manager_id =6 WHERE dname = '咨询部';
#修改"教学部"的主管id为1
UPDATE t_department SET manager_id =1 WHERE dname = '教学部';
#修改"教学部"的主管id为"张亮"的编号
UPDATE t_department,t_employee
SET t_department.manager_id =t_employee.eid
WHERE t_department.dname = '教学部'
AND t_department.did = t_employee.dept_id
AND t_employee.ename = '张量';
修改前:(在这里"张亮"是属于"教学部"的)
修改后:
#修改所有员工的领导编号为该员工所在部门的主管编号
UPDATE t_employee,t_department
SET t_employee.mid = t_department.manager_id
WHERE t_employee.dept_id = t_department.did;
修改前:
修改后:
#修改教学部的主管编号,以及该部门所有员工的领导编号为"邓超"的编号
UPDATE t_department,t_employee
SET t_department.manager_id =t_employee.eid
WHERE t_department.dname = '教学部'
AND t_department.did = t_employee.dept_id
AND t_employee.ename = '邓超';
UPDATE t_employee,t_department
SET t_employee.mid = t_department.manager_id
WHERE t_employee.dept_id = t_department.did
AND t_department.dname = '教学部';
修改前:
修改后:
3. 删除数据
(1)语法
delete from 表名 【where 条件】; |
---|
delete 表1,表2,… from 表1,表2,… 【where 条件】; |
(2)说明
- 如果不加where条件,表示删除整张表的数据,表结构保留。
delete from 表名;
删除整张表的数据还可以使用truncate 表名;
区别:truncate相当于删除表再重建一张同名结构的表,操作后得到一张全新表,而delete是在原有表中删除数据。如果决定清空一张表的数据,truncate速度更快一些。
TRUNCATE语句不能回滚
-
如果删除主表的记录,要注意查看从表的外键是否有依赖该行的值,如果有
-
如果外键是on delete RESTRICT或on delete NO ACTION,那么要先处理从表的数据,才能删除
-
如果外键是on delete SET NULL 或 on delete CASCADE,那么删除时从表的对应记录也会被置空或跟着删除
-
-
可以一次删除多个表的数据
例如:两个表没有建立外键,但逻辑上有外键关系,也可以通过删除多个表的数据来实现级联删除
(3)示例
# 删除学号为9的学生信息
DELETE FROM t_stu WHERE sid = 9;
删除前:
删除后:
# 注意:前提是没有外键或外键是on delete cascade
# 删除所有“教学部”的员工信息和薪资信息和“教学部”部门信息
DELETE t_employee,t_department,t_salary
FROM t_employee,t_department,t_salary
WHERE t_department.dname ='教学部'
AND t_employee.dept_id=t_department.did
AND t_employee.eid = t_salary.eid;
# 报错
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`class`.`t_employee`, CONSTRAINT `t_employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `t_department` (`did`))
4. 查询数据
(1)语法
SELECT 查询列表
FROM 表名或视图列表
【WHERE 条件表达式】
【GROUP BY 字段名 【HAVING 条件表达式】】
【ORDER BY 字段 【ASC|DESC】】
【LIMIT m,n】;
#查询表中的所有行所有列
#使用*表示,查询所有字段,即查询所有行
select * from t_stu;
#查询部分字段,查询部分列
select sname,major from t_stu;
#查询所有列,部分行
select * from t_stu where major = 'JavaEE';
#查询部分行,部分列
select sname,major from t_stu where major = 'JavaEE';
说明:
- 如果SELECT后面是*,那么表示查询所有字段
- SELECT后面的查询列表,可以是表中的字段,常量值,表达式,函数
- 查询的结果是一个虚拟的表
select语句,可以包含5种子句:依次是where、 group by、having、 order by、limit必须照这个顺序。
(2)别名AS
语法:AS 别名
说明:
- 可以给字段取别名、可以给表名取别名
- AS 可以省略
- 如果给字段取别名,如果别名中包含特殊符号,例如“空格”等,建议给别名加上双引号或单引号
- 如果是给表名取别名,那么不能加双引号或单引号,也不能有特殊符号,例如“空格”等
- 建议别名简短,见名知意
UPDATE t_department AS d,t_employee AS e
SET d.manager_id =e.eid
WHERE d.dname = '教学部'
AND d.did = e.dept_id
AND e.ename = '孙红雷';
#查询员工姓名以及手机号码
SELECT ename AS '员工姓名',tel AS '手机号码'
FROM t_employee;
(3)去重DISTINCT
#查询员工表的部门编号
SELECT DISTINCT dept_id FROM t_employee;
#统计员工表中员工有几个部门
SELECT COUNT(DISTINCT dept_id) FROM t_employee;
(4)去着重号
例如:
SELECT `name` FROM t_stu;
可以给字段或表名加着重号
如果字段名或表名与关键字一样更要加着重号了