数据库(mysql)-新手笔记(主外键,视图)

数据库基本知识点- http://t.csdnimg.cn/CVa9e

主外键

主键(唯一性,非空性)

主键是数据库表中的一个或多个字段，其值唯一标识表中的每一行/记录。

唯一性: 主键字段中的每个值都必须是唯一的，不能有两个或更多的记录具有相同的主键值

非空性：主键字段不能包含NULL值。

外键(引用完整 防无效数据)

引用完整性：确保引用的数据存在。

防止无效数据：通过限制可以在外键字段中输入的值，可以防止无效数据的插入。

作用

如果想要删除含主键的表的话,需要解除外键否则是不允许删除的 ,提高了安全保密性

其次链接了主键的字段名的外键的字段名如果超出含主键字段名的数据的范围 ,可以控制用户不会乱输入,确保了表的规范性

如果主键不唯一的话,也不会让其插入(更新) ,防止了数据重复录入

模版

CREATE TABLE 表名(字段名 字段类型 NOT NULL PRIMARY KEY...);
//创建一个order创建一个表,因为order是关键字所以用反引号包裹
CREATE TABLE 表名(字段名 字段类型...,FOREIGN KEY (字段名) REFERENCES 引用的表(字段名有主键)
NOT NULL 非空约束 插入和更新数据都不能插入更改为null,

CREATE TABLE `order`(order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,item VARCHAR(10));
//创建一个order创建一个表,因为order是关键字所以用反引号包裹
CREATE TABLE personnel(personnel_id INT NOT NULL PRIMARY KEY ,name VARCHAR(10));
//创建一个人员表
CREATE TABLE personnelNeeded(order_id INT NOT NULL ,personnel_id INT NOT NULL ,satisfaction INT,moneyAmount DOUBLE,FOREIGN KEY (order_id) REFERENCES `order`(order_id),FOREIGN KEY (personnel_id) REFERENCES personnel(personnel_id),PRIMARY KEY (order_id,personnel_id))
//外键连接order表中的order_id和personnel表中的personnel_id

需要注意的是可以使用 AUTO_INCREMENT来实现每插入一行,数据会自增一

如果id字段已经有了主键约束,那么只要添加自增即可(没添加自增的情况)

//举个例子
CREATE TABLE `order`(order_id INT NOT NULL PRIMARY KEY ,item VARCHAR(10));
//创建一个order创建一个表,因为order是关键字所以用反引号包裹
ALTER TABLE `order` CHANGE id id INT AUTO_INCREMENT//不用写主键约束

特殊情况

如果主键字段有自增,显示的插入的null值也不会将null插入,主键依然会自增

CREATE TABLE `order`(order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,item VARCHAR(10));
INSERT INTO `order`(order_id,item) VALUES(NULL,'香蕉')//这边id也会自增,不会出现null的显示

插入

注意的是 PRIMARY KEY是唯一

如果是多个组合在一起的话,只要有一个 PRIMARY KEY就可以录入到含外键的表,

#插入单字字段名的数据(含主键)
INSERT INTO `order`(item) VALUES ('苹果'),('李子'),('香蕉'),('橙子'),('水蜜桃'),('牛油果');
SELECT * FROM `order`;
#插入多字字段名的数据(含主键)
INSERT INTO personnel VALUES (1,'张四'),(2,'张五'),(3,'张六'),(4,'张七'),(5,'张八');
SELECT * FROM personnel;
#插入多字字段名的数据(含外键)
INSERT INTO personnelNeeded VALUES (1,2,5,300),(1,3,5,500),(2,2,5,700),(3,2,5,400),(4,2,5,800),(5,4,3,300);

 查询

通过外键查询主键其他的字段名下的数据

单主外键相连

关键字JOIN 含主键的表 (可以取别名) ON 含主键的表.字段名 (当前外键的表).字段名

SELECT (item) FROM personnelneeded 
JOIN `order` o on o.order_id = personnelNeeded.order_id;

多主外键相连 
变化的是SElECT 后的查询字段名 已经join后面在加一个含主键表的字段名

SELECT item,name FROM personnelneeded
 JOIN `order` o on o.order_id = personnelNeeded.order_id 
JOIN personnel p on personnelNeeded.personnel_id = p.personnel_id;

视图

作用

提高重用性：视图相当于一个虚拟表，它可以使复杂的SQL查询语句变得简单，减少复杂SQL语句的使用频次

数据库重构：当需要修改数据库结构时，通过使用视图，可以不修改脚本程序的功能。也就是说，对数据库进行重构，不会影响到基于视图的程序的运行

提高安全性：视图可以对不同的用户设定不同的权限，提供不同的数据视图 ,无法修改变结构

据清晰化：视图可以按需创建，只包含用户关心的数据，使得数据更加清晰，方便用户理解和使用。 

这边我们使用一个视图将通过外键查询的字段名来创建一个虚拟的表

CREATE VIEW 视图名称 AS
 SELECT 字段名

FROM 含外键的表
JOIN 含主键的表 (别名)on(别名).(主键字段名) =含外键的表.外键的字段名
 

CREATE VIEW  orderView AS
 SELECT name,item FROM personnelNeeded 
JOIN `order` o on o.order_id = personnelNeeded.order_id 
JOIN personnel p on p.personnel_id = personnelNeeded.personnel_id;

巧学巧记

主键唯一标识 ,外键引用主键用 REFERENCES指向某含主键表

主键非空,外键调用主键时候必须符合主键已录入的数据

外键想要查询主键中的其他字段名下的数据 需要用主键字段名链接外键字段名

关键字 JOIN 主键表 (别名) ON 别名.主键字段名= 外键表名.主键字段名

视图可以限制用户权限,不会更改表结构