Mysql学习总结七：创建和管理数据库、创建表、修改表、删除表

1、创建和管理数据库

1.1 创建数据库

判断数据库是否已经存在,不存在则创建数据库：

CREATE DATABASE IF NOT EXISTS 数据库名;

1.2 使用数据库

查看当前所有的数据库：

SHOW DATABASES; #有一个S,代表多个数据库

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

SELECT DATABASE();  #使用的一个 mysql 中的全局函数

查看指定库下所有的表：

SHOW TABLES FROM 数据库名;

查看数据库的创建信息：

SHOW CREATE DATABASE 数据库名;

使用/切换数据库：

USE 数据库名;

1.3 修改数据库

更改数据库字符集：

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

1.4 删除数据库

删除指定的数据库：

DROP DATABASE IF EXISTS 数据库名;

2、创建表

2.1 创建方式1:自定义创建

必须具备：

• CREATE TABLE权限
• 存储空间
  语法格式如下：

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

2.2 创建方式2：使用已有表创建

• 使用 AS subquery 选项,将创建表和插入数据结合起来
• 指定的列和子查询中的列要一一对应
• 通过列名和默认值定义列

eg：把员工表employees备份一份：

CREATE TABLE employees_backup AS SELECT * FROM employees;

2.3 查看数据表结构

在MySQL中创建好数据表之后,可以查看数据表的结构。MySQL支持使用 DESCRIBE/DESC 语句查看数据表结构,也支持使用 SHOW CREATE TABLE 语句查看数据表结构。

SHOW CREATE TABLE 表名\G

使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句,还可以查看存储引擎和字符编码。

3、修改表

3.1 追加一个列

语法格式如下:

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

3.2 修改一个列

可以修改列的数据类型,长度、默认值和位置，语法格式如下:

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

3.3 重命名一个列

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

3.4 删除一个列

ALTER TABLE 表名 DROP 【COLUMN】字段名

4、重命名表

方式一:使用RENAME

RENAME TABLE emp
TO myemp;

方式二:

ALTER table dept
RENAME [TO] detail_dept;  -- [TO]可以省略

5、删除表

DROP TABLE 语句不能回滚

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

6、清空表

TRUNCATE TABLE语句:删除表中所有的数据＋释放表的存储空间。

TRUNCATE TABLE detail_dept;

TRUNCATE语句不能回滚,而使用 DELETE 语句删除数据,可以回滚。

SET autocommit = FALSE;  #先将自动提交关闭
 
DELETE FROM emp2;  #使用delete清空表
 
SELECT * FROM emp2;　　#此时数据应为空
 
ROLLBACK;　　#回滚
 
SELECT * FROM emp2;　　#此时数据会恢复

阿里开发规范:
【参考】TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少,但 TRUNCATE 无事务且不触发 TRIGGER,有可能造成事故,故不建议在开发代码中使用此语句。
说明:TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。