SQLite多表联合查询

我的微博
本文将从SQLite创建表单开始直到多表查询进行介绍，以供新手练习使用，若对SQL有一定了解、只需了解多表查询的童鞋可从中间分割线开始阅读,分割线后依次介绍了最基础的两张表联合查询和三张表联合查询，多表查询可参考三表联合查询。

准备工作

首先创建一个教师表和一个学生表（2017年高考刚结束的新生。。。）

教师表.png
学生表.png

对应教师表参数依次为 教师姓名、教师工号、教学课程、课程编号
对应学生表参数依次为 学生姓名、学生学号、课程编号、课程考试分数

1. 创建教师表
   CREATE TABLE TEACHER(ID INTEGER PRIMARY KEY AUTOINCREMENT,T_name TEXT,T_id INTEGER,COURSE TEXT,C_id TEXT)

2. 添加教师数据
   INSERT INTO TEACHER(T_name,T_id,COURSE,C_id) VALUES('赵老师',1000,'语文',1100); INSERT INTO TEACHER(T_name,T_id,COURSE,C_id) VALUES('钱老师',1001,'数学',1101); INSERT INTO TEACHER(T_name,T_id,COURSE,C_id) VALUES('孙老师',1002,'英语',1102);
3. 创建学生表
   CREATE TABLE STUDENT(ID INTEGER PRIMARY KEY AUTOINCREMENT,S_name,S_id INTEGER,C_id INTEGER,SCORE INTEGER)
4. 添加学生数据
   INSERT INTO STUDENT(S_name,S_id,C_id,SCORE) VALUES('张三',2017000,1101,75); INSERT INTO STUDENT(S_name,S_id,C_id,SCORE) VALUES('李四',2017001,1102,70); INSERT INTO STUDENT(S_name,S_id,C_id,SCORE) VALUES('王五',2017002,1101,90); INSERT INTO STUDENT(S_name,S_id,C_id,SCORE) VALUES('赵六',2017003,1100,58); INSERT INTO STUDENT(S_name,S_id,C_id,SCORE) VALUES('小明',2017004,1100,79); INSERT INTO STUDENT(S_name,S_id,C_id,SCORE) VALUES('小红',2017005,1101,55);

---

准备工作完成，开始多表查询

INNER JOIN 关键字

(注：AS 关键字起到在ＳＱＬ语句中创建对象的作用)

1. 查询学生都选了什么课?(根据TEACHER表和STUDENT表中的共同)
   SELECT s.S_name,t.COURSE FROM STUDENT AS s INNER JOIN TEACHER AS t WHERE s.C_id = t.C_id
   查询结果：
   学生选课.png
   2.查询每位老师所对的及格学生、学科和成绩；
   及格情况查询：
   SELECT t.T_name,t.COURSE,s.S_name,s.SCORE FROM TEACHER AS t INNER JOIN STUDENT AS s ON t.C_id = s.C_id WHERE s.SCORE > 60;
   查询结果
   及格情况.png

---

左外连接查询介绍

LEFT JOIN关键字（也可写作LEFT OUTER JOIN）

1.向教师表添加一条数据（该教师课程没有学生选择）
INSERT INTO TEACHER(T_name,T_id,COURSE,C_id) VALUES('李老师',1003,'挖掘机原理',1110)
修改后教师表如图

教师表+.png

• 首先我们以教师为左侧表查询教师及其对应的学生 [李老师《挖掘机原理课》没有学生，对应右侧为空（null）]
  SELECT t.T_name,t.COURSE,s.S_name,s.SCORE FROM TEACHER AS t LEFT JOIN STUDENT AS s ON t.C_id = s.C_id
  查询结果
  左连接查询.png
• 因为SQLite不支持右外连接查询，可将学生表作为左侧表，实现左右互换（因李老师的《挖掘机原理》没有对应的左侧学生表，所以李老师的课程未进行查询，应不显示也不为空）
  SELECT s.S_name,s.SCORE, t.T_name,t.COURSE FROM STUDENT AS s LEFT JOIN TEACHER AS t ON t.C_id = s.C_id
  查询结果
  学生为左侧表查询.png

三张表单联表查询示例

（多表联合查询可依次类推）

已准备好如下三张表单：

油品
CREATE TABLE Oils(ID integer PRIMARY KEY AUTOINCREMENT,Brand_id integer,Type_id integer,Level_id integer); INSERT INTO Oils VALUES(1,1,1,1); INSERT INTO Oils VALUES(2,2,2,2); INSERT INTO Oils VALUES(3,3,3,3); INSERT INTO Oils VALUES(4,4,4,4);

创建油品表.png

创建品牌表
CREATE TABLE Brands(ID integer PRIMARY KEY AUTOINCREMENT,Brand text,remark text); INSERT INTO Brands VALUES(1,'金龙鱼','真含量，真可以'); INSERT INTO Brands VALUES(2,'中粮','产业链，好产品'); INSERT INTO Brands VALUES(3,'香满园','每年卖出3亿瓶……'); INSERT INTO Brands VALUES(4,'黑心作坊','吐……')

创建品牌表.png

创建类型表
CREATE TABLE Types(ID integer PRIMARY KEY AUTOINCREMENT,Type text,Remark text); INSERT INTO Types VALUES(1,'葵花油','优'); INSERT INTO Types VALUES(2,'花生油','优'); INSERT INTO Types VALUES(3,'芝麻油','优'); INSERT INTO Types VALUES(4,'地沟油','差');

创建类型表.png

创建等级表
CREATE TABLE Levels (ID integer PRIMARY KEY AUTOINCREMENT,Level text,remark text); INSERT INTO Level(ID,Level) VALUES(1,'一级'); INSERT INTO Level(ID,Level) VALUES(2,'二级'); INSERT INTO Level(ID,Level) VALUES(3,'三级'); INSERT INTO Level(ID,Level) VALUES(4,'四级');

创建等级表.png

多表联合查询数据 ，查询
![Uploading 查询结果_870450.png . . .]
显示各类油品的品牌，类型，等级
SELECT br.Brand,ty.Type,le.Level FROM Oils oi INNER JOIN Brands br ON oi.Brand_id = br.ID INNER JOIN Types ty ON oi.Type_id = ty.ID INNER JOIN Levels le ON oi.Level_id = le.ID

查询结果.png

另附一份 “AND”关键字用法

AND 运算符允许在一个 SQL 语句中的 WHERE 子句中 经行多个条件并存的查询。

• 查询钱老师哪位学生没有及格
  SELECT * FROM STUDENT AS s WHERE s.SCORE < 60 AND s.C_id = 1101
  钱老师的不及格学生.png