MySQL基本概念

临时表

临时表可能是非常有用的，在某些情况下，保持临时数据。最重要的是应该知道的临时表是，他们将当前的客户端会话终止时被删除。

当工作在非常大的表上时，你可能偶尔需要运行很多查询获得一个大量数据的小的子集，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些，然后多这些表运行查询。

表建在内存里，数据在内存里。

创建临时表很容易，给正常的CREATE TABLE语句加上TEMPORARY关键字：

CREATE TEMPORARY TABLE tmp_table (
    name VARCHAR(10) NOT NULL,
    value INTEGER NOT NULL
)

临时表将在你连接MySQL期间存在。当你断开时，MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。

DROP TABLE tmp_table

如果在你创建名为tmp_table临时表时名为tmp_table的表在数据库中已经存在，临时表将有必要屏蔽（隐藏）非临时表tmp_table。

如果你声明临时表是一个HEAP（内存）表，MySQL也允许你指定在内存中创建它：

CREATE TEMPORARY TABLE tmp_table (
    name VARCHAR(10) NOT NULL,
    value INTEGER NOT NULL
) TYPE = HEAP

因为HEAP表存储在内存中，你对它运行的查询可能比磁盘上的临时表快些。然而，HEAP表与一般的表有些不同，且有自身的限制。

临时表和内存表的ENGINE 不同，临时表默认的是Mysql指定的默认Engine,而内存表是MEMORY。

临时表是指使用create temprary table创建的临时表.临时表可以使用任何存储引擎,临时表只在单个连接中可见,当连接断开时,临时表也会消失。

MySQL最初会将临时表创建在内存中,当数据变的太大后,就会转储到磁盘上。

使用内存临时表的场景

1）ORDER BY子句和GROUP BY子句不同， 例如：ORDERY BY price GROUP BY name；

2）在JOIN查询中，ORDER BY或者GROUP BY使用了不是第一个表的列 例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name

3）ORDER BY中使用了DISTINCT关键字 ORDERY BY DISTINCT(price)

4）SELECT语句中指定了SQL_SMALL_RESULT关键字 SQL_SMALL_RESULT的意思就是告诉MySQL，结果会很小，请直接使用内存临时表，不需要使用索引排序 SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用 一般情况下，我们没有必要使用这个选项，让MySQL服务器选择即可。

直接使用磁盘临时表的场景

1）表包含TEXT或者BLOB列；

2）GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列；

3）使用UNION或者UNION ALL时，SELECT子句中包含大于512字节的列；

在MySql中，任何一个查询实质上都是一个关联查询。那么对于子查询或UNION查询是如何实现关联操作的呢。
对于UNION查询，MySql先将每一个单表查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询。MySql读取结果临时表和普通表一样，也是采用的关联方式。
当遇到子查询时，先执行子查询并将结果放到一个临时表中，然后再将这个临时表当做一个普通表对待。
MySql的临时表是没有任何索引的，在编写复杂的子查询和关联查询的时候需要注意这一点。
临时表也叫派生表。

内存表（HEAP）

内存表，就是放在内存中的表，所使用内存的大小可通过My.cnf中的max_heap_table_size指定，如max_heap_table_size=1024M，

内存表与临时表并不相同，临时表也是存放在内存中，临时表最大所需内存需要通过tmp_table_size = 128M设定。当数据超过临时表的最大值设定时，自动转为磁盘表，此时因需要进行IO操作，性能会大大下降。

而内存表不会，内存表满后，会提示数据满错误。

表建在磁盘里，数据在内存里。

临时表和内存表都可以人工创建，但临时表更多的作用是系统自己创建后，组织数据以提升性能，如子查询，临时表在多个连接之间不能共享。这里只讨论内存表。

HEAP表是访问数据速度最快的MySQL表，他使用保存在内存中的散列索引。但如果MySQL或者服务器重新启动，表中数据将会丢失。

1.表受限于mysqld变量max_heap_table_size(默认16M)；
2.HEAP数据表从MySQL4.1开始才允许使用自增列；
3.ENGINE =Heap;从MySQL4.1后，首选 ENGINE =MEMORY;
5.与许多其他散列表不同，Heap表允许非惟一的键；
6.Memory表使用固定长度行的格式存储
7.不支持BLOB或TEXT列；
8.除了max_heap_table_size限制和计算机的内存限制以外，可以在有些安装上达到每个表4GB的限制，因为这个限制是由32位计算机的地址空间强加的。
9.heap对所有用户的连接是可见的，这使得它非常适合做缓存。

create table test(
    id int unsigned not null auto_increment primary key,
    state char(10),
    type char(20),
    date char(30)
)ENGINE=MEMORY DEFAULT CHARSET=utf8;

视图

视图是一个虚拟表，其内容由查询定义。视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）。

对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。

通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图的作用

• 方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；
• 更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别；

应用场景

• 权限控制的时候，不希望用户访问表中某些含敏感信息的列，比如salary…

具体使用

-- ----------------------------
-- View structure for `view_user_course`
-- ----------------------------
DROP VIEW
IF EXISTS `view_user_course`;

CREATE ALGORITHM = UNDEFINED 
DEFINER = `root`@`localhost` 
SQL SECURITY DEFINER 
VIEW `view_user_course` AS (
    具体SQL查询语句内容...
);

几点说明（MySQL中的视图在标准SQL的基础之上做了扩展）：

• ALGORITHM=UNDEFINED：指定视图的处理算法；
• DEFINER=root@localhost：指定视图创建者；
• SQL SECURITY DEFINER：指定视图查询数据时的安全验证方式；

创建好视图之后，我们可以直接用以下SQL语句在视图上查询相关信息，同样可以得到所需结果：

SELECT
    vuc.username,
    vuc.coursename
FROM
    view_user_course vuc
WHERE
     vuc.username = '小张'

更新视图

MySQL中，更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟表，其中没有数据，所以通过视图更新时，都是转换到基本表来更新。

更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。
某些视图是可更新的。也就是说，可以在诸如UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。更具体地讲，如果视图包含下述结构中的任何一种，那么它就是不可更新的：

· 聚合函数（SUM(), MIN(), MAX(), COUNT()等）。
· DISTINCT
· GROUP BY
· HAVING
· UNION或UNION ALL
· 位于选择列表中的子查询
· Join
· FROM子句中的不可更新视图
· WHERE子句中的子查询，引用FROM子句中的表。
· 仅引用文字值（在该情况下，没有要更新的基本表）。
· ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。

视图和临时表的区别
视图只是一条预编译的SQL语句，并不保存实际数据
临时表是保存在tempdb中的实际的表
物理空间的分配不一样，试图不分配空间，临时表会分配空间
视图是一个快照,是一个虚表
临时表是客观存在的表类型对象Create TEMPORARY table
它们的结构一个是表、一个快照。可以把视图像象成联合表的快捷方式

触发器

自定义函数

用户自定义函数（user-defined function,UDF）是一种对MySQL扩展的途径，其用法与内置函数相同。

自定义函数两个必要条件：
（1）参数
（2）返回值

创建自定义函数

CREATE FUNCTION function_name
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body

关于函数体

（1）函数体由合法的SQL语句构成；
（2）函数体可以是简单的SELECT或INSERT语句；
（3）函数体如果为复合结构则使用BEGIN…END语句；
（4）复合结构可以包含声明，循环，控制结构。

SET NAMES gbk; //仅影响客户端编码
SELECT NOW();
SELECT DATE_FORMAT(NOW(),'Y年%m月%d日 %H点:%i分:%s秒');

CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'Y年%m月%d日 %H点:%i分:%s秒');

SELECT f1();


CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGEND) RETURNS FLOAT(10,2) UNSIGEND
RETURN (num1+num2)/2;

SELECT f2(10,15);

DELIMITER //
CREATE FUNCTION adduser(username VARCHAR(20)) RETURNS INT UNSIGNED 
BEGIN
INSERT test(username) values (username)
RETURN LAST_INSERT_ID();
END
//
DELIMITER;

SELECT addusers('Rose');

存储过程

MySQL命令的解析过程：

SQL命令—>MySQL引擎（分析）—>语法正确—>可识别命令—>（执行）—>执行结果（返回）—>客户端

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储作为一个单元处理。

优点

• 增强SQL语句的功能和灵活性
• 实现较快的执行速度
• 减少网络流量

查询存储过程

show procedure status;
select specific_name from mysql.proc;

查看存储过程或函数的创建代码

show create procedure procedure_test03;
show create function func_name;

删除存储过程

drop procedure procedure_test03;

创建存储过程

CREATE 
[DEFINER = {USER|CURRENT_USER}]
PROCEDURE sp_name ([proc_paramer[,...]])
[characteristic...] routine_body

proc_parameter;
[IN|OUT|INOUT] param_name type

参数

IN，表示该参数的值必须在调用存储过程时指定
OUT，表示该参数的值可以被存储过程改变，并且可以返回
INOUT，表示该参数的调用时指定，并且可以被改变和返回

过程体

• 过程体由合法的SQL语句构成；
• 过程体可以是任意SQL语句；
• 过程体如果为复合结构则使用BEGIN…END语句；
• 复合结构可以包含声明、循环、控制结构。

创建无参存储过程

CREATE PROCEDURE sp1() SELECT VERSION();

创建带有IN类型参数的存储过程

DELIMITER //
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELITE FROM users WHERE id=p.id;
END
//
DELIMITER

调用：

CALL removeUserById(3);

创建带有IN和OUT类型参数的存储过程

DELIMITER //
CREATE PROCEDURE removeUserAndEReturn(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p.id;
SELECT count(id) FROM users INTO userNums;
END
//
DELIMITER

调用：

CALL removeUserAndEReturn(27,@nums);
SELECT @nums;

创建带有多个OUT类型参数的存储过程

SELECT ROW_COUNT(); //上一条SQL语句影响的数据行数

DELIMITER //
CREATE PROCEDURE removeUserByAgeAndReturn(IN p_age INT UNSIGNED,OUT delNums INT UNSIGNED,OUT levNums INT UNSIGNED)
BEGIN
DELETE FROM users WHERE age = p_age;
SELECT ROW_COUNT() INTO delNums;
SEELCT COUNT(id) FROM users INTO userCounts;
END
//
DELIMITER

调用：

CALL removeUserByAgeAndReturn(20,@a,@b);
SELECT @a,@b;

另举例：

delimiter //
CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure_test03`()
begin
DECLARE str_date CHAR(20);
DECLARE create_sql VARCHAR(500);
SET str_date=DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 DAY), "%Y%m%d"); 
SELECT str_date;
SET create_sql=CONCAT('create table IF NOT EXISTS dt_wo_clickeven_',str_date,'(
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `msisdn` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `ruleID` INT(11) DEFAULT NULL,
  `popTime` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8');
SELECT create_sql;
SET @create_sql=create_sql;
PREPARE p1 FROM @create_sql;
EXECUTE p1;
DEALLOCATE PREPARE p1;
end
delimiter //

存储过程与自定义函数的区别

• 存储过程实现的功能要复杂一些；而函数针对性更强
• 存储过程可以返回多个值，函数只能有一个返回值
• 存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分来出现；

定时器Event

开启功能

show variables like '%sche%';
set global event_scheduler = ON; 

查询

SELECT EVENT_NAME,EVENT_DEFINITION,INTERVAL_FIELD,LAST_EXECUTED FROM information_schema.EVENTS;
SELECT * FROM mysql.event;

SELECT * FROM information_schema.EVENTS;
//可以看到相应的库中的作业信息，其中LAST_EXECUTED字段会反映出相应的作业最近一次的执行时间

show create EVENT event_test03;

创建

CREATE EVENT `event_test01` ON SCHEDULE EVERY 1 DAY STARTS '2014-03-24 14:48:51' ON COMPLETION PRESERVE ENABLE DO call procedure_test01()