一、临时表从创建到查改删与普通表没有区别,只是属于临时创建的表,只对本连接有效,关闭连接后自动删除。
示例:
CREATE TEMPORARY TABLE lsb(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
sex CHAR(5) DEFAULT '男'
);
查看:
SELECT * FROM lsb;
DESC lsb;
SHOW KEYS FROM lsb;
结果:
再测试增改删
增
INSERT INTO lsb VALUES(NULL,'张三','女'),(NULL,'李四',NULL),(NULL,'王五',NULL);
SELECT * FROM lsb;
改:
UPDATE lsb SET NAME='张三丰' WHERE id=1;
SELECT * FROM lsb;
删:
DELETE FROM lsb WHERE id =2;
SELECT * FROM lsb;
临时表和表不同的是查不到约束信息:
SELECT * FROM information_schema.`TABLE_CONSTRAINTS` WHERE table_name='lsb';
临时表删除也同普通表一样:
DROP TABLE lsb;
临时表只对当前连接有效,其它连接无法看到(其它连接查询会报错),关闭连接自动删除。只在当前连接有效,保证了隐私性,也保证了高效率且无并发问题,关闭连接自动删除减轻了磁盘占用,这就是临时表的特点。
二、游标(光标)可以视做指针,用来在函数或存储过程中逐行读取(也就是对一个结果集进行遍历)。光标必须在声明处理程序之前被声明,并且变量和条件必须在声明光标或处理程序之前被声明。
示例:
现在有两张表,一条有4行语句,一条有3行,把两张表中的某些字段插入到第三张表。
创建第三张表:
CREATE TABLE gb(
id INT,
c_name VARCHAR(10)
);
存储过程如下:
DELIMITER //
CREATE PROCEDURE cur()
BEGIN
-- 光标控制变量初始为0
DECLARE done INT DEFAULT 0;
-- 接收变量
DECLARE a INT;
DECLARE b VARCHAR(10);
-- 定义两个光标
DECLARE c1 CURSOR FOR SELECT id FROM aa;
DECLARE c2 CURSOR FOR SELECT country_name FROM country;
-- 光标读取完设置控制变量为1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 开启光标
OPEN c1;
OPEN c2;
WHILE NOT done DO
FETCH c1 INTO a;
FETCH c2 INTO b;
INSERT INTO gb VALUES(a,b);
END WHILE;
-- 关闭光标
CLOSE c1;
CLOSE c2;
END //
DELIMITER;
调用存储过程并查看第三张表:
语法格式:
一、声明游标: declare 游标名称 CURSOR for 结果集
二、打开定义的游标:open 游标名称;
三、获得下一行数据:FETCH 游标名称 into 列值
四、执行语句
五、释放游标:CLOSE 游标名称;
因为光标溢出会引发not foud错误,所以用DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
这条语句来设置光标控制变量done,即初始变量为0(当done为0,not done为真就一直循环),当光标溢出引发not found时设置done为1,此时条件为假,退出循环。
值得注意的是,用来返回结果集的两张表行数并不同,一张为3行,一张为4行。而最终结果是被插入值的表有4行数据,说明遍历是以行数最大的光标为准,而行数较少的光标会在对应结果集最后一行重复读取。