外键约束 自关联 视图

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

出现这种问题的原因一般有两个：

1. 两张表里要设主键和外键的字段的数据类型或者数据长度不一样。 
   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语句