0%

sql基础2

数据库的表名一般建议使用t_开头

创建表的语法格式

1
2
3
4
5
create table tableName(
columnName dataType(length),
………………..
columnName dataType(length)
);

MySql常用数据类型

类型 描述
Char(长度) 定长字符串,存储空间大小固定,适合作为主键或外键
Varchar(长度) 变长字符串,存储空间等于实际数据空间
double(有效数字位数,小数位) 数值型
Float(有效数字位数,小数位) 数值型
Int( 长度) 整型
bigint(长度) 长整型
Date 日期型 年月日
DateTime 日期型 年月日 时分秒 毫秒
time 日期型 时分秒
BLOB Binary Large OBject(二进制大对象)
CLOB Character Large OBject(字符大对象)
其它…………………

当字符的长度不确定时,应当使用varchar,当长度是确定的时,例如性别,生日等,使用char,同时使用char的范围较小。

1
2
3
4
5
6
7
8
9
#建立学生信息表,字段包括:学号、姓名、性别、出生日期、email、班级标识
create table t_student(
student_id int(10),
student_name varchar(20),
sex char(2),
birthday date,
email varchar(30),
classes_id int(3)
)

操作表的语法格式

添加字段

如:需求发生改变,需要向t_student中加入联系电话字段,字段名称为:contatct_tel 类型为varchar(40)

alter table t_student add contact_tel varchar(40);

修改字段

如:student_name无法满足需求,长度需要更改为100

alter table t_student modify student_name varchar(100) ;

删除字段

如:删除联系电话字段

alter table t_student drop contact_tel;

操作数据的语法格式

插入数据

1
2
3
insert into t_student(student_id, student_name, birthday, email, classes_id) 
values
(1002, 'zhangsan', '1988-01-01', 'qqq@163.com', 10)

修改数据

1
2
3
4
语法格式:
update 表名 set 字段名称1=需要修改的值1, 字段名称2=需要修改的值2 where …….
将job为manager的员工的工资上涨10%
update emp set sal=sal+sal*0.1 where job='MANAGER';

删除数据

1
2
3
4
5
6
语法格式:
Delete from表名 where 。。。。。
删除津贴为500的员工
delete from emp where comm=500;
删除津贴为null的员工
delete from emp where comm is null;

事务

事务的ACID属性

  1. 原子性(Atomicity)
    原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。

  2. 一致性(Consistency)
    事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

  3. 隔离性(Isolation)
    事务的隔离性是指一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。

  4. 持久性(Durability)
    持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来的其他操作和数据库故障不应该对其有任何影响。

数据库的并发问题

  • 对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题:
    • 丢失修改:一个事务的更新操作被另外一个事务的更新操作替换,例如:T1 和 T2 两个事务都对一个数据进行修改,T1 先修改并提交生效,T2 随后修改,T2 的修改覆盖了 T1 的修改。
    • 脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
    • 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。
    • 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如果 T1 再次读取同一个表, 就会多出几行。
  • 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。
  • 一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

四种隔离级别

  • 数据库提供的4种事务隔离级别:

    1555586275271

  • Oracle 支持的 2 种事务隔离级别:READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED

  • Mysql 支持 4 种事务隔离级别。Mysql 默认的事务隔离级别为: **REPEATABLE READ。

封锁

封锁粒度

MySQL 中提供了两种封锁粒度:行级锁以及表级锁。

应该尽量只锁定需要修改的那部分数据,而不是所有的资源。锁定的数据量越少,发生锁争用的可能就越小,系统的并发程度就越高。

但是加锁需要消耗资源,锁的各种操作(包括获取锁、释放锁、以及检查锁状态)都会增加系统开销。因此封锁粒度越小,系统开销就越大。

在选择封锁粒度时,需要在锁开销和并发程度之间做一个权衡。

封锁类型

读写锁

  • 互斥锁(Exclusive),简写为 X 锁,又称写锁。
  • 共享锁(Shared),简写为 S 锁,又称读锁。

有以下两个规定:

  • 一个事务对数据对象 A 加了 X 锁,就可以对 A 进行读取和更新。加锁期间其它事务不能对 A 加任何锁。
  • 一个事务对数据对象 A 加了 S 锁,可以对 A 进行读取操作,但是不能进行更新操作。加锁期间其它事务能对 A 加 S 锁,但是不能加 X 锁。

意向锁

使用意向锁(Intention Locks)可以更容易地支持多粒度封锁。

在存在行级锁和表级锁的情况下,事务 T 想要对表 A 加 X 锁,就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁,那么就需要对表 A 的每一行都检测一次,这是非常耗时的。

意向锁在原来的 X/S 锁之上引入了 IX/IS,IX/IS 都是表锁,用来表示一个事务想要在表中的某个数据行上加 X 锁或 S 锁。有以下两个规定:

  • 一个事务在获得某个数据行对象的 S 锁之前,必须先获得表的 IS 锁或者更强的锁;
  • 一个事务在获得某个数据行对象的 X 锁之前,必须先获得表的 IX 锁。

通过引入意向锁,事务 T 想要对表 A 加 X 锁,只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁,如果加了就表示有其它事务正在使用这个表或者表中某一行的锁,因此事务 T 加 X 锁失败。

解释如下:

  • 任意 IS/IX 锁之间都是兼容的,因为它们只表示想要对表加锁,而不是真正加锁;
  • 这里兼容关系针对的是表级锁,而表级的 IX 锁和行级的 X 锁兼容,两个事务可以对两个数据行加 X 锁。(事务 T1 想要对数据行 R1 加 X 锁,事务 T2 想要对同一个表的数据行 R2 加 X 锁,两个事务都需要对该表加 IX 锁,但是 IX 锁是兼容的,并且 IX 锁与行级的 X 锁也是兼容的,因此两个事务都能加锁成功,对同一个表中的两个数据行做修改。)

封锁协议

三级封锁协议

一级封锁协议

事务 T 要修改数据 A 时必须加 X 锁,直到 T 结束才释放锁。

可以解决丢失修改问题,因为不能同时有两个事务对同一个数据进行修改,那么事务的修改就不会被覆盖。

二级封锁协议

在一级的基础上,要求读取数据 A 时必须加 S 锁,读取完马上释放 S 锁。

可以解决读脏数据问题,因为如果一个事务在对数据 A 进行修改,根据 1 级封锁协议,会加 X 锁,那么就不能再加 S 锁了,也就是不会读入数据。

三级封锁协议

在二级的基础上,要求读取数据 A 时必须加 S 锁,直到事务结束了才能释放 S 锁。

可以解决不可重复读的问题,因为读 A 时,其它事务不能对 A 加 X 锁,从而避免了在读的期间数据发生改变。

两段锁协议

加锁和解锁分为两个阶段进行。

可串行化调度是指,通过并发控制,使得并发执行的事务结果与某个串行执行的事务结果相同。串行执行的事务互不干扰,不会出现并发一致性问题。

事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议,它是可串行化调度。

1
lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)Copy to clipboardErrorCopied

但不是必要条件,例如以下操作不满足两段锁协议,但它还是可串行化调度。

1
lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)

MySQL 的 InnoDB 存储引擎采用两段锁协议,会根据隔离级别在需要的时候自动加锁,并且所有的锁都是在同一时刻被释放,这被称为隐式锁定。

InnoDB 也可以使用特定的语句进行显示锁定:

1
2
SELECT ... LOCK In SHARE MODE;
SELECT ... FOR UPDATE;