select e.emp_id, e.emp_name, d.dept_name
 from Employee e
 inner join Department d on e.dept_id = d.dept_id
 where d.dept_name =  'finance'
-- and e.emp_name like '%A%';

-- MySQL有几种注释语法

-- 1、单行注释符"--"，--注释符后需要加一个空格，注释才能生效
/* 2、用C语言风格的注释注释语句块 */
-- 3、单行注释符"#"，# 注释符后直接加注释内容
SELECT 1+1 from dual;     -- 注释文字

SELECT 1+1 from dual;    # 注释文字，这个#号在MySQL中也是注释
/* 标准SQL注释采用了C语言中的注释语法, MySQL同样支持这种语法  */
-- 下面这段注释表示：当接收SQL的Server版本大于4.0.001时，后面的注释文字就可以被识别。
SELECT /*!40001 SQL_NO_CACHE */  count(*) FROM db_test.test_table
SELECT SQL_NO_CACHE  count(*) FROM db_test.test_table

-- 建库，在Unix中，库名是严格区分大小的，强烈建议库名由小写字母和下划线组成
CREATE DATABASE `database_test`  IF NOT EXISTS CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci';

-- 设置某个库只读
-- MySQL 8.0.22中引入的READ ONLY选项控制是否允许修改数据库和其中的对象。允许的值为DEFAULT或0（非只读）和1（只读）。
-- 此选项对于数据库迁移非常有用，因为启用了只读功能的数据库可以迁移到另一个MySQL实例，而不必担心在操作过程中数据库可能会发生更改。

ALTER DATABASE  `database_test`   READ ONLY = 0 ;


-- 删库
DROP DATABASE database_test;


-- 修改数据库
-- 注意：在 MySQL 中，数据库创建之后库名是无法直接修改的。能改的只有字符集等属性。

-- 文件大小写，在不同的操作系统中的文件系统，是不一样的。
-- Linux操作系统，严格区分大小写，Test 和 TEST 是两个不同的文件或文件夹。
-- Windows操作系统，不区分大小写，无法同时创建 Test 和 TEST 两个同名的文件或文件夹。

-- 对于MySQL数据库和表，要在操作系统创建与库名和表名相同的文件夹或文件。

-- lower_case_file_system 是一个只读参数，无法被修改，这个参数是用来告诉你在当前的系统平台下，是否对文件名大小写敏感。

-- lower_case_table_names是一个MySQL可配置的参数，它的取值如下：

-- 0 大小写敏感。（Unix，Linux默认） 创建的库表将原样保存在磁盘上。
-- 如 create database TeSt ; 将会创建一个TeSt的目录，create table AbCCC …将会原样生成AbCCC.frm。 SQL语句也会原样解析。

-- 1 大小写不敏感。（Windows默认） 创建库表时，MySQL将所有的库表文件转换成小写存储在磁盘上。SQL语句同样会将库表名转换成小写。
-- 如需要查询以前创建的Testtable（生成Testtable.frm文件），即便执行select * from Testtable，也会被转换成select * from testtable，致使报错表不存在。

-- 列名，索引名，存储过程、函数及事件名称在任何操作系统下都不区分大小写，列别名也不区分大小写。


-- 最佳实践：建议将lower_case_table_names值统一设置为0，在代码中对大小写进行严格区分
-- 库名，表名，字段名，统一由小写英文字符和数字组成。

-- 在各家公司，都存在各种设计规范：
-- https://support.huaweicloud.com/bestpractice-rds/rds_02_0013.html


-- lower_case_table_names 参数是全局系统变量，不可以动态修改，想要变动时，必须写入配置文件然后重启数据库生效。
-- 如果你的数据库该参数一开始为 0 ，现在想要改为 1 ，这种情况要格外注意，因为若原实例中存在大写的库表，则改为 1 重启后，这些库表将会不能访问。
-- 如果需要将 lower_case_table_names 参数从 0 改成 1 ，可以按照下面步骤修改：

-- 1、首先核实下实例中是否存在大写的库及表，若不存在大写的库表，则可以直接修改配置文件然后重启。若存在大写的库表，则需要先将大写的库表转化为小写，然后才可以修改配置文件重启。

-- 2、通过 mysqldump 备份相关库，备份完成后删除对应库，之后修改配置文件重启，最后将备份文件重新导入。此方法用时较长，一般很少用到。

-- 3、 通过 rename 语句修改

-- 将大写表重命名为小写表
rename table TEST to test;

-- 若存在大写库 则需要先创建小写库 然后将大写库里面的表转移到小写库
rename table TESTDB.test_tb to testdb.test_tb;

-- 分享两条可能用到的SQL
-- 查询实例中有大写字母的表
SELECT
 TABLE_SCHEMA,
 TABLE_NAME
FROM
 information_schema.`TABLES`
WHERE
 TABLE_SCHEMA NOT IN ( 'information_schema', 'sys', 'mysql', 'performance_schema' )
 AND table_type = 'BASE TABLE'
 AND TABLE_NAME REGEXP BINARY '[A-Z]'


-- 拼接SQL 将大写库中的表转移到小写库
SELECT
 CONCAT( 'rename table TESTDB.', TABLE_NAME, ' to testdb.', TABLE_NAME, ';' )
FROM
 information_schema.TABLES
WHERE
 TABLE_SCHEMA = 'TESTDB';


-- 建表
-- 表名，字段名，字段类型，字段的约束，字段的字符集，存储引擎等
CREATE TABLE `cwd_group` (
  `id` bigint NOT NULL,
  `group_name` varchar(255) COLLATE utf8_bin NOT NULL,
  `lower_group_name` varchar(255) COLLATE utf8_bin NOT NULL,
  `active` char(1) COLLATE utf8_bin NOT NULL,
  `local` char(1) COLLATE utf8_bin NOT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  `description` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `group_type` varchar(32) COLLATE utf8_bin NOT NULL,
  `directory_id` bigint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cwd_group_name_dir_id` (`lower_group_name`,`directory_id`),
  KEY `idx_group_active` (`active`,`directory_id`),
  KEY `idx_group_dir_id` (`directory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE Product (
    product_id        char(4)         NOT NULL,
    product_name      varchar(100)    NOT NULL,
    product_type      varchar(32)     NOT NULL,
    sale_price        integer                 ,
    purchase_price    integer                 ,
    regist_date       date                    ,
    PRIMARY KEY (`product_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- 在demo库下建表ChineseCharInfo，有两列，其中ID为自增主键
CREATE TABLE demo.ChineseCharInfo
(
    ID        int              NOT NULL     auto_increment,
    Hanzi     varchar(10)      NOT NULL,
    primary   key (ID)
)
ENGINE=InnoDB auto_increment=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


-- 在demo库下建表ChinesePinyinInfo
CREATE TABLE demo.ChinesePinyinInfo
(
    ID         INT                  NOT NULL     AUTO_INCREMENT,
    CharID     INT                  NULL,
    Pinyin     VARCHAR(10)          NULL,
    Tone       TINYINT UNSIGNED     NULL,

    primary key (ID),
    -- 方式一：不指定外键名称，数据库自动生成
    FOREIGN KEY (CharID) REFERENCES ChineseCharInfo(ID) ON DELETE CASCADE ON UPDATE CASCADE

    -- 方式二：指定外键名称为(FK_Name)
    -- constraint FK_Name foreign key (CharID) references ChineseCharInfo(ID) on delete cascade on update cascade
)
ENGINE=innodb AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- 创建一个与原表结构一样的表
CREATE TABLE demo.ChineseCharInfobak LIKE demo.ChineseCharInfo

-- 通过sidea和sideb列中直角三角形的边的长度 ，并计算下斜边的长度 sidec（其他边的平方和的平方根）

-- 建表定义直角三角形（长边，短边，斜边（斜边是计算出来的））
CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);

-- 测试数据
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);


mysql> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|     1 |     1 | 1.4142135623730951 |
|     3 |     4 |                  5 |
|     6 |     8 |                 10 |
+-------+-------+--------------------+


-- 表生成列语法
col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

-- 其中，GENERATED ALWAYS可以省略；
-- AS定义了生成列的表达式；
-- VIRTUAL表示创建虚拟生成列，虚拟列的值不会存储，而是在读取时BEFORE触发器之后立即计算；
-- STORED表示存储生成列；默认创建的是VIRTUAL生成列。



CREATE TABLE `iam`.`biz_cloudsign_login`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `business_system_code` int(11) NOT NULL,
  `user_department` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `employee_num` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `identity_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `client_id` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `client_ip` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `random_num` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `cert_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `encrypted_token` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `updated_at` datetime(0) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,

) ENGINE = InnoDB AUTO_INCREMENT = 302 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;



SELECT
    DATE_FORMAT(v.updated_at,   '%Y-%m-%d'),
    count(DATE_FORMAT(v.updated_at,   '%Y-%m-%d'))
FROM
    biz_cloudsign_login v
    INNER JOIN ( SELECT MAX( id ) 'id' FROM biz_cloudsign_login GROUP BY employee_num,DATE_FORMAT(updated_at, '%Y-%m-%d') ) c ON v.id = c.id
    GROUP BY DATE_FORMAT(v.updated_at,   '%Y-%m-%d')


-- 在查询字段和分组字段加上索引
ALTER TABLE `iam`.`biz_cloudsign_login`
ADD INDEX `idx_updated_at`(`updated_at`),
ADD INDEX `idx_employee_num_updated_at`(`employee_num`, `updated_at`);


-- 有时候需要对字段上加函数然后进行GROUP BY。查看执行计划，发现出现 Using temporary， 分组条件并没有走索引。
-- 因为mysql 5.7 的函数会导致索引失效。我们可以通过添加一个冗余字段来保存函数的计算结果，然后添加索引，这时候的GROUP BY就会走索引了。

-- 加个新字段，虚拟列。
ALTER TABLE `iam`.`biz_cloudsign_login`
ADD COLUMN `update_at_date` date GENERATED ALWAYS AS (date_format(`updated_at`,'%Y-%m-%d')) Virtual NULL AFTER `updated_at`;

-- 对虚拟字段加上索引
ALTER TABLE `iam`.`biz_cloudsign_login`
ADD INDEX `idx_updated_at`(`update_at_date`),
ADD INDEX `idx_employee_num_updated_at`(`employee_num`, `update_at_date`);

-- 第一种语法用于将指定的数据插入目标表
INSERT INTO … VALUES …
-- 第二种语法可以将一个查询结果插入目标表
INSERT INTO … SELECT …

--单条插入
INSERT INTO items(name,city,price,number,picture)  VALUES ('耐克运动鞋','广州',500,1000,'003.jpg');



--批量插入
INSERT INTO  items(name,city,price,number,picture)
 VALUES
('耐克运动鞋','广州',500,1000,'003.jpg'),
('耐克运动鞋2','广州2',500,1000,'002.jpg');

-- 1、要根据情况设置一次批量插入的数据量，数据量大，在网络中传输的事件也越久，出现问题的可能也越大；

-- 2、除了网络，还要看机器配置，MySQL Server配置差了，sql写得再好，效率也不会太高；

-- 3、另外批量这个词，是指一次插入多条数据，我们除了要注意数据的条数，还要注意一条数据的大小，举个例子：比如一条记录的数据量有1M，10条记录的数据量就10M，这时一次插100条，100M数据，嘿嘿，你试试看！！所以，一次插入多少数据，一定要经过多次测试后再决定，别人1次插100条最优，你可能1次插10条才最优，没有绝对的最优值（批量插入未必总是比单条插入效率高）。

-- 4、数据库有个参数，max_allowed_packet，也就是每一个包（sql）命令大小，默认是1M，那么sql的长度大于1M就会报错。你可能会说，咱们把这个参数设成10M，100M不就行了？？？对呀，没毛病，但你是DBA吗？你有权限吗？即使调大这一个参数，你要知道影响的可不止你这一张表，而是整个DB Server，那影响的可是很多库，很多表。

-- 5、批量插入并不是越快越好，我们可能希望越快越好，这很正常，节省时间嘛。但是我们一定要知道，数据库分读写，有集群，这就意味着，需要同步！！！如果有分库分表分区的情况，如果短时间内插入的数据量太大，数据库同步可能就会比较迷了，读写数据不一致的情况在所难免了，可能会因为一张表的批量插入，影响整个DB服务组的同步，同时还要考虑并发问题。

--如果使用 Oracle 数据库，我们需要在执行 INSERT 语句之后使用 COMMIT 语句提交修改　

-- REPLACE INSERT语句

-- REPLACE INTO是INSERT INTO的加强版，既可以更新数据也可以插入数据；
-- 在向表中插入数据的时候，经常遇到这样的情况：1. 首先判断数据是否存在； 2. 如果不存在，则插入；3.如果存在，则更新。

-- REPLACE INTO用于实时覆盖写入数据。写入数据时，会先根据主键判断待写入的数据是否已经存在于表中，并根据判断结果选择不同的方式写入数据：

-- 使用REPLACE INTO时，首先会根据主键或者唯一索引查看表中是否有该条记录，有的话则先删除，然后插入数据；没有该记录则直接插入。

CREATE TABLE `stu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cid` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cid` (`cid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
-- 其中id为自增主键，cid为唯一索引。

-- 插入两条数据
INSERT INTO stu(cid, name) VALUES(1, '1111');
INSERT INTO stu(cid, name) VALUES(2, '2222');
REPLACE INTO stu(cid, name) VALUES(3,'3333');


-- 1    1   1111
-- 2    2   2222
-- 3    3   3333


REPLACE INTO stu(cid, name) VALUES(3,'33331');
-- 使用REPLACE INTO时，首先会根据主键或者唯一索引查看表中是否有该条记录，有的话则先删除，然后插入数据；没有该记录则直接插入。
-- 原先id为3的记录变成了id为4了，说明原先id为3的记录先删除了，然后新插入了id为4（id自增）的新记录

-- 1    1   1111
-- 2    2   2222
-- 4    3   33331


--再次执行replace，id为4和cid为2的原记录都存在，则被删除。然后再插入这条新纪录
REPLACE INTO stu(id, cid, name) VALUES(4, 2,'42')
> Affected rows: 3
> 查询时间: 0.007s
-- 1    1   1111
-- 4    2   42

-- INSERT INTO…SELECT…FROM 语句用于快速地从一个或多个表中取出数据，并将这些数据作为行数据插入另一个表中。
-- SELECT 子句返回的是一个查询到的结果集，INSERT 语句将这个结果集插入指定表中，结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致。

-- UPDATE 语句的基本语法如下
UPDATE t
SET col1 = expr1,
    col2 = expr2,
    ...
    [WHERE condition];

-- 其中 t 是更新操作的目标表，SET 子句指定了需要更新的列和更新后的值，多个字段使用逗号进行分隔。
-- 如果指定了 WHERE 子句，只有满足条件的数据行才会被更新。如果没有指定条件，将会更新表中的所有数据行

-- 更新赵云的月薪和奖金
UPDATE emp_devp
SET salary = salary + 1000,
    bonus = 8000
WHERE emp_name = '赵云';

DELETE FROM t
    [WHERE conditions];

-- 其中 t 是删除操作的目标表。
-- 如果指定了 WHERE 子句，只有满足条件的数据行才会被删除。如果没有指定条件，将会删除表中所有的数据行。


-- leetcode. 196. Delete Duplicate Emails 删除表中重复的数据

-- Person表中有ID（主键）和EMAIL两列，删除email重复的行，仅保留id最小的那一行
DELETE p from Person p, Person q where p.Id>q.Id AND q.Email=p.Email

DELETE FROM Person
WHERE Id NOT IN (
    SELECT Id FROM (
        SELECT MIN(Id) AS Id
        FROM Person
        GROUP BY Email
    ) AS min_id  -- 此处需使用别名，否则会发生报错
);

-- Oracle、MySQL、Microsoft SQL Server以及PostgreSQL
TRUNCATE TABLE emp_devp;

-- SQLite 没有提供 TRUNCATE 语句，不过它对 DELETE 语句进行了优化，如果我们不指定 WHERE 子句，实际的效果等同于 TRUNCATE 语句。

UPDATE ... WHERE EXIST (SELECT 1 FROM ... WHERE 条件)
INSERT ... WHERE NOT EXIST(SELECT 1 FROM ... WHERE 条件)

-- Oracle、Microsoft SQL Server 以及 PostgreSQL
MEGRE INTO target_table [AS t_alias]
USING source_table [AS s_alias]
ON (conditions)
WHEN MATCHED THEN
  UPDATE
  SET col1 = expr1,
      col2 = expr2,
      ...
WHEN NOT MATCHED THEN
  INSERT (col1, col2, ...)
  VALUES (expr1, expr2, ...);

-- 其中 target_table 是合并操作的目标表。
-- USING 子句指定了数据源，可以是一个表或者查询语句。
-- ON 子句指定了数据合并的条件，通常使用主键或者唯一键相等作为合并的条件。

-- 对于数据源中的每条记录:
-- 如果目标表中存在匹配的记录则执行 WHEN MATCHED THEN 分支的更新操作，.
-- 如果目标表中不存在匹配的记录则执行 WHEN NOT MATCHED THEN 分支的插入操作。

-- 在MySQL数据库中，如果在INSERT语句后面带上ON DUPLICATE KEY UPDATE 子句，而要插入的行与表中现有记录的惟一索引或主键中产生重复值，那么就会发生旧行的更新；如果插入的行数据与现有表中记录的唯一索引或者主键不重复，则执行新纪录插入操作。另外，ON DUPLICATE KEY UPDATE不能写WHERE条件。


-- MySQL
-- MySQL使用 ON DUPLICATE KEY UPDATE 子句合并数据，插入数据时如果主键或者唯一索引出现重复值则执行更新操作。这种专有语法不支持 DELETE 子句。
INSERT INTO emp_devp(emp_id, emp_name, sex, dept_id, manager,hire_date, job_id, salary, bonus, email)
SELECT emp_id, emp_name, sex, dept_id, manager,hire_date, job_id, salary, bonus, email
FROM employee s
WHERE dept_id = 4
ON DUPLICATE KEY UPDATE
   emp_name = s.emp_name, sex = s.sex,
   dept_id = s.dept_id, manager = s.manager,
   hire_date = s.hire_date, job_id = s.job_id,
   salary = s.salary, bonus = s.bonus,
   email = s.email;

-- PostgreSQL和SQLite
-- PostgreSQL 和 SQLite 使用 ON CONFLICT(emp_id) DO UPDATE 子句合并数据，插入数据时如果 emp_id 出现重复值则执行更新操作，EXCLUDED 代表了数据源中的记录。这种专有语法不支持 DELETE 子句。
INSERT INTO emp_devp(emp_id, emp_name, sex, dept_id, manager,hire_date, job_id, salary, bonus, email)
SELECT emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email
FROM employee s
WHERE dept_id = 4
ON CONFLICT(emp_id) DO UPDATE
SET emp_name = EXCLUDED.emp_name, sex = EXCLUDED.sex,
    dept_id = EXCLUDED.dept_id, manager = EXCLUDED.manager,
    hire_date = EXCLUDED.hire_date, job_id = EXCLUDED.job_id,
    salary = EXCLUDED.salary, bonus = EXCLUDED.bonus,
    email = EXCLUDED.email;

--PostgreSQL
CREATE TABLE users (firstname text, lastname text, id serial primary key);

INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;


UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name, price AS new_price;

DELETE FROM products
  WHERE obsoletion_date = 'today'
  RETURNING *;

CREATE TABLE users (
    firstname text,
    lastname text,
    id serial primary key
);

-- 在INSERT INTO或者UPDATE的时候在最后面加上RETURNING colname，PostgreSQL会在插入或者更新数据之后会返回你指定的字段。
-- 当使用 serial列来提供唯一标识符时， RETURNING可以返回分配给新行的ID：
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

-- 违反外键约束，职位不存在
INSERT INTO employee (emp_name, sex, dept_id, manager, hire_date,
                      job_id, salary, bonus, email, comments,
                      create_by, create_ts, update_by, update_ts)
VALUES ('马超', '男', 5, 18, CURRENT_DATE, -- Microsoft SQL Server 需要替换该函数
        11, 6000, NULL, 'machao@shuguo.com', NULL,
        'Admin', CURRENT_TIMESTAMP, NULL, NULL);

-- 查询语句的基本结构

select A1,A2,A3 ... An
from r1,r2,r3 ... rn
where P

-- 注意：这个方法只是用来理解SQL语句的查询结果
-- from 多个关系后，可以理解为所有关系的笛卡尔积
-- 谓词P对关系进行过滤
-- 再从 select 中提取需要的列
-- 实际上，组成笛卡尔积的时候，会尽可能先执行谓词过滤一些数据后，再进行笛卡尔积

--SQL92标准
SELECT DISTINCT ...,...,...(存在聚合函数)
FROM ...,...,...(多表查询)
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ...,...
HAVING 包含聚合函数的过滤条件
ORDER BY ...,...(ASC/DESC)
LIMIT ...,...;


--SQL99标准
SELECT DISTINCT ...,...,...(存在聚合函数)             --->2
FROM ... (LEFT/RIGHT) JOIN ... ON 多表的连接条件   --->1
WHERE 不含聚合函数的过滤条件                         --->1
GROUP BY ...,...                                 --->1
HAVING 包含聚合函数的过滤条件                        --->1
ORDER BY ...,...(ASC/DESC)                              --->3
LIMIT ...,...;                                          --->3

SELECT  *
FROM table_name
WHERE col_name IN (value1, value2,...);


-- in 操作符允许在 where 子句中规定多个值。
-- in 查询相当于多个 or 条件的叠加，比较好理解。
-- in 查询就是先将子查询条件的记录全都查出来。
-- in 查询的子条件返回结果必须只有一个字段。


-- 查找拥有员工的部门
SELECT *
FROM department d
WHERE EXISTS (SELECT 1 FROM employee WHERE dept_id = d.dept_id);


SELECT *
FROM department d
WHERE dept_id IN (SELECT dept_id FROM employee);

SELECT *
FROM department d
WHERE dept_id = ANY (SELECT dept_id FROM employee);

-- 学生成绩表
DROP TABLE IF EXISTS tbl_student_score;
CREATE TABLE tbl_student_score (
  id INT(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  sno VARCHAR(12) NOT NULL COMMENT '学号',
    subject VARCHAR(5) NOT NULL COMMENT '课程',
    score TINYINT(3) NOT NULL COMMENT '分数',
  PRIMARY KEY (id)
);
INSERT INTO tbl_student_score(sno,subject,score) VALUES
('20190607001','数学',100),
('20190607001','语文',80),
('20190607001','物理',80),
('20190608003','数学',80),
('20190608003','语文',95),
('20190609006','数学',40),
('20190609006','语文',90),
('20190610011','数学',80);

SELECT * FROM tbl_student_score;


-- 查询出"所有科目分数都在 50 分以上的学生"
-- 20190607001、20190608003、20190610011 这三个学生满足条件，我们需要将这 3 个学生查出来

-- 将查询条件“所有科目分数都在 50 分以上” 转换成它的双重否定 “没有一个科目分数不满 50 分”，然后用 NOT EXISTS 来表示转换后的命题

-- 没有一个科目分数不满 50 分
SELECT DISTINCT sno
FROM tbl_student_score tss1
WHERE NOT EXISTS -- 不存在满足以下条件的行
(    SELECT * FROM tbl_student_score tss2
    WHERE tss2.sno = tss1.sno
    AND tss2.score < 50    -- 分数不满50 分的科目
);

--CTE语句如下
-- cte_name 指定了 CTE 的名称，后面是可选的字段名。
-- AS关键字后面的子查询是CTE 的定义语句，定义了它的表结构和数据。
-- 最后的 SELECT 是主查询语句，它可以引用前面定义的 CTE。除了 SELECT 之外，主查询语句也可以是 INSERT、UPDAT 或 DELETE 等。

WITH cte_name(col1, col2, ...) AS (
 subquery
)
SELECT * FROM cte_name;

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;



-- 列表中的名称数量必须与查询结果中的字段数量相同
WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT * FROM cte;


-- 子查询的CTE写法
-- CTE 将子查询的结果集用 with 语句提到最前面，取一个别名，再在后面的语句中使用
with derived(...) as (
    subquery
)
select ... from derived, table_name1 ……;


-- CTE 可以引用其他CTE
with derived_one as (
    subquery
),
derived_two as (
    select …… from derived_one
)
select …… from derived_one, derived_two ……;

-- 在同一个语句级别中只允许存在一个WITH子句，有效的语法格式是为一个WITH子句定义多个从句，使用逗号进行分隔：
-- 在包含WITH子句的查询中，可以使用CTE的名称访问相应 CTE 的结果集。
-- 前面定义的CTE可以在其他的CTE中进行引用，因此 CTE 可以基于前面的 CTE 进行定义。
-- 引用自己的 CTE 被称为递归 CTE。递归 CTE 的使用场景包括生成序列，遍历层次数据或树状结构的数据。
-- 通用表表达式属于 DML 语句的可选部分。




--找出预算值最大的系:系名，预算
-- 方法一
select dept_name , budget
 from department ,
 (SELECT max( budget )as budget FROM department) as max_budget

   where department.budget = max_budget.budget


-- 先用with语句查出一个临时表 max_budget ( VALUE )，
这里面存了所有系里面的最大的预算值。再将这个表与系表关联查询
WITH max_budget (value) AS ( SELECT max( budget ) FROM department ) SELECT
budget
FROM
    department,
    max_budget
WHERE
    department.budget = max_budget.value

    SELECT ...
    FROM t1 LEFT JOIN ((SELECT ... FROM ...) AS dt JOIN t2 ON ...) ON ...


    WITH dt AS (SELECT ... FROM ...)
    SELECT ...
    FROM t1 LEFT JOIN (dt JOIN t2 ON ...) ON ...

    -- 传统派生表子查询写法：每个子查询都要写完成SQL

    SELECT ...
    FROM (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b) AS d1
    JOIN (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b) AS d2 ON d1.b = d2.a;

    -- CTE写法：一次生成，多次引用

    WITH d AS (SELECT a, b, SUM(c) s FROM t1 GROUP BY a, b)
    SELECT ... FROM d AS d1 JOIN d AS d2 ON d1.b = d2.a;

    -- 传统这种不行，会报表不存在，要用更复杂的子查询
    SELECT ...
    FROM (SELECT ... FROM ...) AS d1, (SELECT ... FROM d1 ...) AS d2 ...


    -- 派生表写法
    WITH d1 AS (SELECT ... FROM ...),
    d2 AS (SELECT ... FROM d1 ...)
    SELECT
    FROM d1, d2 ...

-- 递归CTE ,  MySQL、PostgreSQL 以及 SQLite
WITH RECURSIVE derived(n) AS (
 SELECT 1
 UNION ALL
 SELECT n + 1 FROM derived WHERE n < 5
)
 SELECT * FROM derived;

-- 以上语句的执行结果是一个连续的数字序列：1,2,3,4,5

-- 如果在WITH子句中引用了自己，WITH子句必须使用WITH RECURSIVE。（如果没有 CTE 引用自己，也可以使用RECURSIVE，但不强制。）

-- 递归 CTE 的子查询由两部分组成，中间使用UNION [ALL]或者UNION DISTINCT进行连接：

-- 第一个SELECT语句用于生成初始数据行，该语句不会引用 CTE 自身。
-- 第二个SELECT语句在它的FROM子句中引用了 CTE自身，通过递归产生更多的结果。
-- 当第二个语句不会产生更多的新数据时结束递归。因此，递归 CTE由一个非递归的SELECT语句和一个递归的SELECT语句组成。
-- CTE最终结果中的字段类型由非递归的SELECT语句决定，所有字段都可以为空。查询结果的字段类型与递归SELECT语句无关。
-- 如果递归部分和非递归部分使用UNION DISTINCT进行连接，查询结果将会排除重复的数据行。
-- 这种方式可以用于执行传递闭包（transitive closure，例如两个地点之间的乘车路线）的查询，防止无限循环。

-- 递归部分的每次迭代只针对上次迭代生成的新数据行进行操作。
-- 如果递归部分包含多个查询块，迭代时每个查询块的执行顺序不固定，每个查询块基于它自己前一次迭代的结果，或者上次迭代结束后其他查询块生成的结果进行操作。


-- 前面递归 CTE 示例中的非递归语句如下，它会产生一条初始化的数据：
SELECT 1
-- 它的递归部分如下：
SELECT n + 1 FROM cte WHERE n < 5
-- 每次迭代时，SELECT语句将会产生一个比上一次结果中的 n 大 1 的新值。
-- 第一次迭代基于初始值（1）进行操作，生成 1+1=2；
-- 第二次迭代基于第一次迭代的结果（2），生成 2+1=3；如此等等。
-- 迭代一直执行到递归结束，此处为 n 的值大于或等于 5。
-- 如果递归部分产生的结果比非递归部分的字段长度更大，需要在非递归部分指定一个更宽的字段类型，避免数据被截断。



-- 假设用树形结构来描述一个部门的组织架构

-- DDL  用id来标识一个人，用manager_id来表示向上一个人汇报
CREATE TABLE db_test.employees_mgr (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR ( 100 ) NOT NULL,
    manager_id INT NULL,
    INDEX ( manager_id ),
    FOREIGN KEY ( manager_id ) REFERENCES employees_mgr ( id )
);


-- test data
insert into db_test.employees_mgr values
(333, "Yasmina", null), /* Yasmina is the CEO (manager_id is null) */
(198, "John", 333),     /* John has id 198 and reports to 333 (Yasmina) */
(692, "Tarek", 333),    /* Tarek has id 692 and reports to 333 (Yasmina) */
(29, "Pedro ", 198),    /* Pedro has id 29 and reports to 198 (John) */
(4610, "Sarah", 29);    /* Sarah has id 4610 and reports to 29 (Pedro) */


-- 要查询出如下这种树形结果

+------+---------+--------------------+
| id   | name    | path               |
+------+---------+--------------------+
|  333 | Yasmina | 333                |
|  198 | John    | 333->198           |
|  692 | Tarek   | 333->692           |
|   29 | Pedro   | 333->198->29       |
| 4610 | Sarah   | 333->198->29->4610 |
+------+---------+--------------------+
5 rows in set (0.00 sec)
mysql>


-- MySQL8.0中支持with子句
-- 通过递归CTE来查询用户的层级关系
-- 规划一个ID，name，path这样的一个层级临时表
 with recursive employee_paths (id, name, path) as (
     --先查最顶层节点
     select id, name, cast(id as char(200))
     from db_test.employees_mgr
     where manager_id is null
     union all
     -- 递归查询
     select e.id, e.name, concat(ep.path, '->', e.id)
     from employee_paths as ep
     join db_test.employees_mgr as e
     on ep.id = e.manager_id
    )
     select * from employee_paths;

 [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]


-- ORDER BY后面可以接列名，别名，列号（数字）、表达式、函数、分组函数等
-- 如果不显示指出是升序ASC还是降序DESC，则默认为是升序
SELECT * FROM tablename ORDER BY column1,column2,column3 ;

-- 如果排序字段中存在相同的数据，那么它们的排序顺序是随机的。为了进一步明确这些数据的排序顺序，可以使用多列排序。
-- ORDER BY column1,column2         表示：column1和column2都是升序
-- ORDER BY column1,column2 DESC    表示：column1升序，column2降序

-- 对字符串类型中的中文字段排序

--

-- 子查询
-- 在另一个查询(外部查询)中嵌套另一个查询语句(内部查询)，并使用内部查询的结果值作为外部查询条件。
-- 子查询在where中
-- where条件比对的值是从其他表查出来的。
-- 其实不光是 where ，select 后面跟的字段名也可以是从其他表中查出来的。

SELECT
       customerNumber, checkNumber, amount
FROM
   　　 payments
WHERE
 　　   amount = (SELECT  MAX(amount) FROMpayments);


-- 标量子查询 （一行一列）
-- 那些只返回一个单一值的子查询称之为标量子查询：子查询里面的查询结果只返回一行一列一个值的情况。
-- 子查询可以像常量一样被用于 SELECT、WHERE、GROUP BY、HAVING 以及 ORDER BY 等子句中。
SELECT (SELECT m1 FROM e1 LIMIT 1);
SELECT * FROM e1 WHERE m1 = (SELECT MIN(m2) FROM e2);
SELECT * FROM e1 WHERE m1 < (SELECT MIN(m2) FROM e2);

-- 计算员工月薪与平均月薪之间的差值
SELECT emp_name AS "员工姓名", salary AS "月薪",
       salary - (SELECT AVG(salary) FROM employee) AS "差值"
FROM employee;


-- 行子查询（一行多列）
-- 顾名思义，就是返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）。
-- 其中的(SELECT m2, n2 FROM e2 LIMIT 1)就是一个行子查询
-- 整条语句的含义就是要从 e1 表中找一些记录，这些记录的 m1 和 n1 列分别等于子查询结果中的 m2 和 n2 列
SELECT * FROM e1 WHERE (m1, n1) = (SELECT m2, n2 FROM e2 LIMIT 1);

-- 查找所有与"孙乾"在同一个部门并且职位相同的员工

-- Oracle、MySQL、PostgreSQL 以及 SQLite
SELECT emp_name, dept_id, job_id
FROM employee
WHERE (dept_id, job_id) = (SELECT dept_id, job_id
                           FROM employee
                           WHERE emp_name = '孙乾')
AND emp_name != '孙乾';

-- 其中，外部查询的 WHERE 子句中包含一个行子查询，返回了"孙乾"所在的部门编号和职位编号，这两个值构成了一个记录。
-- 然后，外部查询使用该记录作为条件进行数据过滤，AND 运算符用于排除"孙乾"自己。
-- Microsoft SQL Server 目前不支持行子查询。

-- 列子查询（一列数据）
-- 列子查询自然就是查询出一个列的数据，不过这个列的数据需要包含多条记录
-- 其中的(SELECT m2 FROM e2)就是一个列子查询，表明查询出 e2 表的 m2 列 的所有值作为外层查询 IN 语句的参数。
SELECT * FROM e1 WHERE m1 IN (SELECT m2 FROM e2);

-- 表子查询（二维多行多列）
-- 顾名思义，就是子查询的结果既包含很多条记录，又包含很多个列
-- 对于返回多行数据的子查询，我们可以使用 IN 或者 NOT IN 运算符进行比较。
-- 其中的(SELECT m2, n2 FROM e2)就是一个表子查询、此SQL必须要在m1，n1都满足的条件下方可成立
SELECT * FROM e1 WHERE (m1, n1) IN (SELECT m2, n2 FROM e2);
-- 因为单个值（外部查询条件中的字段）与多个值（子查询返回的多个元组记录）的比较不能使用比较运算符（=、!=、<、<=、>、>= 等）。

-- ALL 运算符与比较运算符（=、!=、<、<=、>、>=）的组合分别表示等于、不等于、小于、小于或等于、大于、大于或等于子查询结果中的全部数据。


-- From子句中的子查询
-- 派生表（子查询）
-- FROM后面跟的表是通过其他查询查出来的，这种查询叫派生表，派生表必须要有别名，以便稍后在查询中引用其名称。
-- 这种子查询后边的 AS t 表明这个子 查询的结果就相当于一个名称为 t 的表，这个名叫 t 的表的列就是子查询结果中的列（m和n）。
-- 这个放在 FROM 子句中的子查询本质上相当于一个表，但又和我们平常使用的表有点儿不一样，MySQL 把这种由子查询结果集组成的临时表称之为派生表。
SELECT m, n FROM (SELECT m2 + 1 AS m, n2 AS n FROM e2 WHERE m2 > 2) AS t;
-- 派生表也是从 select 语句中返回的虚拟表。
SELECT
    column_list
FROM
    (SELECT column_list ... FROM table_1) derived_table_name
WHERE derived_table_name.c1 > 0;


-- 一般来说，子查询可以像普通查询一样包含各种子句，例如 JOIN、WHERE、GROUP BY 等，甚至可以嵌套其他的子查询。但是需要注意，不同数据库对于子查询中 ORDER BY 子句的处理方式存在差异。

-- 通常来说，子查询中的排序没有实际意义，不会影响到查询结果和显示顺序。只有外部查询中的 ORDER BY 子句能够决定最终结果的显示顺序。

 select * from stu cross join class;
 select * from stu,class;
 select * from stu as a ,  class as b join on 1=1 ;
 -- 可以看出，笛卡尔乘积的运算量超级大，一般不会使用笛卡尔乘积做表的关联查询

-- 标准SQL写法一
select user_name,dept_name from user natural join dept ;     --这个限制比较大，要求字段名称一致。
-- 标准SQL写法二
select user_name,dept_name from  user, dept where user.id = dept.id  ...  --所有共有属性都连接起来。
-- MySQL写法三


-- select name , id , title from A1 natural join A2 natural join A3
-- select name , id , title from A1 natural join A2 , A3 where A2.id = A3.id
-- 这两个写法的结果可能不一样，
-- 第一个可以认为将A1 A2进行自然连接的结果，再与A3进行自然连接
-- 第二个可以认为将A1 A2进行自然连接的结果，再与A3进行等值连接

-- 在这个例子中，只在ID上进行匹配。要求两个表都要有ID这个字段。
-- 使用 using 语法 ， select ... from T1 join T2 using(id)
select user_name,dept_name from user join dept using (id) ;
-- 使用 join on 关键字，on条件允许在参与连接的关系上设置连接谓词。
select  user_name,dept_name from user join dept on user.id = dept.id ;

-- tb1 inner join tb2 on something
-- 不保留未匹配的元组
select  user_name,dept_name from students  left outer join dept on user.id = dept.id ;

-- 查找拥有员工的部门
SELECT *
FROM department d
WHERE EXISTS (SELECT 1 FROM employee WHERE dept_id = d.dept_id);

-- 也可以用IN查询
SELECT *
FROM department d
WHERE dept_id IN (SELECT dept_id FROM employee);

-- 也可以用 =ANY 操作符
SELECT *
FROM department d
WHERE dept_id = ANY (SELECT dept_id FROM employee);

-- 查找没有员工的部门
SELECT *
FROM department d
WHERE NOT EXISTS (SELECT 1 FROM employee WHERE dept_id = d.dept_id);

-- 查询每个系的男教师平均工资(系名，平均工资)
-- where过滤出所有男教师。group by按系分组，聚合计算查出需要的列
select dept_name ,avg(salary) from instructor  where employe_type="male" group by dept_name


-- 对于下面这样的两个表：

-- 项目（项目名称，项目经理，价格）
-- 消费（消费流水号，会员手机号，项目名称，消费金额，消费日期）
-- 每个项目只能有一个项目经理，一个项目经理只能负责一个项目


-- 问题：统计出所负责项目消费总金额大于等于10万的项目和项目经理信息



SELECT
    项目.项目名称,
    项目.项目经理,
    sum( 消费金额 )
FROM
    项目,消费
WHERE
    项目.项目名称 = 消费.项目名称
GROUP BY
    项目.项目名称 项目.项目经理
HAVING
    sum( 消费金额 ) > 10000
ORDER BY
    sum( 消费金额 ) DESC

CREATE TABLE `tbl_ware` (
  `ware_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `ware_name` VARCHAR(100) NOT NULL COMMENT '商品名称',
  `ware_category` VARCHAR(100) NOT NULL COMMENT '商品类别',
  `sale_unit_price` INT COMMENT '销售单价',
  `purchase_unit_price` INT COMMENT '进货单价',
  `registration_date` DATE COMMENT '登记日期',
  PRIMARY KEY (`ware_id`) USING BTREE
) ENGINE=InnoDB COMMENT='产品';

INSERT INTO tbl_ware VALUES
(1,'T恤衫','衣服',100, 50,'2023-12-15'),
(2,'打孔器','办公用品',25, 10,'2023-12-15'),
(3,'运动T恤','衣服',150, 50,'2023-12-15'),
(4,'菜刀','厨房用具',75, 30,'2023-12-15'),
(5,'高压锅','厨房用具',600, 200,'2023-12-15'),
(6,'叉子','厨房用具',7, 3,'2023-12-15'),
(7,'菜板','厨房用具',98, 30,'2023-12-15'),
(8,'圆珠笔','办公用品',5, 2,'2023-12-15');


-- 统计每个类别产品的总进价
SELECT ware_category AS '商品类别' , SUM(purchase_unit_price) AS '总进价' FROM tbl_ware GROUP BY ware_category;

-- 如果还要同时统计所有总进价：两个SELECT结果集连接起来
SELECT ware_category AS '商品类别' , SUM(purchase_unit_price) AS '总进价' FROM tbl_ware GROUP BY ware_category;
UNION ALL
SELECT ware_category AS '合计' , SUM(purchase_unit_price) AS '总进价' FROM tbl_ware ;


-- 最后一行其实就是合计，WITH ROLLUP 是 MySQL 的独有写法
SELECT ware_category AS '商品类别' , SUM(purchase_unit_price) AS '总进价' FROM tbl_ware GROUP BY ware_category WITH ROLLUP;

explain SELECT city ,count(*) AS 'NUM' FROM user GROUP BY city;

-- 不能使用松散索引扫描
select c1,c3 from t1 group by c1,c2;
-- 可以使用松散索引扫描
select c1,c3 from t1 where c3 =  3 group by c1,c2;

an index idx(c1,c2,c3) on table t1(c1,c2,c3,c4)

CREATE TABLE `t1` (
    `c1` int(11) DEFAULT NULL,
    `c2` int(11) DEFAULT NULL,
    `c3` int(11) DEFAULT NULL,
    `c4` int(11) DEFAULT NULL,
    KEY `idx_g` (`c1`,`c2`,`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 下面的查询可以使用松散索引扫描
SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;


-- 下面的查询不能使用松散索引扫描
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 GROUP BY c2, c3;
SELECT c1, c3 FROM t1 GROUP BY c1, c2;

-- （1）SQL执行计划 "Using where; Using index" 使用紧凑索引扫描，索引列范围查询
explain SELECT DISTINCT `c1` FROM t1 WHERE `c2`>'B';

-- （2）SQL执行计划 "Using where; Using index for group-by" 使用松散索引扫描，索引列等值查询
explain SELECT DISTINCT `c1` FROM t1 WHERE `c2`='B';

-- （3）SQL执行计划 "Using index for group-by" 使用松散索引扫描
explain SELECT MIN(c2) from t1 group by c1;

-- （4）SQL执行计划 "Using index" 使用min/max之外的其它聚集函数，则不能使用松散索引扫描，使用紧凑索引扫描
explain SELECT SUM(c2) from t1 group by c1;

-- （5）SQL执行计划 "Using index for group-by" 使用松散索引扫描，满足索引前缀
explain SELECT `c1`,`c2` FROM t1 GROUP BY `c1`,`c2`;

-- （6）SQL执行计划 "Using where; Using index for group-by" 使用松散索引扫描，满足索引前缀、索引列等值查询
explain SELECT `c1`,`c2`,`c3` FROM t1  WHERE c3='C' GROUP BY `c1`,`c2`;

-- （7）SQL执行计划 "Using where; Using index for group-by" 使用松散索引扫描，虽然不满足索引前缀，但前缀中的列为常量
explain SELECT `c1`,`c2`,`c3` FROM t1  WHERE c1='C' GROUP BY `c1`,`c2`,`c3`;

-- （8）SQL执行计划 "Using index; Using temporary; Using filesort"，使用临时表，不满足前缀索引，分组无法走索引，需要临时表并对分组内元素排序
explain SELECT `c2`,`c3` FROM t1 GROUP BY `c2`,`c3`;

-- （9）SQL执行计划 "Using index; Using temporary; Using filesort"，使用临时表，不满足前缀索引，分组无法走索引，需要临时表并对分组内元素排序
explain SELECT `c1`,`c3` FROM t1 GROUP BY `c1`,`c3`;

-- （10）SQL执行计划 "Using where; Using index"，使用紧凑索引扫描，却别于（9）使用临时表，尽管不满足前缀索引，但前缀中的列为常量
explain SELECT `c1`,`c3` FROM t1 WHERE c2='B' GROUP BY `c1`,`c3`;

SELECT SQL_BIG_RESULT city,count(*) AS num FROM staff GROUP BY city;

--仅查询平均工资大于4000的系，列出这样系的系名和平均工资

select dept_name ,avg(salary) from instructor group by dept_name having avg(salary) > 4000;

select user_id  from user group by user_id

-- 另外一种思路，把所有系的平均工资查出来，再从这个结果里面查平均工资大于4000的
select   dept_name , avg(salary) from ( select dept_name ,avg(salary)  from instructor group by dept_name ) where avg(salary) > 4000;

-- distinct支持单列、多列的去重方式

SELECT name FROM users;                 --返回一个包含重复行的结果集
SELECT DISTINCT name FROM users;        --去除重复的行
-- 需要注意的是，DISTINCT 关键字会对查询的性能产生一定的影响，因为它需要对结果集进行排序和去重的操作。因此，在使用 DISTINCT 关键字时需要谨慎，尽可能地使用索引来优化查询，以提高查询的性能。

SELECT DISTINCT columns FROM table_name WHERE where_conditions;   --DISTINCT用法
-- 按照SQL标准，DISTINCT子句将所有NULL值视为相同的值，如果列具有NULL值，并且对该列使用DISTINCT子句，MySQL将保留一个NULL值，并删除其它的NULL值。



-- 多列的去重则是根据指定的去重的列信息来进行，即只有所有指定的列信息都相同，才会被认为是重复的信息。
SELECT DISTINCT column1,column2 FROM table_name WHERE where_conditions;


-- 这条语句中的 DISTINCT 表示返回不同部门 id 和性别的组合值。
SELECT DISTINCT dept_id, sex FROM employee;

-- GROUP BY也可以达到同样的效果
SELECT dept_id, sexFROM employee GROUP BY dept_id, sex;

-- 所有列都相同，才被认为是重复的。
SELECT DISTINCT * FROM TABLE

-- 两条记录之间之后只有部分字段的值是有重复的，但是主键唯一
SELECT * FROM TABLE WHERE ID IN (SELECT MAX(ID) FROM TABLE GROUP BY [去除重复的字段名列表,....])

-- SQL语句中存在3个著名的量词，SOME ALL 和 ANY
-- ALL：只有当其所有数据都满足条件时，条件才成立
-- ANY：只要有一条数据满足条件，条件就成立
-- SOME：其中存在一些数据满足条件，作用和Any大致相同 常规的使用中看作一致即可

-- AS 更名的用处：给查询结果的列名改一个名字，给要查询的表改一个名字。尤其是在同一个表中进行比较。

-- 教师表  instructor(ID, name, dept_name, salary)
-- 找出满足下面条件的所有教师的姓名：他们的工资比 Biology 系教师的最低工资要高

-- 方法一
-- 将一个表和它自己进行笛卡尔积运算，然后再过滤
select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dep_tname='Biology';

-- >some 比较:  至少比集合中某一个值要大
-- 大于some就是：比最小的还要大
-- 小于some就是，比最大的还要小
--方法二
--等价上面的写法，使用 > some 格式判断，子查询查出 Biology 系所有的工资组成集合。再用 >some 比较
select name from instructor where salary > some (select salary from instructor where dep_tname='Biology' );



-- 找出满足下面条件的所有教师的姓名：他们的工资比 Biology 系每个教师的最高工资要高

-- 方法一：子查询中找出Biology系的最高工资
select name from instructor where salary >  (select MAX(salary) from instructor where dep_tname='Biology'  );

-- 方法二：大于所有
-- >all 比较，比集合中的所有值都大。
select name from instructor where salary > all (select salary from instructor where dep_tname='Biology' );

-- 找出平均工资最高的系

-- 按系名分组，计算每个系的平均工资，并将结果按照平均工资从高到低排序。最后，只返回排序结果中的第一行，即平均成绩最高的那个系。

SELECT
  dept_name,
  AVG(salary) AS avg_salary
FROM
  instructor
GROUP BY
  dept_name
ORDER BY
  avg_salary DESC
LIMIT
  1;


-- 方法：先查出所有系的平均工资做为一个集合，再在所有系的平均的工资里面比较，找出最大值。
select dept_name from instructor group by dept_name having avg(salary) >=all (select avg(salary) from instructor group by deptname);

-- 并集（union）

-- 并集 union 自动去重(效率很低)，将两个结果集求并集，如果想合并没有刻意要删除重复行。可以保留重复，则使用union all

-- UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔（因此，如果组合4条 SELECT 语句，将要使用3个UNION 关键字）。
-- UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
-- 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

-- 找出在2009年秋季或在2010年春季开课的课程信息
(select course_id from section where semester='Fall' and year=2009) union (select course_id from section where semester='Spring' and year=2010);

-- 也等价于下面这条查询
select course_id from section where (semester='Fall' and year=2009) or (semester='Spring' and year=2010);



-- 交集（intersect all）

-- 交集 intersect all
-- 找出在2009年秋季和2010年春季同时开课的课程信息
(select course_id from section where semester='Fall' and year=2009) intersect all (select course_id from section where semester='Spring' and year=2010);


-- 差运算 (expect)
-- EXCEPT 子句/运算符用于将两个 SELECT 语句结合在一起，并返回第一个 SELECT 语句的结果中那些不存在于第二个 SELECT 语句结果的记录
-- 找出在2009年秋季开课，2010年春季没开课的课程信息
(select course_id from section where semester='Fall' and year=2009) expect (select course_id from section where semester='Spring' and year=2010);



-- 集合成员资格测试  in ，测试元组是否是集合中的成员。集合一般是由select查出来的。用于筛出在集合中的元素。

-- 找出在2009年秋季和2010年春季同时开课的课程信息
-- in关键字用于测试关素是否是集合中的成员。对于这个例子，3个查询条件：1、秋季；2、2009学期，3、课程id必须2010年春季开课的课程id一样。
-- 使用in关键字来描述这个查询。
select course_id from section where semester='Fall' and year=2009 and course_id in (select course_id from section where semester='Spring' and year=2010);


-- 集合成员资格测试 not in ，测试元组是否是集合中的成员。用于筛出不在集合中的元素。

create table rowtocolumn (name string, subject string, result bigint);
insert into table rowtocolumn values
('张三' , '语文' , 74),
('张三' , '数学' , 83),
('张三' , '物理' , 93),
('李四' , '语文' , 74),
('李四' , '数学' , 84),
('李四' , '物理' , 94);