/* 将已有编号方式转换为新的方式并统计 */
CREATE TABLE PopTbl
(pref_name VARCHAR(32) PRIMARY KEY,
 population INTEGER NOT NULL);

INSERT INTO PopTbl VALUES('德岛', 100);
INSERT INTO PopTbl VALUES('香川', 200);
INSERT INTO PopTbl VALUES('爱媛', 150);
INSERT INTO PopTbl VALUES('高知', 200);
INSERT INTO PopTbl VALUES('福冈', 300);
INSERT INTO PopTbl VALUES('佐贺', 100);
INSERT INTO PopTbl VALUES('长崎', 200);
INSERT INTO PopTbl VALUES('东京', 400);
INSERT INTO PopTbl VALUES('群马', 50);


/* 用一条SQL语句进行多条件统计 */
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);


/* 用CHECK约束定义多个列的条件关系 */
CREATE TABLE TestSal
(sex CHAR(1) ,
 salary INTEGER comment '男:1  女:2',
CONSTRAINT check_salary CHECK
             ( CASE
                   WHEN sex = '2' THEN
                       CASE WHEN salary <= 200000 THEN 1 ELSE 0 END
                   ELSE
                       1
                   END = 1 )
);
# CHECK等式判断:
# 后，CHECK 约束要求 CASE 语句的结果必须等于 1。
# 如果等于 1，则表示数据有效，插入或更新操作可以成功；
# 如果不等于 1，则会导致插入或更新操作失败。

INSERT INTO TestSal VALUES(1, 200000);
INSERT INTO TestSal VALUES(1, 300000);
INSERT INTO TestSal VALUES(1, NULL);
INSERT INTO TestSal VALUES(2, 200000);
#
INSERT INTO TestSal VALUES(2, 300000);  -- error
INSERT INTO TestSal VALUES(2, NULL);    -- error
INSERT INTO TestSal VALUES(1, 300000);


/* 在UPDATE语句里进行条件分支 */
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, '三');


/* 表之间的数据匹配 */
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(201806, 1);
INSERT INTO OpenCourses VALUES(201806, 3);
INSERT INTO OpenCourses VALUES(201806, 4);
INSERT INTO OpenCourses VALUES(201807, 4);
INSERT INTO OpenCourses VALUES(201808, 2);
INSERT INTO OpenCourses VALUES(201808, 4);


/* 在CASE表达式中使用聚合函数 */
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');




# 使用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 PopTbl
 GROUP BY CASE pref_name
             WHEN '德岛' THEN '四国'
             WHEN '香川' THEN '四国'
             WHEN '爱媛' THEN '四国'
             WHEN '高知' THEN '四国'
             WHEN '福冈' THEN '九州'
             WHEN '佐贺' THEN '九州'
             WHEN '长崎' THEN '九州'
             ELSE '其他' END;
# SQL语句的执行过程
# https://bbs.huaweicloud.com/blogs/326034#:~:text=%E7%AD%94%3A%20%E5%9C%A8SQL%E6%89%A7%E8%A1%8C%E9%A1%BA%E5%BA%8F,%E5%AD%97%E6%AE%B5%2C%E5%88%99%E5%8F%AF%E8%83%BD%E5%87%BA%E7%8E%B0%E6%AD%A7%E4%B9%89%E3%80%82

# 2、为什么group by和select同时使用时,select中的字段必须出现在group by后或者聚合函数中。
#   答: 在SQL执行顺序中可以发现,是先执行group by再执行select,所以此时数据就可以能存在分组的一个字段对应非分组字段的多条数据,如果此时查询非分组字段,则可能出现歧义。如:使用班级分组,但是查询班级中的学生,此时一个班级对应多个学生,无法在分组的同时又查询单个学生,所以会出现歧义。


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

# 优化(使用别名)
SELECT CASE
           WHEN pref_name IN ('德岛', '香川', '爱媛', '高知') THEN '四国'
           WHEN pref_name IN ('福冈', '佐贺', '长崎') THEN '九州'
           ELSE '其他'
           END AS district,
       SUM(population) AS total_population
FROM
    PopTbl
GROUP BY district;



-- 按人口数量等级划分都道府县
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;



-- 把县编号转换成地区编号(2)：将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 PopTbl
 GROUP BY district;


SELECT pref_name,
       population
  FROM PopTbl2
 WHERE sex = '1';


SELECT pref_name,
       population
  FROM PopTbl2
 WHERE sex = '2';


# 统计各县的人口
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;


SELECT pref_name,
       -- 男性人口
       CASE WHEN sex = '1' THEN population ELSE 0 END AS cnt_m,
       -- 女性人口
       CASE WHEN sex = '2' THEN population ELSE 0 END AS cnt_f
FROM PopTbl2;


# 表级约束  限制性别和薪资
# CONSTRAINT check_salary CHECK
#    ( CASE WHEN sex = '2'
#           THEN CASE WHEN salary <= 200000
#                     THEN 1 ELSE 0 END
#      ELSE 1 END = 1 );


# 建表约束
# CONSTRAINT check_salary CHECK
#    ( sex = '2' AND salary <= 200000 );



# 创建Salaries表
create table Salaries(
    name varchar(5) not null comment '名称',
    salary int comment '薪资'
);

insert into Salaries(name, salary) values ('相田',270000),
                                          ('神绮',324000),
                                          ('木村',220000),
                                          ('齐藤',290000);

-- 条件1
UPDATE Salaries
SET salary = salary * 0.9
WHERE salary >= 300000;

-- 条件2
UPDATE Salaries
SET salary = salary * 1.2
WHERE salary >= 250000 AND salary < 280000;


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;



-- 1.将a转换为中间值d
UPDATE SomeTable
   SET p_key = 'd'
 WHERE p_key = 'a';

-- 2.将b调换为a
UPDATE SomeTable
   SET p_key = 'a'
 WHERE p_key = 'b';

-- 3.将d调换为b
UPDATE SomeTable
   SET p_key = 'b'
 WHERE p_key = 'd';


-- 用CASE表达式调换主键值
# 在 MySQL 中，原生延迟约束是不支持的。如果需要类似的功能，通常需要通过存储过程、触发器、应用程序代码，或者手动管理事务和约束来实现。每种方法都有各自的优缺点，选择时应根据实际需求和风险进行权衡。
# 以下SQL将会执行错误  因为当主键为 a 的行 的主键更改为 b 时。此时将会出现两个主键为 b 的行  违反了主键的唯一性
# MySQL 不支持延迟约束 （其他SQL可以通过延迟约束的方式来避免此问题  当开启延迟约束时，约束会等到SQL执行完之后再去检查）
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');



-- 表的匹配：使用IN谓词
SELECT course_name,
       CASE WHEN course_id IN
                    (SELECT course_id FROM OpenCourses
                      WHERE month = 201806) THEN '○'
            ELSE '×' END AS "6月",
       CASE WHEN course_id IN
                    (SELECT course_id FROM OpenCourses
                      WHERE month = 201807) THEN '○'
            ELSE '×' END AS "7月",
       CASE WHEN course_id IN
                    (SELECT course_id FROM OpenCourses
                      WHERE month = 201808) THEN '○'
            ELSE '×' END AS "8月"
  FROM CourseMaster;


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



-- 条件1：选择只加入了一个社团的学生
SELECT std_id, MAX(club_id) AS main_club
  FROM StudentClub
 GROUP BY std_id
HAVING COUNT(*) = 1;


-- 条件2：选择加入了多个社团的学生
SELECT std_id, club_id AS main_club
  FROM StudentClub
 WHERE main_club_flg = 'Y';


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;





# # # # # # # # # # # # # # # # # #
# 习题
# # # # # # # # # # # # # # # # # #

# 使用SQL从 多列 中选出最大的值

# 创建样例表
create table Greatests(
    `key` varchar(1) PRIMARY KEY ,
    x int default null,
    y int default null,
    z int default null
);
insert into Greatests(`key` , x, y ,z)
            values ('A',1,2,3),
                   ('B',5,5,2),
                   ('C',4,7,1),
                   ('D',3,3,8);

# case 1 : 从x y选出最大的值
select `key`,
       case when x > y then x
            else y
       end as greatest
from Greatests;

# case 2 : 从 x y z 三列中选择最大的
select `key`,
       case when x >= y and x >= z then x
            when y >= x and y >= z then y
            when z >= x and z >= y then z
           else ''
           end as greatest
from Greatests;



# 练习题1-1-2：转换行列——在表头里加入汇总和再揭


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


# If函数只能用作单个条件判断，case when函数可以用作多个条件判断
select sex                                        as '性别',
       sum(population)                            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 = '高知'
                    then population
                else 0 end
           )  as '高知',
       sum(case when pref_name = '德岛' or pref_name = '香川'
                     or pref_name = '爱媛' or pref_name = '高知'
                    then population
                else 0 end
           )  as '四国（再揭）'
from PopTbl2
group by sex;


# 1-1-1  例题按照 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 ;


# 使用FIELD 函数
select `key`
from Greatests
order by FIELD(`key`,'B','A','D','C');