这里写目录标题
一、约束
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查询语句上
- 视图的好处
- 简单
- 安全
- 数据独立