接实验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 NULLOR 作业2成绩 IS NULLOR 作业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 学生表2WHERE 学生表1.专业班级=学生表2.专业班级AND 学生表1.姓名='张志国'AND 学生表2.姓名'张志国'

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

连接查询：

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

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

1.常见写法

SELECT T.TNo,TN,RNoFROM T INNER JOIN RON T.TNo=R.TNo

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

2.投影法

SELECT T.TNo,TN,RNoFROM(SELECT TNo,TN) AS T0INNER JOIN(SELECT TNo,RNo) AS R0ON T0.TNo=R0.TNo

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

3.筛选法

SELECT T.TNo,TN,RNoFROM T,RWHERE T.TNo=R.TNo

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

方式二：子查询：

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

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

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

方式一：连接查询

SELECT 课程表2.课程号,课程表2.课程名,课程表2.学分数,课程表2.学时数,课程表2.任课老师FROM 课程表 AS 课程表1,课程表 AS 课程表2WHERE 课程表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.删除张乐的信息

DELETEFROM 学生表WHERE 姓名='张乐'

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