數(shù)據(jù)庫(kù)基礎(chǔ)(2)

查詢練習(xí)

-- 先創(chuàng)建幾個(gè)表

學(xué)生表students
create table students(
sno varchar(20) primary key,
sname varchar(20) not null,
sgender varchar(20) not null,
sbirth datetime,
class varchar(20)
);

教師表teachers
create table teachers(
tno varchar(20) primary key,
tname varchar(20) not null,
tgender varchar(10) not null,
tbirth datetime,
prof varchar(20) not null,
depart varchar(20) not null
);

課程表course
create table course(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teachers(tno)
);

成績(jī)表score
create table score(
sno varchar(20) not null,
cno varchar(20) not null,
degree decimal,
foreign key(sno) references students(sno),
foreign key(cno) references course(cno),
primary key(sno,cno)
);

--給表中插入數(shù)據(jù)

INSERT INTO students VALUES('101','曾華','男','1977-09-01','95033');
INSERT INTO students VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO students VALUES('103','王麗','女','1976-01-23','95033');
INSERT INTO students VALUES('104','李軍','男','1976-02-20','95033');
INSERT INTO students VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO students VALUES('106','陸君','男','1974-06-03','95031');
INSERT INTO students VALUES('107','王尼瑪','男','1974-02-20','95033');
INSERT INTO students VALUES('108','張全蛋','男','1974-02-10','95031');
INSERT INTO students VALUES('109','趙鐵柱','男','1974-06-03','95031');

INSERT INTO teachers VALUES('804','李誠(chéng)','男','1958-12-02','副教授','計(jì)算機(jī)系');
INSERT INTO teachers VALUES('856','張旭','男','1969-03-12','講師','電子工程系');
INSERT INTO teachers VALUES('825','王萍','女','1972-05-05','助教','計(jì)算機(jī)系');
INSERT INTO teachers VALUES('831','劉冰','女','1977-08-14','助教','電子工程系');

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');

INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('103','6-166','85');
INSERT INTO score VALUES('103','3-105','92');

INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('105','6-166','79');

INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('109','6-166','81');

-- 查詢students表中的所有記錄

mysql> select * from students;
+-----+-----------+---------+---------------------+-------+
| sno | sname     | sgender | sbirth              | class |
+-----+-----------+---------+---------------------+-------+
| 101 | 曾華      | 男      | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |
| 103 | 王麗      | 女      | 1976-01-23 00:00:00 | 95033 |
| 104 | 李軍      | 男      | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |
| 106 | 陸君      | 男      | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼瑪    | 男      | 1974-02-20 00:00:00 | 95033 |
| 108 | 張全蛋    | 男      | 1974-02-10 00:00:00 | 95031 |
| 109 | 趙鐵柱    | 男      | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+---------+---------------------+-------+
9 rows in set (0.00 sec)

-- 查詢students表中的所有記錄的sname、sgender茄猫、class列

mysql> select sname,sgender,class from students;
+-----------+---------+-------+
| sname     | sgender | class |
+-----------+---------+-------+
| 曾華      | 男      | 95033 |
| 匡明      | 男      | 95031 |
| 王麗      | 女      | 95033 |
| 李軍      | 男      | 95033 |
| 王芳      | 女      | 95031 |
| 陸君      | 男      | 95031 |
| 王尼瑪    | 男      | 95033 |
| 張全蛋    | 男      | 95031 |
| 趙鐵柱    | 男      | 95031 |
+-----------+---------+-------+
9 rows in set (0.00 sec)

-- 查詢教師teachers所有的單位depart 即不重復(fù)的depart列
-- distinct 排重

mysql> select distinct depart from teachers;
+-----------------+
| depart          |
+-----------------+
| 計(jì)算機(jī)系        |
| 電子工程系      |
+-----------------+
2 rows in set (0.00 sec)

-- 查詢score表中成績(jī)degree在60~80之間的所有記錄
-- 查詢區(qū)間 where...between...and

mysql> select * from score where degree between 60 and 80; #等于 select * from score where degree > 60 and degree < 80;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
+-----+-------+--------+
4 rows in set (0.00 sec)

-- 查詢score表中成績(jī)?yōu)?5、86或88的記錄
-- 表示或者關(guān)系 in()

mysql> select * from score where degree in(85, 86, 88);
+-----+-------+--------+
| sno | cno   |  degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
+-----+-------+--------+
3 rows in set (0.00 sec)

-- 查詢 students 表中"95031"班或性別為"女"的同學(xué)記錄
-- or 表示或者

mysql> select * from students where class='95031' or sgender='女';
+-----+-----------+---------+---------------------+-------+
| sno | sname     | sgender | sbirth              | class |
+-----+-----------+---------+---------------------+-------+
| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |
| 103 | 王麗      | 女      | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |
| 106 | 陸君      | 男      | 1974-06-03 00:00:00 | 95031 |
| 108 | 張全蛋    | 男      | 1974-02-10 00:00:00 | 95031 |
| 109 | 趙鐵柱    | 男      | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+---------+---------------------+-------+
6 rows in set (0.00 sec)

-- 以 class 降序查詢 students 表的所有記錄
-- 升序asc 降序decs

mysql> select * from students order by class desc;
+-----+-----------+---------+---------------------+-------+
| sno | sname     | sgender | sbirth              | class |
+-----+-----------+---------+---------------------+-------+
| 101 | 曾華      | 男      | 1977-09-01 00:00:00 | 95033 |
| 103 | 王麗      | 女      | 1976-01-23 00:00:00 | 95033 |
| 104 | 李軍      | 男      | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼瑪    | 男      | 1974-02-20 00:00:00 | 95033 |
| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |
| 106 | 陸君      | 男      | 1974-06-03 00:00:00 | 95031 |
| 108 | 張全蛋    | 男      | 1974-02-10 00:00:00 | 95031 |
| 109 | 趙鐵柱    | 男      | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+---------+---------------------+-------+
9 rows in set (0.00 sec)

-- 以cno升序猴抹、degree降序查詢score表的所有記錄
-- 先排cno 后排degree

mysql> select * from score order by cno asc,degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 103 | 6-166 |     85 |
| 109 | 6-166 |     81 |
| 105 | 6-166 |     79 |
+-----+-------+--------+
9 rows in set (0.00 sec)

-- 查詢"95031"班的學(xué)生人數(shù).

mysql> select count(*) from students where class='95031';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

-- 查詢score表中的最高分的學(xué)生學(xué)號(hào)和課程號(hào).(子查詢或者排序)

mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)

-- 查詢每門課的平均成績(jī)
-- avg() where...='...';

select cno,avg(degree) from score where cno='3-105';
select cno,avg(degree) from score where cno='3-245';
select cno,avg(degree) from score where cno='6-166';

-- 如何在一個(gè)語(yǔ)句中實(shí)現(xiàn)上面3條業(yè)務(wù)代碼?
-- group by 分組

mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+
3 rows in set (0.00 sec)

-- 查詢score表中至少有2名學(xué)生選修并以3開頭的課程的平均成績(jī)

select cno,avg(degree) from score
group by cno
having count(cno)>=2
and cno like '3%';

-- 查詢分?jǐn)?shù)在70到90之間的同學(xué)

select sno,degree from score where degree between 70 and 90;

select sno,degree from score where degree>70 and degree<90;

-- 查詢學(xué)生的sname,cno,degree sname在students表 cno,degree在score表

mysql> select * from students;
+-----+-----------+---------+---------------------+-------+
| sno | sname     | sgender | sbirth              | class |
+-----+-----------+---------+---------------------+-------+
| 101 | 曾華      | 男      | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |
| 103 | 王麗      | 女      | 1976-01-23 00:00:00 | 95033 |
| 104 | 李軍      | 男      | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |
| 106 | 陸君      | 男      | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼瑪    | 男      | 1974-02-20 00:00:00 | 95033 |
| 108 | 張全蛋    | 男      | 1974-02-10 00:00:00 | 95031 |
| 109 | 趙鐵柱    | 男      | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+---------+---------------------+-------+
9 rows in set (0.00 sec)
mysql> select * from score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
9 rows in set (0.00 sec)

-- 合并

mysql> select sname,cno,degree from students,score where students.sno = score.sno;
+-----------+-------+--------+
| sname     | cno   | degree |
+-----------+-------+--------+
| 王麗      | 3-105 |     92 |
| 王麗      | 3-245 |     86 |
| 王麗      | 6-166 |     85 |
| 王芳      | 3-105 |     88 |
| 王芳      | 3-245 |     75 |
| 王芳      | 6-166 |     79 |
| 趙鐵柱    | 3-105 |     76 |
| 趙鐵柱    | 3-245 |     68 |
| 趙鐵柱    | 6-166 |     81 |
+-----------+-------+--------+
9 rows in set (0.00 sec)

-- 查詢所有學(xué)生的sno,cname和degree
-- sno,cname來自course , degree來自score

mysql> select * from course;
+-------+-----------------+-----+
| cno   | cname           | tno |
+-------+-----------------+-----+
| 3-105 | 計(jì)算機(jī)導(dǎo)論      | 825 |
| 3-245 | 操作系統(tǒng)        | 804 |
| 6-166 | 數(shù)字電路        | 856 |
| 9-888 | 高等數(shù)學(xué)        | 831 |
+-------+-----------------+-----+
4 rows in set (0.00 sec)

mysql> select * from score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
9 rows in set (0.00 sec)

mysql> select sno,cname,degree from course,score where
    -> course.cno = score.cno;
+-----+-----------------+--------+
| sno | cname           | degree |
+-----+-----------------+--------+
| 103 | 計(jì)算機(jī)導(dǎo)論      |     92 |
| 105 | 計(jì)算機(jī)導(dǎo)論      |     88 |
| 109 | 計(jì)算機(jī)導(dǎo)論      |     76 |
| 103 | 操作系統(tǒng)        |     86 |
| 105 | 操作系統(tǒng)        |     75 |
| 109 | 操作系統(tǒng)        |     68 |
| 103 | 數(shù)字電路        |     85 |
| 105 | 數(shù)字電路        |     79 |
| 109 | 數(shù)字電路        |     81 |
+-----+-----------------+--------+
9 rows in set (0.00 sec)

-- 查詢所有學(xué)生的same、cname和degree列
-- sname來自students cname來自course degree來自score

select sname,cname,degree from students,course,score where
students.sno = score.sno and course.cno = score.cno;

-- 查詢'95031'班每門課的平均分

mysql> select * from students where class='95031';
+-----+-----------+---------+---------------------+-------+
| sno | sname     | sgender | sbirth              | class |
+-----+-----------+---------+---------------------+-------+
| 102 | 匡明      | 男      | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳      | 女      | 1975-02-10 00:00:00 | 95031 |
| 106 | 陸君      | 男      | 1974-06-03 00:00:00 | 95031 |
| 108 | 張全蛋    | 男      | 1974-02-10 00:00:00 | 95031 |
| 109 | 趙鐵柱    | 男      | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+---------+---------------------+-------+
5 rows in set (0.04 sec)

mysql> select * from score where sno in(select sno from students where class='95031');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
6 rows in set (0.00 sec)

mysql> select cno,avg(degree) from score
where sno in(select sno from students where class='95031')
group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+-------+-------------+
3 rows in set (0.00 sec)

-- 查詢選修"3-105"課程的同學(xué)成績(jī)高于'109'號(hào)同學(xué)'3-105'成績(jī)的所有同學(xué)

mysql> select sno,cno,degree from score 
where 
degree>(select degree from score where sno='109' and cno='3-105') 
and 
cno ='3-105';
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
+-----+-------+--------+
2 rows in set (0.00 sec)

-- 查詢和學(xué)號(hào)為108锁荔、101的同學(xué)同年出生的所有學(xué)生的sno蟀给、sname和sbirth列

select year(sbirth) from students where sno in(108,101);

select sno,sname,sbirth from students 
where 
year(sbirth) in 
(select year(sbirth) from students where sno in(108,101));

-- 查詢張旭教師任課的學(xué)生成績(jī)
select cno from course where tno = (select tno from teachers where tname='張旭');

mysql> select sno,cno,degree from score 
where cno=
(select cno from course where tno = 
(select tno from teachers where tname='張旭'));
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 6-166 |     85 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
3 rows in set (0.00 sec)

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市阳堕，隨后出現(xiàn)的幾起案子跋理，更是在濱河造成了極大的恐慌，老刑警劉巖恬总，帶你破解...
沈念sama閱讀 216,470評(píng)論 6贊 501
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件前普，死亡現(xiàn)場(chǎng)離奇詭異，居然都是意外死亡壹堰，警方通過查閱死者的電腦和手機(jī)拭卿，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 92,393評(píng)論 3贊 392
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來贱纠，“玉大人峻厚，你說我怎么就攤上這事∽缓福” “怎么了目木？”我有些...
開封第一講書人閱讀 162,577評(píng)論 0贊 353
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長(zhǎng)。經(jīng)常有香客問我刽射，道長(zhǎng)，這世上最難降的妖魔是什么剃执？我笑而不...
開封第一講書人閱讀 58,176評(píng)論 1贊 292
?港島之戀（遺憾婚禮）
正文為了忘掉前任誓禁，我火速辦了婚禮，結(jié)果婚禮上肾档，老公的妹妹穿的比我還像新娘摹恰。我一直安慰自己，他們只是感情好怒见，可當(dāng)我...
茶點(diǎn)故事閱讀 67,189評(píng)論 6贊 388
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布俗慈。她就那樣靜靜地躺著，像睡著了一般遣耍。火紅的嫁衣襯著肌膚如雪闺阱。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 51,155評(píng)論 1贊 299
城市分裂傳說
那天舵变，我揣著相機(jī)與錄音酣溃，去河邊找鬼。笑死纪隙，一個(gè)胖子當(dāng)著我的面吹牛赊豌，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播绵咱，決...
沈念sama閱讀 40,041評(píng)論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼碘饼，長(zhǎng)吁一口氣：“原來是場(chǎng)噩夢(mèng)啊……” “哼！你這毒婦竟也來了悲伶？” 一聲冷哼從身側(cè)響起艾恼，我...
開封第一講書人閱讀 38,903評(píng)論 0贊 274
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤，失蹤者是張志新（化名）和其女友劉穎拢切，沒想到半個(gè)月后蒂萎，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 45,319評(píng)論 1贊 310
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡淮椰，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 37,539評(píng)論 2贊 332
?白月光啟示錄
正文我和宋清朗相戀三年五慈，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片主穗。...
茶點(diǎn)故事閱讀 39,703評(píng)論 1贊 348
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡泻拦，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出忽媒，到底是詐尸還是另有隱情争拐，我是刑警寧澤，帶...
沈念sama閱讀 35,417評(píng)論 5贊 343
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布晦雨，位于F島的核電站架曹，受9級(jí)特大地震影響隘冲，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜绑雄，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,013評(píng)論 3贊 325
男人毒藥：我在死后第九天來索命
文/蒙蒙一展辞、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧万牺，春花似錦罗珍、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,664評(píng)論 0贊 22
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)核无。三九已至画舌，卻和暖如春曲聂，著一層夾襖步出監(jiān)牢的瞬間朋腋，已是汗流浹背旭咽。一陣腳步聲響...
開封第一講書人閱讀 32,818評(píng)論 1贊 269
情欲美人皮
我被黑心中介騙來泰國(guó)打工，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人揍障。一個(gè)月前我還...
沈念sama閱讀 47,711評(píng)論 2贊 368
代替公主和親
正文我出身青樓，卻偏偏與公主長(zhǎng)得像努释，于是被迫代替她去往敵國(guó)和親伐蒂。傳聞我的和親對(duì)象是個(gè)殘疾皇子饿自，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 44,601評(píng)論 2贊 353