创建和管理表

1. 基础知识

为从系统架构的层次上看，MySQL 数据库系统从大到小依次是 数据库服务器 、 数据库 、 数据表 、 行与列

2. 创建和管理数据库

2.1 创建数据库

CREATE DATABASE 数据库名 IF NOT EXISTS [CHARACTER SET 字符集];

2.2 使用数据库

SHOW DATABASES; # 查看当前所有数据库
SELECT DATABASE(); # 查看当前正在使用的数据库
SHOW TABLES FROM 数据库名; # 查看指定库下所有的表
SHOW CREATE DATABASE 数据库名; # 查看数据库的创建信息
USE DATABASE; # 使用/切换数据库

2.3 修改数据库

ALTER DATABASE 数据库名 CHARACTER SET 字符集; #更改字符集 比如：gbk、utf8等

2.4 删除数据库

DROP DATABASE IF EXISTS 数据库名;

3. 创建表

3.1 创建表

• 方法1

CREATE TABLE IF NOT EXISTS 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);

• 方法2
  • 使用 AS ，将创建表和插入数据结起来

CREATE TABLE emp1 AS SELECT * FROM employees;
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建emp1表结构的空表

3.2 查看表结构

DESC 表名;
SHOW CREATE 表名;

4. 修改表

• 指的是修改表的数据结构

4.1 追加一个列

ALTER TABLE 表名 ADD 字段名 字段类型 【FIRST|AFTER 字段名】;

4.2 修改一个列

可以修改列的数据类型，长度、默认值和位置

ALTER TABLE 表名 MODIFY 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;

对默认值的修改只影响今后对表的修改

4.3 重命名一个列

LTER TABLE 表名 CHANGE 列名 新列名 新数据类型;

4.4 删除一个列

ALTER TABLE 表名 DROP 字段名;

5. 重命名表

RENAME TABLE emp TO myemp;
# 或
ALTER table dept RENAME  detail_dept; 

6. 删除表

• 当一张数据表 没有与其他任何数据表形成关联关系 时，可以将当前数据表直接删除。
• 数据和结构都被删除
• 所有正在运行的相关事务被提交
• 所有相关索引被删除

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

8. 清空表

TRUNCATE TABLE 表名;

TRUNCATE 不能回滚， DELETE 可以

SET autocommit = FALSE;
DELETE FROM emp2;
#TRUNCATE TABLE emp2;
SELECT * FROM emp2;
ROLLBACK;
SELECT * FROM emp2;

阿里开发规范： 【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

拓展 DDL 的原子化

在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即 DDL操作要么成功要么回滚 。

• 例子：DROP TABLE book1,book2;
  • 删除两张表，但其中一张不存在
  • 删除失败

---

增删改

1. 插入数据

1.1 VALUES 方法添加

• 为所有字段按默认顺序插入，值列表需和字段一一对应

INSERT INTO 表名 VALUES (value1,value2,....);

• 为指定字段插入

INSERT INTO 表名(column1 [, column2, …, columnn]) VALUES (value1 [,value2, …, valuen]);

• 同时插入多条(快，建议使用)

INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

• 使用INSERT同时插入多条记录时，会返回一些单行插入时没有的信息：
  • Records：表明插入的记录条数。
  • Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
  • Warnings：表明有问题的数据值，例如发生数据类型转换。

1.2 将查询结果插入

• 子查询中的值列表应与 INSERT 子句中的列名对应。

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

2. 更新数据

• 可以一次更新多条数据。

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

3. 删除数据

DELETE FROM table_name [WHERE <condition>];

4. MySQL8 新特性： 计算列

CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

只需插入 A、B， C自动计算而来