003DDL通用语法及分类

SQL通用语法

1. SQL语句可以单行或多行书写,以分号结尾。
2. SQL语句可以使用空格/缩进来增强语句的可读性。
3. MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
4. 注释:
   单行注释:—注释内容或#注释内容(MySQL特有)
   多行注释: /注释内容/

SQL分类

• DDL
• DML
• DQL
• DCL | 分类 | 全称 | 说明 | | —- | —- | —- | | DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表,字段) | | DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增删改 | | DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 | | DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |

DDL-数据库操作

查询

查询所有数据库

SHOW DATABASES;

查询当前数据库

SELECT DATABASE();

创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名[DEFAULT CHARSET字符集][COLLATE 排序规则]；

推荐字符集： utf8mb4

删除数据库

DROP DATABASE [IF NOT EXISTS]数据库名;

使用数据库

USE 数据库名；

切换到xx数据库

DDL-表操作-查询

查询当前数据库所有表

SHOW TABLES;

查询表结构

DESC 表名；

查询指定表的建表语句

SHOW CREATE TABLE 表名 ；

DDL-表操作-创建

CREATE TABLE 表名(
    字段1 字段1类型[COMMENT 字段1注释],
  字段2    字段2类型[COMMENT 字段2注释],
  字段3 字段3类型[COMMENT 字段3注释],
  .......,
    字段n字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];

注意: []为可选参数,最后一个字段后面没有逗号

CREATE TABLE tb_user(
    id int comment '编号',
  name varchar(50) comment '姓名',
  age int comment '年龄',
  gender varchar(1) comment '性别'
) comment '用户表';

查询表结构

desc tb_user

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(1)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

查询表详细信息（建表语句）

+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_user | CREATE TABLE `tb_user` (
  `id` int DEFAULT NULL COMMENT '编号',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `gender` varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表'            |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql数据类型

数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。

案例

设计一张员工信息表,要求如下:
1.编号(纯数字)
2.员工工号(字符串类型,长度不超过10位)
3.员工姓名(字符串类型,长度不超过10位)
4.性别(男/女,存储一个汉字)
5.年龄(正常人年龄,不可能存储负数)
6.身份证号(二代身份证号均为18位,身份证中有X这样的字符)
7.入职时间(取值年月日即可)

CREATE TABLE emp(
    id int comment '编号',
    workno varchar(10) comment '工号',
  name varchar(10) comment '姓名',
  gender char(1) comment '性别',
  age tinyint unsigned comment '年龄',
  idcard char(18) comment '身份证',
  entrydate  date comment '入职日期'
) comment '员工表';

mysql> desc emp;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id        | int              | YES  |     | NULL    |       |
| workno    | varchar(10)      | YES  |     | NULL    |       |
| name      | varchar(10)      | YES  |     | NULL    |       |
| gender    | char(1)          | YES  |     | NULL    |       |
| age       | tinyint unsigned | YES  |     | NULL    |       |
| idcard    | char(18)         | YES  |     | NULL    |       |
| entrydate | date             | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

DDL-表修改-修改

添加字段

ALTER Table 表名 ADD 字段名 类型(长度) [comment 注释] [约束 ]；

案例:

为emp表增加一个新的字段”昵称”为nickname,类型为varchar(20)

ALTER  Table emp ADD nickname varchar(20) comment '昵称';

 Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id        | int              | YES  |     | NULL    |       |
| workno    | varchar(10)      | YES  |     | NULL    |       |
| name      | varchar(10)      | YES  |     | NULL    |       |
| gender    | char(1)          | YES  |     | NULL    |       |
| age       | tinyint unsigned | YES  |     | NULL    |       |
| idcard    | char(18)         | YES  |     | NULL    |       |
| entrydate | date             | YES  |     | NULL    |       |
| nickname  | varchar(20)      | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+---

修改数据类型

ALTER Table 表名 MODIFY 字段名 新数据类型(长度)；

修改字段名和字段类型

ALTER Table 表名 CHANGE 旧字段名 类型(长度) [comment 注释] [约束]；

案例:

将emp表的nickname字段修改为username, 类型为varchar(30)

ALTER TABLE emp CHANGE nickname username varchar(30) comment '用户名';

| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id        | int              | YES  |     | NULL    |       |
| workno    | varchar(10)      | YES  |     | NULL    |       |
| name      | varchar(10)      | YES  |     | NULL    |       |
| gender    | char(1)          | YES  |     | NULL    |       |
| age       | tinyint unsigned | YES  |     | NULL    |       |
| idcard    | char(18)         | YES  |     | NULL    |       |
| entrydate | date             | YES  |     | NULL    |       |
| username  | varchar(30)      | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

删除字段

ALTER TABLE 表名 DROP 字段名;

案例:

将emp表的字段username删除

ALTER TABLE emp DROP username;

-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id        | int              | YES  |     | NULL    |       |
| workno    | varchar(10)      | YES  |     | NULL    |       |
| name      | varchar(10)      | YES  |     | NULL    |       |
| gender    | char(1)          | YES  |     | NULL    |       |
| age       | tinyint unsigned | YES  |     | NULL    |       |
| idcard    | char(18)         | YES  |     | NULL    |       |
| entrydate | date             | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
7 rows in set (0.01 sec)

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

案例:

将emp表的表名修改为 employee

ALTER TABLE emp RENAME TO employee;

mysql> show tables;
+------------------+
| Tables_in_xuxian |
+------------------+
| employee         |
| tb_user          |
+------------------+
2 rows in set (0.00 sec)

删除表

DROP TABLE [IF EXISTS] 表名;

删除指定表，并重新创建新表

TRUNCATE TABLE 表名;