数据定义语言(DDL)

lg
205 阅读8分钟

数据库管理

创建数据库

  • 语法：
CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];
  • [ ]中的内容是可选的
  • <数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。
  • IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
  • [DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
  • [DEFAULT] COLLATE：指定字符集的默认校对规则。
  • MySQL 的字符集（CHARACTER）和校对规则（COLLATION）是两个不同的概念。字符集是用来定义 MySQL 存储字符串的方式，校对规则定义了比较字符串的方式。

修改数据库

  • 语法：
ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}
  • ALTER DATABASE 用于更改数据库的全局特性。
  • 使用 ALTER DATABASE 需要获得数据库 ALTER 权限。
  • 数据库名称可以忽略，此时语句对应于默认数据库。
  • CHARACTER SET 子句用于更改默认的数据库字符集。

删除数据库

  • 语法：
DROP DATABASE [ IF EXISTS ] <数据库名>
  • <数据库名>：指定要删除的数据库名。
  • IF EXISTS：用于防止当数据库不存在时发生错误。
  • DROP DATABASE：删除数据库中的所有表格并同时删除数据库。
  • 如果要使用 DROP DATABASE，需要获得数据库 DROP 权限。

表管理

关系数据库的规范化

良好的数据库设计表现在以下几方面：

  • 访问效率高
  • 减少数据冗余，节省存储空间，便于进一步扩展
  • 可以使应用程序的开发变得更容易

关系数据库的规范化理论为：关系数据库中的每一个关系都要满足一定的规范。根据满足规范的条件不同，可以分为6个等级：第一范式（1NF）、第二范式（2NF）……第五范式（5NF）。其中，NF是Normal Form的缩写。一般情况下，只要把数据规范到第三范式标准就可以满足需要了。

第一范式（1NF）

  • 在一个关系中，消除重复字段，且各字段都是最小的逻辑存储单位。即，要满足原子性。
  • 第一范式是第二和第三范式的基础，是最基本的范式。第一范式包括下列指导原则。 （1）数据组的每个属性只可以包含一个值。 （2）关系中的每个数组必须包含相同数量的值。 （3）关系中的每个数组一定不能相同。
  • 在任何一个关系数据库中，第一范式是对关系模式的基本要求，不满足第一范式的数据库就不是关系型数据库。

第二范式（2NF）

  • 第二范式是在第一范式的基础上建立起来的，即满足第二范式必先满足第一范式（1NF）。
  • 第二范式要求数据库表中的每个实体（即各个记录行）必须可以被唯一地区分。
  • 为实现区分各行记录通常需要为表设置一个“区分列”，用以存储各个实体的唯一标识。这个唯一属性列被称为主关键字或主键。
  • 第二范式要求实体的属性完完全依赖于主关键字，即不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。

第三范式（3NF）

  • 第三范式是在第二范式的基础上建立起来的，即满足第三范式必先满足第二范式。
  • 第三范式要求关系表不存在非关键字列对任意候选关键字列的传递函数依赖，也就是说，第三范式要求一个关系表中不包含已在其他表中包含的非主关键字信息。
  • 除主键外，其他字段必须依赖主键。

表管理语句

创建表

  • 语法：
CREATE TABLE 表名称
(
列名称1 数据类型 [(长度) 约束],
列名称2 数据类型 [(长度) 约束],
列名称3 数据类型 [(长度) 约束],
....
)
  • 常用数据类型
数据类型描述
tinyint(m)1个字节 范围(-128~127)
smallint(m)2个字节 范围(-32768~32767)
mediumint(m)3个字节 范围(-8388608~8388607)
int(m)4个字节 范围(-2147483648~2147483647)
bigint(m)8个字节 范围(+-9.22*10的18次方)
float(m,d)单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)双精度浮点型 16位精度(8字节) m总个数，d小数位
decimal(m,d)m表示十进制数字总的个数，d表示小数点后面数字的位数。常用于货币
char(n)固定长度，最多255个字符
varchar(n)不固定长度，最多65535个字符
tinytext可变长度，最多255个字符
text可变长度，最多65535个字符
mediumtext可变长度，最多2的24次方-1个字符
longtext可变长度，最多2的32次方-1个字符
date日期 '2008-12-2'
time时间 '12:25:36'
datetime日期时间 '2008-12-2 22:06:44'
timestamp自动存储记录修改时间
enum(选项1, 选项2, ...)单选字符串数据类型，适合存储表单界面中的“单选值”
set(选项1,选项2, ...)多选字符串数据类型，适合存储表单界面的“多选值”。
  • 示例：
# 创建数据库mydb
mysql> create database mydb default charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb;
Database changed

# 创建部门表
mysql> create table departments (
    ->   id int,
    ->   dept_name varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

修改表

修改列名
  • 语法：
ALTER TABLE 表
CHANGE [COLUMN] 列表 数据类型
  • 示例：
mysql> alter table departments
    -> change id dept_id int;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
修改列的类型或约束
  • 语法：
ALTER TABLE 表
MODIFY [COLUMN] 列名 类型
  • 示例：
mysql> alter table departments
    -> modify dept_name varchar(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
添加新列
  • 语法：
ALTER TABLE 表
ADD [COLUMN] 列名 类型
  • 示例：
mysql> alter table departments
    -> add manager_id int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
删除列
  • 语法：
ALTER TABLE 表
DROP [COLUMN] 列名
  • 示例：
mysql> alter table departments
    -> drop manager_id;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
修改表名
  • 语法：
ALTER TABLE 表名RENAME TO 新表名
  • 示例：
mysql> alter table departments    -> rename to depts;Query OK, 0 rows affected (0.00 sec)

删除表

  • 语法：
DROP TABLE [IF EXISTS] 表名
  • 示例：
mysql> drop table depts;Query OK, 0 rows affected (0.01 sec)

表复制

仅复制表结构
  • 语法：
CREATE TABLE 待创建的表名 LIKE 已有表名
  • 示例：
mysql> create table departments like nsd2021.departments;Query OK, 0 rows affected (0.01 sec)
复制表结构及数据
  • 语法：
CREATE TABLE 待创建的表名SELECT 字段, ... FROM 已有表名
  • 示例：
mysql> create table departments2    -> select * from nsd2021.departments;Query OK, 13 rows affected (0.01 sec)Records: 13  Duplicates: 0  Warnings: 0

约束

  • 约束是一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性。
  • 创建表时可以添加约束
  • 修改表时可以添加约束

约束分类

  • PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。
  • NOT NULL ：非空，用于保证该字段的值不能为空。
  • DEFAULT：默认值，用于保证该字段有默认值。
  • UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空。
  • FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。

约束可应用在列级或表级。列表所有约束均支持，但外键约束没有效果；表级约束可以支持主键、唯一、外键约束。

约束应用

列级应用

  • 创建表时使用约束
mysql> create table employees(    ->   employee_id int primary key,       -- 主键约束    ->   name varchar(20) not null,         -- 非空约束    ->   gender enum('男', '女'),    ->   email varchar(20) unique,          -- 唯一约束    ->   nation varchar(10) default '汉族'  -- 默认值约束    -> );Query OK, 0 rows affected (0.00 sec)

表级约束

  • 创建表时使用约束
mysql> create table employees2 (    ->   employee_id int,    ->   name varchar(20),    ->   email varchar(20),    ->   dept_id int,    ->   primary key (employee_id),   -- 主键    ->   unique (email),              -- 唯一    ->   foreign key (dept_id) references departments(dept_id)  -- 外键    -> );    # 查看约束mysql> select * from information_schema.table_constraints  where table_name='employees2' \G
  • 自定义约束名称
mysql> create table employees3 (    ->   employee_id int,    ->   name varchar(20),    ->   dept_id int,    ->   constraint pk primary key(employee_id),   # 不报错，不生效    ->   constraint fk_employees3_departments foreign key(dept_id) references departments(dept_id)    -> );

删除约束

  • 语法：
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>
  • 示例：
mysql> alter table employees3    -> drop foreign key fk_employees3_departments;

例：创建员工数据库的三张表

# 创建部门表create table departments(    dept_id int AUTO_INCREMENT PRIMARY KEY,    dept_name VARCHAR(10) UNIQUE);# 创建员工表create table employees(    employee_id INT auto_increment primary key,    name VARCHAR(10) not null,    hire_date DATE,    birth_date DATE,    email varchar(25) UNIQUE,    phone_number varchar(11),    dept_id int,    FOREIGN KEY(dept_id) references departments(dept_id));# 创建工资表create table salary(    id int AUTO_INCREMENT PRIMARY KEY,    date DATE,    employee_id int,    basic int,    bonus int,    FOREIGN KEY(employee_id) references employees(employee_id))
avatar
文章
阅读
粉丝