Ⅰ、DQL-数据查询语言

一、字符函数

注意：MySQL中，字符串的位置是从1开始的。

二、数字函数

三、日期和时间函数

1、获取日期、时间

2、日期与时间戳的转换

3、日期的格式化与解析

四、流程控制函数

1. if 函数

select if(10<5,'大','小');//结果：小

2. 方式一：case 函数
   case 要判断的字段或表达式
   when 常量1 then 要显示的值1或语句1；
   when 常量2 then 要显示的值2或语句2；
   …
   else 要显示的值n或者语句n;
   end

select salary 原始工资，department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees

3. 方式二：case 函数
   case
   when 条件1 then 要显示的值1或者语句1
   when 条件2 then 要显示的值2或者语句2
   …
   else 要显示的值n或语句n
   end

select salary,
case
where salary>2000 then 'A'
where salary>5000 then 'B'
where salary>15000 then 'C'
else 'D'
end as 工资级别
from employees

五、多表连接

1、92语法

• MySql只支持内连接

# 查询员工的id和部门id（内连接，两表都可以匹配上）
select
	e.employee_id ,
	d.department_name
from
	employees e ,
	departments d
where
	e.department_id = d.department_id

• MySql不支持外连接，Oracle支持

# 查询所有员工的id和部门id（左外连接）
select
	e.employee_id ,
	d.department_name
from
	employees e ,
	departments d
where
	e.department_id = d.department_id(+)

# 查询所有员工的id和部门id（右外连接）
select
	e.employee_id ,
	d.department_name
from
	employees e ,
	departments d
where
	e.department_id(+) = d.department_id

2、99语法

• 内连接（inner 可省略）

select
	e.employee_id ,
	d.department_name
from
	employees e
inner join
	departments d
on
	e.department_id = d.department_id

# 多个表内连接
select
	e.employee_id ,
	d.department_name,
	l.city
from
	employees e
inner join
	departments d
on
	e.department_id = d.department_id
inner join locations l 
	on
	d.location_id = l.location_id

# 多个表内连接（最后添加连接条件）
select
	e.employee_id ,
	d.department_name,
	l.city
from
	employees e
inner join
	departments d
inner join locations l 
	on
	e.department_id = d.department_id
	and
	d.location_id = l.location_id

• 左外连接（outer可省略）

select
	e.employee_id ,
	d.department_name
from
	employees e
left outer join
	departments d
on
	e.department_id = d.department_id

• 右外连接（outer可省略）

select
	e.employee_id ,
	d.department_name
from
	employees e
right outer join
	departments d
on
	e.department_id = d.department_id

• 全外连接（outer可省略），MySql不支持全外连接，Oracle支持

select
	e.employee_id ,
	d.department_name
from
	employees e
full outer join
	departments d
on
	e.department_id = d.department_id

3、7种join操作

1. 中图：内连接

select
	e.employee_id ,
	d.department_name
from
	employees e
inner join
	departments d
on
	e.department_id = d.department_id

2. 左上图：左外连接

select
	e.employee_id ,
	d.department_name
from
	employees e
left join
	departments d
on
	e.department_id = d.department_id

3. 右上图：右外连接

select
	e.employee_id ,
	d.department_name
from
	employees e
right join
	departments d
on
	e.department_id = d.department_id

4. 左中图

select
	e.employee_id ,
	d.department_name
from
	employees e
left join
	departments d
on
	e.department_id = d.department_id
where
	d.department_id is null

5. 右中图

select
	e.employee_id ,
	d.department_name
from
	employees e
right join
	departments d
on
	e.department_id = d.department_id
where
	e.employee_id is null

6. 左下图：满外连接（左上图 union all 右中图）

7. 右下图：左中图 union all 右中图

4、SQL99语法新特性

• 自然连接

标准写法：

select employee_id,last_name,department_name
from employees e join departments d 
on e.department_id = d.department_id
and e.manager_id = d.manager_id

新特性写法：

select employee_id,last_name,department_name
from employees e 
natural join departments d;

• USING连接

标准写法：

select employee_id,last_name,department_name
from employees e join departments d 
on e.department_id = d.department_id

新特性写法：

select employee_id,last_name,department_name
from employees e join departments d 
USING (department_id)

六、分页查询

①select 字段
②from 表1
③join 表2
④on 连接条件
⑤where 筛选条件
⑥group by 分组字段
⑦having 分组后筛选条件
⑧order by 排序字段
⑨limit offset，size
执行顺序：② ③ ④ ⑤ ⑥ ⑦ ① ⑧ ⑨

• offset 起始索引从0开始
• size 显示的条目个数

 select * from employees limit 0,5;
 //默认从0开始
 select * from employees limit 5;

七、联合查询

union
应用场景：查询结果来自多个表，且多个表没有连接关系，但查询信息一致时。

1. 要求多条查询语句的查询列数是一致的
2. 要求多条查询语句每一列的类型和顺序最好一致
3. union关系字默认去重，如果使用union all 可以包含重复项

Ⅱ、DML-数据操纵语言（增删改）

一、delete和truncate区别

1. delete可以加where条件，truncate不能
2. truncate效率高，不用过滤
3. delete删除数据后，自增值从断电开始。truncate删除后，自增值从1开始
4. truncate删除没有返回值，delete删除有返回值
5. truncate删除不能回滚，delete删除可以回滚

Ⅲ、DDL-数据定义语言（视图索引等）

一、库的管理

• 创建库books

create database books;
//books库不存在创建，存在则不创建（不报错）
create database if not exists books;

• 修改库books

//修改字符集
alter database books character set utf-8;

无修改库名命令

• 删除库books

drop database books;
//books库存在删除，不存在不删除（不报错）
drop database if exists books;

二、表的管理

• 表的创建

create table book(
	id int,
	name varchar(20)
)
//查看表结构：
desc book;

• 表的修改

①修改列名

alter table book change column publish_date pub_date datetime;

②修改列的类型或约束

alter table book modify column pub_date timestamp;

③添加新列

alter table book add column annual double;

④删除列

alter table book drop column annual;

⑤修改表名

alter table book rename to book_new;

column 可以省略

• 表的删除

drop table book_new;
//查询所有库
show tables;

• 表的复制

①仅复制表结构

create table boos2 like book;

②复制表结构和内容

create table book3 select * from book;

三、视图的管理

• 视图创建

create view 视图名
as
查询语句;

• 视图修改

alter view 视图名
as
查询语句;

• 视图删除

drop view 视图名1,视图名2,...;

• 视图查看

dec 视图名;

Ⅳ、DCL-数据控制语言（事务）

• DDL语言没有事务，事务只存在于增删改查。

一、事务的特点（ACID）

1. 原子性：一个事务不可再分割，要么都成功要么都失败。
2. 一致性：一个事务执行使数据从一个一致性状态变换为另一个一致性状态。（可以理解为能量守恒）
3. 隔离性：一个事务的执行不受其他事务的干扰。
4. 持久性：一个事务一旦提交，则永久的改变数据状态。

二、事务的并发问题

• 脏读：一个事务读到另一个事务修改未提交的数据。
• 不可重复读：一个事务两次读取同一数据，在两次读取之间，另外一个事务将此数据修改或删除，则同一事务，两次读取结果不同。
• 幻读
  ①幻读是在解决了不可重复读即在可重复读基础上发生的。
  ②A事务通过检索条件查询一组数据，B事务新增或删除此检索范围内数据并提交。这时候重点来了：A事务通过相同的检索条件查询不会显示（因为可重复读，如果显示就是不可重复读了），但实际已经查询到（只是不显示），若此时根据检索条件delete，会将B事务新增的数据删除

三、事务的隔离级别

1. READ UNCOMMITTED （读未提交）：允许事务读取未被其他事务提交的变更。脏读、不可重复读、幻读都会出现。
2. READ COMMITTED （读已提交）：只允许事务读取已经被其他事务提交的变更。避免脏读，不可重复读、幻读会出现。
3. REPEATABLE READ （可重复读）：可以保证同一个事务两次读取同一数据不变，即使其他事务修改此数据。避免脏读、不可重复读，幻读会出现。
4. SERIALIZABLE （串行化）：一个事务读取数据，禁止其他事务插入、修改、删除操作。最高级别，所有问题都能避免，但效率特别低。

四、默认隔离级别

• oracle 支持2种事务隔离级别：读已提交、串行化。默认读已提交。
• mysql 支持4种事务隔离级别，默认可重复读。

五、隔离级别演示

//1.显示是否自动提交  on-自动提交 off-手动提交
show variables like 'autocommit';
//2.设置手动提交
set autocommit = 0;
//3.设置自动提交
set autocommit = 1;
//4.开启事务，搭配2事务，可省略
start transaction;
//5.查看事务隔离级别
select @@transaction_isolation;
//6.设置当前会话隔离级别
set session transaction isolation level read committed;
//7.查看正在执行的事务
SELECT * FROM information_schema.INNODB_TRX;