MySQL第三次作业

原创于 2025-07-16 13:53:37 发布 · 947 阅读

16 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

题目

主题目

附加题

数据库及表数据准备

-- 新建数据库
mysql> create database mydb9_stusys;
mysql> use  mydb9_stusys;

-- 新建student学生表
mysql> create table student( sno char(4), sname varchar(10) , ssex char(2), sage int(2) );
insert into student values("s001","张玲丽","女",20),("s002","吴鹏","男",19),("s003","李锐","男",19),("s004","赵丁雯","女",21),("s005","陈晓晓","女",18),("s006","孙德胜","男",22),("s007","刘琦玉","男",20),("s008","李波","男",20),("s009","李晨","男",19),("s010","王子涵","女",23);

-- 新建sc成绩表
mysql> create  table sc(sno char(4), cno char(4),score float(5.2));
insert into sc values("s001","c001",85.65),("s001","c002",77.45),("s001","c003",60.00),("s002","c002",72.35),("s003","c001",74.12),("s003","c002",85.05),("s004","c001",45.50),("s005","c001",99.00);

-- 新建course科目表
mysql> create  table course(cno char(4), cname varchar(20),tno char(4));
 insert into course values("c001","LINUX","t002"),("c002","JAVA","t002"),("c003","SSH","t001"),("c004","MYSQL","t001"),("c005","C","t003"),("c006","RHCE","t003"),("c007","RHCSA","t002"),("c008","PHP","t001"),("c009","HTML","t003"),("c010","PYTHON","t002");

-- 新建teacher教师表
mysql> create  table teacher(tno char(4), tname varchar(10));
insert into teacher values("t001","刘洋"),("t002","周星星"),("t003","王胜利"),("t004","刘毅");
mysql> select  *  from  teacher;

1.将mydb9_stusys数据库下的student、sc 和course表，备份到本地主机保存为st_msg_bak.sql文件，然后将数据表恢复到自建的db_test数据库中；

2.在db_test数据库创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。

3.查看mydb9_stusys库下哪些是视图表；

4.删除视图表

sql语句

-- 1.分别查询student表和score表的所有记录
mysql> select * from student;select * from score;

-- 2.查询student表的第2条到5条记录
mysql> select * from student limit 1,4;

-- 3.从student表中查询计算机系和英语系的学生的信息
mysql> select * from student where department in ('计算机系','英语系');

-- 4.从student表中查询年龄小于22岁的学生信息
mysql> select * from student where year(curdate()) - birth < 22;
-- year(curdate())函数作用：取出当前系统日期的“年份”部分，返回一个 4 位整数。

-- 5.从student表中查询每个院系有多少人
mysql> select department,count(*) as student_count from student group by department;

-- 6.从score表中查询每个科目的最高分
mysql> select c_name,max(grade) as max_grade from score group by c_name;

-- 7.查询李广昌的考试科目（c_name）和考试成绩（grade）
mysql> select s.c_name,s.grade from score as s join student as st on s.stu_id = st.id where st.name = '李广昌';

-- 8.用连接的方式查询所有学生的信息和考试信息
mysql> select st.*,s.c_name,s.grade from student as st left join score as s on st.id = s.stu_id;

-- 9.计算每个学生的总成绩
mysql> select st.id,st.name,sum(s.grade) as total_grade from student as st left join score as s on st.id = s.stu_id group by st.id,st.name;

-- 10.计算每个考试科目的平均成绩
mysql> select c_name,round(avg(grade),2) as avg_grade from score group by c_name;

-- 11.查询计算机成绩低于95的学生信息
mysql> select distinct st.* from student as st join score as s on st.id =  s.stu_id where s.c_name = '计算机' and s.grade <95;

-- 12.将计算机考试成绩按从高到低进行排序
mysql> select st.name,s.grade from score as s join student as st on s.stu_id = st.id where s.c_name = '计算机' order by s.grade desc;

-- 13.从student表和score表中查询出学生的学号，然后合并查询结果
mysql> select id from student union select stu_id from score;

-- 14.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
mysql> select st.name,st.department,s.c_name,s.grade from student as st join score as s on st.id = s.stu_id where st.name like '张%' or st.name like '王%';

-- 15.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
mysql> select st.name,year(curdate()) - st.birth as age,st.department,s.c_name,s.grade from student as st join score s on st.id = s.stu_id where st.address like '湖南省%';

--附加题：

-- 1.将mydb9_stusys数据库下的student、sc 和course表，备份到本地主机保存为st_msg_bak.sql文件，然后将数据表恢复到自建的db_test数据库中；
-- 逻辑备份数据库、表（Windows系统）
PS C:\Users\xiafe> mysqldump -uroot -pXiaFeng9968# --default-character-set=utf8mb4 mydb9_stusys student sc course teacher > E:\数据库\欧鹏\数据库备份还原练习\st_msg_bak.sql
-- 指定字符集也没有用，导出的还是utf-16，老实使用记事本另存为然后改编码吧
-- 创建新数据库、恢复数据
mysql> create database db_test;
C:\Users\xiafe>mysql -uroot -pXiaFeng9968# --force db_test < E:\数据库\欧鹏\数据库备份还原练习\st_msg_bak_copy.sql
-- 使用参数--force虽然也丢表数据，但是至少把表结构给你恢复了（对了Windows powershell 不支持恢复时的重定向符，换命题提示符）
-- 物理备份
-- 备份失败，物理备份只能备份数据库文件夹，备份不了数据库文件夹中的表
--图形化工具备份，成功

-- 2.在db_test数据库创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。
mysql> use db_test;
mysql> create view stu_info as
    -> select s.sname as 姓名,s.ssex as 性别,c.cname as 课程名,sc.score as 成绩
    -> from student as s
    -> join sc on s.sno = sc.sno
    -> join course c on sc.cno = c.cno;
mysql> show full tables where table_type like 'VIEW';
mysql> select * from stu_info;

-- 3.查看mydb9_stusys库下哪些是视图表；
mysql> use mydb9_stusys;
mysql> show full tables where table_type like 'VIEW';

-- 4.删除视图表
mysql> drop view if exists db_test.stu_info;
mysql> show full tables from db_test where table_type like 'VIEW';
mysql> show tables from db_test;