SQL学习记录

`SQL` 学习记录

sql 分类

DDL：数据定义语言 (库)
DML: 数据操纵语言 (表)
DCL: 数据控制语言 (数据)

常见语法及书写方法

关键字大写，前置逗号
单行注释 -- ，多行注释 /* */
select DISTINCT column1 from table

聚合函数：聚合就是把多行汇总成一行

函数名称	作用
COUNT	计算表中的行数
SUM	计算数值列中数据的合计值
AVG	计算数值列中数据的平均值
MAX	计算任意列中的最大值
MIN	计算任意列中的最小值

如果用了GROUP BY却没有用聚合函数，是为了去重。GROUP BY一般习惯和聚合函数配合使用
SQL中的执行顺序FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY
WHERE子句——指定行条件，WHERE中不可以使用聚合函数

HAVING子句——指定组条件， HAVING中可以使用聚合函数
能写在WHERE中的不要写到HAVING中，因为HAVING的逻辑是排序之后才对数据进行分组，但是WHERE是排序之前就进行了过滤。比如要处理SCORE=90的数据，WHERE是只处理SCORE=90的数据，但是HAVING是把所有数据都查出来进行处理
ORDER BY ASC升序 DESC 降序， ORDER BY 中也可以使用聚合函数
数据插入语法：INSERT INTO 表名 (字段名) VALUES (值)
INSERT INTO 表名 (字段名) SELECT 字段名 FROM 表名 这个是从其他表中复制数据的语法
删除语法： DELETE FROM 表名 WHERE 条件 删除表中所有数据：TRUNCATE 表名
更新语法： UPDATE 表名 SET 字段 WHERE 条件

事务

事务就是需要在同一个处理单元中执行的一系列更新的集合。
ACID：数据库事务正确执行的四个基本特性，原子性、一致性、隔离性、持久性
BEGIN TRANSACTION 开启事务
COMMIT：提交处理，一旦提交就无法恢复到事务开始前的状态。
ROLLBACK：取消处理

视图

使用视图时并不会把数据保存到存储设备里面
创建视图语法： CREATE VIEW 视图名 AS SELECT 语法

-- 使用视图
SELECT 字段名 FROM 视图名

-- 等同于
select 字段名 FROM (SELECT 字段名 FROM 表名) AS 视图名

关联子查询和非关联子查询

-- 错误写法：因为是 WHERE 比较的值不是标量
SELECT product_id, product_name, sale_price
FROM Product
WHERE sale_price > (
    SELECT AVG(sale_price)
    FROM Product
    GROUP BY product_type
);

-- 正确写法
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_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表达式

比较null的时候用is null 和is not null，不使用<>
COALESC函数，从左到右一次检查每个参数，返回第一个非NULL的值
使用in和exists的情况分析
```
-- IN
SELECT *
FROM Class_A
WHERE id IN 
(
    SELECT id
    FROM Class_B
);

-- EXISTS
SELECT *
FROM Class_A A
WHERE EXISTS
(
    SELECT *
    FROM Class_B B
    WHERE A.id = B.id
);
```
如果把上例的 Class_A 看成外表，Class_B 看成内表的话。

答：

维度一：从外表和内表的数据行大小的关系来看

1、IN 只执行一次，此内表查出后就缓存了，所以 IN 适合外表 > 内表的情况；

2、EXISTS 是针对外表去作循环，每次循环会跟内表作关联子查询，所以 EXISTS 适合外表 < 内表的情况；

3、当内外表数据差不多大时，IN 与 EXISTS 也差不多。

维度二：索引的角度

EXISTS 可以用到索引，而 IN 不行。所以 EXISTS 更佳。

维度三：是否全表遍历

针对内表，EXISTS 只要查到一行数据满足条件就会终止遍历，而 IN 必须遍历整个内表。所以 EXISTS 更佳。

维度四：可读性

IN 更佳。

综上所述：还是考虑实际情况。但是 EXISTS 替代 IN 提高性能的可能性更大。

1、要想改善 IN 的性能，除了使用 EXISTS，还可以使用连接。

2、最近有很多数据库也尝试着改善了 IN 的性能。例如，在 Oracle 数据库中，如果我们使用了建有索引的列，那么即使使用 IN 也会先扫描索引；PostgreSQL 从版本 7.4 起也改善了使用子查询作为 IN 谓词参数时的查询速度。

CASE表达式分为简单CASE表达式和搜索CASE表达式两种

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;

Exists函数

-- IN 写法
SELECT product_name, sale_price
FROM Product WHERE product_id IN (    
    SELECT product_id    
    FROM ShopProduct      
    WHERE shop_id = '000C'
);

-- EXISTS 写法 [推荐]
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
);

-- NOT EXISTS 写法 —— “东京店（shop_id:000A）在售之外的商品的销售单价”
SELECT product_name, sale_price
FROM Product AS P  
WHERE NOT EXISTS (    
    SELECT *    
    FROM ShopProduct AS SP     
    WHERE SP.shop_id = '000A'    
    AND SP.product_id = P.product_id
);

案例1、用一条SQL语句进行不同条件的统计

// 老写法
-- 男性人口
SELECT pref_name,
SUM(population)
from PopTb12
WHERE sex='1'
GROUP BY pref_name;
-- 女性人口
SELECT pref_name,
COUNT(population)
from PopTb12
WHERE sex='2'
group by pref_name

// 新写法
SELECT pref_name,
-- 男性人口
SUM(CASE WHEN sex='1' THEN population ELSE 0 END) cnt_m,
-- 女性人口
SUM(CASE WHEN sex='2' THEN population ELSE 0 END) cnt_f
from PopTb12
group by pref_name;

CHECK函数，用于检验数据是否符合要求。通常建表的时候直接通过CONSTRAINT 约束名 CHECK(表达式)建立，如后期执行的数据不符合CHECK的格式，则无法执行
集合运算符：UNION、EXCEPT、INTERSECT在使用时需注意，1.作为运算对象的记录的列数必须相同2.作为运算对象的记录中列的类型必须一致3.ORDER By子句只能在最后使用一次

posted @ 2025-05-12 17:31 少想多做养身体阅读(25) 评论(0) 收藏举报

刷新页面返回顶部

好学你就多学点

SQL学习记录

`SQL` 学习记录

sql 分类

常见语法及书写方法

事务

视图

函数、CASE表达式

公告

好学你就多学点

SQL学习记录

SQL 学习记录

sql 分类

常见语法及书写方法

事务

视图

函数、CASE表达式

公告

`SQL` 学习记录