数据库的操作
/*
查询所有数据库
标准语法:
SHOW DATABASES;
*/
-- 查询所有数据库
SHOW DATABASES;
/*
查询某个数据库的创建语句
标准语法:
SHOW CREATE DATABASE 数据库名称;
*/
-- 查询mysql数据库的创建语句
SHOW CREATE DATABASE mysql;
/*
创建数据库
标准语法
CREATE DATABASE 数据库名称;
*/
-- 创建db1数据库
CREATE DATABASE db1;
/*
创建数据库,判断、如果不存在则创建
标准语法:
CREATE DATABASE IF NOT EXISTS 数据库名称;
*/
-- 创建数据库db2(判断,如果不存在则创建)
CREATE DATABASE IF NOT EXISTS db2;
/*
创建数据库、并指定字符集
标准语法:
CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称;
*/
-- 创建数据库db3、并指定字符集utf8
CREATE DATABASE db3 CHARACTER SET utf8;
-- 查看db3数据库的字符集
SHOW CREATE DATABASE db3;
-- 练习:创建db4数据库、如果不存在则创建、指定字符集为gbk
CREATE DATABASE IF NOT EXISTS db4 CHARACTER SET gbk;
-- 查看db4数据库的字符集
SHOW CREATE DATABASE db4;
/*
修改数据库的字符集
标准语法:
ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称
*/
-- 修改数据库db4的字符集为utf8
ALTER DATABASE db4 CHARACTER SET utf8;
-- 查看db4数据库的字符集
SHOW CREATE DATABASE db4;
/*
删除数据库
标准语法:
DROP DATABASE 数据库名称;
*/
-- 删除db1数据库
DROP DATABASE db1;
/*
删除数据库,判断、如果存在则删除
标准语法:
DROP DATABASE IF EXISTS 数据库名称;
*/
-- 删除数据库db2,如果存在
DROP DATABASE IF EXISTS db2;
/*
使用数据库
标准语法:
USE 数据库名称;
*/
-- 使用db4数据库
USE db4;
/*
查询当前使用的数据库
标准语法:
SELECT DATABASE();
*/
-- 查询当前正在使用的数据库
SELECT DATABASE();
数据表的操作
-- 使用mysql数据库
USE mysql;
/*
查询所有数据表
标准用法:SHOW TABLES;
*/
-- 查询库中所有的表
SHOW TABLES;
/*
查询表结构
标准用法:
DESC 表名;
*/
-- 查询user表结构
DESC USER;
/*
查询数据表的字符集
标准用法:
SHOW TABLE STATUS FROM 数据库名称 LIKE ‘ 表名 ’;
*/
-- 查看mysql数据库中user表字符集
SHOW TABLE STATUS FROM mysql LIKE 'user';
/*
创建数据表
标准语法:
CREATE TABLE 表名(
列名 数据类型 约束,
列名 数据类型 约束,
...
列名 数据类型 约束
);
*/
-- 创建一个product 商品表(商品编号、商品名称、商品价格、商品库存、上架时间)
CREATE TABLE product(
id INT,
NAME VARCHAR (20),
price DOUBLE,
stock INT,
insert_time DATE
);
-- 查看product表结构
DESC product;
/*
修改表名
标准语法:
ALTER TABLE 旧表名 RENAME TO 新表名;
*/
-- 修改product 表名为product2
ALTER TABLE product RENAME TO product2;
/*
修改表的字符集
标准语法:
ALTER TABLE 表名 CHARACTER SET 字符集名称;
*/
-- 查看db3数据库中product2数据表字符集
SHOW TABLE STATUS FROM mysql LIKE 'product2';
-- 修改product2数据表字符集为gbk
ALTER TABLE product2 CHARACTER SET gbk;
/*
给表添加列
标准语法:
ALTER TABLE 表名 ADD 列名 数据类型;
*/
-- 给product2表添加一列color
ALTER TABLE product2 ADD color VARCHAR(200);
-- 查看product2表结构
DESC product2;
/*
修改表中列的数据类型
标准语法:
ALTER TABLE 表名 MODIFY 列名 数据类型;
*/
-- 将color数据类型修改为int
ALTER TABLE product2 MODIFY color INT;
-- 删除color属性
ALTER TABLE product2 drop color;
-- 查看product2表结构
DESC product2;
/*
修改表中列的名称和数据类型
标准语法:
ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型
*/
-- 将color修改为address
ALTER TABLE product CHANGE color address VARCHAR(200);
新增表数据
USE db1;
CREATE TABLE product(
id INT,
NAME VARCHAR (20),
price DOUBLE,
stock INT,
insert_time DATE
);
/*
给指定列添加数据
标准语法:
INSERT INTO 表名 (列名1,列名2,...) VALUES (值1,值2,....);
*/
-- 向product表添加一条数据
INSERT INTO product (id,NAME,price,stock,insert_time) VALUES (1,'玩具',100,5,'2021-02-01');
-- 向product表添加指定列数据
/*
给全部列添加数据
标准语法:
INSERT INTO 表名 VALUES (值1,值2,....);
*/
INSERT INTO product VALUES (1,'玩具',100,5,'2021-02-01'),(2,'食品',20,100,'2021-01-01');
修改和删除表数据
/*
修改表数据
标准语法:
UPDATE 表名 SET 列名1=值1,列名2=值2,... where 条件;
*/
-- 修改手机的价格为3500
UPDATE product SET price=3500 WHERE NAME='变形金刚';
-- 修改电脑的价格为1800、库存为36
UPDATE product SET price=1800,stock=36 WHERE NAME='笔记本';
/*
删除表数据
标准语法:
DELETE FROM 表名 WHERE 条件;
*/
-- 删除product表中的玩具信息
DELETE FROM product WHERE NAME='玩具';
-- 删除product表中库存为10的商品信息
DELETE FROM product WHERE stock=10;
-- 删除profuct数据表
DROP TABLE product;
外键约束
-- 创建db2数据库
CREATE DATABASE db2;
-- 使用db2数据库
USE db2;
/*
外键约束
标准语法:
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
*/
-- 建表时添加外键约束
-- 创建user用户表
CREATE TABLE USER(
id INT PRIMARY KEY AUTO_INCREMENT, -- id
NAME VARCHAR(20) NOT NULL -- 姓名
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');
-- 创建orderlist订单表
CREATE TABLE orderlist(
id INT PRIMARY KEY AUTO_INCREMENT, -- id
number VARCHAR(20) NOT NULL, -- 订单编号
uid INT, -- 外键列
CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加订单数据
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
(NULL,'hm003',2),(NULL,'hm004',2);
-- 添加一个订单,但是没有真实用户。添加失败
INSERT INTO orderlist VALUES (NULL,'hm005',3);
-- 删除李四用户。删除失败
DELETE FROM USER WHERE NAME='李四';
/*
删除外键约束
标准语法:
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
*/
-- 删除外键约束
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
/*
建表后单独添加外键约束
标准语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
*/
-- 添加外键约束
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);
主键约束
-- 创建学生表(编号、姓名、年龄) 编号设为主键
CREATE TABLE student(
id INT PRIMARY KEY,
NAME VARCHAR(100),
age INT
);
-- 查询学生表的详细信息
DESC student;
-- 添加数据
INSERT INTO student VALUES(1, '张三', 23);
INSERT INTO student VALUES(2, '李四', 24);
-- 删除主键
ALTER TABLE student DROP PRIMARY KEY;
-- 建表后单独添加主键约束
ALTER TABLE student MODIFY id INT PRIMARY KEY;
外键级联操作
/*
添加外键约束,同时添加级联更新 标准语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
ON UPDATE CASCADE;
添加外键约束,同时添加级联删除 标准语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
ON DELETE CASCADE;
添加外键约束,同时添加级联更新和级联删除 标准语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)
ON UPDATE CASCADE ON DELETE CASCADE;
*/
-- 删除外键约束
ALTER TABLE orderlist DROP FOREIGN KEY ou_fk1;
-- 添加外键约束,同时添加级联更新和级联删除
ALTER TABLE orderlist ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
ON UPDATE CASCADE ON DELETE CASCADE;
-- 将李四这个用户的id修改为3,订单表中的uid也自动修改
UPDATE USER SET id=3 WHERE id=2;
-- 将李四这个用户删除,订单表中的该用户所属的订单也自动删除
DELETE FROM USER WHERE id=3;
主键自增约束
-- 删除数据表
DROP TABLE student;
-- 创建学生表的详细信息
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT
);
-- 查询学生表的详细信息
DESC student;
-- 添加数据
INSERT INTO student VALUES(NULL, '张三', 23), (NULL, '李四', 24);
-- 删除自增约束
ALTER TABLE student MODIFY id INT;
-- 建表后单独添加自增约束
ALTER TABLE student MODIFY id INT AUTO_INCREMENT;
唯一约束
-- 创建学生表(编号、姓名、年龄) 编号设为主键自增,年龄设为唯一
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT UNIQUE
);
-- 查询学生表的详细信息
DESC student;
-- 添加数据
INSERT INTO student VALUES (NULL, '张三', 23);
INSERT INTO student VALUES (NULL, '李四', 23);
-- 删除唯一约束
ALTER TABLE student DROP INDEX age;
-- 建表后单独添加唯一约束
ALTER TABLE student MODIFY age INT UNIQUE;
非空约束
-- 创建学生表(编号、姓名、年龄) 编号设为主键自增,年龄设为唯一
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30) NOT NULL,
age INT UNIQUE
);
-- 查询学生表的详细信息
DESC student;
-- 添加数据
INSERT INTO student VALUES (NULL, '张三', 23);
-- 删除非空约束
ALTER TABLE student MODIFY NAME VARCHAR(30);
-- 建表后单独添加非空约束
ALTER TABLE student MODIFY NAME VARCHAR(30) NOT NULL;
多表查询 —— 内连接查询
/*
显示内连接
标准语法:
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;
*/
-- 查询用户信息和对应的订单信息
SELECT * FROM USER INNER JOIN orderlist ON orderlist.uid = USER.id;
-- 查询用户信息和对应的订单信息,起别名
SELECT * FROM USER u INNER JOIN orderlist o ON o.uid = u.id;
/*
隐式内连接
标准语法:
SELECT 列名 FROM 表名1,表名2 WHERE 关联条件;
*/
-- 查询用户姓名,年龄和订单编号
SELECT
u.name,
u.age,
o.number
FROM
USER u,
orderlist o
WHERE
u.id = o.uid;
多表查询 —— 外连接查询
/*
左外连接
标准语法:
SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
*/
-- 查询所有用户信息,以及用户对应的订单信息
SELECT
u.*,
o.number
FROM
USER u
LEFT OUTER JOIN
orderlist o
ON
o.uid = u.id;
/*
右外连接
标准语法:
SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
*/
-- 查询所有订单信息,以及用户对应的用户信息
SELECT o.number, u.* FROM USER u RIGHT OUTER JOIN orderlist o ON o.uid = u.id;
SELECT * FROM USER WHERE age = 23;
多表查询 —— 子查询
/*
结果是单行单列的
标准语法:
SELECT 列名 FROM 表名 WHERE 列名=(SELECT 列名 FROM 表名 [WHERE 条件]);
*/
-- 查询年龄最高的用户姓名
SELECT NAME, age FROM USER WHERE age=(SELECT MAX(age) FROM USER);
/*
结果是多行单列的
标准语法:
SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]);
*/
-- 查询张三和李四的订单信息
SELECT id FROM USER WHERE NAME IN ('张三', '李四');
SELECT * FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME IN ('张三', '李四'));
/*
结果是多行多列的
标准语法:
SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];
*/
-- 查询订单表中id大于4的订单信息和所属用户信息
SELECT * FROM orderlist WHERE id > 4;
SELECT
u.name,
o.number
FROM
USER u,
(SELECT * FROM orderlist WHERE id > 4) o
WHERE
o.uid = u.id;
多表查询 —— 自关联查询
-- 创建员工表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
NAME VARCHAR(20), -- 员工姓名
mgr INT, -- 上级编号
salary DOUBLE -- 员工工资
);
-- 添加数据
INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.00),
(1002,'猪八戒',1005,8000.00),
(1003,'沙和尚',1005,8500.00),
(1004,'小白龙',1005,7900.00),
(1005,'唐僧',NULL,15000.00),
(1006,'武松',1009,7600.00),
(1007,'李逵',1009,7400.00),
(1008,'林冲',1009,8100.00),
(1009,'宋江',NULL,16000.00);
-- 查询所有员工的姓名及其直接上级的姓名,没有上级的员工也需要查询
/*
分析
员工信息 employee表
条件:employee.mgr = employee.id
查询左表的全部数据,和左右两张表有交集部分数据,左外连接
*/
ALTER TABLE employee RENAME TO emp;
SELECT
e1.id,
e1.NAME,
e2.NAME
FROM
emp AS e1
INNER JOIN
emp AS e2
ON
e1.id = e2.mgr;
视图的创建和查询
/*
创建视图
标准语法
CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;
*/
-- 创建city_country视图,保存城市和国家的信息(使用指定列名)
CREATE VIEW city_country (city_id,city_name,country_name) AS
SELECT
c1.id,
c1.name,
c2.name
FROM
city c1,
country c2
WHERE
c1.cid=c2.id;
/*
查询视图
标准语法
SELECT * FROM 视图名称;
*/
-- 查询视图
SELECT * FROM city_country;
视图的修改和删除
/*
修改视图数据
标准语法
UPDATE 视图名称 SET 列名=值 WHERE 条件;
修改视图结构
标准语法
ALTER VIEW 视图名称 (列名列表) AS 查询语句;
*/
-- 修改视图数据,将北京修改为深圳。(注意:修改视图数据后,源表中的数据也会随之修改)
SELECT * FROM city_country;
UPDATE city_country SET city_name='深圳' WHERE city_name='北京';
-- 将视图中的country_name修改为name
ALTER VIEW city_country (city_id,city_name,NAME) AS
SELECT
c1.id,
c1.name,
c2.name
FROM
city c1,
country c2
WHERE
c1.cid=c2.id;
/*
删除视图
标准语法
DROP VIEW [IF EXISTS] 视图名称;
*/
-- 删除city_country视图
DROP VIEW IF EXISTS city_country;
创建和调用存储过程
/*
创建存储过程
-- 修改分隔符为$
DELIMITER $
-- 标准语法
CREATE PROCEDURE 存储过程名称(参数列表)
BEGIN
SQL 语句列表;
END$
-- 修改分隔符为分号
DELIMITER ;
*/
-- 创建stu_group()存储过程,封装 分组查询总成绩,并按照总成绩升序排序的功能
DELIMITER $
CREATE PROCEDURE stu_group()
BEGIN
SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END$
DELIMITER ;
/*
调用存储过程
CALL 存储过程名称(实际参数);
*/
-- 调用stu_group()存储过程
CALL stu_group();
查看和删除存储过程
/*
查询数据库中所有的存储过程
SELECT * FROM mysql.proc WHERE db='数据库名称';
*/
-- 查看db6数据库中所有的存储过程
SELECT * FROM mysql.`proc` WHERE db='db6';
/*
删除存储过程
DROP PROCEDURE [IF EXISTS] 存储过程名称;
*/
DROP PROCEDURE IF EXISTS stu_group;
存储过程 —— 变量
/*
定义变量
DECLARE 变量名 数据类型 [DEFAULT 默认值];
*/
-- 定义一个int类型变量,并赋默认值为10
DELIMITER $
CREATE PROCEDURE pro_test1()
BEGIN
-- 定义变量
DECLARE num INT DEFAULT 10;
-- 使用变量
SELECT num;
END$
DELIMITER ;
-- 调用pro_test1存储过程
CALL pro_test1();
/*
变量赋值-方式一
SET 变量名 = 变量值;
*/
-- 定义一个varchar类型变量并赋值
DELIMITER $
CREATE PROCEDURE pro_test2()
BEGIN
-- 定义变量
DECLARE NAME VARCHAR(10);
-- 为变量赋值
SET NAME = '存储过程';
-- 使用变量
SELECT NAME;
END$
DELIMITER ;
-- 调用pro_test2存储过程
CALL pro_test2();
-- 查看存储过程
SELECT * FROM mysql.`proc` WHERE db='db6';
/*
变量赋值-方式二
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];
*/
-- 定义两个int变量,用于存储男女同学的总分数
DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
-- 定义两个变量
DECLARE men,women INT;
-- 查询男同学的总分数,为men赋值
SELECT SUM(score) INTO men FROM student WHERE gender='男';
-- 查询女同学的总分数,为women赋值
SELECT SUM(score) INTO women FROM student WHERE gender='女';
-- 使用变量
SELECT men,women;
END$
DELIMITER ;
-- 调用pro_test3存储过程
CALL pro_test3();
存储过程 —— if 语句
/*
if语句
IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;
*/
/*
定义一个int变量,用于存储班级总成绩
定义一个varchar变量,用于存储分数描述
根据总成绩判断:
380分及以上 学习优秀
320 ~ 380 学习不错
320以下 学习一般
*/
DELIMITER $
CREATE PROCEDURE pro_test4()
BEGIN
-- 定义变量
DECLARE total INT;
DECLARE info VARCHAR(10);
-- 查询总成绩,为total赋值
SELECT SUM(score) INTO total FROM student;
-- 对总成绩判断
IF total > 380 THEN
SET info='学习优秀';
ELSEIF total >=320 AND total <= 380 THEN
SET info='学习不错';
ELSE
SET info='学习一般';
END IF;
-- 查询总成绩和描述信息
SELECT total,info;
END$
DELIMITER ;
-- 调用pro_test4存储过程
CALL pro_test4();
存储过程 —— 参数传递
/*
参数传递
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
SQL 语句列表;
END$
*/
/*
输入总成绩变量,代表学生总成绩
输出分数描述变量,代表学生总成绩的描述信息
根据总成绩判断:
380分及以上 学习优秀
320 ~ 380 学习不错
320以下 学习一般
*/
DELIMITER $
CREATE PROCEDURE pro_test5(IN total INT,OUT info VARCHAR(10))
BEGIN
IF total > 380 THEN
SET info='学习优秀';
ELSEIF total >=320 AND total <= 380 THEN
SET info='学习不错';
ELSE
SET info='学习一般';
END IF;
END$
DELIMITER ;
-- 调用pro_test5存储过程
CALL pro_test5((SELECT SUM(score) FROM student),@info);
SELECT @info;
存储过程 —— while循环
/*
while循环
初始化语句;
WHILE 条件判断语句 DO
循环体语句;
条件控制语句;
END WHILE;
*/
-- 计算1~100之间的偶数和
DELIMITER $
CREATE PROCEDURE pro_test6()
BEGIN
-- 定义求和变量
DECLARE result INT DEFAULT 0;
-- 定义初始化变量
DECLARE num INT DEFAULT 1;
-- while循环
WHILE num <= 100 DO
IF num % 2 = 0 THEN
SET result = result + num;
END IF;
SET num = num + 1;
END WHILE;
-- 查询求和结果
SELECT result;
END$
DELIMITER ;
-- 调用pro_test6存储过程
CALL pro_test6();
存储函数
/*
创建存储函数
CREATE FUNCTION 函数名称([参数 数据类型])
RETURNS 返回值类型
BEGIN
执行的sql语句;
RETURN 结果;
END$
*/
-- 定义存储函数,获取学生表中成绩大于95分的学生数量
DELIMITER $
CREATE FUNCTION fun_test1()
RETURNS INT
BEGIN
-- 定义变量
DECLARE s_count INT;
-- 查询成绩大于95分的数量,为s_count赋值
SELECT COUNT(*) INTO s_count FROM student WHERE score > 95;
-- 返回统计结果
RETURN s_count;
END$
DELIMITER ;
/*
调用函数
SELECT 函数名称(实际参数);
*/
-- 调用函数
SELECT fun_test1();
/*
删除函数
DROP FUNCTION 函数名称;
*/
-- 删除函数
DROP FUNCTION fun_test1;
repeat循环
/*
repeat循环
标准语法
初始化语句;
repeat
循环体语句
条件控制语句
UNTIL 条件判断语句
END REPEAT;
*/
-- 注意:repeat循环是条件满足则停止。while循环是条件满足则执行
-- 计算1~10之间的和
DELIMITER $
CREATE PROCEDURE pro_test9()
BEGIN
-- 定义求和变量
DECLARE result INT DEFAULT 0;
-- 定义初始化变量
DECLARE num INT DEFAULT 1;
-- repeat循环
REPEAT
-- 累加
SET result = result + num;
-- 让num + 1
SET num = num + 1;
-- 停止循环
UNTIL num > 10
END REPEAT;
-- 查询求和结果
SELECT result;
END$
DELIMITER ;
-- 调用pro_test9存储过程
CALL pro_test9();
输出结果:
![](https://i-blog.csdnimg.cn/blog_migrate/600da9e5f533ccc37cb0e5caaba658bc.png)
loop循环
/*
loop循环
标准语法
初始化语句;
[循环名称:] LOOP
条件判断语句
[LEAVE 循环名称;]
循环体语句;
条件控制语句;
END LOOP 循环名称;
注意:loop可以实现简单的循环,但是退出循环需要使用其他的语句来定义。我们可以使用leave语句完成!
如果不加退出循环的语句,那么就变成了死循环。*/
-- 计算1~10之间的和
DELIMITER $
CREATE PROCEDURE pro_test10()
BEGIN
-- 定义求和变量
DECLARE result INT DEFAULT 0;
-- 定义初始化变量
DECLARE num INT DEFAULT 1;
-- loop循环
l:LOOP
-- 条件成立,停止循环
IF num > 10 THEN
LEAVE l;
END IF;
-- 累加
SET result = result + num;
-- 让num+1
SET num = num + 1;
END LOOP l;
-- 查询求和结果
SELECT result;
END$
DELIMITER ;
-- 调用pro_test10存储过程
CALL pro_test10();
输出结果:
![](https://i-blog.csdnimg.cn/blog_migrate/7f921793ba5b9da2fd32bb6164a6eefe.png)
游标的基本使用
/*
游标
标准语法
1.创建游标:DECLARE 游标名称 CURSOR FOR 查询sql语句
2.打开游标:OPEN 游标名称;
3.使用游标:FETCH 游标名称 INTO 变量名1,变量名2, ......
4.关闭游标:CLOSE 游标名称
*/
-- 创建stu_score表
CREATE TABLE stu_score(
id INT PRIMARY KEY AUTO_INCREMENT,
score INT
);
DELIMITER $
CREATE PROCEDURE pro_test11()
BEGIN
-- 定义一个保存成绩的变量
DECLARE s_score INT;
-- 创建游标
DECLARE stu_result CURSOR FOR SELECT score FROM student;
-- 开启游标
OPEN stu_result;
-- 使用游标
-- 遍历结果,拿到第1行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES(NULL, s_score);
-- 遍历结果,拿到第2行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES(NULL, s_score);
-- 遍历结果,拿到第3行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES(NULL, s_score);
-- 遍历结果,拿到第4行数据
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES(NULL, s_score);
-- 关闭游标
CLOSE stu_result;
END$
DELIMITER ;
-- 调用pro_test11的存储过程
CALL pro_test11();
-- 查询stu_score数据表
SELECT * FROM stu_score;
游标的优化
/*
当游标结束后,会自动的触发一个事件,就代表游标已经结束了
加标记的思想:
1.定义一个标记变量,默认值为0(意味着有数据)
2.当游标结束后,将游标变量的值修改为1(意味着没有数据了)
*/
DELIMITER $
CREATE PROCEDURE pro_test12()
BEGIN
-- 定义一个保存成绩的变量
DECLARE s_score INT;
-- 定义一个标记变量,默认值为0
DECLARE flag INT DEFAULT 0;
-- 创建游标
DECLARE stu_result CURSOR FOR SELECT score FROM student;
-- 当游标结束后,将标记变量的值修改为1(意味没有数据了)
DECLARE EXIT HANDLER FOR NOT FOUND SET flag = 1;
-- 开启游标
OPEN stu_result;
-- 循环使用游标
REPEAT
-- 遍历结果,拿到
FETCH stu_result INTO s_score;
-- 将数据保存到stu_score表中
INSERT INTO stu_score VALUES(NULL,s_score);
-- 当flag的值是1的时候,就停止循环
UNTIL flag = 1
END REPEAT;
-- 关闭游标
CLOSE stu_result;
END$
DELIMITER ;
-- 调用stu_score表
CALL pro_test12();
-- 查询stu_score表
SELECT * FROM stu_score;