五、DDL语言的学习
数据库的管理
数据库的创建
- CREATE DATABASE 【IF NOT EXISTS】库名 ;
【IF…】意为若库不存在则创建
数据库的修改
(想库名修改,只能修改MySQL保存数据的库文件名)
- ALTER DATABASE 库名 CHARACTER SET gbk(utf8);
更改库的基字符集
数据库的删除
- DROP DATABASE 【IF EXISTS】库名 ;
【IF…】意为若库存在则删除
表的管理
表的创建
- CREATE TABLE 【IF NOT EXISTS】表名
(
列名 列的类型 【(长度) 约束】,
列名 列的类型 【(长度) 约束】,
…
列名 列的类型 【(长度) 约束】
)
表的修改
-
修改列名:
ALTER TABLE 表名
CHANGE 【COLUMN】 旧列名 新列名 字段类型 ; -
修改列的类型或者约束:
ALTER TABLE 表名
MODIFY COLUMN 列名 字段类型 约束名; -
添加新列:
ALTER TABLE 表名
ADD COLUMN 新列名 字段类型 约束名
【FIRST|(AFTER 字段名)】;可以通过【…】定义新列的位置
-
删除列:
ALTER TABLE 表名
DROP COLUMN 列名 ; -
修改表名:
ALTER TABLE 表名
RENAME TO 新表名 ;
表的删除
- DROP TABLE 【IF EXISTS】表名 ;
表的复制
- 只复制表的结构:
CREATE TABLE 复制的表名 LIKE 被复制的表名 ; - 只复制表的部分结构:
CREATE TABLE 复制的表名
SELECT 想要的字段 FROM 被复制的表名 WHERE 0; - 复制表的结构+数据:
CREATE TABLE 复制的表名 SELECT * FROM 被复制的表名 ; - 只复制表的部分数据:
CREATE TABLE 复制的表名
SELECT 想要的字段 FROM 被复制的表名 WHERE 筛选条件 ;
常见数据类型
类型选择原则:所选择的类型越简单越好,能保存数值的类型越小越好
数值型
-
整型
-
分类:
tinyint(1),smallint(2),mediumint(3),
int/integer(4),bigint(8) -
特点:
-
默认是有符号数;如果想设置为无符号数,需要添加UNSIGNED关键字
-
如果插入的数值超出了当前定义整型的范围,会报错(out of range),并且插入临界值
-
如果不设置长度,会有默认长度
(长度是指显示的最大宽度,如果不够则会用0在左边填充,但必须搭配 ZEROFILL(默认为无符号数)关键字使用)
-
-
-
小数
-
浮点型
- float(M,D),double(M,D)
-
定点型
- dec(M,D)= decimal(M,D)
-
特点:
-
M:数值总位数长度(整数位数+小数位数);
D:小数点后的位数长度;
如果超过范围,则插入临界值
-
M和D都可以省略;
如果是decimal,则M默认为10,D默认为0;
如果float和double,则根据插入的数值精度来决定精度
-
定点型的精度比浮点型的精度高
-
-
字符型
写法 | M的意思 | 特点 | 空间的耗费 | 效率 | |
---|---|---|---|---|---|
char | char(M) | 最大字符数,可以省略,默认值为1 | 固定长度的字符 | 比较耗费 | 高 |
varchar | varchar(M) | 最大字符数,不可以省略 | 可变长度的字符 | 比较节省 | 低 |
- enum();用于保存枚举
- set();用于保存集合
日期型
- data;只保存日期
- time;只保存时间
- year;只保存年
字节 | 范围(年份) | 时区影响 | |
---|---|---|---|
datatime(保存日期+时间) | 8 | 1000-9999 | 不受 |
timestamp(保存日期+时间) | 4 | 1970-2038 | 受 |
常见约束👌
一种限制,用于限制表中数据,为了保证表中的数据的准确性和可靠性
六大约束
-
NOT NULL;
非空约束,用于保证该字段的值不为NULL
-
DEFAULT;
默认值约束,用于保证该字段有默认值
-
PRIMARY KEY;
-
主键,用于保证该字段的值具有唯一性,并且非空;
-
不可以定义多个主键约束的字段,但允许组合;
-
注意:表级约束中组合主键也只是一个主键
-
PRIMARY KEY(id,stuname)#组合主键 √
PRIMARY KEY(id)#定义多个主键约束 ×
PRIMARY KEY(stuname)
-
UNIQUE;
-
唯一性约束,用于保证该字段的值具有唯一性,可以为NULL;
-
注意:表级约束可以定义多个唯一性约束的字段,且允许组合
-
UNIQUE(id,stuname)#组合唯一性约束 √
UNIQUE(id)#定义多个唯一性约束 √
UNIQUE(stuname)
-
CHECK;
检查约束
-
FOREIGN KEY;
- 外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值;
- 在从表添加外键约束,用于引用主表中的某列值;
- 创建格式:
【CONSTRAINT 约束名】 FOREIGN KEY(字段名)
REFERENCES 主表名(被引用列名); - 注意:
- 要求在从表设置外键关系;
- 从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求;
- 主表的关联列必须是一个KEY(一般是主键或具有唯一性约束);
- 插入数据时,先插入主表,再插入从表,删除数据反之;
#删除主表的记录
#方式一:级联删除
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(字段名) REFERENCES 主表名(被引用列名) ON DELETE CASCADE;
#方式二:级联置空
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(字段名) REFERENCES 主表名(被引用列名) ON DELETE CASCADE;
约束的添加分类
-
列级约束:
- 六大约束语法上都支持,但外键约束没有效果;
- 位于定义字段的末尾;
每一个字段可以用空格隔开添加多个列级约束
(前提:约束间不冲突)
-
表级约束:
-
【CONSTRAINT 约束名】 约束类型 (字段名);
位于表中最末行; -
除了非空、默认约束,其他约束都支持
-
修改表时添加或删除约束
- 添加约束
- 添加非空、默认约束的方法:
ALTER TABLE 表名
MODIFY COLUMN 列名 字段类型 约束名; - 添加主键、唯一性约束的方法:
- 列级约束:
ALTER TABLE 表名
ADD COLUMN 列名 字段类型 约束名 ; - 表级约束:
ALTER TABLE 表名
ADD COLUMN 【CONSTRAINT 约束名】约束名(列名 )
- 列级约束:
- 添加外键的方法(表级约束):
ALTER TABLE 表名
ADD COLUMN
【CONSTRAINT 约束名】 FOREIGN KEY(字段名)
REFERENCES 主表名(被引用列名);
- 添加非空、默认约束的方法:
- 删除约束
- 删除非空、默认约束:
ALTER TABLE 表名
MODIFY COLUMN 列名 字段类型 ; - 删除主键约束:
ALTER TABLE 表名
DROP PRIMARY KEY; - 删除唯一性约束:
ALTER TABLE 表名
DROP INDEX 列名; - 删除外键约束:
ALTER TABLE 表名
DROP FOREIGN KEY 列名(约束名);
- 删除非空、默认约束:
#列级约束SA:
CREATE TABLE stuinfo(
id INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL,#非空约束
gender CHAR(1) CHECK(gender IN ('男','女')),#检查(无语法错误,但博主当前版本MySQL无效)
seat INT UNIQUE,#唯一约束
age INT DEFAULT(18),#默认约束
majorId INT REFERENCES major(id)#外键(无语法错误,但无效)
);
#表级约束SA:
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 IN ('男','女')),#检查(无语法错误,但博主当前版本MySQL无效)
CONSTRAINT FK_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
#主表:
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
标识列
又称自增长列;可以不用手动的插入值,系统提供默认的的序列值
-
AUTO_INCREMENT;
-
特点:
- 标识列不一定要和主键搭配,但是必须要有一个KEY;
- 一个表只可以有一个标识列;
- 标识列的类型只能是数值型;
- 通过SET AUTO_INCREMENT_INCREMENT设置自增长步长;
可以通过手动插入数值设置起始值