day2 MySql数据库基础其DML语言及DDL语言

数据操作语言(DML)

插入：insert
修改：update
删除:delete

一、插入语句

语法：
方式一：insert into 表名(列名...) values(值...);
方式二：insert into 表名 set 列名=值, 列名=值, ...
两个方式：方式一支持插入多行，方式二不支持
				  方式一支持子查询，方式二不支持 

1.插入的值的类型要与列的类型一致或兼容
2.可以为null的字段可以不插入值，会直接插入默认值或者是null
3.列的顺序可以调换，值也必须跟着调换，一 一对应。
4.列数和值的个数必须一致
5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致

二、修改语句

语法：
	1.修改单表的记录
		update 表名 
		set 列=新值，列=新值，...	
		where 筛选条件；
	2.修改多表的记录
		sql92语法（仅支持内连接）
			update 表1 别名，表2 别名
			set 列=值，...
			where 连接条件
			and 筛选条件；
		sql99语法
			update 表1 别名
			inner | left | right | join 表2 别名
			on 连接条件
			where 筛选条件
案例：#1.修改张无忌的女朋友的手机号为114
		UPDATE boys bo
		INNER JOIN beauty b ON bo.id = b.boyfriend_id
		SET b.phone = '114'
		WHERE bo.boyName = '张无忌'
	#2.修改没有男朋友的女神的男朋友编号都为2号
		UPDATE boys bo
		RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
		SET b.boyfriend_id = 2
		WHERE bo.id is NULL

三、删除语句

语法：
	方式一：delete
		1.单表删除
			delete from 表名 where 筛选条件
		2.多表删除【了解】
		sql92语法（仅支持内连接）
			delete 表1的别名或者是表2的别名（要删哪个表就写哪个表的别名;级联删的话两个表都写上）
			from 表1 别名，表2 别名
			where 连接条件
			and 筛选条件；
		sql99语法(级联删除)
			delete 表1的别名或者是表2的别名（要删哪个表就写哪个表的别名;级联删的话两个表都写上）
			from 表1 别名
			inner | left | right | join 表2 别名
			on 连接条件
			where 筛选条件
			
	方式二：truncate（删除表中全部数据）
		truncate table 表名

	两者区别：(delete和truncate)
		1.delete可以家where条件，truncate不能加
		2.效率上truncate>delete
		3.假如要删除的表中有自增长列，如果用delete删除数据后，
		  再插入数据，自增长的值从断点开始；
		  而truncate删除的话，再插入数据，自增长的值从1开始
		4.truncate删除没有返回值，delete删除有返回值
		5.truncate删除不能回滚事务，delete删除可以回滚

数据定义语言(DDL)

库与表的管理：
	库的管理：创建(create), 修改(alter), 删除(drop)
	表的管理：创建(create)，修改(alter)，删除(drop)

一、库的管理
创建数据库：
	create database 【IF NOT EXISTS】 库名；
库的修改：
	更改库的字符集：ALTER databses 库名 CHARACTER SET 字符集格式
库的删除：
	drop database 【IF  EXISTS】 库名

二、表的管理
	1.表的创建
		create table 表名(
			列名 列的类型 【(长度) 约束】，
			列名 列的类型 【(长度) 约束】，
			列名 列的类型 【(长度) 约束】，
			...
			列名 列的类型 【(长度) 约束】
		）
	2.表的修改
	语法：
	alter table 表名 add | drop | modify | change column 列名 【列类型 约束】
		①修改列名
			alter table 表名 change 【column】 旧列名 新列名 类型；
		②修改列的类型或约束
			alter table 表名 modify column 旧列名 新列名 类型(长度) 约束条件；
		③添加列
			alter table 表名 add column 新列名 类型(长度) 约束条件
		④删除列
			alter table 表名 drop column 列名
		⑤修改表名
			alter table 表名 rename to 新表名
	3.表的删除（删除表结构和数据）
		drop table 【IF EXISTS】 表名；
	4.表的复制
		仅仅复制表的结构：
			create table 新表名 like 表名（要复制的表）
		仅仅复制表的某些字段
			create table 新表名 
				select 字段1，字段2...
				from 表名 （要复制的）
				where 0；（0代表false，非0为true）
	 	复制表的结构+数据
	 		create table 新表名
	 			select * from 表名（要复制的）where 筛选条件

常见的数据类型

数值型：
	整型：															范围
		Tinyint				1字节		有符号：-128-127 		   无符号：0-255
		Smallint			2字节		有符号：-32768--32767 无符号：0-65535
		Mediumint			3字节
		Int | integer		4字节
		Bigint				8字节
	如果不设置是否为无符号整型，默认为有符号整型；要设置为无符号整型，加关键字unsigned即可
	如果插入的数值超过了整型类型的范围，则会报out of range异常 插入的数据为临界值
	如果整型数据不设置长度会有默认的长度；
	而如果设置长度，比如说int(4) 这个4决定不了整型的大小范围，
		决定的是显示的数字长度，加上zerofilll 整型数字默认变为无符号整型数字，
		并且如果显示长度不够的数字，左边会用0填充
		
	小数：
		定点数：DEC(M,D) | DECIMAL(M,D)  	M+2字节    最大范围与double相同，给定的decimal的有效值范围由M和D组成
		浮点数：
			float(M,D)		4字节
			double(M,D)		8字节
	注意：
		①M是整数部位+小数部位
			D代表小数部位
			如果超过范围，则插入临界值
		②M和D可以省略，如果是decimal，则M默认为10，D默认为0
			如果是float和double，则会根据插入的数值的精度来决定精度
		③定点型的精度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
		所选择的类型越简单越好，字节大小越小越好，节省内存
		 
字符型：
	较短文本：
		char(M)				最多字符数M(0-255之间的整数)，省略M，默认为1
		varchar(M)			最多字符数M(0-65535之间的整数) 不能省略M
	 区别：char和varchar
		①char是固定长度的字符，创建字段是字符的长度是固定的，比较耗费空间，效率较高
		 varchar是可变长度的字符，创建字段是根据数据字符的长度来变换字符长度，效率较低，节省空间
	
	较长文本：
		text
		blob(较长的二进制数据)

位类型：binary，varbinary 都是类似于char和varchar，
		 不同的是他们包含二进制字符串而不包含非二进制字符串

Enum类型：要求插入的值必须属于列表指定的值之一
	如果列表成员是1-255，则需要1个字节存储
	如果列表成员为255-65535，则需要2个字节存储
	最多需要65535个成员
	
Set类型：和Enum类型类似，里面可以保存0-64个成员。
	Set类型一次可以选取多个成员，而Enum只能选取一个。
	根据成员个数不同，存储所占的字节也不同
	1-8个成员		1字节
	9-16个成员		2字节
	17-24个成员 		3字节
	25-32个成员  	4字节
	33-64个成员		8字节
	
日期型
	date					4字节					只保存日期
	datetime				8字节			保存日期+时间 范围1000-01-01 00:00:00~9999-12-31
	timestamp				4字节			保存日期+时间 范围1970010108001--2038年的某个时间
	time					3字节					只保存时间
	year					1字节					只保存年

timestamp和Datetime的区别：
	①Timestamp支持的时间范围较小，取值范围1970010108001--2038年的某个时间
	 Datetime的取值范围：1000-01-01~9999-12-31
	②timestamp和实际时区有关，更能反映实际的日期，而datetime则只能反映插入时的当地时区
	③timestamp的属性受Mysql版本和SQLMode的影响很大

常见约束条件

含义：一种限制，用于限制表中的数据，为了保证表中的数据的一致性
分类：（六大约束）
	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 UNIQUE,#非空
			gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
			seat INT UNIQUE,#唯一
			age INT DEFAULT  18,#默认约束
			majorId INT REFERENCES major(id)#外键
		  );
		  
	#查看表中的所有索引，包括主键、外键、唯一
	SHOW INDEX FROM 表名;

	2.添加表级约束
		在各个字段的最下面
		【constraint 约束名】 约束类型(字段名)
		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)#外键
		);

#通用的写法：★
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

二、修改表时添加约束
1、添加列级约束
	alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
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); 


三、修改表时删除约束
#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  ,
		NAME FLOAT UNIQUE AUTO_INCREMENT,
		seat INT 
	);
SHOW VARIABLES LIKE '%auto_increment%';
设置步长：SET auto_increment_increment=3;