MySQL_DDL数据定义语言_刘锋的博客

DDL数据定义语言

库和表的管理

一、库的管理

创建

创建books库

CREATE DATABASE books
#容错性处理
CREATE DATABASE IF NOT EXISTS books

创建库的通用写法：（先删除在创建）

DROP DATABASE IF EXISTS 库名;
CREATE DATABASE 库名;

修改

库名的修改没有专门的sql语句，不能修改，可以更改库的字符据

ALTER DATABASE books CHARACTER SET gbk

删除

DROP DATABASE books;

# 容错性出路
DROP DATABASE IF EXISTS books;

二、表的管理

创建

语法

create table 表名（

列名 列的类型(长度) 约束条件，

列名 列的类型(长度) 约束条件，

列名 列的类型(长度) 约束条件，

…

列名 列的类型(长度) 约束条件，

）

CREATE TABLE book (
		id INT,
		bName VARCHAR(20),
		price DOUBLE,
		author VARCHAR(20))

创建表的通用写法：（先删除后创建）

DROP TABLE IF EXISTS book;
CREATE TABLE book;

修改

1、修改列名

ALTER TABLE book 
CHANGE COLUMN bName b_name VARCHAR(20);

2、修改列的列的类型或者约束

# 修改列的类型
ALTER TABLE book 
MODIFY COLUMN b_name VARCHAR(15)

3、添加新列

ALTER TABLE bookADD COLUMN annual DOUBLE;

# 在某列的后边添加新的列
ALTER TABLE bookADD COLUMN annual DOUBLE AFTER bName;

4、删除列

ALTER TABLE bookDROP COLUMN annual;

5、修改表名

ALTER TABLE bookRENAME TO books

删除

DROP TABLE books;
# 容错处理
DROP TABLE IF EXISTS books;

复制

1.仅仅复制表的结构

CREATE TABLE copy_boook 
LIKE book;

2、复制表的结构和数据

CREATE TABLE copy_book2SELECT * 
FROM book;

3、只复制部分数据

CREATE TABLE copy_book3SELECT book.`author` 
FROM bookWHERE book.`id` = 2;

4、复制部分结构

CREATE TABLE copy_book3SELECT book.`author` 
FROM bookWHERE 1=2;# 添加一个恒不成立的条件判断

三、常见的数据类型

数值型

1、整形

tinyint、smallint、mediumint、int/integer、bigint

1 2 3 4 8

特点：

①都可以设置为无符号和有符号，默认是有符号的，用unsigned来设置无符号

②如果超出范围会报异常、插入临界值

③长度可以不指定，会有一个默认的长度，长度不够左边用0田中

2、浮点型

定点数

decimal（M，D）

浮点数

double（M，D）

float（M，D）

特点：

①M表示整数+小数，D表示小数的位数

②如果超出范围，就会报异常，并且插入临界值

③M和D都可以省略，但是对于定点数来说，M默认是10，D默认是0

④精度高的是定点数

字符型

char，varchar，binary，varbinary，enum，set，text，blob

char：固定长度的字符，写法为char（M），最大长度不可以超过M，M可以省略，M的默认是1

varchar：可变长度的字符，写法为varchar（M），最大长度不可以超过M，M不可以省略

日期型

year年

data日期

timedata日期+时间 8

timestamp日期+时间 4 受到时区，语法模式，版本的影响

四、常见的约束

含义：约束在创建字段的时候会在 后面添加约束，用于限制行或者列的数据的，为了保证数据的准确和可靠性，保证数据的一致性。

---

分类（六大约束）

NOT NULL 非空约束

保障该字段的值不能为空

DEFAULT 默认约束

保证该字段的值有默认值

PRIMARY KEY 主键约束

保证该字段的值是不重复（具有唯一性）且非空的

UNIQUE 唯一约束

保证该字段的值是唯一的

CHECK 检查约束

在MySQL中是不支持的

FOREIGN KEY 外键约束

用于限制两个表之间的关系，用于保证该字段的值必须来自主表的关联列的值

在从表添加外键约束，用于引用主表中的某列的值

添加约束的时间

一、创建表的时候添加

列级约束

直接在字段名和类型后面追加 约束类型即可，check和外键约束不支持

CREATE TABLE students (	id INT PRIMARY KEY ,# 主键	
stuName VARCHAR(20) NOT NULL,# 非空	
gender CHAR CHECK (gender = '男' OR gender = '女'),# 检查	
seat INT UNIQUE,# 唯一	
age INT DEFAULT 18# 默认	
# majorId references major(id) #外键（Mysql不可用，Orcle可以用）	)

查看表结构

在各个字段的下面

【constraint 约束名】 约束类型（字段名） 【references 表名 （列名）】

DESC students;
# 查看索引
SHOW INDEX FROM students;

表级约束（constraint）

CREATE TABLE students(	id INT,	stuName VARCHAR(20),	
gender CHAR(1),	
seat INT ,	
age INT,	
majorid INT,		
CONSTRAINT pk_id PRIMARY KEY(id),	
CONSTRAINT uq_seat UNIQUE (seat),	
CONSTRAINT ck CHECK(gender = "nan" OR gender = "nv"),	
CONSTRAINT fk_stu_majorid FOREIGN KEY (majorid) REFERENCES major(id)	)

主表和从表：

主表（父表）
在数据库中建立的表格即Table，其中存在主键(primary key)用于与其它表相关联，并且作为在主表中的唯一性标识。

从表（子表）
以主表的主键（primary key）值为外键 (Foreign Key)的表，可以通过外键与主表进行关联查询。从表与主表通过外键进行关联查询。

关系及用法概述
从表数据依赖于主表，一般最后查询数据时把主表与从表进行关联查询。

CREATE TABLE students(	
id INT,	stuName VARCHAR(20),	
gender CHAR(1),	
seat INT ,	
age INT,	
majorid INT,		
PRIMARY KEY(id),	
UNIQUE (seat),	
FOREIGN KEY (majorid) REFERENCES major(id))

通用的写法：

CREATE TABLE students(	
id INT PRIMARY KEY,	
stuName VARCHAR(20) NOT NULL,	
gender CHAR(1),	
seat INT UNIQUE,	
age INT DEFAULT 18,	
majorid INT,	
CONSTRAINT fk_majorid FOREIGN KEY(majorid) REFERENCES major(id))

主键和唯一的对比：

1、都可以保证唯一性

2、主键不能为空，唯一可以为空，但是只能是一个值为null

3、主键只能有一个，唯一可以有多个

4、都可以组合列（两个列或者多个列可以组成一个主键或者为唯一键），但是不推荐使用

外键的特点：

1、在从表上设置外键关系

2、从表的外键列的类型和主表的关联列的类型要求是一致的或者是兼容的

3、主表的关联列必须是一个key（主键、唯一），一般是主键

4、插入数据的时候，应该先添加主表的信息，在删除的时候，应该先删除从表的信息

二、修改表的时候添加

添加非空约束

ALTER TABLE students MODIFY COLUMN stuName VARCHAR(20) NOT NULL;

添加 默认约束

ALTER TABLE students MODIFY COLUMN age INT DEFAULT 18; 

添加主键

# 列级约束的添加方式
ALTER TABLE students MODIFY COLUMN id INT PRIMARY KEY;
# 表级约束的添加方式
ALTER TABLE students ADD PRIMARY KEY(id);

添加唯一键

# 表级
ALTER TABLE students MODIFY COLUMN seat INT UNIQUE;
# 列级
ALTER TABLE students ADD UNIQUE(seat);

添加外键

ALTER TABLE students ADD CONSTRAINT fk_majorid FOREIGN KEY(majorid) REFERENCES major(id); 

约束添加的分类

一、列级约束

六大约束语法上都支持，但是外键约束没有效果

二、表级约束

除了非空、默认，其他的都支持

删除约束

删除非空约束

ALTER TABLE studentsMODIFY COLUMN stuName VARCHAR(20) NULL;

删除默认约束

ALTER TABLE students MODIFY COLUMN age INT ;

删除主键

ALTER TABLE students DROP PRIMARY KEY;

删除唯一

ALTER TABLE students DROP INDEX seat;

删除外键

ALTER TABLE students DROP FOREIGN KEY fk_majorid;
# 设置级联删除（在删除主表的数据的时候，子表所引用的主表的数据也一起删除）
ALTER TABLE students DROP FOREIGN KEY fk_majorid ON DELETE CASCADE;
#设置级联置空
ALTER TABLE students DROP FOREIGN KEY fk_majorid ON DELETE SET NULL;

五、标志列（自增长列）

特点：

1、标识列不一定必须是主键，是一个key就可以

2、仅仅只能添加一个自增长列

3、标识列的类型只能是数值型

4、可以设置步长，可以通过手动插入值来设置起始值

创建表的时候设置标识列

CREATE TABLE tab_identity(	id INT PRIMARY KEY AUTO_INCREMENT,	NAME VARCHAR(20))

AUTO_INCREMENT设置起始值没有效果

但是设置步长是有效果的

# 查看属性值
SHOW VARIABLES LIKE "%auto_increment%"
# 设置步长
SET auto_increment_increment = 10;
# 删除信息以及表的自增索引
TRUNCATE TABLE tab_identity;

修改表时设置标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT AUTO_INCREMENT;

删除自增

ALTER TABLE tab_identity MODIFY COLUMN id INT