数据库实验（二二二）

数据库实验二

实验内容：
使用各种查询条件完成指定的查询操作
实验步骤
1、创建学生表student、课程表course和选课表SC,并输入数据（注意数据的完整性。）;（可以使用实验一中已经建立的表和数据）
2、对各表中的数据进行不同条件的查询；
包括的运算：投影、选择、比较运算符、逻辑运算符、字符匹配运算符、匹配列表范围、算术运算符、内部函数、排序、分组、分组函数使用
（1）查询全体学生的学号和姓名
（2）查询全体学生的详细记录
（3）查询软件学院的学生姓名、年龄、系别
（4）查询所有选修过课程的学生学号（不重复）
（5）查询考试不及格的学生学号（不重复）
（6）查询不是软件学院、计算机系的学生性别、年龄、系别
（7）查询年龄18－20岁的学生学号、姓名、系别、年龄；
（8）查询姓刘的学生情况
（9）查询姓刘或姓李的学生情况
（10）查询姓刘且名字为两个字的学生情况
（11）查询1983年以后出生的学生姓名。
（12）创建表 studentgrad(sno,mathgrade,englishigrade,chinesegrade)
计算学生各科总成绩并赋予别名
（13）利用内部函数 year()查找软件学院学生的出生年份
（14）利用字符转换函数实现字符联接。
Select sname + ‘年龄为’+cast(sage as char(2))+’岁’
From student
（15）查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。
（16）查询学生总人数。
（17）查询选修了课程的学生人数。
（18）查询选修了7号课程的学生总人数和平均成绩
（19）查询选修6号课程学生的最好成绩
（20）查询每个系的系名及学生人数。
（21）查找每门课的选修人数及平均成绩
（22）查找没有先修课的课程情况
**

实验要求

**

实验具体代码：
1.创建一个数据库并使用该数据库

CREATE DATABASE MY;

USE MY;

2.创建三个基本表

--创建学生表
CREATE TABLE student(
sno char(8) PRIMARY KEY,
sname char(6) UNIQUE,
ssex char(2),
sage int,
sdept char(10),
Constraint sex_1 check(ssex IN ('男','女'))
);


CREATE TABLE course(
cno char(2) PRIMARY KEY,
cname char(30),
credit int,
cpno char(3),
);


CREATE TABLE sc(
sno char(8),
cno char(2),
grade int CHECK (grade>=0 and grade<=100),
PRIMARY KEY(sno,cno),
Constraint FK_SNO FOREIGN KEY (sno) REFERENCES student(sno),
Constraint FK_CNO FOREIGN KEY (cno) REFERENCES course(cno)
);

3.向三个表中插入数据

INSERT INTO student VALUES('001','李华','男','20','CS'),
('002','刘晨','女','19,'CS'),
('003','王敏','女','18','MA'),
('004','张立','男','19','IS'),
('005','张三','男','20','IS'),
('006','刘平','女','20','CS'),
('007','李四','男','20','SW'),
('008','刘三世','女','16','MA'),
('009','王麻子','男','19','SW'),
('010','赵敏','女','18','SW');


INSERT INTO course (cno,cname,credit) VALUES('1','数据库','4','5'),
('2','数学','2','1'),
('3','信息系统','4','1'),
('4','操作系统','3','6'),
('5','数据结构','4','7'),
('6','数据处理','2','8'),
('7','mysql语言','4','6');




INSERT INTO sc VALUES('001','1',98),
('001','2',55),
('002','2',68),
('002','5',90),
('004','1',92),
('004','6',86),
('005','3',94),
('005','7',45),
('006','1',50),
('006','5',58),
('007','1',68),
('008','2',78),
('008','6',68),
('009','1',82),
('009','7',76);

4.查询全体学生的学号和姓名

SELECT sno,sname
FROM student;

5.查询全体学生的详细记录

SELECT *
FROM student;

6.查询软件学院的学生姓名、年龄、系别

SELECT sname,sage,sdept
FROM student
WHERE sdept='SW';

7.查询所有选修过课程的学生学号（不重复）

SELECT DISTINCT sno
FROM sc;

8.查询考试不及格的学生学号（不重复）

SELECT DISTINCT sno
FROM sc
WHERE grade<=60;

9.查询不是软件学院、计算机系的学生性别、年龄、系别

SELECT ssex,sage,sdept
FROM student
WHERE sdept<>'SW' and sdept<>'CS';


where sdept not in('CS','MA');

10.查询年龄18－20岁的学生学号、姓名、系别、年龄；

SELECT sno,sname,sdept,sage
FROM student
WHERE sage>=18 and sage<=20;

11.查询姓刘的学生情况

SELECT *
FROM student
WHERE sname LIKE '刘%';

12.查询姓刘或姓李的学生情况

SELECT *
FROM student
WHERE sname LIKE '[刘李]%';

13.查询姓刘且名字为两个字的学生情况

SELECT *
FROM student
WHERE sname LIKE '刘_';

14.当前年为2020年，查询1983年以后出生的学生姓名

SELECT sname
FROM student
WHERE 2020-sage>1983;

15.创建表studentgrad(sno,mathgrade,englishigrade,chinesegrade)计算学生各科总成绩并赋予别名

CREATE TABLE studentgrade(
sno char(8) primary key,
mathgrade int,
englishgrade int,
chinesegrade int,
);


INSERT INTO studentgrade values( '001',85,95,74),
( '002',86,91,70),
( '003',80,92,71),
( '004',81,91,75),
( '005',87,97,78),
( '006',70,90,84),
( '007',76,80,79),
( '008',75,95,52),
( '009',96,70,69),
( '010',66,100,89);


SELECT SUM(mathgrade+chinesegrade+englishgrade) AS 学生总成绩
FROM studentgrade
GROUP BY sno;

16.用内部函数 year()查找软件学院学生的出生年份

SELECT sname,(year(getdate())-student.sage )
from student
where sdept='CS';

17.字符转换函数实现字符联接

SELECT sname + '年龄为'+cast(sage as char(2))+'岁'
FROM student;

18.查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列

SELECT *
FROM student
ORDER BY sdept ASC,sage DESC;

19.查询学生总人数

SELECT COUNT(SNO)
FROM student;

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

SELECT COUNT(DISTINCT sno)
FROM sc;

21.查询选修了7号课程的学生总人数和平均成绩

SELECT COUNT(sno),AVG(grade)
FROM sc
WHERE cno='7'

22.查询选修6号课程学生的最好成绩

SELECT max(grade) as maxgrade
FROM sc
WHERE cno='6';

23.查询每个系的系名及学生人数

SELECT sdept,COUNT(*)
FROM student
GROUP BY sdept;

24：查找每门课的选修人数及平均成绩

SELECT cno,COUNT(sno),AVG(grade)
FROM sc
GROUP BY cno;

25.查找没有先修课的课程情况

SELECT *
FROM course
WHERE cpno is null;

---

注：

---

1：在选取不重复的记录时要加入DISTINCT关键词
2：在匹配字符串时所遇到的通配符"_“和”%"，要求别两者，前者代表一个字符，后者代表无穷字符。
3：对SQL内部函数的调用和每个内部函数的意义需理解清楚。