数据库约束、事务、视图

最新推荐文章于 2024-09-25 11:06:23 发布

菜鸡编程人

最新推荐文章于 2024-09-25 11:06:23 发布

阅读量236

点赞数

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/m0_51207400/article/details/110212950

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

约束

实例完整性约束

主键约束 PRIMARY KEY
- 标识表中的一行数据，此列不可重复，不能为null
- PRIMARY KEY
- 当我们创建表的时候在齐列名之后添加主键约束使得这列不能重复且值不能为null
- ```
CREATE TABLE `subject`(
	subjectID INT PRIMARY KEY,#加入主键约束
	subjectNAME VARCHAR(20),
	subjectHOURS INT
)CHARSET=utf8;
#加入两次相同的主键值
INSERT INTO `subject`(subjectID,subjectNAME,subjectHOURS) VALUE (1001,'xian',20);
INSERT INTO `subject`(subjectID,subjectNAME,subjectHOURS) VALUE (1001,'xian',20);
```
- 查询：insert into subject(subjectID,subjectNAME,subjectHOURS) value (1001,‘xian’,20)
  
  错误代码： 1062
  Duplicate entry ‘1001’ for key ‘PRIMARY’

唯一约束 UNIQUE

标识表中的数据，不可以重复，可以为null
UNIQUE
与主键约束不同的是可以存在null值

CREATE TABLE `subject`(
	subjectID INT PRIMARY KEY,
	subjectNAME VARCHAR(20) UNIQUE,#加入唯一约束
	subjectHOURS INT
)CHARSET=utf8;
#加入两个相同的name
INSERT INTO `subject`(subjectID,subjectNAME,subjectHOURS) VALUE (1001,'xian',20);
INSERT INTO `subject`(subjectID,subjectNAME,subjectHOURS) VALUE (1002,'xian',20);

查询：INSERT INTO subject(subjectID,subjectNAME,subjectHOURS) VALUE (1002,‘xian’,20)

错误代码： 1062
Duplicate entry ‘xian’ for key ‘subjectNAME’

自动增长列 AUTO_INCREMENT

AUTO_INCREMENT
在主键约束之后加上AUTO_INCREMENT

CREATE TABLE `subject`(
	subjectID INT PRIMARY KEY AUTO_INCREMENT,#自动增长约束
	subjectNAME VARCHAR(20) UNIQUE,
	subjectHOURS INT
)CHARSET=utf8;
#自动增长的列不需要添加
INSERT INTO `subject`(subjectNAME,subjectHOURS) VALUE ('xian',20);
INSERT INTO `subject`(subjectNAME,subjectHOURS) VALUE ('java',20);

域完整性约束

非空约束

NOT NULL
不允许为空

#创建subject表
CREATE TABLE `subject`(
	subjectID INT PRIMARY KEY AUTO_INCREMENT,
	subjectNAME VARCHAR(20) UNIQUE NOT NULL,#非空约束
	subjectHOURS INT
)CHARSET=utf8;
#添加空
INSERT INTO `subject`(subjectNAME,subjectHOURS) VALUE (NULL,20);

查询：INSERT INTO subject(subjectNAME,subjectHOURS) VALUE (null,20)

错误代码： 1048
Column ‘subjectNAME’ cannot be null

默认约束
- 指定该列的默认值
- DEGAULT 数字

引用完整性约束

CONSTRAIMT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名(列名)

CREATE TABLE speciailty(#创建被引用表（主表）
	id INT PRIMARY KEY AUTO_INCREMENT,
	specialname VARCHAR(10) UNIQUE NOT NULL
);
INSERT INTO speciailty(id,specialname) VALUE (1,'java');
INSERT INTO speciailty(id,specialname) VALUE (2,'HTML');
SELECT * FROM speciailty;

CREATE TABLE `subject`(#创建引用表（从表）
	subjectID INT PRIMARY KEY AUTO_INCREMENT,
	subjectNAME VARCHAR(20) UNIQUE NOT NULL,
	subjectHOURS INT,
	specialID INT NOT NULL,
	CONSTRAINT fk_subject_specialID FOREIGN KEY (specialID) REFERENCES speciailty(id)
)CHARSET=utf8;

INSERT INTO `subject`(subjectNAME,subjectHOURS,specialID) VALUE ('javase',30,3);#这里的specialID为3 上述的表中并没有所以报错

查询：insert into subject(subjectNAME,subjectHOURS,specialID) value (‘javase’,30,3)

错误代码： 1452
Cannot add or update a child row: a foreign key constraint fails (mydb1.subject, CONSTRAINT fk_subject_specialID FOREIGN KEY (specialID) REFERENCES speciailty (id))
要删除主表需要先删除从表
从表可以直接删除

事务

事务特性
- 原子性
  - 表示一个事物的索引操作是一个整体，要么全部成功，要么全部失败
- 一致性
  - 表示一个事务内有一个操作失败时，所有更改过的数据都必须回滚到修改前的状态
- 隔离性
  - 事务不会查看中间状态的数据，只能查看修改之前或者回滚之后的数据
- 持久性
  - 当事务完成以后对于系统或者数据库的影响是永久性的；

事务的应用

用于增删改查的操作结果，可通过程序逻辑手动控制事务提交或者回滚；

转账的完善

#开启事务
START TRANSACTION;

UPDATE `account` SET money = money-1000 WHERE id=1;
UPDATE `account` SET money = money+1000 WHERE id=2;
#提交
COMMIT;
#失败回滚
ROLLBACK;

视图

虚拟表，从一个表和多个表中查询出来的表作用和真实的表一样，可以进行增删改查等一系列操作，视图可以使用户操作方便，并保障数据库系统安全。
视图特点
- 优点
  - 简单化，数据所见即所得
  - 安全性，用户只能查询或修改他们所能得到的数据
  - 逻辑独立性，可以屏蔽真是表结构变化带来的影响
- 缺点
  - 性能相对较差，简单的查询也会变得稍显复杂
  - 修改不方便，特别是复杂的聚合视图基本无法更改

视图的创建

CREATE VIEW

CREATE VIEW t_empinfo   #创建一个视图
AS
SELECT money FROM `account`;#定义视图数据来源

SELECT * FROM t_empinfo;#直接查询视图

视图的修改
- CREATE OR REPLACE VIEW 第一种方式
  - ```
  CREATE OR REPLACE VIEW t_empinfo
  AS
  SELECT money,id FROM `account`;
```
- 不知道是不是视图本身存在
- ALTER VIEW
  - ```
  ALTER VIEW t_empinfo
  AS
  SELECT money,id FROM `account`;
```
- 知道视图本身就存在使用alter
视图的删除
- DROP VIEW 视图名称
- 删除视图不会对原表有任何影响
- 视图不会独立于存储数据，原表发生改变，视图也发生改变，没有优化任何性能
- 如果视图包含以下结构中的一种，则视图不可更新
  - 聚合函数的结果
  - 去重后的结果 DISTINCT
  - 分组后的结果 GROUP BY
  - 筛选过滤后的结果 HAVING
  - 联合后的结果 UNION , UNION ALL