SQL

增
insert into table (字段名1,字段2)values(值1，值2);
改
update 表名 set 字段1=值1，字段2=值2【where条件】；
删
delete from 表名[where 条件];
查
select
字段
from
表名
where
条件列表
group by
分组字段列表
having
分组后的条件列表
order by
排序字段列表
limit
分页参数

去除重复记录
select distinct 字段列表 from 表名;
条件查询
select 字段 from 表名 where 条件列表;
>
>=
<
<=
=
!=或<>
between ...and ...
in(...)
like
is null
案例
年龄等于30
select * from employee where age =30;
年龄小于30
select * from employee where age<30;
小于等于
select * from employee where age <=30;
没有身份证
select * from employee where idcard is null or idcard='';
有身份证
select * from employee where idcard is not null;
不等于
select * from employee where age !=30;
年龄在20到30之间
select * from employee where age between 20 and 30;
性别为女且年龄小于30
select * from employee where gender='女' and age<30;
年龄等于25或30或35
select * from employee where age =25 or age=30 or age=35;
select * from employee where age in(25,30,35);
姓名是两个字
select * from employee where name like '__';
身份证最后是X
select * from employee where idcard like '%X';

聚合查询（聚合函数）
常见聚合函数
count统计数量
max最大值
min最小值
avg平均值
sum求和

select 聚合函数(字段) from 表名;

分组查询
select * from employee [where 条件] group by 分组字段名 [having 分组后的过滤条件];
where和having的区别：
执行时机不同：where分组之前过滤，不满足where条件不参与分组，having是分组后对结果进行过滤
判断条件不同：where不能对聚合函数进行判断，having可以

根据性别分组，统计男性和女性数量（只显示分组数量，不显示哪个是男哪个是女）
select count(*) from employee group by gender;
根据性别分组，统计男性和女性数量
select gender ,count(*) from employee group by gender;
根据性别分组，统计男性和女性的平均年龄
select gender, avg(age) from employee group by gender;
年龄小于45，并根据工作地址分组
select address, from employee where age<45 group by address;
年龄小于45，并根据工作地址分组，获取员工数量大于等于3的工作地址
select address from employee where age<45 group by address having address_count>3;
执行顺序：where > 聚合函数 > having
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

排序查询
select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序字段2;
排序方式
ASC：升序
DESC:降序
select * from employee order by age ASC；
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

分页查询
select 字段列表 from 表名 limit 起始索引,查询记录数;

查询第一页数据，展示10条
select * from employee limit 0,10;
查询第二页
select * from employee limit 10,10;
起始索引从0开始，起始索引 = （查询页码 - 1） * 每页显示记录数
分页查询是数据库的方言，不同数据库有不同实现，MySQL是LIMIT
如果查询的是第一页数据，起始索引可以省略，直接简写 LIMIT 10

DQL执行顺序
FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

函数

字符串函数
concat(s1,s2,s3),字符串拼接，将其拼成一个字符串
lower(str),将字符串转为小写
upper(str),将字符串转为大写
lpad(str,n,pad),左填充，用字符串pad 对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad),右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim(str),去除字符串头尾的空格
substring(str,start,len),返回字符串str从start位置起的len个长度的字符串
replace(column,source,replace),替换字符串

-- 拼接
SELECT CONCAT('Hello', 'World');
-- 小写
SELECT LOWER('Hello');
-- 大写
SELECT UPPER('Hello');
-- 左填充
SELECT LPAD('01', 5, '-');
-- 右填充
SELECT RPAD('01', 5, '-');
-- 去除空格
SELECT TRIM(' Hello World ');
-- 切片（起始索引为1）
SELECT SUBSTRING('Hello World', 1, 5);

数值函数
ceil(x)向上取整
floor(x)向下取整
mod(x/y)返回x/y的模
rand()返回0~1的随机数
round(x,y)求参数x的四舍五入值，保留y位小数

日期函数
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date) 获取指定date的年份
MONTH(date) 获取指定date的月份
DAY(date) 获取指定date的日期
DATE_ADD(date, INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1, date2) 返回起始时间date1和结束时间date2之间的天数

流程函数
IF(value, t, f) 如果value为true，则返回t，否则返回f
IFNULL(value1, value2) 如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END 如果val1为true，返回res1，… 否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END 如果expr的值等于val1，返回res1，… 否则返回default默认值

select
name,
(case when age > 30 then '中年' else '青年' end)
from employee;
select
name,
(case workaddress when '北京市' then '一线城市' when '上海市' then '一线城市' else '二线城市' end) as '工作地址'
from employee;

内连接查询
内连接查询的是两张表的交集
隐式内连接
select 字段列表 from 表1，表2 where 条件;
显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

显式性能比隐式性能高

外连接查询
左外连接
查询左表所有数据，以及两张表交集部分数据
select 字段列表 from 表1 left [outer] join 表2 on 条件...;

右外连接：
查询右表所有数据，以及两张表交集部分数据
select 字段列表 from 表1 right [outer] join 表2 on 条件...;

-- 左
select e.*, d.name from employee as e left outer join dept as d on e.dept = d.id;
select d.name, e.* from dept d left outer join emp e on e.dept = d.id; -- 这条语句与下面的语句效果一样
-- 右
select d.name, e.* from employee as e right outer join dept as d on e.dept = d.id;

自连接查询
自连接必须使用表别名
SELECT 字段列表 FROM 表A 别名B JOIN 表A 别名B ON 条件 ...;

自连接查询，可以是内连接查询，也可以是外连接查询

联合查询 union, union all
把多次查询的结果合并，形成一个新的查询集

语法：

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...
注意事项
UNION ALL 会有重复结果，UNION 不会
联合查询比使用or效率高，不会使索引失效

子查询
SQL语句中嵌套SELECT语句，称谓嵌套查询，又称子查询。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);
子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个
根据子查询结果可以分为：

标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）
行子查询（子查询结果为一行）
表子查询（子查询结果为多行多列）
根据子查询位置可分为：

WHERE 之后
FROM 之后
SELECT 之后

标量子查询
子查询返回的结果是单个值（数字、字符串、日期等）。
常用操作符：- < > > >= < <=

例子：

-- 查询销售部所有员工
select id from dept where name = '销售部';
-- 根据销售部部门ID，查询员工信息
select * from employee where dept = 4;
-- 合并（子查询）
select * from employee where dept = (select id from dept where name = '销售部');
-- 查询xxx入职之后的员工信息
select * from employee where entrydate > (select entrydate from employee where name = 'xxx');

列子查询
返回的结果是一列（可以是多行）。

常用操作符：

操作符 描述
IN 在指定的集合范围内，多选一
NOT IN 不在指定的集合范围内
ANY 子查询返回列表中，有任意一个满足即可
SOME 与ANY等同，使用SOME的地方都可以使用ANY
ALL 子查询返回列表的所有值都必须满足
-- 查询销售部和市场部的所有员工信息
select * from employee where dept in (select id from dept where name = '销售部' or name = '市场部');
-- 查询比财务部所有人工资都高的员工信息
select * from employee where salary > all(select salary from employee where dept = (select id from dept where name = '财务部'));
-- 查询比研发部任意一人工资高的员工信息
select * from employee where salary > any (select salary from employee where dept = (select id from dept where name = '研发部'));

行子查询
返回的结果是一行（可以是多列）。
常用操作符：=, <, >, IN, NOT IN

例子：

-- 查询与xxx的薪资及直属领导相同的员工信息
select * from employee where (salary, manager) = (12500, 1);
select * from employee where (salary, manager) = (select salary, manager from employee where name = 'xxx');

返回的结果是多行多列
常用操作符：IN

例子：

-- 查询与xxx1，xxx2的职位和薪资相同的员工
select * from employee where (job, salary) in (select job, salary from employee where name = 'xxx1' or name = 'xxx2');
-- 查询入职日期是2006-01-01之后的员工，及其部门信息
select e.*, d.* from (select * from employee where entrydate > '2006-01-01') as e left join dept as d on e.dept = d.id;

四大特性ACID
原子性(Atomicity)：事务是不可分割的最小操作但愿，要么全部成功，要么全部失败
一致性(Consistency)：事务完成时，必须使所有数据都保持一致状态
隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
并发事务
问题 描述
脏读 一个事务读到另一个事务还没提交的数据
不可重复读 一个事务先后读取同一条记录，但两次读取的数据不同
幻读 一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在

如果索引关联了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

索引失效情况
在索引列上进行运算操作，索引将失效。如：explain select * from tb_user where substring(phone, 10, 2) = '15';
字符串类型字段使用时，不加引号，索引将失效。如：explain select * from tb_user where phone = 17799990015;，此处phone的值没有加引号
模糊查询中，如果仅仅是尾部模糊匹配，索引不会是失效；如果是头部模糊匹配，索引失效。如：explain select * from tb_user where profession like '%工程';，前后都有 % 也会失效。
用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。
如果 MySQL 评估使用索引比全表更慢，则不使用索引。

设计原则
针对于数据量较大，且查询比较频繁的表建立索引
针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询