sql基础笔记

D:\”program files“\PostgreSQL\9.5\bin\psql.exe –U postgres

语句执行顺序（group by 比 select前）

groub by->select->order by

insert into a1 select * from a

使用group by 时select 后字段必须是在group by 后出现过的

(1)from
(3) join
(2) on
(4) where
(5)group by(开始使用select中的别名，后面的语句中都可以使用)
(6) avg,sum…
(7)having
(8) select
(9) distinct
(10) order by

表清空

truncate不能加where, group by, order by清空表比delete效率更高

从其它表中复制数据

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;

新增列

alter table xxx add column newColum varchar not null;

事务开启与提交

begin transaction;

…

commit;

视图

表中保存的是实际数据

视图中保存的是select语句

1.create view actor_name_view (first_name_v,last_name_v) as
select first_name ,last_name from actor

2.create view actor_name_view as
select first_name first_name_v,last_name last_name_v from actor;

视图两大特点

1.无需保存数据，节省存储容量

2.将频繁使用的select语句保存成视图，不用重复书写，由于是执行select语句，视图中的数据随着表的内容变化

使用视图的查询

1.首先执行定义视图的select语句

2.根据得到的结果再执行from子句中使用视图的select语句

应该避免在视图上建立视图，多重视图会降低sql性能

视图和表需要同时进行更新，汇总的到的视图不能更新

子查询

– 在FROM子句中直接书写定义视图的SELECT语句
SELECT product_type, cnt_product FROM ( SELECT product_type, COUNT(*) AS cnt_product FROM Product GROUP BY product_type ) AS ProductSum;

先执行内层，再执行外层

标量子查询

标量子查询必须而且只能返回 1 行 1列的结果(不能返回多行结果)，也就是返回表中某一行的某一列的值，例如“10”或者“东京都”这样的值。

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

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

无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是ORDER BY 子句，几乎所有的地方都可以使用

SELECT product_id, product_name,
sale_price,
(SELECT AVG(sale_price)
FROM Product) AS avg_price
FROM Product;

执行结果
product_id | product_name | sale_price | avg_price
------------±----------------±----------±---------------------
0001 | T恤衫 | 1000 | 2097.5000000000000000
0002 | 打孔器 | 500 | 2097.5000000000000000
0003 | 运动T恤 | 4000 | 2097.5000000000000000
0004 | 菜刀 | 3000 | 2097.5000000000000000
0005 | 高压锅 | 6800 | 2097.5000000000000000
0006 | 叉子 | 500 | 2097.5000000000000000
0007 | 擦菜板 | 880 | 2097.5000000000000000
0008 | 圆珠笔 | 100 | 2097.5000000000000000

关联子查询

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

分组计算平均数

select product_id,product_name,product_type,sale_price,(
select avg(sale_price)
from Product as p2
where p1.product_type = p2.product_type
group by p1.product_type
) as avg_sale_price
from Product as p1;

±-----------±-------------±-------------±-----------±---------------+
| product_id | product_name | product_type | sale_price | avg_sale_price |
±-----------±-------------±-------------±-----------±---------------+
| 001 | TT血衫 | 衣服 | 1000 | 2500.0000 |
| 002 | 打孔器 | 办公用品 | 500 | 300.0000 |
| 003 | 运动T血 | 衣服 | 4000 | 2500.0000 |
| 004 | 菜刀 | 厨房用具 | 3000 | 2795.0000 |
| 005 | 高压锅 | 厨房用具 | 6800 | 2795.0000 |
| 006 | 叉子 | 厨房用具 | 500 | 2795.0000 |
| 007 | 擦菜板 | 厨房用具 | 880 | 2795.0000 |
| 008 | 圆珠笔 | 办公用品 | 100 | 300.0000 |
±-----------±-------------±-------------±-----------±---------------+

函数

数值

ABS(数值)

MOD(被除数，除数) a%b

ROUND(对象数值，保留小数的位数)

字符串

**拼接：**字符串1||字符串2

concat(s1,s2)

SELECT str1, str2, str3,
CONCAT(str1, str2, str3) AS str_concat
FROM SampleStr;

**长度：**LENGTH(字符串)

LOWER(字符串)

UPPER(字符串)

REPLACE(对象字符串，替换前的字符串，替换后的字符串)

截取：

SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

SUBSTR（str1, 3, 2）

日期函数

当前日期： SELECT CURRENT_DATE;

当前时间： SELECT CURRENT_TIME;

日期和时间： SELECT CURRENT_TIMESTAMP;

日期截取：

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;

执行结果：

now | year | month | day | hour | minute | second
---------------------------------±-----±------±----±----±-------±------
2010-04-25 19:07:33.987+09 | 2010 | 4 | 25 | 19 | 7 | 33.987

类型转化cast

CAST（转换前的值 AS 想要转换的数据类型）

SELECT CAST(‘0001’ AS INTEGER) AS int_col;

将字符串类型转换为日期类型

SELECT CAST(‘2009-12-14’ AS DATE) AS date_col;

将null转为其他值

COALESCE(数据1，数据2，数据3……)

该函数会返回可变参数 A 中左侧开始第1个不是 NULL 的值。参数个数是可变的，因此可以根据需要无限增加。

模糊查询

LIKE

SELECT * FROM SampleLike
WHERE strcol LIKE ‘%ddd%’;

BETWEEN AND

SELECT product_name, sale_price
FROM Product
WHERE sale_price BETWEEN 100 AND 1000;

包含100和1000

IN 和 NOT IN (注意：在使用IN 和 NOT IN 时是无法选取出 NULL 数据的)

SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IN (320, 500, 5000);

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

SELECT product_name, sale_price
FROM Product
WHERE product_id IN (SELECT product_id
FROM ShopProduct
WHERE shop_id = ‘000C’);

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

CASE表达式

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

SELECT product_name,
CASE WHEN product_type = ‘衣服’
THEN ‘A ：’ | | product_type
WHEN product_type = ‘办公用品’
THEN ‘B ：’ | | product_type
WHEN product_type = ‘厨房用具’
THEN ‘C ：’ | | product_type
ELSE NULL
END AS abc_product_type
FROM Product;

使用CASE表达式进行行列转换（返回0也是true，不返回用null）

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;

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

union

通过 UNION 进行并集运算时可以使用任何形式的 SELECT 语句，
之前学过的 WHERE、GROUP BY、HAVING 等子句都可以使用。但是
ORDER BY 只能在最后使用一次

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

在 UNION 的结果中保留重复行的语法。其实非常
简单，只需要在 UNION 后面添加 ALL 关键字就可以了

SELECT product_id, product_name
FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;

选取表中公共部分——INTERSECT

SELECT product_id, product_name
FROM Product
INTERSECT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;

希望保留重复行时同样需要使用INTERSECT ALL

记录的减法——EXCEPT

SELECT product_id, product_name
FROM Product
EXCEPT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;

product-product2 与 product2-product不同

JOIN

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;

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;

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 笛卡尔积

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

过时的内连结

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

关系除法

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NOFz4O6t-1647847391959)(0b22e8c0f501dae9fa7fcd844d405b6a.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-30dT3V9b-1647847391961)(9a1e34b1083511ab5592120b230576c3.png)]

*CREATE TABLE Skills
(**skill **VARCHAR(32),
PRIMARY KEY(skill)); CREATE TABLE EmpSkills
(**emp *VARCHAR(32),
skill VARCHAR(32),
PRIMARY KEY(emp, skill));

BEGIN TRANSACTION;
INSERT INTO Skills VALUES(‘Oracle’);
INSERT INTO Skills VALUES(‘UNIX’);
INSERT INTO Skills VALUES(‘Java’);
INSERT INTO EmpSkills VALUES(‘相田’, ‘Oracle’);
INSERT INTO EmpSkills VALUES(‘相田’, ‘UNIX’);
INSERT INTO EmpSkills VALUES(‘相田’, ‘Java’);
INSERT INTO EmpSkills VALUES(‘相田’, ‘C#’);
INSERT INTO EmpSkills VALUES(‘神崎’, ‘Oracle’);
INSERT INTO EmpSkills VALUES(‘神崎’, ‘UNIX’);
INSERT INTO EmpSkills VALUES(‘神崎’, ‘Java’);
INSERT INTO EmpSkills VALUES(‘平井’, ‘UNIX’);
INSERT INTO EmpSkills VALUES(‘平井’, ‘Oracle’);
INSERT INTO EmpSkills VALUES(‘平井’, ‘PHP’);
INSERT INTO EmpSkills VALUES(‘平井’, ‘Perl’);
INSERT INTO EmpSkills VALUES(‘平井’, ‘C++’);
INSERT INTO EmpSkills VALUES(‘若田部’, ‘Perl’);
INSERT INTO EmpSkills VALUES(‘渡来’, ‘Oracle’);
COMMIT;

SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE NOT EXISTS
(SELECT skill
FROM Skills
EXCEPT
SELECT skill
FROM EmpSkills ES2
WHERE EP1.emp = ES2.emp);

结果:

emp ------- *神崎
*相田

除法和乘法是相辅相成的关系，除法运算的结果（商）乘以除数就能得到除
法运算前的被除数了。例如对于 20÷4 = 5 来说，就是 5( 商 )×4( 除数 ) = 20( 被
除数）。
关系除法中这样的规则也是成立的。通过商和除数相乘，也就是交叉联结，
就能够得到作为被除数的集合了

窗口函数(OLAP函数)

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

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

窗口函数和group by不同，group by 是分组后每组只返回一行，而聚合开窗函数使用OVER子句是不会改变原来的行数的（即在返回基本列的同时，在同一行对它们进行聚合）所以需要去重加distinct

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 (PARTITION BY product_type
ORDER BY sale_price) AS ranking
FROM Product;

PARTITION BY 能够设定排序的对象范围。本例中，为了按照商品
种类进行排序，我们指定了 product_type。

通过 PARTITION BY 分组后的记录集合称为窗口。此处的窗口并
非“窗户”的意思，而是代表范围。这也是“窗口函数”名称的由来。

ORDER BY 能够指定按照哪一列、何种顺序进行排序。

窗口函数的适用范围

原则上窗口函数只能在SELECT子句中使用，这类函数不能在WHERE 子句或者 GROUP BY 子句中使用。

在 DBMS 内部，窗口函数是对 WHERE 子句或者 GROUP
BY 子句处理后的“结果”进行的操作

将SUM函数作为窗口函数使用 (累计的统计方法)

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

product_id | product_name | sale_price | current_sum
------------±-------------±-----------±-----------
0001 | T恤衫 | 1000 | 1000 ←1000
0002 | 打孔器 | 500 | 1500 ←1000+500
0003 | 运动T恤 | 4000 | 5500 ←1000+500+4000
0004 | 菜刀 | 3000 | 8500 ←1000+500+4000+3000
0005 | 高压锅 | 6800 | 15300
0006 | 叉子 | 500 | 15800
0007 | 擦菜板 | 880 | 16680
0008 | 圆珠笔 | 100 | 16780

计算移动平均

SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS 2 PRECEDING) AS moving_avg
FROM Product;

这里我们使用了 ROWS（“行”）和 PRECEDING（“之前”）两个关键
字，将框架指定为“截止到之前 ~ 行”，因此“ROWS 2 PRECEDING” 就是将框架指定为“截止到之前 2 行”，也就是将作为汇总对象的记录限
定为如下的“最靠近的 3 行”。

● 自身（当前记录）
● 之前 1行的记录
● 之前 2行的记录

结果

product_id product_name sale_price moving_avg

0001     T恤衫         1000        1000 ←(1000)/1
0002     打孔器        500       750 ←(1000+500)/2
0003     运动T恤       4000         1833 ←(1000+500+4000)/3
0004     菜刀         3000         2500 ←(500+4000+3000)/3
0005     高压锅        6800      4600 ←(4000+3000+6800)/3
0006     叉子         500          3433
0007     擦菜板        880       2726
0008     圆珠笔        100       493

我们通过指定框架，将“1 PRECEDING”（之前 1 行）和“1 FOLLOWING”（之后 1 行）的区间作为汇总对象

SELECT product_id, product_name, sale_price,
AVG (sale_price) OVER (ORDER BY product_id
ROWS BETWEEN 1 PRECEDING AND
1 FOLLOWING) AS moving_avg
FROM Product;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6lmPYesP-1647847391962)(343d150dd51d34aeeb68704fa0eb8c84.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h25Mzjpx-1647847391962)(b8055e3537edfd64b44b4edce82c408c.png)]

GRROUPING运算符

ROLLUP

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

一次计算出了如下两种组合的汇总结果

① GROUP BY ()
② GROUP BY (product_type)

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

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

① GROUP BY ()
② GROUP BY (product_type)
③ GROUP BY (product_type, regist_date)

GROUPING

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

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

还可以把1转换为合计行

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 ROLLUP(product_type, regist_date);

CUBE

① GROUP BY ()
② GROUP BY (product_type)
③ GROUP BY (regist_date) ←添加的组合
④ GROUP BY (product_type, regist_date)

GROUPING SETS

运算符可以用于从 ROLLUP 或者 CUBE 的结果中取出部分记录。

 product_type   regist_date sum_price
-------------- ------------ ----------

商品种类 合计   2008-04-28    880
商品种类 合计   2009-01-15    6800
商品种类 合计   2009-09-11    500
商品种类 合计   2009-09-20    4500
商品种类 合计   2009-11-11    100
商品种类 合计                 4000
厨房用具        登记日期 合计 11180
办公用品        登记日期 合计 600
衣服            登记日期 合计 5000

上述结果中也没有全体的合计行（16780 日元）。