外键约束 自关联 视图
Mysql 外键约束
建表时生成外键
FOREIGN KEY (`sid`) REFERENCES `student` (`id`);
建表后添加外键
ALTER TABLE `course_student` ADD FOREIGN KEY (`sid`) REFERENCES `student` (`id`);
删除外键
ALTER TABLE `course_student` DROP FOREIGN KEY `course_student_ibfk_1`;
外键用于与另一张表的关联。是能确定另一张表记录的字段,用于保持数据的一致性。比如,A表中的一个字段,是B表的主键,那他就可以是A表的外键。
主键、外键和索引的区别
主键 外键 索引
定义 唯一标识一条记录,不能有重复的,不允许为NULL 表的外键是另一表的主键, 外键可以有重复的, 可以是NULL 没有重复值,可以为NULL(会使索引无效)
作用 用来保证数据完整性 用来和其他表建立联系用的 提高查询排序的速度
个数 主键只能有一个 一个表可以有多个外键 一个表可以有多个惟一索引
错误的设计方式—[1215] Cannot add foreign key constraint
出现这种问题的原因一般有两个:
- 两张表里要设主键和外键的字段的数据类型或者数据长度不一样。
2.某个表里已经有记录了。
Mysql 自关联
自关联的基本含义就是自己join自己,用一张表搞定多张表可以达到的效果
需要注意的是,当表自关联的时候,需要使用as 起别名,防止关联的时候引发重名的问题。
自关联
select
*
from
areas as p
inner join areas as c on c.pid=p.aid
where
p.atitle='河南省';
查询所有区
select
*
from
areas as c
inner join areas as a on a.pid=c.aid
where
c.atitle='郑州市';
三表关联
select
*
from
areas as p
left join areas as c on c.pid=p.aid
left join areas as a on a.pid=c.aid
where
p.atitle='河南省'
子查询的定义
子查询允许把一个查询嵌套在另一个查询当中。
1. 标量子查询:
是指子查询返回的是单一值的标量,如一个数字或一个字符串,也是子查询中最简单的返回形式。 可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较,通常子查询的位置在比较式的右侧
示例:
SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)
SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)
SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)
2. MySQL 列子查询:
指子查询返回的结果集是 N 行一列,该结果通常来自对表的某个字段查询返回。
可以使用 IN ,不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。
示例:
select * from student where cls_id in (select id from classes where id in (1,2))
3 mysql 行子查询
指 子查询返回的结果集是 N 列一行,通常用来多列的比较
示例:查询一个班身高最高,岁数最大的学生
select * from student where(age,height) = (select max(age),max(height) from student);
Mysql 视图(view)
什么是视图
通俗的讲,视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候,主要的工作就落在创建这条SQL查询语句上。
视图的特性
视图是对若干张基本表的引用,一张虚表,查询语句执行的结果,不存储具体的数据(基本表数据发生了改变,视图也会跟着改变);
可以跟基本表一样,进行增删改查操作(ps:增删改操作有条件限制);
视图的作用
方便操作,特别是查询操作,减少复杂的SQL语句,增强可读性;
更加安全,数据库授权命令不能限定到特定行和特定列,但是通过合理创建视图,可以把权限限定到行列级别;
使用场合
权限控制的时候,不希望用户访问表中某些含敏感信息的列,比如salary...
关键信息来源于多个复杂关联表,可以创建视图提取我们需要的信息,简化操作;
建立视图
建议名称以v_开头,用来和普通表区分
使用show tables 可以显示视图
create view 视图名称 as select语句;
使用视图
select * from 视图名称
删除视图
drop view 视图名称
修改视图
create or replace view 视图名称 as sql语句