在互联网行业从事数据分析，要学一些必备的技能，比如SQL/HIVE。因此我将之前学习的SQL知识资源进行统一整理一下，以备不时之需。

（一）MySQL初识

1.如何系统学习MySQL
知乎帖子： https://www.zhihu.com/question/21760988
不准备做DBA的可能不需要全看，数据分析其实只需要会使用select查询函数，但涉及复杂的取数逻辑时，如何高效、准确地的从数据库中获得数据，也是一个技能。推荐基础入门书籍：《MySQL必知必会》

2.MySQL安装及使用

查找name字段中以'st'为开头的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
查找name字段中以'ok'为结尾的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok ';

源文档 http://www.runoob.com/mysql/mysql-regexp.html

like字句 模糊匹配%
WHERE 获取数据的条件
获取 author 字段含有 "COM" 字符的所有记录，就需要在 WHERE 子句中使用 SQL LIKE 子句。
SQL LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *
%通配符 两边都使用了百分号，检查字母前后的所有内容，也可以只使用一个百分号 匹配任意字符
下划线也是通配符 匹配任意一个字符
select * from authors where author like "Aaron Webe " 返回所有作者名是以”Aaron Webe"开始，后面跟任意一个字符的记录
SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM%';

3.2 insert into
INSERT INTO 语句用于向表格中插入新的行。
语法：INSERT INTO 表名称 VALUES (值1, 值2,....) 若没有指定列则数值的顺序应该要和定义表时的顺序一致
语法：INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....)
引号括起来：字符串+日期时间 不能引号：数字数值+函数+NULL
即时有的列没有指定数值，添加数据时也应该指定所有的列，为它留出位置
-- 向表 Persons 插入一条字段 LastName = Wilson 字段 Address = shanghai
INSERT INTO Persons (LastName, Address) VALUES ('JSLite', 'shanghai');
-- 向表 meeting 插入 字段 a=1 和字段 b=2
INSERT INTO meeting SET a=1,b=2;
-- SQL实现将一个表的数据插入到另外一个表的代码
-- 如果只希望导入指定字段，可以用这种方法：
-- INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;
INSERT INTO orders (user_account_id, title) SELECT m.user_id, m.title FROM meeting m where m.id=1;

3.3 update 修改表中的数据 以新替旧——同select只是多了SET
语法：UPDATE ——表名称 ——SET ——列名称 = 新值 ——WHERE ——列名称 = 某值

3.4 alter 修改表的结构 只有ALTER要加table，drop也要加table
语法：ALTER TABLE 表名 ADD 列名 列数据类型 [after 插入位置];
-- 在表students的最后追加列 address:
alter table students add address char(60);
-- 在名为 age 的列后插入列 birthday:
alter table students add birthday date after age;
-- 在名为 number_people 的列后插入列 weeks:
alter table students add column weeks varchar(5) not null default "" after number_people ;

删除，添加或修改表字段
ALTER TABLE testalter_tbl DROP
ALTER TABLE testalter_tbl ADD i INT; 定义数据类型 （first ,after c)
修改字段类型及名称
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
ALTER TABLE testalter_tbl CHANGE i j INT;
修改字段时，你可以指定是否包含只或者是否设置默认值。
mysql> ALTER TABLE testalter_tbl MODIFY j INT NOT NULL DEFAULT 100;
修改字段默认值
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
ALTER TABLE testalter_tbl RENAME TO alter_tbl;
修改存储引擎：修改为myisam
alter table tableName engine=myisam;
删除外键约束：keyName是外键别名
alter table tableName drop foreign key keyName;
修改字段的相对位置：这里name1为想要修改的字段，type1为该字段原来类型，first和after二选一，这应该显而易见，first放在第一位，after放在name2字段后面
alter table tableName modify name1 type1 first|after name2;

源文档 http://www.runoob.com/mysql/mysql-alter.html

3.5 delete
DELETE 语句用于删除表中的行。
语法：DELETE FROM 表名称 WHERE 列名称 = 值
-- 在不删除table_name表的情况下删除所有的行，清空表。
DELETE FROM table_name
-- 或者
DELETE * FROM table_name
-- 删除 Person表字段 LastName = 'Wilson'
DELETE FROM Person WHERE LastName = 'Wilson'
-- 删除 表meeting id 为2和3的两条数据
DELETE from meeting where id in (2,3);

清空表数据
方法一：delete from 表名; 方法二：truncate from "表名";
DELETE:1. DML语言;2. 可以回退;3. 可以有条件的删除;
TRUNCATE:1. DDL语言;2. 无法回退;3. 默认所有的表内容都删除;4. 删除速度比delete快。
-- 清空表为 workmates 里面的数据，不删除表。
delete from workmates;
-- 删除workmates表中的所有数据，且无法恢复
truncate from workmates;

删除整张表
语法：drop table 表名;
-- 删除 workmates 表:
drop table workmates;

删除整个数据库
语法：drop database 数据库名;
-- 删除 samp_db 数据库:
drop database samp_db;

3.6 其他语法
WHERE 子句用于规定选择的标准。
语法：SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
-- 从表 Persons 中选出 Year 字段大于 1965 的数据
SELECT * FROM Persons WHERE Year>1965

AND - 如果第一个条件和第二个条件都成立；
OR - 如果第一个条件和第二个条件中只要有一个成立；
-- 删除 meeting 表字段
-- id=2 并且 user_id=5 的数据 和
-- id=3 并且 user_id=6 的数据
DELETE from meeting where id in (2,3) and user_id in (5,6);
-- 使用 AND 来显示所有姓为 "Carter" 并且名为 "Thomas" 的人：
SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter';
-- 使用 OR 来显示所有姓为 "Carter" 或者名为 "Thomas" 的人：
SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'

ODER BY语句默认按照升序对记录进行排序。
ORDER BY - 语句用于根据指定的列对结果集进行排序。
DESC - 按照降序对记录进行排序。
ASC - 按照顺序对记录进行排序。
-- Company在表Orders中为字母，则会以字母顺序显示公司名称
SELECT Company, OrderNumber FROM Orders ORDER BY Company
-- 后面跟上 DESC 则为降序显示
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
-- Company以降序显示公司名称，并OrderNumber以顺序显示
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC

IN - 操作符允许我们在 WHERE 子句中规定多个值。
IN - 操作符用来指定范围，范围中的每一条，都进行匹配。IN取值规律，由逗号分割，全部放置括号中。 语法：SELECT "字段名"FROM "表格名"WHERE "字段名" IN ('值一', '值二', ...);
-- 从表 Persons 选取 字段 LastName 等于 Adams、Carter
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

NOT - 操作符总是与其他操作符一起使用，用在要过滤的前面。
SELECT vend_id, prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;

as - 可理解为：用作、当成，作为；别名
语法：select column_1 as 列1,column_2 as 列2 from table as 表
SELECT * FROM Employee AS emp
-- 这句意思是查找所有Employee 表里面的数据，并把Employee表格命名为 emp。
-- 当你命名一个表之后，你可以在下面用 emp 代替 Employee.
-- 例如 SELECT * FROM emp.
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
-- 列出表 Orders 字段 OrderPrice 列最大值，
-- 结果集列不显示 OrderPrice 显示 LargestOrderPrice
-- 显示表 users_profile 中的 name 列
SELECT t.name from (SELECT * from users_profile a) AS t;
-- 表 user_accounts 命名别名 ua，表 users_profile 命名别名 up
-- 满足条件 表 user_accounts 字段 id 等于 表 users_profile 字段 user_id
-- 结果集只显示mobile、name两列
SELECT ua.mobile,up.name FROM user_accounts as ua INNER JOIN users_profile as up ON ua.id = up.user_id;

join用于根据两个或多个表中的列之间的关系，从这些表中查询数据。
JOIN: 如果表中有至少一个匹配，则返回行
INNER JOIN:在表中存在至少一个匹配时，INNER JOIN 关键字返回行。
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

4.sql函数
COUNT 让我们能够数出在表格中有多少笔资料被选出来。
语法：SELECT COUNT("字段名") FROM "表格名";
-- 表 Store_Information 有几笔 store_name 栏不是空白的资料。
-- "IS NOT NULL" 是 "这个栏位不是空白" 的意思。
SELECT COUNT (Store_Name) FROM Store_Information WHERE Store_Name IS NOT NULL;
-- 获取 Persons 表的总数
SELECT COUNT(1) AS totals FROM Persons;
-- 获取表 station 字段 user_id 相同的总数
select user_id, count(*) as totals from station group by user_id;

MAX 函数返回一列中的最大值。NULL 值不包括在计算中。
语法：SELECT MAX("字段名") FROM "表格名"
-- 列出表 Orders 字段 OrderPrice 列最大值，
-- 结果集列不显示 OrderPrice 显示 LargestOrderPrice
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

8.MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

下面的 SQL 语句从 "Websites" 和 "apps" 表中选取所有不同的country（只有不同的值）：
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
下面的 SQL 语句使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country（也有重复的值）：
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录：
SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。其实Group by name可以实现统计
SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name【 WITH ROLLUP】;

select coalesce(a,b,c);
如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）

5.添加索引
普通索引（INDEX)
语法：ALTER TABLE 表名字 ADD INDEX 索引名字 ( 字段名字 )
-- –直接创建索引
CREATE INDEX index_user ON user(title)
-- –修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
-- 给 user 表中的 name字段 添加普通索引(INDEX)
ALTER TABLE table ADD INDEX index_name (name)
-- –创建表的时候同时创建索引
CREATE TABLE table (
id int(11) NOT NULL AUTO_INCREMENT ,
title char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
time int(10) NULL DEFAULT NULL ,
PRIMARY KEY ( id ),
INDEX index_name (title(length))
-- –删除索引
DROP INDEX index_name ON table

全文索引(FULLTEXT)
语法：ALTER TABLE 表名字 ADD FULLTEXT (字段名字)
-- 给 user 表中的 description 字段添加全文索引(FULLTEXT)
ALTER TABLE user ADD FULLTEXT (description);

添加多列索引
语法： ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3)
-- 给 user 表中的 name、city、age 字段添加名字为name_city_age的普通索引(INDEX)
ALTER TABLE user ADD INDEX name_city_age (name(10),city,age);

建立索引的时机
在WHERE和JOIN中出现的列需要建立索引，但也不完全如此：
MySQL只对<，<=，=，>，>=，BETWEEN，IN使用索引
某些时候的LIKE也会使用索引。
在LIKE以通配符%和_开头作查询时，MySQL不会使用索引。
-- 此时就需要对city和age建立索引，
-- 由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。
SELECT t.Name
FROM mytable t LEFT JOIN mytable m ON t.Name=m.username
WHERE m.age=20 AND m.city='上海';
SELECT * FROM mytable WHERE username like'admin%'; -- 而下句就不会使用：
SELECT * FROM mytable WHEREt Name like'%admin'; -- 因此，在使用LIKE时应注意以上的区别。

键：独一无二
外键：通过包括另外一张表的键形成的表之间的连接被称作外键
关系：一一， 一多，多多
规范化：范式
第一范式：无重复列，所有列只能包含一个值——同一行内的冗余
第二范式：

7.连接符，数值运算和函数
连接符：+或者|| mysql不支持连接符，CONCAT: select concat(tname, '(',dname,')') 连接的列有相同或者相近的数据类型
select tname + '('+dname+')' AS info, age from teacher where子句不可使用别名
select tname + cast(sal as varchar(5)), age from teacher cast转换数据类型
SELECT执行顺序：from > where > group by > having > select > order by
数值运算： + - * / %余
CASE WHEN :
SELECT cname,ctime,credit=
WHEN ctime >= 40 THEN 5
WHEN ctime >=30 THEN 4
WHEN ctime >=20 THEN 3
ELSE 2
FROM course
函数：截取字符串：substring() 日期转换convert 截取当前日期curdate() 不同数据库函数不同
SQL SERVER: 字符转换 UPPER/LOWER SELECT UPPER(bookname) as book from bookitem
去空格 LTRIM/RTRIM 字符串前面/后面的空格 RTRIM(tname)
取字符串 LEFT/RIGHT/SUBSTRING LEFT(bookname,6)从左边开始取6位
字符串比较 CHARINDEX查询字符串在目标中第一次出现的位置 CHARINDEX('boy', bookname) as position
字符串操作 REPLACE替换 REPLACE (bookname, 'Procing', 'Pro.') AS replace
四舍五入 ROUND round(price, 0) as round_price
日期函数： DAY/MONTH/YEAR 返回日期、月份、年份 month(ctest) 6
DATENAME() 以字符串的形式返回日期指定部分 DATENAME(month,ctest) june
DATEPART()以整数值形式返回日期指定部分 6
covert 可以实现转换日期，时间类型

8.聚合分析与分组
聚合分析：对表中一列或者多列数据的统计分析
聚合函数：SUM MAX MIN AVG COUNT
聚合函数是对列中的一系列数据进行处理，返回单个统计值；前面的函数是对列中的单个数据进行处理
1.SELECT SUM() FROM TABLE WHERE …
只有count( )计算NULL值
2.COUNT()
SELECT Count(cast( tmo as varchar(5)) + tname) as t_noname from teacher 多列计数
查询两列的行数，由于数据类型不一致，要用CAST表达式将他们转换为相同的数据类型
Count()函数只对参数不是NULL的行计数
Count（ ）返回表中的总行数
3.MAX/MIN()
数值，字符串，日期
返回年龄最大的教师的教工号，姓名，性别
错误：SELECT tname, dname, tsex, sal, MAX(age) FROM teacher
聚合函数处理的是数据组，本例中MAX()将整个teacher表看成一组，而其他数据没有进行任何分组，SELECT没有任何逻辑意义
SELECT tname, dname, tsex, sal, age FROM teacher WHERE age=MAX(age) 同样错误
正确：SELECT tname, dname, tsex, sal,age FROM teacher WHERE age=(SELECT MAX(age) FROM teacher)
4.AVG()
数值型， 总sum除以总行数时忽略NULL的行
同MAX(),不能直接作用于WHERE字句，必须以子查询的形式使用
SELECT *
FEOM teacher
WHERE age>= (SELECT AVG(age) FROM teacher)
ORDER BY age
查询所有年龄高于平均年龄的教师的信息
SELECT AVG([all / distinct] column_name) FROM table_name
SELECT COUNT(DISTINCT sal) AS distinct_count FROM teacher

9.多表查询 JOIN
WHERE: 二表连接： SELECT name, test FROM teacher, course WHERE teacher.cno = course.cno
多表连接： and
JOIN: select colum from table join_type table on(连接条件) 自然连接，内连接，外连接，交叉连接
自连接：表与其自身进行连接
SELECT DISTINCT xuehao from student where mark<60
SELECT sname, dname,cno,mark from student where xuehao in('123','234','554') order by sname
查询成绩中存在不及格课程的学生的姓名，所在系，所有课程和成绩---------首先查成绩有不及格的学号，再根据学号查所有课程
SELECT DISTINCT S1.sname, S1.dname, S1.cno, S1.mark 否则有重复
FROM student as S1, student as s2
WHERE S1.xuehao = S2.xuehao
AND S2.mark < 60
OEDER BY S1.sname
自然连接：自动判断具有相同名称的列，自动进行匹配,不能人为指定列进行匹配
SELECT SNAME,DNAME,CNO,TNAME FROM student NATURAL JOIN teacher
SELECT SNAME teacher.dname, teacher.cno, tname from student teacher where student.dname=teacher.dname AND student.cno=teacher.cno
内连接：返回结果是两个表中所有相匹配的数据
SELECT sname, s.dname,s.dno, tname FROM student as s INNER JOIN teacher as t ON s.cno=t.cno ORDER BY sname
等价于 where S.CNO=T.CNO
不等连接：> , <, <>, >=, <=, !>, !<
SELECT sname, s.dname,s.dno, tname FROM student as s
INNER JOIN teacher as t
ON s.dname <> t.dname
AND s.cno=t.cno
INNER JOIN COURSE as c
ON t.cno=c.cno
ORDER BY sname 非本系老师开课
外连接：不仅包括符合连接条件的行，也包括左/右/全部表中的所有数据行
LEFT JOIN: 内连接+ 左边表未匹配的行 缺少的右边表的属性值用NULL表示 等价于where子句中的“ =”
RIGHT JOIN: 内 + 右 “= ”
FULL OUTER JOIN ：内+左+右
交叉连接：CROSS JOIN
SELECT * FROM table1, table2 = SELECT * FROM table1 CROSS JOIN table2 等到m*n行，a+b列的新数据表
生成的是两个表的笛卡尔积，所以不能使用ON关键字，只能在WHERE子句中定义搜索条件