Content Table
2020-08-30

常用 SQL

创建数据库

1
CREATE DATABASE ebag DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

增加新列

1
ALTER TABLE table_name ADD COLUMN column_name varchar(64)

分页查询

MySQL 中使用 LIMIT 进行分页，第一个参数是起始位置 offset，从 0 开始，第二个参数是要取多少条记录

1
SELECT * FROM question WHERE subject_code='XXX' LIMIT 0, 30

插入更新

把插入和更新合并为一条语句，业务逻辑层不需要先访问数据库判断数据是否是存在再决定执行插入或者更新，直接调用这个 SQL 即可。

查看 UNIQUE 索引或 PRIMARY KEY 对应的行是否存在，存在则更新 (执行 ON DUPLICATE KEY UPDATE 后面的语句)，不存在则插入新行

1
2
3
4
# id 是唯一主键
INSERT INTO question (id, type, content) VALUES (#{id}, #{type}, #{content})
ON DUPLICATE KEY 
UPDATE content=#{content}

插入时先使用条件查询，满足条件时才插入，不满足条件就不进行插入

1
2
3
4
5
6
7
8
# 根据条件查询，满足条件时才插入
INSERT INTO paper_knowledge_point_relation(paper_id, knowledge_point_id, tenant_code)
SELECT #{paperId}, #{knowledgePointId}, #{tenantCode}
FROM   dual
WHERE NOT EXISTS(
    SELECT 1 FROM paper_knowledge_point_relation
    WHERE paper_id=#{paperId} AND knowledge_point_id=#{knowledgePointId} AND tenant_code=#{tenantCode}
)

查询更新

查询的结果作为临时表，更新知识点下的题目数量

1
2
3
UPDATE question_knowledge_point qkp
JOIN   (SELECT knowledge_point_id AS id, COUNT(id) AS count FROM question GROUP BY knowledge_point_id) AS t ON qkp.id=t.id
SET    qkp.count=t.count

使用了子查询

避免重复插入

如果是用主键 primary 或者唯一索引 unique 区分了记录的唯一性，避免重复插入记录可以使用 INSERT IGNORE INTO。

当插入数据出现错误时，如重复数据，将不返回错误，只以警告形式返回，所以使用 ignore 请确保语句本身没有问题，否则也会被忽略掉。

1
INSERT IGNORE INTO book(name) VALUES('MySQL Manual')

左连接

查询所有题目及它的选项

1
2
3
SELECT q.id, q.content, qo.id, qo.content
FROM   question q
LEFT JOIN question_option qo ON q.id=qo.question_id

内连接

内连接 JOIN 和 WHERE 等价，查询所有有选项的题目

1
2
3
4
5
6
7
SELECT q.id, q.content, qo.id, qo.content
FROM   question q
JOIN   question_option qo ON q.id=qo.question_id

SELECT q.id, q.content, qo.id, qo.content
FROM   question q, question_option qo
WHERE  q.id=qo.question_id

可参考 https://www.cnblogs.com/eflylab/archive/2007/06/25/794278.html

分组

统计有选项的题目的选项个数

1
2
3
4
SELECT q.id, count(1)
FROM   question q
JOIN   question_option qo ON q.id=qo.question_id
GROUP BY q.id

数据量大时 JOIN 比 LEFT JOIN 快很多

使用 HAVING 进行聚合条件判断

类型转换

使用 CAST 转换类型

1
SELECT CAST(id AS CHAR) AS id FROM question

切换 0 和 1

如果是 0 则设置为 1，否则设置为 0，对于切换 true 和 false 很有用，很像三元运算符

1
UPDATE demo SET marked=IF(marked=0, 1, 0)

字符串连接

使用 CONCAT(p1, p2, p3) 连接字符串

1
SELECT CONCAT(subject_code, '-', original_id) FROM question WHERE marked=1

返回布尔值

JDBC 标准中，0 表示 false，1 表示 true，大于 1 和小于 0 的数没有定义，MySQL 的 JDBC Driver 中 <=0 表示 false，>=1 表示 true，为了保险起见，使用 EXISTS 来查询返回布尔值

1
2
3
SELECT EXISTS (
    SELECT 1 FROM paper WHERE paper_id=#{paperId}
)

建表语句

建表语句中需要有足够的注释描述每一列的作用，便于维护

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#-------------------------------------------
# 表名：question
# 作者：公孙二狗
# 日期：2018-04-01
# 版本：1.0
# 描述：保存题目
#------------------------------------------
DROP TABLE IF EXISTS question;

CREATE TABLE question (
    id bigint(20) unsigned NOT NULL COMMENT '题目 ID',
    type varchar(8) DEFAULT ''      COMMENT '题目类型',
    content mediumtext              COMMENT '题目内容：题干+选项',
    analysis mediumtext             COMMENT '题目解析',
    answer text                     COMMENT '题目答案',
    demand varchar(32) DEFAULT ''   COMMENT '教学要求',
    score double DEFAULT 0          COMMENT '题目分值',
    difficulty int(11) DEFAULT 0    COMMENT '题目难度',
    original_id varchar(64) DEFAULT  ''        COMMENT '题目在乐教乐学数据库中的 ID',
    subject_code varchar(64) DEFAULT ''        COMMENT '题目的科目编码',
    knowledge_point_code varchar(8) DEFAULT '' COMMENT '题目的知识点编码',
    knowledge_point_id bigint(20)   DEFAULT 0  COMMENT '题目的知识点 ID',
    marked tinyint(4) DEFAULT 0                COMMENT '是否被标记过，0 为未标记，1 为已标记',
    created_at datetime  NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB COMMENT '存储题目的表';

复合主键: PRIMARY KEY (code, type)，因为用 id 作为主键，所以不推荐使用复合主键，可以使用多列建立唯一约束。

1
id int(11) PRIMARY KEY AUTO_INCREMENT COMMENT '自增 ID'

唯一约束

UNIQUE 约束唯一标识数据库表中的每条记录。

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

  • 唯一约束

    1
    2
    3
    4
    ALTER TABLE table_name ADD CONSTRAINT dict_identifier UNIQUE(code, type)

    建表语句中:
    UNIQUE KEY idx_user_vote (user_id, vote_item_id) COMMENT '同一个用户对一个选项只能投票一次'

  • 删除约束

    1
    ALTER TABLE table_name DROP CONSTRAINT dict_identifier

添加索引

  • 唯一索引

    1
    ALTER TABLE table_name ADD UNIQUE (`column`)

  • 普通索引

    1
    ALTER TABLE table_name ADD INDEX index_name (`column`)

  • 多列索引

    1
    ALTER TABLE table_name ADD INDEX index_name (`column1`, `column2`, `column3`)

  • 建表时用 KEY 创建索引

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    #-------------------------------------------
    # 表名：dict
    # 作者：二狗
    # 日期：2018-03-07
    # 版本：1.0
    # 描述：保存字典数据
    #------------------------------------------
    CREATE TABLE dict (
        id    bigint(20) unsigned NOT NULL COMMENT '字典的 ID',
        code  varchar(128) NOT NULL        COMMENT '字典的编码',
        value varchar(256) NOT NULL        COMMENT '字典的值',
        type  varchar(128) NOT NULL        COMMENT '字典的类型',
        description text                   COMMENT '字典的描述',
        PRIMARY KEY (id),
        UNIQUE KEY index_dict_identifier (code, type) COMMENT 'code + type 唯一标记一个字典数据',
        KEY index_type (type) COMMENT '类型建立索引'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除索引

1
ALTER TABLE table_name DROP INDEX index_name

创建视图

1
2
3
4
5
6
7
8
9
10
11
DROP VIEW IF EXISTS view_paper_knowledge_point;

CREATE VIEW view_paper_knowledge_point
AS SELECT
    pkpr.paper_id AS paper_id,
    kp.knowledge_point_id AS knowledge_point_id,
    kp.name AS name,
    kp.tenant_code AS tenant_code
FROM paper_knowledge_point_relation pkpr
LEFT JOIN knowledge_point kp ON pkpr.knowledge_point_id = kp.knowledge_point_id
WHERE kp.deleted=0;