库和表的管理

DDL语言的学习

库和表的管理 √

库的管理

一、创建库
create database 库名
二、删除库
drop database 库名

表的管理

#1.创建表
格式：
	create table 表名(
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	...
	列名 列的类型【(长度) 约束】	
	)

创建：
CREATE TABLE IF NOT EXISTS stuinfo(
	stuId INT,
	stuName VARCHAR(20),
	gender CHAR,
	bornDate DATETIME
);

DESC studentinfo;
#2.修改表 alter
	语法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名 【字段类型】;

#①修改字段名
	ALTER TABLE studentinfo CHANGE  COLUMN sex gender CHAR;

#②修改表名
	ALTER TABLE stuinfo RENAME [TO]  studentinfo;

#③修改字段类型和列级约束
	ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;

#④添加字段
	ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20) first;

#⑤删除字段
	ALTER TABLE studentinfo DROP COLUMN email;

#3.删除表
	DROP TABLE [IF EXISTS] studentinfo;	
	
#4.删除一个库中的所有表

    #生成所有需要删除表的sql语句，从结果集复制所有行的数据到剪贴板就好了
	SELECT CONCAT('DROP TABLE IF EXISTS ', table_name, ';')
	FROM information_schema.tables
	WHERE table_schema = 'test';	

#4.表的复制
#4.1仅仅复制表的结构
	CREATE TABLE copy LIKE author;

#4.2复制表的结构+数据
	CREATE TABLE copy2
	SELECT * FROM author;

#4.3复制表的部分数据
	CREATE TABLE copy3
	SELECT id,au_name
	FROM author
	WHERE nation='中国';
	
#4.4仅仅复制 某些字段
	CREATE TABLE copy4
	SELECT id,au_name
	FROM author
	WHERE 0;

标识列

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

特点：

1.标识列不是一定要和主键搭配，但要求和它搭配的是一个key
2.一个表可以有至多一个标识列
3.标识列的类型只能是数值型
4.标识列可以通过SET auto_increment_increment=1;设置步长
  也可以通过手动插入值，来设置起始值

#一、创建表时设置标识列

CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
)
#设置步长
SET auto_increment_increment=1;

#二、修改表时设置标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

#三、修改表时删除标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT;

常见数据类型介绍 √

分类

数值型：
	整型
	小数：
		定点数
		浮点数
字符型：
	较短的文本：char、varchar
	较长的文本：text、blob（较长的二进制数据）

具体

整型：	
tinyint、smallint、mediumint、int/integer、bigint
1	  			2	    3			4		8
特点：
①若不设置无符号还是有符号，默认是有符号，添加unsigned关键字可以设置为无符号
②若插入的数值超过了整型的范围，会报out of range异常，并插入临界值
③若不设置长度，会有默认的长度
④长度代表了显示的最大宽度，若插入的宽度不足，则用0填充（搭配zerofill）
 如何设置无符号和有符号
DROP TABLE table_int;
CREATE TABLE table_int(
t1 INT(7) ZEROFILL,
t2 INT(7) ZEROFILL
);

小数：
	浮点型
		float(M,D)
		double(M,D)
	定点型
		dec(M,D)
		decimal(M,D)
特点：
①M和D
M：整数部位+小数部位
D：小数部位
若超过范围，则插入临界值
②
M和D都可以省略
若是decimal，则M默认为10，D默认为0
若是float和double，则会根据插入的数值的精度来决定精度
③定点型的精确度较高，如果要求插入的数值的精度要求较高如货币运算，可以使用

字符型：
	较短的文本：
	char
	varchar
	
	较长的文本：
	text
	blob(较大的二进制）
	特点：
	     写法		   M的意思				 				特点	  空间的耗费情况	  效率
char	 char(M) 	   最大的字符数，可以省略，默认为1	  固定长度的字符	  比较耗费		  高
varchar	 varchar(M)	   最大的字符数，不可以省略	 	  可变长度的字符	  比较节省		  低

	其它：
	binary和varbinary用于保存较短的二进制
	enum用于保存枚举
	set用于保存集合


日期型：
	date只保存日期
	time 只保存时间
	year只保存年
	
	datetime保存日期+时间
	timestamp保存日期+时间

Blob类型：     保存较大的二进制

常见约束 √

含义：

一种限制，用于限制表中的数据，为了保证表中的 数据的准确和可靠性

分类：六大约束

NOT NULL :非空，用于保证该字段的值不能为空
DEFAULT:默认，用于保证该字段有默认值
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
CHECK:检查约束【mysql 中不支持】
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的值
	    在从表中添加外键约束，用于引用主表中某列的值   

添加约束的时机：

1.创建表时
	#1.添加列级约束
		语法：直接在字段名和类型后面追加约束类型即可
	#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='男' OR gender='女'),#检查约束
					CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
				);
				SHOW INDEX FROM stuinfo;
	
2.修改表时
	①.添加列级约束
		alter table 表名 modify colmun 字段名 字段类型 新约束;
	
	②.添加表级约束
		alter table 表名 add 【constraint 约束名】约束类型(字段名);
		
	例如：
		#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 CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

约束的添加分类：

列级约束：
	六大约束语法上都支持，但是外键约束没有效果
	
表级约束：
	除了非空、默认、其它都支持、
	
create table 表名(
	字段名 字段类型 约束,
	字段名 字段类型,
	表级约束
)

主键和唯一的对比：

		保证唯一性	允许是否为空		一个表中可以有多少个		是否允许组合
主键	√				×			一个表中至多一个			√（不推荐）
唯一	√				√			多个					√（不推荐）

注意：唯一仅仅允许至多一个为空

外键：

1.要求在从表设置外键关系
2.从表的外键类型要与主表的关联列的类型要求一致或兼容，名称无要求
3.要求主表中的关联列必须是一个key（一般是主键或唯一）
4.插入数据时，要先插入主表，再插入从表
  删除数据时，先删除从表，再删除主表

修改表时删除约束

#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT;

#3.删除主键
ALTER TABLE stuinfo MODIFY COLUMN id INT;
ALTER TABLE stuinfo DROP INDEX `primary`;
#或
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键
ALTER TABLE stuinfo DROP INDEX fk_stuinfo_major;
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

注意：从表引用了主表的列时，更新时若涉及到引用的列，则要先更新主表
以下两种方式删除主表记录：
1.级联删除：通过在添加外键约束时在末尾添加 ON DELETE CASCADE 设置级联删除
2.级联置空：通过在添加外键约束时在末尾添加 ON DELETE SET NULL设置级联置空

ALTER TABLE emp2 DROP FOREIGN KEY fk_this_dept1_id;#先删除外键，再删除其索引
ALTER TABLE emp2 DROP INDEX fk_this_dept1_id;

#在emp2中引用dept1的id为外键约束,同时添加级联删除
ALTER TABLE emp2 ADD CONSTRAINT FOREIGN KEY(dept1_id) REFERENCES dept1(id) ON DELETE CASCADE;

DELETE FROM dept1 WHERE id=3;

#在emp2中引用dept1的id为外键约束,同时添加级联置空
ALTER TABLE emp2 ADD CONSTRAINT FOREIGN KEY(dept1_id) REFERENCES dept1(id) ON DELETE SET NULL;