数据库基础概念总结

 

一、SQL

1、SQL的分类

SQL可以分为以下四类：

DDL（Data Definition Language）数据定义语言：create、alter、drop、truncate、comment、rename；DDL不需要commit。

DML（Data Manipulation Language）数据操纵语言：insert、delete、update、select、merge、call、explain、plan、lock table；DML需要commit。

DCL（Data Control Language）数据控制语言：授权、角色控制等；grant授权、revoke取消授权。

TCL（Transaction Control Language）事务控制语言：savepoint设置保存点、rollback回滚、set transaction

2、SQL的基本使用

（1）增

往数据表中插入数据的通用语法是：insert into table (field1, field2, ..., fieldN) values (value1, value2, ..., valueN); 或者 insert into table values (value1, value2, ..., valueN);    如果数据是字符型，则必须使用单引号或者双引号，如"value"。

另一种语法是：insert into table select ... from ...，其实就是先查询，再将查到的结果插入到表中（此表已存在）。

第三种语法：select into ...，从一个表中选取数据，然后把数据插入另一个表中（此表通常未存在，即创建一个新表）。此用法常用于创建表的备份或者对记录进行存档。insert into要求目标表必须存在，select into如果不存在目标表则会自动创建。

select * into new_table from old_table将所有列插入新表，select field1, field2 into new_table from old_table将想要的列插入新表。具体用法可以参考：https://www.w3school.com.cn/sql/sql_select_into.asp

如，某出租车公司将驾驶历程（drivedistance）超过5000里的司机信息转移到一张称为seniordrivers的表中，司机的详细情况被记录在drivers表中，则SQL语句如下：

select * into seniordrivers from drivers where drivedistance >= 5000;

（2）删

 

（3）改

 

（4）查

 

3、SQL中的聚合函数

（1）count

（2）avg

 

（5）对null值的处理

count(字段名)：会忽略该列中所有的null值

count(*)：不会忽略null值，本质上计算的是行数

count(1)：不会忽略null值，本质上计算的是行数。select count(*)和select count(1)两者返回的结果是一样的。如果表没有主键的话，那么count(1)会比count(*)快；如果有主键的话，那主键作为count的条件时，count(主键)最快；如果你的表只有一个字段的话，那count(*)最快。关于两者的区别，可以参考：https://www.cnblogs.com/Richardzhu/p/3419690.html

avg函数会忽略null值   所有的统计函数都会忽略null值

给定一个表结构如下：
create table `score` (
    `id` int(11) not null auto_increment,
    `sno` int(11) not null,
    `cno` tinyint(4) not null,
    `score` tinyint(4) default null,
    primary key (`id`)
);

则查询结果一定相等的是()

A. select sum(score) / count(*) from score where cno = 2;
B. select sum(score) / count(id) from score where cno = 2;
C. select sum(score) / count(sno) from score where cno = 2;
D. select sum(score) / count(score) from score where cno = 2;
E. select sum(score) / count(1) from score where cno = 2;
F. select avg(score) from score where cno = 2;

A. 统计所有学生的平均分，就算成绩为空的学生，也将其作为分母基数（count(*)）
B. 与A一样，因为id主键非空，count(id)得到的分母基数是所有学生
C. 与B一样，非空属性sno，count(sno)得到的分母基数是所有学生
D. 由于score字段的值可能为空，count(score)在统计时会忽略空值，因此得到的分母基数可能小于所有学生的个数，也就是无法计算所有学生的平均分
E. 与A一样，因为count(1)与count(*)一样
F. avg()函数会忽略空值，avg(score)计算结果是“有成绩的学生的平均分”，无法计算所有学生的平均分，与D一样

因此，A、B、C、E等价，返回sum(score)除以行数；D、F等价，返回sum(score)除以score不为null的行数

由于id、sno都not null，因此count(*)、count(1)、count(id)、count(sno)本质都是计算所有记录的行数；
由于score为default null，因此count(score)本质是计算score不为null的行数（遇到值为null的行会忽略、跳过）

id sno cno score
1   2   2   80
2   4   2   null
A、B、C、E的结果为 80 / 2， D、F的结果为 80 / 1

 

 

 

 

4、SQL注入

 

 

一、锁

1、锁的概念

数据库是一个多用户使用的共享资源，当多个用户并发存取数据时，数据库中就可能出现多个事务同时存取同一数据的情况。如果对并发操作不加以控制，就可能破坏数据库的一致性（读取和存储不正确的数据）。

锁是实现数据库并发控制的一种机制，其目的也就是控制共享数据的并发访问和修改问题。有了加锁机制，当事务在操作某个数据之前，可以先向系统请求加锁，一旦加锁成功，在该事务释放锁之前，其他事务就不能对此数据进行更新操作。

2、锁的分类

（1）按锁的使用者的看法来划分

可以分为乐观锁、悲观锁

乐观锁：就是很乐观，每次拿数据都认为别人不会修改，不需要上锁。取而代之的是，在更新的时候会判断在此之前别人有没有更新，通过版本号、时间戳等机制实现。乐观锁适用于读操作比较多的场景，可以提高系统吞吐量。

悲观锁：就是悲观地认为修改是很常见的，因此在对数据操作之前需要加锁。

（2）按锁的使用特性划分

可以分为共享锁（S锁）、排它锁（X锁）、更新锁（U锁）

共享锁：也叫读锁，共享锁是非独占的，允许多个并发事务对同一共享资源加锁，从而可以读取其锁定的资源。多个事务可以封锁同一个共享页；任何事务都不能修改该页；通常等该页被读取完毕，S锁立即释放。

#SQL Server默认情况下，读取操作加共享锁，当数据被读取完毕，立即释放共享锁
select * from table;    #首先锁定第一页，读取完毕释放对第一页的锁定，然后锁定第二页，此时允许其他事务修改未被锁定的第一页
select * from table holdlock;    #在整个查询过程中，保持对表的锁定，直到查询完成后才释放锁定

排它锁：也叫写锁，表示对数据执行写操作。当一个事务对数据加了排它锁，其他事务就不能再给它加任何锁了。仅允许一个事务封锁该页； 其他事务必须等到X锁被释放才能对该页进行访问； X锁一直到事务结束才能被释放。

#产生排它锁的SQL语句
select * from table for update;

更新锁：在修改操作的初始化阶段锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。用来预定要对此页加X锁，它允许其他事务读，但不允许再加U锁或X锁； 当被读取的页要被更新时，则升级为X锁； U锁一直到事务结束时才能被释放。

因为当使用共享锁时，修改数据的操作分为两步：
1. 首先获得一个共享锁，读取数据
2. 然后将共享锁升级为排他锁，再执行修改操作
这样如果有两个或多个事务同时对一个事务申请了共享锁，在一些事务需要修改数据时，这些事务要将共享锁升级为排他锁。此时，这些
事务都不会释放共享锁，而是一直等待对方释放，这样就造成了死锁。
如果一个事务在修改数据之前直接申请更新锁，在数据修改时再升级为排他锁，就可以避免死锁。

（3）按锁的粒度划分

可以分为行级锁、页级锁、表级锁等，表示对不同大小的资源进行加锁

SQL Server中锁的粒度包括：行、页、扩展盘区、键、键范围、索引、表、库等资源

MySQL中锁的粒度通常有：行级锁、页级锁、表级锁

锁定较小粒度的资源（比如行），可以增加系统的并发量，但是需要较大的系统开销，因为锁定的粒度较小导致加锁的数量增加；锁定较大粒度的资源（比如表），则并发性能较弱，因为锁定整张表限制了其他事务对表中任意部分进行访问，但是开销较小，因为需要维护的锁较少。总之，并发量和系统开销之间有一种相互制约的关系，需要我们根据场景做出较好的平衡。

 

二、事务

1、事务的概念

事务：访问、更新数据库的一个程序执行单元，它是恢复和并发控制的基本单位。

事务本质上就是满足ACID特性的一组操作，我们可以通过commit提交一个事务（当执行提交操作之后，数据库就从一个一致性状态变成另一个一致性状态），也可以通过rollback进行回滚（当执行回滚操作之后，数据库就恢复到事务开始之前的那个一致性状态，即，不让失败的事务影响数据库的一致性）。如下所示是网购的一组操作。

1. 更改客户所购商品的库存信息
2. 保存客户付款信息
3. 生成订单并且保存到数据库中
4. 更改用户相关信息，例如购物数量等
这些操作就构成一个事务，当某个操作出现了差错，例如更新商品库存信息时出现异常、顾客银行账户余额不足等，都会导致整个交易过程
失败，此时必须进行回滚，也就是保证数据库中所有信息不受“失败的交易”影响，保证数据库的“数据一致性”：原有的库存信息没有被更
新、用户也没有付款、订单也没有生成

2、事务的ACID特性

事务必须满足四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），即ACID四种特性。

原子性：事务被视为不可分割的最小单元（一个整体），事务的所有操作要么全部提交成功，要么全部失败回滚。

一致性：数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。而且一致性还意味着，数据库中的数据是通过一个个“执行成功”的事务的增删改查而来的，是“绝对正确”的。

银行转账，转账前后两个账户金融之和应保持不变

隔离性：一个事务所做的修改在最终提交之前，对其他事务是不可见的。

持久性：一旦事务提交，则其所做的修改将会永远保存到数据库中。即时系统发生奔溃，事务执行的结果也不能丢失。系统发生奔溃可以用重做日志（Redo Log）进行恢复，从而实现持久性。与回滚日志记录数据的修改逻辑不通，重做日志记录的是数据页的物理修改。

四种特性之间的关系：以上四种特性之间不是平级的关系。原子性、隔离性是一致性的保证，一致性主要是确保执行结果正确；持久性主要是用于应对系统奔溃。

1. 只有满足一致性，事务的执行结果才是正确的
2. 在“无并发”的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性
3. 在“并发”的情况下，多个事务并行执行，事务不仅要满足原子性，还要满足隔离性，才能满足一致性
4. 事务满足持久化是为了能应对系统奔溃的情况