(8)多表查询

格式

select 列名列表 from 表名列表 where …

笛卡尔积

有两个集合A,B ，取这两个集合的所有组成情况。
要完成多表查询，就要消除无用的数据。

分类

内连接查询

1. 隐式内连接：使用where字段消除无用数据
   select 列列表 from 表列表[别名] where…
2. 显式内连接：select 查询字段 from 表1 [inner] join 表2 on 条件
3. 内连接查询：1.从哪些表中查询数据 2.查询条件是什么 3. 查询哪些字段

外连接查询

1. 左外连接
   select 字段列表 from 表1 left [outer] join 表2 on 条件
   查询的是左表所有数据以及其交集的部分
2. 右外连接
   select 字段列表 from 表1 right [outer] join 表2 on 条件
   查询的是右表所有数据以及其交集的部分

子查询

概念：查询中嵌套查询，称嵌套查询为子查询。

1. 子查询的结果是单行单列的
   把问题拆分，子查询可以作为条件，使用运算符（=><等等）去判断。
2. 子查询的结果是多行单列的
   子查询可以作为条件，使用运算符in(集合)判断
3. 子查询的结果是多行多列的
   子查询可以作为一张虚拟表；也可以用普通内连接。

案例

CREATE TABLE dept(
	id INT PRIMARY KEY,
	dname VARCHAR(50),
	loc VARCHAR(50)
);
INSERT INTO dept VALUES
(10,"研发部","北京"),
(20,"学工部", "上海"),
(30,"销售部","广州"),
(40,"财务部","深圳");

CREATE TABLE job(
	id INT PRIMARY KEY,
	jname VARCHAR(20),
	descripition VARCHAR(50)
);

INSERT INTO job VALUES
(1,"董事长","管理整个公司"),
(2,"经理","管理部门员工"),
(3,"销售员","向客人推销产品"),
(4,"文员","使用办公软件");

CREATE TABLE emp(
	id INT PRIMARY KEY,
	ename VARCHAR(50),
	job_id INT,
	mgr INT,
	joindate DATE,
	salary DECIMAL(7,2),
	bonus DECIMAL(7,2),
	dept_id INT,
	FOREIGN KEY(job_id) REFERENCES job(id),
	FOREIGN KEY(dept_id) REFERENCES dept(id)
);


INSERT INTO emp VALUES
(1001,"孙悟空",4,1004,"2000-12-17",8000.00,NULL,20),
(1002,"卢俊义",3,1006,"2001-02-20",16000.00,3000.00,30),
(1003,"林冲",3,1006,"2001-02-22",12500.00,5000.00,30),
(1004,"唐僧",2,1009,"2001-04-02",29750.00,NULL,20),
(1005,"李逵",4,1006,"2001-09-28",12500.00,14000.00,30),
(1006,"宋江",2,1009,"2001-05-01",28500.00,NULL,30),
(1007,"刘备",2,1009,"2001-09-01",24500.00,NULL,10),
(1008,"猪八戒",4,1004,"2007-04-19",30000.00,NULL,20),
(1009,"罗贯中",1,NULL,"2001-11-17",50000.00,NULL,10),
(1010,"吴用",3,1006,"2001-09-08",15000.00,0.00,30),
(1011,"沙僧",4,1004,"2007-05-23",11000.00,NULL,20),
(1012,"李逵",4,1006,"2001-12-03",9500.00,NULL,30),
(1013,"小白龙",4,1004,"2001-12-03",30000.00,NULL,20),
(1014,"关羽",4,1007,"2002-01-23",13000.00,NULL,10);



CREATE TABLE salarygrade(
	grade INT PRIMARY KEY,
	losalary INT,
	hisalary INT
);


INSERT INTO salarygrade VALUES
(1,7000,12000),
(2,12000,14000),
(3,14000,20010),
(4,20010,30010),
(5,30010,99990);