MySQL经典习题

use xk01;
#1.    从Student表中查询学号为“000000001”同学的学号和姓名。
select StuNo,StuName from student where StuNo="00000001"; 
#2.    从课程表中查询所有的课程信息。
select * from course;
#3.    从课程表中查询课程类别的信息。
select kind from course;
#4.    从课程表中查询课程类别，要求消除值相同的那些行。
select distinct kind from course;
#5.    从学生表中查询所有的信息，要求只显示查询结果的前6行数据。
select * from student limit 6;
#6.    查询课程表中课程编号、课程名称、教师、上课时间、限制选课人数和报名人数。
select CouNo,CouName,teacher,SchoolTime,LimitNum,Willnum from course; 
#7.    查询课程表的信息，要求给出查询结果为：
#课程名称                               课程编号
#SQL Server实用技术    课程编号为：          001
#……
select CouName '课程名称'," " '课程编号为：',CouNo'课程编号'from course;
#8.    在课程表中查询课程类别为“信息技术”而且学分为2的课程信息。
select * from course where kind='信息技术' and Credit='2';
#9.    查询课程表的课程、报告人数与限选人数之比。
select CouName,WillNum/LimitNum from course;
#10.查询课程表中最小的报名人数、最大的报名人数以及平均报名人数。
select min(WillNum),max(WillNum),avg(WillNum) from course;
#11.查询课程信息、报名人数与限选人数之比。要求查询结果按照报名人数升序排序
select *,WillNum/LimitNum from course  order by WillNum;
#12.查询课程表的教师名、课程编号、课程名，要求查询结果首先按照教师名降序排序，教师名相同时，则按照课程编号升序排序。
select teacher,CouNo,CouName from course order by 1 desc,2; 
#13.查询课程编号为“004”、“007”、“013”的课程信息。
select * from course where CouNo in ('004','007','013'); 
#14.查询课程表号不为“004”、“007”、“013”的课程信息和课程名称。
select * from course where CouNo not in ('004','007','013'); 
#15.查询课程名以字母D开始的课程信息。
select * from course where CouName like 'D%'; 
#16.查询课程名以“制作”两字结尾的课程信息。
select * from course where CouName like '%制作';
#17.查询姓名第二字为“宝”的学生信息。
select * from student where StuName like '_宝%';
#18.查询不姓“刘”的学生信息。
select * from student where StuName not like '刘%';
#19.查询课程表中教师姓名未定的课程信息。
select * from course where CouName like 'D%';
#20.查询报告人数少于15人的课程信息。
select * from course where WillNum < '15';
#21.查询报名人数少于25并且多于15人的课程信息。
select * from course where WillNum > '15' and WillNum < '25';
#22.查询报名人数小于等于25并且大于等于15人的课程信息，要求查询结果按照报名人数降序排序。
select * from course where WillNum >= '15' and WillNum <= '25' order by WillNum desc;
#23.查询报名人数多于25或者少于15人的课程信息。要求查询结果按照报名人数降序排序。
select * from course where WillNum > '15' or WillNum < '25' order by WillNum desc;
#24.用户需按照学生姓名查询信息，希望提高查询速度，请建立查询。
create unique index StuName on student (StuName);
#25.按课程类别分组统计各类课程的报名人数。
select kind,sum(WillNum) from course group by kind;
#26.查询“信息技术”类课程的平均报名人数。
select avg(WillNum) from course where kind='信息技术'; 
#27.请按课程类别计算各类课程的平均人数。
select kind,avg(WillNum) from course group by kind;
#28.查询报名人数大于平均人数的课程信息
select * from course where WillNum > (select avg(WillNum) from course);
#29.从班级表中查询班级名，从系部表中查询系部名，合并后显示结果。并按降序显示。
select ClassName,DepartName from class,department where class.DepartNo=department.DepartNo  order by  ClassName desc;
#30.查询班级信息，要求显示班级编号，班级名称，班级所在的系部编号和系部名称。
select class.ClassNo,class.ClassName,department.DepartNo,department.DepartName from class,department where class.DepartNo=department.DepartNo;
#31.查询学生的信息显示信息包括学生基本信息和班级名称。
select student.*,class.ClassName from student,class where class.ClassNo=student.ClassNo;
#32.查询学生选课信息，要求显示姓名，课程名称，志愿号并按姓名和志愿号排序。
select student.StuName,course.CouName,stucou.CouNo from student,course,stucou where student.StuNo=stucou.StuNo and course.CouNo=stucou.CouNo order by course.CouName,stucou.CouNo;
#33.查询学生报名“计算机应用工程系”开设的选修课程情况，显示信息包括学生姓名，课程名称和授课老师。
select student.StuName,course.CouName,course.Teacher from student,course,stucou where student.StuNo=stucou.StuNo and course.CouNo=stucou.CouNo and course.CouNo in( select course.CouNo from course,department where course.DepartNo=department.DepartNo AND department.DepartName='计算机应用工程系');
#34.查询每个班级可以选修的、不是自己所在系部开设的选课的信息，显示信息包括班级、课程名、课程类别、学分、老师、上课时间和报名人数。
select class.ClassName,course.CouName,course.Kind,course.Credit,course.Teacher,course.SchoolTime,course.WillNum from class,course where course.DepartNo<>class.DepartNo;
#35.查询课程类别相同但开课系部不同的课程信息，要求显示课程号、课程名称、课程类别他系部编号，并按照课程编号升序排序查询结果。
select c1.CouNo,c1.CouName,c1.Kind,c1.DepartNo from course c1,course c2 where c1.Kind=c2.Kind and c1.DepartNo<>c2.DepartNo order by c1.CouNo;
#36.查询所有学生报名选课课程的详细情况，要求包括已报名选修课程的学生，也包括未报名选修课程的学生情况，显示内容为学号、课程编号和课程名称。
select stucou.StuNo,course.CouNo,course.CouName from course left join stucou on stucou.CouNo=course.CouNo;
#37.使用右连接查询学生已报名和未报名的课程信息（课程编号、课程名称、学号）
select course.CouNo,course.CouName,stucou.StuNo from course right join stucou on  stucou.CouNo=course.CouNo;
#38.使用全连接查询学生报名信息（课程编号、课程名称、学号）
select course.CouNo,course.CouName,stucou.StuNo from stucou,course where stucou.CouNo=course.CouNo;
#39.查询学生选课信息，要求显示姓名、课程名称、志愿号，并按志愿号和姓名显示。
select student.StuName,course.CouName,stucou.CouNo from student,course,stucou where student.CouNo=stucou.CouNo and stucou.CouNo=course.CouNo order by stucou.CouNo,student.StuName;
#40.在表Course中的课程名称列上建立唯一索引
create unique index SCH on course(CouName);
#41.在StuCou表上创建名为IX_StuNoCouNo的聚集、唯一的复合索引，该索引基于StuNo列和CouNo列创建
create unique index IX_StuNoCouNo on stucou(StuNo,CouNo);
#42.删除StuCou表中的IX_StuNoCouNo索引，练习删除student中的主键索引，然后再回复主键。
drop index IX_StuNoCouNo on stucou;
alter table student drop primary key;
alter table student add primary key (StuNo);
#44.如果你的数据库中已建立了P70页第6的表P，表J，表S和表SPJ,可省略第一小题
#(1)用命令创建表P，表J，表S和表SPJ, 并用primary key 定义主键，用foreign key reference 定义表SPJ的外键。
create table s(sno char(10)primary key,sname char(20),status char(10),city char(20));
create table j(jno char(2),jname char(8),city char(4));
create table p(pno char(2) unique,pname char(6),color char(2),weight int);
create table spj(sno char(2),pno char(2),jno char(2),qty int);
#44.（2）用insert命令向各表添加数据，注意数据添加的顺序。表SPJ应最后添加。
insert into s
values('s1','精益','20','天津');
insert into s
values('s2','盛锡','10','北京');
insert into s
values('s3','东方红','30','北京');
insert into s
values('s4','丰泰盛','20','天津');
insert into s
values('s5','为民','30','上海');

insert into p
values('p1','螺母','红',12);
insert into p
values('p2','螺栓','绿',17);
insert into p
values('p3','螺丝刀','蓝',14);
insert into p
values('p4','螺丝刀','红',14);
insert into p
values('p5','凸轮','蓝',40);
insert into p
values('p6','齿轮','红',30);

insert into j
values('j1','三建','北京');
insert into j
values('j2','一汽','长春');
insert into j
values('j3','弹簧厂','天津');
insert into j
values('j4','造船厂','天津');
insert into j
values('j5','机车厂','唐山');
insert into j
values('j6','无线电厂','常州');
insert into j
values('j7','半导体厂','南京');

insert into spj
values('s1','p1','j1',200);
insert into spj
values('s1','p1','j3',100);
insert into spj
values('s1','p1','j4',700);
insert into spj
values('s1','p2','j2',100);
insert into spj
values('s2','p3','j1',400);
insert into spj
values('s2','p3','j2',200);
insert into spj
values('s2','p3','j4',500);
insert into spj
values('s2','p3','j5',400);
insert into spj
values('s2','p5','j1',400);
insert into spj
values('s2','p5','j2',100);
insert into spj
values('s3','p1','j1',200);
insert into spj
values('s3','p3','j1',200);
insert into spj
values('s4','p5','j1',100);
insert into spj
values('s4','p6','j3',300);
insert into spj
values('s4','p6','j4',200);
insert into spj
values('s5','p2','j4',100);
insert into spj
values('s5','p3','j1',200);
insert into spj
values('s5','p6','j2',200);
insert into spj
values('s5','p6','j4',500);

#44.（3）查询每个供应商供应零件的总量和平均量。
select s.sno, s.sname, sum(spj.qty) as total_qty, avg(spj.qty) as avg_qty from s join spj on s.sno = spj.sno group by s.sno, s.sname;
#44.（4）查询项目零件供应总量大于500的项目号，零件供应总量。
select spj.jno, sum(spj.qty) as supply_total from spj group by spj.jno having sum(spj.qty) > 500;
#44.（5）请完成本题1-5的查询。1、
select sno  from spj where jno='j1';
#44.（5）2、
select sno from spj where jno='j1' and pno='p1';
#44.（5）3、
select sno from spj,p where jno='j1' and spj.pno=p.pno and color='红';
#44.（5）4、
select jno from spj where jno not in
     (select jno from spj,p,s where s.city='天津' and color='红' and s.sno=spj.sno and p.pno=spj.pno);
#44.（5）5、
select pno from spj where sno='s1';
select jnofrom spj where pno='p1' and jno in(select jno from spj where pno='p2');
#45.请完成P130页第5题的查询。（1）、
select sname,cityfrom s;
#（2）、
select pname,color,weightfrom p;
#（3）、
select distinct jno from spj where sno='s1';
#（4）、
select pname,qty from spj,p where p.pno=spj.pno and spj.jno='j2';
#（5）、
select pno from spj,s where s.sno=spj.sno and city='上海';
#（6）、
select jname from spj,s,j where s.sno=spj.sno and s.city='上海' and j.jno=spj.jno;
#（7）、
select jno from spj where jno not in(select distinct jno from spj,s where s.sno=spj.sno and s.city='天津');
#（8）、
update p set color='蓝' where color='红';
#（9）、
update spj set sno='s3' where sno='s5' and jno='j4' and pno='p6';
#（10）、
delete from s where sno='s2';
delete from spj where sno='s2';
#（11）、
insert into spj values('s2','j6','p4',200);
#46.请完成P130页第9题的视图的建立。
-- 先创建视图
create view spj_1 as select sno,pno,qty from spj where jno=(select jno from j where jname='三建');  

-- （1）找出三建工程项目使用的各种零件代码及其数量 
select distinct pno,qty from spj_1;

-- （2）找出供应商s1的供应情况
select distinct pno,qty from spj_1 where sno='s1';
#47.创建一个触发器T_sc1，要求每当在stucou表中插入数据时，向客户端显示一条“记录已添加！”的消息。
delimiter //
create trigger T_sc1
after insert on stucou
for each row
begin
    select '记录已添加！' as message;
end //
delimiter ;
#48.创建一个触发器T_stu，要求每当用户删除student表的记录时，自动显示被删除记录的内容
delimiter //
create trigger T_stu
after delete on student
for each row
begin
    select concat('被删除的记录是：', old.stuno, ' ', old.classno, ' ', old.stuname) as message;
end //
delimiter ;
#49.利用IF UPDATE建立一个触发器T_course，在course表上修改课程类别时，显示信息“课程类别已修改”。
delimiter //
create trigger T_course
after update on course
for each row
begin
    if old.Kind <> new.Kind then
        select '课程类别已修改' as message;
    end if;
end //
delimiter ;

#50.创建触发器T_stcour，实现当插入、更新和删除stucou表的选课数据行时，能同时更新Course表中相应的选课人数WillNum.
create trigger T_stucou 
after update on stucou
for each row
update Course
set WillNum = WillNum + 1
where CouNo = new.CouNo;

create trigger T_stucou 
after update on stucou
for each row 
update Course
set WillNum = WillNum + 1
where CouNo = new.CouNo;

create trigger T_stucou 
after delete on stucou
for each row 
update Course
set WillNum = WillNum - 1
where CouNo = old.CouNo;
#51.若批量插入、更新和删除stucou表的选课数据行时，修改上题的触发器
create trigger T_stucou 
after insert on stucou
for each row 
update Course
set WillNum = WillNum + 1
where CouNo = new.CouNo;

create trigger T_stucou 
after update on stucou
for each row 
update Course
set WillNum = WillNum + IF(NEW.State=0, 1, -1)
where CouNo = new.CouNo;

create trigger T_stucou 
after delete on stucou
for each row
update Course
set WillNum = WillNum - 1
where CouNo = old.CouNo;
#52.创建一个触发器T_sc2，要求每当在stucou表中插入数据时，向客户端显示一条“记录添加二次触发”的消息。（知识点：多个触发器）
delimiter //
create trigger T_sc2
after insert on stucou
for each row
begin
    select '记录添加二次触发' as Message;
end //
delimiter ;
#53.创建一个数据库stuxk,将XK数据库中的表导入其中。
create database stuxk;
use stuxk;
#54.为某个系创建一个视图V_elec.
create view V_elec as
select Student.StuNo, Student.StuName, Course.CouNo, Course.CouName from Student join StuCou on Student.StuNo = StuCou.StuNo
            join Course on StuCou.CouNo = Course.CouNo
where Student.ClassNo in (select ClassNo from Class where DepartNo = '01');
#55.为数据库stuxk创建三个用户user1，user2和user3
create user 'user1'@'%' identified by '123456';
create user 'user2'@'localhost' identified by 'abcdef';
create user 'user3'@'192.168.1.100' identified by 'qwerty';
#56.将视图V_elec的查询权限给用户user2和user3.
grant select on stuxk.V_elec to 'user2'@'localhost', 'user3'@'localhost';