实验四SQL数据查询语言DQL

1、数据库实验报告姓名：廖小均班级：0141202学号：2012210153学院：通信与信息工程学院实验四SQL数据查询语言DQL一实验目的： 数据查询语言指对数据库中的数据查询、统计、分组、排序等操作。查询语句可以分为简单查询、连接查询、嵌套查询和组合查询等。本次实验了解SQL语言的SELECT语句对数据的查询，学会在Navicat for MySQL中用SELECT语句对表中的数据进行简单查询、连接查询、嵌套查询和组合查询。二仪器设备及用具：硬件：投影仪、每位同学分配已连接校园网PC机一台。软件：本机已安装MySQL 5.5三实验步骤及代码：1.启动Navicat for MySQL，在MyS

2、QL 新建连接中完成连接参数配置。2.登录到本地数据库服务器后，连接到test数据库上。3.用Create Table建立Student表create table student( SNO varchar(20), Name varchar (10), Age integer,College varchar(30)4.用Create Table建立Course表create table Course(CourseID VARCHAR(15), CourseName varchar(30), CourseBeforeID VARCHAR(15)5.用Create Table建立Choose表CR

3、EATE TABLE Choose(SNO VARCHAR(20), CourseID varchar(30), Score DECIMAL (5,2)6.用INSERT语句向Student表中插入3个元组：注意：每条元组一个完整的INSERT语句，3个元组需要写3个INSERT语句insert into student(SNO,Name,Age,College)values (S00001,张三,20,计算机学院);insert into student(SNO,Name,Age,College)values (S00002,李四,19,通信学院);insert into student(S

4、NO,Name,Age,College)values (S00003,王五,21,计算机学院);7.用INSERT语句向Course表中插入3个元组：注意：每条元组一个完整的INSERT语句，3个元组需要写3个INSERT语句。insert into Course(CourseID,CourseName,CourseBeforeID)VALUES(c1,计算机导论 ,NULL);insert into Course(CourseID,CourseName,CourseBeforeID)VALUES(c2,c语言,c1);insert into Course(CourseID,CourseNam

5、e,CourseBeforeID)VALUES(c3,数据结构,c2);8.用INSERT语句向Choose表中插入7个元组：注意：每条元组一个完整的INSERT语句，7个元组需要写7个INSERT语句。insert into Choose(SNO,CourseID,Score) values (S00001,c1,95);insert into Choose(SNO,CourseID,Score) values (S00001,c2,80);insert into Choose(SNO,CourseID,Score) values (S00001,c3,84);insert into Cho

6、ose(SNO,CourseID,Score) values (S00002,c1,80);insert into Choose(SNO,CourseID,Score) values (S00002,c2,85);insert into Choose(SNO,CourseID,Score) values (S00003,c1,78);insert into Choose(SNO,CourseID,Score) values (S00003,c3,70);9.用SELECT语句，查询计算机学院学生的学号和姓名。select SNO,Name from student where College=

7、计算机学院;10.用SELECT语句的betweenand表示查询条件，查询年龄在2023岁的学生信息。select*from student where Age between 20 and 23;11.用SELECT语句的COUNT()聚集函数，统计Student表中学生总人数。 select count(*) from student;12.分别用SELECT语句的max()、min()、sum()、avg()四个聚集函数，计算Choose表中C1课程的最高分、最低分、总分、平均分。select max(Score)from choose where CourseID=C1;select

8、 min(Score)from choose where CourseID=C1;select sum(Score)from choose where CourseID=C1;select avg(Score)from choose where CourseID=C1; 13. 用SELECT语句对空值(NULL)的判断条件，查询Course表中先修课称编号为空值的课程编号和课程名称。select CourseID,CourseName from Course where CourseBeforeID=NULL; 14. 用SELECT语句的连接查询，查询学生的学号、姓名、选修的课程名及成绩。

9、SELECT student.Sno,student.Name,choose.CourseID,choose.Score from student,choose WHERE student.Sno=choose.Sno15. 用SELECT的存在量词EXISTS，查询与“张三”在同一个学院学习的学生信息。select sno,name,college from student AWhere exists(select college from student B Where B.name=张三and a.college and !=张三）；16. 用SELECT语句的嵌套查询，查询

10、选修C1课程的成绩低于“张三”的学生的学号和成绩。代码：SELECT Sno,Score FROM choose where choose.CourseID=C1 AND choose.Score(SELECT Score from student,choose WHERE student.Sno=choose.Sno and student.Name=张三 and choose.CourseID=C1)结 果：17.用SELECT语句的组合查询(UNION)，查询选修了C1课程或者选修了C3课程的学生学号。代码：SELECT Sno,CourseID FROM choose WHERE Co

11、urseID=C1 UNION SELECT Sno,CourseID from choose where CourseID=C3结果：18.用SELECT语句的组合查询(UNION)与DISTINCT短语，查询选修了C1课程或者选修了C3课程的学生学号，并取消重复数据。代码：SELECT DISTINCT Sno FROM choose WHERE CourseID=C1 UNION SELECT DISTINCT Sno from choose where CourseID=C3结果：四实验总结：1、 Where与Having语句有何差别？答：区别在于作用对象不同。Where子句基本表或者

12、视图，从中选择满足条件的元组。Having短语作用于组，从中选择满足条件的组。2、 Count（*）与Count（字段名）有何差别？答：Count（*）统计元组个数，Count（字段名）统计一列中的数值3、 LIKE sys%与NOT LIKE sys%有无区别？请加以验证？答：有区别4、 全连接、左连接、右连接有什么不同？答：若全连接则左右表中的所有记录都会被选出来若左连接则选出左边表的所有记录和右边表中没有和它匹配的纪录若右连接则选出右边表的所有记录和左边表中没有和他匹配的值5、 IN与Exists语句有何区别？答：6、 不相关子查询、相关子查询有何区别？答：不相关子查询的条件不依赖与父查

13、寻而相关子查询的条件依赖于父查寻。 求解相关子查询不像求解不相关子查询那样，一次将子查询求解出来，然后去求解父查寻。内层查询与外层查询有关，因此必须反复求值7、 UNION与UNION ALL有何差别，谁的性能高？答：UNION和UNION ALL的区别在于UNION将多个查询的结果合并起来时，系统将会自动去掉重复的元组而union all则保留重复的元组。UNION ALL的性能更高，因为UNION要删除重复的元组。实验六MySql的存储过程 一、实验目的存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。存储过程分为两类：1.系统提供的存储过程；2.用户自定义存储过程。存

14、储过程具有的优点：1.存储过程允许标准组件式编程；2.存储过程能够实现较快的执行速度；3.存储过程能够减少网络流量；4.存储过程可被作为一种安全机制来充分利用。存储过程是可复用的组件！想象一下如果你改变了主机的语言，这对存储过程不会产生影响，因为它是数据库逻辑而不是应用程序。存储过程是可以移植的！ 本次实验了解MySQL存储过程的创建、修改和删除的方法和步骤，掌握在Navicat for MySQL中对存储过程的进行创建、修改和删除，掌握在MySQL Command Line Client中调用带参数和不带参数的存储过程。二、实验要求1、实验前：预习实验内容，学习相关知识。2、实验中：按照实验

15、内容要求进行实验，实验时注意每种SQL语句的基本命令及各个关键字的含义，做好实验记录。3、实验后：分析实验结果，总结实验知识，得出结论，按格式写出实验报告。4、在整个实验过程中，要独立思考、独立按时完成实验任务，不懂的要虚心向教师或同学请教。5、要求按指定格式书写实验报告，且报告中应反映出对上次试验报告总结三实验步骤1. 启动Navicatfor MySQL，在MySQL 新建连接中完成连接参数配置。CREATE DATABASE db5536;USE db5536;2. 登录到本地数据库服务器后，连接到db5536数据库上。CREATE TABLE cqupt (s1 INTEGER);IN

16、SERT INTO cqupt VALUES (5);3. 执行五、教学过程中（三）实验内容的1部分的（2）、（3）步骤创建及调用不带参数的存储过程，验证不带参数的存储过程的创建和调用方法。CREATE procedure first_p()SELECTHello MySQL,This is my first procedure/CREATE procedure second_p()SELECT current_date from cqupt/Call first_p()/Call second_p()/4. 在步骤3的基础上,创建存储过程third_p()，显示表cqupt中的数据，并对其进

17、行调用测试。结果如图所示：CREATE procedure third_p()SELECT * from cqupt/5. 执行五、教学过程中（三）实验内容的第2部分创建及调用带参数的存储过程，验证带参数的存储过程的创建和调用方法。CREATE PROCEDURE simpleproc (OUT paraml INT)SELECT COUNT(*) INTO paraml FROM cqupt/CREATE PROCEDURE simpleprocl (IN paraml INT)SET x = paraml -100/CALL simpleproc(a)/Select a/CALL simp

18、leprocl(5500)/SELECT x/6. 在步骤5的基础上，创建存储过程simpleproc2()，对传入参数进行乘以10的处理，变量定义x,并对其进行调用测试。结果如图所示：CREATE PROCEDURE simpleprocl (IN paraml INT)SET x = paraml * 10/CALL simpleprocl2(50)/SELECT x/7. 用Drop Procedure语句删除存储过程first_p。如图所示：DROP PROCEDURE first_p/四实验总结：1、 如何修改存储过程？答：1）用DROP PROC PROC_A2) ALTER PR

19、OC PROC_A: ALTER PROCEDURE Procedure_name;numberparameter data_typyarying=defaultoutput,.n3)用CREATE OR REPLACE PROCEDURE PROC_A;2、 如果雇员数据重复插入，如何修改存储过程来正确显示结果？答：ALTER PROC PROC_A;ALTER PROCEDUREProcedure_name;nnumberparameter data_typyarying=defaultoutput,.n实验七触发器一、实验目的触发器是特定事件出现的时候，自动执行代码块类似于存储过程。 触

20、发器是特定事件出现的时候，自动执行代码块类似于存储过程。 触发器是特定事件出现的时候，自动执行代码块类似于存储过程。 触发 器与存储过程的 区别在于：是由用户或应序显式调器与存储过程的 区别在于：是由用户或应序显式调器与存储过程的 区别在于：是由用户或应序显式调器与存储过程的 区别在于：是由用户或应序显式调器与存储过程的 区别在于：是由用户或应序显式调器与存储过程的 区别在于：是由用户或应序显式调器与存储过程的 区别在于：是由用户或应序显式调器与存储过程的 区别在于：是由用户或应序显式调器与存储过程的 区别在于：是由用户或应序显式调,而触发器 是不能被直接调用的 ，由 DBMS 自动执行 。触

21、发器 具有这些 具有这些 功能： 功能： 1、允许 、允许 /限制对表的修改 限制对表的修改 限制对表的修改 ；2、自动生成派列，比 、自动生成派列，比 、自动生成派列，比 如自增字段 如自增字段 ；3、强制数据一致性 、强制数据一致性 、强制数据一致性 ；4、提供审计和日志记录 、提供审计和日志记录 、提供审计和日志记录 ；5、防止无效的事 、防止无效的事 、防止无效的事 务处理 ；6、启用复杂的业务逻辑 。本次实验了解 MySQL 触发器的创建、修改和删除方法步骤 ，掌握 在 Navicat for MySQL Navicat for MySQL Navicat for MySQL 中对触

22、发器的进行创建、修改和删除，掌握测试 的方法。 二、实验要求1、实验前：预习内容，学相关知识。2、实验中：按照内容要求进行，时注 意每种 SQL 语句的 语句的 基本命令 及各个关键字的含义，做好实验记录。3、实验后：分析结果，总知识得出论按格式写报告。4、在整个实验过程中，要独立思考按时完成任务不懂的虚心向 、在整个实验过程中，要独立思考按时完成任务不懂的虚心向 、在整个实验过程中，要独立思考按时完成任务不懂的虚心向 、在整个实验过程中，要独立思考按时完成任务不懂的虚心向 、在整个实验过程中，要独立思考按时完成任务不懂的虚心向 、在整个实验过程中，要独立思考按时完成任务不懂的虚心向 、在整个

23、实验过程中，要独立思考按时完成任务不懂的虚心向 、在整个实验过程中，要独立思考按时完成任务不懂的虚心向 、在整个实验过程中，要独立思考按时完成任务不懂的虚心向 、在整个实验过程中，要独立思考按时完成任务不懂的虚心向 教师或同学请。5、要求按指定格式书写实验报告，且中应反映出对 、要求按指定格式书写实验报告，且中应反映出对 、要求按指定格式书写实验报告，且中应反映出对 、要求按指定格式书写实验报告，且中应反映出对 、要求按指定格式书写实验报告，且中应反映出对 、要求按指定格式书写实验报告，且中应反映出对 、要求按指定格式书写实验报告，且中应反映出对 、要求按指定格式书写实验报告，且中应反映出对

24、本次 实验的总结，下次 实验的总结，下次实验的总结，下次实验前交实验报告。三实验步骤1.启动Navicat for MySQL，在MySQL 新建连接中完成连接参数配置。2.登录到本地数据库服务器后，连接到test数据库上。3.执行五、教学过程中（三）实验内容的第一部分创建简单触发器，验证触发器的创建和触发器的触发测试。CREATE TABLE student(SNO VARCHAR(10),SNAME VARCHAR(30),SAGE INT);CREATE TABLE student_log(who VARCHAR(30),whattime TIMESTAMP);SELECT * FROM

25、 student_log;USE test;CREATE TRIGGER audit_studentBEFORE INSERT ON studentFOR EACH ROWBEGININSERT INTO student_log(who,whattime) VALUES (CURRENT_USER,CURRENT_TIMESTAMP);END;insert into student values (1992150033,吴思远,40);SELECT * from student_log;4.执行五、教学过程中（三）实验内容的第二部分改造简单触发器，增加触发类型判断功能，验证触发器的改造和改造后

26、的触发器触发测试。alter table student_log add (action varchar (20);insert into student values (1992150034,张飒飒,38);select * from student_log;5.自行编写student表的触发器aduit_student_update，在对student表进行更新操作前触发，触发时，向student_log表写入触发用户、触发时间和触发类型。对该新建进行触发测试。use test; create trigger audit_student_update before update on st

27、udent for each row begin insert into student_log(who,whattime,action) VALUES (current_user,current_timestamp,update);end;update studentset sage=sage+1;select * from student_log;6.自行编写student表的触发器aduit_student_delete，在对student表进行删除操作后触发，触发时，向student_log表写入触发用户、触发时间和触发类型。并对该新建进行触发测试。use test; create trigger audit_student_delete after delete on student for each row begin insert into s