MySQL学习笔记4_DDL数据定义语言

一、库的管理

语法

创建：
	create database [IF NOT EXISTS] 库名[character set 字符集名]；
修改：
	改名字直接在data文件夹里修改
删除：
	drop database [IF EXISTS] 库名；

案例1：创建库books

CREATE DATABASE IF NOT EXISTS books;

案例2：更改books库的字符集为gbk

ALTER DATABASE books CHARACTER SET gbk;

案例3：删除books库

DROP DATABASE IF EXISTS books;

二、表的管理

语法

创建：
		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 表名；
复制：
		①仅仅复制表A的结构
		create table 表名 like A;
		②复制表A的结构+数据
		create table 表名 select * from A;
		③仅仅复制部分结构,不要数据
		create table 表名
		select 需要的列名
		from A
		where 1=2；

案例1：创建表book

CREATE TABLE book(
		id INT, #编号
		bName VARCHAR(20), #图书名
		price DOUBLE, #价格
		authorID INT , #作者编号
		publishDate DATETIME #出版日期
		
);
DESC book;

案例2：创建表author

CREATE TABLE author(
		id INT, #作者编号
		au_name VARCHAR(20),
		nation VARCHAR(10)
) ;
DESC author;

三、常见数据类型

3.1 数值型

3.1.1 整型

分类	tinyint	smallint	mediumint	int	bigint
所占字节	1	2	3	4	8

特点：
① 默认是有符号，如果设置为无符号，添加关键字unsigned
②如果插入的数值超出整型范围，会报out of range,并且插入临界值
③如果不设置长度，会有默认长度。
长度代表了显示的最大宽度，zerofill：如果不够会用0在左边填充，自动设置无符号

案例1：如何设置无符号和有符号

DROP TABLE t_int
CREATE TABLE t_int (
	t1 INT,
	t2 INT UNSIGNED, #无符号
	t3 INT(7) ZEROFILL
	
);
DESC t_int;
INSERT INTO t_int VALUES(-123,123,123);

3.1.2 小数：

分类：
1.定点型
float(M,D) double(M,D)
2.浮点型
dec(M,D) decimal(M,D)
特点：
①M代表整数+小数总长度
D代表四舍五入保留小数位的长度
如果超过范围，则插入临界值
②M和D都可以省略.如果是定点型则默认（10，0），否则根据插入的数值来决定。
③定点型的精确度较高（货币运算）
原则：
所选择的类型越简单越好，能保存数值的类型越小越好

案例2：浮点型和定点型

CREATE TABLE t_float(
	f1 FLOAT(3,1) ,
	f2 DOUBLE,
	f3 DECIMAL
);
DESC t_float;
INSERT INTO t_float VALUES (12.56,123.45,123456789.23);

3.2 字符型：

3.2.1 较短的文本：

	写法	M的意思	特点	空间的耗费	效率
char	char(M)	最大的字符数，可省默认为1	固定长度的字符	比较耗费	高
varchar	varchar(M)	最大的字符数，不可省	可变长度的字符	比较节省	低

• binary/varbinary 包含二进制字符串、不包含非二进制字符串
• enum 枚举型
• set集合型

案例3：enum

CREATE TABLE t_enum(
	e1 ENUM('a','b')
);
INSERT INTO t_enum VALUES('a');
INSERT INTO t_enum VALUES('B');
INSERT INTO t_enum VALUES('c');
INSERT INTO t_enum VALUES('b');
SELECT * FROM t_enum;

案例4：set

CREATE TABLE t_set(
	s1 SET('a','b','c')
);
INSERT INTO t_set VALUES('a');
INSERT INTO t_set VALUES('a,b');
INSERT INTO t_set VALUES('a,c,b');
SELECT * FROM t_set;

3.2.2 较长的文本

text,blob(较长的二进制数据)

3.3 日期型

分类：
date 只保存日期
time 只保存时间
year 只保存年份
datetime 保存日期+时间
timestamp 保存日期+时间
特点:

	字节	范围	受时区等的影响
datetime	8	1000-9999	不受
timestamp	4	1979-2038	受

案例5：datetime 和timestamp

CREATE TABLE t_date(
	t1 DATETIME,
	t2 TIMESTAMP
);
INSERT INTO t_date VALUES(NOW(),NOW());
SELECT * FROM t_date;
SHOW VARIABLES LIKE 'time_zone'; # 查看当前时区
SET time_zone='+9:00'; #更改时区

四、常见约束

含义
一种限制，用于限制表中的数据，为了保证数据的一致性

分类：六大约束

• not null 非空，用于保证该字段的值不能为空
• default 默认，用于保证该字段有默认值
• primary key 主键，用于保证该字段的值具有唯一性，并且非空
• unique 唯一，用于保证该字段的值具有唯一性，可以为空
• check 检查约束【mysql中不支持】
• foreign key 外键，用于限制两个表的关系，保证该字段的值必须来自于主表关联列的值

添加约束的时机
①创建表时
②修改表时

约束添加分类：

• 列级约束：
  六大约束语法上都支持，但外键约束没有效果
• 表级约束：
  除了非空、默认其他都支持

create table 表名 (
	字段名  字段类型  列级约束，
	字段名  字段类型。
	表级约束
)

主键和唯一的对比

	保证唯一性	是否允许为空	一个表中可以有	是否允许组合
主键	√	×	1个	√
唯一	√	√（只允许有一个空值）	多个	√

外键：
①要求在从表设置外键关系
②类型要求一致或兼容
③主表中的关联列必须是一个key(一般是主键或唯一)
④插入数据时，先插入主表，再插入从表
⑤删除数据时，先删除从表，再删除主表

添加外键后，删除从表某一数据时，一起删掉主表中对应不上的数据
	方式一：级联删除 
		添加外键时在句尾加 ON DELETE CASCADE;
	方式二：级联置空（不删除，在相应位置换成空值）
		添加外键时在句尾加 ON DELETE SET NULL;

4.1 创建表时添加约束

4.1.1 添加列级约束

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

# 查看表中所有的索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;

4.1.2 添加表级约束

#constraint 约束名 约束类型(字段名) ,不起名也可以，起名防止约束重复
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR,
	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) #外键 
);

4.2 修改表时添加约束

#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 UNIQUE(seat);
#5.添加外键
ALTER TABLE stuinfo ADD FOREIGN KEY (majorid) REFERENCES major(id);

4.3 修改表时删除约束

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

五、标识列

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

特点：
①标识列必须和key搭配（主键、外键、唯一）
②一个表中只能有一个标识列
③标识列的类型只能是数值型
④标识列可以通过SET auto_increment_increment=3;设置步长
通过插入第一个数据时设定起始值

5.1 创建表时设置标识列

CREATE TABLE t_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)

);
INSERT INTO t_identity(NAME) VALUES ('jack');
SELECT * FROM t_identity;

SHOW VARIABLES LIKE '%auto_increment%';
SET  auto_increment_increment=3; #重新设置步长，起始值mysql无法直接更改，可以再插入第一个数据时设定

5.2 修改表时设置标识列

ALTER TABLE t_identity MODIFY COLUMN id INT AUTO_INCREMENT;

5.3 修改表时删除标识列

ALTER TABLE t_identity MODIFY COLUMN id INT ;