《SQL进阶教程》读书小记

最新推荐文章于 2024-07-31 15:36:36 发布

幺零小柒

最新推荐文章于 2024-07-31 15:36:36 发布

阅读量4k

点赞数 1

分类专栏：数据库文章标签： sql sql进阶教程

本文链接：https://blog.csdn.net/a_842297171/article/details/80317843

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

CASE表达式的两种写法：

①简单CASE表达式：

CASE sex 
    WHEN '1' THEN '男'
    WHEN '2' THEN '女'
ELSE '其他' END

②搜索CASE表达式：

CASE 
    WHEN sex = '1' THEN '男'
    WHEN sex = '2' THEN '女'
ELSE '其他' END

CASE语句不写ELSE子句时，执行结果是NULL。
CASE将已有编号方式转换为新的方式并统计：

     SELECT (CASE语句),SUM(需要统计的列)
      FROM 表
     GROUP BY (CASE语句)

比如：

SELECT CASE pref_name
        WHEN '德岛' THEN '四国'
        WHEN '香川' THEN '四国'
        WHEN '爱媛' THEN '四国'
        WHEN '高知' THEN '四国'
        WHEN '福冈' THEN '九州'
        WHEN '佐贺' THEN '九州'
        WHEN '长崎' THEN '九州'
        ELSE '其他'
    END AS district, SUM(population)
FROM PopTb1
GROUP BY CASE pref_name
        WHEN '德岛' THEN '四国'
        WHEN '香川' THEN '四国'
        WHEN '爱媛' THEN '四国'
        WHEN '高知' THEN '四国'
        WHEN '福冈' THEN '九州'
        WHEN '佐贺' THEN '九州'
        WHEN '长崎' THEN '九州'
        ELSE '其他'
    END

新手用WHERE子句进行条件分支，高手用SELECT子句进行条件分支。

CASE和CHECK搭配使用，使用蕴含式：P→Q。即：如果P为真，Q一定要为真；P不为真，无所谓。
UPDATE使用CASE：

UPDATE products
SET prod_price = CASE 
    WHEN prod_price < 4 THEN prod_price + 0.51
    WHEN prod_price > 11 THEN prod_price - 0.99
    ELSE prod_price
END

CASE语句还能判断表达式：

SELECT course_name
    , CASE 
        WHEN course_id IN (
            SELECT course_id
            FROM opencourses
            WHERE month = 200706
        ) THEN 'O'
        ELSE 'X'
    END AS '6月'
    , CASE 
        WHEN course_id IN (
            SELECT course_id
            FROM opencourses
            WHERE month = 200707
        ) THEN 'O'
        ELSE 'X'
    END AS '7月'
    , CASE 
        WHEN course_id IN (
            SELECT course_id
            FROM opencourses
            WHERE month = 200708
        ) THEN 'O'
        ELSE 'X'
    END AS '8月'
FROM coursemaster;

SELECT course_name
    , CASE 
        WHEN EXISTS (
            SELECT course_id
            FROM opencourses OC
            WHERE month = 200706
                AND OC.course_id= CM.course_id
        ) THEN 'O'
        ELSE 'X'
    END AS '6月'
    , CASE 
        WHEN EXISTS (
            SELECT course_id
            FROM opencourses OC
            WHERE month = 200707
                AND OC.course_id= CM.course_id
        ) THEN 'O'
        ELSE 'X'
    END AS '7月'
    , CASE 
        WHEN EXISTS (
            SELECT course_id
            FROM opencourses OC
            WHERE month = 200708
                AND OC.course_id= CM.course_id
        ) THEN 'O'
        ELSE 'X'
    END AS '8月'
FROM coursemaster CM

在CASE中使用聚合函数：

1.获取只加入了一个社团的学生的社团ID。
2.获取加入了多个社团的学生的主社团ID。

SELECT std_id
    , CASE 
        WHEN COUNT(club_id) = 1 THEN club_id
        ELSE MAX(CASE 
            WHEN main_club_flg = 'Y' THEN club_id
            ELSE NULL
        END)
    END AS main_club
FROM studentclub
GROUP BY std_id

新手用HAVING子句进行条件分支，高手用SELECT子句进行条件分支。

CASE语句可以写在SELECT子句、GROUP BY子句、WHERE子句、ORDER BY子句中。
SQL -> 面向集合语言
在SQL中，只要被赋予了不同的名称，即便是相同的表也应该当作不同的表（集合）来对待。集合是SQL唯一能处理的数据结构。
在需要获取列的组合时，经常需要用到“非等值自连接”。

获取product表的名字的组合，组合不是排列，组合{1,2}和{2,1}是同一个元素，而排列有顺序。

SELECT p1.name AS name1, p2.name AS name2
FROM products p1, products p2
WHERE p1.name > p2.name;

对NULL使用比较谓词后得到的结果总是unknow，比如1 = NULL、2 > NULL 、NULL = NULL。
真值优先级：
AND的情况：false > unknow > true
OR的情况：true > unknow > false
SQL的查询结果里只有判断结果为true的行，false和unknow的行不会出现在结果里。
在SQL中，排中律并不成立，因为有NULL值，比如年龄=20 OR 年龄 <> 20现实生活中是成立的，但在数据库中，年龄可能是NULL，这样就是 unknow OR unknow，结果还是unknow，所以排中律在数据库中并不成立。
CASE子句判断条件中如果有NULL，不要使用

CASE 列 WHEN NULL...

这个永远是unknow，它等于：

CASE WHEN 列 = NULL...

应该使用：

CASE WHEN 列 IS NULL...

的格式。

切记：NULL不是值。
unknow AND 条件 => 结果不会为true
unknow OR 条件 => 结果不会为false
IN和EXISTS可以互相替换使用，NOT IN和NOT EXISTS不可以互相替换。IN相当于多个OR，只要有一个true就为true；NOT IN相当于多个NOT AND（NOT IN（x or y or z）= NOT x AND NOT y AND NOT z），只要x，y，z出现一个NULL就永远不为true。EXISTS和NOT EXISTS子句只会返回true或false，不会返回unknow。
在以前的SQL标准中，HAVING子句必须和GROUP BY子句一起使用；但是按照现在的SQL标准来说，HAVING自己是可以单独使用的，不过这种情况下，就不能再SELECT子句中引用原来的列了，要么使用常量，要么使用聚合函数。单独使用HAVING子句的情况可以看成是对空字段进行了GROUP BY，此时整张表会被聚合为一行。
COUNT(*)可以用于NULL，COUNT(列名)要先排除NULL的行再进行统计。
SQL通过不断生成子集来求得目标集合。
MYSQL不支持FULL JOIN。可以将left join的结果和right join的结果union起来的方式来实现full join。

使用sql进行集合运算

①交集
内连接

SELECT * FROM table_a
INNER JOIN table_b ON table_a.id = table_b.id;

这里写图片描述

②并集
全外连接

SELECT * FROM table_a
FULL OUTER JOIN table_b ON table_a.id = table_b.id;

这里写图片描述
有的DBMS不支持全外连接，比如MYSQL，可以使用union all来实现全外连接：

SELECT * FROM table_a
INNER JOIN table_b ON table_a.id = table_b.id
UNION ALL
SELECT * FROM table_a
LEFT OUTER JOIN table_b ON table_a.id = table_b.id
WHERE table_b.id IS NULL
UNION ALL
SELECT * FROM table_a
RIGHT OUTER JOIN table_b ON table_a.id = table_b.id
WHERE table_a.id IS NULL

③差集
左外连接、右外连接

SELECT * FROM table_a
RIGHT OUTER JOIN table_b ON table_a.id = table_b.id
WHERE table_a.id IS NULL;

这里写图片描述

④异或集
全外连接-内连接

SELECT * FROM table_a
FULL OUTER JOIN table_b ON table_a.id = table_b.id
where table_a.id IS NULL OR table_b.id IS NULL;

这里写图片描述

SIGN(x)函数:如果x > 0，返回1；如果x = 0，返回0；如果x < 0，返回-1。
时间重叠函数overlaps，mysql还不支持，oracle支持。
加上可选项ALL之后，就不会发生排序，所以性能会有所提升。比如UNIOIN ALL，如果不关心数据是否重复，或者确定不会有重复的数据，可以加上ALL。
如果两个集合是相同的，则它们的并集也是相同的。即：A UNIOIN B = A = B。而且是幂等性的，即S UNION S UNION S UNION S...UNION S = S，这里的集合指的是没有重复行的集合，也是数学里的集合，所以主键是多么重要。
支撑SQL和关系数据库的基础理论主要有两个：一个是数学领域的集合论，另一个是作为现代逻辑学标准体系的谓词逻辑。
例如“=、<、>”等比较谓词，以及BETWEEN、LIKE、IN、IS等都属于谓词。实际上，谓词是一种特殊的函数，返回值是真值。谓词逻辑提供谓词是为了判断命题（可以理解成陈述句）的真假。平时使用的WHERE子句，其实也可以看成是由多个谓词组合而成的新谓词，只有能让WHERE子句的返回值为真的命题，才能从表中查询到。
EXISTS的参数是数据的集合，所以是二阶谓词；其他谓词比如=、>的参数都是一行数据，所以使一阶谓词。
EXISTS和NOT EXISTS实际上是把表中的数据一行行用参数的集合来校验。
在SQL中遇到需要全称量化的问题时，一般的思路都是把“所有行都满足条件P”转换成它的双重否定——不存在不满足条件P的行。
四种进行差集运算的方法：
①EXCEPT
②不支持EXCEPT的数据库也能使用，而且易于理解的方法：NOT IN
③NOT IN的相似方法：NOT EXISTS
④麻烦的方法：外连接，比如 A left join B where A.x = B.x，差集就是右边为NULL的列。
性能最好的是NOT EXISTS。
某个集合中，如果元素最大值和最小值相等，那么这个集合中肯定只有一种值。
在数学中，通过GROUP BY生成的子集有一个对应的名字，叫作划分。
数据库中，0/0为NULL。
如果通过CASE表达式生成特征函数，那么无论多么复杂的条件都可以描述。
使用多个字段查找集合中的重复元素时，不应该对各个字段分别进行条件匹配，而应该将他们“整个地作为一个字段”进行条件匹配。
参数是子查询时，使用EXISTS代替IN。一方面子查询可以走索引（如果列上有索引），另一方面只要查询到一行满足条件就会终止查询。使用IN的时候，数据库首先会进行子查询，然后将结果存储在一张临时的工作表中（脑内联视图），然后扫描整个视图。使用EXISTS，数据库不会生成临时的工作表。
参数是子查询时，使用连接代替IN。
避免排序。
像UNION、INTERSECT、EXCEPT这类的运算符，数据库内部都会进行排序，加上ALL可以避免排序（不在乎有重复数据的时候）。
DISTINCT也会进行排序。可以使用EXISTS代替DISTINCT，因为EXISTS有遇到符合的就停止查询的特性。
使用极值函数（比如MAX和MIN），如果参数字段上建有索引，则只需要扫描索引，不需要扫描整张表。
能写在WHERE字句里的条件不要写在HAVING字句里。
HAVING子句是针对聚合后生成的视图进行筛选的。
通过指定带索引的列作为GROUP BY和ORDER BY的列，可以实现高速查询。
把运算的表达式放在查询条件的右侧，就能用到索引了。
使用索引时，条件表达式的左侧应该是原始字段。
使用联合索引时，指定条件的顺序很重要。
使用LIKE谓词进行后方一致或中间一致的匹配。
默认的类型转换不仅会增加额外的性能开销，还会导致索引不可用。
不管是减少排序还是使用索引，抑或是避免中间表的使用，都是为了减少对硬盘的访问。
如果没有为索引和约束显式地指定名称，DBMS就会自动为之分配随机的名称。
SQL中各部分的执行顺序是：FROM -> WHERE -> GROUP BY -> HAVING -> SELECT（-> ORDER BY）。