MySQL进阶总结

MySQL进阶总结

数据类型

• 整型
  种类：tinyint、smallint、mediumint、int/integer、bigint
  长度：1 2 3 4 8

如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用

• 浮点型
  定点数：dec(M,D)；decimal(M,D)
  浮点数：float(M,D) 4；double(M,D) 8

M代表整数部位+小数部位的个数，D代表小数部位
如果超出范围，则报out or range异常，并且插入临界值
M和D都可以省略，但对于定点数，M默认为10，D默认为0
如果精度要求较高，则优先考虑使用定点数

• 字符型
  char、varchar
  binary和varbinary用于保存较短的二进制.
  enum用于保存枚举
  set用于保存集合
  较长的文本：text、blob(较大的二进制)

char和varchar的对比
				M代表最大字符数			区别
char(M)				可以省略				固定长度字符，空间耗费高，效率也高
varchar(M)			不可以省略			可变字符长度，节省空间耗费，效率低

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

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

常见约束

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

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

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

• 主键和唯一的区别

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

事务控制语言

• 事务的特性(ACID)

原子性：一个事务不可再分割，要么都执行要么都不执行
一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据.

• 分类

隐式（自动）事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete

显式事务：事务具有明显的开启和结束的标记；前提：必须先设置自动提交功能为禁用

• 具体步骤

步骤1：开启事务
set autocommit=0;
start transaction;可选的

步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...

步骤3：结束事务
commit;提交事务
rollback;回滚事务

• 并发事务
  多个事务同时操作 同一个数据库的相同数据时发生并发

出现的情况：

1.脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据；
2.不可重复读：一个事务多次读取，结果不一样；
3.幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据。

• 通过设置隔离级别来解决并发问题

							脏读			不可重复读		幻读
read uncommitted:读未提交     ×                ×              ×        
read committed：读已提交      √                ×              ×
repeatable read：可重复读     √                √              ×
serializable：串行化          √                √              √

查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;

视图

虚拟表,和普通表一样使用。mysql5.1版本出现的新特性，是通过表动态生成的数据。
使用场景：多个地方用到同样的查询结果，该查询结果使用的sql语句较复杂

• 视图的创建
  create view 视图名 as 查询语句;
• 视图的好处
  重用sql语句
  简化复杂的sql操作，不必知道它的查询细节
  保护数据，提高安全性
• 修改视图
  方式一：
  create or replace view 视图名 as 查询语句;
  方式二：
  alter view 视图名 as 查询语句;
• 视图的删除
  drop view 视图1，视图2,…;
• 视图和表的对比

		关键字		  是否占用物理空间			使用
视图	view		占用较小，只保存sql逻辑	一般用于查询
表	    table		保存实际的数据			  增删改查

存储过程函数

变量

变量由系统定义，不是用户定义，属于服务器层面
全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别

• 查看系统变量
  show global |【session】variables;
  show global |【session】 variables like ‘%char%’;
• 为某个系统变量赋值
  方式一：
  set global|【session】系统变量名=值;
  方式二：
  set @@global.|【session】系统变量名=值;

可以自定义用户变量和局部变量

• 用户变量和局部变量的对比

			作用域					定义位置					语法
用户变量		当前会话					会话的任何地方			加@符号，不用指定类型
局部变量		定义它的BEGIN END中 		BEGIN END的第一句话		一般不用加@,需要指定类型

存储过程

一组预先编译好的SQL语句的集合，理解成批处理语句
提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

• 创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体（一组合法的SQL语句）
END

参数列表包含三部分：参数模式  参数名  参数类型
参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $

• 调用语法

CALL 存储过程名(实参列表);

• 查看语法

show create procedure 存储过程名;

• 删除语法

drop procedure 存储过程名;

函数

一组预先编译好的SQL语句的集合，理解成批处理语句
与存储过程的区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新；
函数：有且仅有1 个返回，适合做处理数据后返回一个结果。

• 创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END


函数体：肯定会有return语句，如果没有会报错

• 调用语法

SELECT 函数名(参数列表);

• 查看函数

show create function 函数名;

• 删除函数

drop function 函数名;