接实验2
一、数据库的单表查询和连接查询
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 姓名='张乐'
删除成功（已没有张乐的信息）：
版权归原作者 m0_62537560 所有， 如有侵权，请联系我们删除。