SQL新手新手向修炼（2）

由于本人对于plSQL理解有限，如果在文章中出现什么什么漏洞，或者问题欢迎大家指出。

本文就以下几点来进行描述：
(1) 分组计算函数，GROUP BY 语句
(2) 子查询语句
(3) DML语句（即对数据库中的数据进行操作的语句，如增删改）
(4) 事务控制
(5) 数据库的锁

1. 分组计算函数，GROUP BY 语句

1）分组计算函数

* 求和 sum（）* 求平均 avg（）* 求标准差 stddev（）* 求方差 variance（）* 求最大值 max（）* 求最小值 min（）* ------------------------------ * 计数 count（）   1.  count( * ) 返回所有符合条件的数据，不论是否重复或者为空   2.  count（expr）  返回所有符合条件的数据，且数据不为空的   3.  count（distinct expr）返回所有符合条件的数据，且不重复不为空

分组函数的使用：

SELECT sum(salary) , avg(salary) FROM employee WHERE department_id BETWEEN 100 AND 200

注意：
(1) 分组计算函数是建立在已经进行过筛选后数据上的;
(2) MIN , MAX 可用于任何类型，但 AVG,SUM,STDDEV,VARAINCE 仅适用于数值型字段;
(3) count(distinct expr) 返回的的数据不单 不重复，而且还不为 NULL ;
(4) 当分组计算函数遇到空值时,跳过该行,比如说算 AVG 时除数中不包括NULL 的值;
(5) 当使用 avg(nvl(salary,0)) 时求salary不为 NULL 的平均工资时,当salary 为 NULL 时会将 0 值填入,而不会跳过不计入总数 ;

2） GROUP BY函数
基本语法：

select 目标列 from 表名
where 筛选条件 , …
order by 目标排序列 , …
group by 目标分组列 , …

举例：查询部门平均工资高于5000 的部门及平均工资

select department_id  , avg(salary) from employee group by department_idhaving avg(salary) > 5000

注意：
(1) select 后的目标查询列中所包含的列，除了分组函数中的指定列可以不用跟在group by 后，其他的目标查询列必须必须在group by 的目标分组列中 ！！！
(2) 不能在 where 语句中使用分组计算语句，如查询结果需要对分组计算语句进行筛选，则需要将该筛选条件加在 having … 语句中， having … 加在group by 语句结束后

2. 子查询语句

1）个人看法

注意： 子查询中依然能够包括子查询，犹如俄罗斯套娃一般，一层套着一层

子查询十分灵活，简单来说就是你中有我，我有没你，关我什么事，滑稽。
回归正题，子查询的目的是得出一个查询结果，你可以将他看作一个子表或数据，然后我们使用其中的数据进行一系列的操作，以我目前的经验来说，可以使用在以下几点：

select * from （）

即将子查询的查询结果视作一张表，然后从其中select目标数据

select * from table where name = ( )
select * from table where name in ( ) 等等

在这种使用方式中是将子查询当作一个标杆，然后将目标表中的数据与其进行比对

注意：
1. 单行比较必须对应单行子查询（单一结果值的子查询）；
—比如： = ， >
2. 多行比较必须对应多行子查询（多结果值的子查询）；
—比如：IN ， >ANY , >ALL

3. DML语句

1）基础的操作的语句
（1） insert 语句 （插入语句）

• 通过表名 + 列名

  insert into table (列1 ， 列2 ，…) values (值1 ， 值2 ，…)

  注意：
  在你选中的列中，如果存在允许为 null 的列时，对应的值可以不用写在 value 中，但是如果不存在，则绝不允许值为null

• 通过列名

  insert into 列1 values (值1 ， 值2 ，…)

  注意：
  每个值的都需要写明，即使时允许为空的列也必须要显示的将null写出来

• 通过copy另一个表中的数据

  insert into 列1(值1 ， 值2 ，…) select 值1 ， 值2 ，… from table；

• 通过子查询

  insert into （select 值1 ， 值2 ，… from table1 with check option）
  values (值1 ， 值2 ，…)；

  注意：
  with check option 可以检查要插入的值是否符合where 的条件

（2） update 语句 （更新语句）

• 直接插入已知的值

  update 表1 set 列1 = 值1 where 条件

• 通过子查询将符合条件的值插入进去

  update 表1 set
  列1 = （select 目标列1 from tables where 条件），
  列2 = （select 目标列2 from tables where 条件）
  ….
  where 条件

（3） delete语句 （删除语句）

• 删除指定条件的行数据

  delete from tables where 值 = ？；

• 删除表中的所有记录

  delete from tables；

• 截断表

  TRUNCATE TABLE table1;

注意：
(1) 当存在某些约束的时候，比如外键之类的，更新或者删除数据会出现失败
(2) delete删除表是可以回滚的，但是truncate截断表是无法回滚的，慎用

（4）merge 语句（整合语句，即将没有往里添加，有则不做处理）

merge into table1 t1using table2 t2on( t1.id = t2.id )when matched  then update sett1.name = t2.name,t1.age = t2.age ,...when not matched then insert  values(t2.name , t2.age , ....)

4. 事务控制

1）前提声明
关于这篇文章中的事务控制，本人不会介绍过多的概念性的知识点，更多的是本人对事务控制的一些理解，如有问题，欢迎大家指出

2）主要，对没错，想不到小标题了，就是主要
关于事务，主要是对数据库的操作进行的管理，接下来的知识点都是根据Oracle来进行展开的：
（1）三种数据异常

• 脏读
  假设当 A 在进行数据操作时，还未 commit 但是这时 B 来搞事情了，他过来查询并且看到了 A 还未提交的数据，这时 A 将数据回滚，这摆明就像是别人洗澡没洗完，你不但跑过来洗澡，还要偷看别人洗澡，耍流氓，还好别人把衣服穿回去了…这就是脏读

• 不重复读
  假设当 A 正在读取一段数据，可是此时 B 又过来搞事情了，他把 A 看过的数据给改掉了，当 A 回过头来看的时候，发现这段数据跟之前不一样了，这就造成不重复读

• 幻读
  这个跟 不重复读 有一定的类似性， 只不过 幻读 在不重复读的基础上增加了搜索条件，即当 A 通过使用某一搜索条件查询到了一段数据，可是这个 B 真是每次都要来搞事情 ， 他又将 A 读到的数据给修改了，这就导致的当 A 回过头来根据这条条件查询这条信息时，返回了不一样的数据

为了避免这三种数据异常，一般而言通过设置数据库事务隔离级别可以避免，但是如果都避免了， 那数据库的并发性会被降低，所以在编写程序的时候务必要注意这个问题。

数据库的事务隔离级别有4种

隔离级别 脏读 不重复读 幻读 Read uncommitted(读未提交) 是 是 是 Read committed（读已提交） 否 是 是 Repeatable read（可重复读） 否 否 是 Serializable（串行读） 否 否 否

（2）数据的一致性

为了避免数据异常给用户造成的不便，事务控制就显得尤为重要，而控制事务的目的就是为了保证数据的一致性，当用户进程或系统崩溃是，事务控制能够提供更大的灵活性以及操作空间

（数据的一致性的原理涉及到了Oracle的底层设计，等我看懂了，我日后会进行补充）

关于数据的一致性，可能听起来也就几个字，但确实着实重要，以银行体系举例：

• A 卡里有1000 块 ，这个 B 又来了，他卡里有500
• 一日 A 通过电子途径向 B 转了 250 块 ，这100 块要经过银行之手转交给 B
• 正常来说1： A -钱-> 银行 , 2：银行 -钱-> B ，这个过程1 ， 2 要么同时成功，要么同时失败
• 如果不是这般，那么在2执行之前银行当机了，岂不是银行可以把钱吞了，那是不存在的
• 所以由这个小例子可见数据务必保持一致，否则会出现损失

（3）Oracle 中读的一致性

<1> 在任何时候，确保提供统一的视图<2> 一个用户对数据的更改不会影响另一个人对数据的更改<3> “读一致性”确保在同一时刻

• 读数据的人不需要等待些数据的人
• 写数据的人不需要等待读数据的人

简单而言：有人在对数据进行操作的时间段中，在其commit前，不会影响到其他读取数据的人

（4）就数据库而言对于数据大体存在两种操作

*  commit （提交）    * 显示        * 手动提交    *  隐式        *  数据定义语句执行，比如新建一张表        *  数据控制语句执行，比如给用户赋予权限        *  正常退出程序*  rollback （回滚）    * 显示        * 手动设置回滚点，并且手动返回    *  隐式        *  非正常退出程序        *  发生系统错误

正常情况下：

• 数据被更该，但未 commit 前，被更改的记录处于锁定，其他用户无法该更改
• 数据被更该，但未 commit 前，只有当前用户（session）可以看到这种变化，其他用户是看不到的
• 数据被更该，commit 后，被更改的记录解除锁定，其他用户可以更改
• 数据被更该，commit 后，其他用户在再次访问这些书

注意：
<1> 已经 commit 的数据是不允许回滚的，而且 commit 后 rollback point 会被抹去 ；
<2> rollback 必须存在 rollback point ，可以指定目标 rollback point 进行 rollback；
<3> rollback 可以一层一层往回倒退，但是不能前进 。

回滚点的设置
Savepoint a;

删除回滚点
Release savepoint a;

转到指定回滚点
RollBack to a;

全部回滚
Rollback;

5.数据库的锁

1. 对锁的认识
   防止并发事务对相同资源进行更改时，对资源造成破坏，比如当 A 在对数据库一条数据进行操作的同时 那个 B 又来了，他也对这条数据进行了操作，那么此时应该以谁对数据的操作为准？所以在一个人对数据进行操作的时候对这个操作的数据进行上锁就变的很关键了

   如果你在对某张表进行操作时，可以通过代码进行显示的查锁
   如果数据发生死锁，也可以通过代码进行查询

   • 隐式加锁
     当用户对数据进行操作但未提价的之前，数据库会隐式的为这块数据进行加锁
   • 显式加锁

select username from table where id =  10  for update nowait;

2.对锁的分类

• DML lock（data locks，数据锁）：用于保护数据的完整性
• DDL lock（dictionary locks，字典锁）：用于保护数据库对象的结构（例如表、视图、索引的结构定义）
• Internal locks 和latches（内部锁与闩）：保护内部数据库结构
• Distributed locks（分布式锁）：用于OPS（并行服务器）中
• PCM locks（并行高速缓存管理锁）：用于OPS（并行服务器）中