文章目录
DDL语言
常见约束
- 含义:一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性.
create table 表名(
字段名 字段类型 约束,
字段名 字段类型 约束,
字段名 字段类型 列级约束,
表级约束
);
约束分类
- not null:非空约束,用于保证该字段的值不能为空,比如姓名,学号等.
- default:默认约束,用于保证该字段有默认值,比如性别.
- primary key:主键约束,用于保证该字段的值具有唯一性,并且非空,比如学号,员工编号等.
- unique:唯一约束,用于保证该字段的值具有唯一性,可以为空.比如座位号.
- check:检查约束,mysql中不支持,语法支持但没效果,oracle支持,比如年龄,性别
- foreign key:外键约束,用于限制两个表的关系,用于保证该表该字段的值必须来自于主表的关联列的值.==在从表添加外键约束,用于引用主表中某列的值.==比如:专业编号,员工表的部门编号,员工表的工种编号.
添加约束的时机
- 创建表时
- 修改表时
- 约束的添加分类
- 列级约束
- 六大约束语法上都支持,但外键约束没有效果
- 表级约束
- 除了非空,默认,其他的都支持.
- 列级约束
创建表时添加约束
- 添加列级约束
- 语法:直接在字段名和类型后面追加约束类型即可,只支持默认,非空,主键,唯一.
use 库名;
create table stuinfo(
id int primary key,#主键
stuName varchar(20) not null,#非空
gender char(1) check(gender='男' or gender='女'),#检查约束
seat int unique,#唯3一约束
age int default 18,#默认约束
majorId int references major(id)#外键
);
create table major(
id int primary key,
majorName varchar(20)
);
desc stuinfo;
# 查看表的索引,包含主键,外键,唯一
show index from stuinfo;
- 添加表级约束
- 语法:在各个字段的最下面
constraint 约束名 约束类型(字段名),
constraint 约束名 可以省略
drop table if exists stuinfo;
create table stuinfo(
id int,
stuName carchar(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;
通用写法
drop table if exists stuinfo;
create table if not exists stuinfo(
id int primary key,
stuname varchar(20) not null,
gender char(1),
age int default 18,
seat int unique,
majorid int,
constraint fk_stuinfo_major foreign key(majorid) references major(id)
);
修改表时添加约束
- 语法:
# 添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
# 添加表级约束
alter table 表名 add constraint 约束名 约束类型(字段名) 外键应用;
- 添加非空约束
drop table if exists stuinfo;
create table stuinfo(
id int,
stuName carchar(20),
gender char(1),
seat int,
age int,
majorid int
);
# 添加非空约束
alter table stuinfo modify column stuname varchar(20) not null;
desc stuinfo;
# 添加默认约束
alter table stuinfo modify column age int default 18;
# 添加主键
# 列级主键
alter table stuinfo modify column id int primary key;
# 表级主键
alter table stuinfo add primary key(id);
# 添加唯一键
# 列级唯一
alter table stuinfo modify column seat int unique;
# 表级唯一
alter table stuinfo add unique(seat);
# 添加外键
alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);
修改表时删除约束
# 删除非空约束
alter table stuinfo modify column stuname varchar(20) null;
# 删除默认约束
alter table stuinfo modify column age int;
# 删除主键
alter table stuinfo drop primary key;
# 删除唯一
alter table stuinfo drop index seat;
# 删除外键
alter table stuinfo drop foreign key fk_stuinfo_major;
主键和唯一的对比
-
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合键 -
主键 可以 不可以 不可以 允许,但不推荐 -
唯一 可以 可以 可以 允许,但不推荐
外键约束
- 特点:
- 要求在从表设置外键关系
- 从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
- 主表的关联列必须是一个key(一般是主键或唯一)
- 插入数据时,先插入主表,再插入从表,删除数据时,先删除从表,再删除主表
案例讲解常见约束
标识列
- 标识列又称为自增长列
- 含义:可以不用手动的插入值,系统提供默认的序列值
- 特点:
- 标识列必须和主键搭配吗?不一定,但要求是一个key(主键,唯一等)
- 一个表只能有一个标识列(自增长列)
- 标识列的类型只能是数值型.(int,float,double等)
- 标识列可以通过 set auto_increment_increment=3;设置步长,可以通过手动插入值,设置起始值.
# 创建表时设置标识列
drop table if exists tab_identity;
create table tab_identity(
id int primary key auto_increment,
name varchar(20)
);
truncate table tab_identity;
insert into tab_identity(id,name) values(null,'john');
select * from tab_identity;
show variables like '%auto_increment%';
# 修改表时设置标识列
alter table tab_identity modify column id int primary key auto_increment;
# 修改表时删除标识列
alter table tab_identity modify column id int;
TCL事务控制语言
事务
-
存储引擎(了解)
-
transaction control language 事务控制语言
-
事务:一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行.
事务的特性ACID
- 事务的特性(ACID)
- 原子性(Atomicity):原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生.
- 一致性(Consistency):事务必须使数据库从一个一致性状态变换到另外一个一致性状态.
- 隔离性(Isolation):事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰.
- 持久性(Durability):持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响.
事务的创建
- 隐式事务:事务没有明显的开启和结束的标记.比如:insert,update,delete语句
delete from 表 where id=1;
- 显示事务:事务具有明显的开启和结束的标记.
- 前提:必须先设置自动提交功能为禁用
set autocommit=0;
show variables like 'autocommit';
show engines;
# 显示事务
# 步骤1:开启事务
set autocommit=0;
start transaction;# 可选的
# 步骤2:编写事务中的sql语句(select insert update delete)
语句1;
语句2;
# 步骤3:结束事务
commit;# 提交事务
rollback;# 回滚事务
# 演示事务的使用步骤
set autocommit=0;
start transaction;
update 表名 set 字段 = 500 where username='QW';
update 表名 set 字段 = 1500 where username='wq';
commit;
# 或
rollback;
select * from 表名;
数据库的隔离级别
事务并发问题的介绍
- 对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题:
- 脏读:对于两个事务T1,T2,T1读取了已经被T2更新但还没有被提交的字段,之后,若T2回滚,T1读取的内容就是临时且无效的.
- 不可重复读:对于两个事务T1,T2,T1读取了一个字段,然后T2更新了该字段,之后,T1再次读取同一个字段,值就不同了.
- 幻读:对于两个事务T1,T2,T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行,之后,如果T1再次读取同一个表,就会多出几行.
隔离级别
- 一个事务与其他事务隔离的程度称为隔离级别数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但并发性越弱
- 一般使用默认的隔离级别
- read uncommitted:出现脏读,幻读,不可重复读
- read committed:避免脏读,出现幻读和不可重复读
- repeatable read:避免脏读,不可重复读,出现幻读
- serializable:避免脏读,幻读,不可重复读.
- mysql中默认repeatable read
- oracle中默认 read committed
- 查看隔离级别:select @@tx_isolation;
- 设置隔离级别:set session/global transaction isolation level 隔离级别;
- 设置保存点:savepoint 节点名;只搭配rollback使用:rollback to 节点名;
演示事务的隔离级别:脏读,不可重复读,幻读
保存节点,回滚点的使用
# 演示savepoint 的使用
set autocommit =0 ;
start transaction;
delete from 表名account where id=25;
savepoint a;#设置保存点
delete from 表名account where id=28;
rollback to a;# 回滚到保存点a
视图
- 概念:虚拟表,和普通表一样使用
- mysql 5.1版本出现的新特性,是通过表动态生成的数据
视图的创建
- 语法:
create view 视图名
as
查询语句;
视图的好处
- 重用sql语句
- 简化复杂的sql操作,不必知道它的查询细节
- 保护数据,提高安全性
视图的修改
- 语法:
# 方式一:
create or replace view 视图名
as
查询语句;
# 方式二:
alter view 视图名
as
查询语句;
视图的删除和查看结构
- 语法:
# 删除视图
drop view 视图名,视图名,...;
# 查看视图结构
desc 视图名;
show create view 视图名;
视图的练习
视图的更新(更新数据)
- 视图的增删改也会更新原始表
- 给视图添加权限,只读,不能修改.
视图的更新演示
# 插入
insert into 已创建好的视图 values(值1,值2,值3,...);
# 修改,对原始表也有效
update 视图名 set 字段name='123' where 筛选条件;
# 删除
delete from 视图 where 筛选条件;
视图和表的对比
- 创建语法的关键字:视图(create view),表(create table)
- 是否实际占用物理空间:视图(只是保存了sql逻辑),表(保存了数据)
- 使用:视图(一般不能增删改,用来查)表(增删改查)
delete和truncate在事务使用时的区别
- delete可以回滚 ,truncate不支持回滚
演示区别
1101
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
