Ⅰ、DQL-数据查询语言

一、字符函数

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

函数	用法
CHAR_LENGTH(s)	返回字符串s的字符数
LENGTH(s)	返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,…,sn)	连接s1,s2,…,sn为一个字符串
CONCAT_WS(x, s1,s2,…,sn)	同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x
INSERT(str, idx, len, replacestr)	将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str, a, b)	用字符串b替换字符串str中所有出现的字符串a
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(str,n)	返回字符串str最左边的n个字符
RIGHT(str,n)	返回字符串str最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
REPEAT(str, n)	返回str重复n次的结果
STRCMP(s1,s2)	比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len)	返回从字符串s的index位置其len个字符
LOCATE(substr,str)	返回字符串substr在字符串str中首次出现的位置
REVERSE(s)	返回s反转后的字符串
NULLIF(value1,value2)	比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

二、数字函数

函数	用法
ABS(x)	返回x的绝对值
SIGN(X)	返回X的符号。正数返回1，负数返回-1，0返回0
CEIL(x)，CEILING(x)	向上取整
FLOOR(x)	向下取整
LEAST(e1,e2,e3…)	返回列表中的最小值
GREATEST(e1,e2,e3…)	返回列表中的最大值
MOD(x,y)	返回X除以Y后的余数
RAND()	返回0~1的随机值
RAND(x)	返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机
数
ROUND(x)	返回一个对x的值进行四舍五入后，最接近于X的整数
ROUND(x,y)	返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y)	返回数字x截断为y位小数的结果

三、日期和时间函数

1、获取日期、时间

函数	用法
CURDATE() ，CURRENT_DATE()	返回当前日期，只包含年、月、日
CURTIME() ， CURRENT_TIME()	返回当前时间，只包含时、分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	返回当前系统日期和时间
UTC_DATE()	返回UTC（世界标准时间）日期
UTC_TIME()	返回UTC（世界标准时间）时间

2、日期与时间戳的转换

函数	用法
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间
UNIX_TIMESTAMP(date)	将时间date以UNIX时间戳的形式返回
FROM_UNIXTIME(timestamp)	将UNIX时间戳的时间转换为普通格式的时间

3、日期的格式化与解析

函数	用法
DATE_FORMAT(date,fmt)	按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)	按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type)	返回日期字符串的显示格式
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

四、流程控制函数

if 函数

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

方式一：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

方式二：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操作

中图：内连接

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
from
	employees e
left join
	departments d
on
	e.department_id = d.department_id

右上图：右外连接

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

左中图

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

右中图

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

左下图：满外连接（左上图 union all 右中图）
右下图：左中图 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
应用场景：查询结果来自多个表，且多个表没有连接关系，但查询信息一致时。

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

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

一、delete和truncate区别

delete可以加where条件，truncate不能
truncate效率高，不用过滤
delete删除数据后，自增值从断电开始。truncate删除后，自增值从1开始
truncate删除没有返回值，delete删除有返回值
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）

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

二、事务的并发问题

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

三、事务的隔离级别

READ UNCOMMITTED （读未提交）：允许事务读取未被其他事务提交的变更。脏读、不可重复读、幻读都会出现。
READ COMMITTED （读已提交）：只允许事务读取已经被其他事务提交的变更。避免脏读，不可重复读、幻读会出现。
REPEATABLE READ （可重复读）：可以保证同一个事务两次读取同一数据不变，即使其他事务修改此数据。避免脏读、不可重复读，幻读会出现。
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;