在線SQL 測試網(wǎng)站:http://www.sqlfiddle.com/徽鼎,這個(gè)網(wǎng)站有時(shí)訪問不太穩(wěn)定。
下面的表和題出自:https://blog.csdn.net/mrbcy/article/details/68965271
先創(chuàng)建表:
create table students(
sno varchar(3) not null,
sname varchar(4) not null,
ssex varchar(2) not null,
sbirthday datetime,
class varchar(5)
);
create table courses(
cno varchar(5) not null,
cname varchar(10) not null,
tno varchar(10) not null
);
create table scores(
sno varchar(3) not null,
cno varchar(5) not null,
degree numeric(10, 1) not null
);
create table teachers(
tno varchar(3) not null,
tname varchar(4) not null,
tsex varchar(2) not null,
tbirthday datetime not null,
prof varchar(6),
depart varchar(10) not null
);
insert into students (sno,sname,ssex,sbirthday,class) values (108 ,'曾華' ,'男' ,'1977-09-01',95033);
insert into students (sno,sname,ssex,sbirthday,class) values (105 ,'匡明' ,'男' ,'1975-10-02',95031);
insert into students (sno,sname,ssex,sbirthday,class) values (107 ,'王麗' ,'女' ,'1976-01-23',95033);
insert into students (sno,sname,ssex,sbirthday,class) values (101 ,'李軍' ,'男' ,'1976-02-20',95033);
insert into students (sno,sname,ssex,sbirthday,class) values (109 ,'王芳' ,'女' ,'1975-02-10',95031);
insert into students (sno,sname,ssex,sbirthday,class) values (103 ,'陸君' ,'男' ,'1974-06-03',95031);
insert into courses(cno,cname,tno) values ('3-105' ,'計(jì)算機(jī)導(dǎo)論',825);
insert into courses(cno,cname,tno) values ('3-245' ,'操作系統(tǒng)' ,804);
insert into courses(cno,cname,tno) values ('6-166' ,'數(shù)據(jù)電路' ,856);
insert into courses(cno,cname,tno) values ('9-888' ,'高等數(shù)學(xué)' ,100);
insert into scores(sno,cno,degree) values (103,'3-245',86);
insert into scores(sno,cno,degree) values (105,'3-245',75);
insert into scores(sno,cno,degree) values (109,'3-245',68);
insert into scores(sno,cno,degree) values (103,'3-105',92);
insert into scores(sno,cno,degree) values (105,'3-105',88);
insert into scores(sno,cno,degree) values (109,'3-105',76);
insert into scores(sno,cno,degree) values (101,'3-105',64);
insert into scores(sno,cno,degree) values (107,'3-105',91);
insert into scores(sno,cno,degree) values (108,'3-105',78);
insert into scores(sno,cno,degree) values (101,'6-166',85);
insert into scores(sno,cno,degree) values (107,'6-106',79);
insert into scores(sno,cno,degree) values (108,'6-166',81);
insert into teachers(tno,tname,tsex,tbirthday,prof,depart) values (804,'李誠','男','1958-12-02','副教授','計(jì)算機(jī)系');
insert into teachers(tno,tname,tsex,tbirthday,prof,depart) values (856,'張旭','男','1969-03-12','講師','電子工程系');
insert into teachers(tno,tname,tsex,tbirthday,prof,depart) values (825,'王萍','女','1972-05-05','助教','計(jì)算機(jī)系');
insert into teachers(tno,tname,tsex,tbirthday,prof,depart) values (831,'劉冰','女','1977-08-14','助教','電子工程系');
查詢Score表中成績?cè)?0到80之間的所有記錄
select * from scores where degree > 60 and degree < 80
或者:
select * from scores where degree between 60 and 80
查詢Score表中成績?yōu)?5枢纠,86或88的記錄
select * from scores where degree=85 or degree=86 or degree=88
或者:
select * from scores where degree in (85,86,88)
查詢Student表中“95031”班或性別為“女”的同學(xué)記錄
select * from students where class='95033' or ssex='女'
以Class降序查詢Student表的所有記錄霎槐。
select * from students order by class desc
以Cno升序、Degree降序查詢Score表的所有記錄
select * from scores order by cno asc, degree desc
或者:
select * from scores order by cno, degree desc
查詢“95031”班的學(xué)生人數(shù)
select count(1) as sum from students where class='95031'
查詢Score表中的最高分的學(xué)生學(xué)號(hào)和課程號(hào)
原來想的是:select max(degree) from scores
发钝,但這樣沒有顯示出學(xué)號(hào)和課程號(hào)淤刃,
后來想出:select sno,cno from scores where max(degree)
晒他,沒這樣用的。逸贾。陨仅。還是不對(duì)
正確的應(yīng)該是:
select sno,cno from scores group by degree desc limit 1
或者
select sno,cno from scores order by degree desc limit 1
order by 是排序, group by 是分組铝侵,兩者在這里都可以實(shí)現(xiàn)此功能
又想到幾種實(shí)現(xiàn)方式:
select sno,cno,max(degree) from scores
或者:
select sno,cno from scores having degree=max(degree)
查詢‘3-105’號(hào)課程的平均分
select avg(degree) from scores where cno='3-105'
查詢Score表中至少有5名學(xué)生選修的并以3開頭的課程的平均分?jǐn)?shù)灼伤。
like 操作符用于在 where 子句中搜索列中的指定模式。
like 經(jīng)常和通配符一起使用咪鲜,比如上面的例子中狐赡,以3開頭的課程
就可以這樣實(shí)現(xiàn):where cno like '3%'
而至少5名學(xué)生選修
,該怎么實(shí)現(xiàn)疟丙?
我第一次想到的是:where count(sno) >= 5
颖侄,但是:
在 SQL 中,where 關(guān)鍵字無法與 count享郊、sum览祖、max、min炊琉、avg
等合計(jì)函數(shù)一起使用展蒂,所以只能用 having : having count(sno) >= 5
。
而且 having 要放在 where 的后面:
select avg(degree) from scores where cno like '3%' having count(sno) >= 5
上面的sql語句 是不對(duì)的苔咪,我們翻譯一下: 所有符合以3開頭的課程锰悼,這些課程個(gè)數(shù)相加大于等于5?是的团赏,那我們對(duì)這些課程求平均值
看著是否有些別扭箕般,是的,我們應(yīng)該是求 滿足 至少5名學(xué)生選修
且 以3開頭的
每一個(gè)課程 求平均值舔清,所以我們要對(duì)課程 通過 group by
進(jìn)行分組丝里。
要記得:當(dāng)我們使用 avg
可柿、sum
這類統(tǒng)計(jì)相關(guān)的函數(shù)時(shí),看是否需要使用 group by
來對(duì)某一列進(jìn)行分組丙者,看是否是對(duì) 某一列下的同一屬性進(jìn)行統(tǒng)計(jì),譬如:對(duì)分?jǐn)?shù)表中营密,每個(gè)班的分?jǐn)?shù) 求平均值械媒,便是這樣的例子。
那么上面的題目要這樣實(shí)現(xiàn)(group by 放在 where 后面评汰,having 放在 group by 后面):
select cno, avg(degree) from scores
where cno like '3%' group by cno
having count(cno) >= 5
我們是求符合要求的課程纷捞,所以列最好添加課程:cno
查詢最低分大于70,最高分小于90的Sno列
原來是這樣求的:
select sno, degree from scores where degree>70 and degree<90 group by sno
上面對(duì)題目理解錯(cuò)了被去,上面是求 分?jǐn)?shù)在 70 和 90 之間的學(xué)生主儡,而題目是求 最低分大于70 且 最高分小于90,要這樣寫:
select sno from scores group by sno having max(degree)<90 and min(degree)>70
查詢所有學(xué)生的Sname惨缆、Cno和Degree列
select sname, cno, degree
from students join scores on students.sno = scores.sno
或者(inner join 等同于 join):
select sname, cno, degree
from students inner join scores on students.sno = scores.sno
或者(給表名 起別名):
select sname, cno, degree
from students st join scores sc on st .sno = sc .sno
查詢所有學(xué)生的Sno糜值、Cname和Degree列
select sno, cname, degree
from courses join scores on courses.cno = scores.cno
查詢所有學(xué)生的Sname、Cname和Degree列
需要三個(gè)表關(guān)聯(lián)坯墨,考察了多個(gè)join的使用
select sname, cname, degree
from students join scores on students.sno = scores.sno
join courses on courses.cno = scores.cno
查詢“95033”班所選課程的平均分
原來是這樣寫的:
select class, avg(degree)
from students join scores on students.sno = scores.sno
where class='95033' group by class
應(yīng)該是按 課程進(jìn)行分組寂汇,上面 group by class 加上沒啥鳥用。捣染。骄瓣。,正確寫法:
select class, cname, avg(degree)
from students join scores on students.sno = scores.sno
join courses on scores.cno=courses.cno
where class='95033' group by courses.cno
現(xiàn)查詢所有同學(xué)的Sno耍攘、Cno和rank列榕栏。
先創(chuàng)建表:grade,并插入數(shù)據(jù):
create table grade(low numeric(10, 1), upp numeric(10, 1), rank char(1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
考察 join 的另一種用法
select sno, cno, rank
from scores join grade
on scores.degree between grade.low and grade.upp
或者:
select sno, cno, rank
from scores join grade
on scores.degree>=grade.low and scores.degree<=grade.upp
查詢選修“3-105”課程的成績高于“109”號(hào)同學(xué)成績的所有同學(xué)的記錄
select sno, degree
from scores where cno='3-105' having degree>(select degree from scores
where cno='3-105' and sno='109')
或者通過 自己關(guān)聯(lián)自己蕾各,來實(shí)現(xiàn):
select s1.sno, s1.degree
from scores s1 join Scores s2
on(s1.cno=s2.cno and s1.Degree>s2.Degree)
WHERE s1.cno='3-105' and s2.sno='109'
查詢成績高于學(xué)號(hào)為“109”扒磁、課程號(hào)為“3-105”的成績的所有記錄
這一題和上面一題的實(shí)現(xiàn)方式一樣,剛開始寫時(shí) 忘記在 on 后添加: s1.cno=s2.cno
示损,其實(shí)題目沒有說清渗磅,不添加就是表示所有高于 109、 3-105 的成績检访,但想想 添加更符合邏輯始鱼,因?yàn)楸容^同一課程的成績才有意義。
select s1.* from scores s1
join scores s2 on s1.cno=s2.cno and s1.degree>s2.degree
where s2.sno='109' and s2.cno='3-105'
查詢score中選學(xué)一門以上課程的同學(xué)中分?jǐn)?shù)為非最高分成績的記錄
原來想的是:
select *
from scores group by sno having count(sno)>1
然后就不知道 非最高分成績
這個(gè)怎么實(shí)現(xiàn)了脆贵。医清。。卖氨,還以為有什么函數(shù)可以實(shí)現(xiàn)此功能会烙,想多了……负懦,下面是正確實(shí)現(xiàn)方式:
select *
from scores group by sno having count(sno)>1 and degree!=max(degree)
查詢和學(xué)號(hào)為105的同學(xué)同年出生的所有學(xué)生的Sno、Sname和Sbirthday列
datetime 格式的數(shù)據(jù)柏腻,有幾個(gè)函數(shù):
正確使用姿勢:
select s1.* from students s1
join students s2 on year(s1.sbirthday)=year(s2.sbirthday)
where s2.sno='105'
查詢“張旭“教師任課的學(xué)生成績
要關(guān)聯(lián)3個(gè)表:
select sno, degree
from teachers
join courses on teachers.tno=courses.tno
join scores on courses.cno=scores.cno
where teachers.tname='張旭'
查詢選修某課程的同學(xué)人數(shù)多于5人的教師姓名
select tname
from teachers join courses on teachers.tno=courses.tno
join scores on courses.cno=scores.cno
group by scores.cno having count(scores.cno)>5
查詢95033班和95031班全體學(xué)生的記錄
select * from Students
where class='95033' or class='95031'
或者:
select * from Students
where class in ('95033', '95031')
查詢存在有85分以上成績的課程Cno
原來想的是:
select cno, degree from scores
where degree>85
結(jié)果是:
有重復(fù)的 cno纸厉,題目應(yīng)該是列出一個(gè)即可,我們用 distinct 去重:
select distinct cno from scores
where degree>85
查詢選修編號(hào)為“3-105“課程且成績至少高于任意選修編號(hào)為“3-245”的同學(xué)的成績的Cno五嫂、Sno和Degree,并按Degree從高到低次序排序
至少高于任意
這句話的意思是:選3-105課程同學(xué)的成績颗品,只要高出一個(gè)選3-245同學(xué)的成績,就滿足條件沃缘。 要用 any
來表示:
select * from scores
where cno='3-105'
and degree > any(select degree from scores where cno='3-245')
order by degree desc
3-245同學(xué)成績最高為:86分躯枢,最低為75分:
查詢選修編號(hào)為“3-105”且成績高于所有選修編號(hào)為“3-245”課程的同學(xué)的Cno、Sno和Degree.
這一題和上一題的區(qū)別是 不是至少了槐臀,而是高于所有锄蹂,意思是:選3-105同學(xué)的成績,要高出所有選3-245同學(xué)的成績水慨,才滿足條件得糜。用 all
來實(shí)現(xiàn):
select * from scores
where cno='3-105'
and degree > all(select degree from scores where cno=3-245'')
order by degree desc
只有高于3-245最高分:86分 的選3-105的同學(xué)才滿足條件:
查詢所有教師和同學(xué)的name、sex和birthday.
合并相同的列晰洒,要通過 union
來實(shí)現(xiàn):union 內(nèi)部的 select 語句必須擁有相同數(shù)量的列掀亩。列也必須擁有相似的數(shù)據(jù)類型。同時(shí)欢顷,每條 select 語句中的列的順序必須相同槽棍。
select tname,tsex,tbirthday from teachers
union
select sname,ssex,sbirthday from students
查詢所有“女”教師和“女”同學(xué)的name、sex和birthday
和上題類似抬驴,只是添加了條件過濾:
select tname,tsex,tbirthday from teachers where tsex='女'
union
select sname,ssex,sbirthday from students where ssex='女'
查詢成績比該課程平均成績低的同學(xué)的成績表
select * from scores
where degree<(select avg(degree) from scores group by cno)
或者:
select s1.* from students s1 join (
select sno, avg(degree) degree
from scores
group by cno) s2
on s1.cno=s2.cno and s1.degree<s2.degree
上面這種實(shí)現(xiàn)方式 join 的表是在括號(hào)內(nèi)實(shí)時(shí)生成的炼七,要學(xué)會(huì)這種關(guān)聯(lián)方式。
查詢所有任課教師的Tname和Depart
原來以為下面的方式即可:
select tname,depart
from teachers
其實(shí)不然布持,這些教師不一定都任課豌拙,所以要在courses 中匹配:
select tname,depart
from teachers
where tno in (
select tno from courses
)
查詢所有未講課的教師的Tname和Depart
select tname,depart
from teachers
where tno not in (
select tno from courses
)
查詢至少有2名男生的班號(hào)
select class, count(1) boyCount from students
where ssex='男'
group by class
having count(class)>=2
查詢Student表中不姓“王”的同學(xué)記錄
select * from students
where sname not like '王%'
查詢Student表中每個(gè)學(xué)生的姓名和年齡
計(jì)算年應(yīng),要算出今年的年份 減去 出生的年份题暖,通過 now() 函數(shù)得到現(xiàn)在的時(shí)間按傅,通過 year() 得到年份:
select sname, year(now()) - year(sbirthday) age from students
以班號(hào)和年齡從大到小的順序查詢Student表中的全部記錄
select * from students
group by class
order by class desc, sbirthday asc
原來以為 要通過 group by 對(duì) class 進(jìn)行分組,其實(shí)不用胧卤,直接對(duì) class 和 asc 進(jìn)行 order by 即可唯绍,正確使用姿勢:
select * from students
order by class desc, sbirthday asc
查詢“男”教師及其所上的課程
select t.tname, c.cname
from teachers t join courses c
on t.cno=c.cno and t.tsex='男'
注意:關(guān)聯(lián)的時(shí)候,on 后面跟的條件枝誊,必須是兩個(gè)表都包含的屬性况芒,像 and 后面的 t.tsex='男'
則不應(yīng)該放在這里,正確語句:
select t.tname, c.cname
from teachers t join courses c
on t.cno=c.cno
where t.tsex='男'
查詢每科最高分同學(xué)的Sno叶撒、Cno和Degree列
select * from scores
group by cno
having degree=max(degree)
查詢和“李軍”同性別的所有同學(xué)的Sname
select s1.sname from students s1
join students s2
on s1.ssex=s2.ssex
where s2.sname='李軍'
查詢所有選修“計(jì)算機(jī)導(dǎo)論”課程的“男”同學(xué)的成績表
兩種方式绝骚,第一種:
select *
from scores sc
join students s on s.sno=sc.sno
join courses c on c.cno=sc.cno
join teachers t on t.tno=c.tno
where c.cname='計(jì)算機(jī)導(dǎo)論' and s.ssex='男'
第二種:
select *
from scores
where Sno in (
select Sno
from students
where Ssex='男') and
Cno in (
select Cno
from Courses
where Cname='計(jì)算機(jī)導(dǎo)論');
這種方式比較獨(dú)特耐版,多體會(huì)一下