数据库学习笔记-3
一、约束
1.唯一性约束
unique
唯一性约束修饰的字段具有唯一性,不能重复。但可以为null
/*列级约束*/
create table t_user(
id int,
username varchar(255) unique
);
多字段添加 (表级约束,约束的字段不能都相同,但可以部分相同):
create table t_user(
usercode varchar(255),
username varchar(255),
unique(usercode,username)
);
not null约束只有列级约束,没有表级约束
2.主键约束
主键约束:不能为null也不能重复,一张表的主键约束只能有1个
/*列级约束*/
create table t_user(
id int primary key,
);
/*表级约束*/
create table t_user(
id int,
primary key(id)
);
最好不要拿着和业务挂钩的字段做为主键。因为以后的业务一旦发生改变的时候,主键也可能需要随着发生变化,但有的时候没有办法变化,因为变化可能会导致主键重复
主键值自增:
create table t_user(
/*id字段自动维护一个自增的数字,从1开始,以1递增*/
id int primary key auto_increment,
username varchar(255)
);
insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('c');
Oracle当中提供的自增机制为序列(sequence)对象
3.外键约束
t_student中的
classno
字段引用t_class表中的cno
字段,此时t_student表叫做子表。t_class表叫做父表
删除数据的时候,先删除子表,再删除父表
添加数据的时候,先添加父表,再添加子表
创建表的时候,先创建父表,再创建子表
删除表的时候,先删除子表,再删除父表
create table t_class(
cno int,
cname varchar(255),
primary key(cno)
);
create table t_student(
sno int,
sname varchar(255),
classno int,
primary key(sno),
foreign key(classno) references t_class(cno)
);
外键可以为null
外键字段引用其他表的某个字段的时候,被引用的字段不一定是主键,但至少是具有unique约束,具有唯一性,不可重复
二、存储引擎
存储引擎:表的存储方式
/* 建表的时候可以指定存储引擎,也可以指定字符集*/
CREATE TABLE `t_x` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
show engines \G
查看存储引擎列表
1.MyISAM
MyISAM
采用三个文件组织一个表:
xxx.frm
(存储格式的文件)
xxx.MYD
(存储表中数据的文件)
xxx.MYI
(存储表中索引的文件)
优点:可被压缩,节省存储空间。并且可以转换为只读表,提高检索效率。
缺点:不支持事务。
2.InnoDB
表的结构存储在xxx.frm
文件中
数据存储在tablespace这样的表空间中(逻辑概念),无法被压缩,无法转换成只读
这种InnoDB
存储引擎在MySQL数据库崩溃之后提供自动恢复机制
InnoDB
支持级联删除和级联更新(例如父表发生变动同步修改在子表上)
优点:支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障
3.MEMORY(HEPA)
优点:查询速度最快
缺点:不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的
三、事务(Transaction)
一个事务是一个完整的业务逻辑单元,不可再分。事务的存在是为了保证数据的完整性,安全性
例如从A账户向B账户转账10000元,需要执行两条update语句:
update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';
想要保证以上的两条DML(insert delete update)语句同时成功或者同时失败,那么就要使用数据库的"事务机制"
事务包括四大特性:ACID
A:原子性:事务是最小的工作单元,不可再分
B:一致性:事务必须保证多条DML语句同时成功或者同时失败
C:隔离性:事务A与事务B之间具有隔离
D:持久性:持久性说的是最终数据必须持久化到硬盘中,事务才算成功结束
1.隔离级别
事务隔离性存在隔离级别,理论上隔离级别包括4个:
第一级别:读未提交(read uncommitted)
对方事务还没有提交,我们当前事务可以读取到对方未提交的数据,存在脏读(Dirty Read) 现象读到脏数据**(数据在缓存或其它地方,一回滚断点就失去意义)**
第二级别:读已提交(read committed)
对方事务提交之后的数据我方可以读取到,不可重复读**(当前事务未结束,其它事务进行的修改直接作用在当前数据库上,导致每次读到的内容都不一样)**
第三级别:可重复读(repeatable read)
解决了不可重复读问题,存在的问题是读取到幻象(只是数据库的备份)
第四级别:序列化读/串行化读
解决了所有问题但效率低,需要事务排队
2.示例
mysql中的事务是支持自动提交的,只要执行一条DML语句,则提交一次
关闭默认提交:start transaction;
关闭后rollback
可回滚到最近的一次commit
之后
set global transaction isolation level read uncommitted;
set global transaction isolation level read committed;
set global transaction isolation level repeatable read;
四、索引
索引底层采用的数据结构是:B + Tree
查询一张表的时候有两种检索方式:
1.全表扫描
2.根据索引检索(缩小了扫描的范围)
数据一旦修改,索引需要重新排序,进行维护,适用于DML操作少的字段
create index 索引名称 on 表名(字段名);
create index emp_sal_index on emp(sal);
drop index 索引名称 on 表名;
drop index emp_sal_index on emp(sal);
主键具有unique约束的字段会自动添加索引
查看sql语句的执行计划(explain):
explain select ename,sal from emp where sal = 5000;
/*模糊查询的时候,第一个通配符使用的是%,这个时候索引是是失效的*/
select ename from emp where ename like ' %A% ';
五、视图(view)
create view myview as select empno,ename from emp;
select * from myview;
drop view myview;
只有DQL语句才能以视图对象的方式创建出来,对视图进行增删改查,会影响到原表数据(通过视图影响原表数据,不是直接操作的原表)
create table emp_bak as select * from emp;
create view myview1 as select empno,ename,sal from emp_bak;
/*通过视图修改原表数据*/
update myview1 set ename = 'hehe',sal = 1 where empno 7369;
/*通过视图删除原表数据*/
delete from myview1 where empno = 7369;
视图可以隐藏表的实现细节,保密级别较高的系统,数据库只对外提供相关的视图,java程序员只对视图对象进行CRUD
六、数据库设计三范式
第一范式
任何一张表都应该有主键,并且每一个字段原子性不可再分
第二范式
建立在第一范式的基础上,所有非主键字段完全依赖主键,不能产生部份依赖**(复合主键)**
多对多关系使用三张表,关系表+两个含有外键表
t_student 学生表
sno(pk) sname
---------------------
1 张三
2 李四
3 王五
t_teacher 讲师表
tno(pk) tname
----------------------
1 王老师
2 张老师
3 李老师
t_student_teacher_relation 学生讲师关系表
id(pk) sno(fk) tno(fk)
-------------------------------------------
1 1 3
2 1 1
3 2 2
4 2 3
5 3 1
6 3 3
第三范式
建立在第二范式的基础上,所有非主键字段直接依赖主键,不能产生传递依赖**(字段A依赖字段B,字段B依赖主键,这样B就相当于A的主键了,要单建一张表)**
一对多关系使用两张表,关系中多的表加外键
班级 t_class
cno(pk) cname
--------------------------
1 班级1
2 班级2
学生 t_student
sno(pk) sname classno(fk)
--------------------------------------------
101 张1 1
102 张2 2
103 张3 2
104 张4 1
105 张5 2
一对一设计
1.主键共享
t_user_login 用户登陆表
id(pk) username password
----------------------------------------
1 zs 123
2 ls 456
t_user_detail 用户详细信息表
id(pk+fk) realname tel ...
----------------------------------------------------
1 张三 11111111112234
2 李四 12112523432412
2.外键唯一
t_user_login 用户登陆表
id(pk) username password
----------------------------------------
1 zs 123
2 ls 456
t_user_detail 用户详细信息表
id(pk) realname tel userid(fk+unique)
----------------------------------------------------
1 张三 111111114 2
2 李四 121432412 1