SQL 进阶教程(1)

系统管理员 2021-05-10 11:33 527阅读 0赞

### 神奇的 SQL ###

#### 1.1 CASE 表达式 ####

CASE	表达式是 SQL 里非常重要而且使用起来非常便利的技术,我们应该学会用它来描述条件分支.

CASE表达式概述

-- 简单 case 表达式
    case sex 
    	when '1' then '男'
    	when '2' then '女'
    else '其他' end
    
    -- 搜索 case 表达式
    case  
    	when sex = '1' then '男'
    	when sex = '2' then '女'
    else '其他' end

剩余的 when 子句被忽略的写法示例

-- 例如,这样的话结果里不会出现"第二"
    case 
    	when clo_1 in ("a","b") then "第一"
    	when clo_1 in ("b")     then "第二"
    else "其他" end

注意事项

1. 统一各分支返回的数据类型: 某个分支返回字符串类型,其他分支返回数值类型的写法是不正确的
    2. 不要忘记写 end: 不写会报错
    3. 养成写 else 子句的习惯: 减少麻烦

将已有编号方式转换为新的方式并统计

-- 创建测试数据
    CREATE TABLE "public"."PopTbl" (
      "pref_name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
      "population" int4 NOT NULL,
      "id" int4 NOT NULL,
      CONSTRAINT "PopTbl_pkey" PRIMARY KEY ("id")
    );
    
    -- 插入数据
    INSERT INTO "public"."PopTbl"("pref_name", "population", "id") VALUES ('无锡', 100, 1);
    INSERT INTO "public"."PopTbl"("pref_name", "population", "id") VALUES ('常州', 200, 2);
    INSERT INTO "public"."PopTbl"("pref_name", "population", "id") VALUES ('苏州', 150, 3);
    INSERT INTO "public"."PopTbl"("pref_name", "population", "id") VALUES ('南京', 200, 4);
    INSERT INTO "public"."PopTbl"("pref_name", "population", "id") VALUES ('广州', 300, 5);
    INSERT INTO "public"."PopTbl"("pref_name", "population", "id") VALUES ('深圳', 100, 6);
    INSERT INTO "public"."PopTbl"("pref_name", "population", "id") VALUES ('佛山', 200, 7);
    INSERT INTO "public"."PopTbl"("pref_name", "population", "id") VALUES ('北京', 400, 8);
    INSERT INTO "public"."PopTbl"("pref_name", "population", "id") VALUES ('上海', 50, 9);

#### 要求: 找出江苏和广东及其他省的总人口 ####

结果如下:

SELECT CASE pref_name
                 WHEN '常州' THEN '江苏'
                 WHEN '无锡' THEN '江苏'
                 WHEN '南京' THEN '江苏'
                 WHEN '苏州' THEN '江苏'
                 WHEN '广州' THEN '广东'
                 WHEN '深圳' THEN '广东'
                 WHEN '佛山' THEN '广东'
                 ELSE '其他' END AS "地区",
           SUM(population) as "人口数"
    FROM "PopTbl" -- PostgerSql 本身对大小写不敏感,如果你要新建一个大写字母的表，必须使用引号，同样如果你要查询也必须加引号。
    GROUP BY CASE pref_name
                 WHEN '常州' THEN '江苏'
                 WHEN '无锡' THEN '江苏'
                 WHEN '南京' THEN '江苏'
                 WHEN '苏州' THEN '江苏'
                 WHEN '广州' THEN '广东'
                 WHEN '深圳' THEN '广东'
                 WHEN '佛山' THEN '广东'
                 ELSE '其他' END;

执行结果  
![执行结果][20190624233507968.png]

注意: 这里的关键在于将 SELECT 子句里的 CASE 表达式复制到 GROUP BY 子句里,
    	 需注意的是,如果对转换前的列"pref_name"进行 GROUP_BY,就得不到正确的结果
    	 (不会引起语法错误,所以容易被忽视)

#### 要求: 将数值按照适当的级别进行分类统计 ####

-- 按人口数量等级(pop_class)查询省市区个数
    SELECT CASE WHEN population < 100 THEN '01'
                WHEN population >= 100 AND population < 200 THEN '02'
                WHEN population >= 200 AND population < 300 THEN '03'
                WHEN population >= 300 THEN '04'
                ELSE NULL END AS pop_class,
           COUNT(*) AS cnt
     FROM PopTbl
     GROUP BY CASE WHEN population < 100 THEN '01'
                   WHEN population >= 100 AND population < 200 THEN '02'
                   WHEN population >= 200 AND population < 300 THEN '03'
                   WHEN population >= 300 THEN '04'
                   ELSE NULL END;

执行结果  
![在这里插入图片描述][20190703002512430.png]  
简化上面两个例子的写法

SELECT CASE pref_name
                 WHEN '常州' THEN '江苏'
                 WHEN '无锡' THEN '江苏'
                 WHEN '南京' THEN '江苏'
                 WHEN '苏州' THEN '江苏'
                 WHEN '广州' THEN '广东'
                 WHEN '深圳' THEN '广东'
                 WHEN '佛山' THEN '广东'
                 ELSE '其他' END AS district,
           SUM(population) as "人口数"
    FROM "PopTbl" -- PostgerSql 本身对大小写不敏感,如果你要新建一个大写字母的表，必须使用引号，同样如果你要查询也必须加引号。
    GROUP BY district;-- 引用了 select 子句中定义的别名
    
    -- 其实这种写法违反了 SQL 标准,因为 group by 子句比 select 语句限制行,
    -- 所以这种写法是不被允许的,事实上在 oracle,db2,SQL server 数据库中会出错,
    -- 但是 MySQL和 PostgerSql 可以执行,
    -- 因为这些数据库在执行查询语句时,会先对 select 子句里的列表进行扫描,并对列进行计算.

### 用一条 SQL 语句进行不同条件的统计 ###

#### 要求: 需要往存储各县人口数量的表 poptbl 里添加"性别列,然后求按性别,县名汇总的人数 ####

结果如下:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70]

-- 创建表和插入数据
    CREATE TABLE "PopTbl2" (pref_name VARCHAR(32), sex CHAR(1) NOT NULL, population INTEGER NOT NULL, PRIMARY KEY(pref_name, sex));
    
    INSERT INTO "PopTbl2" VALUES('常州', '1',	60 );
    INSERT INTO "PopTbl2" VALUES('常州', '2',	40 );
    INSERT INTO "PopTbl2" VALUES('无锡', '1',	100);
    INSERT INTO "PopTbl2" VALUES('无锡', '2',	100);
    INSERT INTO "PopTbl2" VALUES('南京', '1',	100);
    INSERT INTO "PopTbl2" VALUES('南京', '2',	50 );
    INSERT INTO "PopTbl2" VALUES('苏州', '1',	100);
    INSERT INTO "PopTbl2" VALUES('苏州', '2',	100);
    INSERT INTO "PopTbl2" VALUES('广州', '1',	100);
    INSERT INTO "PopTbl2" VALUES('广州', '2',	200);
    INSERT INTO "PopTbl2" VALUES('深圳', '1',	20 );
    INSERT INTO "PopTbl2" VALUES('深圳', '2',	80 );
    INSERT INTO "PopTbl2" VALUES('佛山', '1',	125);
    INSERT INTO "PopTbl2" VALUES('佛山', '2',	125);
    INSERT INTO "PopTbl2" VALUES('北京', '1',	250);
    INSERT INTO "PopTbl2" VALUES('北京', '2',	150);
    
    -- 执行查找
    SELECT pref_name,
           -- 男性人口
           SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m,
          -- 女性人口
           SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f
    FROM "PopTbl2"
    GROUP BY pref_name;

执行结果:  
![在这里插入图片描述][20190703004643833.png]  
用 CHECK 约束定义多个列的条件关系

-- 假设某公司规定"女性员工的工资必须在 2万元以下",而在这个公司的人事表中,这条无礼的规定是使用 CHECK 约束来描述的,代码如下所示:
    CONSTRAINT check_salary CHECK
    ( CASE WHEN sex = '2' THEN CASE WHEN salary <= 20000 THEN 1 ELSE 0 END
      ELSE 1 END = 1 
      )
     -- 蕴含式逻辑 记作 P → Q
     -- 区别与逻辑与 P且Q,P^Q
     -- 使用逻辑与改写 CHECK
     CONSTRAINT check_salary CHECK
    (sex = '2' and salary <= 20000)

![逻辑与和蕴含式][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 1]  
在 UPDATE 语句中进行条件分支

CREATE TABLE Salaries
    (name VARCHAR(32) PRIMARY KEY,
     salary INTEGER NOT NULL);
    
    INSERT INTO Salaries VALUES('江西', 300000);
    INSERT INTO Salaries VALUES('江苏', 270000);
    INSERT INTO Salaries VALUES('安徽', 220000);
    INSERT INTO Salaries VALUES('浙江', 290000);

工资表 salaries 信息  
![在这里插入图片描述][20190728183626491.png]

-- 当前工资为 30 万以上的员工,降薪 10%
    -- 当前工资为 25 万以上,且不满 28 万的员工,加薪 20%
    UPDATE Salaries
       SET salary = (CASE WHEN salary >= 300000 THEN salary * 0.9
                         WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2
                         ELSE salary END);

***注意:case 语句最后一行的 else salary 非常重要,因为如果没有,默认其余的员工工资都会变为 null,因为 case 默认如果没有指定 else,那么就是 else null.***

有下面的数据表

CREATE TABLE SomeTable
    (p_key CHAR(1) PRIMARY KEY,
     col_1 INTEGER NOT NULL, 
     col_2 CHAR(2) NOT NULL);
    
    INSERT INTO SomeTable VALUES('a', 1, '一');
    INSERT INTO SomeTable VALUES('b', 2, '二');
    INSERT INTO SomeTable VALUES('c', 3, '三');

要求更换主键 a b 的值

-- 不使用 case,需要三条 sql 语句,第一步将 a 改变为中间暂时变量 t;第二步将 b 改变为 a;第三步将 t 改变为 b;
    -- 使用 case 一步解决
    UPDATE SomeTable
       SET p_key = (CASE WHEN p_key = 'a'
                        THEN 'b'
                        WHEN p_key = 'b'
                        THEN 'a'
                        ELSE p_key END)
     WHERE p_key IN ('a', 'b');
    -- 在 PostgerSql 和 MySQL 中执行会报错,其他 DB 中执行是没问题的

表之间的数据匹配  
一张培训学校的课程一览表和一张管理每个月所设课程的表

-- 创建模拟数据
    -- 课程表
    CREATE TABLE CourseMaster
    (course_id   INTEGER PRIMARY KEY,
     course_name VARCHAR(32) NOT NULL);
    
    INSERT INTO CourseMaster VALUES(1, '会计入门');
    INSERT INTO CourseMaster VALUES(2, '财务知识');
    INSERT INTO CourseMaster VALUES(3, '簿记考试');
    INSERT INTO CourseMaster VALUES(4, '税务师');
    
    -- 课程安排表
    CREATE TABLE OpenCourses
    (month       INTEGER ,
     course_id   INTEGER ,
        PRIMARY KEY(month, course_id));
    
    INSERT INTO OpenCourses VALUES(200706, 1);
    INSERT INTO OpenCourses VALUES(200706, 3);
    INSERT INTO OpenCourses VALUES(200706, 4);
    INSERT INTO OpenCourses VALUES(200707, 4);
    INSERT INTO OpenCourses VALUES(200708, 2);
    INSERT INTO OpenCourses VALUES(200708, 4);

生成交叉表

<table> 
 <thead> 
  <tr> 
   <th>course_name</th> 
   <th>6 月</th> 
   <th>7 月</th> 
   <th>8 月</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>会计入门</td> 
   <td>o</td> 
   <td>x</td> 
   <td>x</td> 
  </tr> 
  <tr> 
   <td>财务知识</td> 
   <td>x</td> 
   <td>o</td> 
   <td>o</td> 
  </tr> 
  <tr> 
   <td>簿记考试</td> 
   <td>o</td> 
   <td>x</td> 
   <td>x</td> 
  </tr> 
  <tr> 
   <td>税务师</td> 
   <td>o</td> 
   <td>o</td> 
   <td>o</td> 
  </tr> 
 </tbody> 
</table>

-- 表的匹配：使用IN谓词
    SELECT CM.course_name,
           CASE WHEN CM.course_id IN 
                        (SELECT course_id FROM OpenCourses 
                          WHERE month = 200706) THEN '○'
                ELSE '×' END AS "6月",
           CASE WHEN CM.course_id IN 
                        (SELECT course_id FROM OpenCourses
                          WHERE month = 200707) THEN '○'
                ELSE '×' END AS "7月",
           CASE WHEN CM.course_id IN 
                        (SELECT course_id FROM OpenCourses
                          WHERE month = 200708) THEN '○'
                ELSE '×' END  AS "8月"
      FROM CourseMaster CM;
    
    -- 表的匹配：使用EXISTS谓词 
    SELECT CM.course_name,
           CASE WHEN EXISTS
                        (SELECT course_id FROM OpenCourses OC
                          WHERE month = 200706
                            AND CM.course_id = OC.course_id) THEN '○'
                ELSE '×' END AS "6月",
           CASE WHEN EXISTS
                        (SELECT course_id FROM OpenCourses OC
                          WHERE month = 200707
                            AND CM.course_id = OC.course_id) THEN '○'
                ELSE '×' END AS "7月",
           CASE WHEN EXISTS
                        (SELECT course_id FROM OpenCourses OC
                          WHERE month = 200708
                            AND CM.course_id = OC.course_id) THEN '○'
                ELSE '×' END  AS "8月"
      FROM CourseMaster CM;

性能分析

exists 性能更好,因为 exists 进行子查询能够用到 month,course_id 这样的主键索引,
    所以在表 opencourses 里数据比较多的时候更有优势

在 CASE 中使用聚合函数

-- 创建模拟数据
    -- 对于加入了多个社团的,我们将主社团标记为 Y,对于加入一个社团的,我们将其主社团标记为 N;
    CREATE TABLE StudentClub
    (std_id  INTEGER,
     club_id INTEGER,
     club_name VARCHAR(32),
     main_club_flg CHAR(1),
     PRIMARY KEY (std_id, club_id));
    
    INSERT INTO StudentClub VALUES(100, 1, '棒球',  'Y');
    INSERT INTO StudentClub VALUES(100, 2, '管弦乐','N');
    INSERT INTO StudentClub VALUES(200, 2, '管弦乐','N');
    INSERT INTO StudentClub VALUES(200, 3, '羽毛球','Y');
    INSERT INTO StudentClub VALUES(200, 4, '足球',  'N');
    INSERT INTO StudentClub VALUES(300, 4, '足球',  'N');
    INSERT INTO StudentClub VALUES(400, 5, '游泳',  'N');
    INSERT INTO StudentClub VALUES(500, 6, '围棋',  'N');
    
    -- 获取只加入一个社团的学生的社团 ID
    SELECT std_id,MAX(club_id) as main_club 
    FROM StudentClub
    GROUP BY std_id 
    HAVING COUNT(*) =1;
    
    -- 获取加入了多个社团的学生的主社团 ID
    SELECT std_id,club_id as main_club 
    FROM StudentClub
    where main_club_flag = 'Y';

使用 case

SELECT std_id,
           CASE 
           		WHEN COUNT(*) = 1 /* 只加入了一个社团的学生 */ THEN MAX(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;

CASE 表达式用在 SELECT 子句里时,既可以写在聚合函数内部,也可以写在聚合函数外部.这种高度自由的写法是 CASE 表达式的魅力所在.

##### 练习题 #####

###### 第一题 ######

-- 建表
    CREATE TABLE Greatests
    (key CHAR(1) PRIMARY KEY,
     x   INTEGER NOT NULL,
     y   INTEGER NOT NULL,
     z   INTEGER NOT NULL);
    
    INSERT INTO Greatests VALUES('A', 1, 2, 3);
    INSERT INTO Greatests VALUES('B', 5, 5, 2);
    INSERT INTO Greatests VALUES('C', 4, 7, 1);
    INSERT INTO Greatests VALUES('D', 3, 3, 8);

1. 从表里选出 x 和 y 二者中较大的值的情况

解答:

SELECT key,
           CASE WHEN x < y THEN y
                ELSE x END AS greatest
      FROM Greatests;

2. 从表中选出 x y z 三者中最大的值

解答:

-- 答案一
    SELECT key,
           CASE WHEN CASE WHEN x < y THEN y ELSE x END < z
                THEN z
                ELSE CASE WHEN x < y THEN y ELSE x END
            END AS greatest
      FROM Greatests;
    -- 答案二
    SELECT key, MAX(col) AS greatest
      FROM (SELECT key, x AS col FROM Greatests
            UNION ALL
            SELECT key, y AS col FROM Greatests
            UNION ALL
            SELECT key, z AS col FROM Greatests) TMP
     GROUP BY key;
    
    -- 答案三
    /* 仅适用于PostgerSql Oracle和MySQL */
    SELECT key, GREATEST(GREATEST(x,y), z) AS greatest
      FROM Greatests;

###### 第二题 ######

SELECT sex,
           SUM(population) AS total,
           SUM(CASE WHEN pref_name = '常州' THEN population ELSE 0 END) AS 常州,
           SUM(CASE WHEN pref_name = '无锡' THEN population ELSE 0 END) AS 无锡,
           SUM(CASE WHEN pref_name = '南京' THEN population ELSE 0 END) AS 南京,
           SUM(CASE WHEN pref_name = '苏州' THEN population ELSE 0 END) AS 苏州,
           SUM(CASE WHEN pref_name IN ('广州', '深圳', '佛山', '北京') THEN population ELSE 0 END) AS "牛逼"
      FROM "PopTbl2"
     GROUP BY sex;

###### 第三题 ######

将 Greatests 表中的数据按照 B-A-D-C的顺序排序

-- 答案一
    SELECT key
      FROM Greatests
     ORDER BY CASE key
                WHEN 'B' THEN 1
                WHEN 'A' THEN 2
                WHEN 'D' THEN 3
                WHEN 'C' THEN 4
                ELSE NULL END asc;
    
    -- 答案二
    SELECT key,
           CASE key
             WHEN 'B' THEN 1
             WHEN 'A' THEN 2
             WHEN 'D' THEN 3
             WHEN 'C' THEN 4
             ELSE NULL END AS sort_col
      FROM Greatests
     ORDER BY sort_col;

#### 1.2 自连接的用法 ####

CREATE TABLE Products
    (name VARCHAR(16) PRIMARY KEY,
     price INTEGER NOT NULL);

##### 1.2.2 可重排列、排列、组合 #####

假设有一张存放了商品名称及价格的表, 表里有"苹果,橘子.香蕉"3 条记录,在生成用于查询销售额的报表等的时候,我们有时会需要获取这些商品的组合.

<table> 
 <thead> 
  <tr> 
   <th>name(商品名称)</th> 
   <th>price(价格)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>苹果</td> 
   <td>50</td> 
  </tr> 
  <tr> 
   <td>橘子</td> 
   <td>100</td> 
  </tr> 
  <tr> 
   <td>香蕉</td> 
   <td>80</td> 
  </tr> 
 </tbody> 
</table>

--可重排列·排列·组合
    INSERT INTO Products VALUES('苹果',	50);
    INSERT INTO Products VALUES('橘子',	100);
    INSERT INTO Products VALUES('香蕉',	80);

组合:

1.  有顺序的有序对–排列;
2.  无顺序的无序对–组合;

-- 用于获取可重排列的 SQL 语句
    SELECT 
    	P1.name AS name_1, P2.name AS name_2
    FROM 
    	Products P1, Products P2;

![在这里插入图片描述][20191029232152548.png]

#### 1. 3 三值逻辑和 NULL ####

#### 1.4 HAVING 字句的力量 ####

#### 1. 5 外连接的用法 ####

#### 1. 6 用关联自查询比较行与行 ####

#### 1. 7 用 SQL 进行集合运算 ####

#### 1. 8 EXISTS 谓词的用法 ####

#### 1.9 用 SQL 处理数列 ####

#### 1.10 HAVING 子句又回来了 ####

#### 1.11 让 SQL 飞起来 ####

#### 1.12 SQL 编程方法 ####

[20190624233507968.png]: /images/20210508/a36eb6f6ecec4e258d837ab917d65a51.png
[20190703002512430.png]: /images/20210508/3eb282b49aa44144ab358d7fc68d465b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70]: /images/20210508/604616f6b10c4be5bd67fa26ca5d0162.png
[20190703004643833.png]: /images/20210508/3f6e9f4be934428eae0f23c22921f415.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d1aml6aGlzaHVp_size_16_color_FFFFFF_t_70 1]: /images/20210508/4ceeb7e7500b43b1be8c85b23952e681.png
[20190728183626491.png]: /images/20210508/f8fa39860f6b404a8d11af17b5de5380.png
[20191029232152548.png]: /images/20210508/6430431e75894661a35ae1d94bf3d857.png