在互联网行业从事数据分析，要学一些必备的技能，比如SQL/HIVE。因此我将之前学习的SQL知识资源进行统一整理一下，以备不时之需。
（一）MySQL初识
1.如何系统学习MySQL
知乎帖子：https://www.zhihu.com/question/21760988
不准备做DBA的可能不需要全看，数据分析其实只需要会使用select查询函数，但涉及复杂的取数逻辑时，如何高效、准确地的从数据库中获得数据，也是一个技能。推荐基础入门书籍：《MySQL必知必会》
2.MySQL安装及使用
- 了解基本原理后，就要进行实战操作，进行表和数据库的增删改查。我起初在电脑上安装了SQLyog，这是图形化的MySQL数据库工具，非常好用。
安装使用过程中可能遇到的问题：
(1)中文乱码
解决乱码： 将字符集改成UTF-8
https://blog.csdn.net/u012410733/article/details/61619656
(2)导入导出sql文件
http://database.51cto.com/art/201005/200739.htm
image.png
（二）MySQL学习笔记
需要注意的是，在不同数据库版本里，SQL语法存在一些差异。
一千行MySQL学习笔记：http://www.cnblogs.com/shockerli/p/1000-plus-line-mysql-notes.html
MySQL基础入门：https://github.com/jaywcjlove/mysql-tutorial/blob/1fb941c78566efdc2cc95ac68681f2b9fa3d2f91/21-minutes-MySQL-basic-entry.md
子查询与函数嵌套：https://juejin.im/entry/5a28afc1f265da431d3c9144
50道SQL练习题：https://zhuanlan.zhihu.com/p/32137597
牛客网SQL实战：https://www.nowcoder.com/ta/sql
leetcode题库：https://leetcode-cn.com/problemset/database/
其他：
1.对大小写不敏感;
注意:所有的数据库名，表名，表字段都是区分大小写的
分号是在数据库系统中分隔每条 SQL 语句的标准方法，这样就可以在对服务器的相同请求中执行一条以上的 SQL
语句
养成用TABLE.COLUMN方式引用列的习惯，这样可以在两个表具有同名的列的时候避免混淆
SELECT - 从数据库中提取数据
UPDATE - 更新数据库中的数据
DELETE - 从数据库中删除数据
INSERT INTO - 向数据库中插入新数据
CREATE DATABASE - 创建新数据库
ALTER DATABASE - 修改数据库
CREATE TABLE - 创建新表
ALTER TABLE - 变更（改变）数据库表
DROP TABLE - 删除表
CREATE INDEX - 创建索引（搜索键）
DROP INDEX - 删除索引
导入sql文件：
exit/quit——在CMD中输入MySQL -h localhost -u root -p mydb2 < e:\MySQL\mydb2.sql
导出sql文件：
MySQLdump -h localhost -u root -p mydb >e:\MySQL\mydb.sql
可以修改MySQL配置文件位置my.ini文件，自己写个ini文件再指定MySQL位置就行了
https://www.cnblogs.com/wawahaha/p/4373493.html
1.mysql服务的启动和停止编辑
net stop mysql
net start mysql
2、登陆mysql编辑
登陆终端(windows cmd或者linux 命令行下)且已经安装mysql-client软件;
语法如下： mysql -h 主机ip -u 用户名 -p 用户密码 主机名默认localhost
例如：mysql -h 192.168.31.194 -u root -p 123456
即可登陆mysql管理
键入命令：mysql -u root -p， 回车后提示你输入密码，输入：12345，然后回车即可进入到mysql中了，mysql的提示符是：
mysql -u root -p
mysql>
1.创建/删除数据库（对于表的操作，需首先进入库，use+库名）
-- 创建一个名为 samp_db 的数据库，数据库字符编码指定为 gbk
create database samp_db character set gbk;
drop database samp_db; -- 删除 库名为samp_db的库
show databases; -- 显示数据库列表。
use samp_db; -- 选择创建的数据库samp_db
show tables; -- 显示samp_db下面所有的表名字
describe 表名; -- 显示数据表的结构
delete from 表名; -- 清空表中记录
创建数据库
mysql> CREATE DATABASE if not exists test;
特定权限创建
$ mysqladmin -uroot -p create test
Enter password:***
删除数据库
DROP DATABASE test2;
mysql> drop database if exists test2;
2.创建表
create table 表名称(列声明);
not null声明往表中添加数据时，必须提供对应的值，如主键必须为NOT NULL；
auto_increment自动为该列分配键值，确保在插入新的记录时该列被自动赋予一个唯一的值
varchar(32) 表明该列可以容纳不多于150个字符
CREATE TABLE user_accounts (
id int(100) unsigned NOT NULL AUTO_INCREMENT primary key,
password varchar(32) NOT NULL DEFAULT '' COMMENT '用户密码',
reset_password tinyint(32) NOT NULL DEFAULT 0 COMMENT '用户类型：0－不需要重置密码；1-需要重置密码',
mobile varchar(20) NOT NULL DEFAULT '' COMMENT '手机',
create_at timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
update_at timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
-- 创建唯一索引，不允许重复
UNIQUE INDEX idx_user_mobile(mobile)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
COMMENT='用户表信息';
CREATE TABLE purchases(
purchase_id int NOT NULL AUTO_INCREMENT,
title_id int(11) NOT NULL,
PRIMARY KEY (purchase_id));
3.增删改查
3.1 select
语法：SELECT 列名称 FROM 表名称
语法：SELECT * FROM 表名称
-- 表abc 两个 id 表abc中不包含 字段a=b 的 查询出来，只显示id
SELECT s.id from station s WHERE id in (13,14) and user_id not in (4);
-- 从表 Persons 选取 LastName 列的数据
SELECT LastName FROM Persons
-- 结果集中会自动去重复数据
SELECT DISTINCT Company FROM Orders
-- 表 Persons 字段 Id_P ，等于 Orders 字段 Id_P 的值，
-- 结果集显示 Persons表的 LastName、FirstName字段，Orders表的OrderNo字段
SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p, Orders o WHERE p.Id_P = o.Id_P
正则表达式（regular expression） https://zh.wikipedia.org/wiki/%E6%AD%A3%E5%88%99%E8%A1%A8%E8%BE%BE%E5%BC%8F
MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
^ 字符开始位置 如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
也匹配 '\n' 或 '\r' 之前的位置。
. 匹配除 "\n" 之外的任何单个字符 要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式
[…] 字符集合,匹配所包含的任意一个字符。 例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^…] 负值字符集合,匹配未包含的任意字符。 例如， '[^abc]' 可以匹配 "plain" 中的'p'。
P1|P2|p3 匹配 p1 或 p2 或 p3。 例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
- 匹配前面的子表达式零次或多次。 例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
- 匹配前面的子表达式一次或多次。 例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n} n 是一个非负整数。匹配确定的 n 次。 例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m} m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。
查找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 ——列名称 = 某值
- update语句设置字段值为另一个结果取出来的字段
update user set name = (select name from user1 where user1 .id = 1 )
where id = (select id from user2 where user2 .name='小苏');
-- 更新表 orders 中 id=1 的那一行数据更新它的 title 字段
UPDATEordersset title='这里是标题' WHERE id=1;
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 表名 change 列旧名称 列新名称 新数据类型;
-- 将表 tel 列改名为 telphone:
alter table students change tel telphone char(13) default "-";
-- 将 name 列的数据类型改为 char(16):
alter table students change name name char(16) not null;
-- 修改 COMMENT 前面必须得有类型属性
alter table students change name name char(16) COMMENT '这里是名字';
-- 修改列属性的时候 建议使用modify,不需要重建表
-- change用于修改列名字，这个需要重建表
alter table meeting modify weeks varchar(20) NOT NULL DEFAULT "" COMMENT "开放日期 周一到周日：0~6，间隔用英文逗号隔开";
删除列
语法：alter table 表名 drop 列名称;
-- 删除表students中的 birthday 列:
alter table students drop birthday;
重命名表
语法：alter table 表名 rename 新表名;
-- 重命名 students 表为 workmates:
alter table students rename workmates;
删除，添加或修改表字段
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: 只要其中一个表中存在匹配，就返回行
NULL
= 和 != 运算符是不起作用的
必须使用 IS NULL 和 IS NOT NULL
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;
9.分组
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
使用 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
主键索引
语法：ALTER TABLE 表名字 ADD PRIMARY KEY ( 字段名字 )
-- 给 user 表中的 id字段 添加主键索引(PRIMARY key)
ALTER TABLE user ADD PRIMARY key (id);
唯一索引
语法：ALTER TABLE 表名字 ADD UNIQUE (字段名字)
-- 给 user 表中的 creattime 字段添加唯一索引(UNIQUE)
ALTER TABLE user ADD UNIQUE (creattime);
全文索引(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时应注意以上的区别。
键：独一无二
外键：通过包括另外一张表的键形成的表之间的连接被称作外键
关系：一一， 一多，多多
规范化：范式
第一范式：无重复列，所有列只能包含一个值——同一行内的冗余
第二范式：
5.查询
order by col desc/asc
where: <>表示不等于 between a and b is(not) null判断非空，控制不满足任何查询比较条件，结果都为false
AND/OR: null and false:false null or true:true AND运算符的运算级别高于OR运算符
select sal from teacher where dname='计算机' or dname='生物' and sal>1000 order by sal
计算机所有老师和生物系中工资大于1000的教师的相关信息
建议使用括号来实现需要的执行顺序
IN: 满足多个条件中的一个， not in
NOT: 求反 null的反还是null not dname = '计算机' not between A and B
LIKE:模糊查询 通配符：%， -， [] 针对的是CHAR,VARCHAR和TEXT类型
‘%计算机'以计算机结尾 开始 '计算机%' 中间 '%计算机%'
-表示一个任意字符的匹配，--两个，需确定查询的字符串的个数才能使用 ‘------’得到小于等于6个字的字符串
[] 指定一系列的字符，满足其一且位置为通配符位置
‘[计生]%’以计或生开头 []内字符前加^表示否定 ‘[^计生]%’= not cname like‘[计生]%’
ESCAPE:定义转义符，告诉DBMS紧跟在准义字符后的字符看作是实际值，而不是通配符
LIKE ‘%M%' ESCAPE 'M' 定义了转义字符M,将第二个百分号看做是实际值,查询所有以%结尾的字符串
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=
CASE
WHEN ctime >= 40 THEN 5
WHEN ctime >=30 THEN 4
WHEN ctime >=20 THEN 3
ELSE 2
END
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
查询所有年龄高于平均年龄的教师的信息
5.重值
SELECT AVG([all / distinct] column_name) FROM table_name
SELECT COUNT(DISTINCT sal) AS distinct_count FROM teacher
组合查询
分组聚合，将查询对象按一定条件分组，再对每一个组进行聚合分析
1.Group by
Group by是根据所选列的数据进行分组，将该列具有相同值的行化为一组
归纳类型，汇总数据
SELECT tsex, avg(sal) as avg_sal FROM teacher GROUP BY sex; 同时得到男教师和女教师的平均工资
SELECT column, sum(column) FROM table WHERE COLUMN=… GROUP BY column2
根据column2进行分组，column2只能来自于table, 不能根据实际值，聚合函数或者其他表达式计算的值进行分组
2.ROLLIP运算符和CUBE运算符
在group by子句中使用，扩展查询结果，计算分组后的每个组的组合信息
SELECT DNAME, TSEX, COUNT() AS TOTAL_NUM FROM teacher
GROUP BY dname, sex WITH ROLLUP/CUBE
ORDER BY dname
查询各系男女教师数量+统计各系（DNAME）教师总人数----各系增加了一行汇总人数且总人数增加一行
而cube，包含多列组合的统计结果，包含了整表的统计结果和各单列的统计结果（分别对性别进行的统计，和各系的统计）
3.HAVING子句
通常与GROUP BY子句同时使用，可以在分组中删除一些不满足条件的行组
SELECT dname, count() AS num_teacher FROM teacher
GROUP BY dname
HAVING count(*) >=2
Teacher表中至少有两位教师的系及教室人数
HAVING与where的不同之处是，HAVING字句与组有关，而不是单个的行
HAVING DNAME=”生物” 错误语句
WHERE字句仅可以接受来自FROM的输入，而having字句可以接受来自GROUP BY子句，where子句，from子句
WHERE子句在分组之前过滤数据，而HAVING子句则过滤分组后的数据，可以联合使用
顺序：
SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY
SELECT dname, count(tsex) AS num_girl
FROM teacher
WHERE tsex=’女’
GROUP BY dname
HAVING count(tsex)>=2
ORDER BY num_girl
从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子句中定义搜索条件
UNION 与UNION JOIN:对集合进行关系并运算 集合并
SELECT sno FROM teacher where cno=1 UNION SELECT * FROM teacher where cno=2 等价，自动去除重复元组
SELECT DISTINCT sno FROM teacher where cno=1 OR cno=2
UNION只要求列的类型匹配，而对应列的列名可以不同(sname, tname)
A UNION B UNION C 多表并
