MYSQL基础之创建数据库表，以及修改表

前面聊查询，但是查询数据的前提是需要提前存储数据，而存储数据提前需要的是有数据表，而表是存在在数据库中的。所以现在开始聊一些创建数据库，以及创建表的内容。

当然在创建数据库之前，先看一下其需要遵守的规范。

命名规则

• 数据库名，表名不得超过30个字符，变量名限制为29个
• 必须只能包含A-Z，a-z，0-9，_ 共63个字符
• 数据库名，表名，字段名等对象名中间不能包含空格
• 同一个MYSQL中，数据库不能同名，同一个数据库中表名不能重复，同一个表中字段名不能重复。
• 尽量保证字段没有和保留字，数据库系统或常用的方法冲突，如果非要用的话，需要用着重号（`）引起来。
• 保持字段名和类型一致性：比如某个数据作为两个表的关联其字段名一样的同时其数据类型也要保持一致。

数据库的数据类型,前面一篇具体聊过，所以就点击列出；

类型	类型举例
整数类型	TINYINT,SMALLINT,MEDIUMINT,INT(INTEGER),BIGINT
浮点类型	FLOAT,DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR,TIME,DATE,DATATIME,TIMESTAMP
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY,VARBINARY,TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB
JSON类型	JSON对象，JSON数组
空间数据类型	单值类型：GEOMETRY,POINT,LINESTRING,POLYGON 集合类型: MULTIPOINT,MULTILINESTRING,MULTIPOLYGON,GEOMETRYCOLLECTION

上面很多种类型，类型种又分好几种，不过常用的也就几种而已。

创建数据库

创建数据库方式：

-- 方式1  使用默认的字符集
CREATE DATABASE 数据库名;

-- 方式2  设置自己想要用的字符集
CREATE DATABASE 数据库名 CHARACTER SET 字符集;

-- 方式3 推荐 多了一个判断是否有数据库，如果没有就创建，这样sql就不会报错说数据库已存在
CREATE DATABASE IF NOT EXISTS 数据库名 [  CHARACTER SET 字符集 ]  ;

--方法4 通过可视化工具，比如SQLyog 通过菜单栏创建数据库

补充：数据库创建后不能修改名，虽然很多可视化工具有修改数据库名的选项，其本质就是创建一个新的数据库，然后将所有的表以及数据复制到新库，然后删除旧数据库而已。

关于数据库的常用SQL

查看数据库创建的语句

SHOW CREATE DATABASE 数据库名;

查看连接中有多少数据库：

SHOW DATABASES;
-- 如果查看某数据库下的表
SHOW TABLES;
-- 如果查询指定的数据库下的表
SHOW TABLES FROM 数据库;

切换数据库

USE 数据库名

查看当前使用是数据库

SELECT DATABASE();
-- 或者
SELECT DATABASE() FROM dual;

修改数据库

-- 无法修改数据库名，前面说过
-- 修改数据库的字符集  一般不会修改毕竟如果构建的时候这个就没有考虑清楚，不然一个数据库下面很多张表成本有点高
ALTER DATABSE 数据库名  CHARACTER SET 字符集;

删除数据库

-- 方式1
DROP DATABASE 数据库名;
-- 方式2  推荐 判断是否存在然后删除，不会报错  不过一般的时候也不会用如果用来弄不好要吃牢饭
DROP DATABSE IF EXISTS 数据库名

创建表

创建表的前提就是登录的用户，拥有创建表的权限。然后需要有足够的表空间。

创建表方式

• 方式一

CREATE TABLE [IF NOT EXISTS] 表名(
字段 
....
    
)

创建表的使用其默认的字符集是数据库的字符集。

• 方式二

  这个是根据已有的表创建新的表

  CREATE TABLE 表名 
  AS
  SELECT * FROM 某表（这个搜索可以带过滤条件甚至可以用子查询，以）
  

  这个创建表中字段名以及类型，和搜索后的数据名和类型保持一致，当然如果某个字段有别名，新报的字段名也就变成了别名。

  AS 后面的SQL可以用前面聊搜索的的时候所有格式SQL。

  同时这样创建的表，也会带有AS后面SQL搜索返回的值。

  如果SQL搜索的返回没有数据，自然信标中也不会有数据，这个时候最常用的一种方式就是

  CREATE TABLE 表名 
  AS
  SELECT * FROM 某表 WHERE 1=0;
  

修改表

修改表可以添加一个字段，修改一个字段包括数据类型，长度默认值等，重名一个字段，删除一个字段等操作。

• 添加一个字段

ALTER TABLE 表名
ADD 字段名 [FIRST或者  AFTER 另一个字段]
-- 位置就是 FIRST   添加在第一个位置， AFTER 是放在另一个字段后面

• 修改一个字段

  ALTER  TABLE 表名
  MODIFY  修改的字段名 重新定义
  
  -- 一般会修改长度以及默认值，不会修改其类型
  

• 修改字段名

  ALTER TABLE 表名
  CHANGE 原来字段名  新字段名 定义新字段
  

• 删除一个字段

  ALTER TABLE  表名
  DROP COLUMN 字段名
  

重命名表

这个两个方式

RENAME TABLE 原表名 
TO  新表名

ALTER TABLE 表名
RENAME [TO]  新表名   
-- TO 可以省略

删除表

DROP 删除的表无法回滚。其不不光删除表结构，同时删除表内的数据，释放表空间。

DROP TABLE  [IF EXISTS] 表名 

清空表

清空表和删除表不一样，其主要就是清楚表内的数据，不会删除表结构。用到的关键字是：TRUNCATE

TRUNCATE TABLE 表名

清空表的效果其实和DCL中的DELETE很相似，但是又有什么不同呢，首先要

TRUNCATE 和 DELETE 区别

补充两个DML中的关键字COMMIT和ROLLBACK

• COMMIT:就是提交数据，一旦执行COMMIT，数据库就会保存执行其上面SQL语句执行的结果,MYSQL默认是自动执行的，而ORACLE默认是不自动执行的。
• ROLLBACK: 回滚数据。就是执行其以上的SQL（COMMIT以后的），在执行ROLLBACK之后就会返回，说白就是等于没有执行修改。

说到了默认是自动执行COMMIT的，如果修改默认提交事务的化，可以用下面SQL语句：

SET autocommit=FALSE;

现在开始聊TRUNCATE 和DELETE 的区别

• 相同点： 都可以删除表的数据，而不删除表结构。

• 不同点：

  • TRUNCATE 删除表一点执行此操纵，表的数据就会被删除，其数据是不可以回滚，简单理解 TRUNCATE = DELETE + COMMIT;那么前提设置了不自动提交事务。
  • DELETE 可以带WHERE，删除表中的部分数据，这样比TRUNCATE删除数据更加灵活，同时其删除的数据可以实现回滚。前提是设置不自动提交事务。

  阿里规范中：

  TRUNCATE 比DELETE速度快，且使用的系统和事务日志资源少，但是TRUNCATE无事务且不触发TRIGGER，有可能会造成事故，故不建议在开发中使用。

  在扩展说一下，其实DDL就是无论是否设置了不自动提交事务，其都是无法回滚，因为DDL执行后自动会执行一次COMMIT。而DCL却是如果设置不自动提交事务都可以混过。

阿里的命名规则

• 强制 表名，字段名必须使用小写字母或者数字，禁止出现以数字开头，禁止两个下滑线中间值出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名要需要慎重考虑。
• 强制 禁止使用保留字，如DESC,RANGE,MATCH等，请参考MYSQL官方保留字。
• 强制表必备三字段：id,gmt_create,gmt_modified
  • 说明：其中id必为主键，类型为BIGINT UNSIGNED,单表自增，步长为1。gmt_create,gmt_modified的类型均为DATETIME类型，前者显示表示主动式创建，后者过去分词表示被动式更新。
• 推荐 表名最后遵循“业务名称_表的作用”。
• 推荐库名和应用名称尽量一致。
• 推荐 在执行删除表和更新表 （删除字段也会下删除数据）时，因为在MYSQL中几乎没有提示确认以及回滚，所以建议在执行之前先备份一下。

扩展

在MYSQL8.0中，InnoDB表DDL支持事务的完整性，即DDL要么都成功要么回滚。DDL操作回滚日志写入到data dictionary数据字典表 mysql innodb_ddl_log(该表是隐藏的表,通过 show tables无法看到)中,用于回滚操作。通过设置参数,可将DL操作日志打印输出到MSQ错误日志中。

简单演示

-- 首先在创建数据库以及创建表
CREATE DATABASE IF NOT EXISTS  test;
USE test;
CREATE TABLE test_table1(
id INT(2),
NAME VARCHAR (10)
)
-- test_table2 不存在
DROP test_table1,test_table2;

当然无论在5.7还是在8.0中都会报错，毕竟test_table2这个表不存在，但是5.7中test_table1还是被删除了，但是在8.0中test_table1却存在，所以可以看出事务被原子化了，要么都执行，要么都不执行。

​