android SQLite room 连表查询

创建三张示例表

---

方法一：
将文件中的命令全部复制，在MariaDB中粘贴回车即可。
方法二：
将此文件当做数据库的备份，使用数据库恢复命令即可。

文件下载链接

创建好效果如下

---

MariaDB [scott]> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| salgrade        |
+-----------------+

三张表中的内容如下

---

MariaDB [scott]> select * from dept;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

MariaDB [scott]> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+

MariaDB [scott]> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

group by子句

---

显示每个部分的平均工资和最高工资。

MariaDB [scott]> select deptno, avg(sal), max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal)    | max(sal) |
+--------+-------------+----------+
|     10 | 2916.666667 |  5000.00 |
|     20 | 2175.000000 |  3000.00 |
|     30 | 1566.666667 |  2850.00 |
+--------+-------------+----------+
3 rows in set (0.00 sec)

显示每个部门的每种岗位的平均工资和最低工资。

MariaDB [scott]> select deptno, job, avg(sal), min(sal) from emp group by deptno, job;
+--------+-----------+-------------+----------+
| deptno | job       | avg(sal)    | min(sal) |
+--------+-----------+-------------+----------+
|     10 | CLERK     | 1300.000000 |  1300.00 |
|     10 | MANAGER   | 2450.000000 |  2450.00 |
|     10 | PRESIDENT | 5000.000000 |  5000.00 |
|     20 | ANALYST   | 3000.000000 |  3000.00 |
|     20 | CLERK     |  950.000000 |   800.00 |
|     20 | MANAGER   | 2975.000000 |  2975.00 |
|     30 | CLERK     |  950.000000 |   950.00 |
|     30 | MANAGER   | 2850.000000 |  2850.00 |
|     30 | SALESMAN  | 1400.000000 |  1250.00 |
+--------+-----------+-------------+----------+
9 rows in set (0.00 sec)

显示平均工资低于2000的部门和它的平均工资。

MariaDB [scott]> select deptno, avg(sal) as sal_avg from emp group by deptno having sal_avg < 2000;
+--------+-------------+
| deptno | sal_avg     |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)

多表查询

---

显示雇员名，雇员工资以及所在部门的名字。

MariaDB [scott]> select emp.ename, emp.sal, dept.dname
    -> from emp, dept
    -> where emp.deptno = dept.deptno;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| SMITH  |  800.00 | RESEARCH   |
| ALLEN  | 1600.00 | SALES      |
| WARD   | 1250.00 | SALES      |
| JONES  | 2975.00 | RESEARCH   |
| MARTIN | 1250.00 | SALES      |
| BLAKE  | 2850.00 | SALES      |
| CLARK  | 2450.00 | ACCOUNTING |
| SCOTT  | 3000.00 | RESEARCH   |
| KING   | 5000.00 | ACCOUNTING |
| TURNER | 1500.00 | SALES      |
| ADAMS  | 1100.00 | RESEARCH   |
| JAMES  |  950.00 | SALES      |
| FORD   | 3000.00 | RESEARCH   |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+

显示部门号为10的部门名，员工名和工资。

MariaDB [scott]> select ename, sal, dname 
    -> from emp, dept 
    -> where emp.deptno = dept.deptno and dept.deptno = 10;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| CLARK  | 2450.00 | ACCOUNTING |
| KING   | 5000.00 | ACCOUNTING |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+

显示各个员工的姓名，工资和工资级别。

MariaDB [scott]> select ename, sal, grade
    -> from emp, salgrade
    -> where emp.sal between salgrade.losal and salgrade.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

自连接

---

显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号–empno）。

MariaDB [scott]> select leader.empno, leader.ename
    -> from emp leader, emp worker
    -> where worker.mgr = leader.empno and worker.ename = 'ford';
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+

子查询

---

单行子查询

---

显示smith同一部门的员工。

MariaDB [scott]> select * from emp where deptno = (select deptno from emp where ename = 'smith');
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007369 | SMITH | CLERK   | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
| 007876 | ADAMS | CLERK   | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL |     20 |
| 007902 | FORD  | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+---------+------+---------------------+---------+------+--------+

多行子查询

---

in关键字

---

查询和10号部门的工作相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的。

MariaDB [scott]> select ename, job, sal, deptno
    -> from emp 
    -> where job in (select distinct job from emp where deptno = 10) and deptno != 10;
+-------+---------+---------+--------+
| ename | job     | sal     | deptno |
+-------+---------+---------+--------+
| SMITH | CLERK   |  800.00 |     20 |
| JONES | MANAGER | 2975.00 |     20 |
| BLAKE | MANAGER | 2850.00 |     30 |
| ADAMS | CLERK   | 1100.00 |     20 |
| JAMES | CLERK   |  950.00 |     30 |
+-------+---------+---------+--------+

all关键字

---

显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号。

MariaDB [scott]> select ename, sal, deptno
    -> from emp
    -> where sal > all (select sal from emp where deptno = 30);
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| JONES | 2975.00 |     20 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+

any关键字

---

显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号。

MariaDB [scott]> select ename, sal, deptno 
    -> from emp
    -> where sal > any (select sal from emp where deptno = 30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| CLARK  | 2450.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| KING   | 5000.00 |     10 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
| MILLER | 1300.00 |     10 |
+--------+---------+--------+

多列子查询

---

查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人。

MariaDB [scott]> select *
    -> from emp
    -> where (deptno, job) = (select deptno, job from emp where ename = 'smith') and ename != 'smith';
+--------+-------+-------+------+---------------------+---------+------+--------+
| empno  | ename | job   | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+-------+------+---------------------+---------+------+--------+
| 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL |     20 |
+--------+-------+-------+------+---------------------+---------+------+--------+

子查询在from子句中的使用

---

子查询出现在from子句中, 可以将子查询的结果当做一个临时表使用

---

显示高于自己部门平均工资的员工的姓名、部门、工资、平均工资。

MariaDB [scott]> select emp.ename, emp.deptno, emp.sal, temp.avg_sal 
    -> from emp, (select avg(sal) as avg_sal, deptno from emp group by deptno) temp
    -> where emp.sal > temp.avg_sal and emp.deptno = temp.deptno;
+-------+--------+---------+-------------+
| ename | deptno | sal     | avg_sal     |
+-------+--------+---------+-------------+
| ALLEN |     30 | 1600.00 | 1566.666667 |
| JONES |     20 | 2975.00 | 2175.000000 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| KING  |     10 | 5000.00 | 2916.666667 |
| FORD  |     20 | 3000.00 | 2175.000000 |
+-------+--------+---------+-------------+

查找每个部门工资最高的人的姓名、工资、部门、最高工资。

MariaDB [scott]> select emp.ename, emp.sal, emp.deptno, temp.max_sal
    -> from emp, (select max(sal) as max_sal, deptno from emp group by deptno) temp
    -> where emp.deptno = temp.deptno and emp.sal = temp.max_sal;
+-------+---------+--------+---------+
| ename | sal     | deptno | max_sal |
+-------+---------+--------+---------+
| BLAKE | 2850.00 |     30 | 2850.00 |
| SCOTT | 3000.00 |     20 | 3000.00 |
| KING  | 5000.00 |     10 | 5000.00 |
| FORD  | 3000.00 |     20 | 3000.00 |
+-------+---------+--------+---------+

合并查询

---

union操作符

---

取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。
将工资大于2500或职位是MANAGER的人找出来。

MariaDB [scott]> select ename, sal, job from emp where sal > 2500 
    -> union
    -> select ename, sal, job from emp where job = 'manager';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
| CLARK | 2450.00 | MANAGER   |
+-------+---------+-----------+

union all操作符

---

取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。
将工资大于25000或职位是MANAGER的人找出来

MariaDB [scott]> select ename, sal, job from emp where sal > 2500
    -> union all 
    -> select ename, sal, job from emp where job = 'manager';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| CLARK | 2450.00 | MANAGER   |
+-------+---------+-----------+

从上面可以看出，union、union all的功能通过or也可以完成，为什么还要使用union、union all？

• 首先，or的功能和union类似；
• 其次，union和union all前后两个select可以针对不对的表进行查询，只要保证返回的结果集表头是相同的，这一点，or没办法做到；
• 数据量比较大的时候，union和union all的效率比or高。

表的内连接和外连接

---

内连接

---

内连接实际上就是利用where子句对两种表形成的笛卡尔积进行筛选，前面提到的查询都是内连接，也是在开发过程中使用的最多的连接查询。
语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;

示例：
显示smith的名字和部门名称：

--前面的写法
MariaDB [scott]> select ename, dname from emp, dept 
    -> where emp.deptno = dept.deptno
    -> and ename = 'smith';
+-------+----------+
| ename | dname    |
+-------+----------+
| SMITH | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)

--标准的内连接写法
MariaDB [scott]> select ename, dname from
    -> emp inner join dept on emp.deptno = dept.deptno
    -> and ename = 'smith';
+-------+----------+
| ename | dname    |
+-------+----------+
| SMITH | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)

外连接

---

左外连接

---

如果联合查询，左侧的表完全显示我们就说是左外连接。
语法：

select 字段名 from 表名1 left join 表名2 on 连接条件;

示例：
创建两张表。

MariaDB [lab330]> create table student(
    -> id int primary key auto_increment comment '序号', 
    -> name varchar(10) not null comment '姓名'
    -> );
Query OK, 0 rows affected (0.01 sec)

MariaDB [lab330]> create table exam(
    -> id int primary key auto_increment comment '序号', 
    -> grade int not null comment '成绩'
    -> );
Query OK, 0 rows affected (0.01 sec)

两张表中插入一些数据。

MariaDB [lab330]> insert into student(name) values
    -> ('范庄元'), 
    -> ('林英新'), 
    -> ('孙悟空'), 
    -> ('胡南'), 
    -> ('邢玉莹'), 
    -> ('郭梦杰');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [lab330]> insert into exam(grade) values
    -> (69), (91), (66);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

查询所有学生的成绩，如果这个学生没有成绩，也要将这个学生的个人信息显示出来。

MariaDB [lab330]> select * from student
    -> left join exam 
    -> on student.id = exam.id;
+----+-----------+------+-------+
| id | name      | id   | grade |
+----+-----------+------+-------+
|  1 | 范庄元     |    1 |    69 |
|  2 | 林英新     |    2 |    91 |
|  3 | 孙悟空     |    3 |    66 |
|  4 | 胡南       | NULL |  NULL |
|  5 | 邢玉莹     | NULL |  NULL |
|  6 | 郭梦杰     | NULL |  NULL |
+----+-----------+------+-------+
6 rows in set (0.00 sec)

右外连接

---

如果联合查询，右侧的完全显示我们就说是右外连接。
语法：

select 字段 from 表名1 right join 表名2 on 连接条件;

示例：
先插入几条数据。

MariaDB [lab330]> insert into exam(grade) values
    -> (73), (82), (60), (19), (39), (47);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

对student表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来。

MariaDB [lab330]> select * from student 
    -> right join exam on student.id = exam.id;
+------+-----------+----+-------+
| id   | name      | id | grade |
+------+-----------+----+-------+
|    1 | 范庄元     |  1 |    69 |
|    2 | 林英新     |  2 |    91 |
|    3 | 孙悟空     |  3 |    66 |
|    4 | 胡南       |  4 |    73 |
|    5 | 邢玉莹     |  5 |    82 |
|    6 | 郭梦杰     |  6 |    60 |
| NULL | NULL      |  7 |    19 |
| NULL | NULL      |  8 |    39 |
| NULL | NULL      |  9 |    47 |
+------+-----------+----+-------+
9 rows in set (0.00 sec)

内连接和外连接的区别

---

内连接：连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。
外连接：连接结果不仅包含符合连接条件的行同时也包含自身不符合条件的行。包含左外连接、右外连接、全外连接。

• 左外连接(left join)：左边数据行全部保留，右边表保留符合连接条件的行；
• 右外连接(right join)：右边表数据行全部保留，左边表保留符合连接条件的行；
• 全外连接(full join)：左外连接 union 右外连接。