sql vb xml 换行_《SQL基础教程》笔记

weixin_39992483

于 2020-12-28 14:35:02 发布

阅读量119

点赞数

文章标签： sql vb xml 换行

本文链接：https://blog.csdn.net/weixin_39992483/article/details/112408084

版权

第1章数据库和SQL

1-1 数据库是什么

将大量数据保存起来，通过计算机加工而成的可以进行高效访问的数据集合称为 数据库。 数据库（Database，DB） 数据库管理系统（Database Management System，DBMS） 关系数据库管理系统（Relational Database Management System，RDBMS）

DBMS的种类

层次数据库（Hierarchical Database，HDB）把数据通过层次结构（树形结构）的方式表现出来
关系数据库（Relational Database，RDB）由行和列组成的二维表来管理数据，使用专门的SQL（Structured Query Language，结构化查询语言）对数据进行操作
面向对象数据库（Object Oriented Database，OODB）面向对象是把数据以及对数据的操作集合起来以对象为单位进行管理，面向对象数据库是用来保存这些对象的数据库
XML数据库（XML Database，XMLDB） XML：eXtensible Markup Language，可扩展标记语言，是一种标记语言。标记指计算机所能理解的信息符号，通过此种标记，计算机之间可以处理包含各种信息的文章等。XML数据库可以对XML形式的大量数据进行高速处理
键值存储系统（Key-Value Store，KVS）单纯用来保存查询所使用的主键（Key）和值（Value）的组合的数据库

1-2 数据库的结构

RDBMS最常见的系统结构是客户端_服务器类型（C_S类型）结构
根据SQL语句的内容返回的数据同样必须是二维表的形式
表的列（垂直方向）称为字段，它代表了保存在表中的数据项目；表的行（水平方向）称为记录，它相当于一条数据。关系数据库必须以行为单位进行数据读写
行和列交汇的方格称为单元格，一个单元格中只能输入一个数据
本书介绍的是标准SQL的书写方式

1-3 SQL概要

SQL语句及其种类

DDL（Data Definition Language，数据定义语言）用来创建或者删除存储数据用的数据库以及数据库中的表等对象。DDL包含以下几种指令：CREATE（创建数据库和表等对象） DROP（删除数据库和表等对象） ALTER（修改数据库和表等对象的结构）
DML（Data Manipulation Language，数据操纵语言）用来查询或者变更表中的记录。DML包含以下几种指令：SELECT（查询表中的数据） INSERT（向表中插入新数据） UPDATE（更新表中的数据） DELETE（删除表中的数据）
DCL（Data Control Language，数据控制语言）用来确认或者取消对数据库中的数据进行的变更，还可以对RDBMS的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL包含以下几种指令：COMMIT（确认对数据库中的数据进行的变更）ROLLBACK（取消对数据库中的数据进行的变更） GRANT（赋予用户操作权限） REVOKE（取消用户的操作权限）

SQL的基本书写规则

SQL语句要以分号(;)结尾
SQL语句不区分关键字的大小写，但为了理解起来更加容易，一般按以下规则来书写SQL语句：关键字大写；表名的首字母大写；其余（列名等）小写
插入到表中的数据是区分大小写的
字符串和日期常数书写时需要使用单引号(‘’)括起来，如‘abc’、‘2010-01-26’；数字常数书写时不需要使用任何符号标识，直接写成1000这样的数字即可
单词之间需要使用半角空格或者换行符进行分隔

1-4 表的创建

CREATE TABLE <表名>
(<列名1> <数据类型> <该列所需约束>,
 <列名2> <数据类型> <该列所需约束>,
 ……
 <该表的约束1>, <该表的约束2>, ……);

例：

CREATE TABLE Product
(product_id     CHAR(4)         NOT NULL,
 product_name       VARCHAR(100)    NOT NULL,
 sale_price         INTEGER         ,
 register_date  DATE            ,
 PRIMARY KEY (product_id));

只能使用半角英文字母、数字、下划线(_)作为数据库、表和列的名称，名称必须以半角英文字母开头；所有的列都必须指定数据类型

基本数据类型

INTEGER型：用来指定存储整数的列的数据类型（数字型），不能存储小数
CHAR型：用来指定存储字符串的列的数据类型（字符型），可以像CHAR(10)这样在括号中指定该列可以存储的字符串的长度。字符串以定长字符串的形式存储在被指定为CHAR型的列中，当列中存储的字符串长度达不到最大长度时，使用半角空格进行补足
VARCHAR型：与CHAR型类似，但该类型的列以可变字符串的形式保存字符串，即使字符数未达到最大长度也不会用半角空格补足
DATE型：用来指定存储日期的列的数据类型（日期型）

约束的设置

NOT NULL：必须输入数据的约束
PRIMARY KEY：主键约束，意味着该列数据不能重复，根据该列可以唯一确定一行数据

1-5 表的删除和更新

表的删除

DROP TABLE <表名>;

表定义的更新

添加列 ALTER TABLE <表名> ADD COLUMN <列的定义>

删除列 ALTER TABLE <表名> DROP COLUMN <列的名称>

向表中插入数据

INSERT INTO <表名> VALUES (<value1>,<value2>,……);

第2章查询基础

2-1 SELECT语句基础

列的查询

SELECT <列名>,……
FROM <表名>;

查询所有的列

SELECT *
FROM <表名>;

为列设定别名

SELECT product_id AS id,
         product_name AS name
FROM Product;

使用双引号可以设定包含空格或中文的别名

SELECT product_id AS "商品编号",
         product_name AS "商品名称"
FROM Product;

从结果中删除重复行

使用DISTINCT关键字

SELECT DISTINCT product_type
FROM Product;

使用DISTINCT时，NULL也被视为一类数据，NULL存在于多行中时，也会被合并为一条NULL数据 DISTINCT也可以在多列之前使用，会将多个列的数据进行组合，将重复的数据合并为一条。此时DISTINCT关键字只能用在第一个列名之前

根据WHERE语句来选择记录

SELECT语句通过WHERE子句来指定查询数据的条件

SELECT <列名>,……
FROM <表名>
WHERE <条件表达式>;

注释的书写方法

单行注释：书写在“--”之后，只能写在同一行。MySQL中需要在“--“后加入半角空格
多行注释：书写在/*和*/之间，可以跨多行

2-2 算术运算符和比较运算符

注意NULL

所有包含NULL的计算，结果肯定是NULL

比较运算符

比较运算符可以对字符、数字和日期等几乎所有数据类型的列和值进行比较

<>：不等于
=：大于等于
<=：小于等于

字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。 ’1‘、’2‘、’3‘、’10‘、’11‘、’222‘按字典顺序排列结果为：‘1’，‘10’，‘11’，‘2’，‘222’，‘3’。即，‘2’>’10’
不能对NULL使用比较运算符。在找NULL记录时，用“=NULL”或者“<>NULL”是得不到结果的，要使用“IS NULL”和“IS NOT NULL”

2-3 逻辑运算符

三值逻辑

SQL除了真、假外，还存在第三个逻辑值——不确定（UNKNOWN）

第3章聚合与排序

3-1 对表进行聚合查询

聚合函数

COUNT：计算表中的记录数
SUM：计算表中数值列中数据的合计值
AVG：计算表中数值列中数据的平均值
MAX：求出表中任意列中数据的最大值
MIN：求出表中任意列中数据的最小值

COUNT函数的结果根据参数的不同而不同。COUNT()会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数
除了COUNT()之外，聚合函数会将NULL排除在外。比如，在计算平均值时，会事先删除NULL再进行计算，分母会减少NULL的个数

3-2 对表进行分组

GROUP BY子句

SELECT <列名1>,<列名2>,<列名3>,……
FROM <表名>
GROUP BY <列名1>,<列名2>,<列名3>,……;

GROUP BY子句中指定的列称为聚合键或者分组列。当聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来

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

在SELECT子句中书写了多余的列。使用聚合函数时，SELECT子句中只能存在以下三种元素：常数、聚合函数、GROUP BY子句中指定的列名（也就是聚合键）
在GROUP BY子句中写了列的别名。这种写法在MySQL中不会发生执行错误，但其并不通用，因此最好不要使用
GROUP BY子句的结果能排序吗？GROUP BY子句结果的显示是无序的
在WHERE子句中使用聚合函数。只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数

3-3 为聚合结果指定条件

HAVING子句

HAVING子句用来为聚合结果指定条件

SELECT <列名1>,<列名2>,<列名3>,……
FROM <表名>
GROUP BY <列名1>,<列名2>,<列名3>,……
HAVING <分组结果对应的条件>;

WHERE子句用来指定数据行的条件，HAVING子句用来指定分组的条件

3-4 对查询结果进行排序

ORDER BY子句

SELECT <列名1>,<列名2>,<列名3>,……
FROM <表名>
ORDER BY <排序基准列1>,<排序基准列2>,……(DESC);

默认按升序排列，加DESC后按降序排列
ORDER BY子句中可以使用存在与表中、但并不包含在SELECT子句中的列，也可以使用COUNT(*)这样的聚合函数

SELECT语句的书写顺序和执行顺序

书写顺序： 1.SELECT子句→2.FROM子句→3.WHERE子句→4.GROUP BY子句→ 5. HAVING 子句 → 6. ORDER BY 子句

执行顺序： FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY

第4章数据更新

4-1 数据的插入

INSERT语句的用法

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

对表进行全列INSERT时，可以省略表名后的列清单，VALUES子句的值会默认按照从左到右的顺序赋给每一列
SQL语句执行失败时都不会对表中数据造成影响

从其他表中复制数据

INSERT INTO ProductCopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date 
FROM Product;

执行该INSERT…SELECT语句时，可以将Product表中这六列的数据插入到ProductCopy表中。在上述语句中，也可以使用WHERE子句或者GROUP BY子句等

INSERT INTO ProductType (product_type, sum_sale_price, sum_purchase_price)
SELECT product_type, SUM(sale_price), SUM(purchase_price) 
FROM Product
GROUP BY product_type;

将Product表中的数据按商品种类进行聚合，计算出销售单价合计值以及进货单价合计值，将结果存储在ProductType表中

4-2 数据的删除

DELETE语句的使用方法

保留数据表，仅删除全部数据行 DELETE FROM <表名>;
DELETE语句的删除对象不是表或者列，而是行，因此有FROM
删除部分数据行

DELETE FROM <表名>
WHERE <条件>;

DELETE语句中只能使用WHERE子句

4-3 数据的更新

使用UPDATE语句可以更改表中的数据

UPDATE <表名>
SET <列名1> = <表达式1>,
    <列名2> = <表达式2>,
……
WHERE <条件>;

4-4 事务

事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务，可以对数据库中的数据更新处理的提交和取消进行管理 MySQL中更新商品信息的事务

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

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

各个DBMS的事务开始语句不相同，但事务结束语句都是COMMIT和ROLLBACK

COMMIT——提交处理 提交事务包含的全部更新处理的结束指令，相当于文件处理中的覆盖保存。一旦提交，就无法恢复到事务开始前的状态 ROLLBACK——取消处理 取消事务包含的全部更新处理的结束指令，相当于文件处理中的放弃保存。一旦回滚，数据库就会恢复到事务开始之前的状态 事务处理的模式：SQL Server、PostgreSQL和MySQL默认使用自动提交模式，每条SQL语句就是一个事务，相当于每一条语句都自动包含在事务的开始语句和结束语句之间 Oracle直到用户执行COMMIT或者ROLLBACK为止算作一个事务

ACID特性

原子性（Atomicity）原子性是指在事务结束时，其中所包含的更新处理要么全部执行，要么完全不执行，也就是要么占有一切要么一无所有。
一致性（Consistency）一致性指的是事务中包含的处理要满足数据库提前设置的约束，如主键约束或者NOT NULL约束等
隔离性（Isolation）隔离性指的是保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。此外，在某个事务中进行的更改，在该事务结束之前，对其他事务而言是不可见的
持久性（Durability）持久性指的是在事务(不论是提交还是回滚)结束后，DBMS 能够保证该时间点的数据状态会被保存的特性。即使由于系统故障导致数据丢失，数据库也一定能通过某种手段进行恢复

第5章复杂查询

5-1 视图

表中存储的实际数据，而视图中保存的是从表中取出数据所使用的SELECT语句。我们从视图中读取数据时，视图会在内部执行该SELECT语句并创建出一张临时表

创建视图的方法

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

视图的限制：

定义视图时不能使用ORDER BY子句。视图和表一样，数据行是没有顺序的，在有些DBMS中定义视图的语句可以使用ORDER BY子句，但这不是通用的语法，所以最好不要使用
当视图满足以下条件时才可以对视图中的数据进行更新：SELECT子句中未使用DISTINCT；FROM子句中只有一张表；未使用GROUP BY子句；未使用HAVING子句。因为视图和表需要同时进行更新，因此通过汇总得到的视图无法进行更新

删除视图

DROP VIEW 视图名称;

5-2 子查询

子查询就是将用来定义视图的SELECT语句直接用于FROM子句当中

视图ProductSum和确认用的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 product_type, cnt_product
FROM (SELECT product_type, COUNT(*) AS cnt_product
            FROM Product
            GROUP BY product_type) AS ProductSum;

两种方法得到的结果完全相同

原则上子查询必须设定名称

标量子查询

标量就是单一的意思。标量子查询必须而且只能返回1行1列的结果要查询出销售单价高于平均销售单价的商品，由于在WHERE子句中不能使用聚合函数，因此以下的写法是错误的

SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > AVG(sale_price);

用标量子查询替换聚合函数

SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price) FROM Product);

标量子查询的书写位置

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

5-3 关联子查询

在细分的组内进行比较时，需要使用关联子查询选取出各商品种类中高于该商品种类的平均销售单价的商品。如果用以下方式

SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (SELECT AVG(sale_price)
                        FROM Product
                        GROUP BY product_type);

会报错，因为该子查询不是标量子查询，会返回多行结果使用关联子查询，在子查询中添加where子句的条件

SELECT product_type, product_name, sale_price
FROM Product AS P1
WHERE sale_price > (SELECT AVG(sale_price)
                        FROM Product AS P2
                        WHERE P1.product_type = P2.product_type
                        GROUP BY product_type);

在WHERE子句中追加了P1.product_type = P2.product_type这个条件，该条件的意思是在同一商品种类中对各商品的销售单价和平均单价进行比较，使得AVG函数按照商品种类进行了平均值计算，即使在子查询中不使用GROUP BY子句也能得到正确的结果，但为了跟前面出错的查询进行对比，这里仍然加上了GROUP BY子句

增加一列每种商品种类平均售价

SELECT product_id, product_name, product_type, sale_price,
         (SELECT AVG(sale_price) FROM Product AS P2 
                WHERE P1.product_type = P2.product_type 
         ) AS avg_sale_price
FROM Product AS P1;

结合条件一定要写在子查询中

SELECT product_type, product_name, sale_price 
FROM Product AS P1 
WHERE P1.product_type = P2.product_type
AND sale_price > (SELECT AVG(sale_price) 
                            FROM Product AS P2 
                            GROUP BY product_type);

如果将子查询中的关联条件移到外层查询中，会发生错误，因为这样违反了关联名称的作用域。子查询内部设定的关联名称，只能在该子查询内部使用。下图是子查询内的关联名称的有效范围

第6章函数、谓词、CASE表达式

6-1 各种各样的函数

算术函数

ABS——绝对值 ABS(数值)
MOD——求余 MOD(被除数,除数)
ROUND——四舍五入 ROUND(对象数值,保留小数的位数)

字符串函数

CONCAT——拼接 CONCAT(字符串1,字符串2,……)
LENGTH——字符串长度 LENGTH(字符串) MySQL中，LENGTH函数返回的是字节的长度，LENGTH(“山田”)返回的结果是6，因为UTF-8编码中一个汉字占3个字节。若要在MySQL中计算字符的长度，请使用CHAR_LENGTH函数，CHAR_LENGTH(“山田”)返回的结果是2
LOWER——小写转换 LOWER(字符串)
UPPER——大写转换 UPPER(字符串)
REPLACE——字符串的替换 REPLACE(对象字符串,替换前的字符串,替换后的字符串)
SUBSTRING——字符串的截取 SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数)

日期函数

CURRENT_DATE——当前日期没有参数，无需使用括号
CURRENT_TIME——当前时间没有参数，无需使用括号
CURRENT_TIMESTAMP——当前日期和时间获取当前时间戳
EXTRACT——截取日期元素 EXTRACT(日期元素 FROM 日期) eg. EXTRACT(YEAR FROM CURRENT_TIMESTAMP) 该函数的返回值并不是日期类型，而是数值类型

转换函数

CAST——类型转换 CAST(转换前的值 AS 想要转换的数据类型)
COALESCE——将NULL转换为其他值 COALESCE(数据1,数据2,数据3,……) 返回参数中左侧开始第一个不是NULL的值

6-2 谓词

谓词是满足返回值是真值这个条件的函数

LIKE谓词——字符串的部分一致查询

前方一致查询

-- 选取出以“ddd”开头的字符串
SELECT *
FROM SampleLike
WHERE strcol LIKE 'ddd%';

中间一致查询

-- 选取出包含“ddd”的字符串
SELECT *
FROM SampleLike
WHERE strcol LIKE '%ddd%';

后方一致查询

-- 选取出以“ddd”结尾的字符串
SELECT *
FROM SampleLike
WHERE strcol LIKE '%ddd';

%代表“0字符以上的任意字符串”
_(下划线)代表“任意1个字符”

BETWEEN谓词——范围查询

BETWEEN 数值1 AND 数值2 BETWEEN的结果中会包含数值1和数值2这两个临界点

IS NULL、IS NOT NULL——判断是否为NULL

为了选取出某些值为NULL的列的数据，不能使用=，而只能使用特定的谓词IS NULL

IN谓词——OR的简便用法

-- 取出进货单价为320、500、5000的商品
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);

两者等价

否定形式NOT IN可以取出进货单价不是320、500、5000的商品

IN和NOT IN无法取出NULL数据，NULL数据需要使用IS NULL和IS NOT NULL来判断

使用子查询作为IN的参数

-- 取得“在大阪店（000C）销售的商品的销售单价” 
SELECT product_name, sale_price 
FROM Product
WHERE product_id IN (SELECT product_id 
                        FROM ShopProduct
                        WHERE shop_id = '000C');

即使ShopProduct（商店商品）表发生变化，同样的SELECT语句依然可以取出“在大阪店销售的商品的销售单价”，使程序变得易于维护

EXIST谓词

使用EXIST谓词实现与“IN和子查询”相同的功能

-- 使用EXIST谓词选取出“大阪店在售商品的销售单价”
SELECT product_name, sale_price
FROM Product AS P
WHERE EXISTS (SELECT *
                FROM ShopProduct AS SP 
                WHERE SP.shop_id = ‘000C’
                AND SP.product_id = P.product_id);

EXIST只需要在右侧书写一个参数，该参数通常都是一个关联子查询
由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号(shop_id)为 ‘000C’，商品(Product)表和商店商品(ShopProduct)表中商品编号(product_id)相同”的记录，只有存在这样的记录时才返回真(TRUE)。因此子查询中是SELECT *，即使改为SELECT 1也可以。
作为EXIST参数的子查询中经常会使用SELECT *

6-3 CASE表达式

简单CASE表达式

CASE <表达式>
        WHEN <表达式> THEN <表达式> 
        WHEN <表达式> THEN <表达式> 
        WHEN <表达式> THEN <表达式> 
……
        ELSE <表达式> 
END

使用简单CASE表达式将字符串A～C添加到商品种类中

SELECT product_name, 
        CASE product_type 
                WHEN '衣服' THEN CONCAT("A:", product_type)
                WHEN '办公用品' THEN CONCAT("B:", product_type)
                WHEN '厨房用具' THEN CONCAT("C:", product_type)
                ELSE NULL 
        END AS abc_product_type
FROM Product;

简单CASE表达式的缺点是无法在WHERE子句中指定不同的列

搜索CASE表达式

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

使用搜索CASE表达式将字符串A～C添加到商品种类中

SELECT product_name,
        CASE WHEN product_type = '衣服' 
             THEN CONCAT('A:', product_type) 
             WHEN product_type = '办公用品' 
             THEN CONCAT('B:', product_type) 
             WHEN product_type = '厨房用具' 
             THEN CONCAT('C:', product_type) 
             ELSE NULL 
        END AS abc_product_type 
FROM Product;

搜索CASE表达式包含了简单CASE表达式的全部功能，所以一般都使用搜索CASE表达式

利用CASE表达式进行行列转换

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

执行结果

product_type | sum_price
-------------+----------
衣服                5000
办公用品              600
厨房用具            11180

如果要得到如下结果

sum_price_clothes | sum_price_kitchen | sum_price_office 
------------------+-------------------+-----------------
             5000 |             11180 |              600

可以在SUM函数中使用CASE表达式

-- 对按照商品种类计算出的销售单价合计值进行行列转换 
SELECT SUM(CASE WHEN product_type = '衣服' 
            THEN sale_price ELSE 0 END) AS sum_price_clothes,            
       SUM(CASE WHEN product_type = '厨房用具' 
            THEN sale_price ELSE 0 END) AS sum_price_kitchen,                
       SUM(CASE WHEN product_type = '办公用品' 
            THEN sale_price ELSE 0 END) AS sum_price_office 
FROM Product;

第7章集合运算

7-1 表的加减法

表的加法——UNION

-- 用UNION选出两张表中所有的商品ID和商品名称
SELECT product_id, product_name 
FROM Product 
UNION
SELECT product_id, product_name 
FROM Product2;

集合运算的注意事项

集合运算符通常会除去重复记录
作为运算对象的记录的列数必须相同
作为运算对象的记录中列的类型必须一致
可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次

包含重复行的集合运算——ALL选项

在集合运算符后添加ALL关键字，可以保留重复行

选取表中公共部分——INTERSECT

语法与UNION类似。UNION相当于求两个表的并集，INTERSECT相当于求两个表的交集

记录的减法——EXCEPT

-- 用EXCEPT选出Product表中有而Product2表中没有的商品ID和商品名称
SELECT product_id, product_name 
FROM Product 
EXCEPT
SELECT product_id, product_name 
FROM Product2 
ORDER BY product_id;

EXCEPT两个表的位置不一样，结果会不一样。因为减法运算中减数和被减数的位置不同，结果也不同

MySQL尚不支持INTERSECT和EXCEPT

7-2 联结（以列为单位对表进行联结）

UNION是以行（纵向）为单位进行操作，而联结是以列（横向）为单位进行的。联结就是将其他表中的列添加进来，进行“添加列”的集合运算。

内联结

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price 
FROM ShopProduct AS SP INNER JOIN Product AS P 
                            ON SP.product_id = P.product_id;

内联结注意要点

进行联结时需要在FROM子句中使用多张表
ON子句指定联结所使用的列（联结键），且ON要紧跟在FROM后
使用联结时SELECT子句中的列最好按照“<表的别名>.<列名>”的格式进行书写

外联结

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price 
FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P
                            ON SP.product_id = P.product_id;

外联结注意要点

内联结只能选取出同时存在于两张表中的数据，而外联结会选取出单张表中的全部信息
外联结中用LEFT和RIGHT指定主表，LEFT OUTER JOIN左边的表或者RIGHT OUTER JOIN右边的表是主表，主表中的所有数据将会被取出

三张表的联结

原则上联结表的数量并没有限制

FROM ShopProduct AS SP INNER JOIN Product AS P 
    ON SP.product_id = P.product_id 
        INNER JOIN InventoryProduct AS IP 
            ON SP.product_id = IP.product_id

使用两次INNER JOIN …… ON ……

第8章 SQL高级处理

8-1 窗口函数

窗口函数也称为OLAP函数（Online Analytical Processing），可以对数据库数据进行实时分析处理。目前MySQL8.0版本已经支持窗口函数

窗口函数语法

<窗口函数> OVER ([PARTITION BY <列清单>]
                        ORDER BY <排序用列清单>)
-- []中的内容可以省略

窗口函数大体可以分为以下两种：

能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）
RANK、DENSE_RANK、ROW_NUMBER等专用窗口函数

排序函数——RANK函数

/*根据不同的商品种类(product_type)，按照销售单价(sale_price)从低到高的顺序排序*/
SELECT product_name, product_type, sale_price,
    RANK() OVER (PARTITION BY product_type
                    ORDER BY sale_price) AS ranking
FROM Product;

执行结果如下：

PARTITION BY能够设定排序的对象范围，ORDER BY能够指定按照哪一列、何种顺序进行排序。前者在横向上对表进行分组，而后者决定了纵向排序的规则。
窗口函数兼具GROUP BY子句的分组功能以及ORDER BY子句的排序功能，但PARTITION BY子句并不具备GROUP BY子句的汇总功能，因此不会减少原表中记录的行数。
通过PARTITION BY分组后的记录集合称为窗口，此处的窗口代表范围

无需指定PARTITON BY

如果将整个表作为一个大的窗口来使用，无需指定PARTITION BY，与使用没有GROUP BY的聚合函数时的效果一样

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

执行结果如下：

排序方式

RANK函数计算排序时，如果存在相同位次的记录，则会跳过之后的位次。比如，有 3 条记录排在第 1 位时：1 位、1 位、1 位、4 位……
DENSE_RANK函数同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。比如，有 3 条记录排在第 1 位时：1 位、1 位、1 位、2 位……
ROW_NUMBER函数赋予唯一的连续位次。比如，有 3 条记录排在第 1 位时：1 位、2 位、3 位、4 位……

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;

执行结果如下：

窗口函数的适用范围

原则上窗口函数只能在SELECT子句中使用。因为窗口函数是对WHERE子句或者GROUP BY子句处理后的“结果”进行的操作，在SELECT子句之外使用窗口函数是没有意义的。

作为窗口函数使用的聚合函数

将SUM函数作为窗口函数使用

SELECT product_id, product_name, sale_price, 
    SUM(sale_price) OVER (ORDER BY product_id) AS current_sum FROM Product;

执行结果如下：

我们得到的并不仅仅是合计值，而是按照ORDER BY子句指定的product_id的升序进行排列，计算出商品编号“小于自己”的商品的销售单价的合计值。因此，计算该合计值的逻辑就像金字塔堆积那样，一行一行逐渐添加计算对象。使用AVG的效果类似，计算平均值时作为统计对象的只是“排在自己之上”的记录。

在按照时间序列的顺序，计算各个时间的销售额总额等的时候，通常都会使用这种称为累计的统计方法。像这样以“自身记录（当前记录）”作为基准进行统计，就是将聚合函数当作窗口函数使用时的最大特征

计算移动平均

在窗口中可以指定更加详细的汇总范围，该备选功能中的汇总范围称为框架

--指定“最靠近的3行”作为汇总对象
SELECT product_id, product_name, sale_price, 
        AVG(sale_price) OVER (ORDER BY product_id
                                ROWS 2 PRECEDING) AS moving_avg
FROM Product;

执行结果如下：

ROWS 2 PRECEDING就是将框架指定为“截止到之前2行”，也就是将作为汇总对象的记录限定为“自身（当前记录）、之前1行的记录、之前2行的记录”这最靠近的3行。

这种统计方法称为移动平均（moving average）。ROWS 2 FOLLOWING表示“截止到之后2行“，ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING表示从之前1行开始到之后1行结束，即包括当前记录一共3行

8-2 GROUPING运算符

ROLLUP——同时得出合计和小计

SELECT product_type, SUM(sale_price) AS sum_price 
FROM Product 
GROUP BY product_type WITH ROLLUP;
--MySQL写法，标准SQL语句为ROLLUP(product_type)

执行结果如下：

ROLLUP运算符相当于一次计算出不同聚合键组合的结果。上述代码实际上就是一次计算出了如下两种组合的汇总结果：

GROUP BY ()
GROUP BY (product_type)

GROUP BY ()表示没有聚合键，会得到全部数据的合计行的记录，该合计行记录称为超级分组记录（super group row）

在聚合键中使用多个值

SELECT product_type, regist_date, SUM(sale_price) AS sum_price FROM Product 
GROUP BY product_type, regist_date WITH ROLLUP;
--MySQL写法，标准SQL语句为ROLLUP(product_type, register_date)

执行结果如下：

相当于一次计算出如下三种组合的汇总结果：

GROUP BY ()
GROUP BY (product_type)
GROUP BY (product_type, regist_date)

如下图所示

GROUPING函数——让NULL更加容易分辨

GROUPING函数在其参数列的值为超级分组记录所产生的NULL时返回1，其他情况返回0

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

执行结果如下：

使用GROUPING函数和CASE WHEN子句在超级分组记录的键值中插入字符串

当GROUPING函数的返回值为1时，指定“合计”或者“小计”等字符串，其他情况返回通常的列的值

SELECT CASE WHEN GROUPING(product_type) = 1 
                THEN ‘商品种类 合计’ 
                ELSE product_type END AS product_type, 
         CASE WHEN GROUPING(regist_date) = 1 
                THEN ‘登记日期 合计’ 
                ELSE regist_date END AS regist_date,                     SUM(sale_price) AS sum_price 
FROM Product
GROUP BY product_type, regist_date WITH ROLLUP;

执行结果如下：

CUBE——用数据来搭积木

CUBE语法和ROLLUP相同

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

执行结果如下：

相当于一次计算出如下四种组合的汇总结果：

GROUP BY ()
GROUP BY (product_type)
GROUP BY (regist_date)
GROUP BY (product_type, regist_date)

CUBE会将GROUP BY子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中，一共有2^n（n是聚合键的个数）的组合个数；使用ROLLUP时组合的个数是n+1，ROLLUP的结果一定包含在CUBE的结果之中

GROUPING SETS——取得期望的积木

--只选取出将“商品种类”和“登记日期”各自作为聚合键的结果
SELECT CASE WHEN GROUPING(product_type) = 1 
            THEN '商品种类 合计' 
            ELSE product_type END AS product_type,       CASE WHEN GROUPING(regist_date) = 1 
            THEN '登记日期 合计' 
            ELSE regist_date END AS regist_date,             SUM(sale_price) AS sum_price 
FROM Product
GROUP BY GROUPING SETS (product_type, regist_date);

执行结果如下：

weixin_39992483

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

sql vb xml 换行_《SQL基础教程》笔记

第1章 数据库和SQL

1-1 数据库是什么

DBMS的种类

1-2 数据库的结构

1-3 SQL概要

SQL语句及其种类

SQL的基本书写规则

1-4 表的创建

基本数据类型

约束的设置

1-5 表的删除和更新

表的删除

表定义的更新

向表中插入数据

第2章 查询基础

2-1 SELECT语句基础

列的查询

查询所有的列

为列设定别名

从结果中删除重复行

根据WHERE语句来选择记录

注释的书写方法

2-2 算术运算符和比较运算符

注意NULL

比较运算符

2-3 逻辑运算符

三值逻辑

第3章 聚合与排序

3-1 对表进行聚合查询

聚合函数

3-2 对表进行分组

GROUP BY子句

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

3-3 为聚合结果指定条件

HAVING子句

3-4 对查询结果进行排序

ORDER BY子句

SELECT语句的书写顺序和执行顺序

第4章 数据更新

4-1 数据的插入

INSERT语句的用法

从其他表中复制数据

4-2 数据的删除

DELETE语句的使用方法

4-3 数据的更新

4-4 事务

ACID特性

第5章 复杂查询

5-1 视图

创建视图的方法

视图的限制：

删除视图

5-2 子查询

标量子查询

标量子查询的书写位置

5-3 关联子查询

结合条件一定要写在子查询中

第6章 函数、谓词、CASE表达式

6-1 各种各样的函数

算术函数

字符串函数

日期函数

转换函数

6-2 谓词

LIKE谓词——字符串的部分一致查询

BETWEEN谓词——范围查询

IS NULL、IS NOT NULL——判断是否为NULL

IN谓词——OR的简便用法

使用子查询作为IN的参数

EXIST谓词

6-3 CASE表达式

简单CASE表达式

搜索CASE表达式

利用CASE表达式进行行列转换

第7章 集合运算

7-1 表的加减法

表的加法——UNION

集合运算的注意事项

包含重复行的集合运算——ALL选项

第1章数据库和SQL

第2章查询基础

第3章聚合与排序

第4章数据更新

第5章复杂查询

第6章函数、谓词、CASE表达式

第7章集合运算