数据库DDL语言

数据库DDL语言

一、库的管理
1、库的创建

语法：
create database 【if not exists】库名;
*/

#案例：创建库Books
CREATE DATABASE IF NOT EXISTS books;

2、库的修改(一般不修改)
RENAME DATABASE books TO 新库名;

#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;

3、库的删除
DROP DATABASE IF EXISTS books;
二、表的管理
1、表的创建*


create table 表名(
	列名 列的类型【（长度）约束】,
	列名 列的类型【（长度）约束】，
	...
	列名 列的类型【（长度）约束】
)

*/
CREATE DATABASE IF NOT EXISTS books;
案例：创建表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)
)

2、表的修改

alter table 表名 add|drop|modify|change column 列名 【列类型 约束】

*/
#修改列名
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME; #column可以省略

#修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;

#添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;

#删除列
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,'金庸','中国');

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

复制表的结构+数据
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 1=2;

三、常见的数据类型

数值型：
	整型
	小数：
		定点数
		浮点数
字符型：
	较短的文本：char、varchar
	较长的文本：text,blob(较长的二进制数据)
日期型：
	
*/
一、整型

分类：
tinyint、smallint、mediumint、int/integer、bigint
    1	    2           3         4	     8
特点：
1、如果不设置无符号还是有符号，默认是有符号，如果像设置无符号，需要添加UNSIGNED关键字
2、如果插入的数值超出了整型的范围，则会报out of range异常，并且插入临界值。
3、如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够用0在左边填充，但必须搭配zerofill使用！
*/
#1、如何设置无符号和有符号
DROP TABLE tab_int;
CREATE TABLE tab_int(
	t1 INT,
	t2 INT UNSIGNED
);
DESC tab_int

INSERT INTO tab_int VALUES(-123456);

INSERT INTO tab_int VALUES(-123456,-123456);
SELECT * FROM tab_int;

二、小数

1、浮点型：
float（M,D）、double(M)
2、定点型
dec(M,D) decomal(M,D)

特点：
1、M和D：M代表的是，整数部位+小数部位
D：小数部位
如果超出范围，则插入临界值。

2、M和D都可以省略
如果是decimal，则M默认是10，D默认为0.
如果是float和double，则会根据插入的数值的精度来确定精度。

3、定点型的精度较高，如果要求插入数值的精度较高如货币运算等则考虑使用。

*/

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

三、字符型

较短的文本：
char：固定长度
varchar：可变长度

较长的文本：
text
blob(较大的二进制)

其他：
binary

特点：


	   写法       M的意思         		         特点            空间耗费    效率
char     char(M)      最大的字符数，可以省略默认为1   	固定长度的字符    比较耗费     高
varchar  varchar(M)   最大的字符数，不可以省略          可变长度的字符    比较节省     低
enum类型：enum('a','b')
set类型：
*/

#四、日期型

分类：
date只保存日期
time 只保存时间
year 只保存年

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

*/

常见约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性
分类：六大约束
	NOT NULL：非空，用于保证该字段的值不能为空，比如姓名、学号。
	DEFAULT：默认，用于保证该字段有默认值，比如性别
	PRIMAY KEY：主键，用于保证该字段的值具有唯一性，并且非空，如学号，编号。
	UNIQE：唯一，用于保证该字段的值具有唯一性，可以为空，比如座位号。
	CHECK：检查约束【mysql中不支持】
	比如年龄、性别
	FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。
		在从表添加外键约束，用于引用主表中的某一列的值。
	比如：学生表的专业编号。
添加约束的时机：
	1、创建表时
	2、修改表时

约束的添加分类：
	列级约束：
		六大约束语法上都支持，但是外键约束没有效果。
	表级约束：
		除了非空、默认，其他的都支持
主键和唯一的大对比：
		保证唯一性  是否允许为null  一个表中可以有多少个  是否允许组合
	主键	    是         否		至多有1个           是（不推荐）
	唯一        是         是               可以有很多个        是（不推荐）
外键的特点：
	1、要求在从表设置外键关系
	2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
	3、主表的关联列必须是一个key(一般是主键或唯一)
	4、插入数据时，先插入主表，再插入从表
	删除数据时，先删除从表，再删除主表
	
*/
CREATE TABLE 表名(
	字段名 字段类型 列级约束，
	字段名 字段类型，
	表级约束
)

CREATE DATABASE students;
一、创建表时添加约束

#1、添加列级约束
/*
语法：
直接在字段名和类型后面追加约束类型即可。

只支持：默认、非空、主键、唯一。
也可以在一个字段上添加多个约束

*/
USE students;

CREATE TABLE stuinfo(
	id INT PRIMARY KEY, #主键
	stuName VARCHAR(20) NOT NULL, #非空
	gender CHAR(1) CHECK(gender IN ('男','女')), #检查
	seat INT UNIQUE, #唯一
	age INT DEFAULT 18,#默认约束
	majorId INT REFERENCES major(id) #外键
);

CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)

);

DESC stuinfo;
#查看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), #主键
	CONSTRAINT uq UNIQUE(seat),    #唯一键
	CONSTRAINT ck CHECK(gender IN ('男','女')), #检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键
);


SHOW INDEX FROM stuinfo;

#通用的写法：
CREATE TABLE stuinfo(
	id INT PRIMARY KEY, #主键
	stuName VARCHAR(20) PRIMARY KEY, #非空
	gender CHAR(1) CHECK(gender IN ('男','女')), #检查
	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 表名 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
*/
CREATE TABLE stuinfo(
	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 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 UNION(seat);

#5、添加外键
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);


#三、修改表时删除约束

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

标识列

又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值

特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key。
2、一个表中可以有多少个标识列？至多一个
3、标识列的类型，只能是数值型
4、标识列可以通过SET auto_increment_increment=3; 设置步长。
可以通过手动插入值修改起始值。

*/
#一、创建表时设置标识列
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);

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 PRIMARY KEY;