「MySQL基础」Day3 表操作

2019-03-01

1. 任务背景

Datawhale组队学习中MySQL一周学习任务，通过一周组队自主学习，快速熟悉MySQL相关基础知识，并能掌握基本的数据查询操作方式

1.1 任务内容

MySQL表数据类型
用SQL语句创建表
- 语句解释
- 设定列类型、大小、约束
- 设定主键
用SQL语句向表中添加数据
- 语句解释
- 多种添加方式（指定列名；不指定列名）
用SQL语句删除表
- 语句解释
- DELETE
- DROP
- TRUNCATE
- 不同方式的区别
用SQL语句修改表
- 修改列名
- 修改表中数据
- 删除行
- 删除列
- 新建列
- 新建行

2. MySQL表数据类型

2.1 数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

2.2 日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

2.3 字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

3. 用SQL语句创建表

3.1 创建表的基本语法

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition
  | {INDEX|KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
  | CHECK (expr)

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]
  | data_type
      [COLLATE collation_name]
      [GENERATED ALWAYS] AS (expr)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [reference_definition]

data_type:
    (see Chapter 11, Data Types)

key_part:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

reference_definition:
    REFERENCES tbl_name (key_part,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
    table_option [[,] table_option] ...

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 [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

query_expression:
    SELECT ...   (Some valid select or union statement)

3.2 实例

接下来创建一个测试用的学生信息表

CREATE TABLE IF NOT EXISTS `test_student` (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键id',
    no INT NOT NULL DEFAULT 0 COMMENT '学号，纯数字',
    name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名',
    gender TINYINT(2) NOT NULL DEFAULT 0 COMMENT '性别，0为男，1为女，2为其他',
    class INT NOT NULL DEFAULT 0 COMMENT '对应班级表的id',
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据修改时间',
    UNIQUE `UNIQ_NO`(`no`)
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COMMENT '学生测试表';

使用IF NOT EXISTS来表示，当表不存在的时候才会创建表
设置主键id，并使其为自增变量AUTO_INCREMENT
name使用VARCHAR类型，并最多20个字符
添加create_time和update_time，默认值都取CURRENT_TIMESTAMP，也就是当前时间的时间戳，对于update_time，设置ON UPDATE CURRENT_TIMESTAMP，使得当update数据的时候，会自动修改字段为当前时间戳
对学号字段no添加唯一索引
数据表引擎使用InnoDB，默认编码为utf8mb4
对表和字段分别使用COMMENT关键字，添加注释，便于其他人使用

3.3 其他问题

暂时不在这表述了，必要时另开笔记

4. 用SQL语句向表中添加数据

4.1 INSERT语法

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

INSERT用来将行插入（或添加）到数据库表。插入有几种方式：

插入完整的行
插入行的一部分
插入某些查询的结果

4.2 不指定列名插入行

mysql> INSERT INTO test_student VALUE (1, 100001, 'name1', 0, 12, '2019-03-01 21:00:00', '2019-03-01 21:05:00');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_student\G
*************************** 1. row ***************************
         id: 1
         no: 100001
       name: name1
     gender: 0
      class: 12
create_time: 2019-03-01 21:00:00
update_time: 2019-03-01 21:05:00
1 row in set (0.00 sec)

mysql> SELECT * FROM test_student;
+----+--------+-------+--------+-------+---------------------+---------------------+
| id | no     | name  | gender | class | create_time         | update_time         |
+----+--------+-------+--------+-------+---------------------+---------------------+
|  1 | 100001 | name1 |      0 |    12 | 2019-03-01 21:00:00 | 2019-03-01 21:05:00 |
+----+--------+-------+--------+-------+---------------------+---------------------+
1 row in set (0.00 sec)

当不指定列名时，VALUE后面内容需要列出所有列对应的值，并且需要按照表中列的顺序一一对应

4.3 指定列名插入行的一部分

mysql> INSERT INTO test_student(`no`, `name`, `class`) VALUE (100123, 'name1', 15);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test_student;
+----+--------+-------+--------+-------+---------------------+---------------------+
| id | no     | name  | gender | class | create_time         | update_time         |
+----+--------+-------+--------+-------+---------------------+---------------------+
|  1 | 100001 | name1 |      0 |    12 | 2019-03-01 21:00:00 | 2019-03-01 21:05:00 |
|  2 | 100123 | name1 |      0 |    15 | 2019-03-01 21:11:40 | 2019-03-01 21:11:40 |
+----+--------+-------+--------+-------+---------------------+---------------------+
2 rows in set (0.00 sec)

这条INSERT语句中，指定了no，name，class列，后面VALUE中的值需要对应这几列，没有指明的列会自动取默认值，如果未指定默认值则是NULL

4.4 插入多条数据

mysql> INSERT INTO test_student(`no`, `name`, `class`) VALUES (100233, 'name2', 15), (100149, 'name3', 12);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test_student;
+----+--------+-------+--------+-------+---------------------+---------------------+
| id | no     | name  | gender | class | create_time         | update_time         |
+----+--------+-------+--------+-------+---------------------+---------------------+
|  1 | 100001 | name1 |      0 |    12 | 2019-03-01 21:00:00 | 2019-03-01 21:05:00 |
|  2 | 100123 | name1 |      0 |    15 | 2019-03-01 21:11:40 | 2019-03-01 21:11:40 |
|  3 |      0 | name1 |      0 |    12 | 2019-03-01 21:16:44 | 2019-03-01 21:16:44 |
|  4 | 100233 | name2 |      0 |    15 | 2019-03-01 21:19:40 | 2019-03-01 21:19:40 |
|  5 | 100149 | name3 |      0 |    12 | 2019-03-01 21:19:40 | 2019-03-01 21:19:40 |
+----+--------+-------+--------+-------+---------------------+---------------------+
5 rows in set (0.00 sec)

此处VALUE改为VALUES，相应插入的数据直接与前面同理，用逗号分隔即可，此处一次添加了2条数据

4.5 插入检索的数据

INSERT一般用来给表插入具有指定列值的行。 INSERT还存在另一种形式，可以利用它将 SELECT语句的结果插入表中，这就是所谓的INSERT SELECT。顾名思义，它是由一条 INSERT语句和一条 SELECT语句组成的。

mysql> INSERT INTO test_student(`name`, `class`) SELECT name, class FROM test_student WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test_student;
+----+--------+-------+--------+-------+---------------------+---------------------+
| id | no     | name  | gender | class | create_time         | update_time         |
+----+--------+-------+--------+-------+---------------------+---------------------+
|  1 | 100001 | name1 |      0 |    12 | 2019-03-01 21:00:00 | 2019-03-01 21:05:00 |
|  2 | 100123 | name1 |      0 |    15 | 2019-03-01 21:11:40 | 2019-03-01 21:11:40 |
|  3 |      0 | name1 |      0 |    12 | 2019-03-01 21:16:44 | 2019-03-01 21:16:44 |
+----+--------+-------+--------+-------+---------------------+---------------------+

此时不再使用VALUE关键字，直接紧接SELECT语句，此处为了简单，直接去了id为1的数据的name和class，其他自动取默认值，同时由于id未指定，自动取自增量AUTO_INCREMENT

5. 用SQL语句删除表

5.1 DELETE语句

5.1.1 DELETE语法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

5.1.2 语句举例

mysql> DELETE FROM test_student WHERE id = 3;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_student;
+----+--------+-------+--------+-------+---------------------+---------------------+
| id | no     | name  | gender | class | create_time         | update_time         |
+----+--------+-------+--------+-------+---------------------+---------------------+
|  1 | 100001 | name1 |      0 |    12 | 2019-03-01 21:00:00 | 2019-03-01 21:05:00 |
|  2 | 100123 | name1 |      0 |    15 | 2019-03-01 21:11:40 | 2019-03-01 21:11:40 |
|  4 | 100233 | name2 |      0 |    15 | 2019-03-01 21:19:40 | 2019-03-01 21:19:40 |
|  5 | 100149 | name3 |      0 |    12 | 2019-03-01 21:19:40 | 2019-03-01 21:19:40 |
+----+--------+-------+--------+-------+---------------------+---------------------+
4 rows in set (0.00 sec)

此处删除了id为3的一条数据，DELETE用法和SELECT有点类似，也需要添加WHERE，如果不添加WHERE条件，会自动删除所有数据

mysql> DELETE FROM test_student;
Query OK, 4 rows affected (0.02 sec)

mysql> SELECT * FROM test_student;
Empty set (0.00 sec)

需要注意的是，如果表中存在外键约束，比如class列如果指向另一个班级表test_class，那么只有删除这个表中的class为12的行以后，才能删除test_class表中id为12的数据

5.2 TRUNCATE

TRUCNATE的语法很简单

1	RUNCATE [TABLE] tbl_name

可以使一个表清空，同时会还原类似AUTO_INCREMENT的值，如果只是用DELETE，其值是不会变的

mysql> INSERT INTO test_student(`no`, `name`, `class`) VALUES (100233, 'name2', 15), (100149, 'name3', 12);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test_student;
+----+--------+-------+--------+-------+---------------------+---------------------+
| id | no     | name  | gender | class | create_time         | update_time         |
+----+--------+-------+--------+-------+---------------------+---------------------+
|  6 | 100233 | name2 |      0 |    15 | 2019-03-01 21:29:27 | 2019-03-01 21:29:27 |
|  7 | 100149 | name3 |      0 |    12 | 2019-03-01 21:29:27 | 2019-03-01 21:29:27 |
+----+--------+-------+--------+-------+---------------------+---------------------+
2 rows in set (0.01 sec)

mysql> SHOW CREATE TABLE test_student\G
*************************** 1. row ***************************
       Table: test_student
Create Table: CREATE TABLE `test_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `no` int(11) NOT NULL DEFAULT '0' COMMENT '学号，纯数字',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `gender` tinyint(2) NOT NULL DEFAULT '0' COMMENT '性别，0为男，1为女，2为其他',
  `class` int(11) NOT NULL DEFAULT '0' COMMENT '对应班级表的id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_NO` (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COMMENT='学生测试表'
1 row in set (0.01 sec)

mysql> TRUNCATE TABLE test_student;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE test_student\G
*************************** 1. row ***************************
       Table: test_student
Create Table: CREATE TABLE `test_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `no` int(11) NOT NULL DEFAULT '0' COMMENT '学号，纯数字',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `gender` tinyint(2) NOT NULL DEFAULT '0' COMMENT '性别，0为男，1为女，2为其他',
  `class` int(11) NOT NULL DEFAULT '0' COMMENT '对应班级表的id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_NO` (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生测试表'
1 row in set (0.00 sec)

可以看见，如果不TRUNCATE数据，此时再次INSERT数据，id会从6开始计数，同时AUTO_INCREMENT值为8，当TRUNCATE完毕后，表会恢复之前创建的时候

5.3 DROP

5.3.1 DROP的语法

DROP有两种用法，一种是删除某个table

1
2
3

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

另一种是删除数据库

1	DROP {DATABASE \| SCHEMA} [IF EXISTS] db_name

当执行DROP TABLE后

mysql> DROP TABLE test_student;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW TABLES;
+---------------------------+
| Tables_in_datawhale_mysql |
+---------------------------+
| email                     |
| world                     |
+---------------------------+
2 rows in set (0.00 sec)

此时会看建表不存在了

6. 用SQL语句修改表

我们先重复此前建表过程和插入数据过程，使其可以使用

6.1 ALTER语句语法

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 {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name]
        (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 [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (col_name,...)
        reference_definition
  | ADD CHECK (expr)
  | ALGORITHM [=] {DEFAULT|INPLACE|COPY}
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | 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 {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

key_part:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

table_options:
    table_option [[,] table_option] ...

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 [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)

partition_options:
    (see CREATE TABLE options)

6.2 删除列

mysql> SHOW CREATE TABLE test_student\G
*************************** 1. row ***************************
       Table: test_student
Create Table: CREATE TABLE `test_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `no` int(11) NOT NULL DEFAULT '0' COMMENT '学号，纯数字',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `gender` tinyint(2) NOT NULL DEFAULT '0' COMMENT '性别，0为男，1为女，2为其他',
  `class` int(11) NOT NULL DEFAULT '0' COMMENT '对应班级表的id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_NO` (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='学生测试表'
1 row in set (0.00 sec)

mysql> ALTER TABLE test_student DROP COLUMN gender;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test_student\G
*************************** 1. row ***************************
       Table: test_student
Create Table: CREATE TABLE `test_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `no` int(11) NOT NULL DEFAULT '0' COMMENT '学号，纯数字',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `class` int(11) NOT NULL DEFAULT '0' COMMENT '对应班级表的id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据修改时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_NO` (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='学生测试表'
1 row in set (0.00 sec)

此处删除了test_student表中的gender数据

6.3 新建列

mysql> ALTER TABLE test_student ADD COLUMN gender TINYINT(2) NOT NULL DEFAULT 0 COMMENT '性别，0为男，1为女，2为其他';
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test_student\G
*************************** 1. row ***************************
       Table: test_student
Create Table: CREATE TABLE `test_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `no` int(11) NOT NULL DEFAULT '0' COMMENT '学号，纯数字',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `class` int(11) NOT NULL DEFAULT '0' COMMENT '对应班级表的id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据修改时间',
  `gender` tinyint(2) NOT NULL DEFAULT '0' COMMENT '性别，0为男，1为女，2为其他',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_NO` (`no`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='学生测试表'
1 row in set (0.00 sec)

此处重新添加了列gender，此时所有数据在gender列自动取默认值0

6.4 删除行

这个直接参考DELETE语句即可

6.5 修改列名

mysql> ALTER TABLE test_student CHANGE no number INT NOT NULL DEFAULT 0 COMMENT '学号，纯数字';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE test_student\G
*************************** 1. row ***************************
       Table: test_student
Create Table: CREATE TABLE `test_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `number` int(11) NOT NULL DEFAULT '0' COMMENT '学号，纯数字',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `class` int(11) NOT NULL DEFAULT '0' COMMENT '对应班级表的id',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '数据修改时间',
  `gender` tinyint(2) NOT NULL DEFAULT '0' COMMENT '性别，0为男，1为女，2为其他',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_NO` (`number`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='学生测试表'
1 row in set (0.00 sec)

mysql> SELECT * FROM test_student;
+----+--------+-------+-------+---------------------+---------------------+--------+
| id | number | name  | class | create_time         | update_time         | gender |
+----+--------+-------+-------+---------------------+---------------------+--------+
|  1 | 100001 | name1 |    12 | 2019-03-01 21:00:00 | 2019-03-01 21:05:00 |      0 |
|  2 | 100123 | name1 |    15 | 2019-03-01 21:36:49 | 2019-03-01 21:36:49 |      0 |
|  3 |      0 | name1 |    12 | 2019-03-01 21:36:56 | 2019-03-01 21:36:56 |      0 |
|  4 | 100233 | name2 |    15 | 2019-03-01 21:37:00 | 2019-03-01 21:37:00 |      0 |
|  5 | 100149 | name3 |    12 | 2019-03-01 21:37:00 | 2019-03-01 21:37:00 |      0 |
+----+--------+-------+-------+---------------------+---------------------+--------+
5 rows in set (0.00 sec)

此时可以看到，只是对no列的信息进行了修改，实际表中每一行的值并未发生变动，当然如果修改了列的类型，就会不一样了

6.6 修改表中数据

6.6.1 UPDATE单表语法

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

6.6.2 UPDATE语句举例

mysql> SELECT * FROM test_student;
+----+--------+-------+-------+---------------------+---------------------+--------+
| id | number | name  | class | create_time         | update_time         | gender |
+----+--------+-------+-------+---------------------+---------------------+--------+
|  1 | 100001 | name1 |    12 | 2019-03-01 21:00:00 | 2019-03-01 21:05:00 |      0 |
|  2 | 100123 | name1 |    15 | 2019-03-01 21:36:49 | 2019-03-01 21:36:49 |      0 |
|  3 |      0 | name1 |    12 | 2019-03-01 21:36:56 | 2019-03-01 21:36:56 |      0 |
|  4 | 100233 | name2 |    15 | 2019-03-01 21:37:00 | 2019-03-01 21:37:00 |      0 |
|  5 | 100149 | name3 |    12 | 2019-03-01 21:37:00 | 2019-03-01 21:37:00 |      0 |
+----+--------+-------+-------+---------------------+---------------------+--------+
5 rows in set (0.00 sec)

mysql> UPDATE test_student SET number = 123123, name = 'name5' WHERE id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM test_student;
+----+--------+-------+-------+---------------------+---------------------+--------+
| id | number | name  | class | create_time         | update_time         | gender |
+----+--------+-------+-------+---------------------+---------------------+--------+
|  1 | 100001 | name1 |    12 | 2019-03-01 21:00:00 | 2019-03-01 21:05:00 |      0 |
|  2 | 100123 | name1 |    15 | 2019-03-01 21:36:49 | 2019-03-01 21:36:49 |      0 |
|  3 | 123123 | name5 |    12 | 2019-03-01 21:36:56 | 2019-03-01 21:53:08 |      0 |
|  4 | 100233 | name2 |    15 | 2019-03-01 21:37:00 | 2019-03-01 21:37:00 |      0 |
|  5 | 100149 | name3 |    12 | 2019-03-01 21:37:00 | 2019-03-01 21:37:00 |      0 |
+----+--------+-------+-------+---------------------+---------------------+--------+
5 rows in set (0.00 sec)

这条语句对test_student表中，id为3的这一行，修改其name列的值为name5，并修改number列值为123123

6.7 新建行

这个直接参考INSERT语句即可

7. 作业部分

7.1 作业一

7.1.1 作业描述

超过5名学生的课（难度：简单）
创建如下所示的courses 表 ，有: student (学生) 和 class (课程)。
例如,表:
+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
| A      | Math       |
+---------+------------+

编写一个 SQL 查询，列出所有超过或等于5名学生的课。
应该输出:
+---------+
| class   |
+---------+
| Math    |
+---------+
Note:
学生在每个课中不应被重复计算。

7.1.2 作业思路

首先需要先建表，student是单个字母，直接用长度1的VARCHAR即可，class是个单词，目前最长字符串为8个字符，我们取16个字符长度的VARCHAR，查询语句就是个GROUP BY然后HAVING判断即可

7.1.3 作业代码

CREATE TABLE IF NOT EXISTS `courses` (
    student VARCHAR(1) NOT NULL DEFAULT '',
    class VARCHAR(16) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COMMENT 'Day3作业一课程';
INSERT INTO courses VALUES ('A', 'Math'), ('B', 'English'), ('C', 'Math'), ('D', 'Biology'), ('E', 'Math'), ('F', 'Computer'), ('G', 'Math'), ('H', 'Math'), ('I', 'Math'), ('A', 'Math');

这里用了多个插入的语句

mysql> SELECT class FROM courses GROUP BY class HAVING COUNT(student) >= 5;
+-------+
| class |
+-------+
| Math  |
+-------+
1 row in set (0.02 sec)

7.2 作业二

7.2.1 作业描述

项目四：交换工资（难度：简单）
创建一个 salary表，如下所示，有m=男性 和 f=女性的值 。
例如:
| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |

交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。
运行你所编写的查询语句之后，将会得到以下表:
| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |

7.2.2 作业思路

这个作业，首先是建表，id参考实例，还是考虑作为主键，并设置AUTO_INCREMENT，name和sex都是单字符，暂时都取长度为1的VARCHAR，salary可以取INT型

这里的sex其实用TINYINT，然后用0和1表示或许会更好

对于更新数据，由于只能有一个更新，可以考虑由CASE END语句来进行

7.2.3 作业代码

首先先是建表和插入数据

CREATE TABLE IF NOT EXISTS `salary` (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键id',
    name VARCHAR(1) NOT NULL DEFAULT '' COMMENT '姓名',
    sex VARCHAR(1) NOT NULL DEFAULT '' COMMENT '性别，2值型，m男，f女',
    salary INT NOT NULL DEFAULT 0 COMMENT '工资'
) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COMMENT 'Day3作业二工资';
INSERT INTO salary(`name`, `sex`, `salary`) VALUES ('A', 'm', 2500), ('B', 'f', 1500), ('C', 'm', 5500), ('D', 'F', 500);

修改数据后

UPDATE `salary` SET `sex` = (
    CASE `sex`
        WHEN 'f' THEN 'm'
        ELSE 'f'
    END
);

此时再次查看数据

mysql> SELECT * FROM salary;
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
|  1 | A    | f   |   2500 |
|  2 | B    | m   |   1500 |
|  3 | C    | f   |   5500 |
|  4 | D    | m   |    500 |
+----+------+-----+--------+