SQL

查询

select

一般查询

select 字段名1,字段名2, .... from TABLE;
select 字段名1 * 12,字段名2 + 1,....(表达式) from TABLE; // 表达式

select SAL*12 from emp;

select 字段名1 as 别名,字段名2 as 别名2,字段名3... from TABLE;

select ENAME,SAL*12 as yearSAL from emp;

// Q: 别名中有中文怎么办
select SAL*12 as 年薪 from emp; // 错误
select SAL*12 as '年薪' from emp; // 正确

注意：字符串必须要用 '' 括起来, 有的数据库 '', "" 通用，但是有的只能使用 '', 为了防止出现跨数据库管理工具的不兼容，建议统一使用 ''。

select SAL*12 yearSAL from emp; // as 关键字可以省略
select * from TABLE; // 查询某个表中的所有数据，实际开发中不建议使用 *，效率不高

条件查询

单个条件

//  select 字段名1,字段名2, .... from 表名 where 条件;
select ENAME from emp where SAL >= 2000;

运算符

> >= (大于, 大于等于)
< <= (小于, 小于等于)
= (等于)
!= 或 <> (不等于)

多个条件 (and / between)

// select 字段名1, 字段名2, .... from 表名 whrer 条件 and 条件 and 条件 ...;
select ENAME from emp where SAL >= 1000 and SAL < => 3000;

// between and 指定查询的范围
select ENAME from emp where SAL between 1100 and 3000;

// 例: 找出工作是 'MANAGER' 且工资大于1000的员工
select ENAME,JOB,SAL from EMP where JOB = 'MANAGER' and SAL > 1000;

注意：
between and 条件包含 1100，3000
数值必须是左边小，右边大
between and 条件除了可以使用在数字上之外还可以使用在字符上

is null / is not null(null 和非null 判断)
select 字段名1,字段名2 ... from 表名 where 字段名 (is null / is not null)

// 例: 找出哪些人津贴为 null。
select ENAME,COMM from EMP where COMM is null;

// 例: 找出那些人的津贴不为 null
select ENAME,COMM from EMP where COMM is not null;

在数据库中 null 不是一个值，是表示当前这个字段位置没有值.

or
select 字段名1,字段名2 ... from 表名 where 条件 or 条件 ....

// 例: 找出哪些人没有津贴(COMM)
// 分析: 就是津贴为 null 或 津贴为 0 的所有人
select ENAME, COMM from EMP where COMM is null or COMM = 0;

// 例: 找出工作是 'MANAGER' 或 'SALESMAN' 的员工
select ENAME,JOB from EMP where JOB = 'MANAGER' or JOS = 'SALEMAN';

// 例: 找出薪资大于 1000 的并且部门编号是 20 或者 30 的员工
select ename, sal, deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30);

注意: 当运算符的优先级不确定的时候一定要记得加 ();

in (等同于 or)
select 字段名1, 字段名2... from 表名 where 字段名 in ('MANAGER', 'SALEMAN')
等同于
select 字段名1, 字段名2... from 表名 where 字段名 = 'MANAGER' or 字段名 = 'SALEMA'

注意: in (1000, 5000)意思是找出值为 1000 和 5000 的，不是找出 1000 到 5000 的。

not in: 不在这几个值当中
not: 取反 (not in 其实就是两个运算符 not 和 in)
like: 模糊查询
select 字段名1, 字段名2... from 表名 where 字段名 in ('MANAGER', 'SALEMAN')

两个特殊的符号

% 代表任意多个字符(包括 0)
_ 代表任意的一个字符(不包括 0)

// 例: 找出名字中含有 o 的
select ENAME from emp where ENAME like '%o%';

// 例: 找出第二个字母是 a 的
select ENAME from emp where ENAME like '_a%';

//  例: 找出名字中有下划线的(_), 此时需要转义
select ENAME from emp where ENAME like '%\_%'

注意: 如果需要匹配 _ % , 可以对这个字符进行转义

子查询

select 语句当中嵌套 select 语句，被嵌套的语句就是子查询

// 例: 找出工资高于平均工资的员工

// 1. 先计算出水平工资
select avg(sal) from emp;
// 2. 找出工资比平均工资高的员工
select avg(sal) as avgsal, sal, ename from emp where sal > (select avg(sal) from emp);

// 例：找出每个部门的平均工资的薪资等级

// 1. 找出每个部门的平均工资 (按照部门编号分组) 记为表 t1
select avg(sal) avgsal, deptno from emp group by deptno; // t1
/**
+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 2175.000000 |     20 |
| 1566.666667 |     30 |
| 2916.666667 |     10 |
+-------------+--------+
**/

// 2. 连接查询找出每个部门的工资等级
select
  t1.deptno, s.grade, t1.avgsal
from
  (select avg(sal) avgsal, deptno from emp group by deptno) t1
left join
  salgrade s
on
  t1.avgsal between s.losal and hisal;

增删改

增
insert into

处理函数

单行处理函数

ifnull(字段名, 为 NULL 时的替换值)
ifnull 函数会检测该字段名我是否为 NULL，为 NULL 的时候会将 NULL 替换为对应的值

/**
   t_emp
   enam     sal       comm
   Li       9000      1000
   Wang     10000     NULL
   
   计算以上的员工表每个员工的平均工资:(月工资sal + 每月补贴comm) * 12
**/

// 1. 直接计算
select enam, (sal + comm)*12 as yearSal from t_emp;
/**
  结果:
  enam      yearSal
  Li        120000
  Wang      NULL
  
  因为含有 NULL 和任何的计算结果都为 NULL
**/

// 2. 正确的计算方式
select ename, (sal + ifnull(comm, 0))*12 as yearSal from t_emp;

其他操作

insert into 表名 (字段名1, 字段名2, ...) values (值1, 值2, ...) // 按指定的字段顺序插入
insert into 表名 values (值1, 值2, ...) // 按默认的字段顺序插入
insert into 表名 values (...), (...), ...; // 一次增加多条记录

更新

update 表名 set 字段名1=值1, 字段名2=值2, ... where 条件
- update 一般符合 where 语句条件的所有表数据更新
- update 语句中如果 where 没有匹配到任何的数据就不会更新也不会报错
- update 语句之后没有跟 where 语句将会更新整个表 (开发中一定要谨慎使用)

delete from 表名 where 条件
- delete 一般符合 where 语句条件的所有表数据更新
- delete 语句中如果 where 没有匹配到任何的数据就不会更新也不会报错
- delete 语句之后没有跟 where 语句将会更新整个表 (开发中一定要谨慎使用)
- delete 会产生数据库操作记录，即使删除之后还可以回滚数据
truncate table 表名
- truncate 操作不能回滚,删除之后表的数据将完全丢失
- truncate 不会产生记录，一旦删除该表将不可找回
drop table
- 删除表,可以回滚不用担心数据丢失
- 当表不存在时候将会报错
drop table if exists 表名
- 如果表存在则删除表,不存在则不进行任何的操作

替换

replace into table 表名(字段1, 字段2, 字段3, ...) values(值1, 值2, 值3, ...)

查询的结果相加 union

// 找出工作岗位是 'SALEMAN' 和 'MANAGER' 的员工

// 写法1
select ENAME,JOB from emp where JOB='SALEMAN' or JOB='MANAGER'

// 写法2
select ENAME,JOB from emp where JOB in('SALEMAN', 'MANAGER')

// 写法3
select ENAME,JOB from emp where JOB='SALEMAN'
union
select ENAME,JOB from emp where JOB='MANAGER'

分页查询和指定固定的条数 limit

select ... limit startIndex, length;
select ... limit length;

// 找出所有的人的工资按照降序排序后取出前5条记录
select * from emp order by sal desc limit 5;

// 例：找出工资在第四位到第九位的员工工资
select * from emp order by sal desc limit 3, 6;

多行处理函数

sum() 求和
avg() 求平均值
max() 求最大值
min() 求最小值
count() 求总条数

// 求工资总和
select sum(sal) from emp;
// 求工资平均值
select avg(sal) frim emp;
// 求最高工资
select max(sal) from emp;
// 求最低工资
select min(sal) from emp;
// 求员工总数
select count(*) from emp;
// 求员工的补贴情况由集中
select count(comm) from emp;

分组

group by
按照某个字段或某些条件分组

分组函数永远都会在 group by 执行完之后执行
分组函数一般都会喝 group by 联合使用，这也是较为分组函数的原因
当一条 sql 语句没有 group by 的话，整张表的数据都看成是一组
group by 一定是在 where 执行完之后执行
分组函数一定是在 group by 执行完之后执行
当一条语句中有 group by 的话，select 后的字段名只能跟分组函数和 group by 分组的字段。其他的不能跟，否则报错。

having
对分组之后进行再过滤

// 例: 找出工资高于平均工资的员工

// 1. 方案一:
select ENAME,SAL from emp where SAL > (select avg(SAL) from emp); // 子查询 (select 语句中嵌套 select 语句)

// 例: 找出每个工作岗位的最高薪资
select max(SAL), job from emp group by JOB;

 // 例: 找出每个岗位的平均工资
select avg(SAL), JOB from emp group by JOB;

// 例: 找出每个部门不同工作岗位的最高薪资
select DEPTNO, JOB, SAL from emp group by DEPTNO, JOB;

// 例: 找出每个部门的最高薪资，要求是薪资大于 2900 的数据。
// 使用 where 提前过滤掉小于 2900 的数据，再分组查最高的数据 (推荐)
select max(SAL) from emp where SAL > 2900 group by deptno
// 先分组找最高的数据，然后使用 having 二次分组过滤掉了小于 2900 的数据
select max(SAL) from emp group by DEPTNO having max(SAL) > 2900

连接查询

在实际的开发过程中，大部分的情况下都不是从单独的一张表中去查询数据，一般都是多张表联合查询数据
在实际的开发中，一般一个业务都会对应多张表。
这也是关系型数据䣌库的特点。

连接查询的分类

根据语法来划分

SQL92 (一些比较老的 DBA (DataBase Administrator)还会使用这种语法)
SQL99 (比较新的有语法)

根据表的连接方式来划分

内连接
- 等值连接
- 非等值连接
- 自连接
外连接
- 右外连接
- 左外连接
全连接

笛卡尔积现象

在表的连接查询中，有一种现象被称为笛卡尔积现象。当多个表联合查询的时候且没有限定条件的时候，查询出来的结果条数是每个表的记录数的乘积

如何避免笛卡尔积现象

对 sql 语句加入选择的条件（where）进行过滤

案例: 找出每个员工的部门名称，要求显示员工名和部门表

员工的姓名和部门编号

+--------+--------+  
| ENAME  | DEPTNO |  
+--------+--------+  
| SMITH  |     20 |  
| ALLEN  |     30 |  
| WARD   |     30 |  
| JONES  |     20 |  
| MARTIN |     30 |  
| BLAKE  |     30 |  
| CLARK  |     10 |  
| SCOTT  |     20 |  
| KING   |     10 |  
| TURNER |     30 |  
| ADAMS  |     20 |  
| FORD   |     20 |  
| MILLER |     10 |  
+--------+--------+  

部门名称
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

想想以下的 sql 语句，会输出多少条结果？ select ENAME,DNAME from emp,dept; // 4 * 13 = 52

答: emp 表中有 14 条记录，dept 表中有 4 条记录。当没有限定条件的时候（where），从 emp 中取出的一条记录可以跟 dept 中任意一条相匹配，所以结果就是 14 * 4 = 56 条。

笛卡尔乘积现象

关于 table 的别名

例：select e.ENAME, d.DNAME from emp e, dept d;

table 的别名写在表名的后面；
下执行效率高
可读性好

多表连接查询

select 字段名1, 字段名2, ... from 表1 别名 join 表2 别名 on 连接条件 join 表3 别名 on 连接条件

例: 找出每个员工的部门名称和薪资等级。
select 
  e.ename, d.dname, s.grade
from
  emp e
join
  dept d
on
  e.deptno = d.deptno
join
  salgrade s
on
  e.sal between s.losal and s.hisal;

例2: 找出每个员工的部门名称，工资等级，以及上级领导
select 
  e.ename, d.dname, s.grade
from
  emp e
join
  dept d
on
  e.deptno = d.deptno
join
  salgrade s
on
  e.sal between s.losal and s.hisal;
left join
  emp e1
on
  e.mgr = e1.empno;

内连接和外连接

内连接:
假设表A和表B进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。AB两张表没有主副之分。两张表是平等的。

外连接:
假设 A 和 B 进行连接，使用外连接的话。AB两张表中有一张是主表，一张是副表。主要是查询主表中数据，捎带副表的。当副表中的数据没有和主表的数据匹配上，副表自动模拟出 NULL 与之匹配。

外连接分为

左外连接
右外连接

左外连接

就是表示左边的表是主表的外连接

select
字段名1, 字段名2, ...
from
表名表别名
left outer join // outer 可以省略不写
表名表别名
on
连接条件

右外连接

就是表示右边是主表的外连接

select
字段名1, 字段名2, ...
from
表名表别名
right outer join // outer 可以省略不写
表名表别名 on 连接条件

排序

order by (默认升序)
select 字段名1,字段名2 .... from 表名 order by 字段名; // 默认升序

select 字段名1,字段名2 .... from 表名 order by 字段名 (ase/desc);

asc 升序
desc 降序

多个排序的条件使用 , 隔开，只有当前面的条件相等的时候才回去启用下一个条件
select 字段名1,字段名2 .... from 表名 order by 字段名1 (asc/desc), 字段名2 (asc/desc), ...

通过数字指定根据第几个字段去排序，(不推荐使用)
select ENAME,SAL,COMM from emp order by 2; // 根据第二个字段(SAL)去排序
select * from emp order by 6; // 根据第6个字段去排序

// 例: 找出所有员工名字并且按照工资升序排序  
lect ENAME,SAL from emp order by SAL;  

// 例: 按照工资的降序排列，当工资一样的情况下再按照名字的升序排序  
select ENAME,SAL from emp order by SAL desc, ENAME asc;  

// 例: 找出工作岗位是 SALEMAN 的员工，并且按照薪资的降序排序 (结合 where)  
select ENAME,SAL,JOB from emp where JOB = 'SALESAN' order by SAL;

去重

distinct select distinct 字段名1, 字段名2, ... from 表名;

distinct 只能出现在所有字段名的最前面
select JOB, distinct from emp; // 错误，distinct 会被当成是一个字段名
当使用 distinct 的时候，选择多个字段名的时候，是对选出的结果联合去重
select distinct DEPTNO, JOB from emp;

事务管理

一个事务是一个完整的业务逻辑单元，不可再划分。
在数据库中，有时候要保证一组 sql 操作语句同时成功或者同时失败。否则会出现数据不一致的问题
例：银行业务中 A 账户向 B 账户转账1000块
update set t_atc balance = balance - 1000 where atcno = '101';
update set t_atc balance = balance + 1000 where atcno = '102';

上面这两条 sql 语句必须同时成功或者同时失败，不允许存在一条成功一条失败的情况。

只有 DML (insert, update, delete) 语句才有事务机制，

事务的机制

假设一个业务需要执行一条 update，一条 insert，一条 delete。事务机制怎么保证这个业务执行完整

开始事务机制
执行 update 操作，记录 sql 操作历史记录，但是不会修改数据库文件的数据，也不会改变硬盘上的数据
执行 insert 语句，同上
执行 delete 语句，同上
提交或者回滚事务，事务机制结束。
- 提交事务的时候，就会将事务的操作持久化到硬盘中去，并且清除 sql 的历史操作记录
- 回滚事务的时候，只会删除 sql 的历史操作记录。

提交事务 (commit)

设置保存点：savepoint。如果没有设置保存点的话回滚会直接将整个事务回滚，设置了保存点的时候可以选择回到保存点，而不是完全放弃这个事务。

回滚事务 (rollback)

事务的四个特性 (ACID)

原子性：事务时最小的工作单元，不可再分
一致性：事务必须保证多条 DML 语句同时成功或者同时失败
隔离性：事务 A 和事务 B 之间具有隔离
持久性：最终的数据结果必须持久化到硬盘文件中，事务才算是结束

关于事务之间的隔离性

事务的隔离性存在隔离的级别。理论上包括4个级别

第一级别：读未提交 (read uncommitted) 对方的事务还没有提交，我们的事务可以读取到对方未提交的数据。

问题：读未提交存在脏读（Dirty Read）现象，表示读到了脏数据
第二级别：读已提交 (read commited) 对方事务提交之后的数据，我方可以读取到。

问题：不可重复读。

理解：对方事务一直再提交，我方事务未结束但是一直在读取数据。某次读取到两条，下一个读到3条，因为在此之前对方事务有提交了一条数据
第三级别：可重复读 (repeatable read) 这种隔离级别解决了不可重复读

问题：读到的数据都是幻象，不是全部的数据

理解：对方事务一直再提交。我方事务一直未结束但是一直在读取数据，即使对方事务一直在提交数据，但是我方事务每次读取的数据都是一样的。直到我方事务结束了再次开启了下一个事务去读，才会得到全部的数据
第四级别：序列化读/串行化读解决了所有的问题，但是效率低，需要事务排队。
- oracle默认：读已提交
- mysql：可重复读

试图 (view)

试图可以隐藏数据库表的实现细节，保密级别比较高的数据库可能不会对外提供真实的表结构，而是提供一个视图
试图并不会提高检索的效率，因为使用视图对表的操作最终还是会回到表上。
使用视图就是从不同的角度看待数据

创建视图 create view 视图名字 as select 字段1, 字段2，... from 表名
- 视图创建语句后的 as 只能跟 select 语句
删除视图 drop view 视图名称;
对视图的操作会修改到原表的数据

面向视图操作

// 创建表
create table emp_bck as select * from emp;

// 创建视图
/*
 该视图提供了对 emp 表的 EMPNO, ENAME, SAL 的操作
*/
create view myview1 as select EMPNO, ENAME, SAL from emp;

// 使用视图操作数据

// 查询
select * from myview1

// 修改数据
update myview1 set ename='hehe', sal=1 where empno=7653

// 删除数据
delete myview1 where empno=7653;

索引

索引相当是一本书的目录，通过目录可以快速的找到对应的资源（数据）。在一张表中执行查找数据的过程中，默认会从头开始查找，当数据量非常的庞大的时候效率很低。
给某些字段加上索引的时候，sql 语句可以根据索引快速定位。不需要从头开始查找，提升效率

怎么创建索引，怎么删除索引
- 添加索引 create index 索引名称 on 表名(字段名)
- 删除索引对象 drop index 索引名称 on 表名;
什么时候可以考虑给字段添加索引
- 数据量庞大
- 该字段很少的DML操作
- 该字段经常出现在 where 子句中（经常根据哪个字段查询）
主键和具有 unique 约束的字段会自动添加索引
索引根据主键查询效率较高，尽量根据主键检索
查看 sql 语句的检索计划
explain select ename, sal form emp where sql=500;
索引的原理解析
假设对表 emp 上的 ename 建立索引
create index emp_index_ename on emp(ename);
1. 创建 emp_index_ename 对象，然后将 ename 字段名中的所有可能值按顺序分组。形成一个一个的分区 (B Tree 算法) 并且记录每个值得物理地址。（空间换取时间）例如：
  A区：ADAMS, ALLEN, ...
  B区：Black, BKAI, ...
  ...
  S区：SMITH, ...
2. 执行查找 select ename from emp where ename='SMITH'，就会根据 'SMITH' 所属于的分区直接区 S 区中查找，找到 ename 'SMITH' 的物理地址（假设是00x1）
3. 有了物理地址之后，select 语句将会有原来的语句转变成
  select ename from emp where 物理地址='00x1'
  就会直接从该物理地址中取出来对应的数据，完成查找
注：分区的算法使用的是 B Tree 算法。缩小查找的范围，然后获取到具体的物理地址，最后通过地址去直接取出具体的数据

约束 (Constraint)

为了保证数据得合法性，完整性，有效性。可以给字段加一些约束。

约束分类

非空约束 not null：字段不能为空
唯一约束 unique：约束得字段名不能重复
主键约束 primary key：约束得字段既不能为 NULL，也不能重复。(简称PK)
外键约束 foreign key：(简称为FK)
检查约束 check：注意 Oracle 数据库有 check 约束，但是 MySQL 目前不支持。

如何添加约束

在建表语句的时候，写在字段名的最后

例：
drop table if exists t_user;
create table t_user(
id int not null, // 在字段名的最后添加非空约束(not null)
....
)