目录
外键
外键(foreign key):如果一张表中有一个字段(非主键)指向另外一张表的主键,那么该字段被称为外键
其中,外键所在的表称为子表,外键所引用的表称为父表
增加外键
一、在创建表时创建一个外键;在所有表字段后,添加foreign key(外键字段)references 外部表(主键字段);
在创建外键时,要求字段本身必须是一个索引(普通索引),如果字段本身不是一个索引,那么系统会先创建一个索引,然后再创建外键
二、在表格创建完成之后新增一个外键;
alter table 表名 add constraint 外键名 foreign key(外键字段) references 表名(主键字段);
constraint关键字可以指定外键名
删除外键
一张表可以有多个外键,外键不可修改,必须先删除再新增
alter table 表名 drop foreign key 外键名;
外键在删除之后,使用desc查看数据表的结构不能显示外键是否删除,应该使用show create table查看
外键的作用
外键的默认作用:
对子表的约束:在字表在进行写操作时,如果在父表中无法匹配对应数据,则操作失败;
对父表的约束:在父表对其主键进行删除和修改时,如果对应的子表对数据进行了引用,则不允许操作
外键的存在条件
- 存储引擎必须是innodb(默认的存储引擎),如果非此存储引擎,虽然外键能够创建成功,但是无法产生相应约束
- 外键字段的数据类型,必须和父表的主键数据类型完全一致
- 外键名字不能相同
- 增加外键的字段(数据已经存在),必须保证数据与父表主键相对应,例如,子表外键字段中包含父表主键中不包含的数据,则新增外键失败
外键约束
外键约束即外键的作用,除了默认作用外,外键约束的功能支持自定义
外键约束分三个模式,主要是针对父表的约束
- 严格模式:district(默认),父表不能对字表引用数据的记录进行操作
- 级联模式:cascade,父表对相关记录操作的同时,字表数据也跟着进行操作
- 置空模式:set null,父表对记录进行操作之后,子表中的数据被系统置空,前提是该字段允许为空
合理的操作模式如下:删除操作时,使用置空约束;更新操作时,使用级联约束,设置语句如下:
foreign key(外键字段) references 父表(主键字段) on delete 模式(set null) on update 模式(set null)
联合查询
将多条select语句查询结果在记录上进行拼接,但是每条select语句获取的字段数必须严格一致
例如:select 语句1 union [union] 选项 select 语句2.....
union选项和select 选项一样:分为all(保留所有)和distinct(去重)---默认
在联合使用中,如果要使用order by语句,必须使用括号将查询语句选中,并且需要使用limit子句限制结果
由上图可以看出,当没有使用limit子句限制查询结果时,结果并没有按照预期的升序排序
子查询
查询是建立在另外一条查询结果之上进行的,即一条查询语句中包含了另一条查询语句
子查询有两种分类方式:按位置分类、按结果分类
按位置分类
- form子查询:子查询在from之后
- where子查询:子查询在where子句之后
- exits子查询:子查询在exits里面
exits的作用是判断某些条件是否成立,其返回值有两个:0和1
按结果分类(理论上来说,任何一个查询语句的结果都是一个二维表)
- 标量子查询:子查询结果为一行一列
- 列子查询:子查询结果为一列多行
- 行子查询:子查询结果为一行多列(多行多列)
以上三种查询位置为where之后
- 表子查询:子查询得到的结果为多行多列
上面子查询出现的位置为from之后
在使用行子查询时,如果一个行中需要多个多个字段约束一个记录(例如:需要查询身高最高且年龄最大的学生),就需要构造行元素,例如:select * from student where (height,age) = (select max(height),max(age) from student);
在使用表子查询时,因为其子查询语句是跟在from之后的,但是from后只能跟表名,因此需要使用as将子查询结果作为表别名跟在from之后
视图
视图:存储的查询语句,当调用的时候,产生结果集,视图充当了虚拟表的角色
创建视图:create view 视图名 as select 语句;
查看视图:视图是虚拟表,因此查看表的所有方式也同样适用于视图
视图和表一样,在系统完成view的创建之后,也会在数据库的存储位置创建一个.frm结构文件
使用视图 :视图的本质就是执行封装的select语句,但是视图不会占用物理空间,它保存了查询语句,只会在自身被调用的时候产生结果
修改视图:视图的本身是不能修改的,但是可以通过修改其select语句实现对视图的修改
alter view 视图名 as 新的select语句;
删除视图:drop view 视图名
视图的作用
- 视图可以提高SQL语句的复用性;
- 数据安全,视图的操作基本是针对查询,如果对视图进行处理(删除)并不会影响基表数据
- 数据保密,视图可以自定义查询语句,可以在不泄露其它信息的基础上提供所需数据
- 视图可以更方便的进行权限控制
视图的数据操作
视图可以进行数据的操作,但是受到了许多的限制
对视图进行插入数据可以影响到基表(前提是必须有相应权限)
数据的新增
- 多表视图不能插入数据
- 单表视图在插入数据时应当包含所有不为空的字段(默认值为空)
数据的删除
- 多表视图不能删除
数据的更新
- 单表数据和多表数据都能进行数据的更新
- 更新限制:with check option,数据在更新时会受到更新限制的约束,如果在对视图数据进行更新时,系统会进行验证,要保证更新数据之后数据依然能够被查询,否则拒绝更新操作
例如:create view 视图名 where age > 30 with check option;
在受到此约束(age > 30)之后,如果将视图中的age更新为小于30的值,则该更新请求被拒绝
视图算法
视图算法:系统对视图以及 外部查询视图的select语句的一种解析方式
视图算法分为三种:
- undefined:未定义(默认)
- temptable:临时表算法,系统先执行视图的select语句,后执行外部的select语句
- merge:合并算法,系统先将视图的select和外部的select合并,之后再执行(效率高),且如果视图算法为undefined,系统经常会选择此类算法
在创建视图的时候指定视图算法
create algorithm = 视图算法 view 视图名 as select 语句;
数据库的备份与还原
数据备份:将已有的数据或记录保留
数据还原:将已经备份的数据恢复到相应的数据表中
数据备份还原的方式:数据表备份、单表数据备份、SQL备份、增量备份
数据表备份 直接进入数据库文件夹将对应文件复制到指定位置,在还原时直接将备份的数据放回相应位置
数据库的数据表备份根据存储引擎的不同略有区别
存储引擎 即为MySQL进行数据存储的方式,分为innodb和myisam
两种存储引擎数据存储方式:
- innodb:只有表结构,所有的数据文件都在ibdata1中存储
- myisam:表,数据和索引全部分开存储
创建一个新表,将数据库引擎指定为myisam,查看数据库文件
因此,数据表备份十分适合存储引擎为myisam的数据库,将数据表产生的三个文件复制到指定数据库下即可,但是innodb引擎不支持这种备份方式
单表数据备份与还原
单表数据备份
每次只能对一张表进行备份,且只能对数据进行备份而不能对表结构进行备份
备份方式:从表中选取一部分数据保存到外部文件(outfile)
select */字段列表 into outfile 文件路径 from 数据源; #前提是外部文件不存在(必须在备份时新建)
高级备份:自定义字段和记录的处理方式
select */字段列表 into outfile 文件路径 fileds 字段处理 lines 行处理 from 数据源;
fileds字段处理方式:
enclosed by:字段以指定符号包裹,默认为空字符串
terminated by:字段以指定符号结束,默认为'\t',tab键
escaped by:特殊符号的处理方式,默认为以'\\'转义
lines行处理方式:
starting by:行以指定内容开始,默认为空字符串
terminated by:行以指定内容结束,默认为"\r\n",换行符
单表数据还原
将保存正外部的数据重新恢复到数据表中称为数据还原,但是如果表结构不存在,则无法还原
load data infile 文件路径 into table 表名[字段列表] fileds 字段处理 lines 行处理 from 数据源; #还原的字段处理和行处理必须和字段备份的方式保持一致
SQL备份与还原
SQL备份:备份的数据为SQL语句,是系统对数据进行处理所转化的SQL语句,还原时只需执行SQL语句即可
MySQL中没有备份相关的SQL指令,如果需要进行SQL备份,需要使用mysqldump.exe
mysqldump -uroot -p 数据库名 表名1,[表名2..] > 指定文件路径.sql(建议使用.sql文件后缀名,因为导出的文件为SQL语句)
如果不指定表名, 则对整个数据库进行备份
右键选择student.sql的打开方式,选择以MySQL Workbench打开,文件内容如下:
SQL还原数据
1.使用mysql.exe客户端进行数据还原
mysql -uroot -p 数据库名(不需要指定表名) < 备份文件目录;
2.使用SQL指令还原
source 备份文件存储路径;
SQL语句备份可以备份表结构
增量备份与还原
增量备份是针对MySQL服务器的日志文件进行的备份操作
增量备份是指定时间段进行的备份(适合大项目备份)