MySQL中DDL数据定义语句有哪些
发布时间：2022-02-17 01:12:13 所属栏目：MySql教程 来源：互联网
导读：小编给大家分享一下MySQL中DDL数据定义语句有哪些，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ 一、DDL数据定义语句 库的管理 创建：create database [if not exists]
|
小编给大家分享一下MySQL中DDL数据定义语句有哪些，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ 一、DDL数据定义语句 库的管理 创建：create database [if not exists] 库名; 修改：若需要修改库名,直接修改文件夹 删除：DROP DATABASE IF EXISTS 库名; 表的管理 创建 create table IF NOT EXISTS 表名( 列名 列的类型[长度、约束], 列名 列的类型[长度、约束], 列名 列的类型[长度、约束], ... ) 修改 alter table 表名 add|drop|modify|change column 列名 [列类型 约束]; 删除 drop table 表名; 【库的管理】# 创建库CREATE DATABASE IF NOT EXISTS books;# 修改库的字符集ALTER DATABASE books CHARACTER SET gbk;# 删除库DROP DATABASE IF EXISTS books;【表的管理】# 1.创建表CREATE TABLE IF NOT EXISTS book( id INT, bName VARCHAR(20), authorID INT, publishDate DATETIME);DESC book;CREATE TABLE author( id INT, au_name VARCHAR(20), nation VARCHAR(10));DESC author;# 2.修改表# 修改列名ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;# 修改列的类型或约束ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;# 添加新列ALTER TABLE author ADD COLUMN annual DOUBLE; # 添加新列作为第一个字段ALTER TABLE author ADD COLUMN newT1 INT FIRST;# 添加新列在指定的列后ALTER TABLE author ADD COLUMN newT2 INT AFTER newT1;# 删除列ALTER TABLE author DROP COLUMN annual;# 修改表名ALTER TABLE author RENAME TO book_author;# 3.表的删除DROP TABLE IF EXISTS book_author;SHOW TABLES;# 4.表的复制INSERT INTO author VALUES(1,'小樱','日本'),(2,'悟空','中国'),(3,'绿巨人','美国'),(4,'哪吒','中国');# 仅复制表的结构CREATE TABLE author2 LIKE author;# 复制表的结构+数据CREATE TABLE author3 SELECT * FROM author;# 只复制部分数据CREATE TABLE author4 SELECT id,au_name FROM author WHERE nation='中国';# 仅复制某些字段CREATE TABLE author5 SELECT id,au_name FROM author WHERE 1=2; 二、数据类型 2.1 整型 整数类型 别名 字节 无符号范围 有符号范围 Tinint 微整型 1 0~255 -128~127 Smallin 小整型 2 0~65535 -32768~32767 Mediumint 中整型 3 0~1677215 -8388608~8388607 Int或Integer 整型 4 0~4294967295 -2147483648~2147483647 Bigint 大整型 8 0~9223372036854775807*2+1 -9223372036854775808~9223372036854775807 【整型】# 1.默认为有符号,可以添加unsigned设置为无符号CREATE TABLE IF NOT EXISTS tab_int( t1 INT, # 有符号 t2 INT UNSIGNED, # 无符号 t3 INT ZEROFILL #添加zerofill后自动变更为无符号整型,位数不够0填充.);DESC tab_int;SELECT * FROM tab_int;# 2.如果插入的数值超出了整型的范围,会报out of range异常INSERT INTO tab_int VALUES(2147483648,1);# 3.如果不设置长度,会有默认的长度,位数不够时0填充(前提是字段有ZEROFILL). 2.2 小数 浮点数类型 字节 范围 float(M,D) 4 -2^128 ~ +2^128 double(M,D) 8 -2^1024 ~ +2^1024 定点数类型 DEC(M,D) M+2 最大取值范围与double相同，给定decimal的有效范围由M和D决定 注意： M:整数部位+小数部位的总长度 D:小数部位 D和M都省略时： 1、如果是decimal类型,则M默认为10,D默认为0; 2、如果是floact和double,会根据插入的数值的精确度来决定精度。 3、定点型的精确度较高,如果要求插入数值的精确度较高如货币运算则考虑使用。 2.3 字符型 字符串类型 M是否可以省略 特点 空间耗费 效率 范围 char(M) M可以省略,默认为1 定长 比较耗费 高 M为0~255之间的整数 varchar(M) M不可以省略 可变长 比较节省 低 M为0~65535之间的整数 binary和varbinary类型，类似于char和varchar，不同的是它们包含二进制字符而不包含非二进制字符，即保存较短的二进制。 Bit(M)类型，字节为1~8，范围为Bit(1)~Bit(8)。 Enum类型，即枚举类型，要求插入的值必须属于列表中指定的值之一，如果列成员为1~255，则需要1个字节存储；如果列成员为255~65535，则需要2个字节存储，最多需要65535个成员。 Set类型，和Enum类似，可以保存0~64个成员。和Enum最大的区别是：Set类型一次可以选取多个成员，而Enum只能选一个，根据成员个数不同，存储所占的字节也不同。 成员数 字节数 1~8 1 9~16 2 17~24 3 25~32 4 33~64 8 【枚举】CREATE TABLE tab_set( s1 SET('a','b','c'));INSERT INTO tab_set VALUES('a');INSERT INTO tab_set VALUES('c,a');INSERT INTO tab_set VALUES('a,b,c');# 插入后,内部会进行排序,如插入c,a会变成a,c 2.4 日期类型 日期和时间类型 特点 字节 最小值 最大值 date 只保存日期 4 1000-01-01 9999-12-31 datetime 保存日期+时间 8 1000-01-01 00:00:00 9999-12-31 23:59:59 timestamp(使用较多) 保存日期+时间 4 187001080001 2038年的某个时刻 time 只保存时间 3 -838:59:59 838:59:59 year 只保存年 1 1901 2155 timestamp和实际时区有关，更能反映实际的日期；datetime则只能反映出插入时的当地时区。 timestamp的属性受Mysql版本和SQLMode的影响很大。 【日期类型】CREATE TABLE tab_date( t1 DATETIME, # 不受时区影响 t2 TIMESTAMP # 受时区影响);INSERT INTO tab_date VALUES(NOW(),NOW());SET time_zon='+8:00';# 设置时区SHOW VARIABLES LIKE 'time_zone'; #显示当前时区 二、六大约束 含义：一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性。 NOT NULL 非空约束，规定某个字段不能为空 UNIQUE 唯一约束，规定某个字段在整个表汇中是唯一的 PRIMARY KEY 主键(唯一且非空) FOREIGN KEY 外键 CHECK 检查约束(mysql中不支持) DEFAULT 默认值,保证该字段有默认值 列级约束：六大约束语法上都支持，但外键约束没有效果。 表级约束：除了非空、默认，其他都支持。 常用的做法是：其他约束都写在列级，外键约束写在表级。 主键和唯一对比 是否保证唯一性 是否允许为空 允许有几个 是否允许组合(不推荐) 主键 √ × 至多一个主键 允许组合主键 唯一 √ √(允许有一个null) 可以有多个唯一 允许组合唯一 外键的特点： 要求在从表设置外键关系。 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求。 主表的关联列必须是一个key(一般是主键或唯一) 插入数据时，先插入主表再插入从表；删除数据时，先删除从表再删除主表。 列级约束和表级约束比较 位置 支持的约束类型 是否可以起别名 列级约束 列的后面 语法都支持，但外键没有效果 不可以 表级约束 所有列的下面 默认和非空不支持，其他支持 可以(主键没有效果) 【列级约束】 直接在字段名和类型后面追加约束类型。 注意：只支持默认、非空、主键、唯一，不支持外键约束CREATE DATABASE students;USE students;CREATE TABLE major( id INT PRIMARY KEY,# 主键 majorName VARCHAR(20));CREATE TABLE stuinfo( id INT PRIMARY KEY,# 主键 stuName VARCHAR(20) NOT NULL, #非空 gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查 seat INT UNIQUE,# 唯一 age INT DEFAULT 18, #默认约束 majorId INT REFERENCES major(id) # 外键);DESC stuinfo;SHOW INDEX FROM stuinfo; # 查看表中所有的索引,外键,唯一【表级约束】# 语法:[CONSTRAINT 约束名 ] 约束类型(字段名)DROP TABLE IF EXISTS stuinfo;CREATE TABLE stuinfo( id INT, stuname VARCHAR(20), gender CHAR(1), seat INT, age INT, majorid INT, CONSTRAINT pk PRIMARY KEY(id),# 主键 CONSTRAINT uq UNIQUE(seat), # 唯一 CONSTRAINT ck CHECK(gender='男' OR gender='女'),# 检查(不报错,但无效) CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) # 外键);SHOW INDEX FROM stuinfo;【修改表时添加约束】# 添加非空约束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;# 添加默认约束ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;# 添加主键ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;# 添加唯一键ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;# 添加外键ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);DESC stuinfo;SHOW INDEX FROM stuinfo;【修改表时删除约束】# 删除非空约束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;# 删除默认约束ALTER TABLE stuinfo MODIFY COLUMN age INT;# 删除主键ALTER TABLE stuinfo DROP PRIMARY KEY;# 删除唯一ALTER TABLE stuinfo DROP INDEX seat;# 删除外键ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major; 学习了约束，尝试完成籼米的测试题MySQL中DDL数据定义语句有哪些 1、列级约束: ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;# 列约束不支持起名字 表级约束: ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);# 实际上主键起了名字也没效果 2、与1类似 3、 ALTER TABLE emp2 ADD COLUMN dept_id INT; ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id); 四、标识列 标识列又称为自增长列，其实也可以将标识列纳入约束的范围。 含义：可以不用手动的插入值，系统提供默认的序列值。 特点： 1.标识列不一定非要和主键搭配，但要求是一个key。 2.一个表至多可以有一个表示列。 3.表示列的类型只能为数值型。 4.标识列可以通过SET auto_increment_increment=3;设置步长，也可以通过手动插入值来设置起始值。 【创建表时设置标识列】DROP TABLE IF EXISTS tab_identity;CREATE TABLE tab_identity( id INT PRIMARY KEY AUTO_INCREMENT, #设置自动自增 NAME VARCHAR(20));INSERT INTO tab_identity VALUE(NULL,'花花');INSERT INTO tab_identity(NAME) VALUE('Hudie');SELECT * FROM tab_identity;SHOW VARIABLES LIKE '%auto_increment%';SET auto_increment_increment=3;#设置步长为3# 起始值可以通过改变第一条记录的值来更改TRUNCATE TABLE tab_identity;【修改表时设置标识列】CREATE TABLE tab_identity( id INT, #设置自动自增 NAME VARCHAR(20));# 设置主键和标识列ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;修改表时删除标识列【】ALTER TABLE tab_identity MODIFY COLUMN id INT; 五、级联删除与置空 级联删除： ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE; 级联置空： ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL; 【演示级联删除、级联置空】DROP TABLE major,stuinfoCREATE TABLE IF NOT EXISTS major( id INT PRIMARY KEY, majorName VARCHAR(20));INSERT INTO majorVALUES(1,'Java'),(2,'Python'),(3,'Go');CREATE TABLE IF NOT EXISTS stuinfo( id INT, stuname VARCHAR(20), gender CHAR(1), seat INT, age INT, majorid INT);INSERT INTO stuinfoSELECT 1,'join1','女',NULL,NULL,1 UNION ALLSELECT 2,'join2','女',NULL,NULL,1 UNION ALLSELECT 3,'join3','女',NULL,NULL,2 UNION ALLSELECT 4,'join4','女',NULL,NULL,2 UNION ALLSELECT 5,'join5','女',NULL,NULL,1 UNION ALLSELECT 6,'join6','女',NULL,NULL,3 UNION ALLSELECT 7,'join7','女',NULL,NULL,3 UNION ALLSELECT 8,'join8','女',NULL,NULL,1);SELECT * FROM major;SELECT * FROM stuinfo;# 传统方式添加外键ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id);# 删除major表的3号专业# 方式1:级联删除# 先删除外键ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;# 添加外键时添加级联删除ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;DELETE FROM major WHERE id =3;# 方式2:级联置空# 先删除外键ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;# 添加外键时添加级联置空ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;DELETE FROM major WHERE id =2; 以上是“MySQL中DDL数据定义语句有哪些”这篇文章的所有内容，感谢各位的阅读！ （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
