SQL语句之DDL操作全解析

SQL 全称 Structured Query Language，结构化查询语言。操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准 。 #### 一、SQL 基础知识 ##### （一）SQL 通用语法 在学习具体的SQL语句之前，先来了解一下SQL语言的通用语法。 ① SQL语句可以单行或多行书写，以分号结尾。 ② SQL语句可以使用空格/缩进来增强语句的可读性。 ③ MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。 ④ 注释：单行注释为 “-- 注释内容” 或 “# 注释内容”；多行注释为 “/\* 注释内容 \*/” ##### （二）SQL 分类 SQL语言，根据其功能，主要分为四类：DDL、DML、DQL、DCL。 1、DDL：全称为 Data Definition Language，即数据定义语言。用来定义数据库对象（数据库、表、字段） 2、DML：全称为 Data Manipulation Language，即数据操作语言。用来对数据库表中的数据进行增删改。 3、DQL：全称为Data Query Language，即数据查询语言。用来查询数据库中表的记录。 4、DCL：全称为Data Control Language，即数据控制语言。用来创建数据库用户、控制数据库的访问权限。 #### 二、DDL —— 数据库操作 ###### 1、查询所有数据库 > > > show databases; > ######  ###### 2、查询当前数据库 > > > select datebase(); >  ###### 3、创建数据库 > > > create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ] ; > 这条指令的基本形式是 create database 数据库名，方括号中的部分可加可不加。 if not exists：同一个数据库服务器中，不能创建两个名称相同的数据库，否则会报错。可以通过 if not exists 参数来解决这个问题，数据库不存在，则创建该数据库，如果存在，则不创建。 default charest 字符集：如果不指定，则使用默认的字符集utf8mb4，建议使用这个。 ```csharp create database my123; ```  ###### 4、删除数据库 > > > drop database [ if exists ] 数据库名 ; > 如果删除一个不存在的数据库，将会报错。此时，可以加上参数 if exists ，如果数据库存在，再执行删除，否则不执行删除。 ```csharp drop database my123; ```  ###### 5、切换数据库 > > > use 数据库名 ; > 我们要操作某一个数据库下的表时，就需要通过该指令，切换到对应的数据库下，否则是不能操作的。 比如，切换到itcast数据库，执行如下SQL： ```csharp use itcast; ``` #### 三、DDL —— 表操作 ##### （一）查询创建 ###### 1、查询当前数据库所有表 > > > show tables; > 比如，我们可以切换到 sys 这个系统数据库，并查看系统数据库中的所有表结构。 ```csharp use sys; show tables; ```  ###### 2、查看指定表结构 > > > desc 表名; > 通过这条指令，我们可以查看到指定表的字段，字段的类型、是否可以为NULL，是否存在默认值等信息。  ###### 3、查询指定表的建表语句 > > > show create table 表名 ; > 通过这条指令，主要是用来查看建表语句的，而有部分参数我们在创建表的时候，并未指定也会查询到，因为这部分是数据库的默认值，如：存储引擎、字符集等。  ###### 4、创建表结构 > > > CREATE TABLE 表名( > 字段1 字段1类型 [COMMENT 字段1注释 ], > 字段2 字段2类型 [COMMENT 字段2注释 ], > 字段3 字段3类型 [COMMENT 字段3注释 ], > ...... > 字段n 字段n类型 [COMMENT 字段n注释 ] > ) [ COMMENT 表注释 ] ; > > 注意：[...] 内为可选参数，最后一个字段后面没有逗号，因为这就是一条SQL语句，一条SQL语句只有一个逗号。 比如，我们创建一张表 tb\_user，对应的结构如下，那么建表语句为：  ```csharp create table tb_user(id int comment '编号',name varchar(50) comment '姓名',age int comment '年龄',gender varchar(1) comment '性别' ) comment '用户表'; ``` ##### （二）数据类型 在上述的建表语句中，我们在指定字段的数据类型时，用到了int，varchar，那么在MySQL中除了以上的数据类型，还有哪些常见的数据类型呢？ 接下来，我们详细介绍一下MySQL的数据类型。 MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。 ###### 1、数值类型  对表格进行归纳，前 5 个数据类型 tinyint、smallint、mediumint、int/integer、bigint 表示整型数据，仅仅是取值范围不同。接下来 2 个数据类型 float、double 表示浮点型数据。 最后一个 decimal 是精确的浮点型数据，其中的 M 表示小数的位数有多少位，D 表示小数点后面有多少位。例如，decimal(10,2)，即小数的整体位数是 10 位，小数点后面有 2 位。 同时，float 与 double 也可以对位数进行规定，如double(4,1)，但是我们常用的精确的浮点型数据类型还是decimal。 同时如果希望是无符号数，则可在数据类型后面加上 unsigned，如 age tinyint unsigned。 ###### 2、字符串类型  char 与 varchar 都可以描述字符串。char 表示定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关，例如char(10)。而 varchar 表示变长字符串，指定的长度为最大占用长度，例如varchar(10)。 char 中未占用的字符，会用空格来补位；varchar 则是存多少个字符就占用多少个空间。即varchar在规定具体长度的同时，还需要根据当前字符串修改长度，所以相对而言char的性能会更高一些，用空间换时间。 还有两个就是blob与text，只是前缀的不同导致长度的不同，其前缀为tiny、无、medium、long。blob中存储的文本形式是二进制形式的、而 text 中的就是文本。 ###### 3、日期时间类型  ```csharp --例如: -- 1). 生日字段 birthdaybirthday date-- 2). 创建时间 createtimecreatetime datetime ``` ##### （三）修改 ###### 1、添加字段 > > > alter table 表名 add 字段名 类型 [ comment 注释 ] [ 约束 ]; > 其中 “alter table 表名” 是选中具体的表，“add 字段名 类型” 是具体的操作，“comment 注释” 是给代码添加注释。代码中方括号的部分可以省略。  添加字段的意思，就是在一张表中，再加一列。如为 emp 表增加一个新的字段”昵称”为 nickname，类型为varchar(20)。代码与执行结果如下： ```csharp alter table emp add nickname varchar(20) comment '昵称'; ```  ###### 2、修改数据类型 > > > alter table 表名 modify 字段名 新数据类型; > ###### 3、修改字段名和字段类型 > > > alter table 表名 change 旧字段名 新字段名 类型 (长度) [ comment 注释 ] [ 约束 ]; > 将 emp 表的 nickname 字段修改为 username，类型为varchar(30)。代码与执行结果如下： ```csharp alter table emp change nickname username varchar(30) comment '昵称'; ```  ###### 4、删除字段 > > > alter table 表名 drop 字段名; > 将 emp 表的字段 username 删除，代码与执行结果如下： ```csharp alter table emp drop username; ```  ###### 5、修改表名 > > > alter table 表名 rename to 新表名; > 将emp表的表名修改为 employee，具体代码如下： ```csharp alter table emp rename to employee; ``` ##### （四）删除 ###### 1、删除表 > > > drop table [ if exists ] 表名; > 可选项 if exists代表，只有表名存在时才会删除该表，表名不存在，则不执行删除操作(如果不加该参数项，删除一张不存在的表，执行将会报错)。 如果tb\_user表存在，则删除tb\_user表，具体代码如下： ```csharp drop table if exists tb_user; ``` ###### 2、删除指定表，并重新创建表 > > > truncate table 表名; > 注意: 在删除表的时候，表中的全部数据也都会被删除；这个操作就是将这张表进行重置。