0基础学MySQL数据库—从小白到大牛（9）创建和管理表

一、创建数据库

启动MySQL服务之后，输入以下命令连接到MySQL服务器：

[root@zhyno1 ~]# mysql -uroot -pmysql代表客户端命令、-u后面跟的是用户名、-p表示需要输入的密码

• 如果登录成功，会返回 mysql>的提示符。和以下的欢迎界面 欢迎使用MySQL监视器。命令以结尾；或\g。你的MySQL连接id是32服务器版本：5.6.50-log Source distribution版权所有（c）2000、2020，Oracle和/或其附属公司。保留所有权利。Oracle是Oracle Corporation和/或其附属公司。其他名称可能是其各自的商标业主。键入“help；”或“\h”寻求帮助。键入“\c”以清除当前输入语句。 在mysql>提示符后的SQL语句都需要以；或者\g结尾

创建库命令：

创库命令，使用此方法创建，使用的是默认的字符集CREATE DATABASE dbname;创建数据库且带上字符集CREATE DATABASE dbname2 CHARACTER SET 'utf8';查看数据库支持的字符集SHOW VARIABLES LIKE 'character_%';+--------------------------+-----------------------------------+| Variable_name            | Value                             |+--------------------------+-----------------------------------+| character_set_client     | utf8                              || character_set_connection | utf8                              || character_set_database   | utf8mb4                           || character_set_filesystem | binary                            || character_set_results    | utf8                              || character_set_server     | utf8mb4                           || character_set_system     | utf8                              || character_sets_dir       | /www/server/mysql/share/charsets/ |+--------------------------+-----------------------------------+如果创建的数据库存在，则不会创建且不会报错，反之则创建CREATE DATABASE IF NOT EXISTS dbname;查看数据库的字符集show create database zhytest;+----------+------------------------------------------------------------------+| Database | Create Database                                                  |+----------+------------------------------------------------------------------+| zhytest  | CREATE DATABASE `zhytest` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+------------------------------------------------------------------+utf8便是数据库的字符集查看当前使用的数据库SELECT DATABASE() FROM DUAL;+------------+| DATABASE() |+------------+| zhytest    |+------------+查看当前数据库下保存的数据表SHOW TABLES ;查看别的数据下保存的数据表;SHOW TABLES FROM zhytest;修改数据库字符集ALTER DATABASE mytest2 CHARACTER SET 'utf8';

现在创建一个库叫test1；

mysql> CREATE DATABASE test1;Query OK, 1 row affected (0.00 sec)

创建成功后会返回Query OK, 1 row affected (0.00 sec)，首先

• Query OK 表示上面命令执行成功
• 1 row affected 表示只影响了数据库中一行记录
• 0.00 sec 记录操作时间

如果需要查看数据库中有那些库可以用

mysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test1              || zhy                || zhytest            |+--------------------+6 rows in set (0.00 sec)

• information_schema 主要存储系统中一些数据库对象信息，比如用户表信息，列信息，权限信息，字符集信息，分区信息等。每个用户都可以查看这个表，但是根据权限不同，查看的内容也不一样。
• performance_schema MySQL引入的系统库，用于存储系统性能相关的动态参数表。
• mysql 存储系统用户的权限信息。

在查看完已有数据库后，选择要操作的数据库

USE dbname;例如选择test1mysql> USE test1;Database changed再用以下命令来查看test1库中创建的表mysql> SHOW TABLES;Empty set (0.00 sec)因为这个库下面没有表，所以显示Empty set那我们查看一下mysql表USE mysql;mysql> SHOW TABLES;+---------------------------+| Tables_in_mysql           |+---------------------------+| columns_priv              || db                        || event                     || time_zone_transition      || time_zone_transition_type || user                      |+---------------------------+6 rows in set (0.00 sec)表太多这边做删减

二、删除库

方式1DROP DATABASE dbname;方式2-如果要删除的数据库存在，则删除成功，如果不存在，则默默结束，不会报错;DROP DATABASE IF EXISTS dbname;例如要删除test1mysql> DROP DATABASE IF EXISTS test1;Query OK, 0 rows affected (0.00 sec)

• 0 rows affected这个提示含义是前一次MySQL操作所影响记录行数，通常只对增删改查操作生效，drop等DDL操作通常显示0 rows affected
• 注意！删除库后，库下的所有表都会被全部删除，所以删除前一定要检查仔细

三、创建数据表

在数据库中创建数据表基本语法如下：

CREATE TABLE [IF NOT EXISTS]tablename( COLUMN_NAME_1 COLUMN_TYPE_1 CONSTRAINTS COLUMN_NAME_2 COLUMN_TYPE_2 CONSTRAINTS 字段3, 数据类型 [约束条件] [默认值],   ... COLUMN_NAME_N COLUMN_TYPE_N CONSTRAINTS)

• COLUMN_NAME_1 为列名
• COLUMN_TYPE_1 列类型
• CONSTRAINTS 列约束

举例：

• 创建一个emp的表
• 表里包括ename(姓名)、hiredate(雇佣日期)、sal(薪水)、deptno(序号)
• 字段分别为varchar(10)、date、int(2)、int(2)

mysql> CREATE TABLE IF NOT EXISTS emp( ename varchar(10),hiredate date ,sal int(2),deptno int(2));Query OK, 0 rows affected (0.02 sec)查看表结构mysql> desc emp;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| ename    | varchar(10) | YES  |     | NULL    |       || hiredate | date        | YES  |     | NULL    |       || sal      | int(2)      | YES  |     | NULL    |       || deptno   | int(2)      | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)为了得到更全面的建表信息使用mysql> show create table emp \G;*************************** 1. row ***************************       Table: empCreate Table: CREATE TABLE `emp` (  `ename` varchar(10) DEFAULT NULL,  `hiredate` date DEFAULT NULL,  `sal` int(2) DEFAULT NULL,  `deptno` int(2) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR: No query specified可以看到存储引擎和字符集，\G是指可以按照竖向排列

创建一个表zhytest2实现对zhytest的复制，但是不包括表数据的复制；

CREATE TABLE zhytest2AS SELECT *FROM zhytestWHRER 1 = 2;通过WHERE 1=2 实现每一条表的数据过来，都返回0

四、删除&清空表

删表命令如下：

DROP TABLE IF EXISTS tablename;要删除emp表如下DROP TABLE IF EXISTS emp;

清空表命令如下：

• 不删除表结构，只删除内容

TRUNCATE TABLE zhytest;

五、修改表

使用 ALTER TABLE 语句可以实现：

• 向已有的表中添加列
• 修改现有表中的列
• 删除现有表中的列
• 重命名现有表中的列

ALTER TABLE tablename MODIFY [COLUMN] COLUMN_DEFINITION [FIRST / AFTER COL_NAME]例如修改emp表中的ename字段定义，将varcher（10）改为varcher（20）mysql> desc emp;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| ename    | varchar(10) | YES  |     | NULL    |       || hiredate | date        | YES  |     | NULL    |       || sal      | int(2)      | YES  |     | NULL    |       || deptno   | int(2)      | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)mysql> ALTER TABLE emp MODIFY ename varchar(20);Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc emp;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| ename    | varchar(20) | YES  |     | NULL    |       || hiredate | date        | YES  |     | NULL    |       || sal      | int(2)      | YES  |     | NULL    |       || deptno   | int(2)      | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)

那如果我们要添加字段要怎么操作呢：

ALTER TABLE tablename ADD [COLUMN] COLUMN_DEFINITION [FIRST / AFTER COL_NAME]例如在emp中新增字段age，类型为int（3）mysql> ALTER TABLE emp ADD COLUMN age int(3);Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> DESC emp;+----------+-------------+------+-----+---------+-------+| Field    | Type        | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| ename    | varchar(20) | YES  |     | NULL    |       || hiredate | date        | YES  |     | NULL    |       || sal      | int(2)      | YES  |     | NULL    |       || deptno   | int(2)      | YES  |     | NULL    |       || age      | int(3)      | YES  |     | NULL    |       |+----------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)默认添加到最后一个字段如果想要添加到其他位置：放在第一个ALTER TABLE emp ADD COLUMN age int(3) FIRST;放在指定字段后面 salALTER TABLE emp ADD COLUMN age int(3) AFTER sal;

删除字段怎么做呢

ALTER TABLE tablename DROP [COLUMN] col_name;删除age字段ALTER TABLE emp DROP [COLUMN] age;

那如何改名字段呢

ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST / AFTER COL_NAME];将age改为age1并且字段类型修改为int（4）ALTER TABLE emp CHANGE age age1 int(4);

六、重命名表

方式1RENAME TABLE zhytestTO zzhhyytest;方式2ALTER TABLE myemp2RENAME myemp12;

七、常见数据类型

==标黄==为实际开发过程中实际最长使用的数据类型

类型	类型举例
整数类型	TINYINT、SMALLINT、MEDIUMINT、==INT(或INTEGER)==、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	==DECIMAL==
位类型	BIT
日期时间类型	YEAR、TIME、==DATE==、DATETIME、TIMESTAMP
文本字符串类型	CHAR、==VARCHAR==、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值：GEOMETRY、POINT、LINESTRING、POLYGON； 集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION

八、DCL中COMMIT和ROLLBACK

• COMMIT提交数据 一旦执行COMMUT，则数据就被永久保留在了数据库中，意味着数据不可用回滚。
• ROLLBACK回滚数据 一旦执行了ROLLBACK，则可以实现数据的回滚，回滚到最近一次的COMMIT之后。

九、对比TRUNCATE TABLE和DELETE FROM

相同点：都可以实现对表中所有数据的删除，同时保留表结构。
不同点：TRUNCATE TABLE:一旦执行此操作，表数据全部清除。同时，==数据是不可以回滚的==。
DELETE FROM:一旦执行此操作，表数据可以全部清除(不带WHERE)。同时数据是==可以实现回滚的==。

十、DDL和DML的说明

• DDL(数据定义语言)一旦执行，==就不可以回滚==
• DCL(数据库操作语言)==默认情况下一旦执行==，不可以回滚。但是在执行DML之前，执行SET autocommit = FALSE则DML就可以回滚

DELETE FROM回滚到最近的一次COMMIT操作：提交数据COMMIT;查询表所有数据SELECT *FROM zhytest;执行开启可回滚指令SET autocommit = FALSE;清空表数据DELETE FROM myemp3;再次查看是否有数据SELECT *FROM zhytest; 回滚ROLLBACK;发现表中数据回来SELECT *FROM zhytest; TRUNCATE TABLE不做示范，无法回滚。

二者如何选择？
阿里开发规范：

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

说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

• 拓展1：阿里巴巴《Java开发手册》之MySQL字段命名 【==强制==】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。 正例：aliyun_admin，rdc_config，level3_name 反例：AliyunAdmin，rdcConfig，level_3_name 【==强制==】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。 【==强制==】表必备三字段：id, gmt_create, gmt_modified。 说明：其中 id 必为主键，类型为BIGINT UNSIGNED、单表时自增、步长为 1。gmt_create, gmt_modified 的类型均为 DATETIME 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新。 【==推荐==】表的命名最好是遵循 “业务名称_表的作用”。 正例：alipay_task 、 force_project、 trade_config 【==推荐==】库名与应用名称尽量一致。 【==参考==】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。 正例：无符号值可以避免误存负数，且扩大了表示范围。
• 拓展2 MySQL8.0的原子化DDL 原子的意思就是不可拆分，简单来说就是每写的一个语句，都是一个整体，你的语句操作，要么成功，要么回滚。 在MySQL5.7中，一个删除表语句 假设我们没有book2这个表，但是有book1这个表

如果我们删除这两个表DROP TABLE book1,book2;数据库会报错ERROR 1051 (42S02): Unknown table 'mytest.book2'但是还是把book1给删除了

如果我们用MySQL8.0，就不会把book1给删除，因为8.0具有原子化，即指令都是一个整体，不可拆分，如果一个有错误，即会马上回滚，而不会执行。
原子化指的就是 ‘增删改查’ 等组成的事务是一个整体，要么全部执行成功，一旦有一个执行失败则进行回滚，全部都不进行执行，这个整体性不可分割类似于原子。这个只在MySQL8.0中体现。