MYSQL-SQL-01-DDL（Data Definition Language，数据定义语言）

DDL（数据定义语言）

1. DDL（Data Definition Language），数据定义语言，用来定义数据库对象(数据库，表，字段) 。

一、数据库操作

1、 查询mysql数据库管理系统的所有数据库

1. 语法：

show databases;

2. 示例：
   

2、查询当前所在的数据库

1. 语法：

select database();

2. 示例：
   

3、创建数据库

1. 语法：（[]括号内的表示是可选的）

 create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ] ;

• 不加if not exists的情况：当所创建的数据库已经存在的时候，就会报错了。
  

• IF NOT EXISTS 是一个条件子句，用于检查指定的数据库是否已经存在。

  • 如果数据库不存在，则执行创建数据库的语句；
  • 如果数据库已经存在，则不执行创建操作，并且不会返回错误。

• 这个子句作用：因为它可以防止因尝试创建已经存在的数据库而导致的错误。这在自动化脚本或应用程序中尤其重要，因为它们可能需要多次运行，并且不应该因为数据库已经存在而失败。

• 加上if not exists（一般都会加上的）
  

2. 示例：

-- 创建一个company数据库, 使用数据库默认的字符集。
CREATE DATABASE company;-- 如果company数据库不存在，则创建company数据库，如果存在则不创建
CREATE DATABASE IF NOT exists company;-- 创建数据库company并指定字符集utf8mb4CREATE DATABASE company DEFAULT CHARSET utf8mb4;-- 创建数据库时company指定字符集utf8mb4并指定排序规则为utf8mb4_general_ci
CREATE DATABASE company DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

• 在创建数据库时，排序规则（COLLATE）是用于定义字符串比较和排序的规则。这些规则决定了文本数据如何进行排序和比较，包括是否区分大小写、是否区分重音等。
  • 排序规则（Collation）由字符集和排序规则名称组成，格式为 charset_collation。例如，utf8mb4_general_ci 是一个常见的排序规则，其中 utf8mb4 是字符集，general_ci 是排序规则名称，ci 表示不区分大小写（case-insensitive）。
    • 排序：排序规则决定了字符串的排序方式。在 ORDER BY 语句中使用排序规则可以确保字符串按照预期的顺序进行排序。
    • 比较：排序规则影响字符串比较操作，如 =、!=、>、< 等。选择合适的排序规则可以确保字符串比较的结果符合语言规则或业务需求。

4、删除数据库

1. 语法：

 drop database [ if exists ] 数据库名 ;

• if exists是一个条件子句，判断当前数据库是否存在该数据库：

  • 数据库存在则执行sql，删除数据库
  • 当数据库不存在时，SQL 语句不会执行删除操作，而是直接忽略该语句，且不返回错误

• 删除一个不存在的数据库
  

• 使用if exists删除一个不存在数据库
  

2. 示例：

-- 如果company数据库存在则删除company数据库，不存在则不删除。
-- 如果删除一个不存在的数据库，将会报错，使用if exists就是防止报错DROP DATABASE IF EXISTS company;

5、切换数据库

1. 语法：

use 数据库名 ;

2. 示例：

-- 使用company数据库
use company;

二、表操作

1、表操作—查询操作

1.1、查询当前数据库所有表

1. 语法：

show tables;

1.2、查看指定表结构

1. 语法：

desc 表名;

2. 示例：

-- 查看emp表的结构
desc emp;

1.3、查询指定表的建表语句

1. 语法：

show create table 表名 ;

2. 示例：

-- 查看emp表的建表语句
show create table emp;

1.4、创建表结构

1. 语法：

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

2. 示例：

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

表结构如图：

2.、表操作—数据类型

1. MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

2.1、数值类型

2.2、字符串类型

• char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关 。

• varchar是变长字符串，指定的长度为最大占用长度 。

• char和varchar相对来说，char的性能会更高些。

2.3、日期类型

2.4、创建表是字段类型如何选择

1. 在MySQL中创建表时，字段类型的选择应该基于数据的实际类型和预期用途。正确的数据类型选择对于数据库的性能、存储效率和数据完整性都至关重要。
2. 在选择字段类型时应考虑的因素：
   • 1、数据类型
     • 整数类型：如 TINYINT、SMALLINT、MEDIUMINT、INT（或 INTEGER）、BIGINT。选择时应考虑数据的大小范围和是否需要存储负数。
     • 浮点数类型：如 FLOAT、DOUBLE、DECIMAL。FLOAT 和 DOUBLE 用于存储近似值，而 DECIMAL 用于存储精确值（如货币），因为它可以指定小数点后的位数。
     • 字符串类型：如 CHAR、VARCHAR。CHAR 是固定长度的，而 VARCHAR 是可变长度的。选择时应考虑数据的最大长度和是否需要存储空字符串。
     • 日期和时间类型：如 DATE、TIME、DATETIME、TIMESTAMP、YEAR。这些类型用于存储日期和时间值，每种类型都有其特定的用途和存储格式。
     • 文本类型：如 TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT。用于存储大量文本数据，选择时应考虑数据的最大长度和性能考虑（长文本字段可能会影响查询性能）。
     • 二进制类型：如 BINARY、VARBINARY、BLOB（包括 TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB）。用于存储二进制数据，如图像、音频、视频等。
   • 2、存储需求：
     • 考虑每个字段所需存储空间的大小，以及整个表在数据库中的存储效率。
     • 使用合适的数据类型可以减少存储浪费和提高查询性能。
   • 3、数据完整性：
     • 选择正确的数据类型可以确保数据的准确性和一致性。
     • 例如，使用 DECIMAL 类型存储货币可以避免浮点数的舍入误差。
   • 4、性能考虑：
     • 某些数据类型（如 TEXT 和 BLOB）可能会影响查询性能，尤其是在涉及排序和索引时。
     • 选择合适的数据类型和索引策略可以优化查询性能。
   • 5、未来扩展：
     • 考虑数据的未来增长和变化，选择能够容纳预期数据增长的数据类型。

• 在选择字段类型时，务必仔细分析数据的实际需求和预期用途，以确保选择最适合的数据类型。如果不确定，可以先选择一个初步的数据类型，并在实际应用中根据需要进行调整和优化。

举例：设计一张员工信息表，要求如下:

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

CREATE TABLE EmployeeInfo (  ID INT AUTO_INCREMENT PRIMARY KEY,          -- 编号，纯数字，自动递增  EmployeeID VARCHAR(10) NOT NULL,             -- 员工工号，字符串类型，长度不超过10位  EmployeeName VARCHAR(10) NOT NULL,           -- 员工姓名，字符串类型，长度不超过10位  Gender CHAR(1) CHECK (Gender IN ('男', '女')), -- 性别，存储一个汉字，只能是'男'或'女'  Age INT UNSIGNED CHECK (Age >= 0),           -- 年龄，正常人年龄，不可能存储负数，使用无符号整数  IDCard CHAR(18) NOT NULL,                    -- 身份证号，二代身份证号均为18位，允许包含X字符  EntryDate DATE NOT NULL                      -- 入职时间，取值年月日即可  
);

3、表操作—修改

3.1、为表添加新字段

1. 语法：

ALTER TABLE 表名 ADD  字段名  类型 (长度)  [ COMMENT 注释 ]  [ 约束 ];-- 这里的约束就是什么非空约束，外键约束，主键约束等等

2. 示例：

-- 为emp表增加一个新的字段”昵称”为nickname，类型为varchar(10)ALTER TABLE emp ADD nickname varchar(10)  COMMENT '昵称'

3.2、为表修改字段类型（modify）

1. 语法：

ALTER TABLE 表名 MODIFY column  字段名  新数据类型 (长度);-- column可以省略
ALTER TABLE 表名 MODIFY  字段名  新数据类型 (长度);

2. 示例：

-- 为emp表的字段”昵称”为nickname的类型修改为varchar(255)ALTER TABLE emp MODIFY nickname VARCHAR(255)-- 为emp表的字段”昵称”为nickname的类型修改为varchar(255)并修改昵称ALTER TABLE emp MODIFY nickname VARCHAR(255) COMMENT '昵称2';

3.3、修改字段名（change）和字段类型

1. 语法：

ALTER TABLE 表名 CHANGE column  旧字段名  新字段名  类型 (长度)  [ COMMENT 注释 ]  [ 约束 ];-- column可以省略ALTER TABLE 表名 CHANGE  旧字段名  新字段名  类型 (长度)  [ COMMENT 注释 ]  [ 约束 ];

2. 示例：

-- 修改emp表中字段名为nickname的为nick，字段类型修改为varchar(100)
ALTER TABLE emp CHANGE  nickname  nick  VARCHAR(100);

3.4、修改表名

1. 语法：

 ALTER TABLE 表名 RENAME TO 新表名;

2. 示例：

-- 修改employeeinfo的表名为employeeALTER TABLE employeeinfo rename to employee;

3.5、删除字段

1. 语法：

 ALTER TABLE 表名 DROP  字段名;

2. 示例：

-- 删除emp表中的nick字段ALTER TABLE emp DROP nick;

4、表操作—删除

4.1、删除表

1. 语法：

DROP  TABLE [ IF  EXISTS ]  表名;

2. 示例：

-- 如果emp表存在，则删除emp表
DROP  TABLE IF  EXISTS  emp;

4.2、清空表数据

1. 语法：

TRUNCATE  TABLE 表名;

2. 示例：

-- 清空emp表的数据
TRUNCATE  TABLE emp;

在删除表的时候，表中的全部数据也都会被删除,表的结构依旧存在。

注意情况表数据还可以使用delete，但是delete属于DML语言了，而这里的truncate属于DDL（数据定义语言）

truncate与delete区别可以看一下这个