SQL-Day06

2019年6月26日数据库 : 第六天

回顾

• 分库分表
• 将数据冗余的部分分离出来

约束

约束的目的：
- 用于限制约定表中的数据完整性，有效性
- 数据库提供了基础的完整性、有效性检查

主键约束

• 约束一个列中的值，符合数据库主键规则要求，非空且不能重复（唯一）。如果违反就会报错！

    primary key
  

• 大多数情况下，表都使用主键约束，但有的时候，为提高数据插入性能，故意不设置主键约束（只针对MySQL，已在java算法上有设置其约束），比如日志表。

验证主键约束

1、建表：

-- 不添加主键约束

create table t1(id int,name varchar(10));
create table t2(id int,name varchar(10),primary key(id));

2、验证

-- 没有主键约束时候
insert into t1 (id, name) values(1,'Tom');	
insert into t1 (id, name) values(1,'Jerry');	
insert into t1 (id, name) values(null,'Andy');	
-- 有主键约束时候
insert into t2 (id, name) values(1,'Tom');	
insert into t2 (id, name) values(1,'Jerry');	 //error
insert into t2 (id, name) values(null,'Andy');	 //error

验证结果：数据库会检查主键列的规则, 主键不能重复, 主键不能空, 如果违反了主键约束规则, 会出现错误.

-- 主键设置在字符串上
create table t2.1(id int,name varchar(10),primary key(name));
	insert into t2.1 (id, name) values(1,'Tom');	
	insert into t2.1 (id, name) values(1,'Jerry');	
	insert into t2.1 (id, name) values(null,'Andy');	
	insert into t2.1 (id, name) values(1,'Jerry');           //error
	insert into t2.1 (id, name) values(null,'Jerry');         //error

非空 约束

验证表中的列表，不能添加空值。
在设计表的时候为列添加非空约束，则在插入更改数据时候，一旦数据为null则报错，进行添加和修改
？空字符串是否能躲过非空约束

not null

1、建表

create table t3(
	id int primary key,
	name varchar(50) not null,
	nick varchar(50)
);

2、测试

--正常插入
insert into t3(id,name,nick) 
values(1,'刘国兵',null);
insert into t3(id,name,nick) 
values(2,'芳芳','小范');
-- 错误插入
insert into t3(id,name,nick) 
values(3,null,'小强');

唯一约束

限定一个列中的值，不能重复，保持唯一 除了主键以外的其他列 保存唯一。

unique

1、建表

– 设定邮箱地址是唯一约束，不能重复

create table t4(
	id int parmary key,
	name varchar(50) not null,
	email varchar(100) unique
);

2、验证约束：插入重复的邮箱地址时候出现错误!

默认约束

default ‘男’

1. 建表

    -- 将性别的默认值设置为 男 
    create table t5(
    	id int primary key,
    	name varchar(50) not null,
    	sex varchar(10) default '男' 
    ); 
   

2. 验证

    insert into t5 (id, name) values (1, '范传奇');
    insert into t5 (id, name, sex) values (2,'刘国斌','女');
   

外键约束

检查表中点取值与另外一个表的主键列

语法： foreign key 外键列 references 主键表 主键列

FOREIGN KEY(外键列) REFERENCES 主键表(主键列)

1. 建表

    -- 主键表
    create table user(
    	id int primary key,
    	name varchar(50) not null
    ); 
    -- 外键表
    create table trad(
    	id int primary key,
    	uid int,
    	money double,
    	FOREIGN KEY(uid) REFERENCES user(id)
    );
   

   外键列: uid 列的值必须是 user id 的值!

外键特点:

1. 添加外键约束的字段值可以为null，但是不能是关联表中不存在的数据
2. 如果建立了关系被关联的数据不能先删除，被关联的表不能先删除
3. 有外键关联以后,删除数据时候,就必须先删除外键关联, 在删除主键表中的数据.

索引

• 数据库提供的一种高效查询算法！可以提高海量数据查询效率！
• 100条以内的不加索引，效率没变化
• 索引可以大大加快大数量的查询效率
• 数据量常见索引算法：B+树（B plus Tree）
  

B+树原理:

1. 数据库数据是连续存储到 磁盘 上的数据块. 每次读写一个数据块, 每个数据块读写 需要 几个ms. 如果是海量数据查询时候, 就需要很多次读写每个数据块, 累计时间很长!
2. B+树 建立两层索引数据块, 索引中包含数据的范围. 只需要读取两层索引块两次, 就能确定目标数据的位置. 查询到结果. 大大减少磁盘IO次数, 提高查询性能!
3. 在最终数据块上还有一次IO, 一共3次磁盘块IO就可以读取到数据, 其性能非常好!!

语法

create index 索引名 on 表名(字段名(?长度));

1. 创建索引

   create index idx_user_name on user(name);

2. 使用索引(自动使用索引!)

   select id, name from user where name=‘Tom’;

事务(交易)

• 是指保证一个业务过程的最小单位可靠执行. 避免出现半截情况, 要么都执行, 要么都不执行.

• 业务过程: 多个动作合成的一个完整不可再分的过程.

• 4个特点(ACID):

    1. A 原子性: 最小不可拆分"业务逻辑单位"!(不是物理级别的不可拆分!)
         	1. 尽管"业务逻辑单位"中包含多个步骤, 但从业务角度不能拆分执行!
    2. C 一致性: 业务单位执行以后(交易发生以后), 与执行之前的总数据一致.
    		1. 不能多不能少.
    3. I 隔离性: 正在发生的业务过程不能被其他业务过程打扰, 业务过程相互隔离.
    4. D 持久性: 业务过程发生过以后, 将永久保存, 不能被更改
  

• 常见数据库都(自动)支持ACID

    1. 利用数据库提供的事务指令就可以实现ACID
    2. 在事务开启时候, 执行 begin (开始)
    3. 后续的SQL, 都作为一组 "原子业务操作" 
    4. 在事务结束时候, 使用commit提交事务, 确认事务完成
    5. 如果业务失败, 使用rollback回滚事务, 取消业务过程