mysql基础知识一

最新推荐文章于 2023-02-23 13:09:03 发布

鸿儒之观

最新推荐文章于 2023-02-23 13:09:03 发布

阅读量421

点赞数

分类专栏：大数据文章标签： mysql hadoop

本文链接：https://blog.csdn.net/zhijunming/article/details/107580993

版权

大数据专栏收录该内容

18 篇文章 1 订阅

订阅专栏

一、DDL

SQL的分类

DDL：数据定义语言，定义库，表结构等，包括create,drop,alter，show等

DML：数据操作（Manipulation）语言，增删改查数据，包括insert,delete,update,select等

DRL/DQL：专指查询

DCL：数据控制语言，权限，事务等管理。

1、查看所有数据库

show databases;

2、指定使用某个数据库

use 数据库名;

3、创建数据库

create database 数据库名; #使用my.ini中配置的服务器的编码

create database 数据库名 charset 'utf8'; #在mysql中字符集名称不要使用utf-8

create database 数据库名 charset 'gbk';

4、删除数据库

drop database 数据库名;

5、查看某个库下的所有表格

show tables ;  #前提是前面有use 数据库名;的语句
show tables from 数据库名;

6、创建表格

create table 【数据名.】表名(
	字段名1 数据类型,
	字段名2 数据类型,
	....
);

7、删除表格

drop table 【数据库名.】表名称;

8、查看某个表结构

describe 【数据库名.】表名称;
desc 【数据库名.】表名称;

9、增加一个字段

对于表来说，相当于增加一列数据。

alter table 【数据库名.】表名称  add 【column】 字段名 数据类型; #默认添加到最后

alter table student add age int;

alter table 【数据库名.】表名称  add 【column】 字段名 数据类型 after 另一个字段名; #指定位置

alter table student add tel char(11) after gender; #可以，指定在gender的后面

alter table student add address varchar(50) before age; #错误，不支持before

alter table 【数据库名.】表名称  add 【column】 字段名 数据类型 first; #指定首位

alter table student add address varchar(50) first; #可以，表示第一列

10、删除一个字段

alter table 【数据库名.】表名称  drop 【column】 字段名;

alter table student drop tel ;

11、修改一个字段（数据类型和位置）

alter table 【数据库名.】表名称  modify 【column】 字段名 新数据类型;#原来的位置

alter table student modify gender char(2);

alter table 【数据库名.】表名称  modify 【column】 字段名 新数据类型 after 另一个字段;

alter table student modify address varchar(50) after age;

alter table 【数据库名.】表名称  modify 【column】 字段名 新数据类型 first;

12、修改字段名

alter table 【数据库名.】表名称  change 【column】 旧字段名 新字段名 数据类型;

alter table student change gender sex char(2);

alter table student change sex gender char(1);

13、修改表名称

alter table 【数据库名.】旧表名称 rename to 新的表名称;

alter table student rename to xuesheng;

rename table 【数据库名.】旧表名称 to 新表名;

rename table xuesheng to student;

二、DML

1、添加数据

insert into 【数据库名.】表名称 values(值1, 值2 ...); #要求值的数量和表定义的列/字段的数量必须一致，而且顺序也应该一致

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-K4f4bAhw-1595666717885)(imgs/image-20200629100301257.png)]

insert into student values(2, '李四', '女', '18256935752', 24 , '北京');

insert into student values(3, '王五', '男'); #列/字段的数量和值的数量不匹配

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k2nhPoKa-1595666717888)(imgs/image-20200629100451402.png)]

insert into 【数据库名.】表名称(字段名1,字段名2, ...) values(值1, 值2 ...); #后面值的数量、顺序与前面列出来的字段的数量和顺序一致

insert into student(id,name,gender) values(3, '王五', '男');

insert into 【数据库名.】表名称 values(值1, 值2 ...),(值1, 值2 ...),....; #要求值的数量和表定义的列/字段的数量必须一致，而且顺序也应该一致

insert into student values
(4, '赵六', '女', '18256935752', 24 , '深圳'),
(5, '钱七', '男', '18256935752', 25 , '上海');

insert into 【数据库名.】表名称(字段名1,字段名2, ...) values(值1, 值2 ...),(值1, 值2 ...),(值1, 值2 ...)....; #后面值的数量、顺序与前面列出来的字段的数量和顺序一致

insert into student(id,name,gender) values
(6, '冰冰', '女'),
(7, '兰兰', '女');

2、删除

和表结构相关的删除使用drop；和数据相关的删除使用delete。

delete from 【数据库名.】表名称; #删除整张表的数据

delete from student;

delete from 【数据库名.】表名称 where 条件;

where：指的是哪些行。满足条件的行会被删除掉。

delete from student where id = 3;

delete from student where gender = '女';

delete整个表的数据和drop表有什么不同？

delete整个表的数据，数据清空了，但是表结构还在；

drop 表，数据和结构都删了。

3、修改

update 表名称 set 字段名1 = 值1, 字段名2 = 值2, ... ; #没有where条件，所有行的这些列都会改

update student set tel = '10086'; #所有行的tel都修改了

update 表名称 set 字段名1 = 值1, 字段名2 = 值2, ... where 条件;

where：指的是哪些行。满足条件的行的对应字段的值会被修改;

update student set tel = '18256935752' where name ='钱七';

update student set tel = '18256935733' where id = 3;

update student set age = age + 1 ;

4、查询

select * from 表名称; #查看整个表的所有数据

select：选择，即表示选择哪些数据查看

select * from 表名称 where 条件; #查询部分行

where：指的是哪些行。满足条件的行会被筛选出来。

select * from student where gender = '男';

select 字段列表 from 表名称; #查询部分列

select ename,tel from t_employee;

select 字段列表 from 表名称 where 条件; #查询部分行的部分列

select ename,tel from t_employee where gender = '男';

三、问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZKpd0aiF-1595666717890)(imgs/image-20200629110615561.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tCaRvmYK-1595666717892)(imgs/image-20200629110722241.png)]

解决方法：

1、先确定my.ini的编码配置正确

2、再确定你使用(use)的这个数据库的编码是否支持中文

show create database 数据库名;

3、设置当前客户端的编码

默认中文版windows操作系统命令行默认编码是gbk;

set names gbk;

四、约束

约束分为：

（1）键约束：主键约束、唯一键约束、外键约束

（2）非空约束

（3）默认值约束

（4）检查约束：mysql不支持

（5）自增约束

1、主键约束

每一个表必须有主键约束，这是数据库设计的范式中要求的。

（1）关键字：primary key

（2）特点和要求：

每一个表有且只有一个主键约束
设置主键约束的字段是非空
主键约束的值必须是唯一的

（3）如何给一个表设置主键

建表的同时设置主键：

create table 【数据库名.】表名称(
	字段名  数据类型  primary key,
	字段名2 数据类型,
	....
);

create table 【数据库名.】表名称(
	字段名  数据类型 ,
	字段名2 数据类型,
	....,
	primary key(字段名)
);

例如：

create table teacher(
	id int primary key,
	name varchar(20)
);

create table teacher(
	id int ,
	name varchar(20),
	primary key(id)
);

建表后设置主键：

alter table 【数据库名.】表名称 add pimary key(字段名);

例如：

alter table student add primary key(id);

主键必须唯一，主键列的值不能重复。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Kk7jOjRf-1595666717894)(imgs/image-20200629114925620.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EfFYWxPf-1595666717895)(imgs/image-20200629114910164.png)]

insert into student values(5,'钱七','男','18256935752',26,'上海');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4YmIwFZR-1595666717896)(imgs/image-20200629115120589.png)]

（4）删除主键约束

alter table 【数据库名.】表名称 drop primary key;

alter table teacher drop primary key;

注意：删除了主键约束，但是该列仍然非空

因为一个表中只有一个主键约束，所以不用指定列名等。

（5）复合主键

例如：

我们要设计这样几张表：

a：学生表

b：课程表

c：成绩表

选课系统

一个学生选多门课，一门课有多个学生选择。

学生表：

学号	姓名	电话
1	张三	10086
2	李四	10010

课程表：

课程编号	课程名称	课程简介
1001	Java	是基础课程
1002	MySQL	是基础课程

成绩表：

学号	课程编号	成绩
1	1001	89
1	1002	97
2	1001	85
2	1002	75

像学生表中，如果没有额外的序号列作为主键，那么只能选择复合主键。

create table 【数据库名.】表名称(
	字段名 数据类型,
	字段名 数据类型,
	字段名 数据类型,
	primary key(字段1,字段2)
);

create table chengji(
	sid int primary key,
	cid int primary key,
	score int
);#错误

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TIKxQtkU-1595666717897)(imgs/image-20200629120252268.png)]

create table chengji(
	sid int ,
	cid int ,
	score int,
	primary key(sid,cid)
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qX4bgPwi-1595666717898)(imgs/image-20200629120407319.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O436GXqV-1595666717899)(imgs/image-20200629120516778.png)]

实际开发中，可以把复合主键转换为普通的主键，通过增加一个序号列实现。

序号	学号	课程编号	成绩
1	1	1001	89
2	1	1002	97
3	2	1001	85
4	2	1002	75

2、唯一键约束

（1）关键字：unique key

（2）特点：

设置唯一键约束的列的值需要唯一
一个表中可以有多个唯一键存在
唯一键约束可以是null值

（3）如何给表设置唯一键约束

建表的时候：

create table 【数据库名.】表名称(
	字段名  数据类型  primary key,
	字段名  数据类型  unique key,
	字段名  数据类型,
	....
);

create table 【数据库名.】表名称(
	字段名  数据类型 ,
	字段名  数据类型 ,
	字段名  数据类型,
	....,
	primary key(字段名),
	unique key（字段名)
);

例如：

create table student(
	id int primary key,
	name varchar(20) unique key,
	tel char(11) unique key,
	age int
);

insert into student values(1,'张三','10086',23);

insert into student values(2,'张三','10010',25);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-I2AhcAOW-1595666717899)(imgs/image-20200629140639849.png)]

insert into student values(3,null,null,26);
insert into student values(4,null,null,27);

说明：唯一键约束可以为null，都是null值不叫重复，只对非null值的情况进行校验是否重复。

建表后设置唯一键：

alter table 【数据库名.】表名称  add unique key(字段名);

例如：

create table student(
	id int primary key,
	name varchar(20) ,
	tel char(11),
	age int
);

alter table student add unique key(name);
alter table student add unique key(tel);

说明：如果这两个字段是分别唯一，那么需要分开来写。

（4）组合唯一

alter table 【数据库名.】表名称  add unique key(字段1,字段2);

create table 【数据库名.】表名称(
	字段名  数据类型 ,
	字段名  数据类型 ,
	字段名  数据类型,
	....,
	primary key(字段名),
	unique key（字段1,字段2)
);

（5）如何删除唯一键约束

alter table 【数据库名.】表名称  drop index 索引名;

alter table student drop unique key(name); #错误

像主键约束，唯一键约束，外键约束，这些比较关键的约束列，mysql会给它们自动增加一个索引。这个索引的作用是为了提高查询效率的，底层可能是BTREE，哈希结构等。

如何查看某个表的索引名称
show index from 表名称;

alter table student drop index name;

3、非空约束

（1）关键字：not null

（2）特点：

非空，不能为null
一个表可以多个字段非空

（3）如何设置非空约束

建表时：

create table 【数据库名.】表名称(
	字段名  数据类型  primary key,
	字段名  数据类型  【unique key】 【not null】,
	字段名  数据类型 【not null】,
	....
);

create table student(
	id int primary key,
	name varchar(20) not null,
	tel char(11) unique key not null,
	age int
);

insert into student values(1,'张三','10086',23);
insert into student values(2,null,'10010',23);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-48weFg4Z-1595666717900)(imgs/image-20200629143034001.png)]

建表后：

alter table 【数据库名.】表名称 modify 字段名 数据类型 not null 【default 默认值】;

create table student(
	id int primary key,
	name varchar(20) ,
	tel char(11) unique key,
	age int
);

alter table student modify name varchar(20) not null;
alter table student modify tel char(11) not null;

问？如果表中的某个字段已经有null值，然后给这个字段加非空约束，会怎样？

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mcRe6ZEV-1595666717901)(imgs/image-20200629143451659.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BBDrt4Ys-1595666717901)(imgs/image-20200629143437348.png)]

问？alter设置非空约束时，数据类型能不能省略？不可以

alter table student modify name  not null;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DeGzNtYq-1595666717902)(imgs/image-20200629143559180.png)]

问？null与 'null’有没有区别？有区别

insert into student values(1,'null','10086',23);

'null’是字符串，这个使用 = 'null’比较；

null是空值，这个使用is null比较；

（4）如何去掉非空约束？

alter table 【数据库名.】表名称 modify 字段名 数据类型 ;

alter table student modify name varchar(20);
alter table student modify tel char(11);

4、默认值约束

（1）关键字：default

（2）特点：

该字段的值，如果不想指定特定的值，可以使用默认值；
一个表可以有多个字段有默认值约束

（3）如何指定默认值约束

建表时：

create table 【数据库名.】表名称(
	字段名  数据类型  primary key,
	字段名  数据类型  【unique key】 【not null】,
	字段名  数据类型 【not null】【default 默认值】,
	....
);

create table student(
	id int primary key,
	name varchar(20) not null,
	tel char(11) unique key,
	age int default 18,
	gender char default '男'
);

insert into student values(1,'张三','10086');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RmNJMje8-1595666717903)(imgs/image-20200629144813301.png)]

注意：定义表格有5个字段，insert语句中又没有指定字段列表，那么values后面的数量、顺序必须与表结构中的字段数量和顺序一致，和是否有默认值无关。

insert into student values(1,'张三','10086',null,null); #后两个字段是null值
insert into student values(2,'李四','10010',default,default); #后两个字段是默认值 
insert into student(id,name,tel) values(3,'王五','10080'); #后两个字段是默认值

如果字段非空又有默认值约束：

create table student(
	id int primary key,
	name varchar(20) not null,
	tel char(11) unique key,
	age int default 18 not null,
	gender char default '男' not null
);

insert into student values(1,'张三','10086',null,null);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8OjhYVUh-1595666717904)(imgs/image-20200629145235803.png)]

建表后：

alter table 【数据库名.】表名称 modify 字段名 数据类型 default 默认值 【not null】;

create table student(
	id int primary key,
	name varchar(20) not null,
	tel char(11) unique key,
	age int  not null,
	gender char
);

alter table student modify age int default 18; #如果这么写，原来的age字段的非空约束会被去掉
alter table student modify gender char default '男';

alter table student modify age int default 18 not null;#age既有非空约束又有默认值约束

说明：

如果某个字段既要非空又要有默认值，那么必须一起写，不能只写一个。如果只写一个，就表示只有一个约束。

（4）如何去掉默认值约束

alter table 【数据库名.】表名称 modify 字段名 数据类型 【not null】;

修改某字段的声明时，不写默认值约束，就表示去掉默认值约束。

5、自增约束

（1）关键字：auto_increment

（2）特点和要求：

一个表只能有一个自增约束
自增约束的字段必须是整型
自增约束的字段必须是键列（主键、唯一键、外键），一般都是主键自增

（3）如何指定自增约束

建表时：

create table 【数据库名.】表名称(
	字段名  数据类型  primary key auto_increment,
	字段名  数据类型  【unique key】 【not null】,
	字段名  数据类型 【not null】【default 默认值】,
	....
);

create table student(
	id int primary key auto_increment,
	name varchar(20) not null,
	tel char(11) unique key,
	age int  not null,
	gender char
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xxFCpWb8-1595666717905)(imgs/image-20200629151611770.png)]

insert into student values('张三','10086',23,'男'); #在insert语句中，values前面没有指定字段列表时，values（）中的值的数量、顺序无论如何都要和表结构的字段的顺序和数量一致。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Xmo8wb4A-1595666717906)(imgs/image-20200629151834160.png)]

insert into student values(null,'张三','10086',23,'男'); #主键列，写null，有自增约束，会自动加1
insert into student values(null,'李四','10016',23,'男');

insert into student values(5,'王五','10015',25,'男');#如果指定了具体的值，按照具体的值处理
insert into student values(5,'王五2','100152',25,'男');#主键不能重复插入5

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aEibgmqW-1595666717906)(imgs/image-20200629152221090.png)]

insert into student values(null,'找六','100167',25,'男');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D7ItnHAi-1595666717907)(imgs/image-20200629152310295.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tnlDcYnc-1595666717908)(imgs/image-20200629152338790.png)]

 delete from student where id = 6;
 insert into student values(null,'找六','100167',25,'男');

每一个表有一个自增变量值，它记录当前自增值到哪里了，删除数据和修改数据会修改自增变量的值，只有在添加时，才会修改自增变量的值，默认变量值一直自增1，如果你指定的自增变量的值比它现在的大，按照你指定的来。

建表后指定：

alter table 【数据库名.】表名称 modify 字段名 数据类型 auto_increment 【not null】;

create table student(
	id int primary key ,
	name varchar(20) not null,
	tel char(11) unique key,
	age int  not null,
	gender char
);

alter table student modify id int auto_increment;

（4）如何删除自增约束

alter table 【数据库名.】表名称 modify 字段名 数据类型  【not null】【default 默认值】;

说明：如果要去掉非空、默认值、自增约束时，在modify语句中不写即可

问？自增约束是否可以在唯一键约束上？

create table student(
	id int primary key ,
	name varchar(20) not null,
	number int unique key auto_increment not null
);

insert into student values(1,'张三',null);
insert into student values(2,'李四',null);

alter table student modify number int ;

实际开发中，一般都是主键自增，但是如果需要，唯一键列，外键列也可以设置自增，

只要是整型，并且一个表只有一个即可。

6、外键约束

（1）关键字：foreign key

（2）特点：

一个表可以有多个外键约束
外键约束是两个表的两个字段之间的约束或一个表的两个字段之间的约束
外键约束是约束增加（insert）、修改（update）、删除（delete）、删除（drop），是否建外键和查看无关。

（3）什么情况下可能需要加外键

外键约束不是必须的，是可选的，因为一旦加上，影响的是两个表、或一个表的两个字段。

如果两个表的两个字段之间有引用关系，例如：

员工表的员工所在部门编号与部门表的部门编号是有引用关系的。
员工表的员工所属领导编号与领导的员工编号有引用关系。
订单明细表的商品编号和商品表的商品编号有引用关系，订单明细表中的订单编号与订单表中的订单编号有引用关系。
订单表中的购买用户编号与用户信息表的用户编号有引用关系；

（4）如何加外键约束呢？

注意：

首先要对表分角色，分为主表（父表）和从表（子表）
- 例如：员工表和部门表，部门表是主表，员工表是从表
- 例如：学生表、课程表、成绩表，学生表是主表，课程表也是主表，成绩表是从表
外键是设定在从表上
主表必须先存在，然后从表才能建立与主表的外键关系
从表引用主表的字段，这个主表的字段必须是一个键列字段，不能是非键列字段
- 实际中，按照范式，一般都是引用主键

建表时：

create table 【数据库名.】从表名称(
	字段名  数据类型  primary key auto_increment,
	字段名  数据类型  【unique key】 【not null】,
	字段名  数据类型 【not null】【default 默认值】,
	....,
	foreign key(从表字段) references 主表名(被引用字段名)
);

create table employee(
	id int primary key,
	name varchar(20),
	deptmentid int,
	foreign key(deptmentid) references department(id)
);#错误，因为department表不存在

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VpOhwybS-1595666717908)(imgs/image-20200629155243684.png)]

create table department(
	id int primary key,
	title varchar(50)
);
create table employee(
	id int primary key,
	name varchar(20),
	deptmentid int,
	foreign key(deptmentid) references department(id)
);

建表后：

alter table 从表名 add foreign key(从表字段) references 主表(被引用字段);

create table department(
	id int primary key,
	title varchar(50)
);
create table employee(
	id int primary key,
	name varchar(20),
	deptmentid int
);
alter table employee add foreign key(deptmentid) references department(id);

同一个表的两个字段建立外键：

create table employee(
	id int primary key,
	name varchar(20),
	mid int, #员工的领导编号，其实是另一个员工的编号
    foreign key(mid) references employee(id)
);

（5）如何删除外键

删除从表的外键约束：

alter table 从表名 drop foreign key 外键约束名; #删除外键约束
alter table 从表名 drop index 索引名; #在建立外键时自动建立的索引，如果你想保留索引，也可以不删除

alter table employee drop foreign key employee_ibfk_1;

如何查看某个表的约束名？

select CONSTRAINT_NAME from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = '表名称';

例如：

select CONSTRAINT_NAME from information_schema.TABLE_CONSTRAINTS where TABLE_NAME = 'employee';

（6）一个表有两个外键约束

create table department(
	id int primary key,
	title varchar(50)
);
create table employee(
	id int primary key,
	name varchar(20),
	did int, #员工所在的部门编号
	mid int, #员工所属的领导编号
	foreign key(did) references department(id),
	foreign key(mid) references employee(id)
);

（7）外键约束都约束什么？

约束：insert, update, delete , drop等

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PrxIUjR9-1595666717910)(imgs/image-20200629163135615.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ChOGvmM3-1595666717910)(imgs/image-20200629163146437.png)]

insert into employee values(1,'张三',1,null);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-N8tLoDDi-1595666717911)(imgs/image-20200629163327102.png)]

因为现在部门表没有1号部门。

insert into department values(1,'财务部'),(2,'市场部'),(3,'咨询部');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Z8zMZtdL-1595666717911)(imgs/image-20200629163434836.png)]

insert into employee values(1,'张三',1,null);

总结：insert到主表不受影响，但是insert到从表时，外键字段的值受主表的被引用字段的值的约束。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sKtYRTza-1595666717912)(imgs/image-20200629163615888.png)]

update employee set mid = 2 where id = 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PDywJPCD-1595666717913)(imgs/image-20200629163658441.png)]

因为员工编号2不存在。

update department set id = 10 where id = 1; #为了演示，一般很少修改主键

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VVS4KdMc-1595666717914)(imgs/image-20200629163937015.png)]

总结：update修改主表被引用字段的值，如果该字段值被从表引用了，就不能轻易修改；

update修改从表引用字段的值时，也要看是否在主表的被引用字段值的范围内。

delete from employee where id = 2;  #从表
delete from department where id = 1; #主表

总结：删除从表的记录，不受约束；

删除主表的记录，如果该记录被引用了，那么删除受约束；如果要删除，需要把引用关系先解除了。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hcxbMRzt-1595666717915)(imgs/image-20200629164458400.png)]

drop table department; #删除主表
drop table employee; #删除从表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uaTFFgiB-1595666717916)(imgs/image-20200629164629842.png)]

总结：如果先删除主表，失败；

如果先删除从表，成功；

（8）外键约束的修改和删除除了默认的约模式，也可以设置其他的约束模式

restrict：严格，默认的模式
no action：在mysql中等价于restrict
set null：当修改/删除了主表被引用字段的值，从表曾经引用我的字段设置为null，但是前提是从表该字段允许为null。
cascade：级联，当修改/删除了主表被引用字段的值，从表自动跟着修改/删除。

说明：update和delete是可以分开设置模式的，不用要求非得一致。

create table department( #主表
	id int primary key,
	title varchar(50)
);
create table employee( #从表
	id int primary key,
	name varchar(20),
	did int, #员工所在的部门编号
	foreign key(did) references department(id) on update cascade on delete set null
);

insert into department values(1,'财务部'),(2,'市场部'),(3,'咨询部');
insert into employee values(1,'张三',1),(2,'李四',1),(3,'王五',2);

update department set id = 20 where id = 2; #修改主表被引用字段的值，从表的引用字段跟着修改

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hVP570Xz-1595666717916)(imgs/image-20200629165555831.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZMJlcq1f-1595666717917)(imgs/image-20200629165605120.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fbCZ1N4P-1595666717918)(imgs/image-20200629165626785.png)]

delete from department where id = 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FeQGpSWT-1595666717919)(imgs/image-20200629165755293.png)]

create table department( #主表
	id int primary key,
	title varchar(50)
);
create table employee( #从表
	id int primary key,
	name varchar(20),
	did int, #员工所在的部门编号
	foreign key(did) references department(id) on update cascade on delete cascade
);
insert into department values(1,'财务部'),(2,'市场部'),(3,'咨询部');
insert into employee values(1,'张三',1),(2,'李四',1),(3,'王五',2);

delete from department where id = 1; #发现员工表中1号部门的记录跟着删除了

提醒大家：删除需谨慎。

着修改/删除。

说明：update和delete是可以分开设置模式的，不用要求非得一致。

create table department( #主表
	id int primary key,
	title varchar(50)
);
create table employee( #从表
	id int primary key,
	name varchar(20),
	did int, #员工所在的部门编号
	foreign key(did) references department(id) on update cascade on delete set null
);

insert into department values(1,'财务部'),(2,'市场部'),(3,'咨询部');
insert into employee values(1,'张三',1),(2,'李四',1),(3,'王五',2);

update department set id = 20 where id = 2; #修改主表被引用字段的值，从表的引用字段跟着修改

[外链图片转存中…(img-hVP570Xz-1595666717916)]

[外链图片转存中…(img-ZMJlcq1f-1595666717917)]

[外链图片转存中…(img-fbCZ1N4P-1595666717918)]

delete from department where id = 1;

[外链图片转存中…(img-FeQGpSWT-1595666717919)]

create table department( #主表
	id int primary key,
	title varchar(50)
);
create table employee( #从表
	id int primary key,
	name varchar(20),
	did int, #员工所在的部门编号
	foreign key(did) references department(id) on update cascade on delete cascade
);
insert into department values(1,'财务部'),(2,'市场部'),(3,'咨询部');
insert into employee values(1,'张三',1),(2,'李四',1),(3,'王五',2);

delete from department where id = 1; #发现员工表中1号部门的记录跟着删除了

提醒大家：删除需谨慎。

鸿儒之观

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
mysql基础知识一

一、DDLSQL的分类DDL：数据定义语言，定义库，表结构等，包括create,drop,alter，show等DML：数据操作（Manipulation）语言，增删改查数据，包括insert,delete,update,select等 DRL/DQL：专指查询DCL：数据控制语言，权限，事务等管理。1、查看所有数据库show databases;2、指定使用某个数据库use 数据库名;3、创建数据库create database 数据库名; #使用my.ini中配置的服务器的编
复制链接

扫一扫