DDL学习

数据定义语言

库和表的管理

一、库的管理

创建、修改、删除

二、表的管理

创建、修改、删除

创建：create

修改：alter

删除：drop

一、库的管理

1.库的创建

语法：create database 【if not exists】 库名；

2.库的修改

更改库的字符集：alter database 库名 character set ***；

3.库的删除

drop database 【if exists】库名；

二、表的管理

1.表的创建

语法：

create table 表名（

        列名 列的类型【（长度） 约束】，

        列名 列的类型【（长度） 约束】，

        列名  列的类型【（长度）  约束】，

        ······························ ·········

        列名  列的类型【（长度）  约束】，

）

2.表的修改

（1）修改列名

（2）修改列的类型或约束

（3）添加新列

（4）删除列

（5）修改表名

语法：

alter         table         表名 

【add(添加新列）|| drop（删除列） || modify（修改列的类型或约束） ||  change（修改列名） 】

column  列名【列类型        约束】

alter table 表名 rename to 新表名（重命名）

#通用的写法
DROP DATABASE IF EXISTS 旧库名；
CREATE DATABASE 新库名；

DROP TABLE IF EXISTS 旧表名；
CREATE TABLE 表名（）；
 

3.表的删除

drop table 【if exists】表名；

4.表的复制

（1）仅仅复制表的结构

create        table        新表名        like        旧表名；

（2）复制表的结构+数据

create        table        新表名

select        *        from        旧表名

（3）只复制部分数据

create        table        新表名

select        (想要复制的字段内容)

from        旧表名

where【筛选条件】

（4）仅仅复制某些字段

create        table        新表名

select        (想要复制的字段内容）

from        旧表名

where        【1=2、0等类似为false的筛选条件】

常见的数据类型

数值型：整型、小数（定点数、浮点数）、

字符型：较短的文本（char varchar ）       较长的文本（text、blob[较长的二进制数据]）

日期型

整型：

整数类型	字节
tinyint	1
smallint	2
mediumint	3
int、integer	4
bigint	8

特点：1.如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字2.如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值3.如果不设置长度，会有默认长度 长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用

小数：

浮点数类型：float(4字节） double(8）字节

定点数类型：dec(m,d)    decimal(m,d)

特点：

1.m:整数部位+小数部位  d:小数部位   如果超过范围，则插入临界值

2.m d可以省略 

如果是decimal，则m默认为10，d默认为0

如果是float double，则会根据插入的数值的精度来决定

3.定点型的精确度较高，如果要求插入数值的精确度较高如货币等则考虑使用

原则：所选择的类型越简单越好，能保存数值的类型越小越好

字符型：

较短的文本：char varchar

【char:固定长度  varchar:非固定长度    最多字符数】

	写法	m的意思	特点	空间的耗费	效率
char	char(m)	最大的字符数，可以省略，默认为1	固定长度的字符	比较耗费	高
varchar	varchar(m)	最大的字符数，不可以省略	可变长度的字符	比较节省	低

binary和varbinary类型保存较短的二进制类型

说明：类似于char和varchar，不同的是它们包含二进制字符串而不包含非二进制字符串

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

较长的文本：text   blod(较大的二进制)

日期型

日期和时间类型	字节	最小值	最大值
date（只保存日期）	4	1000-01-01	9999-12-31
datetime（保存日期+时间）	8	1000-01-01 00：00：00	9999-12-31 23：59：59
timestamp（保存日期+时间）	4	19700101080001	2038的某个时刻
time（只保存时间）	3	-838：59：59	838：59：59
year（只保存年）	1	1901	2155

datetime和timestamp的区别：

1.timestamp支持的时间范围较小，取值范围：19700101080001--2038的某个时间

datetime的取值范围：1000-1-1--9999--12-31

2.timestamp和实际时区有关，更能反映实际的日期，而datetime则只能反映出插入时的当地时区

3.timestamp的属性受Mysql版本和SQLMode的影响很大

4.datetime不受时区影响，timestamp受时区影响

SHOW VARIABLES LIKE 'time_zone';

SET time_zone='+9:00';

                                                                                                                                                                                                                                                                                                                       

常见约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

create table 表名（

        字段名 字段类型 列级约束

        字段名 字段类型，

        表级约束

）；

分类：六大约束

not null :非空，用于保证该字段的值不能为空，比如姓名、学号等

default：默认，用于保证该字段有默认值，不用手动插入，比如性别

primary key：主键，用于保证该字段的值具有唯一性，不可重复，并且非空，比如学号、员工编号等

unique：唯一，用于保证该字段的值具有唯一性，不可重复，可以为空，比如座位号

check：检查约束【mysql中不支持】，比如年龄、性别

foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值    【在从表添加外键约束，用于引用主表中某列的值，比如学生表的专业编号，员工表的部门编号，员工表的工种编号】

添加约束的时机：1.创建表时2.修改表时

约束的添加分类：

列级约束：六大约束语法都支持，但外键约束没有效果；不可以起约束名【一个字段上追加多个，中间用空格隔开，没有顺序要求】

表级约束 ：除了非空、默认，其他的都支持  ；可以起约束名，但对主键无效 

主键和唯一的对比                      

区别：（1）一个表中至多有一个主键，但可以有多个唯一键（2）主键不允许为空，唯一可以为空

相同点：都具有唯一性，都支持组合键【不推荐】

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

  外键：

1.要求在从表设置外键关系，用于限制两个表的关系，从表的字段值引用了主表的某字段值

2.从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求

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

4.插入数据时，先插入主表，再插入从表

删除数据时，先删除从表，再删除主表                                                                                   

查看表中所有的索引：（包括主键、外键、唯一）

SHOW INDEX FROM `stuinfo`;

可以通过以下两种方式来删除主表的记录：

#方式一：级联删除
ALTER TABLE stuinfo
ADD CONSTRAINT fk_stuinfo_major 
FOREIGN KEY(majorid) REFERENCES major(id)
ON DELETE CASCADE;

#方式二：级联置空
ALTER TABLE stuinfo 
ADD CONSTRAINT fk_stuinfo_major
FOREIGN KEY(majorid) REFERENCES major(id)
ON DELETE SET NULL;

一、创建表时添加约束

create table 表名(

        字段名 字段类型 not null，#非空

        字段名 字段类型 primary key，#主键

        字段名 字段类型 unique，#唯一

        字段名 字段类型 default 值，#默认

        constraint 约束名 foreign key(字段名）references 主表 （被引用例）

)

	支持类型	可以起约束名
列级约束	除了外键	不可以
表级约束	除了非空和默认	可以，但对主键无效

列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求

1.添加列级约束【支持除了外键，不可以起约束名】

直接在字段名和类型后面追加约束类型即可，只支持：默认、非空、主键、唯一

#列级约束
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)#外键
);
CREATE TABLE major(
    id INT PRIMARY KEY,
    majorName VARCHAR(20)
);

2.添加表级约束【除了非空和默认，可以起约束名，但对主键无效】

在各个字段最下面

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

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)#外键
);

CREATE TABLE stuinfo(
    id INT PRIMARY KEY,
    stuname VARCHAR(20) NOT NULL,
    sex CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
)
 

二、修改表时添加约束

1.添加列级约束

alter table 表名 modify column 字段名 字段类型 新约束；

2.添加表级约束

alter table 表名 add【constraint 约束名】 约束类型（字段名）【外键的引用】；

#1.修改表时添加非空约束
ALTER TABLE `stuinfo` MODIFY COLUMN `stuname` VARCHAR(20) NOT NULL;
#2.添加默认约束
ALTER TABLE `stuinfo` MODIFY COLUMN `age` INT DEFAULT 18;
#3.添加主键
#(列级约束）
ALTER TABLE `stuinfo` MODIFY COLUMN `id` INT PRIMARY KEY;
#（表级约束）
ALTER TABLE `stuinfo` ADD PRIMARY KEY(id);
#4.添加唯一
#列级约束
ALTER TABLE `stuinfo` MODIFY COLUMN `seat` INT UNIQUE;
#表级约束
ALTER TABLE `stuinfo` ADD UNIQUE(`seat`);
#5.添加外键
ALTER TABLE `stuinfo` ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

三、修改表时删除约束

#修改表时删除约束
DESC `stuinfo`;
#1.删除非空约束
ALTER TABLE `stuinfo` MODIFY COLUMN `stuname` VARCHAR(20) NULL;
#2.删除默认约束
ALTER TABLE `stuinfo` MODIFY COLUMN age INT;
#3.删除主键
ALTER TABLE `stuinfo` DROP PRIMARY KEY;
#4.删除唯一
ALTER TABLE `stuinfo` DROP INDEX seat;
#5.删除外键
ALTER TABLE `stuinfo` DROP FOREIGN KEY fk_stuinfo_major;

	位置	支持的约束类型	是否可以起约束名
列级约束	列的后面	语法都支持，但外键没有效果	不可以
表级约束	所有列的下面	默认和非空不支持，其他支持	可以（主键没有效果）

标识列

标识列：又称为自增长列

含义：可以不用手动的插入值，系统自动提供默认的序列值，默认从1开始，步长为1

特点：

1.标识列和key搭配使用，标识列（自增长列）必须为一个key【在mysql中】

2.一个表至多有一个标识列，即一个自增长列

3.标识列（自增长列）的类型只能是数值型

4.标识列可以通过set auto_increment_increment=?；设置步长

如果要更改起始值，可以通过手动插入值，设置起始值

如果要更改步长，更改系统变量

一、创建表时设置标识列

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

二、修改表时设置标识列

#二、修改表时设置标识列
ALTER TABLE `tab_identity` MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
#三、修改表时删除标识列
ALTER TABLE `tab_identity` MODIFY COLUMN id INT;