之前做過一個(gè)關(guān)于數(shù)據(jù)庫的使用總結(jié)，里面寫過一些關(guān)于數(shù)據(jù)庫的常用方法的集合，但是我們在實(shí)例工作中，很可能涉及到一需要聯(lián)立多個(gè)表格同時(shí)查詢的情況。而這些也是我們在面試過程中常常會考到的這樣一個(gè)情況，今天我就來用一個(gè)案例，來學(xué)習(xí)一下怎么使用sql的多表查詢。順便鞏固一下我們之前的sql基礎(chǔ)技術(shù)。
例題
現(xiàn)在學(xué)校需要建立一個(gè)數(shù)據(jù)庫，假設(shè)學(xué)校情況如下：一個(gè)系有若干個(gè)專業(yè)，每個(gè)專業(yè)有一個(gè)班，每個(gè)班有若干個(gè)學(xué)生，現(xiàn)在需要建立關(guān)于學(xué)生，班級，系的數(shù)據(jù)庫，關(guān)系模型如下。
STUDENT (stu_id(學(xué)號)，name(姓名)，age(年齡)，class_id(班號))
CLASS (class_id(班號)，spe_name(班級名)，dept_id(系號)stu_data(入學(xué)年份))
DEPARTMENT(dept_id(系號),dept_name(系名))
問題：
1.找出所有名字只有兩個(gè)字的學(xué)生，并且按照年齡的從大到小的排序
2.找出入學(xué)年份在2005年以后的學(xué)生學(xué)號，姓名，所在班級的名稱。
3.找出學(xué)生人數(shù)大于等于3的系的編號和名稱。
首先我們先要自己建立數(shù)據(jù)庫，然后向里面插入數(shù)據(jù)。
//創(chuàng)建STUDENT表
sqlite> create table STUDENT(
...> stu_id int not null primary key,
...> name varchar(20) not null,
...> age int not null,
...> class_id int not null);
//插入數(shù)據(jù)
sqlite> insert into STUDENT (stu_id,name,age,class_id)
...> value (1,"hao",22,11);
//創(chuàng)建CLASS表
sqlite> create table CLASS (
...> class_id int not null primary key,
...> spe_name varchar(20) not null,
...> dept_id int not null,
...> stu_data int not null);
//創(chuàng)建DEPARTMENT表
sqlite> create table DEPARTMENT (
...> dept_id int not null primary key,
...> dept_name varchar(20) not null);
最后表中結(jié)果如下
一、找出所有名字只有兩個(gè)字的學(xué)生，并且按照年齡的從大到小的排序
里面有兩個(gè)重點(diǎn)，一個(gè)匹配字符，一個(gè)是倒序排序。
所以會用到 like 和 order by 兩個(gè)限定條件。
- 通過 “ _ ” 符號，我們來限定所需要顯示的模板。“ _ ”表示一個(gè)占位符，中間可以是任意字符。
- 通過order by實(shí)現(xiàn)對某一列的順序還是倒序，ASC順序，DESC倒序。
因?yàn)橹恍枰獙W(xué)生，所以，我們只需要一個(gè)表就好了。
sqlite> select * from STUDENT where name like "__" order by age DESC;
二、找出入學(xué)年份在2005年以后的學(xué)生學(xué)號，姓名，所在班級的名稱。
分析問題：
學(xué)號、姓名在STUDENT表里面有，而所在班級的名稱就只有CLASS里面才有，所以現(xiàn)在我們就需要從兩個(gè)表里面找東西了。
實(shí)現(xiàn)過程：
- 從CLASS里面找到2005年之后的班級id
- 用班級id在STUDENT里面找學(xué)生的id和姓名
sqlite> select STUDENT.stu_id,STUDENT.name,CLASS.spe_name from STUDENT,CLASS
where CLASS.class_id=(select class_id from CLASS where stu_data>2005)
and CLASS.class_id=STUDENT.class_id;
注意：在關(guān)聯(lián)表查詢的時(shí)候，一定要保證兩個(gè)表中，有一項(xiàng)相同的列，并且給予相等，這樣才能保證兩個(gè)表中得到的數(shù)據(jù)對應(yīng)是一致的。
三、找出學(xué)生人數(shù)大于等于3的系的編號和名稱。
分析問題
從STUDENT里面找到人數(shù)大于3的班級id
從CLASS里面利用班級id找到系的id
從DEPARTMENT里面利用系的id找到系的名稱
select * from DEPARTMENT
where dept_id =
(select dept_id from CLASS where class_id =
( select class_id from STUDENT group by class_id having COUNT(class_id)>3));
