mysql实验训练_数据库实验练习
#① 查询计算机系学⽣的学号、姓名、性别和出⽣⽇期。select sno,sname,ssex,sbirthday
from students
where sdept='计算机';
#② 查询姓“李”的学⽣的学号和姓名。select sno,sname
from students
where sname like '李%';
#③ 查询课程表中先⾏课为空的课程名。select cname
from courses
where precno is null;
#④ 查询考试成绩有不及格的学⽣的学号。select sno
from sc
where grade < 60
group by sno;
#⑤ 求选修了C1 课程或C2 课程的学⽣的学号及成绩。select sno,grade
from sc
where cno='C1' or cno='C2';
#⑥ 查询全体计算机系学⽣的姓名及其年龄。select sname,Timestampdiff(year,sbirthday,now())
from students
where sdept='计算机';
#⑦ 查询计算机系在1986－1987 年之间出⽣的学⽣的姓名。select sname
from students
where sbirthday BETWEEN '1986-1-1' and '1987-12-31' and sdept='计算机';
#⑧ 查询姓“李”且姓名为3个汉字的学⽣学号和姓名。SELECT Sno, Sname
FROM students
WHERE Sname like '李__';
#⑨ 查询选修了两门以上课程的学⽣学号与课程数。 -- ⽅法⼀SELECT Sno AS 学号,Count(Cno) AS 选课门数FROM sc
WHERE Sno IN (SELECT Sno From sc)
GROUP BY sc.Sno
HAVING Count(Cno) > 2;
-- ⽅法⼆select sno,count(cno)
from sc
group by sno having count(cno)>2;
#⑩ 查询选修课程数⼤于等于2 的学⽣的学号、平均成绩和选课门数，并按平均成绩降序排列。select sno,avg(grade),count(cno)
from sc
group by sno having count(cno)>1
order by avg(grade) desc;
#1、多表连接查询#(1) 查询选修了【数据库原理】的计算机系的学⽣学号和姓名。select students.sno,sname,cname
from students,courses,sc
where students.sno=sc.sno and sco=courses.Cno
and cname='数据库原理' and sdept='计算机';
#(2)查询每⼀门课的间接先⾏课(即先⾏课的先⾏课)，显⽰课程编号和该门课程的间接先⾏课编号.select c1o,c1.precno,c2.precno
from courses
c1 left join courses c2 on c1.precno=c2o
-- ⽅法⼆select c1o,c2.precno
from courses as c1,courses as c2
where c1.precno = c2o;
#(3) 查询学⽣的学号、姓名、选修课程的名称和成绩。select sname,sc.sno,cname,grade
FROM students,courses,sc
where students.sno = sc.sno and courseso = sco;
#(4) 查询选修了课程的学⽣姓名。select sname
from students,sc
where sc.sno = students.sno
group by sc.sno;
#(5) 查询所有学⽣的信息和所选修的课程, 要求显⽰学⽣的学号、姓名、课程号及课程名。没有选课的同学对应的选课信息为空。select students.sno,sname,sco,cname
from students LEFT join sc on students.sno = sc.sno
LEFT join courses on courseso=sco;
#(6) 查询所有课程的课程编号、课程名称及选课⼈数，没有被选的课程选课⼈数显⽰为0。select courseso,cname,count(sco)
from courses left join sc on courseso=sco
group by cname,courseso;
#(7) 列出所有学⽣所有可能的选修情况，要求显⽰学⽣的学号、姓名、课程号及课程名。select sno,sname,cno,cname
FROM students,courses;
#(8) 查计算机系的学⽣选修课程数⼤于2 的学⽣的姓名、 平均成绩和选课门数，并按平均成绩降序排列。select
sname,avg(grade),count(cno)
from sc,students
where sc.sno = students.sno and sdept='计算机'
group by sname
having count(cno)>2
order by AVG(grade) desc;
#2、嵌套查询和组合查询操作。#(1) 统计选修了【数据库原理】课程的学⽣⼈数。select count(sno)
from sc
group by cno
having cno in(select cno from courses where cname='数据库原理')
#(2) 查询没有选修【数据库原理】课程的学⽣学号、姓名。select sno,sname
mysql中delete语句
from students
where sno not in
(select sno from sc where cno in
(select cno from courses where cname='数据库原理'))
#(3) 查询其他系中⽐计算机系学⽣年龄都⼩的学⽣学号、姓名和所在系。select sno,sname,sdept
from students
where sdept<>'计算机' and sbirthday>all(
select sbirthday from students where sdept='计算机');
#(4) 查询被0602001 学⽣或0602002 学⽣所选修的课程的课程号(⽤UNION 组合查询与IN 条件查询两种⽅法实现)。select cno from sc
where sno='0602001'
UNION SELECT cno from sc where sno='0602002';
-- ⽅法⼆select DISTINCT cno from sc
where sno in('0602001','0602002');
#(5) 查询0602001 学⽣和0602002 学⽣同时选修的课程的课程号(⽤IN ⼦查询与EXISTS 嵌套⼦查询两种⽅法实现)。select cno from sc
WHERE sno='0602001' and cno in
(select cno from sc where sno='0602002')
-- ⽅法⼆select cno
from sc s1
WHERE sno='0602001' and
EXISTS
(select cno from sc s2 where sno='0602002' and s1o=s2o)
#(6) 查询被学号0602001 学⽣选修，但没有被0602002 学⽣所选修的课程的课程号。select cno
from sc
where sno='0602001' and cno not in
(select cno from sc where sno='0602002')
#① 向表Students 中插⼊(0601001，赵林, 男,1985-09-08，计算机)的记录。insert into students VALUES ('0601001','赵
林','男','1985-09-08','计算机',null);
#② 向SC 表中添加⼀个学⽣的选课记录，学号为0601001，所选的课程号为C2。 SC表中有Sno、Cno、Grade这3个列。这⾥只知道学号和课程号，不知道成绩值。insert into sc(sno,cno) values('0601001','C2');
#③ 向表Students 中插⼊(0601002，张修⾬，default)记录，该记录的数据中default 表⽰默认值‘男’，其他数据为空值。insert into students VALUES ('0601002','张修⾬',default,null,null,null);
#④ ⽤CREATE 语句创建表StudentBAK1，包含(与Students 的Sno、Sname、Sdept 相同)3 个字段，然后⽤INSERT SELECT 语句实现向StudentBAK1 添加Students 表中的计算机系学⽣的学号、姓名、所在系的信息。CREATE table StudentBAK1
(Sno char(7) PRIMARY key,
Sname varchar(20),
Sdept varchar(10)
);
insert into studentbak1
select sno,sname,sdept from students where sdept='计算机';
#⑤ ⽤ Create Table … AS … 语句实现把Students 表中1986 年后(包含1986 年)出⽣的学⽣的学号、姓名存储到⼀个新表StudentBAK2。create table StudentBAK2
as select sno,sname
from students
where year(sbirthday) >= 1986;
#⑥ 将Students表中姓名为【赵林】的同学所在系改为【机电】，爱好改为【⾜球】。update students
set sdept='机电',memo='⾜球'
where sname='赵林';
#⑦ 将选修了课程名为【数据库原理】并且有成绩的学⽣成绩加5 分。update sc
set grade = grade+5
where cno=(select cno from courses where cname='数据库原理');
#⑧ 将Test 数据库的StudentBAK1 表中所有姓赵的同学删除。delete from studentbak1
where sname like '赵%';
#⑨ 删除机电系课程成绩不及格或者没有登记成绩的学⽣选课记录。delete from sc
where (grade<60 and sno=any(select sno from students where sdept='机电'))
or grade is null;
#⑩ 将Test 数据库的StudentBAK2 表中的所有⾏删除。delete from studentbak2;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。