0


SQL第三次实验

接实验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 姓名='张乐'

删除成功(已没有张乐的信息):

标签: sql 数据库

本文转载自: https://blog.csdn.net/m0_62537560/article/details/127289663
版权归原作者 m0_62537560 所有, 如有侵权,请联系我们删除。

“SQL第三次实验”的评论:

还没有评论