SQL基础教程(第2版)学习笔记

-- 2019.03.26 《SQL基础教程(第2版》
----- 第1章 数据库和SQL -----
-- 数据库：将大量数据集合起来，并通过计算机加工而成的可以高效访问的数据集合（Database，DB）
-- 数据库管理系统：管理数据库的计算机系统（Database Management System，DBMS）。
-- 数据库种类：层次数据库，关系数据库（oracle，sqlserver，db2，postgresql，mysql），面向
-- 对象数据库，XML数据库，键值存储数据库
-- RDBMS的常见系统结构：客户端/服务器类型（C/S类型）。客户端向服务器发送一定的SQL语句，
-- 服务器接收到客户端的请求，并根据请求操作数据库，返回请求的数据或更新数据库的数据。
-- 关系数据库通过二维表的形式管理数据，表的列称为'字段'，行称为'记录'，关系数据库以行为单位
-- 读写数据。行列交叉处称为'单元格'，一个单元格只能输入一个数据。
-- 标准SQL：符合国际标准化组织（ISO）为SQL制订的标准。
-- SQL语句种类：DDL（Data Defintion Language，CREATE,DROP,ALTER）用来创建或删除数据库或数
-- 据库中的表等对象；DML（Data Manipulation Language，SELECT,INSERT,UPDATE,DELETE）查询或变
-- 更表中的对象;DCL（Data ControlLanguage，COMMIT,ROLLBACK,GRANT,REVOKE）用来确认或取消对数
-- 据库进行的操作。
-- SQL基本书写规则：语句以';'结尾，不区分大小写，字符串和日期使用单引号（'）括起来，以空格
-- 分隔。
-- 创建数据库：CREATE DATABASE db_name,CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER -- SET CHARSET_NAME]。
-- 创建表：CREATE TABLE table_name(<列名> <数据类型> <该列所需约束> <该表的约束>)。
-- 命名规则：英文字母、数字、下划线，必须以英文字母开头。名称不能相同。
-- 数据类型：INTEGER（整数），CHAR（定长字符串，存入字符串长度不满足最长长度时，用空格补
-- 足）；VARCHAR(变长字符串)；DATE（日期型，年月日）
-- 设置约束：NOT NULL约束（设置了不能输入空白，必须输入数据的约束）；PRIMARY KEY（列名）约
-- 束（设置主键）
-- 删除表：DROP TABLE table_name（删除表后无法恢复，只能重建）。
-- 表定义更新（添加列）：ALTER TABLE table_name ADD COLUMN <列的定义>
-- 表定义更新（删除列）：ALTER TABLE table_name DROP COLUMN <列名>
-- 表名变更：RENAME TABLE <变更前名称> TO <变更后名称>

----- 第2章 查询基础 -----
-- 列的查询：SELECT <列名> FROM table_name。查询结果中列的顺序和select子句中顺序相同
-- 查询所有列：SELECT * FROM table_name。星号（*）代表所有列
-- 列别名：SELECT <列名> AS '别名' FROM table_name
-- 删除重复行：在SELECT语句中使用DISTINCT删除重复行（SELECT DISTINCT product_type FROM
-- product），NULL会被视为一类数据，多条NULL数据被合并为一条。DISTINCT关键字只能用在第
-- 一个列名前。
-- 条件查询：SELECT语句通过WHERE子句来指定查询数据的条件（SELECT <列名> FROM table_name
-- WHERE <条件表达式>），先通过WHERE子句查询出符合条件的记录，然后再选取出SELECT语句指定
-- 的列。
-- 注释：单行注释（--），多行注释（/*...*/）
-- 算术运算符：加减乘除（+-*/），加法运算符遇到数值起加的作用，遇到字符串起拼接的作用
-- 所有包含NULL的运算结果都是NULL
-- 比较运算符：=,<>,>=,<=,>,<,比较运算符可以对数值、字符串、日期等几乎所有数据类型的列
-- 和值进行比较。不能对NULL使用比较运算符。IS NULL运算符专门判断是否为NULL（IS NOT NULL）。
-- 字符串类型的数据原则上按照字典顺序排序。
-- 逻辑运算符：NOT（否定某一条件）；AND（在其两侧的查询条件都成立时整个查询才成立）；OR（
-- 在其两侧的查询条件有一个成立时整个条件都成立）。AND优先级高于OR，使用括号可以提高优先级

----- 第3章 聚合与排序 -----
-- 聚合函数：用于汇总的函数，聚合即将多行汇总为一行。COUNT,SUM,AVG,MAX,MIN
-- 计算全部数据的行数（包含NULL），SELECT COUNT(*) FROM table_name
-- 计算NULL之外的数据的行数，SELECT COUNT(列名) FROM table_name，先排除NULL再计数
-- 聚合函数计算前会将NULL排除在外。COUNT(*)例外，不会排除NULL
-- MAX/MIN几乎适用于所有数据类型，SUM/AVG只适用于数值类型
-- 聚合函数删除重复值（DISTINCT）：SELECT COUNT(DISTINCT 列名) FROM table_name，先删除列中
-- 重复，再计算行数。
-- GROUP BY子句：SELECT 列名 FROM table_name GROUP BY 列名，在GROUP BY中指定的列称为聚合键
-- 或分组列。聚合键中包含NULL时,结果中会以‘不确定’行（空行）的形式表现出来。
-- SELECT 列名 FROM table_name WHERE 条件 GROUP BY 列名；执行顺序：FROM,WHERE,GROUP BY,
-- SELECT。
-- 注意：使用GROUP BY时，SELECT中不能出现聚合键之外的列名（常数、聚合键、聚合函数）；由于
-- 执行顺序问题，GROUP BY子句中不能使用SELECT中定义的别名。
-- 想要删除重复数据使用DISTINCT,想要分组计算汇总结果使用GROUP BY。
-- HAVING子句：指定分组的条件。SELECT 列名 FROM table_name GROUP BY 列名 HAVING 条件
-- HAVING子句执行顺序：FROM,WHERE,GROUP BY,HAVING,SELECT。
-- HAVING子句构成要素：常数，聚合函数，聚合键。
-- 注意：WHERE子句指定行所对应的条件，HAVING子句指定组所对应的条件（聚合键对应的条件应该
-- 写在WHERE子句中）
-- 对查询结果进行排序：SELECT 列名 FROM table_name ORDER BY 排序键 排序关键字
-- 完整书写顺序：SELECT,FROM,WHERE,GROUP BY,HAVING,ORDER BY
-- 升降序关键字：ASC（升，默认），DESC（降序）
-- ORDER BY子句可指定多个排序键，使用含有NULL的列作为排序键时，NULL会在结果的开头或结尾
-- 汇总显示。
-- ORDER BY语句执行顺序：FROM,WHERE,GROUP BY,HAVING,SELECT,ORDER BY
-- ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。

----- 第4章 数据更新 -----
-- 暂时跳过 --

----- 第5章 复杂查询 -----
-- 从SQL角度来看，视图和表是相同的，表中保存的是实际的数据，视图中保存的是SQL。
-- CREATE VIEW 视图名称 (视图列名) AS SELECT语句。视图列名顺序与SELECT语句中列名顺序相同。
-- 多重视图会降低SQL性能。
-- 视图限制：定义视图时不能使用ORDER BY语句
-- 视图和表需要同时进行更新，因此通过汇总得到的视图无法进行更新。
-- 删除视图：DROP VIEW 视图名称（列名）
-- 子查询是一次性视图，在SELECT语句执行完成后会消失。（将用来定义视图的SELECT语句直接用在
-- FROM子句中）
-- SELECT product_type,cnt_product FROM (SELECT product_type,count(*) AS cnt_product FROM
-- product GROUP BY product_type) AS PrductSum（先执行内层查询，再执行外层查询）
-- 避免多层子查询嵌套
-- 标量子查询：必须而且只能返回1行1列的结果（返回单一值的子查询，可以用于WHERE子句中的比较
-- 运算符进行比较）。
-- 标量子查询不仅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子句内（关联名称的作用域，子查询内部设定的关
-- 联名称只能在该子查询内部使用，内部可以看到外部，而外部看不到内部）。

----- 第6章 函数、谓词、CASE表达式 -----
-- 各种各样的函数--
-- 算术函数是最基本的函数，算术运算符即是算术函数
-- ABS计算绝对值，MOD(m,n)计算除法余数（sqlserver中使用%计算余数），ROUND(对象数值,保留的小
-- 数位)作四舍五入操作，
-- 字符串拼接（mysql中用CONCAT，sqlserver中用+，postgresql中用||），LENGTH(字符串)字符串长
-- 度（sqlserver用LEN），LOWER小写转换，UPPER大写转换，REPLACE(对象字符串，替换前字符串，替
-- 换后字符串)字符串的替换，字符串截取（postgresql和mysql中用SUBSTRING(str FROM pos FOR len)，sqlserver中用SUBSTRING(对象字符串，截取开始位置，截取字符数)）
-- 当前日期CURRENT_DATE（sqlserver中用CAST(CURRENT_TIMESTAMP AS DATE)），当前时间CURRENT_TI
-- ME（sqlserver中用CAST(CURRENT_TIMESTAMP AS TIME)），CURRENT_TIMESTAMP当前日期时间，截取
-- 日期元素EXTRACT(日期元素 FROM 日期)（sqlserver中用DATEPART(日期元素,日期)）
-- CAST(expr AS type)类型转换函数
-- 谓词：返回值为真值的函数 --
-- LIKE谓词，字符串模糊查询（%匹配一个或多个字符，_匹配一个字符，前方一致，中间一致，后方一
-- 致）
-- BETWEEN谓词，范围查询（SELECT product_name,sale_price FROM product WHERE sale_price
-- BETWEEN 100 AND 1000;）
-- IS NULL,IS NOT NULL判断是否为NULL，
-- IN谓词，OR的简便用法（NOT IN，都无法选取出NULL数据），可以使用子查询作为其参数
-- EXISTS谓词
-- CASE表达式 --
-- CASE表达式在区分情况（条件分支）时使用（简单CASE表达式和搜索CASE表达式，搜索CASE表达式
-- 包含了简单CASE表达式的全部功能）
-- 语法：CASE WHEN <求值表达式> THEN <表达式> ELSE <表达式> END
-- CASE表达式可以实现结果的行列转换

----- 第7章 集合运算 -----
-- 集合运算：对满足同一规则的记录进行的加减等四则运算。（以行为单位进行的操作，竖向）
-- 表的加法（UNION并集）：SELECT product_id FROM product UNION SELECT product_id FROM
-- product1（集合运算符会除去重复的记录）
-- 集合运算注意事项：作为运算对象的记录的列数必须相同、列的类型必须一致、ORDER BY只能在最后
-- 位置使用一次。
-- UNION保留重复行：在UNION后面添加ALL关键字。
-- 选取集合中公共部分（INTERSECT交集）：SELECT product_id FROM product INTERSECT SELECT
-- product_id FROM product1
-- 记录的减法（EXCEPT）：SELECT product_id FROM product EXCEPT SELECT product_id FROM
-- product1（结果只包含product中减去product1的剩余部分）
-- 联结（JOIN）：将其他表的列拿过来，作‘添加列’运算
-- 内联结（INNER JOIN），外联结（OUTER JOIN,LEFT JOIN,RIGHT JOIN）
-- 交叉联结（CROSS JOIN笛卡尔积）

----- 第8章 SQL高级处理 -----
-- 窗口函数（Online Analytical Processing），对数据库数据进行实时处理分析。
-- 窗口函数语法：<窗口函数> OVER ([PARTITION BY <列清单>] ORDER BY <排序用列清单>)
-- 能够作为窗口函数使用函数：聚合函数（SUM,AVG,COUNT,MAX,MIN），专用窗口函数（RANK、
-- DENSE_RANK、ROW_NUMBE等）
-- RANK函数，计算记录的排序
-- 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指定按照哪一列、何种顺序进
-- 行排序（纵向上排序）。
-- PARTITION BY分组后的记录集合称为窗口。
-- RANK函数（存在相同次位的记录，则会跳过之后的次位）
-- DENSE_RANK函数（存在相同次位的记录，也不会跳过）
-- ROW_NUMBER函数（赋予唯一的连续位次）
-- 原则上窗口函数只能写在SELECT语句中
-- SUM函数作为窗口函数：SELECT product_id,product_name,sale_price,SUM(sale_price) OVER 
-- (ORDER BY product_id) AS 'current_sum' FROM product（累加，其他聚合函数一样的操作逻辑）
-- AVG函数作为窗口函数：SELECT product_id,product_name,sale_price,AVG(sale_price) OVER
-- (ORDER BY product_id) AS 'current_id' FROM product（累计均值）。
-- 计算移动平均：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截止到之前两行（当前记录，之前1行记录，之前2行记录）
-- ROWS 2 FOLLOWING截止到之后两行（当前记录，之后1行记录，之后2行记录）
-- 使用窗口函数时必须在OVER子句中使用ORDER BY，此处的ORDER BY只决定窗口函数按什么顺序计算，
-- SELECT product_name,product_type,sale_price,RANK() OVER (ORDER BY sale_price) AS 
-- FROM product ORDER BY ranking DESC
-- 合计行是不指定聚合键得到的汇总结果
-- GROUPING运算符（ROLLUP,CUBE,GROUPING SETS）
-- ROLLUP同时得出合计和小计，SELECT product_type,SUM(sale_price) AS 'sum_price' FROM 
-- product GROUP BY ROLLUP(product_type)（sqlserver和postgresql中语法）
-- SELECT product_type,SUM(sale_price) AS 'sum_price' FROM product GROUP BY product_type
-- WITH ROLLUP（mysql中语法）
-- GROUPING函数，让NULL更加容易分辨（判断超级分组记录的NULL，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
-- 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语法和ROLLUP语法相同
-- 剩余两章讲的是使用java来操作数据库，我就没必要学了。
-- 2019.03.28 END --