Mysql-DDL库和表的管理 常见数据类 型常见约束

库和表的管理

#DDL
/*
数据定义语言
库和表的管理

一 库的管理
创建，修改，删除
二 表的管理
创建，修改，删除

创建：create
修改：alter
删除：drop

*/
#库的管理
/*1，库的创建
create database 库名；
*/
#1创建books库
CREATE DATABASE IF NOT EXISTS books;
#2库的修改
RENAME DATABASE books TO 新库名;
#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
#3库的删除
DROP DATABASE IF EXISTS books;

#表的管理
#一  表的创建【*】
/*
create table 表名(
	列名 列的类型【(长度) 约束】，
	列名 列的类型【(长度) 约束】，
	...
	列名 列的类型【(长度) 约束】
)
*/
#创建book表
CREATE TABLE IF NOT EXISTS book(
	id INT,
	bName VARCHAR(20),
	price DOUBLE,
	authorId INT,
	publishDate DATETIME
);
#创建author表
CREATE TABLE author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)
);
DESC author

#二 表的修改 
/*
alter table 表名 add|drop|modify|change column 列名【列类型，约束】
*/

#修改列名
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
#修改列的类型或约束
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

DESC book

#三 表的删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;

#通用的写法
DROP DATABASE IF EXISTS 旧库名；
CREATE DATABASE 新库名;

DROP TABLE IF EXISTS 旧表名；
CREATE TABLE 新表名()；

#表的复制
SELECT * FROM author

INSERT INTO author VALUES
(1,'盖世凯1','中国'),
(2,'盖世凯2','中国'),
(3,'盖世凯3','中国'),
(4,'盖世凯4','中国');
#1仅仅复制表的结构
CREATE TABLE copy LIKE author;
#2.0复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;
#2.1只复制表的部分内容
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';
#2.2仅仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;


#--------------------------------------------------------------
#库和表的练习
#1，创建表dept1
USE test;

CREATE TABLE dept1(
	id INT(7),
	NAME VARCHAR(25)
);
#2将department中的东西复制过来
CREATE TABLE dept2
SELECT department_id,department_name
FROM myemployees.`departments`
#3创建表emp5
CREATE TABLE emp5(
	id INT(7),
	first_name VARCHAR(25),
	last_name VARCHAR(25),
	dept_id INT(7)
);
#4将last_name的长度增加到50
ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50)
#5根据employees创建employees2
CREATE TABLE employees2 LIKE myemployees.`employees`;
#6删除表emp5
DROP TABLE IF EXISTS emp5
#7将表employees2重命名为emp5
ALTER TABLE employees2 RENAME TO emp5
#8在表dept和emp5中添加新的列test_column 并检查所作操作
ALTER TABLE emp5 ADD COLUMN test_column INT;
#9直接删除表emp5中的列dept_id
ALTER TABLE emp5 DROP COLUMN test_column

常见的数据类型

#常见的数据类型
/*
数值型：
	整数
	小数：
		定点数
		浮点数
字符型：
	较短的文本：char,varchar
	较长的文本：text,blob（较长的二进制数据）
日期型：
*/

#整型
/*
分类：
tinyint   smallint   mediumint   int/integer   bigint
1		2	3		4	8
特点：
1，如果不设置无符号还是有符号，默认是有符号，如果想要设置无符号，添加unsigned关键字
2，如果插入的数值超出了整型的范围  会报 out of range异常，并且插入临界值
3，如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用
*/
CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL
	t2 INT(7) #设置zerofill默认是无符号
)
#小数
/*
1浮点型：
	float(M,D)
	double(M,D)
2定点型
	dec(M,D)
	decimal(M,D)
	
特点：
M和D（1）
M：整数部门+小数部位
D：小数部位
如果超出范围，则插入临界值

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

M和D（3）
定点型的精确度较高，如果要求插入数值的精度较高或者 货币运算的话 优先考虑

原则：选择的类型越简单越好，能保存数值的类型越小越好
*/
CREATE TABLE tab_float(
	f1 FLOAT,
	f2 DOUBLE,
	f3 DECIMAL
);


#字符型
/*
	较短的文本：char,varchar
	较长的文本：text,blob（较长的二进制数据）
特点
	写法 		M的意思		特点		空间的耗费	效率
char	char(M)		最大的字符数	固定长度的字符	比较耗费空间	高
varchar	varchar(M)	最大的字符数	可变长度的字符	比较节省	低
*/

#Enum类型
/*
要求插入的值必须属于列表中指定的值之一
*/
CREATE TABLE tab_char(
	c1 ENUM('a','b','c')
)
INSERT INTO tab_char VALUES('a')#可以
INSERT INTO tab_char VALUES('A')#不可以

#Set类型
/*
和enum差不多 就是他可以选取多个成员，而enum只能选取一个
*/

#日期型
/*
分类：
	date 只保存日期
	time 只保存时间
	year 只保存年
	
	datetime 保存日期+时间
	timestamp 保存日期+时间
	
特点：
		字节	范围		时区等的影响
datetime	8	1000-9999	不受
timestamp	4	1970-2038	受
*/
CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW())
SELECT * FROM tab_date;
#设置时区
SHOW VARIABLES LIKE 'time_zone'
SET time_zone='+9:00'

常见的六大约束

#常见的约束
/*
含义：一种限制，用于限制表中的数据，为了保证表中数据的准确性和可靠性
分类：六大约束
	NOT NULL :非空，用于保证该字段的值不能为空eg：姓名，学号
	DEFFAULT：默认，用于保证该字段有默认值eg：比如性别
	PARIMARY KEY：主键，用于保证该字段具有唯一性，并且非空eg：学号，员工编号
	UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空eg：座位号
	CHECK：检查约束【sql中不支持】eg：年龄，性别
	FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联列的值
		在从表添加外键约束，用于引用主表中某列的值
		eg：学生的专业编号，员工表的部门编号，都要有一个大的主表来约束
添加约束条件的时机：
	1，创建表时
	2，修改表时
约束的添加分类：
	1，列级约束：六大约束语法上都支持，但外键约束没有效果
	2，表级约束：除了非空，默认，其他的都支持
*/
CREATE TABLE 表名(
	字段名 字段类型 列级约束，
	字段名 字段类型，
	表级约束
)
CREATE DATABASE students
#一，创建表的时候添加约束
/*
#添加列级约束
语法:直接往 字段名和类型 后面追加约束条件即可
只支持：非空，主键，唯一，默认
*/
USE students
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL, #非空
	gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查
	seat INT UNIQUE ,#唯一
	age INT DEFAULT 18,#默认约束
	majorId INT REFERENCES major(id)#外键
);
/*
#添加表级约束
语法:在各个字段的最下面
只支持：【constraint 约束名】 约束类型(字段名)
*/
CREATE TABLE IF NOT EXISTS 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 ='男' OR gender='女')#检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
	*/
	PRIMARY KEY(id),#主键
	UNIQUE(seat),#唯一
	CHECK(gender ='男' OR gender='女'),#检查
	FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
#通用的写法
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuName VARCHAR(20) NOT NULL,
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE ,
	majorid INT,
	CONSTRAINT fk FOREIGN KEY(majorid) REFERENCES major(id)
);
CREATE TABLE IF NOT EXISTS major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);
DESC stuinfo;
SHOW INDEX FROM stuinfo;
DROP TABLE stuinfo;
/*
主键和唯一键的pk
	保证唯一性 	是否允许为空	一个表中可以有多少个	是否允许组合
主键	yes		no		最多一个		可以，不推荐
唯一键	yes		no		可以有多个		可以，不推荐
*/
/*
外键：
	1，要求在 从表 设置外键关系
	2，从表的外键列的类型 和 主表的关联列的类型 要求一致或者兼容，名称没有别的要求
	3，主表的关联列必须是一个key(一般时主键或唯一)
	4，插入数据时，先插入主表，再插入从表
	   删除数据时，先删除从表，在删除主表
#4的两种方法
级联删除
alter table stuinfo add constraint fk foreign key(majorid) references major(id) on delete cascade
级联置空
alter table stuinfo add constraint fk foreign key(majorid) references major(id) on delete set null

*/


#一，创建表的时候添加约束
/*
1,添加列级约束
alter table 表名 modity column 字段名 字段类型 新约束
2，添加表级约束
alter table 表名 add 【constraint 约束名】约束类型(字段名) 【外键的引用】
*/
DROP TABLE IF EXISTS stuinfo;
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 PRIMARY KEY;
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#4添加唯一(两种方式)
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk 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 FOREIGN KEY fk;
/*
		位置		支持的约束类型		是否可以起别名
列级约束	列的后面	都支持，外键没有效果		不可以
表级约束	所有列的下面	默认非空不支持，其他支持	可以（主键没有效果）
*/