-- 筛选表格 student 字段 age 大于14岁的数据
SELECT * FROM student WHERE age>14;

SELECT column-list FROM table_name
    WHERE [ conditions ]
    GROUP BY column1, column2
    HAVING [ conditions ]
    ORDER BY column1, column2;

-- 按照 classno 字段对表格 student 数据进行分组，并汇总各班的学生数量（汇总结果作为表格的新的字段）
SELECT classno, COUNT(studentname) FROM student GROUP BY classno;

-- 按照 classno 对表格 student 数据进行分组，并汇总各班的学生数量，汇总结果以（默认）降序排列展示
SELECT classno, COUNT(studentname) FROM student GROUP BY classno ORDER BY classno;

-- 使用 WHERE 过滤数据再进行分组
SELECT classno,COUNT(studentname) FROM student
    WHERE classno > 2
    GROUP BY classno; -- WHERE 作用与分组前，这里先筛选 classno>2 的数据，再分组
    -- 注意，这里如果书写：WHERE COUNT(studentname) > 1 就会报错

-- 使用 HAVING 过滤分组
SELECT classno,COUNT(studentname) FROM student
    GROUP BY classno
    HAVING COUNT(studentname) > 1; -- HAVING 主要用于过滤分组，且是在分组后进行过滤
    -- 所以一般对于分组的条件过滤都用 HAVING

-- 通过 WHERE 构建匹配逻辑
SELECT * FROM company3, department WHERE company3.no = department.id;

-- 表格 employees
CREATE TABLE employees(
    id int PRIMARY KEY,
    name text,
    age int CHECK(age > 0),
    address text,
    salary numeric CHECK(salary > 0)
    );

INSERT INTO employees VALUES
    (1, '王大', 25, 'beijing', 10000),
    (2, '张三', 25, 'beijing', 10000),
    (3, '李四', 21, 'beijing', 15000),
    (4, '李二', 28, 'shenzhen', 10000),
    (5, '王五', 24, 'shanghai', 20000),
    (6, '杨三', 19, 'shanghai', 15000),
    (7, '张四', 22, 'shenzhen', 20000),
    (8, '杨四', 20, 'beijing', 10000);

-- 表格 department
CREATE TABLE department(id int,dept text,fac_id int);

INSERT INTO department VALUES
    (1, 'IT', 1),
    (2, 'Engineering', 2),
    (3, 'HR', 7),
    (10, 'Market', 10);

# 两个表格的数据

# 表格 employees
 id | name | age | address  | salary
----+------+-----+----------+--------
  1 | 王大 |  25 | beijing  |  10000
  2 | 张三 |  25 | beijing  |  10000
  3 | 李四 |  21 | beijing  |  15000
  4 | 李二 |  28 | shenzhen |  10000
  5 | 王五 |  24 | shanghai |  20000
  6 | 杨三 |  19 | shanghai |  15000
  7 | 张四 |  22 | shenzhen |  20000
  8 | 杨四 |  20 | beijing  |  10000
(8 行记录)

# 表格 deparment
 id |    dept     | fac_id
----+-------------+--------
  1 | IT          |      1
  2 | Engineering |      2
  3 | HR          |      7
 10 | Market      |     10
(4 行记录)

-- 组合两个或多个 SELECT 语句的结果，而不返回任何重复的行
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]
UNION
SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition]

# 表格 t1
 id | value1
----+--------
  1 | a
  2 | b
  3 | c
(3 行记录)

# 表格 t2，有一条数据项与表格 t1 相同
 id | value2
----+--------
  1 | a
  4 | A
  5 | B
  6 | C
(4 行记录)

-- 合并表格 t1 和 表格 t2，即将两个表格按行与行整合
SELECT * FROM t1
UNION
SELECT * FROM t2;

# 返回的组合表去除了重复数据
# 因此字段 value1 中值为 a 的数据项只有一个
 id | value1
----+--------
  6 | C
  2 | b
  5 | B
  3 | c
  1 | a
  4 | A
(6 行记录)

-- 列/字段别名
-- alias_name: 它指定分配给列的临时名称
SELECT column_name AS alias_name FROM table_name [conditions...];

-- 表别名
-- alias_name：它指定分配给表的临时名称
SELECT column1, column2.... FROM table_name AS alias_name [conditions...];

CREATE TABLE new_table_name AS
    SELECT column_name FROM table_name [conditions...];

CREATE TABLE new_table_name (LIKE table_name);

-- 每个 CTE 都需要设置别名 name_for_summary_data（它们通过别名来被使用）。
WITH name_for_summary_data AS (
   SELECT column_name FROM table_name [codition...])

SELECT columns
FROM name_for_summary_data [codition...]

-- 先筛选出 companyname 为 c1 数据并构建临时表 t
-- 临时表 t 并不会存储于数据库中
WITH t AS (
    SELECT companyname, city, client FROM w1
    WHERE companyname = 'c1')

-- 再基于临时表 t 进行数据处理
SELECT SUM(client) FROM t; -- 等价于 SELECT SUM(client) FROM w1 WHERE companyname = 'c1';

WITH t AS (
    DELETE FROM w1 WHERE client < 2000   -- 将表格 w1 中客户数小于 200 的数据项删除
    RETURNING * )                        -- 并使用 RETURNING 子句返回数据，存放在临时表格 t 中

-- 创建空表 w2 用于接收临时表格的数据
-- 其结构需要与 w1 相同
CREATE TAbLE w2(id int PRIMARY KEY, companyname text, city text, client int);

-- 将临时表格的数据插入新建的表格 w2 中
-- 实现了按条件将数据从表格 w1 移动到表格 w2 中
INSERT INTO w2(SELECT * FROM t);