MySQL DDL语句

最新推荐文章于 2024-04-29 18:25:19 发布

毒者

最新推荐文章于 2024-04-29 18:25:19 发布

阅读量916

点赞数 17

文章标签： mysql 数据库学习

本文链接：https://blog.csdn.net/zyb0419/article/details/135134087

版权

DDL 数据定义语言

Data Definition Language
数据定义语言，用来定义数据库对象(数据库，表，字段)

1、操作数据库

1、创建数据库

-- 语法
CREATE DATABASE 数据库名;
# 或
CREATE SCHEMA 数据库名;
-- ---------------------
-- 实例
CREATE DATABASE mydb;
CREATE SCHEMA mydb;

2、使用数据库

-- 语法
USE 数据库名;

-- 实例
USE mydb;

3、查看数据库
-- 语法
SHOW DATABASES;

4、删除数据库

-- 语法
DROP DATABASE 数据库名;
# 或
DROP SCHEMA 数据库名;
-- -------------------
-- 实例
DROP DATABASE mydb;
DROP SCHEMA mydb;

5、查看建库语句

-- 语法
SHOW CREATE DATABASE 数据库名;
-- 实例
SHOW CREATE DATABASE mydb;

2、操作表

1、创建表

-- 语法
CREATE TABLE 表名(
     字段名 列类型 [属性] [索引] [注释],
     字段名 列类型 [属性] [索引] [注释],
     .....
     字段名 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]
-- ---------------------------------------
-- 实例
CREATE TABLE student(
    id   char(10) PRIMARY KEY COMMENT '学号',
    name varchar(50) NOT NULL COMMENT '姓名',
    age  smallint UNSIGNED COMMENT '年龄',
    sex  char(1) COMMENT '性别'
) COMMENT '学生表';

2、查看表
-- 语法
SHOW TABLES;

3、修改表名

-- 语法
ALTER TABLE 旧表名 RENAME TO 新表名;
# 或
ALTER TABLE 旧表名 RENAME AS 新表名;
-- --------------------------------
-- 实例
ALTER TABLE t1 RENAME TO t2;
ALTER TABLE t1 RENAME AS t2;

4、删除表

-- 语法
DROP TABLE 数据库名;
-- ----------------
-- 实例
DROP TABLE student;

5、查看建表语句

-- 语法
SHOW CREATE TABLE 表名;
-- 实例
SHOW CREATE TABLE student;

6、修改表结构

完整性约束如果涉及表的多个字段，则必须定义在表级上，否则表级和列级都可以

6.1、字段设置

1、添加字段

-- 格式
ALTER TABLE 表名 ADD 字段名 类型(长度) 约束;

-- 示例 
ALTER TABLE student ADD Sname varchar(20) NOT NULL;

将字段添加到某个字段后面

-- 格式
ALTER TABLE 表名 ADD 字段名 类型(长度) 约束 AFTER 某个字段;

-- 示例
ALTER TABLE student ADD Sage smallint unsigned AFTER Sname;

将字段添加为第一个字段

-- 格式
ALTER TABLE 表名 ADD 字段名 类型(长度) 约束 FIRST;

-- 示例
ALTER TABLE student ADD Sno char(10) PRIMARY KEY FIRST;

2、修改字段

（1）修改字段的类型长度及约束

-- 格式
ALTER TABLE 表名 MODIFY 字段名 类型(长度) 约束;

-- 示例
ALTER TABLE student MODIFY Sname varchar(50) NULL;

（2）修改字段名和字段的类型长度及约束

-- 格式
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) 约束;

-- 示例
ALTER TABLE student CHANGE Sname stuName varchar(50) UNIQUE;

（3）修改字段的排列位置

-- 格式
ALTER TABLE 表名 MODIFY 字段1 数据类型(长度) 约束 FIRST / (AFTER 字段2);
-- FIRST表示将 字段1 调整到第一列
-- AFTER 表示将 字段1 移动到 字段2 之后

-- 示例
-- FIRST 表示将 Sno 调整到第一列
ALTER TABLE student MODIFY Sno char(10) PRIMARY KEY FIRST;
-- AFTER 表示将 Ssex 移动到 Sage 之后
ALTER TABLE student MODIFY Ssex char(1) AFTER Sage;

区别：modify只能修改字段的类型长度及约束，而change可以修改字段名和字段的类型长度及约束

3、删除字段

-- 格式
ALTER TABLE 表名 DROP 字段名;

-- 示例
ALTER TABLE student DROP Sage;

6.2、约束

完整性约束

（1）添加完整性约束

-- 格式
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束条件;
注：完整性约束条件包括NOT NULL、UNIQUE、PRIMARY KEY短语、FOREIGN KEY短语、CHECK短语等。

2、删除完整性约束

要修改完整性约束可以先删除原来的约束条件，再增加新的约束条件
-- 格式
ALTER TABLE 表名 DROP CONSTRAINT 约束名;

-- 示例
ALTER TABLE student DROP CONSTRAINT c1;

6.2.1、主码(Primary Key)

一个表只能有一个PRIMARY KEY约束（即一个主键），且PRIMARY KEY约束中的列默认具有UNIQUE 和 NOT NULL 约束，即主码唯一且非空。

（1）查看主码

-- 格式
DESC 表名;
DESCRIBE 表名;
#   或
SHOW CREATE 表名;

-- 示例
DESC student;
DESCRIBE student;

SHOW CREATE student;

（2）添加主码

a、建表时添加主码

-- 情况一：单个字段作为主码
CREATE TABLE student (
    Sno char(10) PRIMARY KEY COMMENT '学号' 
)COMMENT '学生表';

-- 情况二：多个字段组合作为主码
CREATE TABLE sc (
    Sno char(10) PRIMARY KEY COMMENT '学号',
    Cno varchar(10) COMMENT '课程号',
    grade smallint unsigned COMMENT '成绩',
    PRIMARY KEY (Sno,Cno)
)COMMENT '选课表';

情况一属于列级完整性约束，情况二属于表级完整性约束。涉及多个字段只能要用表级完整性约束。

b、建表后添加主码

方式一：表级添加

-- 格式
ALTER TABLE 表名 ADD PRIMARY KEY (字段名);
#    或
ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY (字段名);

-- 示例
ALTER TABLE student ADD PRIMARY KEY (Sno);

ALTER TABLE student ADD CONSTRAINT Sno_pKey PRIMARY KEY (Sno);

方式二：列级添加

-- 格式
ALTER TABLE 表名 MODIFY 列名 列类型 PRIMARY KEY;
#   或
ALTER TABLE 表名 CHANGE 列名 列名 列类型 PRIMARY KEY;

-- 示例
ALTER TABLE student MODIFY Sno char(10) PRIMARY KEY;

ALTER TABLE student CHANGE Sno Sno char(10) PRIMARY KEY;

（3）删除主码

主码不能修改，如果要修改主码只能先删除现有的主码，然后再添加新的主码。

-- 格式
ALTER TABLE 表名 DROP PRIMARY KEY;

-- 示例
ALTER TABLE student DROP PRIMARY KEY;

6.2.2、外码(Foreign Key)

外键是一种数据库表的约束，它用于确保数据的一致性。在关系数据库中，表中某字段的值来自于另外一张表的主键或唯一键的列，该字段被称为“外键”。该表称为参考表，另外一张表称为被参考表。
外键是相对于参考表来说的。
一个表可以有多个外键。
外键列类型需要与引用列类型一致。
外键列的值必须是主表中引用列的值或者 NULL。

（1）查看外键

-- 格式
SHOW CREATE TABLE 表名;

-- 示例
SHOW CREATE TABLE sc;

（2）添加外键

a、建表时添加外键

-- 示例
CREATE TABLE sc (
    Sno char(10) COMMENT '学号',
    Cno varchar(10) COMMENT '课程号',
    grade smallint unsigned COMMENT '成绩',
    PRIMARY KEY (Sno,Cno),
    FOREIGN KEY (Sno) REFERENCES student (Sno),
    FOREIGN KEY (Cno) REFERENCES course (Cno)
)COMMENT '选课表';

b、建表后添加外键

-- 格式
ALTER TABLE 表名 ADD FOREIGN KEY (字段名) REFERENCES 被参照表 (主码);
#   或
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (字段名) REFERENCES 被参照表 (主码);

-- 示例
ALTER TABLE sc ADD FOREIGN KEY (Sno) REFERENCES student (Sno);

ALTER TABLE sc ADD CONSTRAINT Sno_fKey FOREIGN KEY (Sno) REFERENCES student (Sno);

（3）删除外键

外键不可直接修改，只能先删除后新增。

若不知道外键名可以先 “show create table 表名” 来查看外键名

-- 格式
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

-- 示例
ALTER TABLE sc DROP FOREIGN KEY sc_ibfk_1;

（4）参照完整性检查和违约处理

对被参照表和参照表进行增删改操作时有可能破坏参照完整性，必须进行检查。

对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值。

参照完整性违约处理

拒绝（NO ACTION），不允许该操作执行。该策略一般设置为默认策略
级联（CASCADE），当删除或修改被参照表（student）的一个元组造成了与参照表（SC）的不一致，则删除或修改参照表中的所有造成不一致的元组
设置为空值（SET NULL），当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。

-- 示例
CREATE TABLE sc (
    Sno char(10) COMMENT '学号',
    Cno varchar(10) COMMENT '课程号',
    grade smallint unsigned COMMENT '成绩',
    PRIMARY KEY (Sno,Cno),
    FOREIGN KEY (Sno) REFERENCES student (Sno)
    ON UPDATE NO ACTION  --当更新student表中的元组造成了与sc表不一致时拒绝更新
    ON DELETE NO ACTION, --当删除student表中的元组造成了与sc表不一致时拒绝删除
    FOREIGN KEY (Cno) REFERENCES course (Cno)
    ON UPDATE CASCADE  --当删除或修改被参照表（student）的一个元组造成了与参照表（sc）不一致时，
    ON DELETE CASCADE  --则删除或修改参照表中的所有造成不一致的元组
)COMMENT '选课表';

6.2.3、添加唯一键(Unique或Unique Key)

（1）查看唯一键

-- 格式
DESC 表名;
DESCRIBE 表名;
#   或
SHOW CREATE 表名;

-- 示例
DESC student;
DESCRIBE student;

SHOW CREATE student;

（2）添加唯一键

a、建表时添加唯一键

-- 示例一
CREATE TABLE course (
    Cno     varchar(10) UNIQUE COMMENT '课程号',
    Cname   varchar(50) COMMENT '课程名',
    Ccredit smallint UNSIGNED COMMENT '学分'
);

-- 示例二
CREATE TABLE course (
    Cno     varchar(10) COMMENT '课程号',
    Cname   varchar(50) COMMENT '课程名',
    Ccredit smallint UNSIGNED COMMENT '学分',
    UNIQUE (Cno)
);

b、建表后添加唯一键

方式一：表级添加

-- 格式
ALTER TABLE 表名 ADD UNIQUE (字段名);
#   或
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);

-- 示例
ALTER TABLE student ADD UNIQUE (Sname);

ALTER TABLE student ADD CONSTRAINT Sname_unique UNIQUE (Sname);

方式二：列级添加

-- 格式
ALTER TABLE 表名 MODIFY 列名 列类型 UNIQUE;
#   或
ALTER TABLE 表名 CHANGE 列名 列名 列类型 UNIQUE;

-- 示例
ALTER TABLE student MODIFY Sname varchar(50) UNIQUE;

ALTER TABLE student CHANGE Sname Sname varchar(50) UNIQUE;

（3）删除唯一键

如果想更改唯一键的字段，需要先删除原来的唯一键，然后添加新的唯一键。

如果没有设置约束名称，名称默认是字段名

-- 格式
ALTER TABLE 表名 DROP INDEX index_name; 删除唯一键，其中 index_name 是要删除的唯一键的名称。

-- 示例
ALTER TABLE course DROP INDEX Cno;

6.2.4、非空约束(NOT NULL)

（1）查看非空约束

-- 格式
DESC 表名;
DESCRIBE 表名;
#   或
SHOW CREATE 表名;

-- 示例
DESC student;
DESCRIBE student;

SHOW CREATE student;

（2）添加非空约束

a、建表时添加非空约束

-- 示例
CREATE TABLE student (
    Sname varchar(50) NOT NULL
);

b、建表后添加非空约束

方式一：列级添加

-- 格式
ALTER TABLE 表名 MODIFY 列名 列类型 NOT NULL;
#   或
ALTER TABLE 表名 CHANGE 列名 列名 列类型 NOT NULL;

-- 示例
ALTER TABLE student MODIFY Sname varchar(50) NOT NULL;
ALTER TABLE student CHANGE Sname Sname varchar(50) NOT NULL;

方式二：表级添加

-- 格式
ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK ( 列名 IS NOT NULL );

-- 示例
ALTER TABLE student ADD CONSTRAINT c1 CHECK ( Sname IS NOT NULL );

（3）删除非空约束

-- 格式
ALTER TABLE 表名 MODIFY 列名 列类型 NULL;
#   或
ALTER TABLE 表名 CHANGE 列名 列名 列类型 NULL;

-- 示例
ALTER TABLE student MODIFY Sname varchar(50) NULL;
ALTER TABLE student CHANGE Sname Sname varchar(50) NULL;

6.2.5、默认值约束(Default)

（1）查看默认值

-- 格式
DESC 表名;
DESCRIBE 表名;
#   或
SHOW CREATE 表名;

-- 示例
DESC student;
DESCRIBE student;

SHOW CREATE student;

（2）添加默认值

a、建表时添加默认值

-- 示例
CREATE TABLE student (
    Sname varchar(50) DEFAULT '佚名'
);

b、建表后添加默认值

-- 格式
ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT 默认值;
#   或
ALTER TABLE 表名 MODIFY 列名 列类型 DEFAULT 默认值;
#   或
ALTER TABLE 表名 CHANGE 列名 列名 列类型 DEFAULT 默认值;

-- 示例
ALTER TABLE student ALTER Sname SET DEFAULT '佚名';
ALTER TABLE student MODIFY Sname varchar(50) DEFAULT '佚名';
ALTER TABLE student CHANGE Sname Sname varchar(50) DEFAULT '佚名';

（3）修改默认值

-- 格式
ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT 默认值;
#   或
ALTER TABLE 表名 MODIFY 列名 列类型 DEFAULT 默认值;
#   或
ALTER TABLE 表名 CHANGE 列名 列名 列类型 DEFAULT 默认值;

-- 示例
ALTER TABLE student ALTER Sname SET DEFAULT '佚名2';
ALTER TABLE student MODIFY Sname varchar(50) DEFAULT '佚名2';
ALTER TABLE student CHANGE Sname Sname varchar(50) DEFAULT '佚名2';

（4）删除默认值

-- 格式
ALTER TABLE 表名 ALTER COLUMN 字段名 DROP DEFAULT;

--示例
ALTER TABLE student1 ALTER COLUMN Sname DROP DEFAULT;

6.2.6、检查约束(Check)

（1）查看检查约束

-- 格式
DESC 表名;
DESCRIBE 表名;
#   或
SHOW CREATE 表名;

-- 示例
DESC student;
DESCRIBE student;

SHOW CREATE student;

（2）添加检查约束

a、建表时添加非空约束

-- 示例
CREATE TABLE student (
    Sno char(10),
    Ssex char(1) CHECK ( Ssex IN ('男','女') )
);

b、建表后添加非空约束

方式一：列级添加

-- 格式
ALTER TABLE 表名 MODIFY 字段名 数据类型 CHECK( 检查条件 );
ALTER TABLE 表名 CHANGE 字段名 字段名 数据类型 CHECK( 检查条件 );

--示例
ALTER TABLE student MODIFY Sage smallint unsigned CHECK( Sage > 0 );
ALTER TABLE student CHANGE Sage Sage smallint unsigned CHECK( Sage > 0 );

方式二：表级添加

-- 格式
ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK ( 约束条件 );

-- 示例
#年龄大于0
ALTER TABLE student ADD CONSTRAINT c1 CHECK ( Sage > 0 );
#性别只能为男或女
ALTER TABLE student ADD CONSTRAINT c2 CHECK ( Ssex IN ('男','女') );
#成绩在0~100之间
ALTER TABLE sc ADD CONSTRAINT c3 CHECK ( grade BETWEEN 0 AND 100);

（3）删除检查约束

修改检查约束，要先删除原来的约束条件，再增加新的约束条件

若不知道约束名可以先 “show create table 表名” 来查看约束名

-- 格式
ALTER TABLE 表名 DROP CONSTRAINT 约束名;

-- 示例
ALTER TABLE test1 DROP CONSTRAINT test1_chk_1;

毒者

关注

17
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
MySQL DDL语句

数据定义语言，用来定义数据库对象(数据库，表，字段)
复制链接

扫一扫