SQL 备忘

/*
  在 admin.db 学习查询

  几个概念:
  database/table/column/row/...
  datatype: STRING/INT/BOOLEAN/...

  auction_house/auction/lot/work/artist/

  SQL:
  USE database;
  USE table;

  说明: 下面代码中使用全大小的为关键字.
*/

SELECT * FROM table_name;
SELECT col_name, col_name FROM table_name;
SELECT col_name as new_col_name FROM table_name;

LIMIT 10;
LIMIT 0, 10;
LIMIT 10 OFFSET 0;
-- LIMIT page_size OFFSET page_size * (page_index - 1)

ORDER BY `A` DESC, `B` ASC; -- default ASC. ascending/descending

SELECT * FROM table_name WHERE 1;
SELECT * FROM table_name WHERE 1 = 1;
SELECT * FROM table_name WHERE id = 90;
SELECT * FROM table_name WHERE price > 1000;
SELECT * FROM table_name WHERE price > 1000 AND price < 10000;
SELECT * FROM table_name WHERE price < 100 OR price > 100000;
SELECT * FROM table_name WHERE price IS NULL;
-- IS NOT NULL; <=>
-- 空值的行与=或<>运算符不匹配
SELECT * FROM table_name WHERE id IN (3, 4, 5); -- 总比多个 OR 方便.
SELECT * FROM table_name WHERE id /* NOT */ IN (
  SELECT id FROM other_table WHERE code = 101
);

SELECT * FROM table_name WHERE introduction LIKE "%abc%";  -- % 通配符, 代替任意个字符; _ 仅代替一个字符;
SELECT cny, cny < 0 as price from t1 ORDER BY cn DESC, price ASC;

SELECT t1.id, t2.name FROM table_1 as t1 JOIN table_2 as t2 ON t1.id = t2.id;
-- 别名只能应用在 GROUP BY/ORDER BY/HAVING 子句中

-- MAX/MIN/AVG/SUM/COUNT/DISTINCT
SELECT COUNT(*) FROM table_name;
SELECT DISTINCT(name) FROM table_name;
-- DISTINCT 需要放在最前面. count(distinct(col))

SELECT COUNT(*) FROM table_name GROUP BY col_name;
-- BROUP BY 支持多列 - 其实就是组合列。分组后，就不再可以使用 WHERE 条件(它主要实现单行的），所以加个 HAVING 关键字

SELECT class FROM (
  SELECT class, COUNT(DISTINCT student) AS num FROM courses
    GROUP BY class HAVING num >= 5
) AS t;

SELECT a.name AS employee FROM employee as a, employee as b
  WHERE a.managerId = b.id AND a.salary > b.salary;

SELECT weather.Id FROM weather
  JOIN weather w
    ON DATEDIFF(weather.RecordDate, w.RecordDate) = 1
      AND weather.Temperature > w.Temperature;

-- LIKE/IN/EXISTS

JOIN - INNER/LEFT/RIGHT/FULL/CROSS => ON a.id = b.id;
-- 交集
SELECT <list> FROM Ta INNER JOIN Tb ON Ta.key = Tb.key
-- Ta 全集
SELECT <LIST> FROM Ta LEFT JOIN Tb ON Ta.key = Tb.key
-- Tb 全集
SELECT <LIST> FROM Ta RIGHT JOIN Tb ON Ta.key = Tb.key
-- 在全集基础之上, 添加 where 过滤语句，可获取差集

-- Ta, Tb 并集
SELECT <LIST> FROM Ta FULL OUTER JOIN Tb ON Ta.key = Tb.key
-- 非交集
SELECT <LIST> FROM Ta FULL OUTER JOIN Tb ON Ta.key = Tb.key WHERE Ta.key IS NULL OR Tb.key IS NULL

SELECT t1.name FROM t1 INNER JOIN t2 ON t1.id = t2.id;

SELECT * FROM (
  -- 子查询
  SELECT * FROM table_name ORDER BY create_time DESC
) AS t -- 自查询名
GROUP BY t.name;

SELECT * FROM table_1
  WHERE price > (SELECT AVG(price) FROM table_1);

-- 关联自查询
SELECT product_type, product_name, sale_price
  FROM product AS p1
  WHERE sale_price > (
    SELECT AVG(sale_price) FROM product as p2
      WHERE p1.product_type = p2.product_type
        GROUP BY product_type
  );

-- 可选
SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN "The quantity is greater than 30"
    WHEN Quantity = 30 THEN "The quantity is 30"
    ELSE "The quantity is under 30"
END AS QuantityText
FROM OrderDetails;

INSERT INTO table_name (col_1, col_2) VALUES (VAL_1, VAL_2);
INSERT INTO table_name (col_1, col_2) VALUES (VAL_1, VAL_2), (VAL_11, VAL_12);
INSERT INTO table_name (col_1, col_2) SELECT col_1, col_2 FROM other_table WHERE code = 1;

DELETE FROM TABLE_NAME WHERE id = 1; --考虑软删除
DELETE FROM Person WHERE Id NOT IN (
  SELECT * FROM (
    SELECT MIN(Id) FROM Person GROUP BY Email
  ) AS P
)

UPDATE table_name SET col = value WHERE id = 1;
-- UPDATE t1, t2 set t1.col = t2.col WHERE t1.external_id = t2.id;
UPDATE TABLE_1 JOIN TABLE_2 ON TABLE_1.id = TABLE_2.ID SET TABLE_1.col = value WHERE TABLE_1.col_1 = cond AND TABLE_2.col_1 == cond_2;
UPDATE table_name SET sex = IF(sex = 'm', 'f', 'm');

-- 交换两列的值
UPDATE ARTIST SET col_a = col_b, col_b = col_a