一，前言
数据分析基本的代码能力是对SQL的使用，下面对业务中实际用到的语法和函数进行学习及总结。
二，常用关键字与函数
SQL的书写规范：语句中出现的所有表名、字段名全部小写，系统保留字、内置函数名、SQL保留字大写(SQL本身不区分大小写，大写可能是为了增加可读性？）
SQL注释：
MySQL——1，单行注释使用“#”或者“-- ”（第二种方法后有一个空格）2，多行注释使用“/* */”
Oracle——1，单行注释使用“--”2，多行注释使用使用“/* */”
SQL关键字与函数：
AS——别名关键字，用来给之前的关系取一个更直观或者“中文”的名字来表示
DISTINCT——去重关键字
能够作用于单列或者多列
COUNT——函数返回指定条件的行的数目
当COUNT与DISTINCT结合使用时：表示对这一列不重复的行进行计数
GROUP BY——分组函数
GROUP BY结合一些聚合函数对一个或多个列的结果集合进行分组
LIKE——该操作符用于在WHERE子句中，搜索列中的指定模式
LIKE配合“通配符”能够完成匹配指定的字符
通配符——“%”代替0个或多个字符；“_”替代一个字符
例子：“A%”表示以A开头的所有字符，“%AA%”表示包含“AA”的所有字符
JOIN——用来把两个或多个表根据某些相同字段，结合起来
JOIN有多种不同的利用，例如“LEFT JOIN”、“INNER JOIN”等
例如“INNER JOIN”，是最常用的JOIN方式，用来获取不同表满足条件的部分，即获得交集
其他JOIN：
LEFT JOIN：即使右表中没有，也从左表中返回所有行
RIGHT JOIN：即使左表中没有，也从右表返回所有行
FULL JOIN：只要其中一个表存在匹配，则返回行
CASE WHEN——用于分类统计
--简单CASE函数
CASE sex
WHEN '1' THEN '男'
WHEN '0' THEN '女'
ELSE '其他' END
--CASE搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '0' THEN '女'
ELSE '其他' END
这里，END后可以直接加别名，不用AS
OVER()——开窗函数，同聚合函数一样，也基于组对行进行某种函数操作，不同的是聚合函数每组返回一行，而开窗函数返回的多个行。常见用法：
ROW_NUMBER() OVER(PARTITION BY column_name ORDER BY column_name)
来组成分析函数，完成复杂报表的统计需求。（后续还需要再开一篇专门学习各种分析函数，进一步对复杂的统计表达方法进行学习）
最后，值得注意的是，SQL的语法顺序和执行顺序是不同的。
SQL的语法顺序：
SELECT——FROM——WHERE——GROUP BY——HAVING——UNION——ORDER BY
执行顺序：
FROM——WHERE——GROUP BY——HAVING——SELECT——DISTINCT——UNION——ORDER BY
DQL语言（查询）
1.基础查询 1).select 查询列表 from 表名 2).别名: 方式一: select 字段 别名 方式二: select 字段 AS 别名 3).+: 在sql中只能用于加法运算 4).常见方法 desc 表名 : 查询表结构 show tables from 库名 : 查看指定库下所有表 show tables : 查看当前库下所有表 show databases : 查看当前连接下的所有库 is null:是否为空 distinct:去重 ifnull(列名,值1):如果列名的值为null,则用值1进行其他运算 concat(字段,字段....):拼接 escape:转移字符 between A and B : 在A和B的区间 in :在...里面 2.条件查询 1).select 查询列表 from 表名 where 条件 2).条件形式 关系: > < >= <= = <> 逻辑:and or not 模糊:like/not like 注: 一般和通配符搭配使用: "__e_%" _:任意单个字符 %:任意0个或多个字符 between A and B / not between A and B in / not in is null/is not null 3.排序查询 select 查询列表 from 表名 where 条件 order by 排序列表(asc|desc) 4.单行函数 常见的单行函数 1).字符函数 length:获取字节个数 substr:截取子串 concat:拼接字符串 trim:去前后空格或指定字符 trim(substr from str):去除指定字符 lower:转换成小写 upper:转换成大写 lpad:左填充 rpad:右填充 instr:获取子串第一次出现的索引 replace:替换字符串 2).数学函数 abs:绝对值 round:四舍五入 floor:向下取整 ceil:向上取整 rand:随机数 mod:取余 truncate:截取 3).日期函数 now():获取当前日期+时间 curdate():获取日期 curtime():获取时间 date_format():将日期转换成指定个数的字符 year/month/day:获取指定部分 str_to_date:将字符串解析成指定格式的日期 4).流程控制函数 if: 语法 if(条件，表达式1，表达式2) 如果条件成立，返回表达式1，否则返回表达式2 case: 方式一: 类似于多重if case when 条件1 then 表达式1 when 条件2 then 表达式2..... else 表达式n end 方式二:类似于switch case case 表达式或字段 when 值1 then 表达式1 when 值2 then 表达式2.... else 表达式n end 5.分组查询 select 分组函数，分组后的字段 from 表名 where 筛选条件 group by 分组的字段 having 分组后筛选 order by 排序列表 asc|desc 6.连接查询 1).SQL92语法:只有内连接 1.等值连接 select 查询列表 from 表1 别名1 ，表2 别名2 where 别名1.关键列 = 别名2.关键列 and 筛选条件 group by 分组 having 分组后的筛选 order by 排序 asc|desc 2.非等值连接 select 查询列表 from 表1 别名1 ，表2 别名2 where 别名1.关键列 > 别名2.关键列 and 筛选条件 //非等值条件 group by 分组 having 分组后的筛选 order by 排序 asc|desc 3.自连接 select 查询列表 from 表 别名1 ，表 别名2 where 别名1.关键列 > 别名2.关键列 and 筛选条件 //表为同一张表 group by 分组 having 分组后的筛选 order by 排序 asc|desc 2).SQL99语法 内连接: 1.等值连接 select 查询列表 from 表1 别名1 [inner] join 表2 别名2 on 等值连接条件 where 筛选条件 group by 分组 having 分组后的筛选 order by 排序 asc|desc 2.非等值连接 select 查询列表 from 表1 别名1 [inner] join 表2 别名2 on 非等值连接条件 where 筛选条件 group by 分组 having 分组后的筛选 order by 排序 asc|desc 3.自连接 select 查询列表 from 表 别名1 [inner] join 表 别名2 on 连接条件 where 筛选条件 //表为同一张表 group by 分组 having 分组后的筛选 order by 排序 asc|desc 外连接: 1.左外连接 select 查询列表 from 主表 别名 left [outer] join 从表 别名 on 连接条件 where 筛选条件 group by 分组 having 分组后的筛选 order by 排序 2.右外连接 select 查询列表 from 从表 别名 right[outer] join 从表 别名 on 连接条件 where 筛选条件 group by 分组 having 分组后的筛选 order by 排序 3.全外连接(mysql不支持全外连接)full[outer] join (left join union right join代替) select 查询列表 from 表1 别名 full[outer] join 表2 别名 on 连接条件 where 筛选条件 group by 分组 having 分组后的筛选 order by 排序 7.子查询 1).select后面 2).from后面 3).where或having后面 4).exists后面 8.分页查询 select 分组函数，分组后的字段 from 表名 where 筛选条件 group by 分组的字段 having 分组后筛选 order by 排序列表 asc|desc limit 起始条目索引,条目数 9.分组函数 方法: avg：平均值 sum：求和 max：最大值 min：最小值 count：统计个数
DML语言（操纵）
vinsert/update/delete 1.插入单行 方式一：insert into 表名(列名....) values/value(值....) 方式二：insert into 表名 set 字段名=值,字段名=值.....; 2.插入多行 方式一：insert into 表名(列名....) values(值...),(值...)....; 方式二：insert into 表名(列名....) select 值1，值2....union [all] ..... select 值1，值2... 注: 如果不使用all关键字，默认去重。如果使用all关键字，可以包含重复项 3.修改语句 update 表名 set 字段名 = 新值....where 条件 4.删除语句 1).delete 删除表数据，删除后可以回滚数据 单表的删除: delete from 表名 where 条件 多表的删除: delete 表的别名 .... from 表1 别名,表2 别名.... where 连接条件 and 筛选条件 2).truncate truncate table 表名；删除表数据，直接删除不能回滚数据
DDL语言（定义）
create/alter/drop 1.库的管理 创建：create database [if not exists] 库名 删除：drop database [if exists] 库名 2.表的管理 创建：create table [if not exists] 表名( 字段名 字段类型 [列级约束], ........ [表级约束]) 注：表级约束语法 constraint 约束名 foreign key （主表字段名）references 表名（字段名） 修改： 1).修改表名：alter table 表名 rename to 新表名 2).添加新列：alter table 表名 add column 列名 类型 约束 3).删除旧列：alter table 表名 drop column 旧列名 4).修改列名：alter table 表名 change column 旧列名 新列名 新类型 约束 5).修改列的类型或约束 情况1.修改非空，默认两大约束或修改类型 alter table 表名 modify column 列名 新类型 新约束 情况2.添加主键，外键，唯一 alter table 表名 add primary key (主键列名); alter table 表名 add unique (唯一键列名); alter table 主表 add constraint 约束名 foreign key (主表已经存在的列名) references 附表(附表已经存在的字段名); 情况3.删除主键，外键，唯一 alter table 表名 drop primary key； alter table 表名 drop index 唯一键的列名； alter table 表名 drop foreign key； 3.表的删除 drop table [if exists] 表名 4.表的复制 1.只复制表的结构： create table 表名 like 旧表
DCL语言(事务控制)
commit:提交 rollback:回滚 示例： Connection con = null; Preparedstatement st = null; try { com = JDBCUtils.getConnection(); con.setAutoCommit(false); //关闭自动提交事务 st = con.prepareStatement("sql语句"); // st.setXX(占位符,值); //参考JDBC的使用 st.executeUpdate(); ..... int i=1/0; //模拟异常 st.setXX(占位符,值); con.commit(); //结束事务，提交事务 } catch (Exception e) { con.rollback(); //回滚事务 }finally { JDBCUtils.close(con); //释放资源 }
