MySQL基础

《海阔天空》是中国香港摇滚乐队Beyond演唱的一首粤语歌曲，由黄家驹作词、作曲，Beyond、梁邦彦共同编曲，收录在Beyond于1993年5月发行的粤语专辑《乐与怒》中。

一、触发器

      触发器(trigger)是数据库服务器提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作(insert/delete/update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器可以从DBA_TRIGGERS，USER_TRIGGERS数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。

二、存储过程

1. 定义
         MySQL从5.0版本开始支持存储过程(Stored Procedure)，是一种在数据库中存储复杂程序以便外部程序调用的一种数据库对象。它是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

2. 语法

CREATE PROCEDURE <过程名> ([过程参数[,…]]) <过程体>

• 2.1 过程名：存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。
• 2.2 过程参数：存储过程的参数列表，结构为[ IN | OUT | INOUT ] <参数名> <类型>。其中，<参数名>为参数名，<类型>可以是任何有效的MySQL数据类型。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数(此时存储过程的名称后仍需加上一对括号)，也可以有 1 个或多个参数。
  • MySQL存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。需要注意的是，参数的取名不要与数据表的列名相同，否则尽管不会返回出错信息，但是存储过程的 SQL 语句会将参数名看作列名，从而引发不可预知的结果。
• 2.3 过程体：存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的SQL语句。这个部分以关键字BEGIN开始，以关键字END结束。若存储过程体中只有一条SQL语句，则可以省略BEGIN/END标志。
• 2.4 DELIMITER：在 MySQL 中，服务器处理SQL语句默认是以分号作为语句结束标志的。然而在创建存储过程时，存储过程体可能包含有多条SQL语句，这些SQL语句如果仍以分号作为语句结束符，那么MySQL服务器在处理时会以遇到的第一条SQL语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的SQL语句，这样显然不行。为解决这个问题，通常可使用 DELIMITER 命令将结束命令修改为其他字符。
  • 语法格式如下：DELIMITER 结束符
  • 结束符可以是一些特殊的符号，如两个“?”或两个“￥”等。
  • 当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符。
• 2.5 查看存储过程 SHOW PROCEDURE STATUS LIKE '存储过程名称'
• 2.6 删除存储过程 DROP PROCEDURE 存储过程名称

3. 扩展

   • 定义变量
     • DECLARE name,address VARCHAR;
     • DECLARE age INT DEFAULT 20; -- 指定默认值，若没有DEFAULT子句，初始值为NULL
   • 为变量赋值SET name = 'zhangsan';
   • 变量自增SET i=i+1;

4. 实战

   • 定义无参数存储过程

delimiter //
create procedure pro1()
begin
	select * from user_info;
end //
delimiter ;

call pro1();

• 定义有参数存储过程

delimiter //
create procedure pro2(IN id INT, OUT result varchar)
begin
	declare tmp;
	select field into tmp from user_info;
	set result = tmp;
end //
delimiter ;

call pro2();

三、外键

四、视图

1. 视图(view)是一种虚拟存在的表，是一个逻辑表，本身并不包含数据，作为一个select语句保存在数据字典中的。

   • 通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。
   • 基表：用来创建视图的表叫做基表base table

2. 语法

   • 创建视图

   CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
   VIEW view_name [(column_list)]
   AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]

   • 删除视图 drop view view_name

3. 作用

   • 提高了重用性，就像一个函数。

     • 如果要频繁获取user的name和goods的name，sql如下：select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;
     • 但有了视图就不一样了，创建视图other，示例create view other as select a.name as username, b.name as goodsname from user as a, goods as b, ug as c where a.id=c.userid and c.goodsid=b.id;
     • 创建好视图后，就可以这样获取user的name和goods的name，示例：select * from other;

   • 对数据库重构，却不影响程序的运行。

     • 假如因为某种需求，需要将user拆房表usera和表userb，该两张表的结构如下：测试表:usera有id，name，age字段和测试表:userb有id，name，sex字段
     • 这时如果php端使用sql语句：select * from user;那就会提示该表不存在，解决方案：创建视图。create view user as select a.name,a.age,b.sex from usera as a, userb as b where a.name=b.name;
     • 以上假设name都是唯一的，此时php端使用sql语句：select * from user;就不会报错什么的。这就实现了更改数据库结构，不更改脚本程序的功能了。

   • 提高了安全性能，可以对不同的用户设定不同的视图。

     • 例如：某用户只能获取user表的name和age数据，不能获取sex数据，则可以这样创建视图示例如下：create view other as select a.name, a.age from user as a;
     • 这样的话，使用sql语句：select * from other; 最多就只能获取name和age的数据，其他的数据就获取不了了。

   • 让数据更加清晰，想要什么样的数据，就创建什么样的视图，其实是以上三个作用的综合应用

4. 扩展：MySQL的两个视图
   • 一个是 view，它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。
   • 另一个是InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC和RR隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

五、数据类型(MySQL 5.7)

1. 数字类型

类型	大小	取值范围(无符号)
Bit(M)	M位	/
TINYINT	1字节	2^8
SMALLINT	2字节	2^16
MEDIUMINT	3字节	2^24
INT	4字节	2^32
BIGINT	8字节	2^64
DECIMAL(M,D)	max(M,D)+2字节	/
NUMERIC	同DECIMAL	/
FLOAT	4字节	/
DOUBLE	8字节	/

2. 日期时间

类型	大小(字节)	范围
DATE	3	1000-01-01 —— 9999-12-31
DATETIME	8	1000-01-01 00:00:00 —— 9999-12-31 23:59:59
TIME	3	-838:59:59 —— 838:59:59
YEAR	1	/
TIMESTAMP	4	1970-01-01 00:00:01 UTC —— 2038-01-19 03:14:07 UTC

• 类型支持：year与year(4)
• 插入值支持整数和字符串，支持2位数或者4位数
  • 01(数字)/00(字符串)69 将转换为20002069之间
  • 7099 将转换为19701999之间
• 插入数字n个0，实际显示0000，插入字符串n个0，实际显示2000

3. 字符串型

类型	大小
CHAR	暂无
VARCHAR	暂无
BINARY	暂无
VARBINARY	暂无
TINYBLOB	暂无
MEDIUMBLOB	暂无
BLOB	暂无
LONGBLOB	暂无
TINYTEXT	暂无
MEDIUMTEXT	暂无
TEXT	暂无
LONGTEXT	暂无
ENUM	暂无
SET	暂无

4. JSON

类型	说明
Json	暂无

5. Spatial

类型	说明
GEOMETRY	暂无
POINT	暂无
LINESTRING	暂无
POLYGON	暂无
MULTIPOINT	暂无
MULTILINESTRING	暂无
MULTIPOLYGON	暂无
GEOMETRYCOLLECTION	暂无

6. 一些对比

   • char-varchar-text

     • 基础
       • char是定长格式，长度范围是0~255字符，如果超过则报1074 - Column length too big for column 'school' (max = 255); use BLOB or TEXT instead
         • 当储存一个长度不足255的字符时，MySQL会用空格来填充剩下的字符，如定义char(20)，存储一个占2字符的字符串,会在字符串右边填充空格以达到指定的长度，占的字符长度仍为20字符
         • 读取数据时char类型的数据会把后面的空格去除
       • varchar类型在mysql5.0.3以下的版本中的最大长度限制为255，而在mysql5.0.3及以上的版本中varchar数据类型的长度支持到了65535(2^16 - 1)字节，也就是说可以存放65532个字节的数据（起始位和结束位占去了3个字节），如果超过则报1074 - Column length too big for column 'address' (max = 21845); use BLOB or TEXT instead
         • UTF8：一个汉字 = 3个字节，英文是一个字节，最多存 65532/3 个字符
         • GBK： 一个汉字 = 2个字节，英文是一个字节，最多存 65532/2 个字符
         • varchar类型的实际长度是它的值的实际长度+1，这个“1”代表1字节用于保存实际使用了多大的长度
         • 对于varchar字段来说，需要使用一个(如果字符串长度小于255)或两个字节(长度大于255)来存储字符串的长度
         • 在mysql5.0.3以下版本中需要使用固定的TEXT或BLOB格式存放的数据可以在高版本中使用可变长的varchar来存放，这样就能有效的减少数据库文件的大小
       • text与char和varchar不同的是，text不可以有默认值，其最大长度是2的16次方-1
         • 总结
           • 长度变化的字段用varchar
           • 长度固定的用char
           • 存储很短的信息如门牌号码101，102…这样很短的信息应该用char，因为varchar还要占1个byte用于存储信息长度
           • 字段值经常改变的用char，因为varchar每次存储都要有额外的计算，得到长度等工作，char不需要
           • 尽量用varchar
           • 超过255字符的只能用varchar或者text
           • 能用varchar的地方不用text
           • mysql5.7版本下，超过char或varchar设定的长度时都会报错
           • 当定义char时，不管存入多少字符都会占用定义的字符数(不可超过定义的长度)
           • 当定义varchar时，则和输入的字符数有关，会多一到两个字节来记录字节长度
             • 当数据位占用的字节数小于255时，用1个字节来记录长度
             • 数据位占用字节数大于255时，用2个字节来记录长度，还有一位来记录是否为null值
           • char类型的效率比varchar的效率稍高，一般用于固定长度的表单提交数据存储，如：身份证号，手机号，电话，密码等
             • varchar是将实际内容单独存储在聚簇索引之外
           • char 和 varchar 后面的长度表示的是字符的个数，而不是字节数。

   • float-double-decimal

     • 对比

       • float和double都是浮点型，而decimal是定点型
       • float表示单精度浮点数值，double表示双精度浮点数值
       • float占四个字节，double占八个字节
       • decimal、float、double都以二进制形式保存
       • MySQL浮点型和定点型可以用类型名称后加(M,D)来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度。如float(7,4)的 可显示为-999.9999，MySQL保存值时进行四舍五入，如果插入999.00009，则结果为999.0001。
       • float和double在不指定精度时默认会按照实际的精度来显示，而decimal在不指定精度时默认整数为10，小数为0。

     • 实现

       • 浮点型存储形式

       类型	符号位	阶码	尾数	长度
       float	1	8	23	32
       double	1	11	52	64

       • 定点型decimal存储

         • MySQL使用二进制格式分别为整数和小数部分分配存储空间

         • 它将9位数字包装成4个字节

         • 对于整数和小数的每个部分，需要4个字节来存储9位数的每个倍数，剩余数字所需的存储如下表所示：

           剩余数字	位
           0	0
           1–2	1
           3–4	2
           5–6	3
           7-9	4

           • eg：DECIMAL(19,9)，对于小数部分具有9位数字需要4个字节；对于整数部分具有10位数字，前9位数字需要4个字节，1个剩余字节需要1个字节，故DECIMAL(19,9)列总共需要9个字节。

   • 原理(二进制怎么表示小数的)

     • 引入：十进制中的小数表示

       • 如125.456可以分解成 1*10^2 + 2*10^1 + 5*10^0 + 4*10^-1 + 5*10^-2 + 6*10^-3
       • 指数表示为1.25456E2，相当于(1*2^0 + 2*10^-1 + 5*10^-2 + 4*10^-3 + 5*10^-4 + 6*10^-5)*10^2

     • 二进制中的小数表示

     二进制	计算方式	十进制
     0.1	1*2^-1	0.5
     0.01	1*2^-2	0.25
     0.001	1*2^-3	0.125
     0.0001	1*2^-4	0.0625
     0.00001	1*2^-5	0.03125
     …	…	…

     • 根据上述规则，二进制数 10001.101 则分解成 1*2^4 + 1*2^0 + 1*2^-1 + 1*2^-3

     • 指数表示为1.0001101*2^4，相当于(1 + 1*2^-4 + 1*2^-5 + 1*2^-7)*2^4

     • 十进制转换二进制

       • 8.5 = 8 + 0.5 = 1*2^4 + 1*2^-1
       • 5.4 ≈ 4 + 1 + 0.25 + 0.125 + 0.015625 = 1*2^2 + 1*2^0 + 1*2^-2 + 1*2^-3 + 1*2^-6
       • 浮点数整数部分和小数部分整体存储，这也就解释了浮点数为什么不能精确的存储小数，定点数则不一样，整数部分和小数部分分开存储的

   • int(1)和int(11)

   • 介绍

     • MySQL字段定义中INT(x)中的x仅仅指的是显示宽度。
       • 显示宽度小于指定的列宽度的值时从左侧用数值0填满宽度(指定ZEROFILL时)
         • 如果设置了ZEROFILL扩展属性，默认就有了无符号属性UNSIGNED
     • 显示宽度并不限制可以在列内保存的值的范围
     • 显示宽度不限制超过列的指定宽度的值的显示

所以INT(x)的定义与存储空间没有任何关系，都是4个字节。

• INT(x)的最大值和最小值与UNSIGNED有关
  • 有符号型范围 -2147483648 ~ 2147483647，即-2^31 ~ 2^31 - 1
  • 无符号型范围 0 ~ 4294967295，即2^32 - 1

int类型在内存中占4个字节，也就是用32位二进制表示，其中最高位为符号位，0表示正，1表示负。

int类型正数上限二进制表示为0111 1111 1111 1111即(2^32)-1，表示0的符号位是0(即最高位是0)：0000 0000 0000 0000
由于0已经被正数这一边表示了，所以负数的1000 0000 0000 0000就可以用来表示-2^32

• 其他
  • tinyint/smallint/mediumint/int/bigint取值范围计算方法
  • length/char_length函数
  • length()返回字符串str的长度，以字节为单位，多字节字符计为多个字节
  • char_length()返回字符串str的长度，以字符为单位，多字节字符算作单个字符
  • 举例：utf-8编码模式下5汉字字符，length()返回15，而char_length()返回5

7. 参考
   • 官方网站
   • cnblogs

六、内存表和临时表

1. 内存表，指的是使用Memory引擎的表，建表语法是create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。
2. 临时表，可以使用各种引擎类型。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。当然临时表也可以使用 Memory 引擎。
   • 用户临时表
   • 内部临时表

七、函数

1. 内置函数

   • group_concat

     • Oracle的wm_concat()
     • Mysql的group_concat()

   • count

     • count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值。

       • count(*)、count(主键id)和count(1)都表示返回满足条件的结果集的总行数
       • count(字段），则表示返回满足条件的数据行里面，参数“字段”不为NULL的总个数

     • 对比

       • 对于count(主键id)来说，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。
       • 对于count(1)来说，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
       • 对于count(字段)来说，如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。
       • count(*)是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是null，按行累加。

       按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以尽量使用count(*)。

2. 自定义函数

   • 定义
   • 语法

   CREATE
   [DEFINER = { user | CURRENT_USER }]
   [aggregate]
   FUNCTION func_name ([func_parameter [,…]])
   RETURNS type
   [begin]
   body
   [end]

   • aggregate：指定创建的函数是普通的自定义函数，还是聚合(AGGREGATE)函数
   • func_name：自定义函数名称
   • func_parameter：函数参数，格式：[ in/out/inout ] param_name param_type
     • param_name：参数名称
     • param_type：参数类型，可以是任何合法的MySQL数据类型
   • RETURNS type：表示函数的返回值类型，可以是任何合法的MySQL数据类型
   • body：
     • 函数体有合法的SQL语句构成
     • 函数体可以是简单的SELECT或INSERT语句
     • 函数体如果为复合结构则使用BEGIN…END语句
     • 复合结构可以包含声明，循环，控制结构等

3. 实战

CREATE DEFINER=`root`@`localhost` FUNCTION `func2`() 
RETURNS int(11)
begin
declare a int;
declare b int;
set a=rand();
set b=a*100;
return b;
end