数据定义语言(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;