MySQL —DDL数据定义语言

发布于 2024-01-12  251 次阅读

SQL

SQL即结构化查询语言，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。从功能上来看，SQL分为DQL（数据查询语言）、DML（数据操作语言）、DDL（数据定义语言）、TCL（事务控制语言）、DCL（数据控制语言）。SQL常用操作为增删改查，即大家常说的CURD。

SQL分类

DDL 数据定义语言

数据库操作

数据库的创建(CREATE)

语法:

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

用例：

  • 创建一个名为testdb的数据库并查看；

    CREATE DATABASE testdb;
SHOW DATABASES;

    image-20240108190303376

SHOW DATABASE的作用就是查看已有数据库。

关于数据库名：

  1. 数据库名不能为中文

  2. 数据库名必须唯一

数据库的删除(DROP)

语法:

DROP DATABASE [IF EXISTS] 数据库名;

用例：

  • 删除刚才创建的testdb

    DROP DATABASE testdb;
SHOW DATABASES;

    image-20240108191038932

数据表操作

MySQL数据类型

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

常用的数值类型有整数（INT）、浮点数（FLOAT）、双精度浮点数（DOUBLE），全部的数值类型如下表：

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 Bytes (-128，127) (0，255) 小整数值
SMALLINT 2 Bytes (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 Bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 Bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 Bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 Bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值
DOUBLE 8 Bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值
日期和时间类型

常用的日期和时间类型有DATE、TIME、DATETIME和YEAR，具体看下表：

类型 大小 ( bytes) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' YYYY-MM-DD hh:mm:ss 混合日期和时间值
TIMESTAMP 4 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYY-MM-DD hh:mm:ss 混合日期和时间值，时间戳
字符串类型

常用的字符串类型有CHAR、VARCHAR、TEXT，全部的字符串类型如下：

类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

关于CHAR、VARCHAR和TEXT：

  1. CHAR(n) 和 VARCHAR(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

  2. CHAR是固定长度的，长度范围为0~255，当存储一个长度不足的数据时，会使用空格填满剩余字节。

  3. VARCHAR的长度是可变的。

  4. TEXT不可以有默认值。

综上：在创建字符类型时，知道固定长度的使用CHAR，其他情况尽量使用VARCHAR。

表的创建(CREATE)

所有针对表进行的操作必须先使用USE指定数据库，创建表的语法如下：

CREATE TABLE 表名(
    字段1 数据类型 [字段约束],
    字段2 数据类型 [字段约束],
    .....
    字段N 数据类型 [字段约束]
)

关于表：

  1. 同一数据库下表名必须唯一
  2. 表名可以为中文
  3. 若表名涉及到关键字，创建及使用时必须加上反引号

MySQL中有以下几种约束：

  • NOT NULL 非空约束 - 指示某列不能存储 NULL 值。
  • UNIQUE 唯一约束 - 保证某列的每行必须有唯一的值。
  • PRIMARY KEY 主键约束 - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
  • FOREIGN KEY 外键约束- 保证一个表中的数据匹配另一个表中的值的参照完整性。
  • DEFAULT 默认约束 - 规定没有给列赋值时的默认值。
  • AUTO_INCREMENT 自增约束 -规定该列的值自动增长，通常配合主键使用。

用例:

  • 创建testdb数据库，并在数据库中新建drop数据表，指定字段名为学号、姓名、性别、年龄、电话号码和地址。
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE `drop`(
    学号 varchar(30) PRIMARY KEY,
    姓名 varchar(10) NOT NULL,
    性别 varchar(4)  DEFAULT "男",
    年龄 int,
    电话号码 varchar(30),
    地址 varchar(100)
);

以上SQL代码中，我们新建了一张drop表，由于drop为SQL中的关键字，因此我们将其放在反引号内，告诉MySQL这是一个表名。建表语句内每列以英文逗号（,）进行分隔，列名后跟着字符类型，由于varchar()是字符类型，因此需要指定长度，保证该列所存储的数据长度不要超过指定的长度即可。约束写于字段类型之后，没有约束可以不写，如果某一列有多个约束，就以空格分割。

使用DESC 表名来查看数据表结构。

image-20240109092855552