主键(列级和表级)
注意 :一张表必须有,且只能有一个主键约束!!作用:主键值是这条记录在这张表中的唯一标识(可以看做这条记录的身份证号); 分类
1. 根据主键字段的数量划分
单一主键:
复合主键:即表级主键约束(可以理解为两个身份证号粘到一起,仍然算一个主键约束,但违背了三范式)
primary key ( 字段1 ,字段2 , . . . )
2. 根据主键性质划分
自然主键:推荐(跟业务无关)
业务主键:例如拿银行卡号,身份证号当主键,但主键字段最好不要跟业务挂钩(万一业务发生改变,主键值就需要改,但有时修改可能会导致主键重复),最好单纯设置一个编号作为主键
自增主键: primary key auto_increment
drop table if exists t_user;
create table t_user (
id int primary key auto_increment,
username varchar ( 255 )
) ;
insert into t_user ( username) values ( 'a' ) , ( 'b' ) , ( 'c' ) , ( 'd' ) ;
select * from t_user;
+ -- -- + -- -- -- -- -- +
| id | username |
+ -- -- + -- -- -- -- -- +
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+ -- -- + -- -- -- -- -- +
存储引擎(mysql默认存储引擎InnoDB,默认字符集utf8)
定义:不同存储引擎代表了底层存储数据的不同方式 完整的建表语句:
create table tmp (
id int
) ENGINE= InnoDB DEFAULT CHARSET= utf8;
show engines \G
MyISAM:
优点:MySQL最常用的,用三个文件存储一张表; 可以对文件压缩、或改为只读表,提高检索效率;
表名. frm(存储表结构的文件)
表名. MYD(存储表数据的文件)
表名. MYI(存储表索引的文件)
缺点:不支持事务
InnoDB :
优点:
表名. frm(在数据库目录中,每个表以. frm文件表示)
表空间tablespace (存储表的内容,是一个逻辑概念,因此无法压缩)
支持事务,行级锁等,保证数据的安全(因此是默认引擎);
在MySQL数据库崩溃后可以自动恢复;
支持外键,级联更新和级联删除(但通常不这么干,如果父表有子表,一般不会直接修改父表)
级联更新:(正常先增加父表记录,再增加子表记录;但InnoDB 支持级联更新,即修改父表的一条记录,则子表中所有使用到该条记录的记录都会同步更新);
级联删除:同理。删除一条父表记录,则会同步删除子表中所有使用了该条记录的记录
Memory:
优点:所有数据和索引存在内存中,且行的内容固定,因此速度最快,适合查询(断电就没了,数据容易丢失);
缺点:不支持事务;
需要节省空间:MyISAM
看中安全:InnoDB
追求速度:Memory
事务(commit,rollback,savepoint):
定义:类似于原子操作,一个事务就是一个完整的业务单元,不可再分;一次事务的多条SQL语句要么同时成功,要么同时失败 事务过程:开启事务到结束事务(提交或回滚)之间,所做的操作不会直接修改硬盘上的文件数据,而是保存在历史操作中;最后如果commit,那么就把这些操作同步到磁盘上并清空历史操作;如果是rollback,则直接清空历史操作;savepoint可以回滚到某个回滚点,而不是全部回滚; 和事务有关的操作:只有insert,delete,update等DML语句需要事务,即需要操作表中数据的语句 事务的四大特征ACID:
A:原子性(Atomicity):事务是最小工作单元,不可再分)
C:一致性(Consistency):事务必须保证多条DML语句要么同时成功,要么同时失败
I:隔离性(Isolation ):同时有多个用户访问数据库,数据库为每个客户开启的事务,不能受其他事务影响,有4 种隔离级别
读未提交(read uncommitted ):对方事务还未提交,我就可以读取;
问题:脏读现象(读到了缓存的数据,很不稳定,毕竟他本次事务还未结束)
读已提交(read committed ):对方提交后的数据,我才可以读取到;解决了脏读
问题:不可重复读现象(在这个事务还未结束时,如果其他人修改表中数据,并不断提交,那我读到的数据永远是最新的,因此每次读到的都不一样)
可重复读(repeatable read ):解决了不可重复读问题(在事务开始时,将需要读取的数据保存到一块空间,因此直到本次业务结束期间,每次读到的数据是一样的,不会受外界影响);是MySQL的默认隔离级别;
问题:幻读现象(我读到的是幻像,假如我开启事务时,保存了一份数据,在事务期间,文件中的数据已经被其他人修改了,因此我重复读到的是假的数据)
序列化读(serializable ):解决了所有问题,类似于加锁,一个事务加锁后,其他事务就无法加锁(分为读锁和写锁)。(效率低,需要事务排队)
D:持久性(Durability):最终数据必须同步到文件中,事务才算结束
MySQL默认一条SQL 语句执行结束,会自动commit ;
start tranction;
insert into 表名( ) values ( ) ;
delete from 表名 where . . . ;
update 表名 set 字段1 = 值1 ,字段2 = 值2 where . . . ;
savepoint 回滚名1 ;
. . .
rollback to 回滚名1 ;
rollback ;
commit ;
索引
作用:相当于一本书的目录,缩小了检索的范围,因此能加快检索速度; 添加索引的条件:
1. 数据量庞大;
2. 该字段很少DML操作(insert delete update),因为字段被修改,索引也相应需要维护
3. 该字段经常出现在where子句中(即经常需要根据该字段做判断)
1. 主键和具有unique约束的字段会自动添加索引(这就是根据主键查询效率高的原因)
1.
create index emp_sal_index on emp ( sal) ;
2. 在创建表时添加索引
create table t_user (
id int primary key,
infomation text,
fulltext key ( infomation)
) ;
drop index emp_sal_index on emp;
索引原理(B+树) select ename from emp where ename = ‘SMITH’; 如果ename字段添加了索引,会被转换为 select ename from emp where 物理地址 = ‘0x3’;
当ename作为where条件时,会判断ename是否添加了索引,如果没有就全表扫描(效率低);
如果添加了(在为ename字段create索引时,会生成一个索引对象,先对表记录排序,构造出一棵B+ 树(跟二叉搜索树同理,左小右大),因此不断缩小区间,最终定位到某一条具体记录(携带有该条记录在表中的物理地址)),我们只要直接访问地址就能找到该条记录;
单一索引:给单一字段添加一个索引
复合索引:多个字段联合起来添加一个索引
主键索引:主键默认添加索引(由于非空且不重复,因此只需要查询一条记录,效率最高!)
唯一索引:unique字段默认添加索引
select ename from emp where ename like '%A%' ;
视图(view)
create view 视图名 as select empno, ename from emp;
1. 创建视图时, as后面只能跟select语句
2. 但创建好视图后,可以用CRUD语句操作视图
drop view 视图名;
1. 对视图(增删改查),会影响原表数据(但不是直接操作原表,而是通过视图影响的)
视图的作用:隐藏实现细节(对一些保密较高的系统,对方只会提供相关视图,我们只能对视图CRUD)
DBA命令(导入导出)
mysqldump bjpowernode> D: \bjpowernode. sql - uroot - plixu1005. .
mysqldump bjpowernode emp> D: \bjpowernode. sql - uroot - plixu1005. .
create database bjpowernode;
use bjpowernode;
source . sql文件所在路径;
三大范式(减少数据冗余)
第一范式:任何一张表都应该有主键,且每一个字段是原子性的不可再分
原子性不可再分(例如联系方式字段中填了手机号和邮箱,我们可以分为手机号字段和邮箱字段)
第二范式:所有非主键字段应完全依赖主键,不能部分依赖
若使用复和主键(字段a和字段b),容易产生部分依赖(有的非主键字段依赖于a,有的依赖于b,会产生数据冗余;若使用单一主键,就不会出现部分依赖)
多对多?三张表,关系表两个外键:例如一个学生上多个老师的课,一个老师带多个学生,即多对多。应创建三张表(学生表,老师表,关系表),其中关系表有两个外键,只用来表示学生表和关系表的关系
第三范式:所有非主键字段直接依赖主键,不能产生传递依赖
一对多?两张表,多的表加外键
实际开发,满足客户需求为主,有时会拿冗余换速度(毕竟分的表越多,表联查会耗费时间)
一对一设计: 1.主键共享(pk + fk) 2.外键唯一(fk + unique)