广西 网站建设/中国站长之家官网
数据定义语言（Data Definition Language，DDL）
用于对数据库和表的操作和管理。
-
库的管理
创建，修改，删除
-
表的管理
创建，修改，删除
-
创建：create
-
修改：alter
-
删除：drop
一、库的管理
1、库的创建
方法1：
方法2:
# 案例：创建Books数据库(如果有不创建，没有就创建）
CREATE DATABASE Books; # 有的话会报错
CREATE DATABASE IF NOT EXISTS Books; # 一般用这个
2、库的修改
一般库的修改就是表的修改，其名字一般不修改
# 更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
3、库的删除
DROP DATABASE books;
二、表的管理
1、表的创建
语法：（可加约束，也可不加约束）
create table 表名（列名 列的类型【长度，约束】,列名 列的类型【长度，约束】,列名 列的类型【长度，约束】.......
）
案例：不加约束
# 创建表book
CREATE TABLE IF NOT EXISTS book(id INT, #编号bName VARCHAR(20), #图书名,字符price DOUBLE, #价格authorID INT, #作者编号publishData DATETIME #出版日期
);
# 查看表的结构
DESC book
# 创建作者表author
CREATE TABLE author(id INT, #编号au_Name VARCHAR(20), #作者名nation VARCHAR(10) #国籍
);
2、表的修改(较少使用）
1、修改列名
2、修改列的类型或约束
3、添加新列
4、删除列
5、修改表名
#修改表名
ALTER TABLE book RENAME to Book;# 修改列名（必须加上类型）
ALTER TABLE book CHANGE COLUMN publishdata pubDate DATETIME;#修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;#添加新列（年薪）
ALTER TABLE author ADD COLUMN annual DOUBLE;#删除列（不用加类型）
ALTER TABLE author DROP COLUMN if exists annual;# 修改表名
ALTER TABLE author RENAME TO book_author;
3、表的删除和复制
# 表的删除
DROP TABLE IF EXISTS book_author;# 表的复制
CREATE TABLE IF NOT EXISTS author(id INT, #编号au_Name VARCHAR(20), #作者名nation VARCHAR(10) #国籍
);
# 1、插入
INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');#2、仅仅复制表的结构
CREATE TABLE copy LIKE author;#3、完全复制（结构+数据）
CREATE TABLE copy2
SELECT * FROM author;SELECT * FROM copy2;#4、部分复制（结构+数据）
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE `nation`='中国';
SELECT * FROM copy3;#4、部分结构复制（只有结构）
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 1=2; # 恒不成立，所以没有数据
SELECT * FROM copy4;
测试：
#在books`books`中创建一个新表dept1，并将departments中的数据插入进去
CREATE TABLE dept1
SELECT *
FROM myemployees.`departments`;
三、常见的数据类型
| 整型 | tinyint smallint int bigint |
|---|---|
| 浮点型 | float（m,n） double(m,n) dec(m,n),其中m和n可选 |
| 字符型 | char vachar(n) text |
| 日期型 | data time datetime timestamp |
| 二进制型 | blob（类似int的小中大都有，用于存储图片数据） |
1、数值型
- 整型int
#一、整型
#1、如何设置无符号和有符号
/*
1、如果不设置，默认是有符号，想设置无符号，添加unsigned关键爱你自
2、如果插入的数值超过整型的范围，会报out of range错误，并且插入最近的临界值
3、如果不设置长度，会默认长度
*/
DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(t1 INT, # 默认是有符号t2 INT UNSIGNED # 设置是无符号
);
DESC tab_int;# 插入符号能成功，表示有符号
INSERT INTO tab_int VALUES(-123456,-123456);
INSERT INTO tab_int VALUES(2147483648,4294967296);SELECT * FROM tab_int
** 小数：**
- 定点型（精度更高，如货币运算）dec(M,D) decimal(M,D) (前面是后的缩写），在表示钱方面使用该类型，因为不会出现精度缺失问题；
- 浮点数 float(M,D) double(M,D)，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
#测试 M,D
CREATE TABLE tab_float(f1 FLOAT(5,2),f2 DOUBLE(5,2),f3 DECIMAL(5,2)
);INSERT INTO tab_float VALUES(123.45,123.45,123.45) # 结果一样
INSERT INTO tab_float VALUES(123.456,123.456,123.456) # 2表示小数点后两位
INSERT INTO tab_float VALUES(123.4,123.4,123.4) # 2表示小数点后两位
INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4) # 5表示整数+小数合起来是5位
SELECT * FROM tab_float
注意：M和D可以省略，不过省略后，DECIMAL默认是（10,0），float和double随着插入可自己变换。
2、字符型
-
较短的文本：char（长度固定，0-255），varchar（可变长度）
-
较长的文本：text，blob（较长的二进制）
-
char(M) M表示最大的字符个数，默认是1 固定长度的字符 耗费空间 效率高
-
varchar(M) M表示最多的字符个数 ，不可以省略 可变长度的字符 节省空间 效率低
-
一个中文两个字符
char：固定长度字符串类型；char(4) 范围是0-255
varchar：可变长度字符串类型；
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(c1 SET('a','b','c')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('a,b');
INSERT INTO tab_set VALUES('A,c,b');
INSERT INTO tab_set VALUES('m');SELECT * FROM tab_set;
3、日期型
SHOW VARIABLES LIKE 'time_zone'; # 查看当前时区
SET time_zone='+9:00' # 东9区，比我们多一个小时
CREATE TABLE tab_date(t1 DATETIME,t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT * FROM tab_date;
四、常见的约束
- 含义：限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性；
- 可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）
CREATE TABLE 表名(字段名 字段类型 列级约束,字段名 字段类型,表级约束
);
有以下六种约束:
- NOT NULL非空约束，规定某个字段不能为空，比如姓名，学号等必须填的。
- DEFAULT 默认值，比如：性别（省事）
- UNIQUE 唯一约束，规定某个字段在整个表中是唯一的，可以是空，比如座位号
- PRIMARY KEY 主键(非空且唯一)，比如学号，员工编号。每一行的唯一标识。
- CHECK 检查约束， MySQL不支持check约束，但可以使用check约束，而没有任何效果；
- FOREIGN KEY 外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表中添加外接约束用来限制主表的某列的值。例如主表中专业编号对应于从表中的某一个专业。
添加约束的时机：
- 修改表时候；
- 创建表的时候；
约束的添加分类：
- 列级约束：只能作用在一个列上，跟在列的定义后面。6大约束语法都支持（不报错），但是外键 约束没有效果。（在列后面直接加约束）
- 表级约束：可以作用在多个列上，不与列一起，而是单独定义。除了非空和默认都支持。（单独拿出来添加约束）
# 创建一个新的数据库
CREATE DATABASE students;
#一、创建表时候添加约束
#1、添加列级约束
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 NOT NULL, #唯一（座位号），约束可以叠加，唯一+非空age INT DEFAULT 18, #默认值18（年龄）majorID INT REFERENCES major(id) #外键，这里没效果，外键只支持表级约束（从表students，主表major）
);
只支持：默认，非空，主键和唯一
#查看索引
SHOW INDEX FROM stuinfo
1、创建表时候添加约束
#2、添加表级约束（非空和默认不支持）
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), #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注意：CONSTRAINT pk /CONSTRAINT uq /CONSTRAINT ck/CONSTRAINT fk_stuinfo_major可以省略不写，默认key_name 就是id/seat等。
通用写法：
- 非空和默认用列级
- 外键用表级
主键和唯一的对比：（经典面试）
| 保证唯一性 | 是否允许为空 | 一个表可以有几个 | 是否允许组合 | |
|---|---|---|---|---|
| 主键 | ✔ | ✘ | 最多一个 | 允许 |
| 唯一 | ✔ | ✔ | 多个 | 允许 |
#组合。主键（唯一）组合的话，id和stuname只要不是完全一样就不会报错，
# 即单独的名字和id都可以一样，但是不能同时一样。但是不推荐使用
primary key(id,stuname)
外键：
- 要求在从表中设置外键关系‘’
- 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求；
- 主表的关联列必须是一个key（一般是主键或者唯一）
- 插入数据的时候，应该先插入主表后插入从表
- 删除数据的时候先删除从表，再删除主表
- 约束可以多个添加，并不是每个字段只能添加一个
stuName VARCHAR(20) NOT NULL UNIQUE DEFAULT '张三',
2、修改表时候添加约束
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束；
2、添加的是表级约束
alter table 表名 add 【constraint 约束名】 约束类型（字段名） 【外键的引用】；*/
DROP TABLE IF EXISTS studinfo;
CREATE TABLE studinfo(id INT, #（学号）stuName VARCHAR(20) , #（姓名）gender CHAR(1), seat INT, #（座位号）age INT, #（年龄）majorID INT, #(专业编号）
);
#1、添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
#2、删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#3、添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#4、添加主键
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;#列级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id); #表级约束
#5、添加唯一
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;#列级约束
ALTER TABLE stuinfo ADD UNIQUE(seat); #表级约束
#4、添加外键
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);
3、修改表时候删除约束
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束；
2、添加的是表级约束
alter table 表名 add 【constraint 约束名】 约束类型（字段名） 【外键的引用】；*/
DROP TABLE IF EXISTS studinfo;
CREATE TABLE studinfo(id INT, #（学号）stuName VARCHAR(20) , #（姓名）gender CHAR(1), seat INT, #（座位号）age INT, #（年龄）majorID INT, #(专业编号）
);
#1、删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#2、删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
#3、删除主键
ALTER TABLE stuinfo MODIFY COLUMN id INT;
ALTER TABLE stuinfo DROP PRIMARY KEY;
#5、删除唯一
ALTER TABLE stuinfo MODIFY COLUMN seat INT;
ALTER TABLE stuinfo DROP INDEX seat;
#4、删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY majorid;
测试：
四、标识列
又称为自增长列，可以不用手动的插入值，系统提供默认的序列值。
#一、创建表时设置标识列
CREATE TABLE tab_identity(#不加AUTO_INCREMENT在插入的时候不能重复，得一个一个修改id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20)
);# 不用修改id=1,2，只用mull代替，运行一次会自动增长一下
INSERT INTO tab_identity VALUES(NULL,'john');
#或者
INSERT INTO tab_identity(name) VALUES('john');
#修改自增长步长
SET auto_increment_increment=3;
INSERT INTO tab_identity(NAME) VALUES('john');
TRUNCATE TABLE tab_identity; #删除表数据
#修改起始值为10
INSERT INTO tab_identity VALUES(10,'john');
#修改自增长步长
SET auto_increment_increment=3;
INSERT INTO tab_identity VALUES(NULL,'john');
特点：
- 标识列必须与主键搭配？不一定，但要求是一个key（主键唯一之类的）
- 一个表中只能有一个标识列；
- 标识列类型:只能是数值型（int，float，double）
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(id INT, NAME FLOAT,seat INT
);
#二、修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;#三、修改表时候，删除标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT;
定义顺序和执行顺序
