接实验2

目录

一、数据库的单表查询和连接查询

1.查询各位学生的学号、班级和姓名

2.查询课程的全部信息

 3.查询数据库有哪些专业班级

 4.查询学时数大于60的课程信息

5.查询在1986年出生的学生的学号、姓名和出生日期

 6.查询三次作业的成绩都在80分以上的学号、课程号

7.查询姓张的学生的学号、姓名和专业班级

8.查询05级的男生信息

9.查询没有作业成绩的学号和课程号

10.查询学号为0538的学生的作业1总分

 11.查询选修了K001课程的学生人数

12.查询数据库中有几个班级

13.查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分和作业3平均分

14.查询于兰兰的选课信息，列出学号、姓名、课程名（使用两种连接查询的方式）

二、数据库查询与数据操纵

1.使用查询语句完成以下任务

1.查询与‘张志国’同一班级的学生信息（使用连接查询和子查询方式）

2.查询比“计算机应用基础”学时多的课程信息（使用连接查询与子查询的方式）

3.查询选修课程号为K002的学生的学号、姓名（使用连接查询、普通子查询、相关子查询、使用exists关键字的子查询）

4.查询没有选修K001和M001课程的学号、课程号和三次成绩（使用子查询）

 2.使用数据操纵完成以下任务

1.在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05

2.将所有课程的学分数变为原来的两倍

3.删除张乐的信息

---

一、数据库的单表查询和连接查询

1.查询各位学生的学号、班级和姓名

SELECT DISTINCT 学号,专业班级,姓名
FROM 学生表

2.查询课程的全部信息

SELECT *
FROM 课程表

 3.查询数据库有哪些专业班级

SELECT DISTINCT 专业班级
FROM 学生表

 4.查询学时数大于60的课程信息

SELECT *
FROM 课程表
WHERE 学时数>60

5.查询在1986年出生的学生的学号、姓名和出生日期

SELECT 学号,姓名,出生日期
FROM 学生表
WHERE 学生表.出生日期>='1986-01-01' AND 学生表.出生日期<='1986-12-31'

 6.查询三次作业的成绩都在80分以上的学号、课程号

SELECT 学号,课程号
FROM 学生作业表
WHERE 作业1成绩>=80 AND 作业2成绩>=80 AND 作业3成绩>=80

7.查询姓张的学生的学号、姓名和专业班级

SELECT 学号,姓名,专业班级
FROM 学生表
WHERE 学生表.姓名 LIKE '张%'

8.查询05级的男生信息

SELECT *
FROM 学生表
WHERE 专业班级 LIKE '%05'
	AND 性别='男'

%表示任意字符，_表示一个字符。用于部分匹配查询（也称模糊查询），用于字符型数据查找。

格式：<属性名> LIKE <字符串常量>

%abc表示以abc结尾，前面可以有任意个字符。

abc%表示以abc开头，后面可以有任意个字符。

%abc%表示中间含有abc，前后可以有任意个字符。

a_b表示ab之间有一个字符。

[0-9]表示在0~9范围的字符。

[^a-z]表示不在a~z范围的字符。

9.查询没有作业成绩的学号和课程号

SELECT 学号,课程号
FROM 学生作业表
WHERE 作业1成绩 IS NULL
	OR 作业2成绩 IS NULL
	OR 作业3成绩 IS NULL

判断数据是否为NULL值，不能用=NULL来判断，要用IS NULL判断。

10.查询学号为0538的学生的作业1总分

SELECT 作业1总分=SUM(作业1成绩)
FROM 学生作业表
WHERE 学号='0538'

select后既可接属性名，也可接表达式。

 11.查询选修了K001课程的学生人数

SELECT 学生人数=COUNT(课程号)
FROM 学生作业表
WHERE 课程号='K001'

12.查询数据库中有几个班级

SELECT 班级数=COUNT(DISTINCT 专业班级)
FROM 学生表

 

13.查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分和作业3平均分

SELECT 学号,作业1平均分=AVG(作业1成绩),作业2平均分=AVG(作业2成绩),作业3平均分=AVG(作业3成绩)
FROM 学生作业表
GROUP BY 学号
HAVING (COUNT(课程号)>=3)

 使用GROUP分组后，如果再进行筛选，不能使用SELECT语句，只能使用HAVING语句。

库函数（如AVG,MAX,COUNT等）用在select或having子句中，不能用在where子句中。

14.查询于兰兰的选课信息，列出学号、姓名、课程名（使用两种连接查询的方式）

方式一：

SELECT DISTINCT 学生作业表.学号,学生表.姓名,课程表.课程名
FROM 学生作业表
INNER JOIN 学生表
	ON 学生作业表.学号=学生表.学号
INNER JOIN 课程表
	ON 学生作业表.课程号=课程表.课程号
WHERE 学生表.姓名='于兰兰'

 方式二：

SELECT DISTINCT 学生作业表.学号,学生表.姓名,课程表.课程名
FROM 学生作业表,学生表,课程表
WHERE 学生表.姓名='于兰兰'
	AND 学生作业表.学号=学生表.学号
	AND 学生作业表.课程号=课程表.课程号

如果两张表有外键关系可以使用内链接，因为通过内链接每一条只能返回单条记录。

使用内链接如果出现重复记录则大部分情况说明查询是错误的，在确保sql查询没有逻辑错误的情况下，可以使用DISTINCT去除重复记录。

显示的内连接，一般称为内连接，有INNER JOIN，形成的中间表为两个表经过ON条件过滤后的笛卡尔积。

隐式的内连接，没有INNER JOIN，形成的中间表为两个表的笛卡尔积。

二、数据库查询与数据操纵

1.使用查询语句完成以下任务

1.查询与‘张志国’同一班级的学生信息（使用连接查询和子查询方式）

方式一：连接查询

SELECT 学生表2.学号,学生表2.姓名,学生表2.性别,
	学生表2.专业班级,学生表2.出生日期,学生表2.联系电话
FROM 学生表 AS 学生表1,学生表 AS 学生表2
WHERE 学生表1.专业班级=学生表2.专业班级
	AND 学生表1.姓名='张志国'
	AND 学生表2.姓名<>'张志国'

采用自连接方式，由于列同名，要带上表名称。

连接查询：

连接方法

类别	连接方法	作用
内连接	内连接 （INNER JOIN）	显示符合条件的记录，此为默认值。
外连接	左（外）连接 （LEFT JOIN）	显示符合条件的数据行以及左边表中不符合条件的数据行。此时右边数据行会以NULL来显示。
	右（外）连接 （RIGHT JOIN）	显示符合条件的数据行以及右边表中不符合条件的数据行。此时左边数据行会以NULL来显示。
	全外连接 （FULL JOIN）	显示符合条件的数据行以及两边表中不符合条件的数据行。此时缺乏数据的数据行会以NULL来显示。
交叉连接	CROSS JOIN	将一个表中的每一个记录和另一个表的每个记录匹配成新的数据行（笛卡尔积）。

引用列名时要加上表名前缀，避免产生二义性。若列名唯一，则不必加前缀。

内连接几种创建方式：
注：R,T为两个不同的表，TNo为两个表共有的列。

1.常见写法

SELECT T.TNo,TN,RNo
FROM T INNER JOIN R
ON T.TNo=R.TNo

内连接两个表，连接条件为TNo属性的值相等。

2.投影法

SELECT T.TNo,TN,RNo
FROM
(SELECT TNo,TN) AS T0
INNER JOIN
(SELECT TNo,RNo) AS R0
ON T0.TNo=R0.TNo

选取两个表需要的属性组成新表，内连接两个新表，连接条件同上。

3.筛选法

SELECT T.TNo,TN,RNo
FROM T,R
WHERE T.TNo=R.TNo

默认连接两表，由于没有设置连接条件，结果为两表的笛卡尔积（相同的属性列重合），需要用WHERE语句筛选。

方式二：子查询：

SELECT *
FROM 学生表
WHERE 专业班级=(SELECT 专业班级
	FROM 学生表
	WHERE 姓名='张志国')
	AND 姓名<>'张志国'

 WHERE中进行子查询，返回符合子查询的集合，父查询在集合中继续查询。

2.查询比“计算机应用基础”学时多的课程信息（使用连接查询与子查询的方式）

方式一：连接查询

SELECT 课程表2.课程号,课程表2.课程名,课程表2.学分数,
	课程表2.学时数,课程表2.任课老师
FROM 课程表 AS 课程表1,课程表 AS 课程表2
WHERE 课程表1.课程名='计算机应用基础'
	AND 课程表2.学时数>课程表1.学时数

方式二：子查询

SELECT *
FROM 课程表
WHERE 学时数>(SELECT 学时数
	FROM 课程表
	WHERE 课程名='计算机应用基础')

3.查询选修课程号为K002的学生的学号、姓名（使用连接查询、普通子查询、相关子查询、使用exists关键字的子查询）

方法一：连接查询

SELECT 学生表.学号,姓名
FROM 学生表,学生作业表
WHERE 学生表.学号=学生作业表.学号
	AND 学生作业表.课程号='K002'

 方法二：普通子查询

SELECT 学号,姓名
FROM 学生表
WHERE (学号 IN (SELECT 学号
	FROM 学生作业表
	WHERE 课程号='K002'))

 方法三：相关子查询

SELECT 学号,姓名
FROM 学生表
WHERE ('K002' IN (SELECT 课程号
	FROM 学生作业表
	WHERE 学号=学生表.学号))

相关子查询涉及表外查询

 方法四：使用exists关键字的子查询

SELECT 学号,姓名
FROM 学生表
WHERE (EXISTS(SELECT 课程号
	FROM 学生作业表
	WHERE 学号=学生表.学号
	AND 课程号='k002'))

 子查询：

4.查询没有选修K001和M001课程的学号、课程号和三次成绩（使用子查询）

SELECT 学号,课程号,作业1成绩,作业2成绩,作业3成绩
FROM 学生作业表
WHERE (课程号 NOT IN(SELECT 课程号
	FROM 学生作业表
	WHERE 课程号 IN ('K001','M001')))

 2.使用数据操纵完成以下任务

1.在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05

INSERT INTO 学生表
VALUES ('0593','张乐','男','电子05',NULL,NULL)

 

2.将所有课程的学分数变为原来的两倍

UPDATE 课程表
SET 学分数=2*学分数

 表格数据变化：

原先：

当前：

3.删除张乐的信息

DELETE
FROM 学生表
WHERE 姓名='张乐'

 删除成功（已没有张乐的信息）：