数据定义语言DDL

DDL数据定义语言概述

数据定义语言(对应英文名称为: Data Definition Language，简称"DDL")就是定义库和表结构的相关语句，这些语句并不直接操作数据，而是对数据库及表的一个管理操作。

数据库操作

定义数据库的规范

创建库需要应该遵循包括但不限于以下规范:

1. 数据库名称小写，尽量不要使用中文字符作为数据库名称;
2. 数据库名称要与业务有关;
3. 数据库名称不要以数字开头;
4. 数据库名称不要太长;
5. 不能使用保留字符串或者关键字(尽管我们可以在关键字加一个反引号强制创建出来，但这样做是没有多大意义的!);

创建数据库

CREATE DATABASE bigdata CHARACTER SET = utf8mb4;

查看数据库信息

SHOW DATABASES;  # 查看现有的数据库信息
SHOW CREATE DATABASE bigdata;   # 查看创建数据库的语句
select database();  # 查询当前数据库 

修改数据库信息

温馨提示:

1. 修改数据库只能信息只能修改字符集和校对规则(排序规则);
2. 修改字符集只能由字符集较小的改为字符集较大的，比如我们可以将utf8字符集改为utf8mb4，千万别反向改动，因为utf8mb4字符集可以存储emoji表情字符，而utf8字符集则不可以哟，如果你反向改动则可能导致乱码;
3. 不推荐将两个不相交的字符集进行修改，比如将utf8mb4字符集修改为gbk字符集，这样可能导致之前写入数据库的数据出现乱码，因为utf8mb4字符集通常以3字节存储一个汉字，而gbk通常以2个字节存储一个汉字。

ALTER DATABASE bigdata DEFAULT CHARACTER SET gbk;

删除数据库

DROP DATABASE bigdata; 

表操作

定义表的规范

创建表需要应该遵循包括但不限于以下规范:

1. 表名称小写，尽量不要使用中文字符作为表名称;
2. 表名称要与业务有关;
3. 表名称不要以数字开头;
4. 表名称不要太长,不建议超过15-20个字符;
5. 不能使用保留字符串或者关键字(尽管我们可以在关键字加一个反引号强制创建出来，但这样做是没有多大意义的!);
6. 必须设置存储引擎和字符集;
7. 针对不同的字段应该设置适合其的数据类型，不要使用使用CHAR类型来定义所有数据类型，尽管语法上没问题，但也要切合实际，比如age字段，推荐使用TINYINT数据类型，而不推荐使用VARCHAR，INT等类型。
8. 一张表中必须要有主键(这个我们在学习表的索引你就明白为什么啦);
9. 每个列都要注释，尽管你当时建表时对每个列都很熟悉，但随着时间的推移你可能依旧会忘记一些字段的作用，因此为字段添加注释还是很有必要的，这也有助于离职交接，我们要做一个有责任心的DBA;
10. 列名称不要太长;

查看表相关的信息

SHOW TABLES;  # 查看当前数据库中已经存在的表
SHOW CREATE TABLE shirts;  # 查看建表时的语句
DESC shirts;  # 查看表结构

创建表

mysql>  CREATE TABLE student(name varchar(30),age tinyint, gender enum('Male','Female') DEFAULT NULL);
Query OK, 0 rows affected (0.03 sec)

# 若不指定字符集，则默认使用表所在库的默认字符集;
# 若不指定存储引擎，则默认使用的存储引擎是INNODB;

修改表

# 添加列：
mysql> ALTER TABLE student ADD COLUMN address VARCHAR(50) NOT NULL COMMENT '家庭住址';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 添加多个列：
mysql> ALTER TABLE student ADD COLUMN(phone_number bigint UNSIGNED NOT NULL UNIQUE KEY COMMENT '手机号码',total_score smallint UNSIGNED COMMENT '总成绩',remarks varchar(150) COMMENT '备注信息');
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 修改某字段的数据类型
mysql> ALTER TABLE student MODIFY COLUMN phone_number VARCHAR(12) NOT NULL UNIQUE KEY COMMENT '手机号码(通常是以数字1开头，共计11位)+座机号码(如0915-2916388)';
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 1

# 修改字段名和字段类型
mysql> ALTER TABLE student CHANGE name name_every_one  varchar(30) DEFAULT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 修改表名 
mysql> ALTER TABLE  student RENAME TO student_every_one;
Query OK, 0 rows affected (0.01 sec)

# 删除表中的某个字段
mysql> ALTER TABLE student_every_one DROP COLUMN remarks;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 删除student_every_one表中的remarks列，注意哈，一旦删除该列字段，就意味着数据也将被删除!

删除表

DROP TABLE student_every_one;

# 删除指定表, 并重新创建表 
TRUNCATE TABLE 表名;
# 注意: 在删除表的时候，表中的全部数据也都会被删除。重新创建表是一个全新的表，里面没有数据。

视图操作

1. 什么是视图:
   暂时可以理解为SELECT语句的执行方法，不保存数据本身。换句话说，视图是虚拟包，不存储数据。

   在mysql数据库实例中，information_schema数据库存放的都是mysql内置的视图，当然information_schema这个数据库也是启动数据库实例时自动创建的。后面有关于元数据内容查询会用到该数据库。

2. 视图的作用:
   当一个SQL查询语句较长时，比如生产环境中可能要查询数据时，一条查询SQL要写几十行甚至几百行都有可能，每次都写一遍比较麻烦，对于这种常用的查询语句，我们可以将其定义为一个视图。

3. 视图的应用场景:
   用于保护敏感数据，比如一张表员工表("staff")，里面下包含的薪资待遇的字段("Salary")，当你给某个开发提供全公司员工的信息时，你不想让该员工看到"staff"的"Salary"字段，就可以考虑使用视图技术。

温馨提示:

视图并不会以表的形式存储，而是存储在内置的系统表("information_schema.VIEWS")。

# 创建视图
mysql> CREATE VIEW student AS SELECT name_every_one,age FROM  student_every_one;
Query OK, 0 rows affected (0.01 sec)

# 查看创建视图语句
SHOW CREATE VIEW student;

# 查看视图数据
SELECT * FROM student;

# 修改 
mysql> ALTER VIEW student  AS SELECT name_every_one,age,gender FROM student_every_one;
Query OK, 0 rows affected (0.01 sec)

# 删除
DROP VIEW student;