SQL语言精要-优快云博客

本文链接：https://blog.youkuaiyun.com/tdly_develop/article/details/104929408

六.数据定义语言(DDL)

1.库的管理
创建、修改(现在基本不用了)、删除
2.表的管理
创建、修改、删除

和刚刚的数据操纵语言分开
创建：create
修改：alter
删除：drop

1.库的管理

(1)库的创建

Ⅰ.语法：
	create database [if not exists] 库名;
加上if not exists表示，如果库不存在则创建，库存在就不创建了

(2)库的修改

Ⅰ.更改库的字符集语法：
	alter database 库名 character set 字符集;
默认是utf8

(3)库的删除

Ⅰ.语法：
	drop database [if exists] 库名;
加上if exist表示如果存在就删除

2.表的管理

(1)表的创建(★)

Ⅰ.语法：
	create table [if not exists] 表名(
		字段名 字段类型 [约束],
		字段名 字段类型 [约束],
		...
		字段名 字段类型 [约束]
	)
Ⅱ.例子：
	#创建表author
	CREATE TABLE author(
		id INT,
		au_name VARCHAR(20),
		nation VARCHAR(10)
	)

(2)表的修改

Ⅰ.语法：
	alter table 表名
	add|drop|modify|change column 列名 [类型 约束];
Ⅱ.用途
	①修改列名
		alter table 表名
		change column 字段名 新字段名 新类型;#改名的同时可以修改类型
	②修改列的类型或约束
		alter table 表名
		modify column 字段名 新类型 [新约束];#这里可以修改类型和约束
		如果想删除列级约束可以modify column 字段名 类型;相当于约束都没了
	③添加列
		alter table 表名
		add column 字段名 类型 [列级约束] [first|after 字段名];#同时可以控制字段插入的位置
	④添加表级约束
		add 表级约束
	⑤删除列
		alter table 表名
		drop column 字段名;
	⑥修改表名
		alter table 表名
		rename to 新表名;

(3)表的删除

Ⅰ.语法：
	drop table [if exists] 表名;

(4)表的复制

跨库复制表，要在表名前加上库名.

Ⅰ.仅仅复制表的结构
	create table 新表 like 旧表;
Ⅱ.复制表的结构+数据
	create table 新表
	select *
	from 旧表;
	可以在select后面正常写查询语句，可以只复制部分行，也可以只复制部分列
Ⅲ.可以只复制部分结构
	create table 新表
	select 字段列表
	from 旧表
	where 0;
	这样where的条件恒不满足为false就不会有数据过去了

3.常见的数据类型

原则：所选择的类型越简单越好，占的空间越小越好

(1)数值型
	整数
	小数：
		定点数
		浮点数
(2)字符型
	较短的文本：char,varchar
	较长的文本：text,blob(较长的二进制数据，如图片等)
(3)日期型

(1)整型

Ⅰ.分类：
			tinyint,smallint,mediumint,int,biging
所占字节数      1，      2，      3，     4，   8
Ⅱ.特点：
	①默认是有符号数，可以通过追加unsigned变成无符号数，即写成int unsigned
	②如果插入插入的数值超出了整型范围，会报out of range异常，并且插入的是临界值
	③整型如果不设置长度，会有默认的长度
	④长度不是值数值的范围，数值的范围只由类型来决定，长度代表的是数值显示的宽度
	 如果位数不够会用0在左边填充，但必须搭配zerofill使用,且zerofill自动会将数值变为无符号数

(2)小数

Ⅰ.分类
	①浮点型
		float[(M,D)]，4个字节
		double[(M,D)]，8个字节
	②定点型
		dec[(M,D)]
		decimal[(M,D)]
Ⅱ.特点
	①M：整数部分+小数部分的数的个数
	 D：小数部分的数的个数
	 如果超过范围，则插入临界值
	②M和D都可以省略
	 浮点型的M和D默认为任意，根据你插入的值来定
	 定点型的M默认为10，D默认为0
	③定点型的精度较高，如果插入数值的精度要求较高，如货币运算就考虑用它

(3)字符型

Ⅰ.分类
	①较短的文本
		char(M)
		varchar(M)
	②较长的文本
		text
		blob(较大的二进制)
	③枚举类型
		Enum()，只能从括号中的集合中选择一个来填
		set()，能从括号中的集合中选择多个来填
		括号中放入可以从中选择的集合(不区分大小写)
Ⅱ.特点
	①        M的意思                            特点             空间的耗费       效率
	 char    最大的字符数(可以省略，默认为1)    固定长度的字符      比较耗费         高
	 varchar 最大的字符数(不可以省略)           可变长度的字符      比较节省         低
	 char即使你插入的数据没有达到最大字符数也会给你开这么多的空间，但是varchar不会
	 基本上是像性别这种我们用char，其他长度不确定的用varchar

(4)日期型

日期型表格

Ⅰ.分类
	date:只保存日期
	time：只保存时间
	year：只保存年
	datetime：保存日期+时间
	timestamp：保存日期+时间
Ⅱ.特点
		  字节     范围         时区等影响
datetime   8      1000-9999    不受
timestamp  4      1970-2038    受

4.常见约束

(1)含义
	约束是一种限制，用于限制表中的数据，保证了表中的数据的准确和可靠性
(2)语法
	create table [if not exists] 表名(
		字段名 字段类型 [列级约束1] [列级约束2]...,
		字段名 字段类型 [列级约束] [列级约束2]...,
		...
		字段名 字段类型 [列级约束] [列级约束2]...
		[表级约束]
	)
(3)分类
	Ⅰ.列级约束，写在字段名和字段类型后追加即可
		六大约束语法上都支持不报错，但外键约束没有效果
		列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序需求
	Ⅱ.表级约束，在各字段的最下面追加
		语法：[constraint 约束名] 约束类型(字段名);
		如果不起约束名，会有默认约束名;
		除了非空和默认，其他的都可以写在表级约束中，对于列的约束也可以写在这
		但习惯是对列的约束写在列级约束中，对表的约束如外键，写在表级约束中;
		表级约束支持起约束名，列级约束不支持;
	
(4)六大约束类型
	①not null:非空，用于保证该字段的值不能为空,比如姓名等
	
	②default 值:默认，设置该字段的默认值，比如性别等
	
	③primary key:主键，用于保证该字段的值具有唯一性，且非空，比如学号等
	
	④unique:唯一，用于保证该字段的值具有唯一性，但是可以为空
	
	⑤check:检查，[MySQL中不支持]
			例如：check(gender='男' or gender='女')
		
	⑥foreign key:外键，用于保证该字段的值必须来自主表的关联列的值
				在从表中添加外键约束，用于引用主表中某列的值
				语法:foreign key(字段名) references 主表(被引用列);

	⑦auto_increment:标识列,又称为自增长列,每插入一个数据就增长1
					不属于约束但语法和列级约束一样就放到这了，就看成是列级约束的一种吧
					
(5)primary key和unique的对比
	主键只能允许有一个NULL值，一个表中只能有一个主键
	唯一允许有多个NULL值，一个表中可以有多个唯一
	但同时主键和唯一都允许多个字段组合成主键或唯一,即可以在括号中写入多个字段名，用逗号隔开
	
(6)外键的特点
	Ⅰ.从表的外键列应该和主表的关联列的类型一致或兼容，名称无要求
	Ⅱ.主表的关联列必须是一个key(一般是主键)
	Ⅲ.插入数据时，先插入主表，再插入从表
	   删除数据时，先删除从表，再删除主表
	Ⅳ.级联删除和级联置空
		级联删除：在外键声明后追加on delete cascade;
			ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major
			FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
		级联置空：在外键声明后追加on delete set null;
			ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major
			FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

(7)标识列的特点
	Ⅰ.标识列不一定和主键搭配，但一定要和key搭配
	Ⅱ.一个表中只能有一个标识列
	Ⅲ.表示列的类型只能是数值型
	Ⅳ.可以通过手动插入第一个值来设置起始值，标识列会从第一个值开始往后增
	
	
(8)修改表时添加/删除约束
	Ⅰ.添加列级约束
		alter table 表名 modify column 字段名 字段类型 新约束;
	Ⅱ.添加表级约束
		alter table 表名 add [constraint 约束名] 约束类型(字段名);
	Ⅲ.删除表级约束
		alter table 表名 drop 约束类型(字段名);

七.事务控制语言(TCL)

1.概念
	事务：一个或一组sql语句组成的执行单元，这个执行单元要么全部执行，要么全部不执行
	 	例如转账时的数据修改，如果中途发生异常则回滚。
	 	回滚：所有受到影响的数据将返回到事务开始以前的状态
	MySQL5.5版本后的存储引擎支持事务，之前的不支持
2.事务的属性(ACID)
	(1)A 原子性(undo log)
		原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
		①undo log日志中存储了sql执行的相关信息
		②一旦事务报错就会根据undo log进行相反的操作回滚
	(2)C 一致性
		事务必须使数据库从一个一致性状态变换到另一个一致性状态
	(3)I 隔离性(加锁+MVCC)
		事务的隔离是指事务之间互不干扰
	(4)D 持久性(redo log)
		持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，不可撤销
		背景：
		①MySQL中的数据是存储在磁盘中的，但是因为磁盘IO很慢，所以innodb提供了一个缓存Buffer
			Ⅰ.数据库读取数据会先从buffer中读取，缓存中没有才去磁盘中读取，并写入buffer
			Ⅱ.数据的写入也是先写到buffer中，然后定期将buffer中的数据刷到磁盘上
			Ⅲ.这样效率变高，但增加了数据丢失的风险
		②redo log：
			Ⅰ.进行更新时先将更改记录到redo log中，再写入buffer中
			Ⅱ.这样就保证即使宕机了数据也能恢复

1.事务的创建

(1)分类
	Ⅰ.隐式事务
		这种事务没有明显的开启和结束的标记
		比如：insert,update,delete等
	Ⅱ.显示事务
		这种事务具有明显的开启和结束的标记
		前提：在合并多个隐式事务成为一个显示事务之前我们需要将自动提交功能设为禁用
			set autocommit=0;
		每次定义显式事务都需要设置一次
(2)定义步骤的语句
	步骤一：开启事务的标记
		set autocommit=0;#这里就默认事务开启了
		start transaction;#这句话可省
	步骤二：编写事务中的sql语句(一般指insert,update,delete，不包括DDL)
		语句1;
		语句2;
		...
	步骤三：结束事务
		commit;#提交事务
		rollback;#回滚事务
	单纯的MySQL做不到出现异常就回滚，没有异常就提交，只能提交回滚二选一，之后和其他语言合在一起了才能实现
(3)例子
	#开启事务
	SET autocommit=0;
	START TRANSACTION;
	#编写一组事务的语句
	UPDATE account SET balance=500
	WHERE username='张无忌';
	UPDATE account SET balance=1500
	WHERE username='赵敏';
	#结束事务
	COMMIT;

2.事务的并发问题

并发问题介绍

(1)事务的隔离级别
							脏读		不可重复读		幻读
read uncommitted(读未提交)	√				√			√
read committed(读已提交)		×				√			√
repeatable read(可重复读)	×				×			√
serializable(串行化)			×				×			×
MySQL中默认第三个隔离级别
Oracle中只支持第二和第四级， 默认第二个隔离级别
最高的隔离级别相当于像java中一样加了一个锁，只有一个事务能访问其他事务等待，但这种隔离级别效率较低，一般我们都用默认的隔离级别

(2)常用的隔离级别的命令
	Ⅰ.查看当前隔离级别
		select @@tx_isolation
	Ⅱ.设置当前MySQL连接的隔离级别
		set session transaction isolation level 隔离级别;
	Ⅲ.设置数据库系统的全局隔离级别
		set global transaction isolation level 隔离级别;

3.事务隔离级别的实现(MVCC)

undo log日志：
①如果数据库中的数据被更新，那么被更新的老数据就会被放到undo log日志中
②undo log日志中除了存储原本的数据库数据之外，还存储了更改了该条数据的事务的ID和回滚指针。
③回滚指针指向了该条数据的上一个版本存储的位置，从而形成了一个版本链
MVCC：指多版本并发控制

ReadView对象(用于实现我们的MVCC)

属性                       意义
m_ids                 表示在生成ReadView时当前系统中活跃的事务ID列表(即未提交的事务ID列表)
min_trx_id            表示m_ids中最小的事务ID    
max_trx_id            表示生成ReadView对象时，应该分配给下一个事务的ID
creator_trx_id        生成这个ReadView的事务ID

ReadView控制访问版本链
如果要访问事务ID为trx_id的数据：

情况	说明
trx_id==creator_trx_id	说明这个记录是我创建的，我当然能访问
trx_id<min_trx_id	要访问的数据肯定不在活跃事务列表中。是已提交的事务，当然能访问
trx_id>max_trx_id	要访问的数据已经超出了我版本链中事务的最大ID，访问不到
min_trx_id<=trx_id<=max_trx_id	如果trx_id在m_ids中则说明它未提交则不能访问，反之可以

MVCC实现RC(读已提交)和RR(可重复读)
①这两个其实都是通过比较当前要访问数据的trx_id和ReadView中的属性来实现的。
②但是RC和RR生成ReadView的时机不同
读已提交：每次select生成一个ReadView，即使是在一个事务中的两个select语句生成的ReadView都是不同的
可重复读：每次事务开启生成一个ReadView，一个事务中的多次select出来的数据都是一样的
解决幻读
①MySQL的innodb虽然使用的是第三个隔离级别但是它进行了优化能解决幻读
②快照读通过MVCC能够解决幻读，每次事务开启都是一个ReadView就不会出现幻读的问题
③当前读通过间隙锁解决幻读，即向某段范围内的数据加锁，不给插入和删除

4.事务的回滚点

(1)delete和truncate在事务使用时的区别
	#演示delete
	SET autocommit=0;
	START TRANSACTION;
	DELETE TABLE account;
	ROLLBACK;
	#演示truncate
	SET autocommit=0;
	START TRANSACTION;
	TRUNCATE TABLE account;
	ROLLBACK;
delete支持回滚，truncate不支持回滚
(2)回滚点的设置
	savepoint 节点名;
(3)例子
	SET autocommit=0;
	START TRANSACTION;
	DELETE TABLE account
	WHERE id=25;
	SAVEPOINT a;
	DELETE TABLE account
	WHERE id=28;
	ROLLBACK TO a;#回滚到之前设置的回滚点

八.变量

分类：
	系统变量：
			全局变量
			会话变量
	自定义变量：
			用户变量
			局部变量

1.系统变量

(1)说明：
	系统变量由系统提供，不用用户定义，属于服务器层面，如隔离级别，是否自动提交等
(2)分类：
	Ⅰ.全局变量：
		服务器每次启动都会为所有的全局变量赋初始值，针对所有会话(连接)都有效，但重启就恢复初始值
	Ⅱ.会话变量
		仅针对当前会话(连接)有效
(3)使用
	Ⅰ.查看所有的系统变量
		show global/[session] variables;#不写默认查看会话变量
	Ⅱ.查看满足条件的部分系统变量
		show global/[session] variables like '';
	Ⅲ.查看指定的某个系统变量的值
		select @@global/[session].系统变量名;
	Ⅳ.为某个系统变量赋值
	方式一：
		set global/[session] 系统变量名=值;
	方式二：
		set @@global/[session].系统变量名=值;
总：如果是全局变量需要加global，如果是会话变量，则需要加session，如果不写，默认会话变量

2.自定义变量

(1)说明：
	由用户自定义的变量，分为用户变量和局部变量两种
(2)使用
	Ⅰ.声明
	Ⅱ.赋值
	Ⅲ.查看，比较，运算等
(3)用户变量
	Ⅰ.作用域：针对当前会话(连接)有效，同于会话变量的作用域
			  可以在任意地方使用，不局限于begin end中
	Ⅱ.使用
		①声明并初始化：
			set @用户变量名:=值;
		②赋值：类似于js弱类型，会根据值设置变量类型
			方式一：
				set @用户变量名:=值;
				和上面那个一样的
			方式二：通过select into
				select 字段 into 变量名
				from 表;
				这个字段必须只有一个值，例如个数count()等
		③查看
			select @用户变量名;
(4)局部变量
	Ⅰ.作用域：仅在定义它的begin end中有效，
			  并且必须是begin end中的第一句话
	Ⅱ.使用
		①声明：可以只声明不初始化
			declare 变量名 类型 [default 值];
			既然指定了类型，值就不能随便写了
		②赋值：和用户变量一样，去掉@就行
			方式一：
					set 局部变量名:=值;
				方式二：通过select into
					select 字段 into 局部变量名
					from 表;
		③查看
			select 局部变量名;

九.存储过程

存储过程和函数都类似于java中的方法
注意在我们的SQLyog中结束标记的定义必须和存储过程和函数的声明一起执行，不然会报错，而且我们的delimiter结束标记的声明每次存储过程或者函数定义时都要执行一次
即声明一次只有一个存储过程或函数的使用和声明能用$，其他语句还要用分号;。

1.存储过程

(1)说明：
	存储过程是一组预先编译好的SQL语句集合，可以理解成批处理语句
(2)好处：
	提高了代码的重用性
	简化操作
	减少了编译次数，减少了和数据库连接的次数，提高了效率
(3)语法
	Ⅰ.创建
		delimiter 结束标记
		create procedure 存储过程名(参数列表)
		begin
			存储过程体(一组合法的SQL语句)
		end 结束标记
	Ⅱ.调用
		call 存储过程名(实参列表)结束标记
	Ⅲ.删除
		drop procedure 存储过程名;#存储过程一次只能删除一个
	Ⅳ.查看
		show create procedure 存储过程名;
(4)注意
	Ⅰ.参数列表中的参数由三部分组成
		参数模式 参数名 参数类型
		例如：
			IN stuname VARCHAR(20);
		三种参数模式：
			in:表示该参数作为输入，即该参数需要调入放传入值
			out:表示该参数作为输出，即该参数作为返回值
			inout:表示该参数既是输入也是输出，既需要传入值，又可以返回值
			
	Ⅱ.如果存储过程体只有一句话，那么begin end可以省略
	   存储过程体中的每条SQL语句结尾加分号
	   存储过程的结尾不使用;，而是用
	   		delimiter 结束标记 来设置(这里不能加;)
	   		例如：DELIMITER $

案例

(1)空参列表
	#向admin表中插入2条记录
	DELIMITER $
	CREATE PROCEDURE myq1()
	BEGIN
		INSERT INTO admin(username,password)
		VALUE ('john','1321'),('lily','1353');
	end $
	#调用
	CALL myq1() $
	
(2)创建带in模式参数的存储过程
	#创建存储过程，根据女神名，查询对应的男朋友信息
	DELIMITER $
	CREATE PROCEDURE myq2(IN beautyName VARCHAR(20))
	BEGIN
		SELECT bo.*
		FROM boys AS bo
		RIGHT JOIN beauty AS b ON bo.id=b.boyfriend_id
		WHERE b.name=beautyName
	end $
	#调用
	CALL myq2('柳岩') $
	
(3)创建带out模式参数的存储过程
	#根据女生名，返回对应的男朋友名字和魅力值
	DELIMITER $
	CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userSP INT)
	BEGIN
		SELECT bo.boyName,bo.userSP INTO boyName,userCP
		FROM boys AS bo
		INNER JOIN beauty b ON bo.id=b.boyfriend_id
		WHERE b.name=beautyName;
	END $
	#调用
	SET @bName$
	SET @userSP$
	CALL myp6('夏种',@bName,@userSP)$
	SELECT @bName,@userSP$

(4)创建带inout模式参数的存储过程
	#传入a和b两个值，将a和b翻倍并返回
	CREATE PROCEDURE myq8(INOUT a INT,INOUT b INT)
	BEGIN
		SET a=a*2;
		SET b=b*2;
	END $
	#调用
	SET @m=20$
	SET @n=20$
	CALL myq8(@n,@m)$
	SELECT @n,@m$