DDL

四、DDL

DDL主要是库和表的管理。

1 库的基本操作

1.1 增

# 简单语法:创建数据库
CREATE DATABASE 数据库名;
# 完整语法:创建数据库,指定它的默认字符集和排序规则,如果已存在则不创建
CREATE DATABASE [IF NOT EXISTS] 数据库名 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
# 示例:
CREATE DATABASE Books;

1.2 查

SHOW DATABASES;  # 查看所有数据库
SELECT DATABASE();  # 查看当前所在的库的名字
USE dbname;  # 切换到指定库下 

1.3 改

对于数据库名一般不更改,要修改数据库名可以先删除数据库,再创建新的数据库。

更改数据库的字符集和排序规则:

ALTER DATABASE 数据库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

1.4 删

DROP DATABASE [IF EXISTS] 数据库名;
# 示例:
DROP DATABASE IF EXISTS Books;

2 表的基本操作

2.1 增

CREATE TABLE 表名(
	列名1 列的类型[长度] [列的约束],
    列名2 列的类型[长度] [列的约束],
    ……
    列名n 列的类型[长度] [列的约束]
);

# 示例
CREATE TABLE student ( 
	id INT primary KEY auto_increment, 
	`name` VARCHAR(255)
);

2.2 查

SHOW TABLES;  # 查看当前库下面所有的表名
DESC tablename  # 查看某个表结构

2.3 改

ALTER TABLE 表名 add|drop|modify|change column 列名 [列类型 约束];
# 示例:
# 修改列名
ALTER TABLE student CHANGE COLUMN `name` stu_name VARCHAR ( 255 );
# 修改列的类型或约束
ALTER TABLE student MODIFY COLUMN `stu_name` VARCHAR ( 32 );
# 添加新列
ALTER TABLE student ADD COLUMN `salary` DOUBLE;
# 删除列
ALTER TABLE student DROP COLUMN `salary`;
# 修改表名
ALTER TABLE student RENAME TO `new_student`;

2.4 删

DROP TABLE IF EXISTS 表名;
# 示例:
DROP TABLE IF EXISTS `student`;

2.5 复制表

仅复制表结构,不复制数据

# 仅复制表的结构
CREATE TABLE 要复制的表名 LIKE 原表名;
# 示例
CREATE TABLE admin_back LIKE admin;

复制表的结构和数据:

# 复制表的结构和数据:
CREATE TABLE 要复制的表名 SELECT * FROM 原表名;
# 示例
CREATE TABLE admin_back SELECT * FROM admin;
# 本质就是利用子查询,因此可以做到复制部分数据或者结构,比如:
CREATE TABLE admin_back SELECT * FROM admin WHERE nation="中国";

3 数据类型和约束

3.1 数据类型

创建表的语法中,字段的数据类型主要有以下几种。

  • 数值型:整型、小数(定点数、浮点数)
  • 字符型:较短的文本(char、varchar)、较长的文本(text、longtext、blob)
  • 日期型:date、datetime、timestamp、time、year

数值类型:

  • 如果不设置无符号还是有符号,默认都是有符号的。如果需要设置无符号,需要添加unsigned关键字。
  • 如果插入的数值超过了整型的范围,MySQL 会报(Out of range for column ...)异常。
  • 如果不设置长度,会有默认的长度。
    DDL

字符类型:

  • char是定长的,可能会浪费空间,但是存取简单;varchar是变长的,节省空间,但是存取稍微麻烦一点。
  • varchar最多能存储65535个字节的数据,一般我们最多定义varchar(255),超过255会被转成text类型
    DDL

时间类型:
DDL

3.2 约束

为了保证数据的一致性和完整性,SQL 规范以约束的方式对表数据进行额外的条件限制。约束是表级的强制规定。可以在创建表的时候添加约束,或者在表创建之后数据添加之前添加约束。

有如下的六大约束:

  • NOT NULL,非空约束,规定某个字段不能为空。

  • UNIQUE,唯一约束,规定某个字段在整个表中是唯一的。

  • PRIMARY KEY,主键,非空且唯一。

  • CHECK,检查约束。

  • DEFAULT,默认值。

  • FOREIGN KEY,外键。

根据约束的作用范围,约束可以分为:

  • 列级约束:只能作用在一个列上,并且是根据列的定义后面。六大约束语法上支持,但是外键约束没有效果。
  • 表级约束:可以作用在多个列上,不和列在一起,而是单独定义。除了非空、默认约束,其他约束都支持。
CREATE TABLE 表名(
	列名 字段类型 列级约束,
    列名 字段类型 列级约束,
    表级约束(列名1,列名2)
);

# 示例:创建表的时候添加列级约束
CREATE TABLE stu_info (
	id INT PRIMARY KEY,# 主键
	stu_name VARCHAR ( 255 ) NOT NULL,# 非空
	gender CHAR ( 1 ) CHECK (
	gender IN ( '男', '女' )),# 检查约束
	seat INT UNIQUE,# 唯一约束
	age INT DEFAULT 18 # 默认约束
);
# 创建表的时候添加表级约束
CREATE TABLE major ( 
	id INT PRIMARY KEY, 
	`name` VARCHAR ( 255 ) 
);

在修改表时也可以添加约束,语法:

# 添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 列名 字段类型 新约束;
# 添加表级约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 (字段名) [外键的引用];

# 示例:
# 修改表时添加约束
ALTER TABLE stu_info MODIFY COLUMN id INT PRIMARY KEY; 
ALTER TABLE stu_info MODIFY COLUMN age INT DEFAULT 18; 
ALTER TABLE stu_info ADD CONSTRAINT fk_major_id FOREIGN KEY (major_id) REFERENCES major(id);
# 修改表时删除非空约束
ALTER TABLE stu_info MODIFY COLUMN stu_name  VARCHAR(255) ;
# 修改表时删除默认约束
ALTER TABLE stu_info MODIFY COLUMN age INT;
# 修改表时删除主键
ALTER TABLE stu_info DROP PRIMARY KEY;
# 修改表时删除主键
# 查询唯一键 show index from stu_info
ALTER TABLE stu_info DROP INDEX seat;
# 修改表时删除外键
ALTER TABLE stu_info DROP FOREIGN KEY fk_marjor_id;

3.3 主键和唯一的区别

保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 × 最多有1个 √,但是不推荐
唯一 可以有多个 √,但是不推荐

3.4 外键的特点

  • 外键的设置:

    • 一对一关系,外键字段建在哪里都可以,但是推荐你建在查询频率较高的一方
    • 一对多关系,外键字段建在多的那方
    • 多对多关系,需要建立第三张表
  • 要求从表的外键列的类型要和主表的关联列的类型要求一致或兼容,但是名称不要求一致。

  • 主表的关联列必须是一个 key(一般是主键或唯一键)。

  • 在删除时,先删除从表,再删除主表(可以设置级联删除)

4 标识列

标识列又称为自增长列,可以不用手动插入值,系统提供默认的序列值。特点如下:

  • 标识列必须不一定和主键搭配,但是要求是一个 key。

  • 一个表中至多有一个标识列。

  • 标识列的类型只能是数值型。

  • 标识列可以通过 set auto_increment_increment = 3; 设置步长,也可以通过手动插入值的方式设置标识列的起始值 INSERT INTO student(id,name) VALUES (5,'xxx')

示例,创建表的时候设置标识列:

CREATE TABLE student ( 
	id INT PRIMARY KEY auto_increment,  # 设置id自增
	`name` VARCHAR ( 255 ) 
);
# 新增数据
INSERT INTO student(`name`) VALUES ('xxx'); # 在插入的时候不需要指定主键id

也可以修改表时设置标识列

CREATE TABLE student ( 
	id INT PRIMARY KEY ,  # 创建时不设置自增
	`name` VARCHAR ( 255 ) 
);
# 修改表的时候设置标识列
ALTER TABLE student MODIFY COLUMN id PRIMARY KEY auto_increment; # 设置id自增
# 新增数据
INSERT INTO student(`name`) VALUES ('xxx'); # # 在插入的时候不需要指定主键id

删除标识列,比如删除自增:

# 创建表的时候设置标识列
CREATE TABLE student ( 
  id INT PRIMARY KEY auto_increment, 
  `name` VARCHAR ( 255 ) 
);
# 修改表时删除标识列
ALTER TABLE student MODIFY COLUMN id PRIMARY KEY;

5 一对多表关系的创建

示例:

# 学生表和班级表是一对多关系,外键在学生表建立
CREATE TABLE class(
	id int PRIMARY KEY auto_increment,
	NAME VARCHAR(255)
);

CREATE TABLE student ( 
	id INT PRIMARY KEY auto_increment, 
	name VARCHAR ( 255 ) ,
    class_id INT,
    FOREIGN KEY(class_id) REFERENCES class(id)
);

注意:

  • 先创建从表,再创建主表
  • 录入数据时,先录入从表,再录入主表
  • 删除数据时,先删除从表,再删除主表

这种操作方式比较费力,可以设置级联更新和级联删除。如下:

# 学生表和班级表是一对多关系,外键在学生表建立
CREATE TABLE class(
	id int PRIMARY KEY auto_increment,
	NAME VARCHAR(255)
);

CREATE TABLE student ( 
	id INT PRIMARY KEY auto_increment, 
	name VARCHAR ( 255 ) ,
    class_id INT,
    # cascade 级联操作,设置级联更新和级联删除
    FOREIGN KEY(class_id) REFERENCES class(id) ON UPDATE CASCADE ON DELETE CASCADE
);

6 多对多表关系创建

示例:

# 学生表和老师表是多对多关系,在第三张表创建关系
CREATE TABLE student(
	id int PRIMARY KEY auto_increment,
	NAME VARCHAR(255)
);

CREATE TABLE teacher(
	id int PRIMARY KEY auto_increment,
	NAME VARCHAR(255)
);

CREATE TABLE student2teacher(
	id int PRIMARY KEY auto_increment,
	teacher_id INT,
    student_id INT,
    FOREIGN KEY(teacher_id) REFERENCES teacher(id)
    ON UPDATE CASCADE 
    ON DELETE CASCADE,
    FOREIGN KEY(student_id) REFERENCES student(id)
    ON UPDATE CASCADE 
    ON DELETE CASCADE
);

7 一对一表关系创建

示例:

# 学生表和学生信息是一对一关系,外键在哪张表都可以创建,可以创建在查询频率高的表上
CREATE TABLE student(
	id int PRIMARY KEY auto_increment,
	NAME VARCHAR(255),
	details_id INT,
	FOREIGN KEY(details_id) REFERENCES student_details(id) 
	ON UPDATE CASCADE
	ON DELETE CASCADE
);

CREATE TABLE student_details(
	id int PRIMARY KEY auto_increment,
	telephone INT,
  address VARCHAR(255)
);
上一篇:关于 Property 'create' does not exist on type 'typeof import("axios") 的问题


下一篇:ORACLE 11g RAC+ASM 配置DG