网站建设 迅雷下载,山东德州网站建设哪家最好,搜狗网站,网络营销模式不是孤立存在的DML和SQL约束 SQL-DML1.添加数据2.修改数据3.删除 TRUNCATE和DELETE的区别#xff1a;SQL-约束Primary Key创建主键约束单列主键联合主键**验证主键约束**删除主键约束设置主键自增AUTO_INCREMENTdelete和truncate删除后#xff0c;主键的自增 SQL-唯一约束UNIQUE创建唯一约束… DML和SQL约束 SQL-DML1.添加数据2.修改数据3.删除 TRUNCATE和DELETE的区别SQL-约束Primary Key创建主键约束单列主键联合主键**验证主键约束**删除主键约束设置主键自增AUTO_INCREMENTdelete和truncate删除后主键的自增 SQL-唯一约束UNIQUE创建唯一约束修改唯一约束删除唯一约束 SQL-非空约束NOT NULL创建非空约束删除非空约束 SQL-外键约束FOREIGN KEY1.作用2.关键字foreign key3.主表和从表/父表和子表4.特点1建表时2建表后了解 5.如何查看外键约束名6.如何查看外键字段索引7.如何删除外键约束了解8.SQL演示9.演示问题10.设置外键约束等级 SQL-检查性约束CHECK添加检查性约束删除检查性约束 SQL-默认值约束Default添加默认值约束 SQL-零填充约束zerofill删除零填充约束 SQL-DML
DMLData Manipulation Language语句: 数据操纵语言主要是对数据进行增加、删除、修改操作。常用的语句关键字有 INSERT、UPDATE、DELETE 等。
数据操作的语言主要对MySQL进行增加、删除、修改
1.添加数据
INSERT INTO 表名(列名1,列名2,....) VALUES (值1,值2,....)
INSERT INTO 表名(列名1,列名2,....) VALUES (值1,值2,....),(值1,值2,....)....--添加数据,单行添加部分列
INSERT INTO dept(deptno,dname) VALUES (1,设计部);
INSERT INTO dept(deptno,dname) VALUES (2,研发部);--添加数据,单行添加所有列
INSERT INTO dept(deptno,dname,location) VALUES (3,研发部,西安);
INSERT INTO dept(deptno,dname,location) VALUES (4,研发部,NULL);
INSERT INTO dept VALUES (5,研发部,NULL);-- 添加多行数据
INSERT INTO dept VALUES(6,测试部,上海),(7,人事部,武汉);2.修改数据
UPDATE 表名 SET 列名1值1,列名2值2...UPDATE 表名 SET 列名1值1,列名2值2... WHERE 条件表达式修改表中符合条件的某些列
UPDATE emp SET deptno30 WHERE empno2296;
修改表中的所有列
UPDATE emp SET salsal-1000,deptnodeptno-10;3.删除
将表中的数据均删除 DELETE FROM 表名删除表中符合条件表达式的记录 DELETE FROM 表名 WHERE 条件表达式删除部分行
删除job为ABC的行
DELETE FROM emp WHERE enameABC;
删除所有的行
DELETE FROM emp;TRUNCATE和DELETE的区别
TRUNCATE删除表和表中的数据重新创建的表和原来的表的结构一样
取消自增的默认值,从默认值1开始(自定义的默认值也不存在)
TRUNCATE TABLE emp;DELETE删除的是表中的数据,可以跟条件
DELETE FROM 表名 WHERE 条件表达式
自增的默认值是从断点的位置自增的数据的值上自增SQL-约束Primary Key
主键不可重复主键不能为NULL
一个表中只能有一个主键(包含联合主键)
添加主键的列的值不能为空也不能重复
唯一标识表中的一行数据提高查询效率
创建主键约束
单列主键
方式1
列名 数据类型 PRIMARY KEY
-- 添加主键的约束方式1
-- 列名 数据类型 PRIMARY KEY
CREATE TABLE test1(NO INT PRIMARY KEY,NAME VARCHAR(10),age INT
);方式2
创建表完成后修改表结构
ALTER TABLE 表名 ADD PRIMARY KEY(列名);
-- 添加主键的约束方式2
-- ALTER TABLE 表名 ADD PRIMARY KEY(列名);
CREATE TABLE test2(NO INT,NAME VARCHAR(10),age INT
);
ALTER TABLE test2 ADD PRIMARY KEY(NO);单列主键的约束规则
一张表只能有一个单列主键,不能重复,不能为空
联合主键
创建方式1
-- 创建方式1:
-- [CONSTRAINT pk1] PRIMARY KEY(列名1,列名2,...)
CREATE TABLE emp1(NAME VARCHAR(10),eid INT,sal INT,CONSTRAINT pk1 PRIMARY KEY(NAME,eid)
)DESC emp1;创建方式2
-- 创建方式2:创建好后添加主键约束
CREATE TABLE emp2(NAME VARCHAR(10),eid INT,sal INT
)ALTER TABLE emp2 ADD PRIMARY KEY(NAME,eid);
DESC emp2;验证主键约束
-- 验证主键约束
INSERT INTO emp1(eid,NAME,sal) VALUES(1,张三,2000);
INSERT INTO emp1(eid,NAME,sal) VALUES(2,李四,2000);
-- 不能添加,主键不能重复
INSERT INTO emp1(eid,NAME,sal) VALUES(2,李四,2000);
-- 不能添加,主键不能为null
INSERT INTO emp1(eid,NAME,sal) VALUES(NULL,王五,2000);
INSERT INTO emp1(eid,NAME,sal) VALUES(3,NULL,2000);
-- 注意,这里的NULL为字符串,可以添加
INSERT INTO emp1(eid,NAME,sal) VALUES(3,NULL,2000);删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;
-- 删除主键
ALTER TABLE emp2 DROP PRIMARY KEY;
DESC emp2;设置主键自增AUTO_INCREMENT
注意:无论SQL执行是否成功都会自增
方式1
类名 数据类型 PRIMARY KEY AUTO_INCREMENT
-- 主键自增并设置起始值方式1
CREATE TABLE student3(id INT PRIMARY KEY AUTO_INCREMENT,sno INT,NAME VARCHAR(10)
)AUTO_INCREMENT100;DESC student3;
-- 添加数据查看自增从指定的100开始,每次自增1
INSERT INTO student3(sno,NAME) VALUES(1,爪爪);
INSERT INTO student3(sno,NAME) VALUES(2,猫猫);SELECT * FROM student3;方式2
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段数据类型 AUTO_INCREMENT PRIMARY KEY;
-- 主键自增并设置起始值方式2
CREATE TABLE student4(id INT,sno INT,NAME VARCHAR(10)
);ALTER TABLE student4 MODIFY COLUMN id INT AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE student4 AUTO_INCREMENT 20; DESC student4;
-- 添加数据查看自增从指定的100开始,每次自增1
INSERT INTO student4(sno,NAME) VALUES(1,爪爪);
INSERT INTO student4(sno,NAME) VALUES(2,猫猫);SELECT * FROM student4;delete和truncate删除后主键的自增
delete在删除之后从断点开始自增
DELETE FROM student4 WHERE id21;--表中最后一个
INSERT INTO student4(sno,NAME) VALUES(2,猫猫);-- id22DELETE FROM student4;-- 删除表
INSERT INTO student4(sno,NAME) VALUES(3,李郁);-- id23truncate数据之后自动增长默认从起始值1开始,和表之前创建时或者设置的指定自增无关
TRUNCATE student4;-- 截断表,实际上是删除表和表中的数据然后复制表结构
INSERT INTO student4(sno,NAME) VALUES(1,爪爪);-- id1
INSERT INTO student4(sno,NAME) VALUES(2,猫猫);-- id2SQL-唯一约束UNIQUE
唯一约束,该约束的键所在的列不能重复,但可以为null
元素不能重复但是值可以为NULL
一个表之中可以有多列为唯一约束
创建唯一约束
方式1
字段名 数据类型 UNIQUE
icard CHAR(18) UNIQUECREATE TABLE student5(id INT PRIMARY KEY AUTO_INCREMENT,sno INT,NAME VARCHAR(10),icard CHAR(18) UNIQUE
);
DESC student5;
SELECT * FROM student5;
-- 添加的元素只要不重复,均可添加,也可以为null
INSERT INTO student5(sno,NAME,icard) VALUES(1001,猫猫,610721200509160985);
INSERT INTO student5(sno,NAME,icard) VALUES(1002,猫猫,610721200509160986);
INSERT INTO student5(sno,NAME,icard) VALUES(1003,猫猫,NULL);
INSERT INTO student5(sno,NAME) VALUES(1004,猫猫);修改唯一约束
方法2
修改表中的sno字段,添加唯一约束,要注意不然是空表,要不然里面的数据符合唯一约束
ALTER TABLE 表名 ADD UNIQUE(字段名);
ALTER TABLE student6 ADD UNIQUE(sno);-- 给sno添加唯一约束
CREATE TABLE student6(id INT PRIMARY KEY AUTO_INCREMENT,sno INT,NAME VARCHAR(10),icard CHAR(18) UNIQUE
);
-- 修改表中的sno字段,添加唯一约束,要注意不然是空表,要不然里面的数据符合唯一约束
ALTER TABLE student6 ADD UNIQUE(sno);
DESC student6;SELECT * FROM student6;
-- 检查唯一约束信息
INSERT INTO student6(sno,NAME,icard) VALUES(1001,袁继峰,610721200506090145);
-- 第二条数据不能添加,sno也是唯一约束
INSERT INTO student6(sno,NAME,icard) VALUES(1001,袁继峰,610721200506090146);删除唯一约束
ALTER TABLE 表名 DROP INDEX 字段名;
-- 删除唯一的约束
ALTER TABLE student6 DROP INDEX sno;SQL-非空约束NOT NULL
特点:添加非空约束后,列不能为空
创建非空约束
方式1创建时声明非空约束
字段名 数据类型 NOT NULL
CREATE TABLE student7(id INT PRIMARY KEY,sno INT,NAME VARCHAR(10) NOT NULL
);方式2修改表的结构为非空
注意:修改时不能存在不符合数据的情况
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
ALTER TABLE student7 MODIFY NAME VARCHAR(10) NOT NULL;删除非空约束
-- 删除非空约束,将name字段的约束修改为NULL
ALTER TABLE student7 MODIFY NAME VARCHAR(10) NULL;SQL-外键约束FOREIGN KEY
1.作用
限定某个表的某个字段的引用完整性
比如员工表的员工所在部门的选择必须在部门表能找到对应的部分。
2.关键字foreign key
3.主表和从表/父表和子表
主表父表被引用的表被参考的表
从表子表引用别人的表参考别人的表
例如员工表的员工所在部门这个字段的值要参考部门表
部门表是主表员工表是从表。
例如学生表、课程表、选课表
选课表的学生和课程要分别参考学生表和课程表
学生表和课程表是主表选课表是从表。
4.特点
0创建外键时主表的字段一定是主键约束
1在“从表”中指定外键约束并且一个表可以建立多个外键约束
2创建(create)表时就指定外键约束的话先创建主表再创建从表
3删表时先删从表或先删除外键约束再删除主表
4从表的外键列必须引用/参考主表的键列主键或唯一键
为什么因为被依赖/被参考的值必须是唯一的
5从表的外键列的数据类型要与主表被参考/被引用的列的数据类型一致并且逻辑意义一致。
例如都是表示部门编号都是int类型。
6外键列也会自动建立索引根据外键查询效率很高很多
7外键约束的删除所以不会自动删除如果要删除对应的索引必须手动删除
如何指定外键约束
1建表时
create table 主表名称(字段1 数据类型 primary key,字段2 数据类型
);create table 从表名称(字段1 数据类型 primary key,字段2 数据类型,foreign key 从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样也可以不一样2建表后了解
alter table 从表名称 add foreign key (从表的字段) references 主表被引用字段) 【on update xx】【on delete xx】;5.如何查看外键约束名
desc 从表名称; #可以看到外键约束但看不到外键约束名show create table 从表名称; #可以看到外键约束名SELECT * FROM information_schema.table_constraints WHERE table_name 表名称;
#information_schema数据库名系统库
#table_constraints表名称专门存储各个表的约束
#WHERE条件
#table_name 表名称条件是指定表名称6.如何查看外键字段索引
show index from 表名称; #查看某个表的索引名7.如何删除外键约束了解
删除外键约束不会自动删除外键约束列的索引需要单独删除。
(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name 表名称;#查看某个表的约束名alter table 从表名 drop foreign key 外键约束名;2第二步查看索引名和删除索引
show index from 表名称; #查看某个表的索引名alter table 从表名 drop index 索引名;8.SQL演示
#演示外键约束
/*
1、作用
用来限定子表和父表的引用关系。
子表的外键字段值必须在父类被引用字段值的范围内。2、外键约束一定是有两个表的角色。
其中一个作为子表又称为从表另一个作为父表又称为主表。子表外键字段的值要从父表被引用字段的值中选择。例如员工表中记录员工信息其中有一个字段是“部门编号”表示这个员工所属的部门。部门的信息是记录在部门表中但是员工表的“部门编号”字段的值不能随意填写需要参考/引用“部门表”的部门记录。员工表是子表引用别人受部门表的牵制/范围管辖。部门表示父表被别人引用。例如商品表、用户表、订单表、订单明细表商品表里面记录了商品信息商品名称、价格、库存量...用户表里面记录了用户信息用户名、密码、账户余额...订单表里面记录了订单信息订单编号、订单总价格、订单商品总数量、收获地址...订单明细表记录了订单中每一个明细商品编号、订单编号、价格、数量....中午下了一个订单买了“米粉”、“袜子”、“手机贴膜”、“高压锅”订单编号112122123222222商品表1 “米粉” 52.5 ....2 “袜子” 23.0 ....3 “手机贴膜” 15.0 ....4 “高压锅” 125.0 ....订单明细中订单编号 商品编号 数量 价格 ....112122123222222 1 2盒米粉 52.5 112122123222222 2 1打 23.0 112122123222222 3 1张 15.0 112122123222222 4 1个 125.0订单明细表是子表商品表和订单表是父表。 3、外键约束要求
1外键约束必须在子表中定义
2子表中要定义外键约束的字段的数据类型和逻辑意义必须和父表中被引用字段的数据类型、逻辑意义一样。
例如员工表中部门编号字段 引用 部门表中的部门编号字段订单明细表中的订单编号 引用 订单表中订单编号订单明细表中的商品编号 引用 商品表中商品编号3子表中要定义外键约束的字段的名字和父表中被引用字段名称可以不同。
4父表中被引用的字段必须是键约束字段一般都是主键或唯一键约束。
此时子表和父表的关系可以是一对一或一对多的关系。
父表是一子表是一或多。例如父表部门表一条记录对应子表员工表多条记录。父表订单表一条记录对应子表订单明细表一条或多条记录。5父表必须先存在然后才能正确建立子表的外键约束4、关键字 foreign key 和 references 5、外键约束约束了什么
1建表的顺序先建父表再建子表
2删表的顺序先删子表再删父表
或者先删掉外键约束然后分别删除两个表顺序就随意了
3给子表添加数据、修改外键字段的数据受影响因为要从父表的被引用字段范围内选值。
4删除和修改父表的被引用字段的数据受影响因为要考虑这个值是否被子表引用了。
5是否建立外键和查询无关。比喻孩子的消费行为要受约束在爸爸的挣钱范围内。爸爸的行为也要受约束被依赖不是那么自由。双方都会受影响。
*/#建表时指定外键约束
create table dept(did int primary key auto_increment,dname varchar(50) unique key not null
);create table emp(id int primary key auto_increment,name varchar(20) not null,departmentid int, #子表中外键约束的字段名和父表的被引用字段名不要求一致但是数据类型和逻辑意义要一样#外键约束只能在字段列表下面单独定义不能在字段后面直接定义foreign key (departmentid) references dept(did)
);#查看表结构
desc dept;
desc emp;mysql desc dept;
--------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------------
| did | int | NO | PRI | NULL | auto_increment |
| dname | varchar(50) | NO | UNI | NULL | |
--------------------------------------------------------
2 rows in set (0.01 sec)mysql desc emp;
---------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
---------------------------------------------------------------
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| departmentid | int | YES | MUL | NULL | |
---------------------------------------------------------------
3 rows in set (0.00 sec)#查看表的定义
show create table dept;
show create table emp;mysql show create table dept\G
*************************** 1. row ***************************Table: dept
Create Table: CREATE TABLE dept (did int NOT NULL AUTO_INCREMENT,dname varchar(50) NOT NULL,PRIMARY KEY (did),UNIQUE KEY dname (dname)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec)mysql show create table emp\G
*************************** 1. row ***************************Table: emp
Create Table: CREATE TABLE emp (id int NOT NULL AUTO_INCREMENT,name varchar(20) NOT NULL,departmentid int DEFAULT NULL,PRIMARY KEY (id),KEY departmentid (departmentid),CONSTRAINT emp_ibfk_1 FOREIGN KEY (departmentid) REFERENCES dept (did)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec)#查看系统库的约束表
SELECT * FROM information_schema.table_constraints WHERE table_name 表名称;SELECT * FROM information_schema.table_constraints WHERE table_name emp;mysql SELECT * FROM information_schema.table_constraints WHERE table_name emp;
-------------------------------------------------------------------------------------------------------------
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
-------------------------------------------------------------------------------------------------------------
| def | atguigu | PRIMARY | atguigu | emp | PRIMARY KEY | YES |
| def | atguigu | emp_ibfk_1 | atguigu | emp | FOREIGN KEY | YES |
-------------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)#添加记录
#添加父表信息没有影响
insert into dept values(null,财务),(null,教学),(null,咨询),(null,后勤);mysql select * from dept;
------------
| did | dname |
------------
| 4 | 后勤 |
| 3 | 咨询 |
| 2 | 教学 |
| 1 | 财务 |
------------
4 rows in set (0.00 sec)#添加子表信息有影响受到约束
insert into emp values(null,张三,1);#成功
insert into emp values(null,李四,1);#成功
insert into emp values(null,王五,2);#成功
insert into emp values(null,赵六,6); #失败
#因为departmentid1或2在父表dept中可以找到对应记录
#因为departmentid6在父表dept中找不到对应记录mysql insert into emp values(null,赵六,6);
ERROR 1452 (23000): Cannot add添加 or update修改 a child(子表 row记录/行:a foreign key constraint fails (atguigu.emp, CONSTRAINT emp_ibfk_1 FOREIGN KEY (departmentid) REFERENCES dept (did))mysql select * from emp;
------------------------
| id | name | departmentid |
------------------------
| 1 | 张三 | 1 |
| 2 | 李四 | 1 |
| 3 | 王五 | 2 |
------------------------
3 rows in set (0.00 sec)#修改子表的外键字段的信息有影响受到约束
update emp set departmentid 3 where id 1;#成功
#因为departmentid 3在父表dept中可以找到对应部门update emp set departmentid 6 where id 3; #失败
#因为departmentid 6在父表dept中找不到对应部门mysql update emp set departmentid 6 where id 3;
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (atguigu.emp, CONSTRAINT emp_ibfk_1 FOREIGN KEY (departmentid) REFERENCES dept (did))mysql select * from emp;
------------------------
| id | name | departmentid |
------------------------
| 1 | 张三 | 3 |
| 2 | 李四 | 1 |
| 3 | 王五 | 2 |
------------------------
3 rows in set (0.00 sec)mysql select * from dept;
------------
| did | dname |
------------
| 4 | 后勤 |
| 3 | 咨询 |
| 2 | 教学 |
| 1 | 财务 |
------------
4 rows in set (0.00 sec)#修改父表的被引用字段的值受约束
update dept set did 6 where did 1;#失败
#因为did1的部门被子表引用
update dept set did 6 where did 4;#成功
#因为 did4的部门没有被子表引用mysql update dept set did 6 where did 1;
ERROR 1451 (23000): Cannot delete删除 or update修改 a parent父表 row记录/行:a foreign key constraint fails (atguigu.emp, CONSTRAINT emp_ibfk_1 FOREIGN KEY (departmentid) REFERENCES dept (did))mysql select * from dept;
------------
| did | dname |
------------
| 6 | 后勤 |
| 3 | 咨询 |
| 2 | 教学 |
| 1 | 财务 |
------------
4 rows in set (0.00 sec)#删除父表的记录受约束
delete from dept where did 6; #成功
#因为 did6的部门没有被子表引用mysql select * from dept;
------------
| did | dname |
------------
| 3 | 咨询 |
| 2 | 教学 |
| 1 | 财务 |
------------
3 rows in set (0.00 sec)#删除父表的记录受约束
delete from dept where did 1; #失败
#因为 did1的部门被子表引用mysql delete from dept where did 1;
ERROR 1451 (23000): Cannot delete or update a parent row:a foreign key constraint fails (atguigu.emp, CONSTRAINT emp_ibfk_1 FOREIGN KEY (departmentid) REFERENCES dept (did))#删除子表的数据不受约束
delete from emp where name 王五; #可以mysql select * from emp;
------------------------
| id | name | departmentid |
------------------------
| 1 | 张三 | 3 |
| 2 | 李四 | 1 |
------------------------
2 rows in set (0.00 sec)#删除父表受约束
drop table dept; #失败mysql drop table dept; #失败
ERROR 3730 (HY000): Cannot drop table dept referenced by a foreign key constraint emp_ibfk_1 on table emp.#删除子表不受约束
drop table emp;#能不能在建表后添加外键约束如何可以怎么写
alter table 子表名 add foreign key(子表的外键字段名) references 父表名(被引用字段);create table emp(id int primary key auto_increment,name varchar(20) not null,departmentid int
);#给emp表子表增加外键约束
alter table emp add foreign key(departmentid) references dept(did);#查看emp的约束信息
SELECT * FROM information_schema.table_constraints WHERE table_name emp;
mysql SELECT * FROM information_schema.table_constraints WHERE table_name emp;
-------------------------------------------------------------------------------------------------------------
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
-------------------------------------------------------------------------------------------------------------
| def | atguigu | PRIMARY | atguigu | emp | PRIMARY KEY | YES |
| def | atguigu | emp_ibfk_1 | atguigu | emp | FOREIGN KEY | YES |
-------------------------------------------------------------------------------------------------------------
2 rows in set (0.01 sec)#只有是键约束主键、唯一键、外键都会自动创建索引。
#查看emp表的索引
show index from emp;mysql show index from emp;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| emp | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| emp | 1 | departmentid | 1 | departmentid | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.01 sec)
#主键字段索引名是PRIMARY删除主键时会自动删除对应索引
#唯一键字段索引名是字段名删除唯一键时就是通过删除对应的索引方式来删除唯一键约束
#外键字段索引名是字段名删除外键时#删除外键约束
alter table 表名称 drop foreign key 外键约束名;#删除emp表的departmentid字段的外键约束
alter table emp drop foreign key emp_ibfk_1;mysql show index from emp;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| emp | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
| emp | 1 | departmentid | 1 | departmentid | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)#说明删除外键约束时不会自动删除外键字段的索引因为它们的命名不一样
#如果需要删除对应的索引需要单独删
alter table 表名称 drop index 索引名;#删除emp表的departmentid字段的索引
alter table emp drop index departmentid;mysql show index from emp;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| emp | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
9.演示问题
1失败不是键列
create table dept(did int , #部门编号dname varchar(50) #部门名称
);create table emp(eid int primary key, #员工编号ename varchar(5), #员工姓名deptid int, #员工所在的部门foreign key (deptid) references dept(did)
);ERROR 1215 (HY000): Cannot add foreign key constraint 原因是dept的did不是键列2失败数据类型不一致
create table dept(did int primary key, #部门编号dname varchar(50) #部门名称
);create table emp(eid int primary key, #员工编号ename varchar(5), #员工姓名deptid char, #员工所在的部门foreign key (deptid) references dept(did)
);ERROR 1215 (HY000): Cannot add foreign key constraint 原因是从表的deptid字段和主表的did字段的数据类型不一致并且要它俩的逻辑意义一致3成功两个表字段名一样
create table dept(did int primary key, #部门编号dname varchar(50) #部门名称
);create table emp(eid int primary key, #员工编号ename varchar(5), #员工姓名did int, #员工所在的部门foreign key (did) references dept(did) #emp表的deptid和和dept表的did的数据类型一致意义都是表示部门的编号#是否重名没问题因为两个did在不同的表中
);10.设置外键约束等级 Cascade方式在父表上update/delete记录时同步update/delete掉子表的匹配记录 Set null方式在父表上update/delete记录时将子表上匹配记录的列设为null但是要注意子表的外键列不能为not null No action方式如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 Restrict方式同no action, 都是立即检查外键约束 Set default方式在可视化工具SQLyog中可能显示空白父表有变更时,子表将外键列设置成一个默认的值但Innodb不能识别
如果没有指定等级就相当于Restrict方式。
/*
外键约束可以设置约束等级
1No action方式如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
2Restrict方式同no action, 都是立即检查外键约束
3Set null方式在父表上update/delete记录时将子表上匹配记录的列设为null但是要注意子表的外键列不能为not null
4Cascade方式在父表上update/delete记录时同步update/delete掉子表的匹配记录
5Set default方式在可视化工具SQLyog中可能显示空白父表有变更时,子表将外键列设置成一个默认的值但Innodb不能识别on update No action | Restrict | Set null | Cascade
on delete No action | Restrict | Set null | Cascade
如果没有写on update 或on delete字句表示选择了No action | Restrict。*/
#父表
create table dept(did int primary key auto_increment,dname varchar(50) unique key not null
);insert into dept values(null,财务),(null,教学),(null,咨询),(null,后勤);mysql select * from dept;
------------
| did | dname |
------------
| 4 | 后勤 |
| 3 | 咨询 |
| 2 | 教学 |
| 1 | 财务 |
------------
4 rows in set (0.00 sec)#子表
create table emp(id int primary key auto_increment,name varchar(20) not null,departmentid int,foreign key (departmentid) references dept(did) on update cascade on delete set null#on delete set null要求departmentid字段没有not null约束
);#添加子表时记录和 定义on update cascade on delete set null无关。还是要在主表能找到对应的记录。
insert into emp values(null,张三,1);
insert into emp values(null,李四,2);
insert into emp values(null,王五,1);mysql select * from emp;
------------------------
| id | name | departmentid |
------------------------
| 1 | 张三 | 1 |
| 2 | 李四 | 2 |
| 3 | 王五 | 1 |
------------------------
3 rows in set (0.00 sec)#修改子表 和 定义on update cascade on delete set null无关。还是要在主表能找到对应的记录。
update emp set departmentid 6 where name 王五;
#失败因为departmentid 6在父表dept中找不到对应部门#修改父表被引用的did值 和 定义on update cascade on delete set null有关。
update dept set did 6 where did 1;
#此时did1的记录被子表引用了可以修改并且会同时修改子表的departmentid1的字段值为6级联修改mysql select * from dept;
------------
| did | dname |
------------
| 4 | 后勤 |
| 3 | 咨询 |
| 2 | 教学 |
| 6 | 财务 |
------------
4 rows in set (0.00 sec)mysql select * from emp;
------------------------
| id | name | departmentid |
------------------------
| 1 | 张三 | 6 |
| 2 | 李四 | 2 |
| 3 | 王五 | 6 |
------------------------
3 rows in set (0.00 sec)#删除父表dept被引用的did的记录 和 定义on update cascade on delete set null有关。
delete from dept where did 6; #did6的部门在子表中有引用mysql select * from dept;
------------
| did | dname |
------------
| 4 | 后勤 |
| 3 | 咨询 |
| 2 | 教学 |
------------
3 rows in set (0.00 sec)mysql select * from emp;
------------------------
| id | name | departmentid |
------------------------
| 1 | 张三 | NULL |
| 2 | 李四 | 2 |
| 3 | 王五 | NULL |
------------------------
3 rows in set (0.00 sec)SQL-检查性约束CHECK
检查性约束的特点
检查为非NULL的插入的记录的范围是否符合check后面的条件
添加检查性约束
字段 数据类型 CHECK (条件)
CREATE TABLE student(sid INT PRIMARY KEY,sno INT,sname VARCHAR(10),sex CHAR(2) CHECK (sex男 OR sex女),age INT CHECK (age15 AND age30)
)添加时检查记录是否符合约束条件
-- 添加成功
INSERT INTO student VALUES(1,1001,爪爪,男,16);
INSERT INTO student VALUES(2,1002,猫猫,女,16);
INSERT INTO student VALUES(3,1003,阿鱼,NULL,NULL);
-- 不符合的情况
-- 超出年龄范围
INSERT INTO student VALUES(5,1003,阿鱼,男,31);
-- 超出性别范围
INSERT INTO student VALUES(3,1003,阿鱼,未知,16);
-- 检查性约束,只有为NULL时才不会检查约束
INSERT INTO student VALUES(3,1003,阿鱼,,NULL);删除检查性约束
ALTER TABLE 表名 DROP CHECK 检查性约束名;
-- 显示表的创建规则
SHOW CREATE TABLE student;
-- 删除表的检查性约束2
ALTER TABLE student DROP CHECK student_chk_2;SQL-默认值约束Default
添加默认值约束
CREATE TABLE student1(sid INT PRIMARY KEY,sno INT,sname VARCHAR(10),sex CHAR(2),age INT,nation VARCHAR(10) DEFAULT 中国
);如果给传值,以传的值为准,否则是默认值
-- 如果给传值,以传的值为准,否则是默认值
-- 国籍为NULL
INSERT INTO student1 VALUES(1,1001,猫猫,女,16,NULL);
-- 国籍为默认值
INSERT INTO student1(sid,sno,sname) VALUES(2,1002,爪爪);
-- 国籍为俄罗斯
INSERT INTO student1 VALUES(3,1003,田欣怡,女,21,俄罗斯);SQL-零填充约束zerofill
CREATE TABLE student2(id INT ZEROFILL,NAME VARCHAR(20)
);INSERT INTO student2 VALUES(1111,猫猫);
INSERT INTO student2 VALUES(1,爪爪);删除零填充约束
-- 删除零填充约束
ALTER TABLE student2 MODIFY id INT;
INSERT INTO student2 VALUES(2,困困);
