SQL基础教程–MICK著。

SELECT 语句的顺序： FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY

SQL基础

DDL(Data Definition Language，数据定义语言)

CREATE:创建数据库和表等对象
DROP: 删除数据库和表等对象
ALTER: 修改数据库和表等对象的结构

DML(Data Manipulation Language，数据操纵语言)

SELECT:查询表中的数据
INSERT:向表中插入新数据
UPDATE:更新表中的数据
DELETE:删除表中的数据

DCL(Data Control Language，数据控制语言)

COMMIT: 确认对数据库中的数据进行的变更
ROLLBACK :取消对数据库中的数据进行的变更
GRANT: 赋予用户操作权限
REVOKE: 取消用户的操作权限

SQL的基本书写规则：

;结尾
关键字大写
表名首字母大写
其余列名小写
常数书写方式固定（字符串、日期、数字）

CREATE DATABASE 语句

CREATE DATABASE shop; # 创建数据库 shop

CREATE TABLE语句

# 创建Product表
CREATE TABLE Product 
(product_id          CHAR(4)         NOT NULL,
product_name         VARCHAR(100)    NOT NULL,
product_type         VARCHAR(32)     NOT NULL,
sale_prioce          INTEGER        ,
purchase_price       INTEGER        ,
regist_date DATE     DATE           ,
PRIMARY KEY (product_id));

命名规则

我们只能使用半角英文字母、数字、下划线(_)作为数据库、表和列的名称
名称必须以半角英文字母开头
名称不能重复。

数据类型的指定

INTEGER 型用来指定存储整数的列的数据类型(数字型)，不能存储小数。
CHAR型固定长度字符型。
VARCHAR型可变长字符串
DATE型用来指定存储日期(年月日)的列的数据类型

约束设置

NOT NULL约束
主键约束：唯一识别出表中观测数据的一组列

表的删除(DROP TABLE语句)

# 删除 Product 表
DROP TABLE Product;

添加列的ALTER TABLE语句

ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);

删除列的ALTER TABLE语句

ALTER TABLE Product DROP COLUMN product_name_pinyin;

向 Product 表中插入数据

-- DML:插入数据
START/BEGIN TRANSACTION;

INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤','衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具',6800, 5000,'2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');
           
COMMIT;

列的查询

SELECT product_id, product_name, purchase_price FROM Product;
SELECT *
    FROM Product;

为列设定别名 : 使用 AS 关键字为列设定别名

SELECT product_id AS id, product_name AS name, purchase_price AS price
    FROM Product;

#设定中文别名
SELECT product_id AS "商品编号", product_name AS "商品名称",
       purchase_price AS "进货单价" 
    FROM Product;

查询常数

SELECT '商品' AS string, 38 AS number, '2009-02-24' AS date, product_id, product_name
    FROM Product;

DISTINCT去重—DISTINCT 关键字只能用在第一个列名之前

DISTINCT 删除 product_type 列中重复的数据
SELECT DISTINCT product_type FROM Product;

WHERE 语句来选择记录

# 选取 product_type 列为'衣服'的记录的 
SELECT product_name, product_type 
    FROM Product
 WHERE product_type = '衣服';   --条件表达式

运算符

算术运算符（+， -, *, /）
比较运算符（> , >=, =, <, <=, <>), 在where子句生成表达式
逻辑运算符

NOT, AND, OR
        - NOT 不能单独使用 
        - AND 运算符优先于 OR 运算符 
        - AND逻辑积，OR逻辑和
IN, NOT IN, EXIST, NOT EXIST
TRUE, FALSE, UNKNOWN

聚合与排序

聚合函数 ：阶数会改变，多行变成一行 COUNT:计算表中的记录数(行数) SUM : 合计值 AVG : 平均值 MAX: 最大值 MIN : 最小值

注：

1.COUNT(*)包含缺失值的行 2.COUNT(COLUMN)不包含缺失值的行 3.平均是会忽视缺失值及其个数 4.MAX/MIN 函数几乎适用于所有数据类型的列。SUM/AVG 函数只适用于数值类型的列。

对表进行分组

使用聚合函数和GROUP BY子句时需要注意以下4点。

只能写在SELECT子句之中
GROUP BY子句中不能使用SELECT子句中列的别名
GROUP BY子句的聚合结果是无序的
WHERE子句中不能使用聚合函数

GROUP BY 子句中指定的列称为聚合键或者分组列

##与聚合函数和GROUP BY子句有关的常见错误

多写了列
用了别名
where子句用了聚合函数，这种情况应该使用having
group by后无序状态

为聚合结果指定条件

where指定行的条件
having指定组的条件

使用 HAVING 子句时 SELECT 语句的顺序

SELECT → FROM → WHERE → GROUP BY → HAVING

HAVING 子句的构成要素

常数
聚合函数
GROUP BY子句中指定的列名(即聚合键)

对查询结果进行排序

ORDER BY子句 —-DESC /ASC

子句的书写顺序

1.SELECT子句→2.FROM子句→3.WHERE子句→4.GROUP BY子句→ 5. HAVING 子句 → 6. ORDER BY 子句

注意：

GROUP BY不能使用别名
ORDER BY可以使用别名

ORDER BY子句中可以使用的列

SELECT子句中未包含的列也可以在ORDER BY子句中使用
ORDER BY子句中也可以使用聚合函数

第 4 章数据更新

数据的插入(INSERT 语句的使用方法)
数据的删除(DELETE 语句的使用方法)
数据的更新(UPDATE 语句的使用方法)

INSERT 语句

列清单——–值清单

INSERT INTO <表名> (列1, 列2, 列3, ......) VALUES (值1, 值2, 值3, ......);

从其他表中复制数据

INSERT INTO ProductCopy (product_id,)
SELECT product_id, product_name
    FROM Product;
按列复制的
插入是按行插入

INSERT语句每次执行一行数据的插入。

数据的删除

DROP TABLE 语句可以将表完全删除
DELETE 语句会留下表(容器)，而删除表中的全部数据
drop table删除表或者列，DELETE是按行删除
搜索型 DELETE即添加where条件表达式
DELETE语句中不能使用GROUP BY、 HAVING和ORDER BY三类子句，

数据的更新(UPDATE语句)

更新是按列更新，即按集合元组更新

指定条件的 UPDATE 语句(搜索型 UPDATE)

多列更新

事务：在同一个处理单元中执行的一系列更新处理的集合

四种特性：

原子性(atomicity)：事务要么全执行、要不全不执行
一致性(consistency)：受到数据库提前设置的约束，如：主键/NOT NULL
隔离性(isolation)：事务之间互不干扰，不能嵌套，是独立的，不提交事务，别人看不见
持久性(durability)：很好的保存、恢复特性

语法：

案例：
BEGIN /START TRANSACTION;
     -- 将运动 T 恤的销售单价降低1000 日元
     UPDATE Product 
        SET sale_price = sale_price - 1000 
     WHERE product_name = ' 运动 T 恤 '; 
COMMIT;

BEGIN TRANSACTION;
     -- 将 T 恤衫的销售单价上浮 1000 日元
    UPDATE Product 
        SET sale_price = sale_price + 1000 
    WHERE product_name = 'T 恤衫 '; 
COMMIT;

第 5 章复杂查询 —-视图子查询关联子查询

视图：临时的表，保存的是 SELECT 语句

优点：

节省储存空间
提高SELECT效率

语法：

创建视图：
CREATE VIEW 视图名称 (< 视图列名 1>, < 视图列名 2>, …… ) 
AS
<SELECT 语句 >

创建案例+定义视图：
CREATE VIEW ProductSum (product_type, cnt_product) 
AS
SELECT product_type, COUNT(*) 
   FROM Product
 GROUP BY product_type;

使用视图：
SELECT product_type, cnt_product) 
   FROM ProductSum;

流程：

执行定义视图的 SELECT 语句以视图为基础创建视图的多重视图 —–降低性能

视图缺点：

定义视图时不能使用ORDER BY子句可以更新视图的条件： 1.SELECT 子句中未使用 DISTINCT 2.FROM 子句中只有一张表 3.未使用GROUP BY子句 4.未使用 HAVING 子句

删除视图：DROP VIEW语句

子查询：就是一次性视图(SELECT语句)。

子查询的层数原则上没有限制

标量子查询就是返回单一值的子查询。
返回单一值需要+groupby/sum等聚合函数
在WHERE子句中不能使用聚合函数

标量子查询的书写位置

能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY子句，几乎所有的地方都可以使用。

关联子查询 :用聚合函数分组

SELECT product_type, product_name, sale_price
    FROM Product AS P1 1
 WHERE sale_price > (SELECT AVG(sale_price)
                         FROM Product AS P2 
                     WHERE P1.product_type = P2.product_type   #   该条件就是成功的关键!
                        GROUP BY product_type);

group by：返回一行
关联子查询：组内比较
partition by：分组排序

函数的种类

算术函数（用来进行数值计算的函数）
字符串函数（用来进行字符串操作的函数）
日期函数（用来进行日期操作的函数）
转换函数（用来转换数据类型和值的函数）
聚合函数（用来进行数据聚合的函数）
算术函数( + - * /)

ABS——绝对值
SELECT m,
        ABS(m) AS abs_col 
  FROM SampleMath;

MOD——求余// 计算除法（n ÷ p）的余数
SELECT n, p,
        MOD(n, p) AS mod_col 
  FROM SampleMath;

ROUND——四舍五入   // 对 m 列的数值进行 n 列位数的四舍五入处理
SELECT m, n,
        ROUND(m, n) AS round_col 
    FROM SampleMath;

字符串函数

#      拼接字符串  | | ， MySQL使用 CONCAT 函数    SQL Server使用“+”   效果：abc + de = abcde
// MySQL
SELECT str1, str2, str3,
        CONCAT(str1, str2, str3) AS str_concat 
    FROM SampleStr;

// Oracle  DB2  PostgreSQL
SELECT str1, str2, str3,
        str1 | | str2 | | str3 AS str_concat 
    FROM SampleStr 
  WHERE str1 = ' 山田 ';

#     LENGTH——字符串长度
SELECT str1,
        LENGTH(str1) AS len_str 
    FROM SampleStr;
#      LOWER——小写转换，  UPPER——大写转换
SELECT str1, 
        LOWER(str1) AS low_str
    FROM SampleStr 
  WHERE str1 IN ('ABC', 'aBC', 'abc', ' 山田 ');


#       REPLACE——字符串的替换
SELECT str1, str2, str3,
        REPLACE(str1, str2, str3) AS rep_str 
    FROM SampleStr;

#       SUBSTRING——字符串的截取
SELECT str1,
        SUBSTRING(str1 FROM 3 FOR 2) AS sub_str 
    FROM SampleStr;

日期函数

#    CURRENT_DATE——当前日期
SELECT CURRENT_DATE;
    FROM dual;

#    CURRENT_TIME——当前时间
SELECT CURRENT_TIME;

#    CURRENT_TIMESTAMP——当前日期和时间
SELECT CURRENT_TIMESTAMP;

#    EXTRACT——截取日期元素
SELECT CURRENT_TIMESTAMP,         
        EXTRACT(YEAR FROM CURRENT_TIMESTAMP)        AS year,
        EXTRACT(MONTH FROM CURRENT_TIMESTAMP)       AS month,
        EXTRACT(DAY FROM CURRENT_TIMESTAMP)         AS day,
        EXTRACT(HOUR FROM CURRENT_TIMESTAMP)        AS hour,
        EXTRACT(MINUTE FROM CURRENT_TIMESTAMP)      AS minute,
        EXTRACT(SECOND FROM CURRENT_TIMESTAM )      AS second;

##转换函数

CAST——数据类型转换

将字符串类型转换为数值类型

// MySQL
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;

谓词：返回值为真值—–（TRUE/ FALSE/UNKNOWN）的函数

LIKE BETWEEN IS NULL / IS NOT NULL IN EXISTS

#       LIKE——字符串的部分一致查询,模糊 ------  前方一致查询   中间一致查询   后方一致查询
SELECT *
FROM SampleLike WHERE strcol LIKE 'ddd%';-------或LIKE '%ddd%'或 LIKE '%ddd'

#    BETWEEN ——范围查询
SELECT product_name, sale_price
    FROM Product
 WHERE sale_price BETWEEN 100 AND 1000;

#       IS NULL 、 IS NOT NULL ——判断是否为 NULL
SELECT product_name, purchase_price 
    FROM Product 
 WHERE purchase_price IS NULL;   ---选取出purchase_price（不）为 NULL 的商品

#           IN / NOT IN —— OR 的简便用法
SELECT product_name, purchase_price 
    FROM Product 
 WHERE purchase_price = 320 
    OR purchase_price = 500 
    OR purchase_price = 5000;
SELECT product_name, purchase_price
    FROM Product 
 WHERE purchase_price IN (320, 500, 5000);

子查询作为 IN 谓词的参数 IN谓词(NOT IN谓词) 参数将子查询作为IN的参数将视图作为 IN 的参数将表作为 IN 的参数

为什么使用子查询——-各个商店销售的商品变化，导致ShopProduct表内大阪店销售的商品变化。如果 SELECT 语句中没有使用子查询的话，一旦商品发生了改变，那么 SELECT 语句也不得不进行修改。

Product 表

product_id (商品编号)	product_name (商品名称)	product_type (商品种类)	sale_price (销售单价)	purchase_price (进货单价)	regist_date (登记日期)
0001	T恤衫	衣服	1000	500	2009-09-20
0002	打孔器	办公用品	500	320	2009-09-11
0003	叉子	厨房用具	500		2009-09-20

ShopProduct(商店商品)表

shop_id (商店)	shop_name (商店名称)	product_id (商品编号)	quantity (数量)
000A	东京	0001 (T 恤衫）	30
000A	东京	0002(打孔器)	50
000B	名古屋	0002 003(运动 T 恤)	30

问题：取出“大阪店(000C)在售商品(product_id)的销售单价(sale_price)”

-- 取得“在大阪店销售的商品的销售单价” 
SELECT product_name, sale_price
    FROM Product
 WHERE product_id IN (SELECT product_id
                        FROM ShopProduct
                    WHERE shop_id = '000C');
-- 子查询展开后的结果
SELECT product_name, sale_price
    FROM Product
WHERE product_id IN ('0003', '0004', '0006', '0007');

CASE表达式

case表达式是函数的一种，进行的是运算功能

搜索case表达式即加上where

CASE WHEN <求值表达式> THEN <表达式> 
     WHEN <求值表达式> THEN <表达式> 
     WHEN <求值表达式> THEN <表达式>
    .
    .
     ELSE <表达式> 
END

窗口函数，又称分析函数，简称OLAP 是 OnLine Analytical Processing

窗口函数的语法

< 窗口函数 > OVER ([ PARTITION BY < 列清单 >] 
                            ORDER BY < 排序用列清单 >)

不能用于where子句和GROUP BY 子句
能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）
RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数

专用窗口函数（排序）

RANK 1 位、1 位、1 位、4 位……
DENSE_RANK 函数 1 位、1 位、1 位、2 位……
ROW_NUMBER 函数 1 位、2 位、3 位、4 位……

--------根据不同的商品种类，按照销售单价从低到高的顺序创建排序表
SELECT product_name, product_type, sale_price,
    RANK () OVER (PARTITION BY product_type    -------有PARTITION BY则分组，无PARTITION BY则不分组
                    ORDER BY sale_price) AS ranking 
FROM Product;


SELECT product_name, product_type, sale_price,
    RANK () OVER (ORDER BY sale_price) AS ranking, 
    DENSE_RANK () OVER (ORDER BY sale_price) AS dense_ranking, 
    ROW_NUMBER () OVER (ORDER BY sale_price) AS row_num 
FROM Product;

聚合函数—-就是统计函数

// SUM 函数
SELECT product_id, product_name, sale_price,
　  SUM (sale_price) OVER (ORDER BY product_id) AS current_sum 
    AVG (sale_price) OVER (ORDER BY product_id) AS current_avg FROM Product;
FROM Product;

移动平均（moving average）把握最近状态。ROWS 5 PRECEDING之前 / FOLLOWING之后

指定“最靠近的3行”作为汇总对象

SELECT product_id, product_name, sale_price,
    AVG (sale_price) OVER (ORDER BY product_id 
                            ROWS 2 PRECEDING) AS moving_avg  -------ROWS （“行”）和 PRECEDING （“之前”），截止到之前2行
FROM Product;

GROUPING运算符——–同时得出小计和合计 ROLLUP———同时得出小计和合计 CUBE——–立体展示数据 GROUPING SETS——-取得期望的积木CUBE

SELECT product_type, SUM(sale_price) AS sum_price 
    FROM Product 
GROUP BY product_type WITH ROLLUP;

// 与 ROLLUP 的结果相比， CUBE 的结果中多出了几行记录

SELECT CASE WHEN GROUPING(product_type) = 1
            THEN ' 商品种类 合计 ' 
            ELSE product_type END AS product_type,
        CASE WHEN GROUPING(regist_date) = 1 THEN ' 登记日期 合计 ' 
            ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
        SUM(sale_price) AS sum_price 
    FROM Product 
GROUP BY CUBE(product_type, regist_date);


SELECT CASE WHEN GROUPING(product_type) = 1
            THEN ' 商品种类 合计 ' 
            ELSE product_type END AS product_type,
        CASE WHEN GROUPING(regist_date) = 1 THEN ' 登记日期 合计 ' 
            ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
        SUM(sale_price) AS sum_price 
    FROM Product 
GROUP BY GROUPING SETS (product_type, regist_date);

SQL基础#

DDL(Data Definition Language，数据定义语言)#

DML(Data Manipulation Language，数据操纵语言)#

DCL(Data Control Language，数据控制语言)#

CREATE DATABASE 语句#

CREATE TABLE语句#

表的删除(DROP TABLE语句)#

添加列的ALTER TABLE语句#

删除列的ALTER TABLE语句#

向 Product 表中插入数据#

列的查询#

为列设定别名 : 使用 AS 关键字为列设定别名#

查询常数#

DISTINCT去重—DISTINCT 关键字只能用在第一个列名之前#

WHERE 语句来选择记录#

运算符#

聚合与排序#

对表进行分组#

为聚合结果指定条件#

使用 HAVING 子句时 SELECT 语句的顺序#

对查询结果进行排序#

子句的书写顺序#

ORDER BY子句中可以使用的列#

第 4 章 数据更新#

INSERT 语句#

从其他表中复制数据#

数据的删除#

数据的更新(UPDATE语句)#

多列更新#

语法：#

第 5 章 复杂查询 —-视图 子查询 关联子查询#

视图：临时的表，保存的是 SELECT 语句#