约束
- 含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性。
- 分类
六大约束
NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认值
比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值
比如学生表的专业编号，员工表的部门编号，员工表的工种编号
- 添加约束的时机：1.创建表时；2.修改表时
- 约束的添加分类：
- 列级约束：六大约束语法上都支持，但外键约束没有效果
- 表级约束：除了非空、默认，其他的都支持
- 语法
CREATE TABLE 表名{
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
};
一、创建表时添加约束
#先新建一个库
CREATE DATABASE students;
1.添加列级约束
- 语法：直接在字段名和类型后面追加 约束类型即可。只支持：默认、非空、主键、唯一
2.添加表级约束
- 语法：语法：在各个字段的最下面 【constraint 约束名】 约束类型(字段名)
TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
SHOW INDEX FROM stuinfo;
- 通用的写法：★
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20),
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
create table 表名(
字段名 字段类型 not null,#非空
字段名 字段类型 primary key,#主键
字段名 字段类型 unique,#唯一
字段名 字段类型 default 值,#默认
constraint 约束名 foreign key(字段名) references 主表（被引用列）
);
注意：
支持类型 可以起约束名
列级约束 除了外键 不可以
表级约束 除了非空和默认 可以，但对主键无效
列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求
主键和唯一的区别
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至多有1个 √，但不推荐
唯一 √ √ 可以有多个 √，但不推荐
二、修改表时添加约束
- 语法
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
三、修改表时删除约束
#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
SHOW INDEX FROM stuinfo;
修改表时添加或删除约束的具体语法总结：
1、非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
删除非空
alter table 表名 modify column 字段名 字段类型 ;
2、默认
添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认
alter table 表名 modify column 字段名 字段类型 ;
3、主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;
4、唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名;
5、外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）;
删除外键
alter table 表名 drop foreign key 约束名;
四、自增长列(标识列)
- 含义：可以不用手动的插入值，系统提供默认的序列值
- 特点
1.不用手动插入值，可以自动提供序列值，默认从1开始，步长为1
auto_increment_increment
如果要更改起始值：手动插入值
如果要更改步长：更改系统变量
set auto_increment_increment=值;
2.一个表至多有一个自增长列
3.自增长列只能支持数值型
4.自增长列必须为一个key
- 语法总结
一、创建表时设置自增长列
create table 表(
字段名 字段类型 约束 auto_increment
);
二、修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment;
三、删除自增长列
alter table 表 modify column 字段名 字段类型 约束;
事务
- 事务：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
事务的特性(ACID)
- 原子性：一个事务不可再分割，要么都执行要么都不执行
- 一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
- 隔离性：一个事务的执行不受其他事务的干扰
- 持久性：一个事务一旦提交，则会永久的改变数据库的数据.
事务的使用步骤 ★
- 了解
- 隐式（自动）事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete
- 显式事务：事务具有明显的开启和结束的标记；前提：必须先设置自动提交功能为禁用
- 具体步骤：
步骤1：开启事务
set autocommit=0;
start transaction;可选的
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...
步骤3：结束事务
commit;提交事务
rollback;回滚事务
并发事务
1、事务的并发问题是如何发生的？
- 多个事务同时操作 同一个数据库的相同数据时
2、并发问题都有哪些？
对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题：
- 脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据；
- 不可重复读：一个事务多次读取，结果不一样；
- 幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据。
3.数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题.
4.一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱
5、如何解决并发问题？
- 通过设置隔离级别来解决并发问题
6、隔离级别
脏读 不可重复读 幻读
read uncommitted:读未提交 × × ×
read committed：读已提交 √ × ×
repeatable read：可重复读 √ √ ×
serializable：串行化 √ √ √
- Oracle 支持的2 种事务隔离级别：READ COMMITED, SERIALIZABLE。Oracle 默认的事务隔离级别为:READ COMMITED
- Mysql 支持4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;
#2.演示事务对于delete和truncate的处理的区别
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
回滚点的演示
- savepoint 节点名;设置保存点
#3.演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点
SELECT * FROM account;
视图
- 视图：虚拟表,和普通表一样使用。mysql5.1版本出现的新特性，是通过表动态生成的数据。
- 应用场景
- 多个地方用到同样的查询结果
- 该查询结果使用的sql语句较复杂
视图的创建
- 语法：create view 视图名 as 查询语句;
- 案例
USE myemployees;
#1.查询姓名中包含a字符的员工名、部门名和工种信息
#①创建
CREATE VIEW myv1 AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
#②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';
#2.查询各部门的平均工资级别
#①创建视图查看每个部门的平均工资
CREATE VIEW myv2 AS
SELECT AVG(salary) ag,department_id
FROM employees GROUP BY department_id;
#②使用
SELECT myv2.`ag`,g.grade_level FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#3.查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
#4.查询平均工资最低的部门名和工资
CREATE VIEW myv3 AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;
SELECT d.*,m.ag FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;
- 视图的好处
- 重用sql语句
- 简化复杂的sql操作，不必知道它的查询细节
- 保护数据，提高安全性
视图的修改
- 语法
方式一：
create or replace view 视图名 as 查询语句;
方式二：
alter view 视图名 as 查询语句;
视图的删除
- 用户可以一次删除一个或者多个视图，前提是必须有该视图的drop权限。
- 语法：drop view 视图1，视图2,…;
视图的查看
- 语法
desc 视图名;
show create view 视图名;
视图的更新
- 插入：insert
- 修改：update
- 删除：delete
- 查看：select
注意：视图一般用于查询的，而不是更新的，所以具备以下特点的视图都不允许更新
- 包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all
- 常量视图
- Select中包含子查询
- join
- from一个不能更新的视图
- where子句的子查询引用了from子句中的表
视图和表的对比
关键字 是否占用物理空间 使用
视图 view 占用较小，只保存sql逻辑 一般用于查询
表 table 保存实际的数据 增删改查
扫一扫
2882
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
