创建和管理表

数据库的一下操作

创建数据库

1. CREATE DATABASE 数据库名
2. CREATE DATABASE 数据库名 CHARACTER SET 字符集
3. CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET 字符集

例：

sqlCREATE DATABASE IF NOT EXISTS test1 CHARACTER SET 'utf8'

使用数据库

在使用数据库前，一定要先说明使用的哪个表——USE 数据库名

• 查看当前所有的数据库

SHOW DATABASES

• 查看当前正在使用的数据库

SELEXT DATABASE()

• 查看指定数据库下的所有表

SHOW TABLES FROM 数据库名 直接写SHOW TABLES,查看的是当前使用数据库下的表

• 查看数据库的创建信息

SHOW CREATE DATABASE 数据库名

修改数据库

一般最好不要修改数据库

• 更改数据库字符集

ALTER DATABASE 数据库名 CHARACTER SET 字符集

删除数据库

DROP DATABASE IF EXISTS 数据库名

表的一些操作

创建表

创建表的时候要有创建表的权限

方式1

自己创建一个新表 语法格式：

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

对于上面的语法，其中

• 必须指定的：

• 表名
• 字段名，数据类型，长度
• 不必须指定的：

• 约束条件
• 默认值

例：

sqlCREATE TABLE IF NOT EXISTS mytable(
id INT,
sname VARCHAR(10),
birthday DATE
);
#显示表的结构
DESC mytable;

如果创建表时没有指定使用的字符集，则默认使用表所在的数据库的字符集

方式2

利用已经存在的表进行创建表，同时会导入数据 语法形式：

sqlCREATE TABLE 表名
AS
查询结果

例：

sqlCREATE TABLE IF NOT EXISTS mytable
AS
SELECT sno "学号",sname "姓名"
FROM student
WHERE sex='男';
#查看表中的内容
SELECT * FROM mytable;

我们发现查询语句中字段的别名，可以作为新创建表的字段的名。

当我们创建的表的数据来自其他数据库中，那么我们在root权限下，指明是哪个数据库的表就可以。

查看表的结构

• DESCRIBE/DESC 表名可以查看表的结构
• SHOW CREATE TABLE 表名可以查看表更详细的信息

修改表

用关键字ALTER进行实现

追加一个列

语法形式： ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [FIRST/AFTER 字段名] COLUMN可加可不加 例：

sqlALTER TABLE mytable
ADD number VARCHAR(11);
#显示表结构
DESC mytable;

默认是加到最后的。

• 添加到第一位FIRST 字段名

sqlALTER TABLE mytable
ADD sex VARCHAR(5) FIRST;
#显示表结构
DESC mytable;

• 任意位置AFTER 字段名

sqlALTER TABLE mytable
ADD age INT AFTER 姓名;
#显示表结构
DESC mytable;

修改一个列

可以修改字段的数据类型，长度，默认值和位置 对于数据类型不要修改，把一种类型改变为另一个类型会出现各种不好的情况

语法形式： ALTER TABLE 表名 MODIFY [COLUMN] 字段名1 字段类型 [DEFAULT 默认值] [FIRST|AFTRE 字段名2]

sqlALTER TABLE mytable
MODIFY sex VARCHAR(10)
AFTER age;
#显示表结构
DESC mytable;

上面的操作不仅更改了字段类型的长度，而且也调整了它的位置。

重命名一个列

语法格式： ALTER TABLE 表名 CHANGE [COLUMN] 列名 新的列名 新数据类型 例：

sqlALTER TABLE mytable
CHANGE 学号 sno VARCHAR(20);
#显示表结构
DESC mytable;

删除一个列

语法形式： ALTER TABLE 表名 DROP [COLUMN] 字段名 COLUMN可加可不加 例：

sqlALTER TABLE mytable
DROP number;

重命名表

推荐下面的方式1

• 方式1：使用关键字RENAME

语法形式：

sqlRENAME TABLE 旧表名
TO 新表名;

• 方式2：

语法形式：

sqlALTER TABLE 旧表名
RENAME [TO] 新表名
#TO可以省略

删除表

删除表会把表中的数据和内容全部删掉。 语法形式： DROP TABLE [IF EXISTS] 表1,表2……

清空表

清空表只是把表中的数据删除掉了，表的结构还在。 语法形式：

1. TRUNCATE TABLE 表名
2. DELETE FROM 表名

例1：

sql#清空前
SELECT * FROM mytable;
#清空
TRUNCATE TABLE mytable;
#清空后
SELECT * FROM mytable;
DESC mytable;

例2

sql#查看表中数据
SELECT * FROM mytest
#清空表
DELETE FROM mytest
#清空之后在查看
SELECT * FROM mytest

这两种清空表有什么差别吗

• 相同点：都是对表中数据的删除，但保留表的结构
• 不同点：

TRUNCATE TABLE：一旦执行此操作，表中数据是不能被回滚恢复的 DELETE FROM:执行此操作，表中的数据虽然也已经被删除，但是在删除之前执行SET autocommit=FALSE,然后再进行回滚是可以恢复的。

下面介绍一下COMMIT和ROLLBACK

1. COMMIT：提交数据。一旦执行，数据永久保存在数据库中
2. ROLLBACK：回滚数据。一旦执行，数据回滚到最近一次COMMIT

关于DDL和DML

1. DDL的操作一旦执行，就不可以回滚，因为它会在执行其他操作之后默认执行COMMIT操作，所以就算之前执行SET autocommit=FALSE也是没有用的。
2. DML的操作，如果在之前执行SET autocommit=FALSE是可以回滚数据的，但是，如果没有写，数据也是不能回滚的。

例：

• TRUNCATE

sqlCOMMIT

SELECT * FROM mytest
SET autocommit=FALSE

TRUNCATE TABLE mytest

ROLLBACK

SELECT * FROM mytest

删除并且进行回滚，查看表数据：结果是不能把数据回滚出来

• DELETE FROM

sqlCOMMIT

SELECT * FROM mytest
SET autocommit=FALSE
 
DELETE FROM  mytest

ROLLBACK

SELECT * FROM mytest

删除并且进行回滚，查看表数据：结果是可以把数据回滚出来

MySQL8新特性——DDL的原子化

在MySQL8.0中，InnoDB表的DDL支持事务完整性——即DDL操作要么成功要么失败后回滚

分别在5.7版本和8版本上执行该操作。

sqlCREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8;

USE test;

CREATE TABLE mytable1(
id INT,
myname VARCHAR(20)
);

SHOW TABLES;

DROP TABLE mytable1,mytable2;

SHOW TABLES;

5.7的结果 删除前，查看

删除后，查看

8.0的结果 删除前，查看

删除后，查看

对于5.7来说，虽然删除失败，但是还是把存在的表删除了。 对于8.0来说，虽然执行删除失败了，但是它是原子化的，所以把之前删除的表的信息回滚了。那么表也就没有被删除。