创建数据库
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 | # id 是唯一主键 |
插入时先使用条件查询,满足条件时才插入,不满足条件就不进行插入
1 | # 根据条件查询,满足条件时才插入 |
查询更新
查询的结果作为临时表,更新知识点下的题目数量
1 | UPDATE question_knowledge_point qkp |
使用了子查询
避免重复插入
如果是用主键 primary 或者唯一索引 unique 区分了记录的唯一性,避免重复插入记录可以使用 INSERT IGNORE INTO
。
当插入数据出现错误时,如重复数据,将不返回错误,只以警告形式返回,所以使用 ignore 请确保语句本身没有问题,否则也会被忽略掉。
1 | INSERT IGNORE INTO book(name) VALUES('MySQL Manual') |
左连接
查询所有题目及它的选项
1 | SELECT q.id, q.content, qo.id, qo.content |
内连接
内连接 JOIN 和 WHERE 等价,查询所有有选项的题目
1 | SELECT q.id, q.content, qo.id, qo.content |
可参考 https://www.cnblogs.com/eflylab/archive/2007/06/25/794278.html
分组
统计有选项的题目的选项个数
1 | SELECT q.id, count(1) |
数据量大时 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 | SELECT EXISTS ( |
建表语句
建表语句中需要有足够的注释描述每一列的作用,便于维护
1 | #------------------------------------------- |
复合主键:
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
4ALTER 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 | DROP VIEW IF EXISTS view_paper_knowledge_point; |