day5-mysql子查询

weixin_30562507

于 2019-05-23 15:54:00 发布

阅读量90

点赞数

原文链接：http://www.cnblogs.com/lisglg/p/10912406.html

版权

子查询

子查询是将一个查询语句嵌套在另一个查询语句中，内层查询语句的查询结果，可以作为外层查询语句提供查询条件。子查询中可能包括IN、NOT IN、ANY、ALL、EXISTS和NOT EXISTS等关键字，还包括比较运算符，如“=”、“！=”等；

学生表

班级表

#查询高三班所有的学生
select * from student where c_id in (select c_id from classes where c_type=3);

#查询高三1班所有的学生
select * from student where c_id = (select c_id from classes where c_name='高三1班');

EXISTS

exists对外表逐条查询，每次查询都会查看exists的条件语句，当 exists里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前这条记录，反之如果exists里的条件语句不能返回记录行，则当前的这条记录被丢弃，exists的条件就像一个条件，当能返回结果集则为true，不能返回结果集则为 false

select * from student as A where exists (select * from stu_cou as B where A.s_id=B.s_id);

ALL

对所有数据都满足条件，整个条件才成立

select * from student where id > all(select s_id from stu_cou)

ANY

只要部分数据满足条件，整个条件成立

select * from student where id > any(select s_id from stu_cou)

虚表

Select * from (Select * from student )  stu;

作业

create table system   /*系*/
(
   sys_id int  primary key not null,
   sys_name char(20) not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

insert into system values(1,'软件系');
insert into system values(2,'资环系');
insert into system values(3,'外法系');
insert into system values(4,'机电系');

create table major  /*专业*/
(
    major_id   int primary key not null,
    major_name char(20) not null
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

insert into major values(1,'软件工程');
insert into major values(2,'网络工程');
insert into major values(3,'英语');
insert into major values(4,'资源与环境工程');
insert into major values(5,'德语');
insert into major values(6,'自动化');

create table course  /*课程表*/
(
    course_id int auto_increment primary key not null,
    course_name char(50) not null,
    credit float not null,  /*学分*/
    period int not null     /*学时*/
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
insert into course(course_name,credit,period) values('数学建模  ',10,80);
insert into course(course_name,credit,period) values('计算机网络',8,60);
insert into course(course_name,credit,period) values('日语      ',6,75);
insert into course(course_name,credit,period) values('数据库    ',9,60);
insert into course(course_name,credit,period) values('商业会计  ',10,70);
insert into course(course_name,credit,period) values('电子商务  ',10,50);

create table post/*职称表*/  
(
    post_id int primary key not null,
    post_name char(20) not null 
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
insert into post values(1,'讲师');
insert into post values(2,'助教');
insert into post values(3,'副教授');
insert into post values(4,'教授');

create table students/*学生*/
(
    stu_id int primary key not null,
    stu_name varchar(20) not null,
    stu_age int not null,
    stu_sex char(5) not null,
    grade char(10) not null,
    class char(20) not null,
    sys_id int NOT NULL ,   /*系Id*/
    major_id int NOT NULL   /*专业*/
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
insert into students values(1,'张三','20','男','三年级','901',1,2);
insert into students values(2,'吴红梅','18','男','二年级','902',1,2);
insert into students values(3,'王武','22','男','三年级','903',1,3);
insert into students values(4,'王倩','21','女','三年级','905',1,4);
insert into students values(5,'刘伟','20','男','二年级','905',1,4);
insert into students values(6,'明珍珠','35','女','二年级','902',1,5);
insert into students values(7,'唐任贤','34','男','二年级','902',1,5);
insert into students values(8,'王杰','50','男','二年级','902',1,6);
insert into students values(9,'彭亮','44','男','二年级','902',1,6);
insert into students values(10,'宝罗','32','男','二年级','902',1,2);
insert into students values(11,'陈斌','43','男','二年级','902',1,1);


create table select_course  /* 选课表 */
(
    stu_id int NOT NULL ,
    course_id int NOT NULL ,
    score float ,
    primary key(stu_id,course_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
select * from select_course order by score desc;
select * from select_course order by stu_id asc, score desc;
insert into select_course values(1,1,90);
insert into select_course  values(1,2,87);
insert into select_course  values(2,1,80);
insert into select_course  values(2,5,66);
insert into select_course  values(3,3,94);
insert into select_course  values(3,4,50);
insert into select_course  values(4,1,76);
insert into select_course  values(5,2,82);
insert into select_course  values(5,4,70);
insert into select_course  values(5,1,86);
insert into select_course  values(6,5,80);
insert into select_course  values(7,3,90);
insert into select_course  values(8,6,60);
insert into select_course  values(8,4,86);
insert into select_course  values(9,4,76);
insert into select_course  values(9,6,80);
insert into select_course  values(9,1,90);
insert into select_course  values(10,2,78);
insert into select_course  values(10,4,72);
insert into select_course values(10,6,90);
insert into select_course values(10,5,94);


create table teacher(
    teacher_id int primary key,
    teacher_name char(10),
    teacher_sex char(2),
    post_id int(10),  /* 职务ID*/
    sys_id int(10)  /*系ID*/
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
insert into teacher values(1,'王刚','男',3,1);
insert into teacher values(2,'李云','女',1,2);
insert into teacher values(3,'李忠','男',4,1);
insert into teacher values(4,'费明','男',2,1);
insert into teacher values(5,'周密','男',1,2);
insert into teacher values(6,'陈静','女',2,3);
insert into teacher values(7,'孙力波','男',2,4);
insert into teacher values(8,'肖君','女',3,4);
insert into teacher values(9,'赵辉','男',4,3);


create table teach_lesson /* 授课表 */  /*自动*/
(
    course_id int  NOT NULL ,
    teacher_id int  NOT NULL 
)ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
insert into teach_lesson values(1, 1);
insert into teach_lesson values(1, 2);
insert into teach_lesson values(2, 3);
insert into teach_lesson values(2, 4);
insert into teach_lesson values(3, 5);
insert into teach_lesson values(3, 6);
insert into teach_lesson values(4, 7);
insert into teach_lesson values(4, 8);
insert into teach_lesson values(5, 9);
insert into teach_lesson values(5, 3);
insert into teach_lesson values(5, 4);
insert into teach_lesson values(6, 1);
insert into teach_lesson values(6, 2);
insert into teach_lesson values(6, 3);
insert into teach_lesson values(6, 5);
insert into teach_lesson values(6, 6);




select * from course; /*课程表*/ 
select * from major;  /*专业*/
select * from post;    /*职称表*/
select * from select_course;  /* 选课表 */  
select * from students;   /*学生*/   
select * from studens;  
select * from system;    /*系统*/
select * from teach_lesson;   /* 授课表 */  
select * from teacher;       

#1.查询学生学号及其所选课程的门数(查选课表)          
#2.查询教师号及其所任课程的门数(查授课表)
#3.查询选课两门以上的学生学号及其所选课程的门数                  
#4.查询课程平均成绩在75分以上的学生的学号及其所选课程的门数     
#5.在课程2号,4号,5号中查询课程平均成绩在75分以上的学生的学号,课程平均成绩极其选课门数
#6.查询选课两门以上而且各门课程均及格的学生学号及其所选课程的门数:
#7.查询选修了课程4号的学生学号和成绩,并按成绩降序排列
#8.查询选修了课程3号,4号,5号的学生学号,课程号和成绩,并按课程号升序排列,课程相同在按成绩降序排列
#9.查询所有姓王的学生的姓名和性别
#10.统计学生选课课程总数
#11.查询所有与“刘伟”同年出生的学生姓名、年龄和性别（假设库中只有一个学生的姓名为“刘伟”）
#12.从学生选课库中查询出每门课程被选修的学生人数，并按所选人数的降序排列出课程号和选课人数。
#13.查询和周密老师职称相同的教师的姓名,性别和职称
#14.查询讲授课程号为4号的教师的姓名,性别和职称
#15.查询比 所有男生 总分高的女生的姓名和总分(考虑)
#16.从学生选课库中查询出被3名以上（不含3名）学生选修的所有课程信息。。
#17.从学生选课库中查询出最多选修了1门课（含未选任何课程）的全部学生信息。(考虑一下)
#18.查询选了"数据库"这门课的所有学生详细信息。
#19.查询选了"数据库"这门课的而且成绩必须是及格的所有学生详细信息。
#20. 查询与王刚教师教相同课程的其他教师的详细信息。
#21.查询学生最多的系名。
#22.查询出每门课的课程号以及最高分，平均分，最低分
#23.查询出每门课程的课程号以及选课人数
#24.查询出选课人数必须大于或者等于3门以上的每门课程的课程号以及选课人数
#25.查询出最高成绩的学号，课程号，和成绩 
#26.查询出至少选课了二门课的学生学号,总成绩，平均成绩
#27.查出每个学生的最高成绩
#28.查询出选修了"计算机网络"的学生所有信息
#29.查询出至少选修了三门课的学生姓名，性别，年龄

转载于:https://www.cnblogs.com/lisglg/p/10912406.html

weixin_30562507

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
day5-mysql子查询

子查询子查询是将一个查询语句嵌套在另一个查询语句中，内层查询语句的查询结果，可以作为外层查询语句提供查询条件。子查询中可能包括IN、NOTIN、ANY、ALL、EXISTS和NOT EXISTS等关键字，还包括比较运算符，如“=”、“！=”等；学生表班级表in#查询高三班所有的学生select * from student where c_id ...
复制链接

扫一扫