SQL/数据定义语言DDL（Data Definition Language）

　　DDL主要是用在定义或改变，表的结构，数据类型，表之间的链接和约束等初始化工作上

　　如：CREATE、ALTER、DROP

　　库和表的管理、数据类型、约束、标识列

/*
数据定义语言
库和表的管理
一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除

创建：create
修改：alter
删除：drop

*/

#一、库的管理

#1、库的创建
/*
语法：
create database 【if not exists】库名;
*/

#创建库books
CREATE DATABASE books;

#2、库的修改
# rename database books to 新库名; #已经不再适用
#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;

#3、库的删除
DROP DATABASE IF EXISTS books;


#二、表的管理

#1、表的创建
/*
语法：
create table 表名(
    列名（字段） 列的类型【（长度）约束】,
    列名（字段） 列的类型【（长度）约束】,
    列名（字段） 列的类型【（长度）约束】,
    ...
    列名（字段） 列的类型【（长度）约束】
);

*/

#创建表 book
CREATE TABLE book(
    id INT, #编号  行尾才有逗号
    bName VARCHAR(20),#图书名
    price DOUBLE,#价格
    authorId INT,#作者编号
    publishDate DATETIME#出版日期
);

#查看表结构
DESC book;

#创建表author
CREATE TABLE author(
    id INT,
    au_name VARCHAR(20), #括号内的长度是指最大可显示字符的宽度
    nation VARCHAR(10)  
);

#查看表结构
DESCRIBE author;

#2、表的修改
/*
alter table 表名 add|drop|modify|change column 列名【列类型 约束】;
*/

#修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubdate DATETIME;
#修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
#添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE【first|AFTER 字段名】;
#删除列
ALTER TABLE author DROP COLUMN annual;
#修改表名
ALTER TABLE author RENAME TO book_author;

#3、表的删除

DROP TABLE IF EXISTS book_author;
SHOW TABLES;

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

DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 新表名();

#4、表的复制

INSERT INTO author VALUES  
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');

SELECT * FROM author;

#drop table if exists author;  
#ALTER DATABASE books CHARACTER SET utf8;  #修改库字符集
#delete from author;

#1、仅仅复制表的结构
CREATE TABLE copy LIKE author;

#2、复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;

#只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation = '中国';

#仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;

#show tables;

#创建一个表dept1
/*
name  null?  type
-----------------
id            int(7)
name          varchar(25)

*/

CREATE TABLE dept1(
    id INT(7),
    NAME VARCHAR(25)
);

#2、将表departments中的数据插入新表dept1中 #跨库插入
INSERT INTO dept1
SELECT department_id, department_name  #departments表中的列（字段）与表dept1对应
FROM myemployees.departments;          #跨库，标注库名

#将表departments中的数据插入新表dept2中 # 建新表dept2
CREATE TABLE dept2
SELECT department_id,department_name
FROM myemployees.`departments`;

#3、创建表emp5
/*
name  null?  type
-----------------
id            int(7)
first_name    varchar(25)
last_name     varchar(25)
dept_id       int(7)
*/

CREATE TABLE emp5(
    id            INT(7),
    first_name    VARCHAR(25),
    last_name     VARCHAR(25),
    dept_id       INT(7)
);

#4、将列last_name的长度增加到50
ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50); #alter + table
#desc emp5;

#5、根据表employees创建employees2
CREATE TABLE employees2 LIKE myemployees.`employees`;
#desc employees2;
#drop table employees2;

#6、删除表emp5
DROP TABLE IF EXISTS emp5;
#show tables;

#7、将表employees2重命名为emp5
ALTER TABLE employees2 RENAME TO emp5;
#show tables;

#8、在表dept1和emp5中添加新列test_column, 并检查所作操作
ALTER TABLE dept1 ADD COLUMN test_column INT;  #  add column
ALTER TABLE emp5 ADD COLUMN test_column INT;

DESC dept1;
DESC emp5;

#9、直接删除表中列 test_column
ALTER TABLE emp5 DROP COLUMN test_column; # alter + table + 表 + 删除列 + 列名

#常见的数据类型
/*
数值型：
    整形
    小数：
        定点数
        浮点型    
字符型：
    较短的文本：char、varchar
    较长的文本: text、blob（较长的二进制数据）
日期型：    

*/

#一、整形
/*
分类：
tinyint           一字节
smallint          二字节
mediumint         三字节
int/integer      四字节
bigint            八字节
特点：
1、如果不设置无符号还是有符号，默认有符号，如果想设置无符号，需要添加unsigned关键字
2、如果插入的数值超出了整形的范围，会报out of range，并且插入临界值
3、如果不设置长度，会有默认长度，长度代表了显示的最大宽度（加 zerofill  则变为默认无符号类型），表数范围是由类型决定的
*/

#如何设置有符号无符号

CREATE TABLE tab_int(
    t1 INT,
    t2 INT UNSIGNED
);

#drop table if exists tab_int;
#describe tab_int;
#insert into tab_int values(-123456, 255);
#select * from tab_int;


#二、小数
/*
浮点数类型：
    float(m,d)           四字节
    double(m,d)          八字节
定点数类型：
    dec(m,d)/decimal(m,d)   m+2字节, 最大取值范围与double相同,给定decimal的有效范围由m和d决定
特点：
1、m和d都可以省略，decimal默认小数位是 0，默认整数位是 10（ m:10, d:0 ）
    如果是float和double，会根据插入的数值的精度来决定精度
2、m：整数部位+小数部位
   d:小数部位         
3、 定点型精确度较高，如果要求插入数值的精度较高如货币运算，则考虑使用定点型
*/

CREATE TABLE tab_float(
    f1 FLOAT(5,2),
    f2 DOUBLE(5,2),
    f3 DECIMAL(5,2)
);
#describe tab_float;
INSERT INTO tab_float VALUES(123.45,123.45,123.45);
INSERT INTO tab_float VALUES(123.456,123.456,123.456); #四舍五入
INSERT INTO tab_float VALUES(123.4,123.4,123.4);
INSERT INTO tab_float VALUES(1523.4,1523.4,1253.4); #小数点前最大值3位999，小数点后2位最大值99
#select * from tab_float;


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

#三、字符型
/*
较短的文本：
    char(m)    #m代表的是最多的字符数，一个字母或汉字的个数，char固定字符
    varchar(m) #可变字符
    其他的：
        binary和varbinary 用于保存较短的二进制
        enum 用于保存枚举
        set  用于保存集合
    binary varbinary 类型
    类似于char 和 varchar，不同的是它们包含二进制字符串而不包含非二进制字符串

    enum 类型
    又称为枚举类型，要求插入的值必须属于列表中指定的值之一
    如果列表成员为1-255，需要一个字节存储
    如果列表成员为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
blob(较大的二进制)

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


*/

CREATE TABLE tab_char(
    c1 ENUM('a','b','c')
);

INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m'); #超出范围，插入失败
INSERT INTO tab_char VALUES('A');

#select * from tab_char;

CREATE TABLE tab_set(
    s1 SET('a','b','c','d')
);

INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B');
INSERT INTO tab_set VALUES('a,c,d');

#select * from tab_set;

#四、日期型
/*
分类：
date 只保存日期
time 只保存时间
year 只保存年

datetime  保存日期 + 时间
timestamp 保存日期 + 时间

特点：
        字节    范围         时区等的影响
datetime        8      1000--9999    不受
timestamp       4      1970--2038     受

*/

CREATE TABLE tab_date(
    t1 DATETIME,
    t2 TIMESTAMP
);

INSERT INTO tab_date VALUES(NOW(),NOW());

SELECT * FROM tab_date;

SHOW VARIABLES LIKE 'time_zone';   #value system

SET time_zone = '+9:00';          
#SELECT * FROM tab_date;           #设置时区后，t1不受影响，t2受影响
#SHOW VARIABLES LIKE 'time_zone';  #显示时区 value +9:00
#SET time_zone = system;           #恢复系统时区         


#常见约束
/*

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

分类：六大约束
    NOT NULL:非空，用于保证该字段的值不能为空
    比如姓名、学号等
    DEFAULT:默认，用于保证该字段有默认值
    比如性别
    PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
    比如学号、员工编号等
    UNIQUE:唯一，用于保障该字段的值具有唯一性，可以为空
    比如座位号
    CHECK：检查约束【mysql中不支持】
    比如年龄、性别
    FOREIGN KEY:外键，用于限制两个表的关系，用于保障该字段的值必须来自主表的关联列的值
        在从表添加外键约束，用于引用主表中某列的值
    比如学生表的专业编号，员工表的工种编号
添加约束的时机：
    1、创建表时
    2、修改表时
约束的添加的分类：
    列级约束：
        六大约束语法上都支持，但外键约束没有效果
    表级约束：
        除了非空、默认，其他的都支持
主键与唯一的对比：
    保证唯一性  是否为空               一个表中可以有多少个  是否允许组合
主键    是        否            至多一个        是    
唯一    是        是                     可以有多个        是

外键：
    1、要求在从表设置外键关系
    2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
    3、主表的关联列必须是一个key（主键或唯一）
    4、插入数据时，先插入主表，再插入从表，删除数据时，先删除从表，再删除主表
    5、一个字段可以添加多个约束，用空格隔开
    
    select * from major;
    select * from stuinfo;
    
    delete from major;
    insert into major values(1,'java');
    insert into major values(2,'h5');
    
    delete from stuinfo;
    insert into stuinfo values(1,'john','男',null,19,1);
    insert into stuinfo values(2,'lili','女',null,19,2); 
    #ALTER DATABASE students CHARACTER SET utf8;  #修改库字符集
    
    drop table if exists major;
    create table major(
        id int primary key,
        majorName varchar(20)
    );
    drop table if exists stuinfo;
    create table stuinfo(
        id int,
        stuname varchar(20),
        gender varchar(1),  # 用char(1)类型，输入中文乱码（不是字符集的问题），改成varchar(1)问题解决
        seat int,
        age int,
        majorid int,
        
        primary key(id),
        unique(seat),
        check(gender = '男' or gender = '女'),
        foreign key(majorid) references major(id)
    
    );
*/

CREATE TABLE 表名(
    字段名 字段类型 列级约束,
    ...
    字段名 字段类型 列级约束
    表级约束
);

CREATE DATABASE students;

#一、创建表时添加约束

#1、添加列级约束
/*
语法：
直接在字段名和类型后面追加 约束类型即可
只支持：默认、非空、主键、唯一（default, not null, primary key, unique）

*/


USE students;

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) #外键 foreign key, 这是列级约束, 外键约束没有效果
);

CREATE TABLE major(
    id INT PRIMARY KEY, #主键
    majorName VARCHAR(20)
);

#desc stuinfo;            #查看表结构
#show index from stuinfo; #查看索引 包括主键、外键、唯一

#2、添加表级约束
/*

语法：在各个字段的最下面
【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), #主键  组合主键: PRIMARY KEY(id,stuName)
    CONSTRAINT uq UNIQUE(seat), #唯一键   组合唯一键：UNIQUE(seat,seat2)
    CONSTRAINT ck CHECK(gender = '男' OR gender = '女'), #检查约束
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键 （从表设置外键）
);

#show index from stuinfo;

#通用的写法：
CREATE TABLE IS NOT EXISTS stuinfo(
    id INT PRIMARY KEY,
    stuName VARCHAR(20) NOT NULL,
    gender CHAR(1),
    seat INT UNIQUE,
    age INT DEFAULT 18,
    majorId INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

#二、修改表时添加约束
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

*/

DROP TABLE IF EXISTS stunifo;
CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    major INT
);

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


#通过以下两种方式删除主表的记录，实际开发中很少使用
#一、*****级联删除*******    #ON DELETE CASCADE
#1、添加级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE; #
#2、删除主表的3号专业    #从表数据删除
DELETE FROM major WHERE id = 3;
#二、*****级联置空*******    #ON DELETE SET NULL 
#1、添加级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL; #
#2、删除主表的2号专业    #从表数据，'专业'字段置空
DELETE FROM major WHERE id = 2;


#三、修改表时删除约束

#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;

#show index from stuinfo;
#desc stuinfo;






#向表emp2的id添加primary key约束( my_emp_id_pk )
#列级添加
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY; #不能添加名字
#表级添加
ALTER TABLE emp2 ADD constrain my_emp_id_pk PRIMARY KEY(id);

#向表dept2的id列中添加primary key约束（my_dept_id_pk）
#列级添加
ALTER TABLE dept2 MODIFY COLUMN  PRIMARY KEY(id); #不能添加名字
#表级添加
ALTER TABLE dept2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id);

#向表emp2中添加列dept_id，并在其中定义foreign key 的约束，与之相关联的列是dept2表中的id列
ALTER TABLE emp2 ADD COLUMN dept_id INT;      #先添加列dept_id
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id); # 可以省略 CONSTRAINT fk_emp2_dept2 

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

*/





#标识列
/*
又称自增长列
含义：可以不用手动插入值，系统提供摩恩的序列值

特点：
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,  #id INT,
    NAME VARCHAR(20), # 少打个逗号报错
    seat INT
);

#truncate table tab_identity;

# id 为标识列的情况下执行

INSERT INTO tab_identity(id,NAME) VALUES(10,'john');   #手动插入值, 比如插入值为10
INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john'); #再执行该语句
INSERT INTO tab_identity(NAME) VALUES('lucy');         #或该语句

#select *from tab_identity;
#show variables like '%auto_increment%';
#set auto_increment_increment = 3;  #设置步长

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

#三、修改表时删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;