3. MySQL 数据表的基本操作

• 数据表是数据库的重要组成部分，每一个数据库都是由若干个数据表组成的 。

比如，在电脑中创建一个空文件夹，如果要把“Hello 普罗米修斯”存放到文件夹中，必须把它写在 Word 文档、记事本或其它能存放文本的文档中。这里的空文件夹就相当于数据库，存放文本的文档就相当于数据表。

【 1. MySQL 创建数据表 】

• 创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程。故创建数据表时必须指定数据表中每个列（字段）的名称和数据类型，如果创建多个列，要用逗号隔开。
  基本语法
  • CREATE TABLE：用于创建给定名称的表，必须拥有表CREATE的权限。
  • <表名>：指定要创建表的名称，必须符合标识符命名规则。表名称被指定为 db_name.tbl_name，以便在特定的数据库中创建表。无论是否有当前数据库，都可以通过这种方式创建。在当前数据库中创建表时，可以省略 db-name。如果使用加引号的识别名，则应对数据库和表名称分别加引号。
    例如，‘mydb’.‘mytbl’ 是合法的，但 ‘mydb.mytbl’ 不合法。
  • <表定义选项>：表创建定义，由列名（col_name）、列的定义（column_definition）以及可能的空值说明、完整性约束或表索引组成。 [表定义选项]的格式为：<列名1> <类型1> [,…] <列名n> <类型n>
  • 默认的情况是，表被创建到当前的数据库中。若表已存在、没有当前数据库或者数据库不存在，则会出现错误。

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];

• 要创建的 表的名称不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。
• 数据表属于数据库，在创建数据表之前，应使用语句“USE<数据库>”指定操作在哪个数据库中进行 ，如果没有选择数据库，就会抛出 No database selected 的错误。
• 实例
  创建员工表 tb_emp1，结构如下表所示。
  
  选择创建表的数据库 mytest，创建 tb_emp1 数据表，输入的 SQL 语句和运行结果如下所示。
  CREATE TABLE tb_emp1
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
  语句执行后，便创建了一个名称为 tb_emp1 的数据表，使用 SHOW TABLES；语句查看数据表是否创建成功，如下所示。
  

【 2. MySQL 查看表 】

2.1 查看表的属性

DESCRIBE/DESC 以表格的形式展示表属性

• 基本语法

DESCRIBE <表名>;#或 简写为DESC  <表名>

表中的字段	含义
Null	表示该列是否可以存储 NULL 值。
Key	表示该列是否已编制索引。PRI 表示该列是表主键的一部分，UNI 表示该列是 UNIQUE 索引的一部分，MUL 表示在列中某个给定值允许出现多次。
Default	表示该列是否有默认值，如果有，值是多少。
Extra	表示可以获取的与给定列有关的附加信息，如 AUTO_INCREMENT 等。

• 实例
  DESC tb_emp1;
  

SHOW CREATE TABLE 以SQL语句的形式展示表属性

• SHOW CREATE TABLE 命令会以 SQL 语句的形式来展示表信息。和 DESCRIBE 相比，SHOW CREATE TABLE 展示的内容更加丰富，它可以 查看表的存储引擎和字符编码；另外，你还可以通过 \g或者\G参数来控制展示格式 。
• 基本语法
  SHOW CREATE TABLE student;

SHOW CREATE TABLE <表名>;

• 实例
  SHOW CREATE TABLE tb_emp1;
SHOW CREATE TABLE tb_emp1\G
SHOW CREATE TABLE tb_emp1\g
  

2.2 查看表的内容

• 详情看 MySQL 数据表查询 SELECT 。

【 3. MySQL 修改数据表结构 】

• 在 MySQL 中可以使用 ALTER TABLE 语句来改变原有表的结构，例如增加或删减列、更改原有列类型、重新命名列或表等。
• 基本语法
  • 修改选项的格式：
    { ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名> }

ALTER TABLE <表名> [修改选项]

3.1 修改表名

• 基本语法：
  • TO 为可选参数，使用与否均不影响结果。

ALTER TABLE <旧表名> RENAME [TO] <新表名>；

• 实例
  将数据表 tb_temp1 改名为 tb_temp2。
  

3.2 修改表字符集

• 基本语法
  • DEFAULT 为可选参数，使用与否均不影响结果。

ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>;

• 实例
  将数据表 tb_temp2 的字符集修改为 gb2312，校对规则修改为 gb2312_chinese_ci。
  ALTER TABLE tb_temp2 CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci;
  

3.3 添加字段

• 随着 MySQL 业务的变化，可能需要在已有的表中添加新的字段，一个完整的字段包括字段名、数据类型和约束条件。

在末尾添加字段

• 这种语法格式默认在表的最后位置（最后一列的后面）添加新字段。
  基本语法：
  • <表名> 为数据表的名字；
  • <新字段名> 为所要添加的字段的名字；
  • <数据类型> 为所要添加的字段能存储数据的数据类型；
  • [约束条件] 是可选的，用来对添加的字段进行约束。

ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];

• 实例
  新建 student 数据表，再在 student 数据表末尾添加一个 INT 类型的字段 age。
  ALTER TABLE student ADD stuId INT(4);
  

在开头添加字段

• 在开头位置（第一列的前面）添加新字段，需要将 FIRST 关键字 放在语句的末尾。
  基本语法：

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;

• 实例
  在表的第一列添加 INT 类型的字段 stuId。
  ALTER TABLE student ADD stuId INT(4) FIRST;
  

在中间添加字段

• 在中间位置（指定的字段之后）添加新字段，需要将 AFTER 关键字+已存在的字段名 放在语句的末尾。基本语法：

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;

• 实例
  在 student 表中添加名为 stuno，数据类型为 INT 的字段，stuno 字段位于 name 字段的后面。
  ALTER TABLE student ADD stuno INT(11) AFTER name;
  

3.3 修改/删除字段

修改字段名称

• 基本语法：
  • 旧字段名：指修改前的字段名；
  • 新字段名：指修改后的字段名；
  • 新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；

• CHANGE 也可以只修改数据类型，实现和 MODIFY 同样的效果，方法是将 SQL 语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”。
• 实例
  使用 ALTER TABLE 修改表 tb_temp2 的结构，将 id 字段名称改为 id2，同时将数据类型变为 CHAR(30)。
  ALTER TABLE tb_temp2 CHANGE id id2 CHAR(30);
  

修改字段数据类型

• 由于不同类型的数据在机器中的存储方式及长度并不相同，修改数据类型可能会影响数据表中已有的数据记录，因此，当数据表中已经有数据时，不要轻易修改数据类型。
  基本语法：
  • 表名：指要修改数据类型的字段所在表的名称；
  • 字段名：指需要修改的字段；
  • 数据类型：指修改后字段的新数据类型。

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

• 实例
  使用 ALTER TABLE 修改表 tb_temp2 的结构，将 name 字段的数据类型由 VARCHAR(25) 修改成 VARCHAR(30)。
  ALTER TABLE tb_temp2 MODIFY name VARCHAR(30);
  

删除字段

• 删除字段是将数据表中的某个字段从表中移除。
  基本语法：
  • 字段名”指需要从表中删除的字段的名称。

ALTER TABLE <表名> DROP <字段名>;

• 实例
  使用 ALTER TABLE 修改表 tb_temp2 的结构，删除 deptID 字段。
  ALTER TABLE tb_temp2 DROP deptID;
  

3.4 删除数据表

• 对于不再需要的数据表，我们可以将其从数据库中删除。在删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。
  基本语法：
  • 表名1, 表名2, 表名3 …表示要被删除的数据表的名称。DROP TABLE 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。
  • IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS，当数据表不存在时 MySQL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告（warning）。

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]

• 用户 必须拥有执行 DROP TABLE 命令的权限，否则数据表不会被删除，表被删除时，用户在该表上的权限不会自动删除。
• 实例
  删除数据表 tb_temp2。
  DROP TABLE tb_temp2;
  

3.5 删除被其它表关联的主表

• 数据表之间经常存在外键关联的情况，这时如果直接删除父表，会破坏数据表的完整性，也会删除失败。故删除父表有以下两种方法：通常采用第2种
  ① 先删除与它关联的子表，再删除父表；但是这样会同时删除两个表中的数据。
  ② 将关联表的外键约束取消，再删除父表；适用于需要保留子表的数据，只删除父表的情况。

• 实例

  1. 创建两个关联表
     表 tb_emp4
     CREATE TABLE tb_emp4
(
id INT(11) PRIMARY KEY,
name VARCHAR(22),
location VARCHAR (50)
);
     表 tb_emp5
     CREATE TABLE tb_emp5
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CONSTRAINT fk_emp4_emp5 FOREIGN KEY (deptId) REFERENCES tb_emp4(id)
);

  2. 使用 SHOW CREATE TABLE tb_emp5\G; 命令查看表 tb_ emp5 的外键约束。
     可以看出，tb_emp5 表为子表，具有名称为 fk_emp4_emp5 的外键约束；tb_emp4 为父表，其主键 id 被子表 tb_ emp5 所关联。

  3. 直接删除被数据表 tb_emp5 关联的数据表 tb_emp4。
     可以看出，当主表在存在外键约束时，不能被直接删除。

  4. ALTER TABLE tb_emp5 DROP FOREIGN KEY fk_emp4_emp5;解除子表 tb_emp5 的外键约束，语句成功执行后，会取消表 tb_emp4 和表 tb_emp5 之间的关联关系。

  5. 解除关联关系后，DROP TABLE tb_emp4;语句直接删除父表 tb_emp4

【 4. 插入数据 INSERT 】

4.1 基本语法

• 在 MySQL 中，相对于多条 INSERT 语句进行多个插入，用单条 INSERT 语句处理多个插入更快。当使用单条 INSERT 语句插入多行数据的时候，只需要将每行数据用圆括号括起来即可。

INSERT…VALUES

• 可以向表中插入一行数据，也可以插入多行数据。
• 基本语法
  • <表名>：指定被操作的表名。
  • <列名>：指定需要插入数据的列名。若向表中的所有列插入数据，则全部的列名均可以省略，直接采用 INSERT<表名>VALUES(…) 即可。
  • VALUES 或 VALUE 子句：该子句包含要插入的数据清单。数据清单中数据的顺序要和列的顺序相对应 。

INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];

INSERT…SET

• 指定插入行中每列的值，也可以指定部分列的值； 也可以向表中插入其他表的数据。
• 基本语法
  • 用于 直接给表中的某些列指定对应的列值，即要插入的数据的列名在 SET 子句中指定，等号后面为指定的数据，而对于未指定的列，列值会指定为该列的默认值。

INSERT INTO <表名>
SET <列名1> = <值1>,<列名2> = <值2>,…

4.2 向表中的全部字段添加值

• 向表中所有字段插入值的方法有两种：一种是指定所有字段名；另一种是完全不指定字段名。
• 实例
  创建一个课程信息表 tb_courses，包含课程编号 course_id、课程名称 course_name、课程学分 course_grade 和课程备注 course_info。
  CREATE TABLE tb_courses
(
course_id INT NOT NULL AUTO_INCREMENT,
course_name CHAR(40) NOT NULL,
course_grade FLOAT NOT NULL,
course_info CHAR(100) NULL,
PRIMARY KEY(course_id)
);
  

指定所有字段名

• 插入数据时，不需要按照表定义的顺序插入，只要保证值的顺序与列字段的顺序相同就可以 。
• 实例1
  在 tb_courses 表中插入一条新记录，course_id 值为 1，course_name 值为“Network”，course_grade 值为 3，info 值为“Computer Network”。
  在插入数据时，指定了 tb_courses 表的所有字段，因此将为每一个字段插入新的值。
  INSERT INTO tb_courses
(course_id,course_name,course_grade,course_info)
VALUES(1,'Network',3,'Computer Network');
  
• 实例2
  在 tb_courses 表中插入一条新记录，course_id 值为 2，course_name 值为“Database”，course_grade 值为 3，info值为“MySQL”。
  INSERT INTO tb_courses
(course_name,course_info,course_id,course_grade)
VALUES('Database','MySQL',2,3);
  

完全不指定字段名

• 使用 INSERT 插入数据时，允许列名称列表 column_list 为空，此时值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。如果表的结构被修改，对列进行增加、删除或者位置改变操作，这些操作将使得用这种方式插入数据时的顺序也同时改变。如果指定列名称，就不会受到表结构改变的影响。
• 实例
  在 tb_courses 表中插入一条新记录，course_id 值为 3，course_name 值为“Java”，course_grade 值为 4，info 值为“Jave EE”。
  INSERT INTO tb_courses
VALUES(3,'Java',4,'Java EE');
  

4.3 向表中指定字段添加值

• 为表的指定字段插入数据，是在 INSERT 语句中 只向部分字段中插入值，而其他字段的值为表定义时的默认值。
• 实例
  在 tb_courses 表中插入一条新记录，course_name 值为“System”，course_grade 值为 3，course_info 值为“Operating System”。
  如查询结果显示，这里的 course_id 字段自动添加了一个整数值 4。这时的 course_id 字段为表的主键，不能为空，系统自动为该字段插入自增的序列值。
  INSERT INTO tb_courses
(course_name,course_grade,course_info)
VALUES('System',3,'Operation System');
  

4.4 复制表数据 INSERT INTO…FROM

• MySQL 可以快速地从一个或多个表中取出数据，并将这些数据作为行数据插入另一个表中。
  基本语法
  • SELECT 子句返回的是一个查询到的结果集；
  • INSERT 语句将这个结果集插入指定表中，结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致。

INSERT INTO…SELECT…FROM

• 实例
  1. 在数据库 test_db 中创建一个与 tb_courses 表结构相同的数据表 tb_courses_new。
     CREATE TABLE tb_courses_new
(
course_id INT NOT NULL AUTO_INCREMENT,
course_name CHAR(40) NOT NULL,
course_grade FLOAT NOT NULL,
course_info CHAR(100) NULL,
PRIMARY KEY(course_id)
);
  2. 从 tb_courses 表中查询所有的记录，并将其插入 tb_courses_new 表中。
     INSERT INTO tb_courses_new
(course_id,course_name,course_grade,course_info)
SELECT course_id,course_name,course_grade,course_info
FROM tb_courses;
     

【 5. 修改数据 UPDATE 】

• 在 MySQL 中，可以使用 UPDATE 语句 来修改、更新一个或多个表的数据。
  基本语法
  • <表名>：用于指定要更新的表名称。
  • SET 子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。
  • WHERE 子句：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。
  • ORDER BY 子句：可选项。用于限定表中的行被修改的次序。
  • LIMIT 子句：可选项。用于限定被修改的行数。
  • 修改一行数据的多个列值时，SET 子句的每个值用逗号分开即可。

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]

5.1 修改表中数据

• 实例
  在 tb_courses_new 表中，更新所有行的 course_grade 字段值为 4。
  UPDATE tb_courses_new SET course_grade=4;
  

5.2 根据条件修改表中的数据

• 实例
  在 tb_courses 表中，更新 course_id 值为 2 的记录，将 course_grade 字段值改为 3.5，将 course_name 字段值改为“DB”。
  保证 UPDATE 以 WHERE 子句结束，通过 WHERE 子句指定被更新的记录所需要满足的条件，如果忽略 WHERE 子句，MySQL 将更新表中所有的行。
  UPDATE tb_courses_new SET course_name='DB',course_grade=3.5 WHERE course_id=2;
  

【 6. 删除数据 DELETE 】

• 在 MySQL 中，可以使用 DELETE 语句 来删除表的一行或者多行数据。
  基本语法
  • <表名>：指定要删除数据的表名。
  • ORDER BY 子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。
  • WHERE 子句：可选项，表示为删除操作限定删除条件；若省略该 WHERE 子句，则代表删除该表中的所有行。
  • LIMIT 子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

6.1 删除表中的全部数据

• 实例
  删除 tb_courses_new 表中的全部数据。
  DELETE FROM tb_courses_new;
  

6.2 根据条件删除表中的数据

• 实例
  在 tb_courses_new 表中，删除 course_id 为 4 的记录。
  由运行结果可以看出，course_id 为 4 的记录已经被删除。
  DELETE FROM tb_courses WHERE course_id=4;
  

【 7. 清空表记录 TRUNCATE 】

• MySQL 提供了 DELETE 和 TRUNCATE 关键字来删除表中的数据，它们都可以用来清空表中的数据。

DELETE	TRUNCATE
DML 类型的语句。	DDL 类型的语句。
逐行一条一条删除记录。	直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 DELETE 快。因此需要删除表中全部的数据行时，尽量使用 TRUNCATE 语句， 可以缩短执行时间。
删除数据后，配合事件回滚 可以找回数据 。	不支持事务的回滚，数据删除后无法找回。
删除数据后，系统不会重新设置自增字段的计数器 。	清空表记录后，系统会重新设置自增字段的计数器。
使用范围更广，因为它 可以通过 WHERE 子句指定条件来删除部分数据 。	不支持 WHERE 子句，只能删除整体。
会返回删除数据的行数 。	只会返回 0，没有任何意义。

• TRUNCATE 关键字 用于 完全清空一个表。
  基本语法
  • 其中，TABLE 关键字可省略。

TRUNCATE [TABLE] 表名

• 实例
  1. 新建表 tb_student_course，插入数据并查询。
     CREATE TABLE tb_student_course (
id int(4) NOT NULL AUTO_INCREMENT,
name varchar(25) NOT NULL,
PRIMARY KEY (id)
);
  2. 插入数据
     INSERT INTO tb_student_course(name) VALUES ('Java'),('MySQL'),('Python');
  3. 查询表tb_student_course
     SELECT * FROM tb_student_course;
     
  4. 清空 tb_student_course 表中的记录
     TRUNCATE TABLE tb_student_course;