3MySQL数据库-SQL语句下-⾃关联-外键-分表-视图 - 全栈式开发29

一、⾃关联

• 可以简单的理解为⾃⼰与⾃⼰进⾏连接查询。就是在表里设置了多种id，用来标志各类，由此我们可以通过关系以及id唯一性来进行索引
• ⽐如： ⼀张 areas 表⾥⾯有省市区，各省各市都有两个id，一个用来标志该名称，另一个表示所指向的id，例如深圳市的id=2287，ppid=2261，而广东省的id=2261，通过这种自关联，我们可以设置所有ppid=2261的，就可以锁定所有广东的市。
• 添加自关联可以更好的查询数据
  

二、外键

定义

• MySQL的外键约束(FOREIGN KEY)是表的⼀个特殊字段。对于两个具有关联关系的表⽽⾔， 相关联字段中的主键所在表就是主表(⽗表)，外键所在的表就是从表(⼦表)。
• myisam 不支持外键 innoDB支持，记得查看自己的设置的环境是啥，在cmd下链接后数据库后查询

意义

• 为了保证数据的一致性和完整性
• 主表是定义了某个唯一的字段，而从表的相同字段是跟随主键改变而改变的，也因为主表有定义，从表的数值才有意义

外键设置选择

• 级联删除：cascade，就是主表删除的话，从表也会删掉，是单方面的约束，就是从表依赖于主表
• 相互约束restrict：默认，相互关联，也就是当我们想要删掉主表的值时要从表没有引用该值，从表要添加某值时，要主表先有这个数据
• no action ：不做任何动作
• set null ：设为空

注意

• 主键与外键的类型必须保持⼀致。
• 主键不能包含空值，但允许在外键中出现空值。
• 只有主表的主键才可以成为外键的参考关联字段
• 设置外键的时候，主表一定要存在
• 主表的主键是primary key，才可以成为外键的参考关联字段

创建

• 创建表的时候添加外键
  • [CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]
• 表创建后添加
  • ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

删除外键

• ALTER TABLE student DROP FOREIGN KEY fk_cid;

练习：

• classes班级的id是唯一先决定的，而学生所属那个班级是根据班级现有的决定的，所classes是主表，classes班级的id是主键，students表示从表，所以要添加班级外键
  

CREATE TABLE classes(
    id INT(4) NOT NULL PRIMARY KEY,
    NAME VARCHAR(36)
);

-- 创建学生表 指定cid为外键
-- [CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]
CREATE TABLE student(
    sid INT(4) NOT NULL PRIMARY KEY,
    sname VARCHAR(30),
    cid INT(4),
    CONSTRAINT fk_cid FOREIGN KEY(cid) REFERENCES classes(id)
);

可以看到连接的效果就是关联了

三、分表

• 分表是因为当⼀张表的数据量⽐较多时，但是我们只需要查询其中的某个字段数据，就会导致 查询效率降低，所以此处我们需要使⽤到分表。当然后⾯会细讲。

练习

• 给下面的表分出商品品类和商品品牌的表

CREATE TABLE goods(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  NAME VARCHAR(150) NOT NULL,
  cate_name VARCHAR(40) NOT NULL,
  brand_name VARCHAR(40) NOT NULL,
  price DECIMAL(10,3) NOT NULL DEFAULT 0,
  is_show TINYINT NOT NULL DEFAULT 1,
  is_saleoff TINYINT NOT NULL DEFAULT 0
);

-- 插入数据
INSERT INTO goods VALUES(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'x240 超极本','超级本','联想','4880',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'u330p 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'svp13226scb 触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);
INSERT INTO goods VALUES(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',DEFAULT,DEFAULT); 
INSERT INTO goods VALUES(0,'商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

-- 1.创建分类表
CREATE TABLE goods_cates(
	id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
	NAME VARCHAR(40) NOT NULL
);

CREATE TABLE goods_brands(
	id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
	NAME VARCHAR(40) NOT NULL
);
-- 2. 给分类表添加分类名称
-- 2.1 商品表cate_name 字段 进行 分组
SELECT cate_name FROM goods GROUP BY cate_name;

-- 2.2 查询出来的结果怎么插入到 goods_cates 表里面
-- 注意：此处不是使用的values 而是 目标表的 字段名 + 查询结果。
INSERT INTO goods_cates (NAME) SELECT cate_name FROM goods GROUP BY cate_name;
INSERT INTO goods_brands (NAME) SELECT brand_name FROM goods GROUP BY brand_name;


-- 3.需要将 goods 表里面的 cate_name 换成 对应 的 id
UPDATE goods g INNER JOIN goods_cates c ON g.`cate_name`=c.`name` SET g.`cate_name`=c.`id`;

UPDATE goods g INNER JOIN goods_brands c ON g.`brand_name`=c.`name` SET g.`brand_name`=c.`id`;

四、视图

（一）视图介绍

定义

• 视图(View)是⼀种虚拟存在的表，对于使⽤视图的⽤户来说基本上是透明的。
• 视图并不在数据 库中实际存在，⾏和列数据来⾃定义视图的查询中使⽤的表，并且是在使⽤视图时动态⽣成 的。
• 它是虚拟表，它是原表查询的结果集，
• 视图一般使用来查询，可以对原表进行修改

作用

• 简单：简单了我们的查询语句，提⾼了重⽤性，就像⼀个函数。
• 安全：不同用户有不同的视图，提⾼了安全性能，可以针对不同的⽤户，设定不同的视图
• 数据独立：⼀旦视图的结构确定了，可以屏蔽表结构变化对⽤户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

原表与视图的关系

• 改变原表的与视图无关字段，对视图无影响，有关字段，视图会自动更新
• 改变原表不能变动视图创建的条件，否则视图的结果会为空。
• 可以修改视图的数据，但可以修改的数据有限

（二）视图实现语法

创建视图

• create view 视图名称 as select 语句

CREATE VIEW v_pro AS SELECT * FROM provinces; 

查看视图

• select * from v_pro;
• show tables; 这里也会包含视图表的信息

删除

• 删除整个视图：drop view 视图名称;

-- 删除视图
DROP VIEW v_p_c;

修改

• 删除某条数据：DELETE FROM v_province WHERE 条件;
• 成功与否和表创建的方式有关

-- 删除视图数据 
DELETE FROM v_province WHERE province="北京市"; 
-- 将 cname 字段的 长沙 改为 长沙市 ，报错，因为创建表from字句中包含多个表
UPDATE v_p_c SET cname="长沙市" WHERE cname="长沙";

这些视图不能修改

• select⼦句中包含distinct
• select字句中包含组函数
• select语句中包含group by⼦句
• selecy语句红包含order by⼦句
• where⼦句中包含相关⼦查询
• from字句中包含多个表
• 如果视图中有计算列，则不能更新
• 如果基表中有某个具有⾮空约束的列未出现在视图定义中，则不能做insert操作。