SQL之美(卷一)

DBMS数据库管理系统
Database Management System
1.有5类
HDB 层次数据库
RDB 关系数据库
OOOB面向对象数据库
XMLDB
KVS 键值存储系统
2.RDBMS :Relational DBMS
有代表性的有5种
Oracle(甲骨文)
SQL Server(Microsoft)
DB2(IBM)
MySQL
PostgreSQL

SQL语句三剑客 关键字+表名+列名
DDL Data Definition Language 数据定义语言
指令：
CREATE DATABASE/TABLE
DROP DATABASE/TABLE
ALTER DATABASE/TABLE
DML Manipulation 操纵
SELECT
INSERT
UPDATE
DELETE (FROM)
DCL Control 控制
COMMIT
ROLLBACK
GRANT
REVOKE

SQL的书写规范
ANSI(美国国家标准协会)和ISO(国际标准化组织)每隔几年联合修订SQL的标准
关键字大写
表名首字母大写
列名等小写
常数: 字符串,日期,数字
字符串,日期需要用单引号括起来,AS别名时如为中文要用双引号
单词之间用半角空格或换行符分割

表的创建
1.约束可以在定义列时设置,也可以在语句的末尾设置(NOT NULL约束只能以列为单位设置)
2.product_id不能写作product-id,标准SQL不允许使用连字符- 作为列名等名称

常用4种数据类型
INTEGER 
    存储整型
CHAR(n) 
    定长字符串,不够n需要用半角空格补足
    n是可以存储的字符串的最大长度,中文占两到三个字符,由编码集定
VARCHAR (Oracle中为VARCHAR2)
    可变长字符串
DATE 
    存储年月日(Oracle中DATE还包含时分秒)

表定义的修改
ALTER TABLE <表名> ADD/DROP COLUMN <列的定义>
列的定义: 列名+类型+约束
(Oracle 和SqlServer中省略COLUMN)
变更表名
Oracle和SqlServer:ALTER TABLE p1 RENAME TO p2
MySQL:RENAME TABLE p1 to p2

匹配查询(query)
子句(clause)以SELECT和FROM等关键字作为起始的短语,书写时以子句为单位进行换行

删除重复行DISTINCT
 1.只能写在第一个列名前
 2.可以查询出NULL,NULL被视为一类
 
 Oracle查询语句中不允许省略FROM子句,要用dual临时表
 SELECT 常数
 FROM dual
 
 比较运算符
 1.可以对几乎所有数据类型做比较,字符串的比较是按照字典序的
 2.不能对NULL进行比较,它有特定的语法: IS (NOT) NULL
 3.NULL / 0 结果为NULL ; 5 / 0 报错
 4.标准SQL里 不等于是 <> 而不是 !=
 
 逻辑运算符 NOT AND OR
 1. 对比较运算符等返回的真值进行操作
 2. 真值表(true table)  若真为1,假为0,则 AND称为逻辑积,OR称为逻辑和
 3.SQL的逻辑运算称为三值逻辑,TRUE，FALSE,UNKNOWN
 4.WHERE NOT price >1000  等同于 WHERE price <=1000
 5.AND优先于OR执行,可通过括号强化处理
 
 聚合查询
 1.count(*) *星号代表所有的行,count(列名)会得到NULL之外的数据行数
 2.所有的聚合函数,如果以列名为参数,那么在计算之前就已经把NULL排除在外了
 3.SUM/AVG只能对数值类型的列使用,MAX/MIN适用于所有数据类型
 4.count(distinct 列名) 计算行数前会删除指定列的重复数据
 5.SELECT子句,HAVING子句可以指定的元素:常数,聚合函数,GROUP BY子句中指定的列名(称为聚合建);
 ORDER BY子句可以使用SELECT子句中未出现的列或聚合函数,可以使用SELECT子句中定义的列的别名
 MySQL方言:下面语句是可以执行的
     select c1,c2,count(*)
     from <表名>
     group by c1;
 6.可以使用聚合函数的子句：
     SELECT
     HAVING
     ORDER BY
 7.聚合操作时,DBMS内部会进行排序,只有尽可能减少排序的行数,才能提高处理速度
 优化:在排序前通过WHERE过滤掉不用的数据,并为WHERE指定条件的列创建索引
 8.ORDER BY 排序键中有NULL值时,会出现在结果集的开头或结尾
 ASC(默认)ascendent上升的
 DESC descendent下降的
 
 数据插入
 1.insert into <表名> (列1,列2,列3...)values(值1,值2,值3...)
 列1,列2,列3... 称为列清单
 值1,值2,值3... 称为值清单
 2.多行insert (multi row)
 insert into <表名> (列1,列2,列3...)values(值清单1),(值清单2),(值清单3);
 3.全列insert时,可省略列清单
 4.插入NULL时，显式传入NULl
 5.插入默认值DEFAULT;隐式插入默认值,列清单里省略该列
 6.INSERT...SELECT... 复制一张表
 
 数据删除
 1.DELETE FROM <表名> (WHERE<条件>) 指定WHERE时称为搜索型DELETE
 2.TRUNCATE清空表,效率更高
 
数据更新
UPDATE <表名>
SET <列名1>=<表达式1>,<列名2>=<表达式2>
where <条件>
 
事务transaction
1.一个处理单元中执行的一系列更新处理的结合
2.ACID 
原子性atomicity 要么全部执行,要么完全不执行
隔离性isolation 事务之间在没有提交之前,是无法查看到其他事务操作后的数据的
一致性consistency 不合法的SQL会回滚,要满足定义好的约束
持久性durability 执行记录保存在硬盘,也就是日志,可以恢复到某个时间点
3.默认为自动提交模式(每条SQL语句就是一个事务)
4.数据库连接时事务已经开始

视图VIEW
1.保存的只是SELECT语句,不保存数据,节省了磁盘空间
2.CERATE VIEW <视图名>(列名1,列名2)AS SELECT ...
3.通常将用来校验数据的SQL保存在视图里,直接SELECT * 查看
4.尽量使用单一视图,而不是多重视图
5.不能使用ORDER BY
6.视图的更新(略)

子查询
1.一次性视图:子查询在SELECT语句执行完后就会消失
2.子查询需要取别名
3.标量子查询 scalar subquery,标量:单一
  结果是一行一列的,故可以比较运算符中
  标量子查询的书写位置:能够使用常数或列名的位置
4.关联子查询 使用场景:组内进行比较时
 结合条件写在子查询的WHERE子句中
 SELECT xxx
 FROM product p1
 WHERE price >(
 SELECT avg(price)
 FROm product p2
 WHERE p1.type=p2.type
 GROUP BY type
 );
 
 SELECT xxx,
        (SELECT avg(price)
         FROM product P2
         WHERE p1.type=p2.type
         GROUP BY type
        ) AS avg_type
 FROM product p1;
 

 函数(函数中的函就是盒子:输入值经过盒子后得到输出结果)
 根据用途分类:
 算术函数
 ABS(列名或常数):绝对值absolute value,不考虑数值的符号,表示一个数到远点的距离
 MOD(被除数,除数):取余modulo,这里被除数和除数可以是列名或常数
 ROUND(列名或常数,保留小数的位数)四舍五入,对指定保留位数的后一位做四舍五入
 SELECT ROUND(27.34,1); -- 27.3
 SELECT ROUND(27.345,2); -- 27.5
 字符串函数
 拼接字符串 c1 || c2 ||c3
 MySQL CONCAT(c1,c2,c3)
 SqlServer  c1+c2+c3
 长度 LENGTH(c1) 计算字符串c1包含多少个字符,汉字占两个字符,JAVA中一个char类型占2个字节,String类型里一个字符等于1个字节,汉字占2个字节
 SqlServer: LEN()
 LOWER()
 UPPER()
 替换:REPLACE(c1,str1,str2) 在c1中将str1替换成str2
 截取:
 MySQL: SUBSTRING(c1 FROM i FOR j) 在c1中从offset为i的位置开始截取j个字符
SqlServer: SUBSTRING(c1,i,j)
Oracle: SUBSTR(c1,i,j)
 日期函数
 CURRENT_DATE 年月日 (无参数,不需要带括号)
 ORACLE: SELECT CURRENT_DATE FROM dual
 SqlServer:  CAST(CURRENT_TIMESTAMP AS DATE)
 CURRENT_TIME 时分秒
  SqlServer:  CAST(CURRENT_TIMESTAMP AS TIME)
  CURRENT_TIMESTAMP 日期+时间
  截取日期EXTRACT(日期元素 FROM 日期)
  EXTRACT(YEAR/MONTH/DAY FROM CURRENT_TIMESTAMP)
  SqlServer: DATEPART(YEAR,CURRENT_TIMESTAMP)
 转换函数
 CAST 类型转换
 CAST('2021-10-26' AS DATE)
 将NULL值转换为其他值COALESCE() 返回第一个非NULL的值
 聚合函数
 
 谓语predicate
 1.返回值为真值得函数
 2.比较运算符,正式的名字就是比较谓语
 3.LiKE 是否包含
 LIKE '%dart%'
 LIKE '_dart_'
 4.范围查询 BETWEEN...AND...
 5.IS(NOT)NULL
 6.(NOT)IN 替换OR
 IN的结果集中如有NULL,则整个SELECT子句返回NULL
 IN通常跟子查询
 7.(NOT)EXISTS 左侧无参数,只关心是否有记录存在
 SELECT xxx
 FROM product p1
 WHERE EXISTS (
 SELECT * 
 FROM order o1 
 WHERE o1.product_id=p1.id
 AND o1.payment='2021-10-26'
 )
 
 CASE表达式(多行转一行)
 1.条件分支
 2.Oracle: DECODE;MySQL:IF
 3.ELSE子句可省略不写,默认是ELSE NULL
 4.两种写法:区别:简单CASE表达式只能对一列做判断
 搜索型CASE表达式:
  CASE  WHEN   <表达式> THEN  <表达式>
        WHEN   <表达式> THEN  <表达式>
  ELSE <表达式>
  END
 简单CASE表达式
 CASE <表达式>
    WHEN   <表达式> THEN  <表达式>
    WHEN   <表达式> THEN  <表达式>
 ELSE <表达式>
 END
 5.SUM(CASE xx WHEN xx THEN xx ELSE 0 END)
 
 集合运算:表的加减法
 1.UNION(表的加法),
 INTERSECT(交集),
 EXCEPT(表的减法):
    Oracle:MINUS
    MySQL不支持EXCEPT
 2.两表的列数必须相同
 3.包含重复行时带ALL选项
 4.ORDER BY子句只能在最后一次使用
 5.UNION ALL不需要去重,所以不会对结果集排序,性能好于UNION
 
 案例:选取掌握所有3个领域的技术的员工
 SELECT DISTINCT emp
 FROM Emp t1
 WHERE NOT EXISTS(
    SELECT skill
    FROM skills
    EXCEPT
    SELECT skill
    FROM emp t2
    WHERE t1.emp=t2.emp
 )
 
 联结
 内联结 INNER JOIN
 外联结 OUTER JOIN
 交叉联结CROSS JOIN笛卡尔积(表的乘法)

过时语法的内联结
SELECT t1.product_name,t1.price,t2.amount
FROM product t1,order t2
WHERE t1.id=t2.product_id
AND t2.order_id='1001';
这样的语法不推荐使用的原因:
1.无法马上判断出是内,外联结
2.无法分辨出WHERE子句里哪部分是联结条件,哪部分是过滤条件
3.后续可能废弃这样的语法


窗口函数 OVER( PARTITION By <列清单> ORDER BY <列清单>)
1.能够作为窗口函数的聚合函数(SUM,AVG,COUNT,MAX,MIN)
2.RANK,ROW_NUMBER,DENSE_RANK等专用窗口函数(没有参数)
3.窗口函数不具备GROUP BY子句的汇总功能,因此不能减少记录行数
4.有相同位次时,排序不同 RANK: 1 1 3;DENSE_RANK: 1 1 2;ROW_NUMBER: 1 2 3
5. 移动平均 moving average(都是相对当前行的位置)
ORDER BY 列1 ROWS n PRECEDING: 前n行,带上当前行也就是最近的(n+1)行
ORDER BY 列1 ROWS n FOLLOWING: 后n行,带上当前行也就是此后的(n+1)行
ORDER BY 列1 ROWS BETWEEN m  PRECEDING AND n FOLLOWING: 前m行到后n行
6. 如果想让结果集全局有序,则在结尾处使用ORDER BY子句
SELECT id,RANK(ORDER BY dt) AS RANKING
FROM t1
ORDER BY dt;

其他运算符
1.合计和小计:ROLLUP 卷起
一次计算出不同聚合键组合的结果
GROUP BY ROLLUP(c1,c2)
MySQL:GROUP BY t WITH ROLLUP
 其实等同于
 GROUP BY ()  默认使用NULL作为聚合键,聚合的结果其实相当于未分组的总值.超级分组 super group row
 GROUP BY c1
 GROUP BY (c1,c2)
 2.GROUPING(聚合键) 超级分组产生的NULL则返回1,其他为0
 3.CUBE:  2^n
  ROLLUP: n+1
 4.GROUPING SETS 指定聚合键组合
 
一条SQL语句可以操作多行数据.但是像JAVA这样的程序语言来说,数据访问是逐行进行的,要想处理多行数据,就需要循环遍历