sql--15回顾学习记录

最新推荐文章于 2023-03-15 22:41:34 发布

Nicole_xzz001

最新推荐文章于 2023-03-15 22:41:34 发布

阅读量458

点赞数

分类专栏： sql学习笔记

本文链接：https://blog.csdn.net/qq_41551159/article/details/113004201

版权

sql学习笔记专栏收录该内容

15 篇文章 2 订阅

订阅专栏

DML

单表查询：

1. 导入数据

（1）decimal是表示小数类型，后面比例为2，表示小数点后两位
（2）navicat 读excel表时，要注意excel表必须是打开的状态
（3）excel的文件一定是没有格式的，比如不能有加粗，不能有网格线。

2. sql 关键字

（1）sql 中关键字不能作为表名、列名，一般大写
（2）语句以英文分号结束
（3）一点代码：

SHOW DATABASES;
CREATE DATABASE SQL2;
USE sqllearning;

3.单表查询select from
（1）SELECT 必需子句1（查询列）FROM 必需子句2（数据表）；
select结果二维表

（2）AS别名；AS不改变原表！；不可以给表名单独改名字，比如：SELECT milk_tea.prod_name FROM milk_tea AS m，会报错，

SELECT m.prod_name FROM milk_tea AS m;
SELECT m.prod_date,m.sale_price FROM milk_tea AS m

（3）增加常数列（不改变原表）

SELECT prod_id, prod_name, in_price, sale_price,0.9 FROM milk_tea;
SELECT prod_id, prod_name, in_price, sale_price, 0.9 AS discount FROM milk_tea;
SELECT prod_id, prod_name, in_price, sale_price, '零食'AS class FROM milk_tea;
SELECT m.*, m.in_price*0.9 newinprice FROM milk_tea AS m;

（4）列的四则运算–加减乘除（不改变原表）

SELECT m.prod_name,m.sale_price - m.in_price FROM milk_tea AS m;
SELECT m.prod_name,m.sale_price - m.in_price AS profit  FROM milk_tea AS m;
SELECT m.prod_name,m.sale_price * 0.9 AS new_saleprice  FROM milk_tea AS m;

4. 字符拼接CONCAT

（1）

SELECT m.*,CONCAT(prod_name,net_w) FROM milk_tea AS m;
SELECT m.*,CONCAT(prod_name,net_w) AS combination FROM milk_tea AS m;
SELECT prod_name,net_w,CONCAT(prod_name,net_w) AS combi FROM milk_tea AS m;
SELECT prod_name,net_w,CONCAT(prod_name,net_w) AS 产品信息 FROM milk_tea AS m；
SELECT prod_id,prod_name,net_w,CONCAT(prod_id,prod_name,net_w) AS 产品信息 FROM milk_tea AS m；

（2）

SELECT prod_name,net_w,CONCAT(prod_name,'（',net_w,'）') AS 产品信息 FROM milk_tea AS m；

（3）

SELECT prod_name,net_w,CONCAT(prod_name,'是',net_w) AS 产品信息 FROM milk_tea AS m；
SELECT m.prod_name,m.net_w,CONCAT(m.prod_name,'是',m.net_w) AS 产品信息 FROM milk_tea AS m；
SELECT m.prod_name, m.net_w, CONCAT(m.prod_name, '是', m.net_w, '，单价是',m.sale_price,'元') AS 产品信息 FROM milk_tea AS m ;

（4）遇到拼接的部分有NULL，CONCAT()会直接将拼接结果变成NULL
（5）指定拼接符，关键字CONCAT_WS

注：CONCAT()与CONCAT_WS()区别
如果遇到拼接的部分有NULL，CONCAT()会直接将拼接结果变成NULL；
CONCAT_WS()是忽略NULL部分，但保留其他拼接的内容

（6）

SELECT m.prod_name, 
       m.net_w,  
       m.sale_price, 
       CONCAT_WS( '+', m.prod_name, m.net_w, m.sale_price ) FROM milk_tea AS m;
       
SELECT m.prod_name, 
       m.net_w,  
       m.sale_price, 
       CONCAT_WS( ' + ', m.prod_name, m.net_w, m.sale_price )
FROM milk_tea AS m;

SELECT m.prod_name, 
       m.net_w,  
       m.sale_price, 
       CONCAT_WS( ' ', m.prod_name, m.net_w, m.sale_price ) FROM milk_tea AS m;

5. 用SELECT在sql中做一个展示器

SELECT 6;
SELECT CONCAT('sql','训练营');
SELECT CONCAT_WS('+',1,2,3,4);
SELECT 15 * 5;

6. DISTINCT

（1）DISTINCT必须放在列名前面；
（2）不能部分使用DISTINCT，DISTINCT作用于所有列。
如果用DISTINCT后面跟了两个列名，那么如果这两行的内容均不完全相同，所有的行都会被检索出来。

7. ORDER BY

（1）ORDER BY一定放在最末尾，与分号;连写在一起！
（2）排序：升序ASC,降序DESC
（3）排序列可以不显示。因为排序是对整张表操作的。
（4）ASC, DESC 只作用与前一列
（5）数字中默认NULL是最小值

8. WHERE

（1）WHERE 子句在FROM之后，ORDER 之前
（2）WHERE过滤的是行，不影响列的展示。
（3）WHERE结果忽略NULL，直接不展示。
（4）常规运算符：等于(=)；不等于（!=或者<>）；大于(>)；小于(<)；大于等于(>=)；小于等于(<=)；

SELECT m.* 
FROM milk_tea AS m 
WHERE m.sale_price - m.in_price < 5;

（5）

SELECT m.* 
FROM milk_tea AS m 
WHERE m.prod_id = 2; 

SELECT m.* 
FROM milk_tea AS m 
WHERE m.prod_id = '2'; 

SELECT m.* 
FROM milk_tea AS m 
WHERE m.prod_name = '奶糖'; 

SELECT m.* 
FROM milk_tea AS m 
WHERE m.net_w = '150g';

（6）where空值遗漏问题
用IFNULL(参数1，参数2)，比如参数1是某个列名，那么当参数1代表的列值为空时，用参数2代替空值。参数1可以是列值运算。

（6.1） WHERE IFNULL(m.sale_price,0) = 15
是比较sale_price列值与15的大小，如果sale_price列值为NULL，那么用0代替列值，再用0与15进行比较，即此时NULL的那行数据不会显示出来。
（6.2） WHERE IFNULL(m.sale_price,0) != 15
如果sale_price列值为NULL，那么用0代替列值，再用0与15进行比较，此时NULL的那行数据会显示

SELECT m.* 
FROM milk_tea AS m 
WHERE IFNULL(m.sale_price,0) = 15; 

SELECT m.* 
FROM milk_tea AS m 
WHERE IFNULL(m.sale_price,0) != 15;

9. IFNULL(expr1,expr2)

（1）IFNULL(参数1，参数2) 参数1可以是运算：

SELECT m.* 
FROM milk_tea AS m 
WHERE IFNULL(m.sale_price * 0.9,0) < 10; 

SELECT m.* 
FROM milk_tea AS m 
WHERE IFNULL(m.sale_price,0) * 0.9 < 10;

8-2. 数据过滤 BETWEEN…AND…/IS/IS NOT

（1）BETWEEN [前端值] AND [后端值]
=> 前端、后端值均包含

SELECT m.* 
FROM milk_tea AS m 
WHERE m.in_price BETWEEN 5 AND 10;
 #>=5且<=10

（2）

SELECT m.* 
FROM milk_tea AS m 
WHERE m.sale_price IS NULL;

SELECT m.* 
FROM milk_tea AS m 
WHERE m.sale_price IS  NOT NULL;

SELECT m.* 
FROM milk_tea AS m 
WHERE IFNULL( m.sale_price, 10) = 10 ;

8-3. LIKE、通配符（_%）、转义字符（\）、AND、OR、IN

（1）下划线_代表：单个的任何字符（指一个字或字母或数字或字符）

使用： WHERE [列名 ] LIKE ‘…_…’
文本要加引号！

适合知道字符数目；知道字符位置

SELECT m.* 
FROM milk_tea AS m 
WHERE m.prod_name LIKE '奶_';

SELECT m.* 
FROM milk_tea AS m 
WHERE m.prod_name LIKE '薯_';

SELECT m.* 
FROM milk_tea AS m 
WHERE m.prod_name LIKE '_糖';

SELECT m.* 
FROM milk_tea AS m 
WHERE m.prod_name LIKE '__糖';
#最后一行写了两个下划线，要找的是三个字且最后一个字是糖

（2）百分号%代表：任意数目（包括0个）的任何字符
使用： WHERE [列名 ] LIKE ‘…%…’
适用范围不知道字符数目；知道字符位置
不会出现连续的两个百分号
注：以通配符开头，查询效率低

SELECT m.* 
FROM milk_tea AS m 
WHERE m.prod_name LIKE '方%'; 

SELECT m.* 
FROM milk_tea AS m 
WHERE m.prod_name LIKE '%糖';

SELECT m.* 
FROM milk_tea AS m 
WHERE m.prod_name LIKE '方%面';

SELECT m.* 
FROM milk_tea AS m 
WHERE m.prod_name LIKE '方便%面';

SELECT m.* 
FROM milk_tea AS m 
WHERE m.prod_name LIKE '奶%';

（3）转义字符
转义字符\ 含义是在它后面紧跟着的符号仅代表那个符号本身，比如 \ %指的就是百分号本身，而不是通配符%。

SELECT p.* 
FROM pet AS p 
WHERE p.owner LIKE 'Gw%_en' ; 
#%能匹配至少0个任意字符，_能匹配1个任意字符，合在一起用就是匹配至少1个任意字符

SELECT p.* 
FROM pet AS p 
WHERE p.owner LIKE 'Gw__en' ;
 #两个下划线匹配两个任意字符
 
SELECT p.* 
FROM pet AS p 
WHERE p.owner LIKE 'Gw%en' ; 
#1个百分号能匹配至少0个任意字符

SELECT p.* 
FROM pet AS p 
WHERE p.owner LIKE 'Gw\%_en' ; 
#\%代表百分号本身，1个下划线匹配1个任意字符

SELECT p.* 
FROM pet AS p 
WHERE p.owner LIKE 'Gw\%_en' ; 
#指第三位一定是百分号%，1个下划线匹配1个任意字符

SELECT p.* 
FROM pet AS p 
WHERE p.owner LIKE 'Gw\%\_en' ; 
#\%代表百分号本身，\_代表下划线本身，这个语句是匹配Gw%_en

（4）且AND:同时满足两个或多个条件；类似求交集

SELECT * 
FROM milk_tea AS m 
WHERE m.sale_price BETWEEN 5 AND 15; #1

SELECT * 
FROM milk_tea AS m 
WHERE m.sale_price >= 5;#2

SELECT * 
FROM milk_tea AS m 
WHERE m.sale_price <= 15;#3 

SELECT * 
FROM milk_tea AS m 
WHERE m.sale_price  >= 5 AND  m.sale_price <= 15; #4,结果与1一样

（5）OR两个或多个条件，至少满足一个；类似求并集

SELECT * 
FROM milk_tea AS m 
WHERE m.sale_price  >= 5 OR m.sale_price <= 15;
#AND、OR使用结果，空值依然会被忽视

（6）IN：明确而不连续的取值（过滤值明确；但不是连续范围）。
WHERE [列名] IN (值1，值2，…)

SELECT * 
FROM milk_tea AS m 
WHERE m.prod_name IN ( '薯片','棒棒糖','奶茶');

SELECT * 
FROM milk_tea AS m 
WHERE m.in_price IN ( 10.8,2.1,9.3); #明确不连续

SELECT * 
FROM milk_tea AS m 
WHERE NOT m.prod_name IN ( '薯片','棒棒糖','奶茶');

（7）NOT：否定一个或多个过滤条件；类似补集的概念
写法：WHERE NOT [条件1]
注：NOT是放在条件前！而且只否定一个条件，而且只否定紧跟着的那个条件。

区分位置：
（1） WHERE [列] IS NOT NULL
（2） WHERE NOT [条件]

SELECT * 
FROM milk_tea AS m 
WHERE NOT m.prod_name = '奶茶' 
      OR 
      NOT m.prod_name = '薯片' 
      OR 
      NOT m.prod_name = '棒棒糖' ;#1
#不要奶茶 或 不要薯片 或 不要棒棒糖，‘或’就是并集的概念
#即奶茶的补集 并 薯片的补集 并 棒棒糖的补集

SELECT * 
FROM milk_tea AS m 
WHERE  NOT m.prod_name = '奶茶';
#NOT放在条件前 #2 

SELECT * 
FROM milk_tea AS m 
WHERE NOT m.prod_name IN ( '薯片','棒棒糖','奶茶'); #结果与1一样
#不要奶茶、棒棒糖、薯片

SELECT * 
FROM milk_tea AS m 
WHERE NOT m.prod_name = '奶茶' 
      OR 
      m.prod_name = '薯片'  
      OR 
      m.prod_name = '棒棒糖' ;
#NOT放在条件前,只否定一个条件，而且只否定紧跟着的那个条件
#不要prod_name=奶茶 或 prod_name是薯片 或 prod_name=棒棒糖


SELECT * 
FROM milk_tea AS m 
WHERE NOT  m.prod_name = '奶茶' 
      OR 
      NOT  m.prod_name = '薯片' 
      OR 
      m.prod_name = '棒棒糖' ;
#不要奶茶 或 不要薯片 或 要棒棒糖，‘或’就是并集的概念

SELECT * 
FROM milk_tea AS m 
WHERE NOT m.prod_name = '奶茶' 
      AND 
      NOT m.prod_name = '薯片';
#不要奶茶 和 不要薯片

SELECT * 
FROM milk_tea AS m 
WHERE NOT m.prod_name = '奶茶' 
      AND  
      m.prod_name = '薯片'; #只有薯片
#不要奶茶 和 要薯片

（8）文字转换CONVERT(列 USING gbk)

SELECT m.* 
FROM milk_tea AS m 
ORDER BY CONVERT(m.prod_name USING gbk);

SELECT m.* 
FROM milk_tea AS m 
ORDER BY CONVERT(m.prod_name USING gbk) DESC;

10. 数据类型

数值型：INTEGER，DECIMAL，FLOAT
两个参数：(宽度，小数点位数)
ABS()，四舍五入函数ROUND()

文本型：CHAR，VARCHAR
一个参数：(字符个数)
字节数LENGTH() ，字符长度CHAR_LENGTH()，去右空值RTRIM()，去左空值LTRIM() ，变大写UPPER()，LOWER()

日期型：DATE，TIME，DATETIME
YEAR()，HUOR()，MONTH()，MONTHNAME()，取当前的时间NOW()，取当前的日期CURDATE()，取当前的时间CURTIME()，DATE_ADD()，DATE_SUB()

（1）整型integer
用法: INTEGER, INTEGER(10)显示10位数字
（2）定点型 decimal，多出的位数四舍五入
定义：固定位数的小数，默认参数为Decimal（10，0）
Decimal(2,2)。第一个2表示两个数，第二个2表示小数点后两位。这种数不会超过1，比如0.16总共是两位，恰好是在小数点后的两位。
DECIMAL(4,2)。总长度是4位，小数点是后2位，那么小数点前是两位，因为4-2=2。
（3）浮点型 float\double\real，多出位数五舍六入
定义：不固定位数的小数
不固定位数，直接写。FLOAT / DOUBLE / REAL
固定位数，标出精度。FLOAT(5,3)
解释：数字是10.0005，FLOAT（5，3），那么结果是10.000
（4）CHAR 定长字符串，CHAR(10)，标定长度为10，即字符个数为10
注：定长字符串，如果定了10个字符，写了4个字符，那么系统自动给补上6个空位。
（5）VARCHAR 可变长字符串，VARCHAR（100），标定长度位100，但可以不填满。

UTF-8中，1个中文字符占3个字节，1个数字或英文占1个字节。
gbk中，1个中文字符占2个字节

（6） DATE
用法：DATE
举例：‘2018-09-01’

（7）TIME
用法：TIME
举例：‘23：08：01’

（8）DATETIME
用法：DATETIME
举例：‘2018-09-01 23：08：01’

11. 函数

（1）绝对值函数ABS，例，ABS（-3）=3；
（2）平方根函数SQRT，例，SQRT(4)=2;
（3）指数函数EXP，例，EXP(4)=e^4；
（4）四舍五入函数ROUND，第二个参数是小数点位数，例，ROUND(1.234,2) = 1.23
（5）圆周率函数PI，例，PI（） = Π
（6）字符长度 CHAR_LENGTH，例，CHAR_LENGTH（‘数据’） = 2
（7）字节数 LENGTH, 例，LENGTH（’数据‘）=6；当然不同的编码语言中，中文文字代表的字节数不同。
（8）去除右边空值 RTRIM, 例，RTRIM(‘home ‘) = ‘home’
（9）去除左边空值 LTRIM，例，LTRIM(’ home’) = ‘home’
（10）大写字母转换 UPPER，例，UPPER(‘home’) = ‘HOME’
（11）小写字母转换 LOWER，例，LOWER(‘HOME’) = ‘home’
（12）获取具体日期段 YEAR、MONTH、DAY，例，
YEAR(‘2019-1-2’) = ‘2019’
（13）获取具体时间段 HOUR、MINUTE、SECOND，例，HOUR(‘14:05:55’) = ‘14’
（14）获取月份名称 MOTHNAME,例，MOTHNAME(‘2019-1-2’) = ‘JAN’
（15）获取当前日期时间 NOW，例，NOW() = systime
（16）获取当前日期 CURDATE，例，CURDATE() = ‘20190501’
（17）获取当前时间 CURTIME，例，CURTIME() = ‘15:10:39’ （18）时间增加 DATE_ADD,例，DATE_ADD(‘20190501’,INTERVAL 1 MONTH)
（19）时间减少 DATE_SUB,例，DATE_SUB(‘20190501’, INTERVAL 1 YEAR)

12. 聚合函数

聚合函数不检索，只汇总。
（1）COUNT()
COUNT()计数过程忽略NULL行，只作用非NULL行。
COUNT(col) 确定col这一列有多少行，不可以计数多列，参数只能是一个列名。
COUNT(*) 确定表中行的数目，COUNT(1) 确定表中第一列的行的数目。

COUNT(DISTINCT col) 对行去重计数

SELECT COUNT(DISTINCT m.sale_price) 
FROM milk_tea AS m;

（2）SUM（col）
SUM求和是直接跳过空值，忽略NULL行，计算其他所有非空值的和。

SELECT SUM(m.in_price) 
FROM milk_tea AS m;

SELECT SUM(m.in_price *  1.1) 
FROM milk_tea AS m;
#进价提升10%

SELECT SUM(m.sale_price) 
FROM milk_tea AS m;

SELECT SUM(m.sale_price-m.in_price) 
FROM milk_tea AS m;
#1,结果会忽略sale_price为NULL的值，原表中saleprice有一个空值。
#因为四则运算是不对空值行做计算，如果有空值，那么四则运算的结果就是空值
#SUM(m.sale_price-m.in_price)计算了7对值的差，然后求和
SELECT SUM(m.sale_price) - SUM(m.in_price) 
FROM milk_tea AS m;
#2，计算时不会忽略sale_price中NULL为空对应的行的in_price的值
#SUM求和是直接跳过空值，如果有空值，就忽略空值，计算其他所有非空值的和。SUM(m.sale_price)计算了7个值的和，SUM(m.in_price)计算了8个值的和

#如果对NULL做一个处理
SELECT SUM(IFNULL(m.sale_price,0) - m.in_price)
FROM milk_tea AS m;
#3,结果与2相同，因为sale_price列跳过NULL求和与将NULL变成0求和结果是一样的
#IFNULL(m.sale_price,0)是将NULL值变成0，参与四则运算后，再参与求和运算

SELECT SUM(m.sale_price) 
FROM milk_tea AS m 
WHERE m.net_w = '100g' ; 
#是先过滤后聚合，这条规则同样适用于AVG、MIN、MAX

执行顺序是：
确定表–过滤表–选取展示列
FROM–WHERE–SELECT

（3）AVG(col)
AVG(col)结果 = 列的SUM值 / 列的COUNT值
分母计数不对NULL计数，分子求和忽略NULL求和，即空值行被过滤掉。

SELECT AVG(m.sale_price) 
FROM milk_tea AS m; #1

SELECT SUM(m.sale_price)/COUNT(m.sale_price) 
FROM milk_tea AS m;#2,与结果1一样

SELECT SUM(m.sale_price)/COUNT(1) 
FROM milk_tea AS m;#3,与结果12不一样，
#因为sale_price有空值，再SUM和COUNT中都是直接跳过NULL，而第一列是prod_id没有空值，所以两者的行数不同
#所以求均值时，分母的COUNT一定要写整列的列名，否则有可能出错

（4）MAX（col）
MAX（col）忽略NULL行，因为NULL没法比较大小

SELECT MAX(m.sale_price) FROM milk_tea AS m;

（5）MIN(col)
MIN(col)忽略NULL行

SELECT MIN(m.sale_price) , MIN(DISTINCT m.sale_price) FROM milk_tea AS m; #说明DISTINCT再MIN、MAX中不起作用

（6）一行写多个聚合函数

SELECT 
      COUNT(m.sale_price) 
      , MIN(m.sale_price) 
      , MAX(m.sale_price) 
      , SUM(m.sale_price)
      , AVG(m.sale_price)
FROM milk_tea AS m;

13. 数据分组、分组过滤

GROUP BY 列；（对列分组）
SELECT 聚合键，聚合函数 FROM 表；
HAVING 聚合函数 +条件；（对分组后的结果进行过滤）
（聚合键在WHERE中过滤）

（1）数据分组结构：
SELECT … FROM … （这是必需子句）
WHERE +条件（这是过滤子句）
GROUP BY +聚合键（这是分组子句）
注： 聚合键所在列尽量不要出起别名
（2）结果：是多行数据；一行数据代表一个分组；SELECT展示的列必须与分组对应，聚合键决定分组标准，那么SELECT展示的列要有聚合键所在列、聚合函数作用得到的结果列；NULL值单独成为一个分组

SELECT 
      m.net_w , 
      SUM(m.sale_price)
FROM milk_tea AS m 
GROUP BY m.net_w;

SELECT 
      m.net_w , 
      SUM(m.sale_price) 
FROM milk_tea AS m 
WHERE m.net_w IN ('100g','150g')
GROUP BY m.net_w;

SELECT 
      m.net_w , 
      COUNT(m.sale_price) 
FROM milk_tea AS m 
WHERE m.net_w IN ('100g','150g')
GROUP BY m.net_w;

（3）分组过滤结构：
SELECT…
FROM…
WHERE + 过滤条件
GROUP BY + 聚合键
HAVING +聚合函数（这是分组筛选子句）；

SELECT brand,
       COUNT(*)
FROM milk_tea
WHERE cost > 5
GROUP BY brand
HAVING COUNT(*) > =2 ;

先WHERE 对整张表进行过滤，再GROUP BY对过滤的结果进行分组，再HAVING对分组结果进行筛选，结果是只剩下满足条件的组。

SELECT 
       m.net_w ,
       SUM(m.sale_price) 
FROM milk_tea AS m
GROUP BY  m.net_w;

SELECT 
       m.net_w , 
       SUM(m.sale_price) 
FROM milk_tea AS m
GROUP BY  m.net_w
HAVING SUM(m.sale_price) > 20;
#这里，Having后跟的是聚合函数结果列，即对分组结果进行过滤

#HAVING相当于在分组聚合的结果下（此时两列为m.net_w和SUM(m.sale_price)），取出此时表中SUM(m.sale_price)>20的行

SELECT 
      m.net_w , 
      SUM(m.sale_price) 
FROM milk_tea AS m
GROUP BY  m.net_w
HAVING m.net_w IN ('100g','150g');
#不推荐这种方法

SELECT 
      m.net_w , 
      SUM(m.sale_price) 
FROM milk_tea AS m
WHERE m.net_w IN ('100g','150g')
GROUP BY  m.net_w ;

SELECT 
      p.class
      , COUNT(1)
      , SUM(p.sale_price) 
      , AVG(p.sale_price)
FROM prod_info as P
GROUP BY p.class;

SELECT 
      p.class
      , COUNT(1)
      , SUM(p.sale_price) 
      , AVG(p.sale_price)
FROM prod_info as P
GROUP BY p.class
HAVING COUNT(1) > 4;

14-1. sql语句的书写顺序

SELECT FROM
WHERE （过滤的是行）
GROUP BY （分组条件）
HAVING （对分组结果过滤，筛选的是组）
ORDER BY （作为排序条件的组） ;

14-2. sql语句的执行顺序

FROM （确定从哪个表中选数据）
WHERE （确定选什么数据）
GROUP BY （对选出来的数据进行分组）
HAVING （对分组的数据进行组的筛选）
SELECT （然后对满足条件的具体的列做一个展示）
ORDER BY （展示的时候进行排序）

注：尽量不要对GROUP BY使用别名，但是ORDER BY可以使用别名。

多表查询+联结：

----多表查询
–子查询：嵌套在其他查询中的查询
标量子查询：只返回一行一列的子查询（得到常数）
关联子查询：组内比较，使用常数，用在WHERE在组内比较
普通子查询：返回一整列，用IN得到二维表，即心的检索表
----联结（列之间组合）
内联结：INNER JOIN… ON
–外联结：
左外联结：LEFT OUTER JOIN … ON
右外联结：RIGHT OUTER JOIN…ON
全外联结：FULL OUTER JOIN…ON
–组合（行之间的组合）
去重组合：UNION
不去重组合：UNION ALL

15. 子查询

例，milk_tea 表中saleprice 的价格大于milk_tea表中的奶茶的价格

SELECT * 
FROM milk_tea AS m1 
WHERE m1.sale_price > (
	                SELECT 
	                m.sale_price 
				    FROM milk_tea AS m 
					WHERE m.prod_name = '奶茶');#2

例，增加常数列为奶茶的价格

SELECT m.* ,
       (  SELECT m.sale_price 
		  FROM milk_tea AS m 
		  WHERE m.prod_name = '奶茶') AS '奶茶价格' 
FROM milk_tea AS m;

例，要查询平均销售价格>milk_tea表中的奶茶价格

SELECT p.class , AVG(p.sale_price) 
FROM prod_info AS p
GROUP BY p.class 
HAVING AVG(p.sale_price) > (SELECT m.sale_price 
						   FROM milk_tea AS m 
						   WHERE m.prod_name = '奶茶');

例,选出销售价格大于平均售价的日用品

SELECT *
FROM prod_info AS p
WHERE p.class = '日用品' 
      AND p.sale_price > (SELECT AVG(p1.sale_price)
                          FROM  prod_info AS p1
                          WHERE p1.class = '日用品' 
                         );

例，选出每个品类中售价大于该品类平均价格的商品

SELECT *
FROM prod_info AS p
WHERE p.sale_price > (SELECT AVG(p1.sale_price)
                      FROM  prod_info AS p1
                      WHERE p1.class = p.class 
                      GROUP BY p1.class);

例，

SELECT * 
FROM milk_tea AS m1 
WHERE m1.prod_name IN (SELECT m2.prod_name 
                       FROM milk_tea AS m2 
					   WHERE m2.sale_price = 15);

例，

SELECT * FROM ( SELECT p.brand , p.prod_name ,p.sale_price 
                FROM prod_info AS p 
                WHERE p.prod_name = '抽纸') AS b;
#注：派生表必须要有别名，所以 AS b 这里不能省略。

例，

SELECT * 
FROM ( 
        SELECT p.type , p.brand , p.prod_name ,p.sale_price 
        FROM prod_info AS p 
        WHERE p.prod_name = '抽纸') AS b
WHERE b.sale_price > 26;

16. 表联结

关系型数据库是将所有的数据分解为多个表，表之间互相联系。
16-1内联结
（1）结构：
select 列名（可以是来自不同表的多个列）
from 表1，表2 （可以拓展到更多表）
where 表1.列a = 表2.列b
（这里where是说明联结方式和联结字段，这是内联结）

SELECT p.* , s.*
FROM prod_info AS p , supplier_info AS s
WHERE p.supplier_id = s.supplier_id;

（2）联结前提：必须有共同列；
（3）创建联结规则：列出所有表、定义所有表关系
（4）联结结果：所有表的所有列、共同列重复行；
（5）通用内联结关键字：INNER JOIN, ON

SELECT p.* , l.*
FROM prod_info AS p  INNER JOIN order_list AS l
ON p.prod_id = l.prod_id #联结
   AND l.order_id = '20190403001'; #过滤条件

16-2 自联结
16-3 外部联结
（1）联结规则：列出所有表、定义所有表关系
（2）外联结前提：有共同列；但不要求共同的列要一摸一样。
（3）联结结果：所有表所有列、包含相关表中没有关联行的行
即：所有表所有列+包含相关表中没有关联行的行
（4）连接方式：
i.左外部联结：包含左边表的所有行
关键字是：LEFT OUTER JION
(就是用这个关键字代替之前的INNER JION)
ii. 右外部联结：包含右边表的所有行
将客户信息放左边，流水信息放右边
关键字是：RIGHT OUTER JION
iii. 全外部联结：包含两边表的所有行
(无所谓两个表的位置，比如可能两个表的信息都不是完全对应的，都有空缺。)
关键字是：FULL OUTER JION

SELECT c.* , l.*
FROM cust_info AS c LEFT OUTER JOIN order_list AS l 
#左外联结，保留左边所有用户信息
ON c.cust_id = l.cust_id
   AND l.order_id LIKE '20190407%';

SELECT c.* , l.*
FROM cust_info AS c LEFT OUTER JOIN order_list AS l 
#左外联结，保留左边所有用户信息
ON c.cust_id = l.cust_id
   AND l.order_id LIKE '20190401%';

SELECT n.cust_id,  COUNT(n.prod_id)
FROM (
       SELECT c.cust_id,
			  c.cust_name,	
			  l.prod_id,
			  l.prodname,
			  l.order_id
       FROM cust_info AS c LEFT OUTER JOIN order_list AS l 
       ON c.cust_id = l.cust_id
           AND l.order_id LIKE '201904%'
	) AS n
GROUP BY n.cust_id;

SELECT n.cust_id, n.prodname, COUNT(n.prod_id)
FROM (
       SELECT c.cust_id,
			        c.cust_name,	
			        l.prod_id,
							l.prodname,
							l.order_id
       FROM cust_info AS c LEFT OUTER JOIN order_list AS l 
       ON c.cust_id = l.cust_id
           AND l.order_id LIKE '201904%'
			 ) AS n
GROUP BY n.cust_id, n.prodname;

SELECT c.cust_id,
	   COUNT(l.prod_id)
FROM cust_info AS c LEFT OUTER JOIN order_list AS l 
ON c.cust_id = l.cust_id
   AND l.order_id LIKE '201904%'
GROUP BY c.cust_id;

17.组合查询

1.组合查询：在查询结果上做处理。
UNION是先查询结果，再进行组合。有多条select语句。
2.UNION使用要求：
（1）多个select语句中的列字段必须一样；表达式、聚集函数也要一样。否则组合结果没意义
（2）数据类型必须兼容，数据类型是确定且唯一。允许隐式转换，指的是字符串的数字和数值数字之间可以隐式转换。
（3）最终的字段名由第一条select语句决定。
（4）最后一条select语句可以只用order，此时order是作用与整个检索结果的排序。
（5）表可以不一样，但是字段的意义要一样，否则合并起来没有意义。比如选择产品名称字段和产品价格字段拼接起来没有意义。有意义的情况是指表1是产品1的价格字段、表2是产品2的价格字段。
UNION执行注意事项：
（1）自动过滤重复行。
比如表1中选出3个商品，表2中选出3个商品，那么有一个商品是重复的，将只显示一条该商品，即最后只有5条记录。
（2）两条及两条以上的select语句，每两条UNION一次。
指 select … UNION select… UNION select …UNION
（3）UNION ALL关键字是不自动过滤重复行。

SELECT *
FROM order_list AS l 
WHERE l.order_id LIKE '20190409%'
UNION
SELECT *
FROM order_list AS l 
WHERE l.order_id LIKE '20190407%';

SELECT *
FROM order_list AS l 
WHERE l.order_id LIKE '20190407%'
UNION ALL
SELECT *
FROM order_list AS l 
WHERE l.order_id LIKE '20190407%';
#重复的也展示出来

增删改

----数据插入
完整插入：INSERT INTO 表 VALUES(值，值);
部分插入：INSERT INTO 表（字段，字段）VALUES（值，值）;
插入查询语句：INSERT INTO 表 SELECT 列 FROM 表；
注意空值的处理
----数据更新
UPDATE 表
SET 字段 = 常数或者NULL
WHERE 过滤条件；
----删除数据
DELETE FROM 表
WHERE 条件；
----清空表
TRUNCATE TABLE 表；

18. 数据插入

因为会改变原表，因此操作时，要先测试。
1.语句结构:
INSERT INTO 表名 VALUES（插入值）
注：插入值可以是完整的行，即每个列都有值；也可以不完整。
注：主键列的值是不能重复，也不能是空值。
这两种的语句区别是：
（1）插入完整的行：
INSERT INTO 表名 VALUES(插入值)

INSERT INTO pro_info 
VALUES(1,'火腿肠','55g','2019/2/4',12,15.5.21)

INSERT INTO prod_info2 
values('T0092','测试商品','test','test','test',12,3,'NJ0109');

（2）插入不完整的行：
INSERT INTO 表名（插入字段名） VALUES （字段对应的插入值）
其中，字段名和插入值一一对应

INSERT INTO pro_info(prod_id, prod_name) 
VALUES (1,'火腿肠')

INSERT INTO prod_info2(prod_id, prod_name, brand, type) 
VALUES ('T0093','测试商品','test','test');

（3）插入值可以是空值，直接在VALUES不填充内容。
注：要与表格中列的格式相对应，比如字符串就用引号，数字就用数字，日期就用日期。
例，插入完整的检索列
用select 字段 from… where … group by …替代VALUES+插入值

INSERT INTO prod_info2 
SELECT CONCAT('f',p.prod_id),
       p.prod_name, 
       p.brand,
       p.type, 
       p.class,
       p.cost,
       p.sale_price,
       p.supplier_id
FROM prod_info AS p 
WHERE p.prod_id = '10001';

注：这里用CONCAT进行拼接的原因？
因为这里的例子中 pro_info表、prod_info2表是一摸一样的，包括主键。sql 表中主键值唯一，且不能重复，所以需要在插入字段时，拼接点内容让主键变化，这里只是为了展示所以这么拼接，没有别的含义。

INSERT INTO prod_info2(prod_id ,prod_name, brand, type) 
SELECT CONCAT('g',p.prod_id),
       p.prod_name, 
       p.brand,
       p.type
FROM prod_info AS p 
WHERE p.prod_id = '10001';

19. 数据更新

因为会改变原表，因此操作时，要先测试。
结构：
UPDATE 表名
SET 字段 = 值
WHERE 过滤条件
注：
（1）等号=是赋值符号
（2）更新的表必须是真实的表。比如不能是联结表，联结表是虚拟表。
（3）set子句中间用 逗号！！！ 隔开
（4）要用过滤条件指定更新的行，否则全部更新
（5）可以更新值为空值，但必须原表允许该列为空值
比如：set goal = NULL

19-1 修改数据

#先测试过滤条件
SELECT p2.* 
FROM prod_info2 AS p2
WHERE p2.prod_id LIKE 'g%';

#再更新

UPDATE prod_info2 
SET class = '零食'
WHERE prod_id LIKE 'g%';

#测试
SELECT p2.* 
FROM prod_info2 AS p2
WHERE p2.prod_id LIKE '3%';

#更新
UPDATE prod_info2 
SET class = '饮料'
WHERE prod_id LIKE '3%';

#检查是否更新
SELECT p2.* 
FROM prod_info2 AS p2
WHERE p2.prod_id LIKE '3%';

#测试
SELECT *
FROM prod_info2 AS p2;

#更新价格
UPDATE prod_info2
SET sale_price = 0.9 * sale_price;

#检查
SELECT *
FROM prod_info2 AS p2;

#测试
SELECT p2.* 
FROM prod_info2 AS p2
WHERE p2.prod_name = '抽纸' OR p2.class = '饮料';

#更新
UPDATE prod_info2
SET sale_price = sale_price * 0.9
WHERE prod_name = '抽纸' OR class = '饮料';

#检查
SELECT p2.* 
FROM prod_info2 AS p2
WHERE p2.prod_name = '抽纸' OR p2.class = '饮料';

#测试
SELECT p2.sale_price, p2.cost
FROM prod_info2 AS p2;
#更新
UPDATE prod_info2 
SET sale_price = sale_price / 0.9,
    cost = cost / 0.9;

19-2 表之间两列数值替换
在更新过程中进行联结，用INNER JOIN

#表之间数据替换--先关联，后替换
#比如把p2表的采购价格替换为p的销售价格
#先检索测试
SELECT p2.* , p.*
FROM prod_info as p INNER JOIN prod_info2 AS p2
WHERE p2.prod_name = p.prod_name
 AND p2.brand = p.brand
 AND p2.type = p.type;
 
#更新(在更新语句之后直接加上联结语句，最后用WHERE加上联结条件)

UPDATE prod_info2 AS p2
INNER JOIN prod_info AS p
SET p2.cost = p.sale_price
WHERE p2.prod_name = p.prod_name
 AND p2.brand = p.brand
 AND p2.type = p.type ;

20. 数据删除

因为会改变原表，因此操作时，要先测试。
语句结构：
DELETE FROM 表名 WHERE 过滤条件
注：删除的是表中数据，但表仍然存在
例

#测试
SELECT * FROM prod_info2 p2 
WHERE p2.prod_name = '测试商品';
#6条数据

DELETE FROM prod_info2 
WHERE prod_name = '测试商品';

#删除表中所有数据
DELETE FROM prod_info2;
#等价于
TRUNCATE TABLE prod_info2 ;

DDL

----创建表
CREATE TABLE 表（
字段数据类型字段属性）；
–复制表
CREATE TABLE 新表
AS SELECT 列 FROM 已有表 WHERE 条件；
–重命名表
RENAME 表 TO 新表名；
----修改表
–增加字段
ALTER TABLE 表
ADD 字段数据类型字段属性；
–删除字段
ALTER TABLE 表
DROP 字段, DROP 字段；
（还有一种写法增加一个关键字COLUMN）
ALTER TABLE 表
DROP COLUMN 字段, DROP COLUMN 字段；
–修改字段内容
ALTER TABLE 表
MODIFY COLUMN 字段数据类型字段属性；
----删除表
DROP TABLE 表名；
----视图
–创建视图
CREATE VIEW 视图名 AS SELECT 列 FROM 已有表 WHERE 条件；
–删除视图
DROP VIEW 视图名;
----存储过程PROCEDURE
通过BEGIN END创建
通过CALL调用

21.创建表CREATE TABLE

语句结构：
CREATE TABLE 表名
（字段名1 数据类型属性，
字段名2 数据类型属性
）

1.表名要求：英文字符开头，由字母、数字、下划线组成，英文通常小写。
2.字段逗号隔开，字段名唯一。
3.字段名、数据类型是必须的项，属性不是必须项，中间空格隔开。
4.属性：是否允许空值NULL，默认允许，设置成不允许空值就是用NOT NULL；默认值设置用DEFAULT设置，必须是常数。

CREATE TABLE pet2
            ( name  VARCHAR(225) NOT NULL,
			  owner VARCHAR(225) NOT NULL,
			  species VARCHAR(225),
			  sex   CHAR(1),
			  birth DATE,
			  death DATE
			);

CREATE TABLE pet2
            (name VARCHAR(225) NOT NULL, #不能为空
			 owner VARCHAR(225) DEFAULT 'police',
			 #默认值为police，也可以后期设置为空NULL
			 species VARCHAR(225),
			 sex CHAR(1),
			 birth DATE,
			 death DATE
		    );
SELECT * FROM pet2; #检查创建成功与否
#插入数据
INSERT INTO pet2 
VALUES('Broswer2','Diane2','dog','m','2000-02-20','2015-08-20';

INSERT INTO pet2(name,owner,species) 
VALUES('Broswer3',NULL,'dog');
#这里的NULL就是类似赋值，只要字段没有要求不能为空值（NOT NULL），就可以将字段名设定为空值NULL

INSERT INTO pet2(name,owner,species,sex) 
VALUES('Broswer4','d2','dog','f');

SELECT * FROM pet2;

22. 复制表结构

复制表mysql操作语句：
CREATE TABLE 表名 AS 目标二维表；
（AS理解为如同）
注：通过select语句得到的就是目标二维表，表结构由select字段决定，表记录由select中的where过滤条件决定。

CREATE TABLE pet4
AS 
  SELECT * FROM pet2;

CREATE TABLE pet4
AS 
  SELECT p2.name,p2.owner FROM pet2 AS p2;

#只要某个表中的两个字段，但是不要里面的记录，即一个空表，但要字段名和别人一样
CREATE TABLE pet4
AS 
  SELECT p2.name,p2.owner FROM pet2 AS p2 WHERE 1 = 2 ;

解释：因为SELECT p2.name,p2.owner FROM pet2 AS p2 WHERE 1 = 2中，1=2是一个伪命题，pet2表中没有满足这个条件的数据，所以取出来的数据也是空

23. 更新表结构ALTER TABLE

表更改不能撤销，建议做完整备份
关键字：ALTER TABLE
语句：ALTER TABLE 表名 + 操作
三种操作：
（i）添加字段ADD
使用：ALTER TABLE 表名 ADD 字段名数据类型属性；

#更新表，增加字段
ALTER TABLE pet4 
ADD sex CHAR(1);

ALTER TABLE pet4 
ADD birth DATE NOT NULL;

SELECT * FROM pet4;

（ii）删除一个或多个字段DROP（或者用DROP COLLUMN），字段名用逗号连接
使用：
ALTER TABLE 表名 DROP 字段名；
ALTER TABLE 表名 DROP COLUMN 字段名；

ALTER TABLE pet4 
DROP birth ;

SELECT * FROM pet4;

ALTER TABLE pet4 
DROP COLUMN birth;

SELECT * FROM pet4;

ALTER TABLE pet4 
DROP birth, DROP sex;

SELECT * FROM pet4;

（iii）修改字段MODIFY COLUMN
使用：ALTER TABLE 表名 MODIFY COLUMN 字段名数据类型属性；

ALTER TABLE pet4 
MODIFY COLUMN sex VARCHAR(255);

ALTER TABLE pet4 
MODIFY COLUMN sex CHAR(1) NOT NULL ;

24. 删除表DROP TABLE

语句：
DROP TABLE 表名；
注：DROP TABLE没有确认、没有撤销、永久删除，建议做完整备份。
例：

DROP TABLE pet4;

25. 表重命名RENAME TABLE

语句：
RENAME TABLE
表名1 TO 新表名1，
表名2 TO 新表名2，
…；

注:可以批量修改，从左往右依次进行。

DCL

commit
ROLLBACK
SAVEPOINT
RELAESE SAVEPOINT 保留点名

26. 事务处理

1.管理对象（对数据修改的操作）：INSERT、UPDATE、DELETE
2.关键字：COMMIT、ROLLBACK、SAVEPOINT、ROLLBACK、RELEASE
3.事务管理通过BEGIN开始，以END结束，并且在进行事务管理的时候是可以对是否提交进行设定。在非事务管理状态下运行代码，直接提交结果，因此，此时回退操作无效。
在MySQL中，使用 SET AUTOCOMMIT = 0; 代表取消自动提交操作

SET AUTOCOMMIT = 0;
#0代表取消自动提交机制

提交COMMIT：将sql语句的执行结果写入数据库表中
回退ROLLBACK：撤销sql语句的执行结果
保留点SAVEPOINT：时间点的备份，可以对其发布回退
注：CTREATE、DROP不可以回滚

4.举例：
begin（开始管理事务）
删除T3行
savepoint sp1（#1）
删除slim行
savepoint sp2
插入test3行
回退到sp1（恢复到了#1状态）
释放sp2（RELESE savepoint sp1）
释放sp1（RELESE savepoint sp2）
回退到sp1（ROLLBACK TO sp1）
出现ERROR（因为没有sp1了）
此时commit
结果仍然是#1的状态。

27. 对字段的约束

27-1. 主键（PRIMARY KEY）

1.作用：确保数据的唯一性
2.特征：
（1）每行都有；
（2）行的唯一标识（主键之间不能有重复,，即主键所在的列中每个值都是唯一的）；
（3）不能修改；
（4）删除后不再分配。
（不同DBMS要求不同，要自己测试）
3.主键不能为空，建表的时候就要标注。
4.建表时设定主键

<字段名> <数据类型> NOT NULL UNIQUE

5.建表后增加主键
ALTER TABLE <表名>
ADD CONSTRAINT PRIMARY KEY(<列名>)

27-2. 外键(foreign key)

（1）确保引用完整性
（2）特征：该表与其他表的关联；外键一定是其他表的主键.
（3）建表时设定外键
<字段名><数据类型> NOT NULL
REFERENCES KEY <关联表>（关联列）

（4）建表后增加外键
ALTER TABLE <表名>
ADD CONSTRAINT FOREIGN KEY(<列名>)
REFERENCES <关联表>（关联列）

27-3. 唯一约束UNIQUE**

1.作用：确保行数据唯一性（有点像主键的意思）
2.具体特征：
（1）可以有多个字段（和主键的不同点）
（2）可以是NULL
（3）可以被更新
（4）可以重复使用（？）
（5）不能定义外键
3.语句：
（1）<字段名> <数据类型> UNIQUE
（2）ALTER TABLE <表名>
ADD CONSTRAINT UNIQUE(<列名>)

27-5. 检查约束CHECK

1.作用：确保该列数据满足指定条件
2.指定条件：
（1）值的范围（>0）
（2）特定值（性别M/F）
3.语句：
（1）<字段名> <数据类型> NOT NULL
CHECK ( <字段> (限定条件) ）

（2）ALTER TABLE <表名>
ADD CONSTRAINT CHECK ( <字段> （限定条件）)

27-6. 默认约束DEFAULT**

1.作用：指定默认值
2.语句：
<字段名> <数据类型> DEFAULT<默认值>

28. 索引

建立索引语句：CREATE INDEX 索引名 ON 表名（列名）;

索引的本质是一种排序，在某列上建立索引，即对该列进行“排序”
（1）可以快速定位、快速排序
（2）可以定义多列，实现组合排序。
使用注意：
（1）要求数据的唯一性较好（某列数据重复的多进行排序的意义不大）

（2）索引提高查询、排序速度，适用于过滤或者排序较多的字段，索引名不能重复

（3）数据修改（增删改）操作时，需要重新更新索引，效率低
（4）占用大量内存（所以要科学使用索引）

29. 数据库安全

限制数据库操作
（1）表的访问权限（可以用VIEW PROCEDURE实现）
（2）表字段的访问权限（可以用VIEW PROCEDURE实现）
（3）表的修改权限(可以用GPANT REVOKE实现)（比如对不同账号采用不同的使用权限）

数据库管理员（运维工程师）–从事管理和维护DBMS 的相关工作人员的统称
管理内容：
数据库设计、数据库测试、数据库部署、数据库交付
核心目标：
保证数据库管理系统的稳定性、安全性、完整性、高性能。
（稳定性、安全性、完整性是基本的工作，而高性能是持续优化和努力的方向）

30. 数据库拓展

数据分SQL、NOSQL

SQL就是结构化数据，如关系型数据库
特点：存在关系模型，有清晰的结构划分
数据化查询数据规模有限、增长速度有预期
NOSQL 包括半结构化数据、非结构化数据
2.1 半结构化数据，包括EMAIL、XML文件
特点：非关系模型，有基本固定结构模式
解释：EMAIL有收件地址、收件人发件地址、发件人这些是固定的，但是内容是不固定的
2.2 非结构化数据，包括WORD、PPT、图片
特点：没有固定模式，采用内容管理
对NOSQL数据对结构化查询的支持比较差，而且海量数据增长速度难以预期，但是要挖掘信息还是需要这种海量的信息支持

31. HIVE

HIVE是数据系统仓库工具
HIVE能做的事情：将操作型数据，抽取出来，进行数据清洗，进行加工、汇总、整理，最后将所有数据放入数据仓库中。

Q1：为什么要进行加工整理这些工作
A1：保证数据一致性,因为不同的系统数据抽取时的数据口径可能不太一样

Q2：原始数据与数据仓库区别？
A2：数据仓库中的数据是面向主题的（比如按业务分类，或者按着使用目分类），经过事务处理的数据进入数据仓库是为了进行决策分析的。整个过程可能就是将NONSQL的数据经过加工后变成SQL数据，数据仓库中的数据就是结构型的数据。

数据仓库工具HIVE有什么特性？
（1）HIVE的底层架构是Hadoop，Hadoop这种结构就决定了HIVE能为海量数据提供存储和计算。
（2）HIVE提供工具ETL（ETL 数据提取转化加载），ETL可以存储、查询、分析存储在Hadoop中的大规模数据。
（即HIVE 最终得到的是结构型数据（SQL型），但是HIVE使用的底层逻辑是Hadoop海量数据（可以是NOSQL型）。HIVE架起了SQL与NOSQL之间的桥梁）
（3）在HIVE中使用的是一种类似SQL的查询语言，称为HQL。（也是结构化的查询语言）