MySQL基础之约束、视图

一、约束

1.1 约束的概念和分类

• 约束的概念
  • 对表中的数据进行限定，保证数据的正确性、有效性、完整性！
• 约束的分类

约束	说明
PRIMARY KEY	主键约束
PRIMARY KEY AUTO_INCREMENT	主键、自动增长
UNIQUE	唯一约束
NOT NULL	非空约束
FOREIGN KEY	外键约束
FOREIGN KEY ON UPDATE CASCADE	外键级联更新
FOREIGN KEY ON DELETE CASCADE	外键级联删除

1.2 主键约束

• 主键约束特点
  • 主键约束包含：非空和唯一两个功能
  • 一张表只能有一个列作为主键
  • 主键一般用于表中数据的唯一标识
• 建表时添加主键约束

CREATE TABLE 表名(
    列名 数据类型 PRIMARY KEY,
    列名 数据类型,
    ...
);

• 删除主键

-- 标准语法
ALTER TABLE 表名 DROP PRIMARY KEY;

• 建表后单独添加主键

ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;

1.3 主键自动增长约束

• 建表时添加主键自增约束

-- 标准语法
CREATE TABLE 表名(
   列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
   列名 数据类型,
   ...
);

• 删除自动增长

– 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型;

- 建表后单独添加自动增长
```sql
-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;

1.4 唯一约束

• 建表时添加唯一约束

-- 标准语法
CREATE TABLE 表名(
    列名 数据类型 UNIQUE,
    列名 数据类型,
    ...
);

• 删除唯一约束

-- 标准语法
ALTER TABLE 表名 DROP INDEX 列名;

• 建表后单独添加唯一约束

-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;

1.5 非空约束

• 建表时添加非空约束

CREATE TABLE 表名(
    列名 数据类型 NOT NULL,
    列名 数据类型,
    ...
);

• 删除非空约束

-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型;

• 建表后单独添加非空约束

-- 标准语法
ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;

1.6 外键约束

• 外键约束概念
  • 让表和表之间产生关系，从而保证数据的准确性！
• 建表时添加外键约束

  -- 创建db2数据库
  CREATE DATABASE db2;
  -- 使用db2数据库
  USE db2;
  
  -- 创建user用户表
  CREATE TABLE USER(
      id INT PRIMARY KEY AUTO_INCREMENT,    -- id
      NAME VARCHAR(20) NOT NULL             -- 姓名
  );
  -- 添加用户数据
  INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五');
  
  -- 创建orderlist订单表
  CREATE TABLE orderlist(
      id INT PRIMARY KEY AUTO_INCREMENT,    -- id
      number VARCHAR(20) NOT NULL,          -- 订单编号
      uid INT                               -- 订单所属用户
  );
  -- 添加订单数据
  INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
  (NULL,'hm003',2),(NULL,'hm004',2),
  (NULL,'hm005',3),(NULL,'hm006',3);
  
  -- 添加一个订单，但是没有所属用户。这合理吗？
  INSERT INTO orderlist VALUES (NULL,'hm007',8);
  -- 删除王五这个用户，但是订单表中王五还有很多个订单呢。这合理吗？
  DELETE FROM USER WHERE NAME='王五';
  
  -- 所以我们需要添加外键约束，让两张表产生关系

• 外键约束格式

CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)

• 创建表添加外键约束

-- 创建user用户表
CREATE TABLE USER(
    id INT PRIMARY KEY AUTO_INCREMENT,    -- id
    NAME VARCHAR(20) NOT NULL             -- 姓名
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五');

-- 创建orderlist订单表
CREATE TABLE orderlist(
    id INT PRIMARY KEY AUTO_INCREMENT,    -- id
    number VARCHAR(20) NOT NULL,          -- 订单编号
    uid INT,                              -- 订单所属用户
    CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)   -- 添加外键约束
);
-- 添加订单数据
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
(NULL,'hm003',2),(NULL,'hm004',2),
(NULL,'hm005',3),(NULL,'hm006',3);

-- 添加一个订单，但是没有所属用户。无法添加
INSERT INTO orderlist VALUES (NULL,'hm007',8);
-- 删除王五这个用户，但是订单表中王五还有很多个订单呢。无法删除
DELETE FROM USER WHERE NAME='王五';

• 删除外键约束

-- 标准语法
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

-- 删除外键
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;

• 建表后添加外键约束

-- 标准语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 添加外键约束
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);

二、视图

2.1 视图的概念

• 视图是一种虚拟存在的数据表
• 这个虚拟的表并不在数据库中实际存在
• 作用是将一些比较复杂的查询语句的结果，封装到一个虚拟表中。后期再有相同复杂查询时，直接查询这张虚拟表即可
• 说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上

2.2 视图的好处

• 简单
  • 对于使用视图的用户不需要关心表的结构、关联条件和筛选条件。因为这张虚拟表中保存的就是已经过滤好条件的结果集
• 安全
  • 视图可以设置权限 , 致使访问视图的用户只能访问他们被允许查询的结果集
• 数据独立
  • 一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

2.3 视图的创建

-- 标准语法
CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;

2.4 视图的查询

-- 标准语法
SELECT * FROM 视图名称;

2.5 视图的修改

• 修改视图表中的数据

-- 标准语法
UPDATE 视图名称 SET 列名=值 WHERE 条件;

-- 修改视图表中的城市名称北京为北京市
UPDATE city_country SET city_name='北京市' WHERE city_name='北京';

-- 查询视图
SELECT * FROM city_country;

-- 查询city表,北京也修改为了北京市
SELECT * FROM city;

注意：视图表数据修改，会自动修改源表中的数据

• 修改视图表结构

-- 标准语法
ALTER VIEW 视图名称 [(列名列表)] AS 查询语句;

-- 查询视图2
SELECT * FROM city_country2;

-- 修改视图2的列名city_id为id
ALTER
VIEW
    city_country2 (id,city_name,cid,country_name)
AS
    SELECT t1.*,t2.country_name FROM city t1,country t2 WHERE t1.cid=t2.id;

2.6 视图的删除

-- 标准语法
DROP VIEW [IF EXISTS] 视图名称;

-- 删除视图
DROP VIEW city_country;

-- 删除视图2，如果存在则删除
DROP VIEW IF EXISTS city_country2;

2.7 视图总结

• 视图是一种虚拟存在的数据表
• 这个虚拟的表并不在数据库中实际存在
• 说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上
• 视图的好处
  • 简单
  • 安全
  • 数据独立

二级目录

三级目录