实验三 数据查询

一、【实验教学

1、掌握单表查询。

2、掌握多表查询。

二、【实验教学的基本要求】

1、掌握SQL程序设计基本规范；

2、熟练运用SQL实现数据基本查询，包括单表查询、分组统计查询和连接查询；

3、理解和掌握SQL查询语句中各个子句的特点和作用，按照SQL程序设计规范，写出具体的SQL查询语句并调试通过。

三、【实验原理】

基本查询和统计查询都要用到SELECT语句，但根据不同的查询要求，会使用不同的关键字或集合函数。

（1）SELECT语句的基本语法格式如下：

SELECT [ALL|DISTINCT ] <目标列表达式> [别名] [,<目标列表达式> [别名] ] …

FROM <表名或视图名> [别名]  [,<表名或视图名> [别名] ] ] … | (<SELECT语句>) [ AS ] <别名>

[WHERE <条件表达式> ]

[GROUP BY <列名1> [ HAVING <条件表达式> ] ]

[ORDER BY <列名2> [ ASC|DESC]]

LIMIT <行数1>[ OFFSET <行数2>];

（2）聚集函数：

统计元组个数

COUNT(*)

统计一列中值的个数

COUNT([DISTINCT|ALL] <列名>)

计算一列值的总和（此列必须为数值型）

SUM([DISTINCT|ALL] <列名>)

计算一列值的平均值（此列必须为数值型）

AVG([DISTINCT|ALL] <列名>)

求一列中的最大值和最小值

MAX([DISTINCT|ALL] <列名>)

MIN([DISTINCT|ALL] <列名>)

四、【实验内容】

题目1：单表查询：——SELECT……FROM

（1） 查询全体学生的所有信息；

（2） 查询选修了课程的学生的学号；

（3） 查询考试成绩有不及格的学生的学号；

（4） 查询年龄在20-30岁直接的学生的姓名，性别，所在系；

（5） 查询 IS，CS，MA系的所有学生的姓名和性别；

（6） 查找所有姓‘李’的学生的姓名，学号和性别；

（7） 查询没有先行课的课程的课程号cno和课程名cname；

题目2：查询结果排序：——ORDER BY 子句

（1）查询全体学生的情况，查询结果按所在系号升序排列，同一系中的学生按年龄降序排列；

题目3：连接查询；——关系数据库中最主要的查询，WHERE子句

（1） 查询每一门课程的间接先修课（只求两层即先修课的先修课），并且间接先修课不为空；

（2） 列出所有学生的基本情况和选课情况，若没有选课，则只列出基本情况信息；

（3） 查询每个学生的学号，姓名，选修的课程名和成绩；

题目4：使用带IN谓词的子查询——适用于集合查询

（1）查询与‘刘晨’在同一个系学习的学生的信息；

（2）查询选修了课程名为‘信息系统’的学生的学号和姓名；

（3）查询选修了课程‘1001’和课程‘1002’的学生的学号；

题目5：使用带比较运算的子查询

（1）查询比‘刘晨’年龄小的所有学生的信息；

五、【主要实验仪器及设备】

计算机、

CREATE DATABASE XSGL;
USE XSGL;CREATE TABLE student(
sno CHAR(9) PRIMARY KEY,
sname CHAR(10) NOT NULL UNIQUE,
ssex CHAR(2),
sage INT,
sdept CHAR(5));CREATE TABLE course(
cno CHAR(4) PRIMARY KEY,
cname CHAR(40) NOT NULL,
cpno CHAR(4),
credit INT,
FOREIGN KEY(cpno)REFERENCES course(cno)
);CREATE TABLE sc(
sno CHAR(9),
cno CHAR(4), 
grade DECIMAL(5,1),
PRIMARY KEY(sno,cno),
FOREIGN KEY (cno) REFERENCES course(cno),
FOREIGN KEY (sno) REFERENCES student(sno) 
);INSERT INTO student(sno, sname, ssex, sage, sdept)
VALUES ('95001', '李勇', '男', 20, 'CS'),('95002', '刘晨', '女', 19, 'IS'),('95003', '王敏', '女', 18, 'MA'),('95004', '张立', '男', 19, 'IS'),('95005', '刘云', '女', 18, 'CS');INSERT INTO course(cno, cname, credit, cpno)
VALUES ('1006', '数据处理', 3, NULL),('1007', 'PASCAL语言', 4, '1006'),('1005', '数据结构', 4, '1007'),('1002', '数学', 6, NULL),('1001', '数据库', 4, '1005'),('1003', '信息系统', 3, '1001'),('1004', '操作系统', 4, '1006');		INSERT INTO sc(sno, cno, grade)
VALUES ('95001', '1001', 92.3),('95001', '1002', 85.0),('95001', '1003', 88.3),('95002', '1002', 90.5),('95002', '1003', 80.0),('95003', '1002', 85.6),('95004', '1001', 58.9),('95004', '1002', 85.7);	-- 	查询全体学生的所有信息；		 SELECT * from student;	
-- 	 查询选修了课程的学生的学号；	 SELECT sno from sc;	-- 查询考试成绩有不及格的学生的学号；SELECT sno from sc WHERE sc.grade<60;	 -- 查询年龄在20-30岁直接的学生的姓名，性别，所在系；
SELECT  sname, ssex,  sdept from student WHERE student.sage>20 and student.sage<30;	
-- 查询 IS，CS，MA系的所有学生的姓名和性别；			 SELECT  sname, ssex from student WHERE  sdept in('IS','CS','MS');--查找所有姓‘李’的学生的姓名，学号和性别；SELECT sno, sname, ssex from student WHERE sname LIKE'李%';-- 查询没有先行课的课程的课程号cno和课程名cname；		SELECT cno,cname from course WHERE cpno is NULL;
-- 查询全体学生的情况，查询结果按所在系号升序排列，同一系中的学生按年龄降序排列；			 
SELECT * from  student JOIN sc ON student.sno=sc.sno  ORDER BY sc.cno ASC,student.sage DESC;
-- 查询每一门课程的间接先修课（只求两层即先修课的先修课），并且间接先修课
SELECT cpon from(SELECT cpon FROM course WHERE course.cpon!=NULL);-- 列出所有学生的基本情况和选课情况，若没有选课，则只列出基本情况信息；
SELECT student.sno, student.sname, student.ssex, student.sage, student.sdept, sc.cno from  student JOIN sc ON student.sno=sc.sno;-- 查询每个学生的学号，姓名，选修的课程名和成绩；
SELECT student.sno, student.sname, student.sdept, sc.cno,sc.grade from  student JOIN sc ON student.sno=sc.sno;
-- 查询与‘刘晨’在同一个系学习的学生的信息；
SELECT s.* from  student s JOIN student  f ON s.sdept=f.sdept where f.sname='刘晨'AND s.sname!='刘晨';-- 查询选修了课程名为‘信息系统’的学生的学号和姓名；
SELECT s.* from  student s JOIN sc c ON s.sno=c.sno  WHERE c.cno=( SELECT cno FROM course WHERE cname='信息系统');-- 查询选修了课程‘1001’和课程‘1002’的学生的学号；
SELECT sno from sc WHERE sc.cno IN('1001','1002');
-- 查询比‘刘晨’年龄小的所有学生的信息；
SELECT s2.* from  student s1 JOIN student s2 ON 
s1.sname='刘晨' WHERE s2.sage<s1.sage;

MySQL