MySQL 系列教程（笔记）

# 创建数据库对象 DDL

Data Definition language

常用的数据库定义有：

  • 建立/修改/删除数据库：create/alter/drop database

  • 建立/修改/删除表：create/alter/drop table

  • 建立/删除索引：create/drop index

  • 清空表：truncate table

    相当于 drop table + create table 所以是 DDL 命令

  • 重命名表：rename table

    还可以在同一个实例的不同数据库之间移动表

  • 建立/修改/删除视图：create/alter/drop view

官网 DDL 文档地址

# 数据库操作

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name			-- 字符集
  | [DEFAULT] COLLATE [=] collation_name					-- 字符集排序

-- 修改数据库
ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...
    
alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

-- 删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 创建数据库
-- 不使用其他参数，则使用默认的
CREATE DATABASE imc_db;
1
2
3

# 表操作

# 创建表

创建表的语法很多，下面讲解简单的，完整的语法请参考官网文档

-- TEMPORARY 临时表，只能当前线程可见，线程退出，表也消失
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)				-- 表定义
    [table_options]
    
create_definition:
    col_name column_definition		-- 列命令和列定义，索引
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (key_part,...) 
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...) reference_definition
  | CHECK (expr)

-- 列定义
column_definition:
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]	-- 数据类型
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY] -- 主键、唯一索引等
      [COMMENT 'string']		-- 备注信息
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [reference_definition]
  | data_type [GENERATED ALWAYS] AS (expression)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      
-- 表的选项
table_option:
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA|INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name
  | UNION [=] (tbl_name[,tbl_name]...)
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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

可以看到创建表的语法是很复杂的。这里只是一些基础的语法

# 修改表

在业务发展或开发中，会常常进行数据列的新增或删除，数据类型的修改调整等

ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]
    
alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition		-- 新增一列
        [FIRST | AFTER col_name]		-- 指定新列在表中的位置
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (col_name,...)
        reference_definition
  | ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}  -- 修改列默认值
  | ALTER INDEX index_name {VISIBLE | INVISIBLE}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition  -- 修改列名并修改列定义
        [FIRST|AFTER col_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | {DISABLE|ENABLE} KEYS
  | {DISCARD|IMPORT} TABLESPACE
  | DROP [COLUMN] col_name	-- 删除列，数据也会同时被删除
  | DROP {INDEX|KEY} index_name
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | FORCE
  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
  | MODIFY [COLUMN] col_name column_definition  -- 在不修改列名的情况下，修改列的定义
        [FIRST | AFTER col_name]
  | ORDER BY col_name [, col_name] ...
  | RENAME COLUMN old_col_name TO new_col_name
  | RENAME {INDEX|KEY} old_index_name TO new_index_name
  | RENAME [TO|AS] new_tbl_name
  | {WITHOUT|WITH} VALIDATION
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | DISCARD PARTITION {partition_names | ALL} TABLESPACE
  | IMPORT PARTITION {partition_names | ALL} TABLESPACE
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | REMOVE PARTITIONING
  | UPGRADE PARTITIONING
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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

# 删除表

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...   -- 可以删除多张表，用逗号分隔
    [RESTRICT | CASCADE]
1
2
3

# 建立所有的项目表

-- 课程表
CREATE TABLE `imc_course` (
	`course_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程ID',
	`title` VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '课程主标题',
	`title_desc` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '课程副标题',
	`type_id` SMALLINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程方向ID',
	`class_id` SMALLINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程分类ID',
	`level_id` SMALLINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程难度ID',
	`online_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '课程上线时间',
	`study_cnt` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '学习人数',
	`course_time` time NOT NULL DEFAULT '00:00' COMMENT '课程时长',
	`intro` VARCHAR ( 200 ) NOT NULL DEFAULT '' COMMENT '课程简介',
	`info` VARCHAR ( 200 ) NOT NULL DEFAULT '' COMMENT '学习需知',
	`harvest` VARCHAR ( 200 ) NOT NULL DEFAULT '' COMMENT '课程收获',
	`user_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '讲师ID',
	`main_pic` VARCHAR ( 200 ) NOT NULL DEFAULT '' COMMENT '课程主图片',
	`content_score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '内容评分',
	`level_score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '简单易懂',
	`logic_score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '逻辑清晰',
	`score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '综合评分',
	PRIMARY KEY ( `course_id` ),
UNIQUE KEY `udx_title` ( `title` ) 
) COMMENT = '课程主表';
-- 这里为了优化查询性能，把列定义为了非空属性，同时使用了默认值
-- 这是为了以后使用索引打下基础，因为索引对空不支持

-- 章节表
CREATE TABLE `imc_chapter` (
	`chapter_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '章节ID',
	`course_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程ID',
	`chapter_name` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '章节名称',
	`chapter_info` VARCHAR ( 200 ) NOT NULL DEFAULT '' COMMENT '章节说明',
	`chapter_no` TINYINT ( 2 ) UNSIGNED ZEROFILL NOT NULL DEFAULT '00' COMMENT '章节编号',
	PRIMARY KEY ( `chapter_id` ),
UNIQUE KEY `udx_couseid` ( `course_id`, `chapter_name` ) 
) COMMENT = '课程章节';
-- UNIQUE KEY 定义了一个联合索引

-- 小节表
CREATE TABLE `imc_subsection` (
	`sub_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '小节ID',
	`chapter_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '章节ID',
	`course_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程ID',
	`sub_name` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '小节名称',
	`sub_url` VARCHAR ( 200 ) NOT NULL DEFAULT '' COMMENT '小节URL',
	`video_type` enum ( 'avi', 'mp4', 'mpeg' ) NOT NULL DEFAULT 'mp4' COMMENT '视频格式',
	`sub_time` time NOT NULL DEFAULT '00:00:00' COMMENT '小节时长',
	`sub_no` TINYINT ( 2 ) UNSIGNED ZEROFILL NOT NULL DEFAULT '00' COMMENT '章节编号',
	PRIMARY KEY ( `sub_id` ),
UNIQUE KEY `udx_chapterid_courseid_subname` ( `chapter_id`, `course_id`, `sub_name` ) 
) COMMENT = '课程小节表';


-- 课程分类表
CREATE TABLE `imc_class` (
	`class_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程分类ID',
	`class_name` VARCHAR ( 10 ) NOT NULL DEFAULT '' COMMENT '分类名称',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
PRIMARY KEY ( `class_id` ) 
) COMMENT = '课程分类表';
-- CURRENT_TIMESTAMP 以当前时间作为添加时间的默认值

-- 课程方向表
CREATE TABLE `imc_type` (
	`type_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程方向ID',
	`type_name` VARCHAR ( 10 ) NOT NULL DEFAULT '' COMMENT '课程方向名称',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '填加时间',
PRIMARY KEY ( `type_id` ) 
) COMMENT = '课程方向表';

-- 课程难度表
CREATE TABLE `imc_level` (
	`level_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程难度ID',
	`level_name` VARCHAR ( 10 ) NOT NULL DEFAULT '' COMMENT '课程难度名称',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '填加时间',
PRIMARY KEY ( `level_id` ) 
) COMMENT = '课程方向表';

-- 用户表
CREATE TABLE `imc_user` (
	`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
	`user_nick` VARCHAR ( 20 ) NOT NULL DEFAULT '慕课网' COMMENT '用户昵称',
	`user_pwd` CHAR ( 32 ) NOT NULL DEFAULT '' COMMENT '密码',
	`sex` CHAR ( 2 ) NOT NULL DEFAULT '未知' COMMENT '性别',
	`province` VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '省',
	`city` VARCHAR ( 20 ) NOT NULL DEFAULT '' COMMENT '市',
	`Position` VARCHAR ( 10 ) NOT NULL DEFAULT '未知' COMMENT '职位',
	`mem` VARCHAR ( 100 ) NOT NULL DEFAULT '' COMMENT '说明',
	`exp_cnt` MEDIUMINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '经验值',
	`score` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '积分',
	`follow_cnt` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '关注人数',
	`fans_cnt` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '粉丝人数',
	`is_teacher` TINYINT UNSIGNED NOT NULL DEFAULT '0' COMMENT '讲师标识,0:普通用户,1:讲师用户',
	`reg_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
	`user_status` TINYINT UNSIGNED NOT NULL DEFAULT '1' COMMENT '用户状态 1:正常 0:冻结',
	PRIMARY KEY ( `user_id` ),
UNIQUE KEY `udx_usernick` ( `user_nick` ) 
) COMMENT = '用户表';

-- 问答评论表
CREATE TABLE `imc_question` (
	`quest_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '评论',
	`user_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '用户ID',
	`course_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程ID',
	`chapter_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '章节ID',
	`sub_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '小节ID',
	`replyid` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '父评论ID',
	`quest_title` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '评论标题',
	`quest_content` text COMMENT '评论内容',
	`quest_type` enum ( '问答', '评论' ) NOT NULL DEFAULT '评论' COMMENT '评论类型',
	`view_cnt` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '浏览量',
	`add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
PRIMARY KEY ( `quest_id` ) 
) COMMENT = '问答评论表';

-- 用户笔记表
CREATE TABLE `imc_note` (
	`note_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '笔记ID',
	`user_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '用户ID',
	`course_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程ID',
	`chapter_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '章节ID',
	`sub_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '小节ID',
	`note_title` VARCHAR ( 50 ) NOT NULL DEFAULT '' COMMENT '笔记标题',
	`note_content` text COMMENT '评论内容',
	`add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
PRIMARY KEY ( `note_id` ) 
) COMMENT = '笔记表';

-- 课程评价表
CREATE TABLE `imc_classvalue` (
	`value_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '评价ID',
	`user_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '用户ID',
	`course_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程ID',
	`content_score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '内容评分',
	`level_score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '简单易懂',
	`logic_score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '逻辑清晰',
	`score` DECIMAL ( 3, 1 ) NOT NULL DEFAULT '0.0' COMMENT '综合评分',
	`add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间',
PRIMARY KEY ( `value_id` ) 
) COMMENT = '课程评价表';

-- 选课表
CREATE TABLE `imc_selectcourse` (
	`select_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '选课ID',
	`user_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '用户ID',
	`course_id` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '课程ID',
	`select_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '选课时间',
	`study_time` time NOT NULL DEFAULT '00:00:00' COMMENT '累积听课时间',
PRIMARY KEY ( `select_id` ) 
) COMMENT = '用户选课表';
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
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150

把以上建表语句，创建在 imc_de 数据库中

# 索引维护语句

在创建表的时候，可以创建索引语句，在创建表之后，往往在业务发展中，或则调优中，也需要进行索引的维护

-- 创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}

index_type:
    USING {BTREE | HASH}

-- 删除索引
DROP INDEX index_name ON tbl_name
    [algorithm_option | lock_option] ...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

本章讲解怎么维护索引，下章讲解怎么使用索引

# 其他的 DDL 语句

前面讲解的是常用的 DDL 语句，再介绍一些

  • 清空表：TRUNCATE TABLE 表

    采用先删除表，再重建表的方式，且不记录二进制日志，所以速度很快

  • 重命名表：RENAME TABLE imc_note TO bak_imc_note;

← 高阶技能：DML & 常用函数 →