1. 基础概念

1. DQL：select
   查询
2. DML：insert、delete、update
   增删改
3. DDL：create、drop、alter、truncate
   表的操作
4. TCL：commit、rollback
   事务操作

2. 数据类型

整数类型：
int：正常情况数字就用它
bigint：处理特别巨大的数字，例如双十一交易量
tinyint：一般对应布尔值true和false

浮点数类型：
float：单精度浮点，占用4字节
double：双精度浮点，占用8字节
浮点数可能会有精度丢失的问题

字符串类型：
varchar：可变长度，效率较低
char：固定长度，效率较高

varchar和char的区别：二者最大长度都是255，varchar可变长度，可以节省空间，但是动态分配空间速度可能很慢。二者创建时必须手动定义长度。
怎么选：短字符串、定长字符串（如UUID）、频繁改变的字符串，用char好一些；在InnoDB引擎中，内部的行存储格式并没有区分固定长度和可变长度列，而且主要影响性能的因素是数据行的存储总量，由于varchar的占用空间较小，所以除了前面说的三种，其余情况用varchar会好一些。

日期与时间类型：
date
datetime

date和datetime区别：date是短日期，只有年月日；datetime是长日期，有年月日时分秒。MySQL用now()函数获取当前时间，是datetime类型的。

3. 函数

3.1 单行处理函数

对具体某一字段的某一个数据进行数学运算、字符串转换等。
单行处理函数的数量很多，可以自行上网搜索。

3.2 多行处理函数

count() sum() avg() max() min()
多行处理函数又叫分组函数，必须先进行分组才能用
如果没有分组，整张表默认为一组

由sql关键字的执行顺序：
from…join…on…where…group by…having…select…order by…limit…
得出：on和where后面不能使用多行处理函数，having和select后面可以。

3.3 其余常用函数

3.3.1 group_concat(distinct xxx separator x)

将分组中括号里字段进行字符串连接，如果分组中括号里的参数xxx有多行，每个字符串之间以特定符号进行分隔（如果不指定，默认以逗号分隔），一般搭配group by使用。

比如这样一张表user：

我们想拿到每个部门的成员信息，去重，以分号间隔。

sql语句

select dept_name, group_concat(distinct real_name separator ',') as real_name
from user group by dept_name;

得到的结果

3.3.2 find_in_set(str,strlist)

str为要查询的字符串，strlist为字段名，其参数以逗号分隔。返回str在strlist中的位置，以1开始。如果str不在strlist 或strlist 为空字符串，则返回值为0。

find_in_set和in的区别：
我们以上面查询得到的结果表为例，如果我们想找real_name中有老八的数据，我们用in就无法做到。

select dept_name, real_name
from (
select dept_name, group_concat(distinct real_name separator ',') as real_name
from user
group by dept_name
) a
where '老八' in real_name;

报错了，in的正确语法应该是：

select dept_name, real_name
from (
select dept_name, group_concat(distinct real_name separator ',') as real_name
from user
group by dept_name
) a
where '老八' in ('老八', '刘勇', '王境泽');

也就是说，in后面跟着的必须得是常量，写死的。

我们用find_in_set即可达到要求：

select dept_name, real_name
from (
select dept_name, group_concat(distinct real_name separator ',') as real_name
from user
group by dept_name
) a
where find_in_set('老八', real_name) != 0;

拿到了有老八的那行数据。
所以，如果strlist是常量，我们使用in是没问题的，如果是变量，我们使用find_in_set。

find_in_set和like的区别：
find_in_set是精确匹配，也就是说要老八就只要老八。而like，如果有个人名字叫岛市老八，也会被匹配上，导致结果不准确。

4. 关键字

4.1 查询

4.1.1 join

单独一个join是内连接，建议写成inner join增强可读性。
left join 和 right join是外连接，也可以在join前面加一个outer。

内连接：连接结果仅包含符合连接条件的行，参与连接的两个表都要满足on之后的连接条件。
外连接：连接结果不仅包含符合连接条件的行，也包含不符合条件的行。外连接分为左外连接、右外连接和全外连接。

内连接外连接怎么选？
例如这样一个需求：查询所有订单中商品总额（商品数量*商品价格）都大于500的用户昵称。

商品表字段

订单表字段

用户表字段

这种情况，我们在连接商品表和订单表的时候，就要注意：如果我们使用外连接，并把商品总额大于500这个条件放到on后面判断，就会导致结果不准确。

select c.nick_name
from t_order a
left join t_goods b
on a.goods_id = b.goods_id
and a.goods_num * b.goods_price > 500
left join t_user c
on a.user_id = c.user_id;

我们发现匹配的结果比预计的要多，找一下原因。

select c.nick_name, b.goods_price, a.goods_num
from t_order a
left join t_goods b
on a.goods_id = b.goods_id
and a.goods_num * b.goods_price > 500
left join t_user c
on a.user_id = c.user_id;

多查询出两个字段，得出了这样的结果。

发现原因：我们连接商品表和订单表的时候，使用left join确定t_order表为主表，也就是说就算数据不匹配，t_order表中所有数据都会展示，t_goods表中不匹配的数据被查询出为Null，所以我们连接订单表与用户表查询用户昵称的时候，就会把所有订单对应的用户昵称查询出来，这显然是不准确的。

还有一点需要注意：一个人可能下了多个订单，所以我们在查询用户昵称时要做去重处理。

解决完这些问题后，sql代码如下：

判断条件放到where里

select distinct c.nick_name
from t_order a
left join t_goods b
on a.goods_id = b.goods_id
left join t_user c
on a.user_id = c.user_id
where a.goods_num * b.goods_price > 500;

使用内连接代替外连接

select distinct c.nick_name
from t_order a
inner join t_goods b
on a.goods_num * b.goods_price > 500
and a.goods_id = b.goods_id
left join t_user c
on a.user_id = c.user_id;

4.1.2 distinct

做查询结果（一条数据）去重，只能出现在所有字段最前方

4.1.3 limit

语法：limit startIndex,length 或者 limit length
前者表示从第startIndex条（第一条数据为0）开始，往后取length条数据，后者就相当于startIndex等于0的情况
例如：limit 0, 10 相当于 limit 10，取前十条数据。
做翻页时，如果每页显示pageSize条数据，要查询第pageNo页，sql语句：
limit (pageNo-1)*pageSize , pageSize

4.1.4 union

两张表连接查询，匹配次数为两张表条数的乘积，又称笛卡尔积。
多表联查时，union的查询效率要更高，因为可以减少匹配的次数。

4.1.5 SQL查询关键字执行顺序

from…join…on…where…group by…having…select…order by…limit…

4.2 插入

4.2.1 语法规则

单条插入

insert into 表名(字段名1, 字段名2) values (值1, 值2);

多条插入

insert into 表名(字段名1, 字段名2) values (值1, 值2), (值3,值4), (值5,值6);

查询插入，要求插入列与查询列一一对应。

insert into 表A(字段名1, 字段名2) select (字段名1, 字段名2) from 表B;

4.3 删除

4.3.1 语法规则

delete

delete from 表名 where 字段名 = 字段值;

如果没有where筛选，则会删除表里所有数据。

truncate
截断 删除表中全部数据

truncate table 表名;

drop

drop table 表名;

4.3.2 truncate、delete、drop区别

delete把表中数据删除了，但是数据在硬盘上真实存储空间不会被释放。
缺点：效率较低；优点：如果后悔了，可以回滚数据。
truncate把表一次截断，效率很高，但是不支持回滚。
truncate为高危操作，轻易不要使用。
drop删除表，整个表都没了。效率最高，但是也不能回滚。

delete属于DML语句，drop和truncate属于DDL语句

4.4 更改

4.4.1 语法规则

update 表名 set 字段名1 = 字段值1, 字段名2 = 字段值2 where 字段名 = 字段值;

4.4.2 case…when…then…else…end

语法格式是这样的

CASE
WHEN cond1 THEN value1
WHEN cond2 THEN value2
WHEN condN THEN valueN
ELSE value
END;

我们以上面的商品表为例，有一个需求：为所有价格超过100元的商品打五折，如果打折后小于100则置为100。

直接上代码

update t_goods
set goods_price = (
case when goods_price > 100
and goods_price < 200
then 100
when goods_price > 200
then goods_price * 0.5
else goods_price
end);

4.5 创建表

创建表语句一般不需要我们自己写，一般是用Navicat创建，然后Navicat会自动给我们生成建表语句。
创建表的时候会有一些字段的约束条件，这个在另一篇文章会有详细讲解。

4.6 更改表结构

4.6.1 添加字段

alter table 表名 add  字段名 字段类型(值);

4.6.2 删除字段

alter table 表名 drop 字段名;

4.6.3 更改字段名

alter table 表名 rename 老字段名 to 新字段名;

4.6.4 更改字段类型

alter table 表名 alter 字段 新字段类型;