MySQL系列目录:
MySQL中DDL语言(数据定义语言)
数据定义语言
库和表的管理
一、库的管理:创建修改删除
二、表的管理:创建create修改alter删除drop
一、库的管理
1.库的创建
- 语法:
CREATE DATABASE [IF NOT EXISTS] 库名
2.库的修改
- 数据库的修改非常不安全。当作目前不修改
- 更改库的字符集:
ALTER DATABASE books CHARACTER SET gbk
3.库的删除
- 语法:
DROP DATABASE IF EXISTS books;
二、表的管理:创建create修改alter删除drop
1.表的创建
- 语法
create table 表明 (
fieldname fieldtype[(length) constraints]
fieldname fieldtype[(length) constraints]
fieldname fieldtype[(length) constraints]
)
2.表的修改
通用语法:ALTER TABLE 表明 add | drop | modify | change column 列名【列类型 约束】
- 修改field
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
- 修改field的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;
- 添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
- 删除列
ALTER TABLE author DROP COLUMN anuual;
- 修改表名
ALTER TABLE author RENAME TO book_author;
3.表的删除
通用的写法:DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE 表没那个();
DROP TABLE [ IF EXISTS] book_author
4.表的复制
- 仅仅复制表的结构
CREATE TABLE author_copy LIKE author
- 复制表的结构和(部分或全部)数据
CREATE TABLE copy2 SELECT * FROM author;
- 仅仅复制某些field
CREATE TABLE copy4 SELECRT id, au_name FROM author WHERE 0;
三、表的数据类型
- 数值型:整型,小数(定点数,浮点数)
- 字符型
- 较短的文本:char,varchar
- 较长的文本:text,blob(较长的二进制数据)
- 日期型
1. 整型
- 分类:tinyint,smallint,mediumint,int/integer,bigint(1,2,4,8 bytes)
- 如何设置无符号和有符号?
CREATE TABLE tab_int (
t1 INT, # 设置有符号(默认有符号)
t2 INT UNSIGNED # 设置无符号
);
- 如果不设置长度,会有默认的长度。如果用zerofill会默认变成无符号。这个长度不决定数字的range。长度代表显示的最大宽度,如果不够用0在左边填充,用zerofill。
2. 小数
M: 代表整数部位长度+小数部位长度
D: 表示小数点后显示的位数
如果超过范围,则插入临界值。
M和D都可以省略
如果是deciaml,则M默认为10,D默认为0,如果超过,插入临界值。
如果是float和double,则会根据插入的数值的精度来决定精度。
定点型精度较高,要求插入精度高或者货币可以用定点型。
- 浮点型 float(4bytes),double(8bytes)
- float(M, D)
- double(M, D)
- 定点型DECIMAL或DEC,最大去之范围和double相同,
- dec(M, D)
- decimal (M, D)
- feature:
3. 字符型
较短的文本:char,varchar
较长的文本:text,blob
其他:binary和varbinary用于保存较短的二进制;enum用于保存枚举;set用于保存集合。
- feature
- char(M,可以省略默认为1),最大的字符数;固定长度的字符;耗费空间;效率高。
- varchar(M,不可以省略),最大的字符数;可变长度的字符;比较节省;效率低。
4. 日期型
datetime和timestamp用的更多。
四、常见约束
- 含义:约束用于限制表中的数据,为了保证表中的数据的准确和可靠性。
- 分类:六大约束
- 添加约束的时间点:1)创建表时;2)修改表时
- 约束的添加分类:
CREATE TABLE tablename (
name int 列级约束
表级约束
)
1)列级约束:六大约束语法上都支持,但外键约束没有效果。
2)表级约束:除了非空,默认,其他的都支持。
表列级对比 | 支持类型 | 起约束名 |
---|---|---|
列级约束 | 除了外键 | 不可以 |
表级约束 | 除了非空NOT NULL和默认DEFAULT | 可以,但对主键无效 |
1. 创建表时添加列级约束
- 语法:直接在字段名和类型后面追加 类型约束即可。
- 仅支持:默认,非空,主键,唯一。
CREATE TABLE student_info (
id INT PRIMARY KEY, # 主键
stuName VARCHAR(20) NOT NULL, # 非空
gender CHAR(1) CHECK(gender='b' OR gender = 'g'), # 检查(列级没效果)
seat INT UNIQUE, # 唯一
age INT DEFAULT 10, # 默认约束
marjorId INT REFERENCES major(id) # 外键(列级这里没效果)
)
2.添加表级约束
CONSTRAINT nickname 可以省略。
CREATE TABLE student_info (
id INT,
stuName VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
marjorId INT,
CONSTRAINT pk PRIMARY KEY(id), # 主键,改了别名也没有对于主键来说
CONSTRAINT uq UNIQUE(seat),
CONSTRAINT ck CHECK(gender='b' OR gender = 'g'),
CONSTRAINT fk stuin_major FOREIGN KEY(majorid) REFERENCES major(id)
)
3. 主键和唯一的区别
- 一个表至多有一个主键,但可以有多个唯一
- 主键不允许为空,唯一可以为空null。
4.外键 和 删除
- 表级约束语法(只能表级约束):
CONSTRAINT 约束名 FOREIGN KEY (字段名) REFERENCES 主表 (被引用列);
- 要求在从表设置外键关系。
- 从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
- 主表的关联列必须是一个key(一般是主键)。
- 插入数据时,先插入主表,再插入从表。删除数据时,先删除从表,再删除主表。
4.1 FOREIGN KEY & 删除
- 方式一:级联删除
ON DELETE CASCADE;
- 方式二:级联置空
ON DELETE SET NULL;
5. 修改表时添加约束
- 添加列级约束:
ALTER TABLE 表名 modify column 字段名 字段类型 新约束;
- 添加表级约束:
ALTER TABLE 表名 add [constraint 约束名] 约束类型(字段名) 【外键的引用】;
支持列级约束的都可以!
- 添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
- 添加默认约束
- 添加主键。
表级约束:ADD PRIMARY KEY(id);
列级约束:MODIFY COLUMN id INT PRIMARY KEY
- 添加唯一
列级约束:ALTER TABLE studinfo MODIFY COLUMN seat INT UNIQUE;
表级约束:ALTER TABLE stuinfo ADD UNIQUE(seat);
- 添加外键
ALTER TABLE stuinfo ADD [CONSTRAINT nickname ] FOREIGN KEY (marjorid) REFERENCES major(id);
6. 修改表时删除约束
五、标识列(自增长列 AUTO_INCREMENT)
- 含义:可以不用手动的插入值,系统提供默认的序列值,默认从1开始,步长为1。
- 代码:
- 创建表时设置标识列:
id INT PRIMARY KEY AUTO_INCREMENT
- 修改表时设置标识列:
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
- 修改表时删除标识列:
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY;
- 创建表时设置标识列:
- 特点:
- 标识列必须和key搭配(mysql),一般是和主键。
- 一个表最多只能有一个自增长列。
- 标识列的类型只能是数值型
- 标识列可以通过
SET AUTO_INCREMENT_INCREMENT=3;
设置步长;也可以通过手动插入值,设置起始值。