超有用的sql查詢語句。項(xiàng)目、面試都有用

create database cstp;
use cstp;
/*1:建立學(xué)生表*/
create table student (
?? 學(xué)號(hào) char(3) primary key,
?? 姓名 char(4),
?? 性別 char(2),
?? 年齡 int,
?? 班級(jí) char(5)
);
insert into student values('108','曾華','男',19,'95033');
insert into student values('105','匡明','男',20,'95031');
insert into student values('107','王麗','女',20,'95033');
insert into student values('101','李軍','男',19,'95033');
insert into student values('109','王芳','女',22,'95031');
insert into student values('103','陸君','男',20,'95031');
/*2:建立教師表*/
create table teacher(
?? 教師號(hào) char(3) primary key,
?? 姓名 char(4),
?? 性別 char(2),
?? 年齡 int ,
?? 級(jí)別 char(6),
?? 專業(yè) char(8)
);
insert into teacher values('804','李成','男',42,'副教授','計(jì)算機(jī)系');
insert into teacher values('856','張旭','男',35,'講師','電子工程');
insert into teacher values('825','王萍','女',28,'助教','計(jì)算機(jī)系');
insert into teacher values('831','劉冰','女',25,'助教','電子工程');
/*3:建立課程表*/
create table course (
?? 課程號(hào) char(5) primary key,
?? 課程名 char(10),
?? 教師號(hào) char(3),
?? foreign key(教師號(hào)) references teacher(教師號(hào))
);
insert into course values('3-105','計(jì)算機(jī)導(dǎo)論','825');
insert into course values('3-245','操作系統(tǒng)','804');
insert into course values('6-166','數(shù)字電路','856');
insert into course values('9-888','高等數(shù)學(xué)','831');
/*4:建立選課表*/
create table sc(
?? 學(xué)號(hào) char(3),
?? 課程號(hào) char(5),
?? primary key(學(xué)號(hào),課程號(hào)),
?? 成績(jī) int,
?? foreign key(學(xué)號(hào)) references student(學(xué)號(hào)),
?? foreign key(課程號(hào)) references course(課程號(hào))
);
insert into sc values('103','3-245',86);
insert into sc values('105','3-245',75);
insert into sc values('109','3-245',68);
insert into sc values('103','3-105',92);
insert into sc values('105','3-105',88);
insert into sc values('109','3-105',76);
insert into sc values('101','3-105',64);
insert into sc values('107','3-105',91);
insert into sc values('108','3-105',78);
insert into sc values('101','6-166',85);
insert into sc values('107','6-166',79);
insert into sc values('108','6-166',81);
/*5:所有表內(nèi)容*/
select * from student;
select * from course;
select * from teacher;
select * from sc;

所有表信息如下:

student(學(xué)生)表 ?	course(課程)表 ?
teacher(教師)表 ?	sc(選課)表 ?

作業(yè)題:
1.查詢選修課程'3-105'且成績(jī)?cè)?0到80之間的所有記錄。
注釋:用于指定某個(gè)范圍使用between and,也可以使用and連接符;
答案:
法1:select * from sc where 課程號(hào)='3-105' and 成績(jī) between 60 and 80;
法2:select * from sc where 課程號(hào)='3-105' and 成績(jī) > 60 and 成績(jī) < 80;
2.查詢成績(jī)?yōu)?5、86或88的記錄。
注釋:用于制定某個(gè)集合使用 in 關(guān)鍵字,也可以使用 or 連接符;
答案:
法1:select * from sc where 成績(jī)=85 or 成績(jī)=86 or 成績(jī)=88;
法2:select * from sc where 成績(jī) in(85,86,88);
3.查詢'95031'班的學(xué)生人數(shù)。
注釋:count(*)用于計(jì)算結(jié)果總數(shù);
答案:
select count(班級(jí)) as 學(xué)生人數(shù) from student where 班級(jí)='95031';
4.查詢最低分大于70,且最高分小于90的學(xué)號(hào)列。
注釋:having后面跟聚合函數(shù):avg,min,max,count;having語句只能跟在:group by語句后面;
答案:
select 學(xué)號(hào),min(成績(jī))as 最低分,max(成績(jī))as 最高分 from sc group by 學(xué)號(hào) having min(成績(jī))>70 and max(成績(jī)) < 90;
5.查詢至少有5名學(xué)生選修并以3開頭的課程的平均成績(jī)。
注釋:group by 語句置于where語句后面表示根據(jù)什么條件來分組;
答案:
select avg(成績(jī)) as 平均成績(jī) from sc where 課程號(hào) like '3%' group by 課程號(hào) having count(課程號(hào)) >= 5;
6.查詢平均分大于80分的學(xué)生的成績(jī)表
注釋:having后面跟聚合函數(shù)avg();
答案:
select * from sc group by 學(xué)號(hào) having avg(成績(jī)) > 80;
7.查詢'95033'班每個(gè)學(xué)生所選課程的平均分。
注釋:此題是根據(jù) '95033'班學(xué)生的學(xué)號(hào)進(jìn)行的分組,使用where語句限制group by語句的分組條件;
答案:
select 學(xué)號(hào),avg(成績(jī)) as 平均成績(jī) from sc where 學(xué)號(hào) in (select 學(xué)號(hào) from student where 班級(jí) = '95033') group by 學(xué)號(hào);
8.以選修 '3-105'為例,查詢成績(jī)高于'109'號(hào)同學(xué)的所有同學(xué)的記錄。
注釋:此題使用典型的嵌套查詢,層層深入;
答案:
select * from student where 學(xué)號(hào) in (select 學(xué)號(hào) from sc where 課程號(hào)='3-105' and 成績(jī) >
(select 成績(jī) from sc where 學(xué)號(hào)='109' and 課程號(hào)='3-105'));
9.查詢與學(xué)號(hào)為'108'的同學(xué)同歲的所有學(xué)生的學(xué)號(hào)、姓名和年齡。
注釋:當(dāng)查詢的結(jié)果集返回只有一個(gè)時(shí)關(guān)鍵字in的作用等價(jià)于'='的作用,但是注意'='只能用與返回結(jié)果集只有一個(gè),而 in 可以有多個(gè)結(jié)果;
答案:
法1:select 學(xué)號(hào),姓名,年齡 from student where 年齡 = (select 年齡 from student where 學(xué)號(hào)='108');
法2:select 學(xué)號(hào),姓名,年齡 from student where 年齡?in (select 年齡 from student where 學(xué)號(hào)='108');
10.查詢'張旭'教師任課的課程號(hào)，選修其課程學(xué)生的學(xué)號(hào)和成績(jī)。
注釋:此題使用了表的連接:inner join,將多個(gè)表連接在一起組成一個(gè)新的表,標(biāo)的連接的條件是必須存在相同的列;
答案:
法1:select 課程號(hào) from course where 教師號(hào)=(select 教師號(hào) from teacher where 姓名='張旭'); select 學(xué)號(hào),成績(jī) from sc where 課程號(hào) in(select 課程號(hào) from course where 教師號(hào) in
(select 教師號(hào) from teacher where 姓名='張旭'));
法2:select teacher.姓名 as 教師姓名,course.課程號(hào),student.姓名 as 學(xué)生姓名,student.學(xué)號(hào),成績(jī) from? teacher inner join (course inner join (sc inner join student on student.學(xué)號(hào)=sc.學(xué)號(hào)) on course.課程號(hào)=sc.課程號(hào)) on course.教師號(hào)=teacher.教師號(hào) where? teacher.姓名='張旭';
11.查詢選修其課程的學(xué)生人數(shù)多于5人的教師姓名。
注冊(cè):此題采用嵌套查詢和連接表兩種方法,表現(xiàn)出了同樣的效果;
答案:
法1:select 姓名 as 教師姓名 from teacher where 教師號(hào) in (select 教師號(hào) from course where 課程號(hào) in (select 課程號(hào) from sc group by 課程號(hào) having count(*) > 5));
法2:select 姓名 as 教師姓名 from teacher inner join (course inner join sc on course.課程號(hào)=sc.課程號(hào)) on teacher.教師號(hào)=course.教師號(hào) group by sc.課程號(hào) having count(*) > 5;
12.查詢'計(jì)算機(jī)系'與'電子工程系'不同職稱的教師的姓名和職稱。
注釋:不同職稱意味著分組后的返回結(jié)果集只有一個(gè);
答案:
select 姓名 as 教師姓名,級(jí)別 as 職稱 from teacher group by 級(jí)別 having count(*) = 1;
13.查詢選修編號(hào)為'3-105'課程且成績(jī)至少高于選修編號(hào)為'3-245'課程的同學(xué)的課程號(hào)、學(xué)號(hào) 、成績(jī)并按成績(jī)從高到低次序排列。
注釋:any 表示任意一個(gè);
答案:
select * from sc where 課程號(hào)='3-105' and 成績(jī) > any (select 成績(jī) from sc where 課程號(hào)='3-245') order by 成績(jī) desc;
14.查詢選修編號(hào)為'3-105'課程且成績(jī)高于選修編號(hào)為'3-245'課程的同學(xué)的課程號(hào)、學(xué)號(hào) 、成績(jī)。
注釋:all 表示所有;
答案:
select * from sc where 課程號(hào)='3-105' and 成績(jī) > all (select 成績(jī) from sc where 課程號(hào)='3-245') order by 成績(jī) desc;
15.列出所有教師和同學(xué)的姓名 、性別 、年齡。
注釋:此題將所有表連接在一起:
答案:
select student.姓名 as 學(xué)生姓名,student.性別 as 學(xué)生性別,student.年齡 as 學(xué)生年齡,
teacher.姓名 as 教師姓名,teacher.性別 as 教師性別,teacher.年齡 as 教師年齡 from
student inner join (sc inner join (course inner join teacher on course.教師號(hào)=teacher.教師號(hào))
on sc.課程號(hào)=course.課程號(hào)) on student.學(xué)號(hào)=sc.學(xué)號(hào);
16.查詢成績(jī)比'3-105'課程的平均成績(jī)低的學(xué)生的成績(jī)表。
答案:
select * from sc where 成績(jī) < (select avg(成績(jī)) from sc where 課程號(hào)='3-105') and 課程號(hào)='3-105';
17.查詢成績(jī)比該課程平均成績(jī)低的學(xué)生的成績(jī)表。
注釋:此題是所有題中最難的,下面的方法很經(jīng)典,請(qǐng)認(rèn)真琢磨,先按照課程進(jìn)行分組,查出小于各門課程平均成績(jī)的所有成績(jī);
答案:
select * from sc where 成績(jī) < any (select avg(成績(jī)) from sc group by 課程號(hào)) order by 課程號(hào) asc;
18.列出所有任課教師的姓名和專業(yè)。
注釋:姓名和專業(yè)涉及兩個(gè)表course表和teacher表,只需連接這兩個(gè)表就能解決此題了;
答案:
select teacher.姓名 as 教師姓名,專業(yè) from teacher where 教師號(hào) in (select 教師號(hào) from course where 課程號(hào) in (select 課程號(hào) from sc group by 課程號(hào)));
19.列出所有未講課教師的姓名和專業(yè)。
注釋:解答同上題;
答案:
select teacher.姓名 as 教師姓名,專業(yè) from teacher where 教師號(hào) not in
(select 教師號(hào) from course where 課程號(hào) in (select 課程號(hào) from sc group by 課程號(hào)));
20.列出至少有2名男生的班號(hào)。
注釋:根據(jù)性別='男'這個(gè)條件來對(duì)student表進(jìn)行分組就可以了;
答案:
select 班級(jí) as 班號(hào) from student where 性別='男' group by 班級(jí) having count(*) >= 2;
21.查詢不姓'王'的學(xué)生記錄。
答案:
法1:select *? from student where 姓名 not like '王%';
法2:select * from student where 姓名 not in (select 姓名 from student where 姓名like '王%');
22.查詢每門課最高分的學(xué)生的學(xué)號(hào)、課程號(hào)、成績(jī)。
注釋:此題的技巧在于根據(jù)最高分來查成績(jī)表;
答案:
select * from sc where 成績(jī) in (select max(成績(jī)) from sc group by 課程號(hào));
23.查詢與'李軍'同性別并同班的同學(xué)名字。
注釋:嵌套查詢;
答案:
select 姓名 from student where 性別=(select 性別 from student where 姓名='李軍') and
班級(jí)=(select 班級(jí) from student where 姓名='李軍');
24.查詢'男'教師及其所上的課程。
注釋:先分析此題涉及的表;
答案:
select 姓名 as 教師名,課程名 as 課程 from teacher inner join course on teacher.教師號(hào)=course.教師號(hào) where 性別='男';
25.查

超有用的sql查詢語句。項(xiàng)目、面試都有用