【SQL实验】表的更新和简单查询

时间：2024-10-25 10:53:55

完整代码在文章末尾

在上次实验创建的educ数据库基础上，用SQL语句为student表、course表和sc表中添加以下记录

【SQL实验】数据库、表、模式的SQL语句操作_创建一个名为educ数据库,要求如下: (下面三个表中属性的数据类型需要自己设计合适-****博客在这篇博文中已经完成了数据库的创建以及educ数据库内三张表的创建，但这些表目前是空的，没有数据。

通过INSERT语句向这三张表中插入数据（可以自行减少数据量和数据内容）

INSERT INTO student (sno, sname, ssex, sage, sdept) 
VALUES  
('Y71814001', '陈靖', '男', 20, '互联网学院'),   
('Y71814003', '江宏', '男', 20, '互联网学院'),   
('Y71814006', '洪增志', '男', 19, '互联网学院'),   
('Y71814008', '程熙', '男', 18, '互联网学院'),   
('Y72014019', '李越', '男', 21, '计算机学院'),   
('Y72014020', '唐子恒', '男', 18, '计算机学院'),   
('Y72014040', '朱凯', '男', 17, '计算机学院'),   
('Y72014041', '李哲', '男', 17, '电子工程学院'),   
('Y72014050', '李原春', '女', 18, '电子工程学院'),   
('Y72014056', '周晓瑞', '女', 19, '电子工程学院');

INSERT INTO course (cno, cname, cpno, ccredit) 
VALUES 
('01', '计算机导论', NULL, 2), 
('02', 'C语言', '01', 2), 
('03', '离散数学', '02', 2), 
('04', '数据结构', '02',NULL), 
('05', '数据库原理及应用', '04', 3), 
('06', '操作系统', NULL, 2);



INSERT INTO sc (sno, cno, grade) 
VALUES 
('Y71814001', '01', 97), 
('Y71814003', '01', 85), 
('Y71814006', '01', 75), 
('Y71814008', '01', 80), 
('Y71814001', '02', 97), 
('Y71814003', '02', 92), 
('Y71814006', '02', 80), 
('Y71814008', '02', 87), 
('Y71814001', '03', 89);

INSERT INTO 语句添加数据

基本语法

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

（1）在student中查找“互联网学院”所有学生的姓名、学号、所在院系信息

SELECT sname, sno, sdept 
FROM student 
WHERE sdept = '互联网学院';
  • SELECT 是用于查询数据库的关键字。它用于指定要查询的列
  • FROM 指定从哪个表中检索数据
  • WHERE 子句用于指定筛选条件

运行结果：

（2）在student中找出2个姓李的学生的学号、姓名和所在院系，并按照学号大小排序

SELECT top 2 sno, sname, sdept 
FROM student 
WHERE sname LIKE '李%' 
ORDER BY sno;

 TOP 2 表示只返回查询结果的前两行数据

LIKE：用于进行模糊查询，允许使用通配符搜索

'李%'：表示查找姓 "李" 的学生，% 是通配符，表示姓 "李" 后可以跟任意字符

ORDER BY：用于对查询结果进行排序（默认情况下为升序）

运行结果：

（3）在student中找出学号包含‘1914’的且年龄在19到21岁之间的女生信息

SELECT * 
FROM student 
WHERE sno LIKE '%1914%' AND sage BETWEEN 19 AND 21 AND ssex = '女';

 AND：用于组合多个条件，表示这些条件必须全部满足

BETWEEN ... AND ... 包括指定的起始和结束值【查询的结果会包含边界值】

运行结果：

（4）在student中找出互联网学院和电子工程学院年龄小于20的所有学生信息

SELECT * 
FROM student 
WHERE sdept IN ('互联网学院', '电子工程学院') AND sage < 20;

*：表示选择表中的所有列

IN：用于匹配多个值，等价于 OR 的组合

运行结果：

（5）查询Student表中互联网学院学生数量

SELECT COUNT(*) 
FROM student 
WHERE sdept = '互联网学院';

 COUNT(*)：这是一个聚合函数，用于计算符合条件的记录的总数。* 表示计数所有记录，无论这些记录的列值是否为 NULL

运行结果：

（6）查询Student表中院系种类

SELECT DISTINCT sdept 
FROM student;

DISTINCT：用于去除查询结果中的重复记录，只返回唯一的值。它确保结果集中每个值都是不同的

运行结果：

（7）按照所在院系分类，统计出每个院系的学生数量

SELECT sdept, COUNT(*) AS 每个院系的学生数量 
FROM student 
GROUP BY sdept;

AS 每个院系的学生数量：为 COUNT(*) 结果指定一个别名，使得返回结果的列名更具可读性

GROUP BY：用于将结果集按指定的列分组

运行结果：

（8）按照所在院系分类，统计出除互联网学院之外的其他学院的学生数量及名称

SELECT sdept, COUNT(*) AS 每个院系的学生数量 
FROM student 
WHERE sdept != '互联网学院' 
GROUP BY sdept;

运行结果：

（9）计算‘02’号课程的平均成绩

SELECT AVG(grade) AS 平均成绩 
FROM sc 
WHERE cno = '02';

AVG(grade)：聚合函数，用于计算指定列的平均值

运行结果：

（10）求各个课程号及相应的选课人数

SELECT cno, COUNT(*) AS 选课人数 
FROM sc 
GROUP BY cno;

运行结果：

（11）求每门课程的平均成绩

--从 sc 表中计算每个课程的平均成绩，并返回每个课程号及其对应的平均成绩

SELECT cno, AVG(grade) AS 平均成绩 
FROM sc 
GROUP BY cno;

运行结果：

（12）求有成绩的每个学生的平均成绩

SELECT sno, AVG(grade) AS 平均成绩 
FROM sc 
WHERE grade IS NOT NULL
GROUP BY sno;

grade IS NOT NULL：只考虑成绩不为空的记录。只会计算有成绩的学生，排除掉成绩为 NULL 的记录。 

运行结果：

（13）求平均成绩大于86分的学生学号及平均成绩

SELECT sno, AVG(grade) AS 平均成绩 
FROM sc 
GROUP BY sno 
HAVING AVG(grade) > 86;

HAVING：用于对 GROUP BY 结果进行筛选。与 WHERE 不同，HAVING 是在聚合操作完成后应用的 

运行结果：

（14）查询至少选修了2门课程（含2门）的学生的学号和平均成绩，并根据平均成绩进行降序排序

SELECT sno, AVG(grade) AS 平均成绩 
FROM sc 
GROUP BY sno 
HAVING COUNT(cno) >= 2 
ORDER BY 平均成绩 DESC;

DESC：降序排列

运行结果：

（15）查询男同学的姓名和出生年份

运行结果：

SELECT sname, (GetDate() - sage) AS 出生年份 
FROM student 
WHERE ssex = '男';

(GetDate() - sage)：计算表达式。计算当前日期 (GetDate()) 和学生年龄 (sage) 之间的差值

GetDate()：当前日期

SELECT sname, (YEAR(GetDate()) - sage) AS 出生年份
FROM student
WHERE ssex = '男';

 YEAR(GetDate())：提取当前日期的年份

INSERT INTO student (sno, sname, ssex, sage, sdept) 
VALUES  
('Y71814001', '陈靖', '男', 20, '互联网学院'),   
('Y71814003', '江宏', '男', 20, '互联网学院'),   
('Y71814006', '洪增志', '男', 19, '互联网学院'),   
('Y71814008', '程熙', '男', 18, '互联网学院'),   
('Y72014019', '李越', '男', 21, '计算机学院'),   
('Y72014020', '唐子恒', '男', 18, '计算机学院'),   
('Y72014040', '朱凯', '男', 17, '计算机学院'),   
('Y72014041', '李哲', '男', 17, '电子工程学院'),   
('Y72014050', '李原春', '女', 18, '电子工程学院'),   
('Y72014056', '周晓瑞', '女', 19, '电子工程学院');

INSERT INTO course (cno, cname, cpno, ccredit) 
VALUES 
('01', '计算机导论', NULL, 2), 
('02', 'C语言', '01', 2), 
('03', '离散数学', '02', 2), 
('04', '数据结构', '02',NULL), 
('05', '数据库原理及应用', '04', 3), 
('06', '操作系统', NULL, 2);



INSERT INTO sc (sno, cno, grade) 
VALUES 
('Y71814001', '01', 97), 
('Y71814003', '01', 85), 
('Y71814006', '01', 75), 
('Y71814008', '01', 80), 
('Y71814001', '02', 97), 
('Y71814003', '02', 92), 
('Y71814006', '02', 80), 
('Y71814008', '02', 87), 
('Y71814001', '03', 89);


--1
SELECT sname, sno, sdept 
FROM student 
WHERE sdept = '互联网学院';

--2
SELECT top 2 sno, sname, sdept 
FROM student 
WHERE sname LIKE '李%' 
ORDER BY sno;


--3
SELECT * 
FROM student 
WHERE sno LIKE '%1914%' AND sage BETWEEN 19 AND 21 AND ssex = '女';

--4
SELECT * 
FROM student 
WHERE sdept IN ('互联网学院', '电子工程学院') AND sage < 20;


--5

SELECT COUNT(*) 
FROM student 
WHERE sdept = '互联网学院';

--6
SELECT DISTINCT sdept 
FROM student;

--7
SELECT sdept, COUNT(*) AS 每个院系的学生数量 
FROM student 
GROUP BY sdept;

--8
SELECT sdept, COUNT(*) AS 每个院系的学生数量 
FROM student 
WHERE sdept != '互联网学院' 
GROUP BY sdept;

--9
SELECT AVG(grade) AS 平均成绩 
FROM sc 
WHERE cno = '02';

--10
SELECT cno, COUNT(*) AS 选课人数 
FROM sc 
GROUP BY cno;

--11
SELECT cno, AVG(grade) AS 平均成绩 
FROM sc 
GROUP BY cno;

--12
SELECT sno, AVG(grade) AS 平均成绩 
FROM sc 
WHERE grade IS NOT NULL
GROUP BY sno;

--13
SELECT sno, AVG(grade) AS 平均成绩 
FROM sc 
GROUP BY sno 
HAVING AVG(grade) > 86;

--14
SELECT sno, AVG(grade) AS 平均成绩 
FROM sc 
GROUP BY sno 
HAVING COUNT(cno) >= 2 
ORDER BY 平均成绩 DESC;


--15
SELECT sname, (GetDate() - sage) AS 出生年份 
FROM student 
WHERE ssex = '男';

相关文章