SQL¶

CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL UNIQUE,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);

ALTER TABLE students ADD INDEX idx_score (score); -- 增加index
ALTER TABLE students DROP COLUMN birthday;        -- 删除列

DROP TABLE students;

CREATE TABLE statistics (
    stu_id CHAR(9),
    class_id CHAR(2),
    score DOUBLE NOT NULL,
    PRIMARY KEY (stu_id) -- 表级设置主键
);

CREATE TABLE statistics (
    stu_id CHAR(9) PRIMARY KEY, -- 列级设置主键
    class_id CHAR(2),
    score DOUBLE NOT NULL,
);

CREATE TABLE statistics (
    stu_id CHAR(9),
    class_id CHAR(2),
    score DOUBLE NOT NULL,
    PRIMARY KEY (stu_id, class_id) -- 表级设置多属性主键
);

CREATE TABLE statistics (
    stu_id CHAR(9),
    sex CHAR(2) CHECK (sex IN ('男', '女'))
);

CREATE TABLE statistics (
    stu_id CHAR(9),
    stu_name CHAR(12),
    sex CHAR(2) CHECK (sex IN ('男', '女')),
    CHECK(sex='女' OR stu_name NOT LIKE 'Ms.%')
);

CREATE TABLE statistics (
    stu_id CHAR(9),
    stu_name CHAR(12),
    sex CHAR(2) CHECK (sex IN ('男', '女')),
    CONSTRAINT C1 CHECK(sex='女' OR stu_name NOT LIKE 'Ms.%')
);

ALTER TABLE statistics DROP CONSTRAINT C1;

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑（使用NEW和OLD访问数据）
END;

SHOW TRIGGERS FROM database_name;
-- 或查询information_schema
SELECT * FROM information_schema.triggers 
WHERE trigger_schema = 'database_name';

DROP TRIGGER [IF EXISTS] trigger_name;

CREATE INDEX index_name ON table_name (column1, column2, ...);

-- MySQL 5.7 引入
ALTER TABLE RENAME INDEX old_index_name TO new_index_name

DROP INDEX index_name;

INSERT 
INTO students (class_id, name, gender, score) 
VALUES (2, '大牛', 'M', 80);

DELETE FROM students WHERE id=1;

UPDATE students SET name='大牛', score=66 WHERE id=1;

SELECT * FROM students WHERE score >= 80;

CREATE VIEW 视图名 AS SELECT 查询语句;

DROP VIEW 视图名;

SELECT * FROM 视图名;

-- 修改视图
UPDATE 视图名 SET 列名 = 新值 WHERE 条件;

-- 删除数据
DELETE FROM 视图名 WHERE 条件;

-- 插入数据
INSERT INTO 视图名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);

WITH [RECURSIVE]
    cte_name [(col_name [, col_name] ...)] AS (subquery)
    [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

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;

SELECT
    -- 查询选项
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    -- 可以多个表达式
    select_expr [, select_expr] ... 
    [into_option]
    -- FROM子句
    [FROM table_references
      [PARTITION partition_list]]
    -- WHERE子句
    [WHERE where_condition]
    -- GROUP子句
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    -- HAVING字句
    [HAVING where_condition]
    -- 窗口函数
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    -- ORDER字句
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    -- 输出限制
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    -- 锁
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

-- 单表
SELECT * FROM t1
SELECT id, t1.* FROM t1
SELECT AVG(score), t1.* FROM t1
-- 多表
SELECT t1.*, t2.* FROM t1 INNER JOIN t2

SELECT CONCAT(last_name,', ',first_name) AS full_name
  FROM mytable ORDER BY full_name;
-- 等同于
SELECT CONCAT(last_name,', ',first_name) full_name
  FROM mytable ORDER BY full_name;

COUNT()
SUM()
AVG()
MAX()
MIN()
GROUP_CONCAT()
STDDEV()
STDDEV_POP()
STDDEV_SAMP()
VARIANCE()
VAR_POP()
VAR_SAMP()
BIT_AND()
BIT_OR()
BIT_XOR()
JSON_ARRAYAGG()
JSON_OBJECTAGG()

-- 数学函数
ABS()
CEIL()
FLOOR()
ROUND()
MOD()
POW()
SQRT()
EXP()
LOG()
RAND()
TRUNCATE()

-- 字符串函数
CONCAT()
CONCAT_WS()
LENGTH()
CHAR_LENGTH()
SUBSTRING()
LEFT()
RIGHT()
LOWER()
UPPER()
TRIM()
REPLACE()
LOCATE()
INSTR()
LPAD()
RPAD()
REVERSE()

-- 日期和时间函数
NOW()
CURDATE()
CURTIME()
DATE()
TIME()
YEAR()
MONTH()
DAY()
HOUR()
MINUTE()
SECOND()
DATE_ADD()
DATE_SUB()
TIMESTAMPDIFF()
TIMESTAMPADD()
STR_TO_DATE()
DATE_FORMAT()

-- 控制流函数
IF()
IFNULL()
NULLIF()
COALESCE()

-- 加密和哈希函数
MD5()
SHA1()
SHA2()
PASSWORD()
UUID()

-- JSON 函数
JSON_EXTRACT()
JSON_UNQUOTE()
JSON_OBJECT()
JSON_ARRAY()
JSON_CONTAINS()
JSON_LENGTH()

-- 其他实用函数
VERSION()
DATABASE()
USER()
CONNECTION_ID()
SESSION_USER()
CURRENT_USER()

CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

over_clause:
    {OVER (window_spec) | OVER window_name}

WINDOW window_name AS (window_spec)
    [, window_name AS (window_spec)] ...

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

partition_clause:
    PARTITION BY expr [, expr] ...

order_clause:
    ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...

frame_clause:
    frame_units frame_extent

frame_units:
    {ROWS | RANGE}

frame_extent:
    {frame_start | frame_between}

frame_between:
    BETWEEN frame_start AND frame_end

frame_start, frame_end: {
    CURRENT ROW
  | UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | expr PRECEDING
  | expr FOLLOWING
}

SELECT 1 + 1 FROM DUAL;
-- 等同于
SELECT 1 + 1;

joined_table: {
    table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
}
join_specification: {
    ON search_condition
  | USING (join_column_list)
}

-- 从子查询中查询
SELECT ... FROM (subquery) [AS] tbl_name ...
-- JSON_TABLE也会生成一个表，可以进行查询
SELECT * FROM JSON_TABLE(arg_list) [AS] tbl_name ...
-- 可以指定列名
SELECT ... FROM (subquery) [AS] tbl_name (col_list) ...

expr:
    expr OR expr
  | expr || expr
  | expr XOR expr
  | expr AND expr
  | expr && expr
  | NOT expr
  | ! expr
  | boolean_primary IS [NOT] {TRUE | FALSE | UNKNOWN}
  | boolean_primary

boolean_primary:
    boolean_primary IS [NOT] NULL
  | boolean_primary <=> predicate
  | boolean_primary comparison_operator predicate
  | boolean_primary comparison_operator {ALL | ANY} (subquery)
  | predicate

comparison_operator: = | >= | > | <= | < | <> | !=

predicate:
    bit_expr [NOT] IN (subquery)
  | bit_expr [NOT] IN (expr [, expr] ...)
  | bit_expr [NOT] BETWEEN bit_expr AND predicate
  | bit_expr SOUNDS LIKE bit_expr
  | bit_expr [NOT] LIKE simple_expr [ESCAPE simple_expr]
  | bit_expr [NOT] REGEXP bit_expr
  | bit_expr

bit_expr:
    bit_expr | bit_expr
  | bit_expr & bit_expr
  | bit_expr << bit_expr
  | bit_expr >> bit_expr
  | bit_expr + bit_expr
  | bit_expr - bit_expr
  | bit_expr * bit_expr
  | bit_expr / bit_expr
  | bit_expr DIV bit_expr
  | bit_expr MOD bit_expr
  | bit_expr % bit_expr
  | bit_expr ^ bit_expr
  | bit_expr + interval_expr
  | bit_expr - interval_expr
  | simple_expr

simple_expr:
    literal
  | identifier
  | function_call
  | simple_expr COLLATE collation_name
  | param_marker
  | variable
  | simple_expr || simple_expr
  | + simple_expr
  | - simple_expr
  | ~ simple_expr
  | ! simple_expr
  | BINARY simple_expr
  | (expr [, expr] ...)
  | ROW (expr, expr [, expr] ...)
  | (subquery)
  | EXISTS (subquery)
  | {identifier expr}
  | match_expr
  | case_expr
  | interval_expr

SELECT * FROM t LIMIT 3;
-- 等同于
SELECT * FROM t LIMIT 0, 3;

START TRANSACTION;
-- 加锁查询
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- 执行更新操作（如转账）
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

START TRANSACTION;
SELECT * FROM products WHERE stock > 0 FOR SHARE;
-- 其他事务可以同时加共享锁，但不能修改库存
COMMIT;

SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
FOR UPDATE OF orders;
-- 仅锁定orders表的行，customers表不受影响

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

-- 在FROM之前
SELECT * INTO @myvar FROM t1;
-- 在FROM之后，加锁之前
SELECT * FROM t1 INTO @myvar FOR UPDATE;
-- 在加锁之后
SELECT * FROM t1 FOR UPDATE INTO @myvar;

query_block [set_op query_block] [set_op query_block] ...

query_block:
    SELECT | TABLE | VALUES

set_op:
    UNION | INTERSECT | EXCEPT