SQL-DDL语言-数据定义语言-库和表的管理

#DDL语言 数据定义语言P110
/*
#库和管理
创建、修改、删除
#标的管理
创建、修改、删除
创建：create
修改：alter
删除：drop
*/

#一、创建库
#语法：create database 库名;
#if not exists表示如果该库存在，就报错以进行提醒
CREATE DATABASE IF NOT EXISTS books;

#不可以更改库的名字，可以更改字符集
ALTER DATABASE books CHARACTER SET gbk;#修改字符集
#删除库
DROP DATABASE IF EXISTS books;

#二、表的管理
#1.表的创建
/*
语法：
create table 表明(
	列名 列的类型【长度，约束等】,
	列名 列的类型【长度，约束等】,
	列名 列的类型【长度，约束等】,
	...	
)

#表管理
alter drop|add|modify|change column 列名【类型和约束】

*/

#创建表
CREATE TABLE IF NOT EXISTS book(
	id INT,
	bname VARCHAR(20),
	price DOUBLE,
	author VARCHAR(20),
	publishdate DATETIME
);
DESC book;
#修改表
#修改列名，类型和约束，添加列，删除列
#1.修改列名change
ALTER TABLE book CHANGE COLUMN publishdate pubdate DATETIME;

#2.修改列的类型或约束modify
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

#添加新列add
ALTER TABLE book ADD COLUMN press VARCHAR(20) FIRST;#column均可省略
#通过添加first 和after 可以确定添加的列名所在的顺序
ALTER TABLE book ADD COLUMN press VARCHAR(20) AFTER price;


#删除列drop
ALTER TABLE book DROP COLUMN press;

#修改表名
ALTER TABLE book RENAME TO book_situation;

#查看表
DESC book_situation;

#表的删除
DROP TABLE IF EXISTS book_author;
SHOW TABLES;#查看当前库的所有表

DROP DATABASE IF EXISTS 旧库;
CREATE DATABASE 库名;

DROP TABLE IF EXISTS 旧表;
CREATE TABLE 表名;

#表的复制：
CREATE TABLE copy LIKE book;
#复制表的结构+数据
CREATE TABLE copy2 SELECT * FROM book;
#只复制部分数据
CREATE TABLE copy3
SELECT id,nameb
FROM book
WHERE price<50;

#只复制列的结构
CREATE TABLE copy4
SELECT id,nameb
WHERE 1=2;#设置成不满足的条件可以复制部分表结构

#常见的数据类型
/*
数值型：
	整型
	小数：定点数decimal、浮点数float、double（定点数更加精确）
	dec(M,D) decimal(M,D) float(M,D)小数点后保留D位，M代表整数部位外加小数部位
字符型：
	较短的文本char、varchar
	较长的文本text、blob(较长的二进制数据)
	
日期型：date、time、year、datetime、datestamp
*/
CREATE tabletab_int(
	t1 INT,
	t2 INT UNSIGNED#默认有符号，无符号需要进行标注
);


#原则--所选择的类型越简单越好

#设置仅能插入的选项。例如性别栏
CREATE TABLE tab_char(
	c ENUM("男","女")#表示枚举
);#此时的选项只能输入男和女两个内容
#set与enum相似，但是可以包含集合中的所有的子集和真子集

#DDL常见约束
/*
含义：一种限制，用于限制表中的数据，保证表中数据的准确和可靠性
分类：六大约束
	not null 非空,用于保证该字段的值不能为空，例如姓名、学号
	default 默认，用于保证该字段有默认值，例如性别
	primary key 主键值，保证该字段的值具有唯一性，例如学号、员工编号
	unique 唯一，用以保证该字段的值具有唯一性，可以为空，例如座位号
	check：检查约束【在mysql中不支持】,例如年龄、性别
	foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
		在从表中添加外键约束，用于引用主表中的某列的值
		例如学生表的专业编号、员工表的工种编号

添加约束的时机：
	1.创建表时
	2.修改表时
约束的添加分类：
	1.列级约束，六大约束语法都支持，但外键约束没有效果
	2.表级约束，除了非空、默认，其他的都支持

#添加多种约束，直接在约束后面添加空格后继续添加其他约束即可
*/
/*
create table 表名(
	字段名 字段类型 列级约束,
	表级约束
);
*/
#一、创建表时如何添加约束
#1.添加列级约束--直接在字段名和类型后面追加约束类型即可
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuname VARCHAR(20) NOT NULL,#非空
	gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查
	seat INT UNIQUE,#唯一
	age INT DEFAULT 18#设置默认约束
);

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

#添加表级约束--在各个字段的最下面：【constrain 约束名】 约束类型（字段名）
#中括号【】中的可省略
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	CONSTRAINT pk PRIMARY KEY(id),#主键
	CONSISTENT uq UNIQUE(seat),#唯一键
	CONSISTENT ck CHECK(gender='男' OR gender='女'),#检查
);
#通用写法:
#主表
CREATE TABLE major(
	id INT PRIMARY KEY,
	majorname VARCHAR(20)
);
#从表
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,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);



#一般只有外键才添加表级约束，外键约束其索引命名为fk表示外键，stuinfo表示从表，major表示主表，其中majorid对应的值只能是主表major中id的值。
#主键和唯一的对比：
/*
	均保证唯一性
	主键不允许为空，唯一可以允许为空
	主键中在一个表中至多有一个，而唯一键可以有多个
	允许两个列组合成一个主键primary(id,seat),unique(id,seat)都是允许的，但是不推荐
*/	
#1.外键要求在从表设置外键约束,在列级约束中没有效果
#2.从表的外键列类型和主表的管理案例额的类型要求一致或兼容
#3.要求主表的关联键必须是一个key（一般是主键或唯一键）
#4.插入数据时应该先插入主表再插入从表，删除数据时应该先删除从表再删除主表

#删除表：drop table if exisits stuinfo;
#删除表中的内容：delete from stuinfo where id=3;truncate table stuinfo;

#列级约束不可以起约束名，表级约束可以，但对主键没有效果，主键是默认的
#列级约束放在列的后面，表级约束放在所有列的下面
#列级约束对所有语法都支持，但对外键没有效果
#表级约束对默认和非空不支持，其他支持

#二、修改表时添加约束
/*
alter table 表名 modify column 字段名 类型 约束
ALTER TABLE 表名 add COLUMN 字段名 类型 约束
*/

#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;#和修改列的类型的语法相似
#2.默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#a.列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#b.表级约束
ALTER TABLE stuindo ADD COLUMN id INT PRIMARY KEY;

#4.添加唯一键
#a.列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#b.表级约束
ALTER TABLE stuindo ADD COLUMN seat INT UNIQUE;

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

#三、修改表时如何删除约束
#删除非空
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
#删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;
#删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
#删除唯一键
ALTER TABLE stuinfo DROP INDEX seat;#看唯一键有没有起名
#删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY majorid;
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

#标识列--又称为自增长列，含义：可以不用手动插入值，系统默认的序列值
#特点：标识列

CREATE TABLE major(
	id INT PRIMARY KEY AUTO_INCREMENT,#标识列需要和key一起使用，可以使primary也可以是unique
	majorname VARCHAR(20)
);
#一个表中可以有多个标识列，但是其职能是数值列
#可通过SET auto_increment_increment=3;设置步长，也可以初始设置id号，确定初始值

SHOW VARIABLES LIKE '%auto_increment%';
#可以设置步长增长：
SET auto_increment_increment=3;#如果设置后，之后这张表的自增长列都将按3增长

#修改表时设置标识列：
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;


#TCL语言事务控制语言