SQL基础总结（背诵版）

2021/7/5 2:21:44

编程Tag： type select id SQL 总结 -- price product 背诵

本文主要是介绍SQL基础总结（背诵版），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

SQL基础教程

类型

DDL数据定义语言Data Definition Language
- CREATE
- DROP
- ALTER
DML数据操纵语言Data Manipulation Language
- SELECT
- INSERT
- DELETE
- UPDATE
DCL数据控制语言Data Control Language
- COMMIT
- ROLLBACK
- GRANT
- REVOKE

基本语法

mysql -u root -p password

-- 一定记得加;
show databases;
use shop; -- 某一数据库
show tables;
show tables from shop;
desc product;-- 表名，查看表的结构
select version();

数据库&表的增删改

表的创建

格式

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

命名规则

我们只能使用半角英文字母、数字、下划线（_）作为数据库、表和

列的名称
名称必须以半角英文字母开头
在同一个数据库中不能创建两个相同名称的表，在同一个表中也不能创建两个名称相同的列

数据类型的指定

所有的列都必须指定数据类型
四种基本的数据类型
- INTEGER
- CHAR(n)
  - 在括号中指定该列可以存储的字符串的长度（最大长度）。RDBMS 不同，长度单位也不一样，既存在使用字符个数的情况，也存在使用字节长度的情况
  - 定长字符串，使用半角空格补齐
- VARCHAR
  - 可变长字符串·
- DATE，‘2021-05-29’

约束的设置

NOT NULL
NULL
PRIMARY KEY(<列名>)

表的删除和更新

使用DROP TABLE语句来删除表
使用ALTER TABLE语句向表中添加列或者从表中删除列
ALTER TABLE 语句和DROP TABLE 语句一样，执行之后无法恢复

DROP TABLE <表名>;
ALTER TABLE <表名> ADD COLUMN <列的定义>；
ALTER TABLE <表名> DROP COLUMN <列名>；
#非标准sql，Mysql
RENAME TABLE Poduct to Product;

插入数据

START TRANSACTION
INSERT INTO Product VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
……
COMMIT;

查询基础

SELECT语句基础

列的查询（字句、换行）

SELECT <列名>，……
FROM <表名>；
-- 包含了SELECT和FROM两个子句，子句是SQL语句的组成要素，是以 SELECT或者FROM等作为起始的短语
-- 查询结果中列的顺序和SELECT 子句中的顺序相同
SELECT *
 FROM <表名>;
-- 如果使用星号的话，就无法设定列的显示顺序了。这时就会按照 CREATE TABLE语句的定义对列进行排序
-- 原则上希望大家能够以子句为单位进行换行（子句过长时，为方便起见可以换行）
-- 插入空行（无任何字符的行）会造成执行错误

为列设置别名

SQL 语句可以使用 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 子句中不仅可以书写列名，还可以书写常数、书写计算式

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

DISTINCT

想要删除重复行时，可以通过在 SELECT 子句中使用DISTINCT来实现
在使用 DISTINCT 时，NULL 也被视为一类数据
可以在多列之前使用
DISTINCT 关键字只能用在第一个列名之前

SELECT DISTINCT product_type
 FROM Product;
 -- 可以在多列之前使用，多列一致消重
SELECT DISTINCT product_type, regist_date
 FROM Product;

WHERE字句

通过 WHERE 子句来指定查询数据的条件
是首先通过WHERE 子句查询出符合指定条件的记录，然后再选取出 SELECT 语句指定的列
SQL中子句的书写顺序是固定的，不能随意更改

SELECT product_name, product_type
 FROM Product
 WHERE product_type = '衣服';

注释

-- 单行注释
/*
多行注释
任何注释都可以插在SQL语句（字句中间）中
*/

算术&比较运算符

算术运算符

算术运算符：+、-、*、/
所有包含 NULL 的计算，结果肯定是 NULL
类似 5/0 这样除数为 0 的话会发生错误，只有 NULL 除以 0 时不会发生错误，并且结果还是 NULL

比较运算符

在 WHERE 子句中通过使用比较运算符可以组合出各种各样的条件表达式
=、<>、>=、>、<=、<
日期，前后
字符串类型的数据原则上按照字典顺序进行排序
可以对计算结果进行比较
比较运算符作用于NULL返回不确定，不选取值
- 希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符
- 希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符

逻辑运算符

NOT、AND、OR
运算优先级：非与或
值可以归结为真TRUE和假FALSE其中之一的值称为真值
比较运算符在比较成立时返回真，不成立时返回假
在 SQL中还存在另外一个特定的真值——不确定UNKNOWN
将根据逻辑运算符对真值进行的操作及其结果汇总成的表称为真值表
SQL中的逻辑运算是包含对真、假和不确定进行运算的三值逻辑

聚合与排序

聚合函数

用于汇总的函数称为聚合函数或者聚集函数，所谓聚合，就是将多行汇总为一行

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

COUNT

-- COUNT(*)会得到包含NULL的数据行数
select count(*)
 from product;
-- COUNT(<列名>)会得到NULL之外的数据行数
select count(purchase_price)
 from product;

-- 聚合函数会先将NULL排除在外，再进行计算
select sum(sale_price)
 from product;

select avg(sale_price)
 from product;

MIN & MAX

-- SUM/AVG 函数只能对数值类型的列使用，而MAX/MIN 函数原则上可以适用于任何数据类型的列(日期，字符串)
select max(sale_price),min(purchase_price)
 from product;

使用DISTINCT去重

-- 想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT
-- 计算去除重复数据后的数据行数
select count(distinct product_type)
 from product;
-- 结果为3
-- 写在括号外的话，就会先计算出数据行数，然后再删除重复数据
select distinct count(product_type)
 from product;
-- 结果为8
-- SUM聚合函数使用DISTINCT
select sum(sale_price), sum(distinct sale_price)
 from product;

Group By子句

GROUP BY就像是切分表的一把刀

语法结构

select <列名1>,<列名2>,<列名3>, ……
 from <表名>
group by <列名1>,<列名2>,<列名3>,……;
-- exqmple
select product_type, count(*)
  from product
 group by product_type

在 GROUP BY 子句中指定的列称为聚合键或者分组列
子句的书写顺序（暂定）
1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

包含NULL

聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来

WHERE&GROUP BY

select <列名1>,<列名2>,<列名3>, ……
  from <表名>
 where
 group by <列名1>,<列名2>,<列名3> ……;
-- example
select purchase_price, count(*)
  from product
 where product_type = '衣服'
 group by purchase_price;

GROUP BY 和 WHERE 并用时 SELECT 语句的执行顺序

FROM → WHERE → GROUP BY → SELECT

聚合函数&GROUP BY子句常见错误

规则：select 子句中只能存在以下三种元素：常数、聚合函数、聚合键（不能包含聚合键之外的列）
常见错误：group by 子句中写了列的别名

-- 错误示例
select product_type as pt, count(*)
  from product
 group by pt;

规则：GROUP BY子句结果的显示是无序的
常见错误：在where子句中使用聚合函数

-- 错误示例
select product_type, count(*)
  from product
 where count(*) = 2
 group by product_type;

HAVING子句

对集合指定条件需要HAVING子句
HAVING子句语

select <列名1>,<列名2>,<列名3> ……
  from <表名>
 group by <列名1>,<列名2>,<列名3>, ……
 having <分组结果对应的条件>
-- 示例
select product_type, count(*)
  from product
 group by product_type
having count(*)=2;

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

HAVING子句构成要素

HAVING 子句中能够使用的3种要素：常数、聚合函数、聚合键

WHERE&HAVING?

有些条件既可以写在 HAVING 子句当中，又可以写在 WHERE 子句当中——聚合键所对应的条件

-- HAVING=子句
select product_type, count(*)
  from product
 group by product_type
having product_type = '衣服'
-- WHERE子句
select product_type, count(*)
  from product
 where product_type = '依附'
 group by product_type;

聚合键所对应的条件还是应该书写在 WHERE 子句之中
- WHERE 子句 = 指定行所对应的条件
  
  HAVING 子句 = 指定组所对应的条件
- 通常情况下，为了得到相同的结果，将条件写在 WHERE 子句中要比写在 HAVING 子句中的处理速度更快，返回结果所需的时间更短

ORDER BY子句

通常，从表中抽取数据时，如果没有特别指定顺序，最终排列顺序便无从得知
即使是同一条 SELECT 语句，每次执行时排列顺序很可能发生改变

ORDER BY子句

ORDER BY 子句的语法

select <列名1>,<列名2>,<列名3>, ……
  from <表名>
 order by <排序基准列1>,<排序基准列2>,……;
-- 示例
select *
  from product
 order by sale_price;

不论何种情况，ORDER BY 子句都需要写在 SELECT 语句的末尾
ORDER BY子句中书写的列名称为排序键
子句的书写顺序
1. SELECT 子句 → 2. FROM 子句 → 3. WHERE 子句 →4. GROUP BY 子句 → 5. HAVING 子句 → 6. ORDER BY 子句

升序降序

降序排列时，在列名后面使用 DESC 关键字
升序排列时，在列名后面使用 ASC 关键字，省略该关键字时会默认使用升序进行排序

select *
  from prduct
 order by sale_price desc;

由于 ASC 和 DESC 这两个关键字是以列为单位指定的，因此可以同时指定一个列为升序，指定其他列为降序

指定多个排序键

规则是优先使用左侧的键，如果该列存在相同值的话，再接着参考右侧的键

select *
  from product
 order by sale_price, product_id

NULL的顺序

使用含有 NULL 的列作为排序键时，NULL 会在结果的开头或末尾汇总显示
究竟是在开头显示还是在末尾显示，并没有特殊规定。某些 DBMS中可以指定 NULL 在开头或末尾显示，希望大家对自己使用的 DBMS 的功能研究一下

在排序建中使用显示用的别名

在 ORDER BY 子句中却是允许使用别名的

select product_id as id, sale_price as sp, purchae_price
  from product
 order by sp, id;

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

ORDER BY子句中可以使用的列

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

-- SELECT子句中未使用的列
SELECT product_name, sale_price, purchase_price
 FROM Product
ORDER BY product_id;
-- 聚合函数
select product_type, count(*)
  from product 
 group by product_type
order by count(*);

不要使用列编号

在 ORDER BY 子句中，还可以使用在 SELECT 子句中出现的列所对应的编号
列编号是指 SELECT 子句中的列按照从左到右的顺序进行排列时所对应的编号（1, 2, 3, …）

-- 通过列名指定
SELECT product_id, product_name, sale_price, purchase_price
 FROM Product
ORDER BY sale_price DESC, product_id;
-- 通过列编号指定
SELECT product_id, product_name, sale_price, purchase_price
 FROM Product
ORDER BY 3 DESC, 1;

不推荐使用
- 可读性差

数据更新

INSERT语句

约束：DEFAULT 0

-- 创建表
CREATE TABLE ProductIns
(product_id CHAR(4) NOT NULL,
 product_name VARCHAR(100) NOT NULL,
 product_type VARCHAR(32) NOT NULL,
 sale_price INTEGER DEFAULT 0,
 purchase_price INTEGER,
 regist_date DATE,
 PRIMARY KEY (product_id));

语法结构

insert into <表名>(列1, 列2, 列3 ……) values (值1, 值2, 值3, ……);
-- 示例
insert into productins
 (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
 values('0001','T恤衫, ’衣服',1000,500,'2009-09-20');

字符型、日期型数据使用单引号括起来
将列名和值用逗号隔开，分别括在（）内，这种形式称为清单
清单的列数必须保持一致

多行INSERT

-- 多行INSERT（Oracle以外）
INSERT INTO ProductIns VALUES
 ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
 ('0003', '运动T恤', '衣服', 4000, 2800, NULL),
 ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

若不正确会发生INSERT 错误，但是由于是多行插入，和特定的单一行插入相比，想要找出到底是哪行哪个地方出错了，就变得十分困难

列清单的省略

对表进行全列 INSERT 时，可以省略表名后的列清单
这时 VALUES子句的值会默认按照从左到右的顺序赋给每一列

insert into productins values('0005','高压锅', '厨房用具', 6800, 5000, '2009-01-15')

插入NULL

INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES子句的值清单中写入 NULL

插入默认值

显式插入

insert into productins
 values('007', '擦菜板', '厨房用具', default, 790, '2009-04-28');

隐式插入

在列清单和 VALUES清单 中省略设定了默认值的列
如果省略了没有设定默认值的列，该列的值就会被设定为 NULL

从其他表中复制数据

-- 表结构与product相同
-- 用来插入数据的商品复制表
CREATE TABLE ProductCopy
(product_id CHAR(4) NOT NULL,
 product_name VARCHAR(100) NOT NULL,
 product_type VARCHAR(32) NOT NULL,
 sale_price INTEGER ,
 purchase_price INTEGER ,
 regist_date DATE ,
 PRIMARY KEY (product_id));

INSERT … SELECT 语句

-- 将商品表中的数据复制到商品复制表中
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语句可以在需要进行数据备份时使用
INSERT语句的SELECT语句中，可以使用WHERE子句或者GROUP BY子句等任何SQL语法（但使用ORDER BY子句并不会产生任何效果）

牛客网刷题所得

insert into：插入数据,如果主键重复，则报错
insert repalce into：插入替换数据,如果存在主键或unique数据则替换数据
insert ignore into：如果存在数据,则忽略

insert IGNORE into actor 
values(3,'ED','CHASE','2006-02-15 12:34:33');

DELETE语句

删除数据方式
- DROP TABLE 语句
```
DROP TABLE <表名>;
```
- DELETE 语句
不管使用哪种方法，删除数据时都要慎重，一旦误删，想要恢复数据就会变得十分困难

基本语法

-- 删除数据行
delete from <表名>;
-- 示例
delete from product
-- DELETE语句的删除对象并不是表或者列，而是记录（行）

搜索型DELETE

delete from <表名>
 where <条件>;
-- 可以通过WHERE子句指定对象条件来删除部分数据
-- 正是删除前确认要删除的结果

TRUNCATE

truncate <表名>;
执行速度比delete from <表名>;快

UPDATE语句

基本语法

update <表名>
   set <列名> = <表达式>;
-- 将更新对象的列和更新后的值都记述在 SET子句中
-- 示例
update product
   set regist_date = '2009-10-10';

搜索型UPDATE

update <表名>
   set <列名> = <表达式>
 where <条件>;
-- 示例
update product
   set sale_price = sale_price*10
 where product_type = '厨房用具';

NULL 清空

update product
   set regist_date = NULL
 where product_id = '008';

多列更新

-- 使用逗号对列进行分隔排列
UPDATE Product
   SET sale_price=sale_price*10,purchase_price=purchase_price/2
 WHERE product_type = '厨房用具';

事务

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

语法结构

使用事务开始语句和事务结束语句，将一系列 DML 语句（INSERT/UPDATE/DELETE 语句）括起来，就实现了一个事务处理

事务开始语句;
	DML语句1;
	DML语句2;
	DML语句3;
事务结束语句;

事务开始语句

START TRANSACTION

start transaction;
	-- 将T恤衫的销售单价降低1000日元
	update product
	   set sale_price = sale_price - 1000
	 where product_name = 'T恤衫';
	 
	--将运动T恤的销售单价上浮1000日元
	update product
	   set sale_price = sale_price + 1000
	 where product_name = '运动T恤';
commit;

事务结束语句

结束事务的指令有两种

COMMIT提交处理

提交事务包含的全部更新处理的结束指令
相当于文件处理中的覆盖保存
一旦提交，就无法恢复到事务开始前的状态

ROLLBACK取消处理

取消事务包含的全部更新处理的结束指令
相当于文件处理中的放弃保存
一旦回滚，数据库就会恢复到事务开始之前的状态

start transaction
	-- 将T恤衫的销售单价降低1000日元
	update product
	   set sale_price = sale_price - 1000
	 where product_name = 'T恤衫';
	 
	--将运动T恤的销售单价上浮1000日元
	update product
	   set sale_price = sale_price + 1000
	 where product_name = '运动T恤';
rollback;

ACID特性

原子性Atomicity

原子性是指在事务结束时，其中所包含的更新处理要么全部执行commit，要

么完全不执行rollback

一致性Consistency

也称完整性
事务中包含的处理要满足数据库提前设置的约束
不合法SQL 处理会被取消，不会执行

隔离性Isolation

独立性
隔离性指的是保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套
在某个事务中进行的更改，在该事务结束之前，对其他事务而言是不可见的

持久性Durability

又称耐久性
在事务（不论是提交还是回滚）结束后，DBMS 能够保证该时间点的数据状态会被保存的特性

复杂查询

视图

视图就是保存好的 SELECT 语句
我们从视图中读取数据时，视图会在内部执行该 SELECT 语句并创建出一张临时表
优点
- 视图无需保存数据，因此可以节省存储设备的容量
- 应该将经常使用的SELECT语句做成视图，这样就不用每次都重新书写了
- 视图中的数据会随着原表的变化自动更新

创建视图

CREATE VIEW 语句
SELECT 语句中列的排列顺序和视图中列的排列顺序相同
视图的列名在视图名称之后的列表中定义

create view 视图名称(<视图列名1>,<视图列名2>,<视图列名3>……)
as
<select 语句>
-- 示例
create view productview (product_type, cnt_product)
as
select product_type, count(*)
  from product
 group by product_type;
 -- 展示视图
 show tables;

使用视图

视图和表一样，可以书写在SELECT 语句的 FROM 子句之中

select product_type, cnt_product
  from productsum;

Product 表中的数据更新之后，视图也会自动更新
FROM子句中使用视图查询通常有两个步骤：
- 执行定义视图的SELECT语句
- 执行FROM子句中使用视图的SELECT语句

多重视图

cerate view productsumjim(product_type, cnt_product)
as
select product_type, cnt_product
  from productsum
 where product_type = '办公用品';

我们还是应该尽量避免在视图的基础上创建视图
这是因为对多数 DBMS 来说，多重视图会降低 SQL 的性能

视图的限制

限制1：ORDER BY语句

定义视图时不能使用ORDER BY子句
视图和表一样，数据行都是没有顺序的

限制2：对视图进行更新

对于 INSERT、DELETE、UPDATE 这类更新语句（更新数据的 SQL）
如果定义视图的 SELECT 语句能够满足某些条件，那么这个视图就可以被更新
代表性的条件（只要创建视图的表唯一且可以改变）
- SELECT 子句中未使用 DISTINCT
- FROM 子句中只有一张表
- – 未使用聚合
- 未使用 GROUP BY 子句
- 未使用 HAVING 子句
如果原表可以更新，那么视图中的数据也可以更新
视图和表需要同时进行更新

CREATE VIEW ProductJim (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
AS 
SELECT *
 FROM Product
 WHERE product_type = '办公用品';

INSERT INTO ProductJim VALUES ('0009', '印章', '办公用品', 95, 10, '2009-11-30');

是对于原表来说却需要设置各种各样的约束（主键和 NOT NULL 等）

删除视图

DROP VIEW语句

drop view 视图名称(<视图列名1>,<视图列名2>……);
-- 示例
drop view productview;

子查询

子查询的特点概括起来就是一张一次性视图
子查询就是将用来定义视图的SELECT语句直接用于FROM子句当中

-- 示例
select product_type, cnt_product
  from (select product_type, count(*) as cnt_product
          from product
         group by product_type) as productsum

子查询作为内层查询会首先执行

多层嵌套的子查询

select product_type, cnt_product
  from(select *
         from (select product_type, count(*) as cnt_product
                 from product
                group by product_type) as productsum
              where cnt_product=4) as productsum2;

子查询嵌套层数的增加，SQL 语句会变得越来越难读懂，性能也会越来越差
尽量避免使用多层嵌套的子查询

子查询名称

原则上子查询必须设定名称
尽量从处理内容的角度出发为子查询设定恰当的名称

标量子查询

标量子查询就是返回单一值的子查询
标量子查询的返回值可以用在 = 或者 <> 这样需要单一值的比较运算符之中

在WHERE子句中使用标量子查询

WHERE 子句中不能使用聚合函数
使用子查询的 SQL 会从子查询开始执行

select product_id, product_name, sale_price
  from product
 where sale_price > (select avg(sale_price)
                     from product);

书写位置

通常任何可以使用单一值的位置都可以使用标量子查询
能够使用常数或者列名的地方，无论是 SELECT 子句、WHERE 子句、GROUP BY 子句、HAVING 子句，还是

ORDER BY 子句，几乎所有的地方都可以使用
在SELECT子句中使用标量子查询

select product_id, product_name, sale_price, (select avg(sale_price) from product) as avg_price
  from product;

在HAVING子句中使用标量子查询

select product_type, avg(sale_price)
  from product
 group by product_type
having avg(sale_price) > (select avg(sale_price) from product);

注意事项

该子查询绝对不能返回多行结果
如果子查询返回了多行结果，那么它就不再是标量子查询，而仅仅是一个普通的子查询了，因此不能被用在 = 或者 <> 等需要单一输入值的运算符当中，也不能用在 SELECT 等子句当中

关联子查询

在细分的组内进行比较时，需要使用关联子查询

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);

相比标量子查询，在子查询中添加的 WHERE 子句的条件
在使用关联子查询时，需要在表所对应的列名之前加上表的别名，以< 表名 >.< 列名 >的形式记述

集合切分

关联子查询也是用来对集合进行切分的
切分之后分别计算，每次只返回一条结果（类似标量子查询）

结合条件

结合条件一定要写在子查询中
关联名称的作用域：子查询内部设定的关联名称，只能在该子查询内部使用；内部可以看到外部，而外部看不到内部

-- 错误示例
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);

函数&谓词&CASE表达式

函数

算术函数
字符串函数
日期函数
转换函数
聚合函数

算术函数

数据类型Numeric
- 通过 NUMERIC( 全体位数 , 小数位数 ) 的形式来指定数值的大小

-- 绝对值函数：ABS(数值)
-- ABS 函数的参数为 NULL 时，结果也是 NULL
-- 并非只有 ABS 函数如此，其实绝大多数函数对于NULL都返回NULL
select m, abs(m) as abs_col
 from samplemath;
 
-- 求余：MOD(被除数，除数)
select n, p, mod(n,p) as mol_col
  from samplemath;

-- 四舍五入：round(对象数值, 保留小数的位数)
select m, n, round(m,n) as round_col
  from samplemath;

字符串函数

-- 拼接||
-- 进行字符串拼接时，如果其中包含 NULL，那么得到的结果也是NULL
-- 三个以上的字符串也可以进行拼接
select str1, str2, str3, concat(str1, str2, str3) as str_concat
from sampleStr;

-- 字符串长度length(str)，以字节为单位
select str1, length(str1) as len_str
  from sampleStr;
-- 与半角英文字母占用 1 字节不同，汉字这样的全角字符会占用2个以上的字节（称为多字节字符）
-- 使用 MySQL 中的LENGTH 这样以字节为单位的函数进行计算时，“LENGTH( 山田 )”的返回结果是4
-- 同样是 LENGTH 函数，不同DBMS的执行结果也不尽相同

-- 小写转换lower(str)
-- 只针对英文字母使用
select str1, lower(str1) as low_str
  from sampleStr
 where str1 in ('ABC','abc','aBC','山田');

-- 大写转换upper(str)
select str1, upper(str1) as upp_str
  from sampleStr
 where str1 in ('ABC','abc','aBC','山田');

-- 字符串转换replace(对象字符串,替换前字符串,替换后字符串)
select str1, str2, str3, replace(str1, str2, str3) as rep_str
  from sampleStr;
-- 对str1做替换
-- str1&str2&str3中任一为空，结果为空
-- 若所有字段为非空
-- str1中若存在str2的部分，则str2的部分被全部替换为str3

-- 字符串截取substring(对象字符串 from 截取的起始位置 for 截取的字段数)
select str1, substring(str1 from 3 for 2) as sub_str
  from sampleStr;

日期函数

-- 当前日期current_date
select current_date;
--'2021-07-01'

-- 当前时间current_time
select current_time;
-- '22:06:25'

--当前时间和日期current_timestamp
select current_timestamp;
-- '2021-07-01 22:06:25'

-- 截取日期元素extract
-- 可以截取出日期数据的一部分，如年、月、日、时、分、秒，返回值为数值类型
-- extract(日期元素 from 日期)
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_timestamp)as second;

转换函数

数据类型转换&值的转换

-- 类型转换cast
-- cast(转换前的值,想要转换的数据类型)
select cast('0001' as integer) as int_col;
select cast('2021-07-01' as date) as date_col;

-- 将NULL转换为其他值coalesce
-- coalesce(数据1,数据2,数据3……)
-- 该函数会返回可变参数中左侧开始第1个不是NULL的值
-- 参数个数是可变的，因此可以根据需要无限增加
select coalesce(NULL,1) as col1, coalesce(NULL, 'test', NULL) as col2, coalesce(NULL, NULL, '2021-07-02') as col3;
select coalesce(str2,NULL)
  from sampleStr;

谓词

谓词就是返回值为真值的函数（TRUE、FALSE、UNKNOWN）
通常的谓词都无法与NULL进行比较，结果一般不会包含NULL结果
LIKE谓词
BETWEEN谓词
IS NULL ＆ IS NOT NULL
IN
EXISTS

LIKE谓词

字符串的部分一致查询

部分一致

前方一致：选取出作为查询条件的字符串与查询对象字符串起始部分相同的记录的查询方法
中间一致：选取出查询对象字符串中含有作为查询条件的字符串的记录的查询方法（起始、中间、最后）
后方一致：选取出作为查询条件的字符串与查询对象字符串的末尾部分相同的记录的查询方法
不使用 = 来指定条件字符串，而以字符串中是否包含该条件的规则为基础的查询称为模式匹配，其中的

模式也就是前面提到的规则

通配符

%替代一个或多个字符（经测验，MySQL可为零个字符）
-仅替代一个字符

实现

-- 前方一致查询
select * 
  from sampleLike
 where strcol like'ddd%';
 
-- 中间一致查询
select * 
  from sampleLike
 where strcol like '%ddd%';
 
-- 后方一致查询
select * 
  from sampleLike
 where strcol like '%ddd';
 
-- -单个字符
select * 
  from sampleLike
 where strcol like 'ddd--';

BETWEEN

范围查询

-- 三个参数
select product_name, product_price
  from product
 where product_price between 100 and 1000;
-- 结果包含临界值

-- 如果不想让结果中包含临界值，就必须使用 < 和 >
select product_name, product_price
  from product
 where product_price > 100 and product_price < 1000;

IS NULL& IS NOT NULL

判断是否为空：IS NULL 7 IS NOT NULL

select product_name, purchase_price
  from product
 where purchase_price is null;
 
select product_name, purchase_price
  from product
 where purchase_price is null;

IN

OR 的简单用法

-- in(值1,值2,……)
select product_name, purchase_price
  from product
 where purchase_price in (320, 500, 1000);
 
-- not in 
select product_name, purchase_price
  from product
 where purchase_price in (320, 500, 1000);

-- 在使用IN和NOT IN时是无法选取出NULL数据的

使用子查询作为IN谓词的参数

IN 谓词（NOT IN谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数（表&视图）

-- primary key(col1, col2) 把属性集作为主键
-- in
select product_name, sale_price
  from product
 where product_id in (select product_d 
                        from shopproduct
                       where shop_id = '000C');
-- 易维护程序&免维护程序

-- not in
select product_name, sale_price
  from product
 where product_id not in (select product_d 
                        from shopproduct
                       where shop_id = '000A');

NOT IN的参数中包含NULL时结果通常会为空
使用子查询作为 NOT IN 的参数时，该子查询的返回值也不能是 NULL

EXISTS

-- exist
-- 判断是否存在满足某种条件的记录，如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）
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 通常都会使用关联子查询作为参数
-- 主语是记录-> select *

-- not exist
select product_name, sale_price
  from product as P
 where not exist(select * 
                   from shopproduct as sp
                  where sp.shop_id = '000A'
                    and sp.product_id = p.product_id);
-- NOT EXIST与EXIST相反，当不存在满足子查询中指定条件的记录时返回真（TRUE）

CASE表达式

分为简单CASE表达式和搜索CASE表达式
搜索 CASE 表达式包含了简单 CASE 表达式的全部功能

搜索case表达式语法

case when <求值表达式> then<表达式>
	 when <求值表达式> then<表达式>
	 ...
	 else<表达式>
end
-- 求值表达式：返回值为真值（TRUE/FALSE/UNKNOWN）的表达式；也可以将其看作使用 =、!= 或者 LIKE、BETWEEN 等谓词编写出来的表达式
-- 表达式：表达式最终会返回一个值

搜索case表达式使用方法

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 product_type
    from product;
-- ELSE子句也可以省略不写，这时会被默认为ELSE NULL
-- 为了防止有人漏读，还是希望大家能够显示地写出ELSE子句
-- CASE表达式中的END不能省略

CASE 表达式是一个表达式；表达式可以书写在任意位置

-- 对按照商品种类计算出的销售单价合计值进行行列转换
select sum(case when product_type = '衣服' then sale_price else NULL end) as sum_price_cloth,
	   sum(case when product_type = '厨房用具' then sale_price else NULL end) as sum_price_chicken,
	   sum(case when product_type = '办公用品' then sale_price else NULL end) as sum_price_office
  from product;
  
-- 练习题
select sum(case when sale_price <= 1000 then 1 else NULL end) as low_price,
	   sum(case when 1000 < sale_price and sale_price <= 3000 then 1 else NULL end) as mid_price,
	   sum(case when 3000 < sale_price then 1 else NULL end) as high_price
  from product;

练习题验证在mysql中比较运算符不可以连用，会出错

简单case表达式

-- 语法
CASE <表达式>
 WHEN <表达式> THEN <表达式>
 WHEN <表达式> THEN <表达式>
 WHEN <表达式> THEN <表达式>
 . . .
 ELSE <表达式>
END

-- 简单CASE表达式示例
SELECT product_name,
 CASE product_type
 WHEN '衣服' THEN 'A ：' | | product_type
 WHEN '办公用品' THEN 'B ：' | | product_type
 WHEN '厨房用具' THEN 'C ：' | | product_type
 ELSE NULL
 END AS abc_product_type
 FROM Product;

特殊CASE表达式

-- MySQL中使用IF代替CASE表达式
-- 只能在特定的 DBMS 中使用，并且能够使用的条件也没有CASE表达式那么丰富，因此并没有什么优势
-- 希望大家尽量不要使用这些特定的SQL语句

集合运算

通过以行方向（竖）为单位的集合运算符和以列方向（横）为单位的联结，就可以将分散在多张表中的数据组合成为期望的结果

集合运算

记录的集合,具体来说，表、视图和查询的执行结果都是记录的集合
用来进行集合运算的运算符称为集合运算符

表的加减法

加法-UNION

UNION并集

-- 示例，并集，可以通过文氏图展示
select product_id, product_name
  from product
 union
select product_id, product_name
  from product2

UNION 等集合运算符通常都会除去重复的记录

集合运算的注意事项

作为运算对象的记录的列数必须相同

-- 列数不一致时会发生错误
SELECT product_id, product_name
 FROM Product
UNION
SELECT product_id, product_name, sale_price
 FROM Product2;

作为运算对象的记录中列的类型必须一致

从左侧开始，相同位置上的列必须是同一数据类型

-- 数据类型不一致时会发生错误
SELECT product_id, sale_price
 FROM Product
UNION
SELECT product_id, regist_date
 FROM Product2;

可以使用任何SELECT语句，但ORDER BY子句只能在最后使用一次

可以使用任何形式的 SELECT 语句，之前学过的 WHERE、GROUP BY、HAVING 等子句都可以使用

select product_id, product_name
  from proudct
 where product_type = '厨房用具'
 union
select product_id, product_name
  from product2
 where product_type = '厨房用具'
 order by product_id;

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

在 UNION 的结果中保留重复行只需要在 UNION 后面添加 ALL 关键字就可以了

-- 保留重复行
select product_id, product_name
  from product
 union all
select product_id, product_name
  from product2

在集合运算符中使用ALL选项，可以保留重复行

交集INTERSECT&差EXCEPT

mysql中不支持交集和差集运算

-- 交集运算
SELECT product_id, product_name
 FROM Product
INTERSECT
SELECT product_id, product_name
 FROM Product2
ORDER BY product_id;

-- 差集运算
SELECT product_id, product_name
 FROM Product
EXCEPT
SELECT product_id, product_name
 FROM Product2
ORDER BY product_id;

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

使用联结就可以从多张表（3 张以上的表也没关系）中选取数据了
SQL 的联结根据其用途可以分为很多种类，这里希望大家掌握的有两种，内联结和外联结
联结键

内联接INNER JOIN

-- 示例
select sp.shop_id, sp.shop_name, sp.product_id, p.product_name, p.sqle_price
  from shopproduct as sp inner join product as p 
    on sp.product_id = p.product_id;

-- FORM子句
-- 进行联结时需要在FROM子句中使用多张表，使用关键字INNER JOIN就可以将两张表联结在一起了
-- SP和P分别是这两张表的别名，别名并不是必需的，为了增强可读性，建议使用别名

-- ON子句
-- 进行内联结时必须使用ON子句，并且要书写在FROM和WHERE之间
-- 在ON之后指定两张表联结所使用的列（联结键），ON 是专门用来指定联结条件的，它能起到与 WHERE 相同的作用
-- 联结条件也可以使用 = 来记述，在语法上，还可以使用<=和BETWEEN等谓词

-- SELECT子句
-- 使用联结时SELECT子句中的列需要按照“<表的别名>.<列名>”的格式进行书写

使用联结运算将满足相同规则的表联结起来时，WHERE、GROUP BY、HAVING、ORDER BY 等工具都可以正常使用

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
 where sp.shop_id = '000A';

外联结OUTER JOIN

--示例 right outer join
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;

内联结只能选取出同时存在于两张表中的数据，对于外联结来说，只要数据存在于某一张表当中，就能够读取出来

外联结名称的由来也跟 NULL 有关，即“结果中包含原表中不存在（在原表之外）的信息

只包含表内信息的联结也就被称为内联结了
最终的结果中会包含主表内所有的数据

指定主表的关键字是 LEFT 和RIGHT

顾名思义，使用 LEFT 时 FROM 子句中写在左侧的表是主表，使用 RIGHT时右侧的表是主表

-- 示例 left outer join
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
 FROM Product AS P LEFT OUTER JOIN ShopProduct AS SP
 ON SP.product_id = P.product_id;

使用二者所得到的结果完全相同（存疑）
经验证在mysql中左联结与右联结结果不同

select case when SP.shop_id is not null
			then SP.shop_id
			else 'unknown'
			end,
		case when SP.shop_id is not null
			 then SP.shop_name
			 else 'unknown'
			 end
		, SP.product_id, P.product_name
  from shopproduct as sp right outer join product as p
    on sp.product_id = p.product_id;
    
select SP.shop_id, SP.shop_name, SP.product_id, P.product_name
  from shopproduct as sp left outer join product as p
    on sp.product_id = p.product_id;

3张以上表的联接

-- 示例
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price, IP.inventory_quantity
 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
 WHERE IP.inventory_id = 'P001';

交叉联结CROSS JOIN

CROSS JOIN（笛卡儿积）

-- 示例
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
 FROM ShopProduct AS SP CROSS JOIN Product AS P;

进行交叉联结时无法使用内联结和外联结中所使用的 ON 子句

交叉联结是对两张表中的全部记录进行交叉组合

结果中的记录数通常是两张表中行数的乘积

过时语法

能读

-- 使用过时语法的内联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, 
P.sale_price
 FROM ShopProduct SP, Product P
 WHERE SP.product_id = P.product_id
 AND SP.shop_id = '000A';

SQL高级处理

窗口函数

窗口函数也称为OLAP函数
OLAP 是OnLine Analytical Processing的简称，意思是对数据库数据进行实时分析处理
窗口函数就是为了实现OLAP而添加的标准SQL功能

窗口函数语法

<窗口函数> OVER ([PARTITION BY <列清单>] 
 ORDER BY <排序用列清单>)
-- []中的内容可以省略 
-- 其中重要的关键字是PARTITION BY和ORDER BY

能够作为窗口函数使用的函数
窗口函数大体可以分为两种：
- 能够作为窗口函数的聚合函数（SUM、AVG、COUNT、MAX、MIN）
- RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数

RANK函数-基本使用方法

RANK 是用来计算 记录排序 的函数

-- 增加一列ranking，表示顺序
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能够指定按照哪一列、何种顺序进行排序

可以通过关键字ASC/DESC 来指定升序和降序

省略该关键字时会默认按照 ASC
一言以蔽之，PARTITION BY 在横向上对表进行分组，而 ORDER BY决定了纵向排序的规则
通过PARTTION BY分组后的记录的集合可以称为窗口
窗口函数兼具分组和排序两种功能

PARTITION 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_numbering
  from product;

由于专用窗口函数无需参数，因此通常括号中都是空的

窗口函数的适用范围

原则上窗口函数==只能在SELECT子句==中使用

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

所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同
依据order by 指定的顺序累计
SUM函数

select product_id, product_name, sale_price,
	sum(sale_price) over (order by product_id) as current_sum
from product;

AVG函数

select product_name, product_type, sale_price,
	avg(sale_price) over(order by product_id) as current_avg
  from product;

计算移动平均

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

-- 需要在ORDER BY子句之后使用指定范围的关键字
-- 使用了ROWS行和PRECEDING之前两个关键字，将框架指定为“截止到之前~行”
-- 将框架指定为“截止到之前2行”，也就是将作为汇总对象的记录限定为如下的“最靠近的3行”
SELECT product_id, product_name, sale_price,
 AVG (sale_price) OVER (ORDER BY product_id
 						ROWS 2 PRECEDING) AS moving_avg
 FROM Product;

这种统计方法称为移动平均
使用关键字 FOLLOWING之后替换PRECEDING，就可以指定“截止到之后~行”作为框架了
如果希望将当前记录的前后行作为汇总对象时，同时使用PRECEDING和FOLLOWING关键字来实现

select product_name, product_type, sale_price,
	avg(sale_price) over(order by product_id
                        rows between 1 preceding and 1 following) as moving_avg
  from product;

两个GROUP BY

OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响
有些 DBMS 也可以按照窗口函数的 ORDER BY 子句所指定的顺序对结果进行排序，但那也仅仅是个例而已
在SELECT 语句的最后，使用 ORDER BY子句进行指定，以保证结果的顺序

GROUPING函数

合计行是不指定聚合键时得到的汇总结果
如果想要获得那样的结果，通常的做法是分别计算出合计行和按照商品种类进行汇总的结果，然后通过UNION ALL连接在一起

select '合计' as product_type, sum(sale_price)
  from product
union all 
select product_type, sum(sale_price) 
  from product
 group by product_type;

GROUPING 运算符包含以下 3 种
- ROLLUP
- CUBE
- GROUPING SETS

ROLLUP-同时得出合计和小计

ROLLUP是卷起的意思
一次计算出不同聚合键组合的结果
超级分组GROUP BY()：超级分组记录默认使用NULL作为聚合键。

-- 示例1
SELECT product_type, SUM(sale_price) AS sum_price
 FROM Product
GROUP BY product_type WITH ROLLUP;
-- 一次计算出了如下两种组合的汇总结果
-- GROUP BY ()
-- GROUP BY (product_type)

-- 示例2
select product_type, regist_data, sum(sale_price) as sum_price
  from product
 group by product_type, regist_data with rollup;
-- 如下三种组合的汇总结果
-- GROUP BY ()
-- GROUP BY (product_type) 
-- GROUP BY (product_type, regist_date)

使用ROLLUP时多出了最上方的合计行以及 3 条不同商品种类的小计行（也就是未使用登记日期作为聚合键的记录），这 4 行就是我们所说的超级分组记录

GROUPING函数-让NULL更容易区分

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

SELECT GROUPING(product_type) AS product_type, 
       GROUPING(regist_data) AS regist_date,
       SUM(sale_price) AS sum_price
 FROM Product 
GROUP BY product_type, regist_data with rollup;

使用 GROUPING 函数还能在超级分组记录的键值中插入字符串

当 GROUPING 函数的返回值为 1 时，指定“合计”或者“小计”等字符串

其他情况返回通常的列的值

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

CAST(regist_date AS VARCHAR(16))：这是为了满足CASE表达式所有分支的返回值必须一致的条件

CUBE-用数据来搭积木

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

所谓CUBE，就是将GROUP BY子句中聚合键的“所有可能的组合”的汇总结果集中到一个结果中
组合的个数就是 2 n 2^n 2n（n 是聚合键的个数）

如上例：GROUP BY ()；GROUP BY (product_type) ；GROUP BY (regist_date)；GROUP BY (product_type, regist_date)

GROUPING SETS-取得期望的积木

GROUPING SETS：可以用于从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 GROUPING SETS (product_type, regist_date);

这篇关于SQL基础总结（背诵版）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！