本文介绍的select高级应用主要包括:联合查询、分组查询、嵌套查询和限定查询数目等,与实例对照演示,所使用数据库为sqlite3。
部门表(dept)
CREATE TABLE dept(
id INT PRIMARY KEY NOT NULL,
name TEXT NOT NULL);
INSERT INTO dept(id,name) VALUES(11,'tech');
INSERT INTO dept(id,name)VALUES(22,'finace');
INSERT INTO dept(id,name) VALUES(33,'hr');
部门表的内容如下:
id | name |
11 | tech |
22 | finace |
33 | hr |
雇员表(employee)
CREATE TABLE employee (
id INT PRIMARY KEY NOT NULL,
name TEXT NOT NULL,
dept_id INT NOT NULL);
INSERT INTO employee (id,name,dept_id)VALUES(1,'ame',11);
INSERT INTO employee (id,name,dept_id)VALUES(2,'beck',11);
INSERT INTO employee (id,name,dept_id)VALUES(3,'beck',11);
INSERT INTO employee (id,name,dept_id)VALUES(4,'mike',11);
INSERT INTO employee (id,name,dept_id)VALUES(5,'john',22);
INSERT INTO employee (id,name,dept_id)VALUES(6,'stan',22);
INSERT INTO employee (id,name,dept_id)VALUES(7,'lily',22);
INSERT INTO employee (id,name,dept_id)VALUES(8,'lory',33);
INSERT INTO employee (id,name,dept_id)VALUES(9,'koby',33);
雇员表的内容如下:
id | name | dept_id |
1 | ame | 11 |
2 | beck | 11 |
3 | star | 11 |
4 | mike | 11 |
5 | john | 22 |
6 | stan | 22 |
7 | lily | 22 |
8 | lory | 33 |
9 | koby | 33 |
1、联合查询:JOIN
查询得到每个雇员id、雇员姓名以及其所在部门名称。
SELECT e.id,e.name,d.name FROM employee e INNER JOIN dept d ON e.dept_id = d.id;
2、分组查询:GROUP BY
查询各部门的id以及员工人数。
SELECT dept_id,COUNT(*) emp_count FROM employee GROUP BY dept_id;
3、嵌套查询:FROM/IN/EXISTS
查询各部门的id、名称以及员工人数,这里将SELECT子句的查询结果和数据表dept内连接后,用作FROM的数据子集。
SELECT d.id,d.name,e_cnt.how_many num_employee FROM dept d INNER JOIN
(SELECT dept_id,COUNT(*) how_many FROM employee e GROUP BY dept_id) e_cnt
ON d.id = e_cnt.dept_id;
另外查询子句还可与IN/EXISTS等配合使用。
IN关键字用于where子句中用来判断查询的表达式是否在多个值的列表中;示例中选出hr部门对应的员工及dept_id。
SELECT name,dept_id FROM employee WHERE dept_id IN (SELECT id FROM dept where name='hr');
EXISTS是sql中的逻辑运算符号。如果子查询有结果集返回,那么就为True;示例中SELECT 1就有结果返回。
SELECT name,dept_id FROM employee EXISTS(SELECT 1);
4、限定查询数目:LIMIT
只想查询人数最多的部门id及员工人数,可以用limit1,表示只取第1条记录。
SELECT dept_id,COUNT(*) emp_count FROM employee
GROUP BY dept_id order by emp_count desc limit 1;
以上所有语句均在sqlite3中实操并确认有效,有兴趣的朋友也可以实践下。