1.备份stusys数据库中的course表中数据,要求字段值如果是字符就用双引号标注,字段值之间用
mysql> select * from course
-> into outfile "C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\course.txt"
-> fields terminated by ','
-> optionally enclosed by'"'
-> lines terminated by '?';
2.使用mysqldump备份stusys数据库的course表到本“学号姓名”文件夹下的My_data目录下。
mysqldump -u root -p stusys course >D:\mysqlbak\course.sql
3.备份stusys数据库到本“学号姓名”文件夹下的My_data目录下。
mysqldump -u root -p stusys>D:\mysqlbak\stusys.sql
4.备份MySQL服务器上的所有数据库到本“学号姓名”文件夹下的My_data目录下。
mysqldump -u root -p -all -database >D:\mysqlbak\alldata.sql
5.删除stusys数据库中的course表中数据后,将例13.1备份文件course.txt导入到空表course中。
mysql>delete from course;
mysql>select*from course;
6.删除stusys数据库中各个表后,用例13.3备份文件stusys.sql将其恢复。
mysql -u root -p stusys<D:\mysqlbak\stusys.sql
1.查询student表中所有学生的学号,姓名,专业
use stusys;
select * from student;
desc student;
select sno as "学号",sname as "姓名",speciality as "专业" from student;
2.查询student表中所有列。
select子句指定列的位置时使用 * ,会查询表中的所有列
use stusys;
select *
from student;
3.修改查询结果的列标题
查询student表中所有学生的学生的sno、sname、speciality,并将结果中各列的标题分别修改为学号, 姓名, 专业。
select sno as 学号, sname as 姓名,speciality as 专业
from student;
4.设student1表的表结构和样本数据与student表相同,且已创建和插入数据;在student1表中,列出学号、学分和增加4分后的学分。
use stusys;
set sql_safe_updates=0;
update student1
set tc=tc+4;
select sno as "学号",tc as 增加后的学分 from student1;
select sno as "学号",tc as 学分, tc+4 as 增加后的学分 from student1;
select * from student1;
5. 查询student表中speciality列,消除结果中的重复行。
use stusys;
select distinct speciality as 专业 from student1;
6.查询student表中专业为计算机或性别为女的学生。
select * from student
where speciality = "计算机" or ssex="女";
7.查询score表成绩为92、95的记录。
select * from score
where grade in (92,95);
8.查询student表中不在1998年出生的学生情况。
select * from student
where sbirthday not between "19880101" and "19881231";
9.查询已选课但未参加考试的学生情况。
select * from score
where grade is null;
10.查询student表中姓董的学生情况。
select * from student
where sname like "董%";
9.查询含有“系统”或“数字”的所有课程名称。
select * from course
where cname regexp "系统|数字";
10.求学生的总人数。
select count(*) as 总人数
from student;
11.查询通信专业学生的总人数。
select count(*) as 总人数
from student
where speciality="通信";
12.查询1201课程总分。
select
sum(grade) as 课程总分
from score
where cno="1201";
15.查询8001课程的最高分、最低分、平均成绩。
select
max(grade) as 课程8001最高分, min(grade) as 课程8001最低分,avg(grade) as 课程8001平均分
from score
where cno="8001";
16.查询各门课程的最高分、最低分、平均成绩。
select cno as 课程号,max(grade) as 最高分,min(grade) as 最低分, avg(grade) as 平均成绩
from score
where not grade is null
group by cno;
17.查询平均成绩在90分以上的学生的学号和平均成绩。
select sno as 学号 ,avg(grade) as 平均成绩
from score
group by sno
having avg(grade) > 90;
18.查询至少有5名学生选修且以8开头的课程号和平均分数。
select cno as 课程号,avg(grade) as 平均分
from score
where cno like "8%"
group by cno
having count(*)>5;
19.将计算机专业的学生按出生时间降序排序。
select *
from student
where speciality="计算机"
order by sbirthday desc;
20.查询成绩表中成绩前3位学生的学号、课程号和成绩。
select sno,cno,grade
from score
order by grade desc
limit 0,3;
21.采用交叉连接查询教师和和讲课地点所有可能组合。
select tname ,location
from teacher cross join lecture;
22.查询每个学生选修课程的情况。
select student.*,score.*
from student,score
where student.sno=score.sno;
23.查询选修了数据库系统课程且成绩在80分以上的学生情况。
select a.sno,sname,cname,grade
from student a,score b,course c
where a.sno=b.sno and b.cno=c.cno and cname = "数据库系统"and grade>=80;
24.对22进行自然连接查询。
select *
from student natural join score;
25.查询选修了“1201”课程的成绩高于学号为“191002”的成绩的学生姓名。
select a.cno,a.sno,a.grade
from score a,score b
where a.cno>b.grade and a.cno="1201" and b.cno="1201" and b.sno="191002"
order by a.grade desc;
26.采用左外连接查询教师任课情况。
select tname , cno
from teacher left outer join lecture on (teacher.tno=lecture.tno)
27.采用右外连接查询教师任课情况。
select tno,cname
from lecture right join course on (course.cno=lecture.cno);
28.查询选修了课程号为8001的课程的学生情况。
select *
from student
where sno in
(select sno
from score
where cno="8001");
29.查询选修某课程的学生人数多于4人的教师姓名。
select tname as 教师姓名
from teacher
where tno in
(
select tno
from lecture
where cno in
( select a.cno
from course a, score b
where a.cno=b.cno
group by a.cno
having count(a.cno)>4
)
);
30. 查询比所有通信专业学生年龄都小的学生。
select*
from student
where sbirthday>all
(
select sbirthday
from student
where speciality="通信"
);
31. 查询选修1004课程的学生姓名。
select sname as 姓名
from student
where exists
(select*
from score
where score.sno=student.sno and cno="1004"
);
32.查询性别为女及选修了课程号为4002的学生。
select sno,sname,ssex
from student
where ssex="女"
union
select a.sno,a.sname,a.ssex
from student a,score b
where a.sno=b.sno and b.cno="4002"
————————————————
版权声明:本文为CSDN博主「戏精少女465」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/weixin_68308506/article/details/129842452