94 lines
2.2 KiB
MySQL
94 lines
2.2 KiB
MySQL
|
/*
|
|||
|
|
根据stu.sql脚本中的数据表完成下列操作：
|
|||
|
|
|
|||
|
|
1） 查询学生选课表中的全部数据
|
|||
|
|
|
|||
|
|
2） 查询全体学生的姓名、学号和所在系。
|
|||
|
|
|
|||
|
|
3） 查询全体学生的姓名及其出生年份。
|
|||
|
|
|
|||
|
|
4） 查询计算机系全体学生的姓名。
|
|||
|
|
|
|||
|
|
5） 查询年龄在33岁以下的学生的姓名及年龄。
|
|||
|
|
|
|||
|
|
6）查询考试成绩有不及格的学生的学号
|
|||
|
|
|
|||
|
|
7）查询成绩在70~80分之间的学生，包括学号，课程号和成绩
|
|||
|
|
|
|||
|
|
【提示】stu.sql文件，在班级群中， 拿到stu.sql文件后，先在数据库创建新的库 studb2, 然后再source。 库中的表说明如下：
|
|||
|
|
student 学生表
|
|||
|
|
teacher 教师表
|
|||
|
|
course 课程表
|
|||
|
|
sc 成绩表
|
|||
|
|
|
|||
|
|
计算年龄的方式： floor(datediff(now(), age)/365) as age
|
|||
|
|
*/
|
|||
|
|
|
|||
|
|
-- 删除已存在同名数据库 studb2
|
|||
|
|
drop database if exists studb2;
|
|||
|
|
|
|||
|
|
-- 新建数据库 studb2
|
|||
|
|
create database studb2;
|
|||
|
|
|
|||
|
|
-- 进入数据库 studb2
|
|||
|
|
use studb2;
|
|||
|
|
|
|||
|
|
-- 导入 stu.sql 数据到数据库 stu
|
|||
|
|
source stu.sql;
|
|||
|
|
|
|||
|
|
-- 显示导入结果
|
|||
|
|
show tables;
|
|||
|
|
|
|||
|
|
-- 查询学生选课表中的全部数据
|
|||
|
|
SELECT *
|
|||
|
|
FROM course;
|
|||
|
|
|
|||
|
|
-- 查询全体学生的姓名、学号和所在系
|
|||
|
|
SELECT
|
|||
|
|
student.name AS '姓名',
|
|||
|
|
student.sid AS '学号',
|
|||
|
|
xb.name AS '所在系'
|
|||
|
|
FROM student
|
|||
|
|
JOIN xb
|
|||
|
|
ON student.xid = xb.xid;
|
|||
|
|
|
|||
|
|
-- 查询全体学生的姓名及其出生年份
|
|||
|
|
SELECT
|
|||
|
|
name AS '学生姓名',
|
|||
|
|
YEAR(age) AS '出生年份'
|
|||
|
|
FROM student;
|
|||
|
|
|
|||
|
|
-- 查询计算机系全体学生的姓名
|
|||
|
|
SELECT
|
|||
|
|
xb.name AS '所在系',
|
|||
|
|
student.name AS '姓名'
|
|||
|
|
FROM xb
|
|||
|
|
JOIN student
|
|||
|
|
ON xb.xid = student.xid
|
|||
|
|
WHERE xb.name = '计算机系';
|
|||
|
|
|
|||
|
|
-- 查询年龄在33岁以下的学生的姓名及年龄
|
|||
|
|
SELECT
|
|||
|
|
name AS '姓名',
|
|||
|
|
FLOOR(DATEDIFF(NOW(), age)/365) AS '年龄'
|
|||
|
|
FROM student
|
|||
|
|
WHERE FLOOR(DATEDIFF(NOW(), age)/365) < 33;
|
|||
|
|
|
|||
|
|
-- 查询考试成绩有不及格的学生的学号 DISTINCT 用于结果行去重
|
|||
|
|
SELECT DISTINCT
|
|||
|
|
student.sid AS '学号'
|
|||
|
|
FROM student
|
|||
|
|
JOIN sc
|
|||
|
|
ON student.sid = sc.sid
|
|||
|
|
WHERE sc.score < 60;
|
|||
|
|
|
|||
|
|
-- 查询成绩在70-80分之间的学生，包括学号，课程号和成绩
|
|||
|
|
SELECT
|
|||
|
|
student.sid AS '学号',
|
|||
|
|
sc.cid AS '课程号',
|
|||
|
|
sc.score AS '成绩'
|
|||
|
|
FROM student
|
|||
|
|
JOIN sc
|
|||
|
|
ON student.sid = sc.sid
|
|||
|
|
-- WHERE sc.score BETWEEN 70 AND 80;
|
|||
|
|
WHERE sc.score >= 70 and sc.score <= 80;
|