MySQL存储过程练习

MySQL数据库基础与实例教程练习题参考答案由于时间仓促，中难免存在错误，不妥之处恳请读者批评指正！第一章答案1．数据库管理系统中常用的数学模型有哪些？数据库管理系统通常会选择某种“数学模型”存储、组织、管理数据库中的数据，常用的数学模型包括“层次模型”、“网状模型”、“关系模型”以及“面向对象模型”等。

2．您听说过的关系数据库管理系统有哪些？数据库容器中通常包含哪些数据库对象？目前成熟的关系数据库管理系统主要源自欧美数据库厂商，典型的有美国微软公司的SQL Server、美国IBM公司的DB2和Informix、德国SAP公司的Sybase、美国甲骨文公司的Oracle。

数据库容器中通常包含表、索引、视图、存储过程、触发器、函数等数据库对象。

3．通过本章知识的讲解，SQL与程序设计语言有什么关系？SQL并不是一种功能完善的程序设计语言，例如，不能使用SQL构建人性化的图形用户界面（Graphical User Interface，GUI），程序员需要借助Java、VC++等面向对象程序设计语言或者HTML的FORM表单构建图形用户界面（GUI）。

如果选用FORM表单构建GUI，程序员还需要使用JSP、PHP或者.NET编写Web应用程序，处理FORM表单中的数据以及数据库中的数据。

其他答案：1、首先SQL语言是数据库结构化查询语言，是非过程化编程语言。

而程序设计语言则有更多的面向对象及逻辑程序设计。

比如用SQL语言编写图形用户界面（例如窗口、进度条），是无法实现的。

2、SQL语言可以说是，程序设计语言和数据库之间的一个翻译官。

程序设计语言需要操作数据库时，需要借助（或者说调用）SQL语言来翻译给数据库管理系统。

3、不同数据库管理系统会有一些特殊的SQL规范，比如limit关键词在SQL Server 中无法使用。

而这些规范与程序设计语言无关。

4．通过本章的学习，您了解的MySQL有哪些特点？与题目2中列举的商业化数据库管理系统相比，MySQL具有开源、免费、体积小、便于安装，但功能强大等特点。

mysql存储过程练习题MySQL存储过程练习题MySQL是一种常用的关系型数据库管理系统，它提供了存储过程的功能，可以将一系列SQL语句封装为一个可重复使用的代码块。

存储过程可以提高数据库操作的效率，并且可以实现复杂的业务逻辑。

在本文中，我们将介绍一些MySQL存储过程的练习题，帮助读者巩固对存储过程的理解和应用。

1. 创建一个存储过程，查询指定学生的成绩信息。

```sqlDELIMITER //CREATE PROCEDURE get_student_score(IN student_id INT)BEGINSELECT * FROM scores WHERE student_id = student_id;END //DELIMITER ;```这个存储过程接收一个学生ID作为参数，然后查询该学生的成绩信息并返回结果。

2. 创建一个存储过程，计算指定学生的平均成绩。

```sqlDELIMITER //CREATE PROCEDURE get_student_average_score(IN student_id INT, OUT average_score DECIMAL(5,2))BEGINSELECT AVG(score) INTO average_score FROM scores WHERE student_id = student_id;END //DELIMITER ;```这个存储过程接收一个学生ID作为输入参数，并将平均成绩作为输出参数返回。

3. 创建一个存储过程，查询指定科目的成绩最高和最低的学生。

```sqlDELIMITER //CREATE PROCEDURE get_top_bottom_student(IN subject_id INT, OUTtop_student VARCHAR(50), OUT bottom_student VARCHAR(50))BEGINSELECT student_name INTO top_student FROM scores WHERE subject_id = subject_id ORDER BY score DESC LIMIT 1;SELECT student_name INTO bottom_student FROM scores WHERE subject_id= subject_id ORDER BY score ASC LIMIT 1;END //DELIMITER ;```这个存储过程接收一个科目ID作为输入参数，并将成绩最高和最低的学生姓名作为输出参数返回。

mysql⽣成时间序列数据-存储过程 由于时间⾃动转换为int值，做⼀步转化，也可在调⽤时处理use `test`;CREATE table test.test1 asSELECT state, id, `规格条码`,`⾊号条码`,`货号`,`在售平台`,`平台售价`,DATE_ADD('1900-01-01', Interval data1.`上架时间` day) as `上架时间`,`下架时间`,`操作员`FROM data1;CREATE table test.test2 asSELECT state, id,DATE_ADD('1900-01-01', Interval `时间` day) as `时间`,`在售平台`,`规格条码`,`销量`,`销售额`,`撤销标志`FROM data2; ⽣成时间序列数据USE `test`;DROP TABLE IF EXISTS tmptb;CREATE TEMPORARY TABLE tmptb (id INT UNSIGNED AUTO_INCREMENT,date DATE NOT NULL,shop VARCHAR(20) NOT NULL DEFAULT 0,sales INT UNSIGNED DEFAULT 0,PRIMARY KEY ( id ))ENGINE=InnoDB DEFAULT CHARSET=utf8;DELIMITER //DROP PROCEDURE IF EXISTS DayRangeProc//CREATE PROCEDURE DayRangeProc ( IN start_date DATE, IN end_date DATE)BEGINDECLARE i, range_day INT;SET i = 0;SET range_day = (SELECT DATEDIFF(end_date, start_date));WHILE i <= range_day DOINSERT INTO tmptb(date) VALUES (ADDDATE(start_date, i));-- SET @sqlcmd = CONCAT('INSERT INTO ', tmptb, ' (date) VALUES (', temp, ')');-- PREPARE stmt FROM @sqlcmd;-- EXECUTE stmt;-- DEALLOCATE PREPARE stmt;SET i = i + 1;END WHILE;END;//DELIMITER ;CALL DayRangeProc ('2010-09-01', '2010-09-10');SELECT * FROM tmptb; 从test1表与test2表，产⽣每个产品上架以来每天在每个平台的销售情况，如⽆销售数据则计销量为0 USE `test`;DROP TABLE IF EXISTS result;CREATE TABLE result (-- 保存结果数据id INT UNSIGNED NOT NULL AUTO_INCREMENT,date DATE NOT NULL,product_id VARCHAR(20) NOT NULL,shop VARCHAR(20) NOT NULL DEFAULT 0,price FLOAT NOT NULL,sales INT(8) DEFAULT 0,amount DOUBLE DEFAULT 0,PRIMARY KEY ( id ))ENGINE=InnoDB DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS shop_name_tb;CREATE TEMPORARY TABLE shop_name_tb AS (SELECT DISTINCT `在售平台` AS NAME FROM test2); -- 保存平台，类似于数组操作DELIMITER //DROP PROCEDURE IF EXISTS DayRangeProc//CREATE PROCEDURE DayRangeProc()BEGINDECLARE i, j, t INT;DECLARE range_day INT;DECLARE shop_num, prod_num INT;DECLARE start_date, end_date DATE;DECLARE prod_id, shop_name VARCHAR(20);DECLARE price FLOAT;SET i = 0;SET j = 0;SET t = 0;SET shop_num = (SELECT COUNT(*) FROM shop_name_tb);SET prod_num = (SELECT COUNT(*) FROM test1);SET end_date = (SELECT MAX(`时间`) FROM test2 ); -- 由于下架时间均为空，假设都在销-- 产品循环WHILE i <= prod_num DOSET prod_id = (SELECT `规格条码` FROM test1 LIMIT i,1); -- 第i个商品名称SET start_date = (SELECT `上架时间` FROM test1 WHERE `规格条码` = prod_id); -- 第i个商品的上架时间SET range_day = (SELECT DATEDIFF(end_date, start_date)); -- 第i个商品累计销售天数，以便插⼊相应长度的数据-- 平台循环WHILE j <= shop_num DOSET shop_name = (SELECT name FROM shop_name_tb LIMIT j,1); -- 店铺名称SET price = (SELECT `平台售价` FROM test1 WHERE `规格条码` = prod_id); -- 第i个商品售价，假设不同平台售价相同-- 时间循环WHILE t <= range_day DOINSERT INTO result(date, product_id, shop, price)VALUES (ADDDATE(start_date, t), prod_id, shop_name, price); -- sales, amountSET t = t + 1;END WHILE;SET j = j + 1;END WHILE;SET i = i + 1;END WHILE;END;//DELIMITER ;CALL DayRangeProc ();-- 查询数据SELECTresult.id,result.date,result.product_id,result.shop,result.price,IF(ISNULL(test2.`销量`), result.sales, test2.`销量`) AS sales,IF(ISNULL(test2.`销售额`), result.amount, test2.`销售额`) AS amountFROM result LEFT JOIN test2ON result.date = test2.`时间`AND result.shop = test2.`在售平台`AND result.product_id = test2.`规格条码`; 给定字符串，拆分后输出⼀列USE test;DROP TABLE IF EXISTS TEMP;CREATE TABLE TEMP (ID INT (8) NOT NULL AUTO_INCREMENT,number VARCHAR(20) NOT NULL,PRIMARY KEY (ID)) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '测试';DELIMITER $$DROP PROCEDURE IF EXISTS `Pr_Rand_insert`$$CREATE PROCEDURE `Pr_Rand_insert` ( IN insert_string VARCHAR (10000)) -- 定义输⼊BEGINDECLARE I INT (8) DEFAULT 1 ;DECLARE J INT (8) DEFAULT 0 ;SET J = CHAR_LENGTH(insert_string) - CHAR_LENGTH( REPLACE (insert_string, ',', '')) + 1; -- 计算共有多少位为",",则再加上1就表⽰共有多少个数值需要插⼊WHILE (I <= J) DOINSERT INTO TEMP(number) VALUES (SUBSTRING_INDEX(SUBSTRING_INDEX(insert_string, ',', I), ',', - 1)) ; -- ⽤到了substring_index()函数 SET I = I + 1 ;END WHILE ;-- SELECT CONCAT('共插⼊了', J, '个值,请确认');END$$DELIMITER ;CALL Pr_Rand_insert ('231,24,1114,151,7831241,9134,989');SELECT * FROM TEMP;。

mysql练习题50道1. 编写一个MySQL查询，用于查找表中的所有数据。

```SELECT * FROM 表名;```2. 编写一个MySQL查询，用于查找表中的所有数据，并按照某一列的升序排列。

```SELECT * FROM 表名 ORDER BY 列名 ASC;```3. 编写一个MySQL查询，用于查找表中的所有数据，并按照某一列的降序排列。

```SELECT * FROM 表名 ORDER BY 列名 DESC;```4. 编写一个MySQL查询，用于查找表中某一列的数据，并去除重复项。

```SELECT DISTINCT 列名 FROM 表名;```5. 编写一个MySQL查询，用于查找表中满足某个条件的数据。

```SELECT * FROM 表名 WHERE 条件;```6. 编写一个MySQL查询，用于查找表中满足多个条件的数据。

```SELECT * FROM 表名 WHERE 条件1 AND 条件2;```7. 编写一个MySQL查询，用于查找表中满足某个条件或另一个条件的数据。

```SELECT * FROM 表名 WHERE 条件1 OR 条件2;```8. 编写一个MySQL查询，用于计算表中某一列的总和。

```SELECT SUM(列名) FROM 表名;```9. 编写一个MySQL查询，用于计算表中某一列的平均值。

```SELECT AVG(列名) FROM 表名;```10. 编写一个MySQL查询，用于计算表中某一列的最大值。

```SELECT MAX(列名) FROM 表名;```11. 编写一个MySQL查询，用于计算表中某一列的最小值。

```SELECT MIN(列名) FROM 表名;```12. 编写一个MySQL查询，用于统计表中某一列的行数。

```SELECT COUNT(列名) FROM 表名;```13. 编写一个MySQL查询，用于在查询结果中限制返回的行数。

2021计算机二级考试MySQL数据库考试习题库（附答案）试题1习题一一、编程题在数据库db_test中创建一个存储过程，用于实现给定表content中一个留言人的姓名即可修改表content中该留言人的电子邮件地址为一个给定的值。

二、简答题1.请解释什么是存储过程?2.请列举使用存储过程的益处。

3.请简述游标在存储过程中的作用。

4.请简述存储过程与存储函数的区别。

习题答案：一、编程题在MySQL命令行客户端输入如下SQL语句即可实现：mysql>USE db_test;Database changedmysql>DELIMITER$$mysql>CREATE PROCEDURE sp_update email(IN USer_name VARCHAR(50)，IN e_mail VARCHAR(50))一>BEGIN一> UPDATE content SET email=e_mail WHERE username=user_name;一>END$$Query OK，0 rows affected(0.06 SeC)二、简答题1.存储过程是一组为了完成某特定功能的SQL语句集，其实质上就是一段存放在数据库中的代码，它可以由声明式的SQL 语句(如CREATE、UPDATE和SELECT等语句)和过程式SQL 语句(如IF-THEN-ELSE控制结构语句)组成。

2.略。

可参考第11章11.1节内容。

3.游标是一个被SELECT语句检索出来的结果集。

在存储了游标后，应用程序或用户就可以根据需要滚动或浏览其中的数据。

4.存储函数与存储过程之间存在这样几点区别：(1)存储函数不能拥有输出参数，这是因为存储函数自身就是输出参数;而存储过程可以拥有输出参数。

(2)可以直接对存储函数进行调用，且不需要使用CALL语句;而对存储过程的调用，需要使用CALL语句。

(3)存储函数中必须包含一条RETURN语句，而这条特殊的SQL语句不允许包含于存储过程中。

mysql存储过程拼接查询语句MySQL存储过程是一种在数据库中保存一组SQL语句的功能，可以使得这组SQL语句可以作为一个单元来执行。

在实际应用中，我们可以使用存储过程来拼接查询语句，以满足特定的查询需求。

以下是一些示例：1. 查询指定表中的所有记录：```mysqlCREATE PROCEDURE sp_select_all_records(IN tbl_name VARCHAR(50))BEGINSET @query = CONCAT('SELECT * FROM ', tbl_name);PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;END```2. 查询指定表中满足条件的记录：```mysqlCREATE PROCEDURE sp_select_records_with_condition(IN tbl_name VARCHAR(50), IN col_name VARCHAR(50), IN col_value VARCHAR(50))BEGINSET @query = CONCAT('SELECT * FROM ', tbl_name, ' WHERE ', col_name, ' = ?');PREPARE stmt FROM @query;EXECUTE stmt USING col_value;DEALLOCATE PREPARE stmt;END```3. 查询指定表中指定列的最大值：```mysqlCREATE PROCEDURE sp_select_max_value(IN tbl_name VARCHAR(50), IN col_name VARCHAR(50))BEGINSET @query = CONCAT('SELECT MAX(', col_name, ') FROM ', tbl_name);PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;END```4. 查询指定表中指定列的平均值：```mysqlCREATE PROCEDURE sp_select_avg_value(IN tbl_name VARCHAR(50), IN col_name VARCHAR(50))BEGINSET @query = CONCAT('SELECT AVG(', col_name, ') FROM ', tbl_name);PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;END```5. 查询指定表中指定列的总和：```mysqlCREATE PROCEDURE sp_select_sum_value(IN tbl_name VARCHAR(50), IN col_name VARCHAR(50))BEGINSET @query = CONCAT('SELECT SUM(', col_name, ') FROM ', tbl_name);PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;END```6. 查询指定表中指定列的记录数：```mysqlCREATE PROCEDURE sp_select_count_records(IN tbl_name VARCHAR(50), IN col_name VARCHAR(50))BEGINSET @query = CONCAT('SELECT COUNT(', col_name, ') FROM ', tbl_name);PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;END```7. 查询指定表中指定列的去重后的记录数：```mysqlCREATE PROCEDURE sp_select_distinct_count_records(IN tbl_name VARCHAR(50), IN col_name VARCHAR(50))BEGINSET @query = CONCAT('SELECT COUNT(DISTINCT ', col_name, ') FROM ', tbl_name);PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;END```8. 查询指定表中指定列的前N条记录：```mysqlCREATE PROCEDURE sp_select_top_records(IN tbl_name VARCHAR(50), IN col_name VARCHAR(50), IN N INT)BEGINSET @query = CONCAT('SELECT * FROM ', tbl_name, ' ORDER BY ', col_name, ' LIMIT ', N);PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;END```9. 查询指定表中指定列按照某个条件排序后的记录：```mysqlCREATE PROCEDURE sp_select_ordered_records(IN tbl_name VARCHAR(50), IN col_name VARCHAR(50), IN order_col VARCHAR(50))BEGINSET @query = CONCAT('SELECT * FROM ', tbl_name, ' ORDER BY ', col_name, ' ', order_col);PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;END```10. 查询指定表中指定列的最小值和最大值：```mysqlCREATE PROCEDURE sp_select_min_max_value(IN tbl_name VARCHAR(50), IN col_name VARCHAR(50))BEGINSET @query = CONCAT('SELECT MIN(', col_name, '), MAX(', col_name, ') FROM ', tbl_name);PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;END```以上是使用MySQL存储过程拼接查询语句的一些示例。

MySQL存储过程实例教程MySQL 5.0以后的版本开始支持存储过程，存储过程具有一致性、高效性、安全性和体系结构等特点，本节将通过具体的实例讲解PHP是如何操纵MySQL存储过程的。

1：存储过程的创建这是一个创建存储过程的实例实例说明为了保证数据的完整性、一致性，提高应用的性能，常采用存储过程技术。

MySQL 5.0之前的版本并不支持存储过程，随着MySQL技术的日趋完善，存储过程将在以后的项目中得到广泛的应用。

本实例将介绍在MySQL 5.0以后的版本中创建存储过程。

技术要点一个存储过程包括名字、参数列表，以及可以包括很多SQL语句的SQL语句集。

下面为一个存储过程的定义过程： create procedure proc_name (in parameterinteger)begindeclare variable varchar(20);if parameter=1 thensetvariable='MySQL';elseset variable='PHP';end if;insert into tb (name) values (variable);end;MySQL中存储过程的建立以关键字create procedure开始，后面紧跟存储过程的名称和参数。

MySQL的存储过程名称不区分大小写，例如PROCE1()和proce1()代表同一个存储过程名。

存储过程名不能与MySQL数据库中的内建函数重名。

存储过程的参数一般由3部分组成。

第一部分可以是in、out或inout。

in表示向存储过程中传入参数；out表示向外传出参数；inout表示定义的参数可传入存储过程，并可以被存储过程修改后传出存储过程，存储过程默认为传入参数，所以参数in可以省略。

第二部分为参数名。

第三部分为参数的类型，该类型为MySQL数据库中所有可用的字段类型，如果有多个参数，参数之间可以用逗号进行分割。

第十章一、填空题1.92. e3.@，变量名4.REPEAT5.DELIMITER二、判断题1.错2.错3.对4.对5.对三、选择题1. A2. D3. D4. C5. B四、简答题1.请简述存储过程和函数的区别。

答：（1）语法中实现的标识符不同，存储过程使用PROCEDURE，函数为FUNCTION。

（2）存储过程在创建时没有返回值，而函数在定义时必须设置返回值。

（3）存储过程没有返回值类型，且不能将结果直接赋值给变量；而函数定义时需要设置返回值类型，且在调用时除在SELECT中，必须将返回值赋给变量。

（4）存储过程必须通过CALL进行调用，不能使用SELECT调用；而函数则可在SELECT语句中直接使用。

2.请说一说触发器以及其作用。

答：概念：触发器可以看作是一种特殊类型的存储过程，在预先定义好的事件（如INSERT、DELETE等操作）发生时，才会被MySQL自动调用。

作用：①触发器可以通过数据库中的相关表实现级联无痕更改操作。

②保证数据安全，进行安全校验五、实训题11. 请在shop数据库中创建一个存储过程，以订单编号为参数，输出该订单的商品信息。

mysql> DELIMITER $$mysql> CREATE PROCEDURE shop.order_proc(IN order_id INT )-> BEGIN-> SELECT g.id, FROM sh_goods g-> LEFT JOIN sh_order_goods og ON g.id = og.goods_id-> WHERE og.order_id = order_id;-> END-> $$Query OK, 0 rows affected (0.01 sec)mysql> DELIMITER ;2. shop.sh_order_goods表上创建一个触发器，当添加订单-商品信息时，修改sh_goods表中对应商品的库存量。

MySQL中的存储过程调试技巧MySQL作为一个流行的关系型数据库管理系统，经常被用于开发和管理各种类型的应用程序。

在开发过程中，存储过程是一种常见的数据库对象，它可以存储一系列的SQL语句，并可以通过调用来执行这些语句。

然而，存储过程的调试对于开发人员来说可能是一项具有挑战性的任务。

在本文中，我们将探讨一些MySQL中的存储过程调试技巧，帮助开发人员更加高效地解决问题。

一、使用调试语句MySQL提供了一些内置的调试语句，可以用于在存储过程中输出变量值或执行过程中的信息。

其中，最常用的是`SELECT`语句和`PRINT`语句。

1. 使用`SELECT`语句在存储过程中使用`SELECT`语句，可以在执行过程中输出变量的值。

通过在关键位置插入`SELECT`语句，开发人员可以查看各个变量的值，并判断程序是否按预期进行。

例如，下面的代码段演示了如何使用`SELECT`语句输出变量的值：```sqlDECLARE @var INT;SET @var = 10;SELECT @var;```2. 使用`PRINT`语句与`SELECT`语句不同，`PRINT`语句在存储过程中不会产生结果集，而是直接将文本输出到客户端。

通过在关键位置插入`PRINT`语句，开发人员可以输出一些执行过程中的信息，以便调试。

例如，下面的代码段演示了如何使用`PRINT`语句输出信息：```sqlDECLARE @var INT;SET @var = 10;PRINT 'The value of @var is: ' + CAST(@var AS VARCHAR);```二、使用条件语句和循环除了使用调试语句，开发人员还可以使用条件语句和循环来控制程序的执行流程，以便在特定条件下进行调试。

以下是一些常用的条件语句和循环语句。

1. 使用`IF`语句`IF`语句是一种常见的条件语句，在存储过程中可以根据特定条件执行不同的操作。

mysql 存储过程写法MySQL 存储过程的写法如下：1. 创建存储过程```CREATE PROCEDURE procedure_name (in parameter_type parameter_name, out parameter_type parameter_name, inout parameter_type parameter_name)BEGIN-- 存储过程体END;```其中，`parameter_type` 表示参数类型，`parameter_name` 表示参数名称。

`in` 表示输入参数，`out` 表示输出参数，`inout` 表示传入传出参数。

2. 编写存储过程体存储过程体是存储过程的主体部分，通过 SQL 语句实现所需的功能。

```BEGIN-- 声明变量DECLARE variable_name variable_type;-- 赋值SET variable_name = value;-- 条件判断IF condition THEN-- SQL 语句ELSE-- SQL 语句END IF;-- 循环语句WHILE condition DO-- SQL 语句END WHILE;-- 返回结果SELECT column_name INTO variable_name FROM table_name WHERE condition;END;```3. 调用存储过程调用存储过程，使用 `CALL` 关键字和存储过程名称。

```CALL procedure_name(parameter);```其中，`parameter` 是存储过程的参数，可根据需要传入相应的值。

存储过程、触发器练习1、在学生选课数据库中，创建一存储过程deptmale，查询指定系的男生人数，其中系为输入参数，人数为输出参数。

create proc p_dept@dept char(20),@mannum int outputasselect @allcre=count(sno) from studentwhere sdept=@dept and ssex='男'declare @num intexec p_dept '计算机系',@num outputprint @num2、在s_c数据库中，创建一个存储过程totalcredit,根据输入的学生姓名，计算其总学分。

(使用输出参数)。

并执行该存储过程。

create proc p_cou@name char(10),@allcre int outputasselect @allcre=sum(ccredit)from student,course,scwhere student.sno=sc.sno and o=oand sname=@name group by sc.snodeclare @asum intexec p_cou '刘晨',@asum outputprint @asum3、创建一更新触发器upd_grade，设置sc表的grade字段不能被更新，并显示信息“学生成绩不能被修改，请与教务处联系”。

CREATE TRIGGER mes_scON scFOR UPDATEASIF UPDATE（grade）BEGINROLLBACK TRANPRINT '学生成绩不能被修改，请与教务处联系'END4、创建一个insert触发器uninsertstu，当在student表中插入一条新纪录时，如果是“计算机系”的学生，则撤销该插入操作，并返回“此系人数已满，不能再添加”信息。

一、编程题
创建数据库为: test_db
数据库中有以下表：
班级表(clazz)
学生表(student)
课程表(course)
选课表(selection)
操作题
1、创建一个视图，视图实现的是查询每个学生的年龄，性别。

2、创建一个视图，视图实现的是查询每个学生的姓名，所在的班级以及班
主任的姓名。

3、创建测试用户test,密码root。

并分配mysql数据库中的所有权限。

4、创建一个存储过程，用于查询课程表中所有信息。

并调用该存储过程。

5、创建一个存储过程，用于查询课程表中学分绩点在某个范围内的课程名
称，课程简介。

6、统计课程表中的课程数量，如果小于6插入两条课程信息，否则删除id
大于6课程信息。

7、创建存储过程，使用游标循环获取班级表中第3结果的班级名称，任
课老师的信息该存储过程。

8、创建存储过程，使用游标循环获取班级表中所有结果的班级名称。

并调用该存储过程。

（使用repeat循环方式）
9、创建存储过程，使用游标循环获取学生表中所有结果的学生姓名和年龄。

并调用该存储过程。

（使用while循环方式）。

超经典MySQL练习50题，做完这些你的SQL就过关了出品：Python数据之道作者：Peter编辑：Lemon相信⼤多学习了 Mysql 数据库语⾔的同学都会上⽹找练习来练⼿，⽽⼤部分的⼈肯定知道有⼀篇 Mysql 经典练习题50题的帖⼦，上⾯的题⽬基本上涵盖了 Mysql 查询语句的关键知识点。

笔者近期对⼜将这 50 题进⾏了练习，同时整理了相关的参考答案，有⾃⼰的思路和⽅法，也有参考⼤神们的。

不得不说，这50题对SQL的提升真的很有帮助！笔者使⽤的 MySQL 版本是MySQL 5.7.28。

在⽂末提供获鉴于 50 题篇幅太长，本⽂只展⽰了其中10题及笔者的思考，50 题完整版练习题以及笔者的答案实践已整理在pdf⽂件中，共有100多页，在⽂末提供获取的⽅法。

取的⽅法建表和插⼊数据在开始之前，先建⽴本⽂所需要的数据表格：-- 建表-- 学⽣表CREATE TABLE `Student`(`s_id` VARCHAR(20),`s_name` VARCHAR(20) NOT NULL DEFAULT '',`s_birth` VARCHAR(20) NOT NULL DEFAULT '',`s_sex` VARCHAR(10) NOT NULL DEFAULT '',PRIMARY KEY(`s_id`));-- 课程表CREATE TABLE `Course`(`c_id` VARCHAR(20),`c_name` VARCHAR(20) NOT NULL DEFAULT '',`t_id` VARCHAR(20) NOT NULL,PRIMARY KEY(`c_id`));-- 教师表CREATE TABLE `Teacher`(`t_id` VARCHAR(20),`t_name` VARCHAR(20) NOT NULL DEFAULT '',PRIMARY KEY(`t_id`));-- 成绩表CREATE TABLE `Score`(`s_id` VARCHAR(20),`c_id` VARCHAR(20),`s_score` INT(3),PRIMARY KEY(`s_id`,`c_id`));-- 插⼊学⽣表测试数据insert into Student values('01' , '赵雷' , '1990-01-01' , '男');insert into Student values('02' , '钱电' , '1990-12-21' , '男');insert into Student values('03' , '孙风' , '1990-05-20' , '男');insert into Student values('04' , '李云' , '1990-08-06' , '男');insert into Student values('05' , '周梅' , '1991-12-01' , '⼥');insert into Student values('06' , '吴兰' , '1992-03-01' , '⼥');insert into Student values('07' , '郑⽵' , '1989-07-01' , '⼥');insert into Student values('08' , '王菊' , '1990-01-20' , '⼥');-- 课程表测试数据insert into Course values('01' , '语⽂' , '02');insert into Course values('02' , '数学' , '01');insert into Course values('03' , '英语' , '03');-- 教师表测试数据insert into Teacher values('01' , '张三');insert into Teacher values('02' , '李四');insert into Teacher values('03' , '王五');-- 成绩表测试数据insert into Score values('01' , '01' , 80);insert into Score values('01' , '02' , 90);insert into Score values('01' , '03' , 99);insert into Score values('02' , '01' , 70);insert into Score values('02' , '02' , 60);insert into Score values('02' , '03' , 80);insert into Score values('03' , '01' , 80);insert into Score values('03' , '02' , 80);insert into Score values('03' , '03' , 80);insert into Score values('04' , '01' , 50);insert into Score values('04' , '02' , 30);insert into Score values('04' , '03' , 20);insert into Score values('05' , '01' , 76);insert into Score values('05' , '02' , 87);insert into Score values('06' , '01' , 31);insert into Score values('06' , '03' , 34);insert into Score values('07' , '02' , 89);insert into Score values('07' , '03' , 98);题⽬1题⽬要求成绩⾼的学⽣的信息及课程分数查询'01'课程⽐'02'课程成绩⾼SQL实现-- ⽅法1selecta.*,b.s_score as 1_score,c.s_score as 2_scorefrom Student ajoin Score b on a.s_id = b.s_id and b.c_id = '01' -- 两个表通过学号连接，指定01left join Score c on a.s_id = c.s_id and c.c_id='02' or c.c_id is NULL -- 指定02，或者c中的c_id直接不存在-- 为NULL的条件可以不存在，因为左连接中会直接排除c表中不存在的数据，包含NULLwhere b.s_score > c.s_score; -- 判断条件-- ⽅法2：直接使⽤where语句selecta.*,b.s_score as 1_score,c.s_score as 2_scorefrom Student a, Score b, Score cwhere a.s_id=b.s_id -- 列出全部的条件and a.s_id=c.s_idand b.c_id='01'and c.c_id='02'and b.s_score > c.s_score; -- 前者成绩⾼第⼆种⽅法实现：第⼆种⽅法实现题⽬2题⽬要求查询'01'课程⽐'02'课程成绩低成绩低的学⽣的信息及课程分数（题⽬1是成绩⾼）SQL实现类⽐题⽬1的实现过程-- ⽅法1：通过连接⽅式实现selecta.*,b.s_score as 1_score,c.s_score as 2_scorefrom Student aleft join Score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL -- 包含NULL的数据join score c on a.s_id=c.s_id and c.c_id='02'where b.s_score < c.s_score;-- 通过where⼦句实现selecta.*,b.s_score as 1_score,c.s_score as 2_scorefrom Student a, Score b, Score cwhere a.s_id=b.s_idand a.s_id=c.s_idand a.s_id=c.s_idand b.c_id='01'and c.c_id='02'and b.s_score < c.s_score; -- 前者⽐较⼩题⽬3题⽬需求查询平均成绩⼤于等于60分的同学的学⽣编号和学⽣姓名和平均成绩SQL实现-- 执⾏顺序：先执⾏分组，再执⾏avg平均操作selectb.s_id,b.s_name,round(avg(a.s_score), 2) as avg_scorefrom Student bjoin Score aon b.s_id = a.s_idgroup by b.s_id -- 分组之后查询每个⼈的平均成绩having avg_score >= 60;-- 附加题：总分超过200分的同学selectb.s_id,b.s_name,round(sum(a.s_score),2) as sum_score -- sum求和from Student bjoin Score aon b.s_id=a.s_idgroup by b.s_idhaving sum_score > 200;附加题：总分超过200分的同学题⽬4题⽬要求查询平均成绩⼩于60分的同学的学⽣编号和学⽣姓名和平均成绩（包括有成绩的和⽆成绩的）SQL实现1-两种情况连接平均分⼩于60selectb.s_id,b.s_name,round(avg(a.s_score), 2) as avg_score -- round四舍五⼊函数from Student bjoin Score aon b.s_id = a.s_idgroup by b.s_id -- 分组之后查询每个⼈的平均成绩having avg_score < 60;结果为：没有成绩的同学：selecta.s_id,a.s_name,a.s_name,0 as avg_scorefrom Student awhere a.s_id not in ( -- 学⽣的学号不在给给定表的学号中 select distinct s_id -- 查询出全部的学号from Score);最后将两个部分的结果连起来即可：通过union⽅法SQL实现2-ifnull函数判断使⽤ifnull函数selectS.s_id,S.s_name,round(avg(ifnull(C.s_score,0)), 2) as avg_score -- ifnull 函数：第⼀个参数存在则取它本⾝，不存在取第⼆个值0 from Student Sleft join Score Con S.s_id = C.s_idgroup by s_idhaving avg_score < 60;使⽤null判断selecta.s_id,a.s_name,ROUND(AVG(b.s_score), 2) as avg_scorefrom Student aleft join Score b on a.s_id = b.s_idGROUP BY a.s_idHAVING avg_score < 60 or avg_score is null; -- 最后的NULL判断题⽬5题⽬需求查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩SQL实现selecta.s_id,a.s_name,count(b.c_id) as course_number -- 课程个数,sum(b.s_score) as scores_sum -- 成绩总和from Student aleft join Score bon a.s_id = b.s_idgroup by a.s_id,a.s_name;题⽬6题⽬需求查询“李”姓⽼师的数量SQL实现select count(t_name) from Teacher where t_name like '李%'; -- 通配符这题怕是最简单的吧题⽬7题⽬需求查询学过张三⽼师张三⽼师授课的同学的信息SQL实现-- ⽅法1：通过张三⽼师的课程的学⽣来查找；⾃⼰的⽅法select * -- 3. 通过学号找出全部学⽣信息from Studentwhere s_id in (select s_id -- 2.通过课程找出对应的学号from Score Sjoin Course Con S.c_id = C.c_id -- 课程表和成绩表where C.t_id=(select t_id from Teacher where t_name='张三') -- 1.查询张三⽼师的课程);-- ⽅法2：通过张三⽼师的课程来查询select s1.*from Student s1join Score s2on s1.s_id=s2.s_idwhere s2.c_id in (select c_id from Course c where t_id=( -- 1. 通过⽼师找出其对应的课程select t_id from Teacher t where t_name='张三'))-- ⽅法3select s.* from Teacher tleft join Course c on t.t_id=c.t_id -- 教师表和课程表left join Score sc on c.c_id=sc.c_id -- 课程表和成绩表left join Student s on s.s_id=sc.s_id -- 成绩表和学⽣信息表where t.t_name='张三';⾃⼰的⽅法：⽅法2来实现：⽅法3实现：题⽬8题⽬需求找出没有学过张三⽼师课程的学⽣SQL实现select * -- 3. 通过学号找出全部学⽣信息from Studentwhere s_id not in ( -- 2.通过学号取反：学号不在张三⽼师授课的学⽣的学号中select s_idfrom Score Sjoin Course Con S.c_id = C.c_idwhere C.t_id=(select t_id from Teacher where t_name ='张三') -- 1.查询张三⽼师的课程);-- ⽅法2：select *where s1.s_id not in (select s2.s_id from Student s2 join Score s3 on s2.s_id=s3.s_id where s3.c_id in( select c.c_id from Course c join Teacher t on c.t_id=t.t_id where t_name='张三' ));-- ⽅法3select s1.*from Student s1join Score s2on s1.s_id=s2.s_idwhere s2.c_id not in (select c_id from Course c where t_id=( -- 1. 通过⽼师找出其对应的课程select t_id from Teacher t where t_name='张三'));⽅法2：题⽬9题⽬需求查询学过编号为01，并且学过并且学过编号为02课程的学⽣信息SQL实现-- ⾃⼰的⽅法：通过⾃连接实现select s1.*from Student s1where s_id in (select s2.s_id from Score s2join Score s3on s2.s_id=s3.s_idwhere s2.c_id='01' and s3.c_id='02');-- ⽅法2：直接通过where语句实现select s1.*from Student s1, Score s2, Score s3where s1.s_id=s2.s_idand s1.s_id=s3.s_idand s2.c_id=01 and s3.c_id=02;-- ⽅法3：两个⼦查询select sc1.s_idfrom (select * from Score s1 where s1.c_id='01') sc1,(select * from Score s1 where s1.c_id='02') sc2where sc1.s_id=sc2.s_id;-- 2.找出学⽣信息select *from Studentwhere s_id in (select sc1.s_id -- 指定学号是符合要求的from (select * from Score s1 where s1.c_id='01') sc1,(select * from Score s1 where s1.c_id='02') sc2where sc1.s_id=sc2.s_id);1. 先从Score表中看看哪些⼈是满⾜要求的：01-05同学是满⾜的通过⾃连接查询的语句如下：查询出学号后再匹配出学⽣信息：通过where语句实现：⽅法3的实现：题⽬10题⽬需求但是没有学过02课程的学⽣信息（注意和上⾯ 题⽬的区别）查询学过01课程，但是没有学过SQL实现⾸先看看哪些同学是满⾜要求的：只有06号同学是满⾜的错误思路1直接将上⾯⼀题的结果全部排出，导致那些没有学过01课程的学⽣也出现了：07，08select s1.*from Student s1where s_id not in ( -- 直接将上⾯⼀题的结果全部排出，导致那些没有学过01课程的学⽣也出现了：07，08select s2.s_id from Score s2join Score s3on s2.s_id=s3.s_idwhere s2.c_id='01' and s3.c_id ='02');错误思路2将上⾯题⽬中的02课程直接取反，导致同时修过01，02，03或者只修01，03的同学也会出现select s1.*from Student s1where s_id in (select s2.s_id from Score s2join Score s3on s2.s_id=s3.s_idwhere s2.c_id='01' and s3.c_id !='02' -- 直接取反是不⾏的，因为修改(01,02,03)的同学也会出现);正确思路https:///p/9abffdd334fa-- ⽅法1：根据两种修课情况来判断select s1.*from Student s1where s1.s_id in (select s_id from Score where c_id='01') -- 修过01课程，要保留and s1.s_id not in (select s_id from Score where c_id='02'); -- 哪些⼈修过02，需要排除！！！！！⽅法2：先把06号学⽣找出来select * from Student where s_id in (select s_idfrom Scorewhere c_id='01' -- 修过01课程的学号and s_id not in (select s_id -- 同时学号不能在修过02课程中出现 from Scorewhere c_id='02'));鉴于篇幅，本⽂只展⽰了50题中的10道题的答案以及笔者的实践⼼得。

Mysql使⽤存储过程，多表多条数据插⼊，异常回滚插⼊多条数据，使⽤字符串在后台拼接的⽅式，传⼊存储过程，再到存储过程⾥⾯分割字符串，这样就取得了每个元素添加事务，异常回滚CREATE DEFINER=`root`@`localhost` PROCEDURE `creat_student_group`(IN `group_name` varchar(128),IN `student_id_card` varchar(900))BEGIN#Routine body goes here...DECLARE group_id_now INT DEFAULT0;DECLARE i INT DEFAULT0;DECLARE result_code INTEGER DEFAULT0; -- 定义返回结果并赋初值0DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code=1; -- 在执⾏过程中出任何异常设置result_code为1DECLARE CONTINUE HANDLER FOR NOT FOUND SET result_code =2; -- 如果表中没有下⼀条数据则置为2START TRANSACTION; -- 开始事务INSERT INTO group_info (group_name) VALUES(group_name);SET group_id_now =@@IDENTITY ;SET@arraylength=1+(LENGTH(student_id_card) - LENGTH(REPLACE(student_id_card,',','')));WHILE i<@arraylengthDOSET i=i+1;SET@result=REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(student_id_card,',',i)),',',1));INSERT INTO group_list(student_info_id,group_info_id) VALUES(@result,group_id_now);END WHILE;IF result_code =1THEN-- 可以根据不同的业务逻辑错误返回不同的result_code，这⾥只定义了1和0ROLLBACK;ELSECOMMIT;END IF;select result_code;END。

mysql测试题MySQL是一种广泛使用的关系型数据库管理系统，被许多企业和开发者用来存储和管理大量的数据。

为了评估一个人的MySQL技能，下面给出了一些MySQL测试题，供参考和练习。

一、基础题1. 创建一个名为"students"的数据库，并设置字符集为UTF-8。

2. 在"students"数据库下创建一个名为"courses"的表，包含以下字段：- id: 主键，自增长的整数类型- name: 学生姓名，字符串类型- age: 学生年龄，整数类型- grade: 学生成绩，浮点数类型3. 在"courses"表中插入至少5条学生记录。

4. 查询"courses"表中年龄大于20岁的学生记录。

5. 查询"courses"表中每个学生的平均成绩，并按照平均成绩降序排列。

二、进阶题1. 创建一个名为"orders"的表，包含以下字段：- id: 主键，自增长的整数类型- order_date: 下单日期，日期类型- total_amount: 订单总金额，浮点数类型- customer_id: 客户ID，整数类型2. 使用一条SQL语句插入至少3条订单记录，其中包括订单日期、总金额和客户ID。

3. 查询"orders"表中每个客户的订单数量，并按照订单数量降序排列。

4. 更新"orders"表中客户ID为1的订单总金额为1000。

5. 删除"orders"表中总金额小于100的订单记录。

三、高级题1. 创建一个名为"departments"的表，包含以下字段：- id: 主键，自增长的整数类型- name: 部门名称，字符串类型2. 在"departments"表中插入至少3个部门记录。

实验三：创建视图、存储过程和实现数据安全_mysql实验三：完整性约束、视图、存储过程和数据安全[⽬的和意义]掌握创建各种完整性约束的⽅法；掌握创建和使⽤视图、存储过程和索引的⽅法；掌握实现数据安全的基本⽅法；掌握数据库的基本备份和恢复技术。

[实验内容]本实验在mysql数据库平台上进⾏操作，实验中的各种数据都来⾃于实验⼆中的各个表。

实验主要内容如下：1．学习创建完整性约束的⽅法。

2．学习创建视图、存储过程和索引的⽅法。

3．学习利⽤⽤户管理和视图实现数据安全。

4．学习mysql数据库的基本备份和恢复技术。

[实验步骤]1 完整性约束1.1 实体完整性约束主键⼀定是⾮空的，⽽且具有唯⼀性，即不能重复。

能够唯⼀标识实体，保证实体完整性。

虽然，上⾯已经成功创建了主键，但是，我们还是要学习⼀下主键的写法。

第⼀种：在建表的时候就加上主键：还是以上⾯course表的创建为例：CREATE TABLE course (cno V ARCHAR(10) NOT NULL,dno CHAR(8) NOT NULL,cname V ARCHAR(20) NOT NULL,hours INTEGER NOT NULL,term INTEGER NOT NULL,credit INTEGER NOT NULL,CONSTRAINT pk_course PRIMARY KEY (cno));其中最后⼀句话就是将cno设为主键。

如果主键包含多个字段，可以在括号内添加所包含字段名，如取cno和dno为主键，则语句为：CONSTRAINTpk_course PRIMARY KEY(cno,dno)。

第⼆种：若建表的时候没有将cno设为主键，表建⽴成功以后，可以加上主键约束：ALTER TABLE course ADD CONSTRAINT pk_course PRIMARY KEY(cno) ；1.2 参照完整性约束建⽴外键：例如，在course表中建⽴和department表之间的外键，语句如下：ALTER TABLE courseADD CONSTRAINT fk_course_departmen_departme FOREIGN KEY (dno) REFERENCES department (dno);在student表中建⽴和department表之间的外键，语句如下：ALTER TABLE studentADD CONSTRAINT fk_student_departmen_departme FOREIGN KEY (dno) REFERENCES department (dno);1.3 业务规则约束有时候，不同系统会有⼀些特定的业务规则；例如这⾥，要求在删除teacher表之前⼀定要先删除teacher_course与student_course_teacher中的相关信息，这⾥，我们⽤触发器实现⼀下：DELIMITER //#修改分隔符CREATE TRIGGER tri_del_teacherAFTER DELETE ON teacherFOR EACH ROWBEGINDELETE FROM teacher_courseWHERE tno=old.tno;DELETE FROM student_course_teacherWHERE tno=old.tno;END //DELIMITER ; #修改回原分隔符建⽴成功以后，如果我们在teacher表中删除⽼师编号为'2004-213'的⽼师的信息，那么teacher_course和student_course_teacher两表中包含此⽼师信息的相关数据都会删除掉（慎⽤！）。

MySQL练习题⼀、练习数据1、数据表学⽣表 Student(SId,Sname,Sage,Ssex)SId 学⽣编号,Sname 学⽣姓名,Sage 出⽣年⽉,Ssex 学⽣性别课程表 Course(CId,Cname,TId)CId --课程编号,Cname 课程名称,TId 教师编号教师表 Teacher(TId,Tname)TId 教师编号,Tname 教师姓名成绩表 SC(SId,CId,score)SId 学⽣编号,CId 课程编号,score 分数2、创建测试数据学⽣表 Studentcreate table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));insert into Student values('01' , '赵雷' , '1990-01-01' , '男');insert into Student values('02' , '钱电' , '1990-12-21' , '男');insert into Student values('03' , '孙风' , '1990-05-20' , '男');insert into Student values('04' , '李云' , '1990-08-06' , '男');insert into Student values('05' , '周梅' , '1991-12-01' , '⼥');insert into Student values('06' , '吴兰' , '1992-03-01' , '⼥');insert into Student values('07' , '郑⽵' , '1989-07-01' , '⼥');insert into Student values('09' , '张三' , '2017-12-20' , '⼥');insert into Student values('10' , '李四' , '2017-12-25' , '⼥');insert into Student values('11' , '李四' , '2017-12-30' , '⼥');insert into Student values('12' , '赵六' , '2017-01-01' , '⼥');insert into Student values('13' , '孙七' , '2018-01-01' , '⼥');科⽬表 Coursecreate table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10))insert into Course values('01' , '语⽂' , '02')insert into Course values('02' , '数学' , '01')insert into Course values('03' , '英语' , '03')教师表 Teachercreate table Teacher(TId varchar(10),Tname varchar(10))insert into Teacher values('01' , '张三')insert into Teacher values('02' , '李四')insert into Teacher values('03' , '王五')成绩表 SCcreate table SC(SId varchar(10),CId varchar(10),score decimal(18,1))insert into SC values('01' , '01' , 80)insert into SC values('01' , '02' , 90)insert into SC values('01' , '03' , 99)insert into SC values('02' , '01' , 70)insert into SC values('02' , '02' , 60)insert into SC values('02' , '03' , 80)insert into SC values('03' , '01' , 80)insert into SC values('03' , '02' , 80)insert into SC values('03' , '03' , 80)insert into SC values('04' , '01' , 50)insert into SC values('04' , '02' , 30)insert into SC values('04' , '03' , 20)insert into SC values('05' , '01' , 76)insert into SC values('05' , '02' , 87)insert into SC values('06' , '01' , 31)insert into SC values('06' , '03' , 34)insert into SC values('07' , '02' , 89)insert into SC values('07' , '03' , 98)注意：使⽤的Mysql版本是5.7.19，答案可能会因版本会有少许出⼊。