1.连接查询概念
连接查询又称多表查询, 当查询的字段来自多个表时, 就用到了连接查询
2.笛卡尔乘积现象
发生情形: 表1具有m行数据, 表2具有n行数据, 当表1与表2进行连表查询时, 得到的结果有m*n行数据
发生原因: 连表查询时没有指定有有效的连接条件
如何规避: 连接查询时添加有效的连接条件
3.连接查询的分类
1 # 按功能分类: 2 内连接: 3 等值连接 4 非等值连接 5 自连接 6 外链接: 7 左外链接 8 右外链接 9 全外链接10 交叉链接11 # 按SQL语法标准分类:12 SQL92标准:支持内连接13 SQL99标准(推荐):支持内连接+外连接(左外&右外)+交叉连接
4.SQL92标准的连接查询
1 # 等值连接: 2 1).多表等值连接的结果为多表的交集部分 3 2).n张表连接,至少需要n-1个连接条件 4 3).多表的顺序没有要求 5 4).等值连接查询,一般要个表起别名,以便于区分不同表中的同名字段 6 5).连接查询可以搭配其他子句进行查询,如排序子句,分组子句,筛选子句等 7 # 1.简单的连接查询 8 # 案例1: 查询员工名和对应的部门名 9 select last_name, department_name # 查询员工姓名与部门名称10 from employees as e, departments as d # 指定从哪张表中查11 where e.department_id = d.department_id;# 指定两张表之间的连接条件, 即两张表的关联字段12 (注:因为两张表中都有department_id字段,为了让SQL区分同名字段而不产生歧义,在from子句中对表进行起别名,在where子句中用别名来区分字段来自哪张表, e.department_id来自employees表, d.department_id来自departments表)
13 # 2.调换连接的两张表, 查询结果不变14 # 案例2: 上面的查询表的顺序可以调换, 查询结果没有区别15 select last_name, department_name16 from departments as d, employees as e17 where d.department_id = e.department_id; 18 # 3.可以在连接查询时添加筛选条件, 由于where用来连接表, 所以使用and来添加筛选条件19 # 案例3: 查询有奖金的员工名, 部门名20 select last_name, department_name, commission_pct21 from employees as e, departments as d22 where e.department_id = d.department_id # where 用于指定连接条件23 and e.commission_pct isnotnull;# 使用and关键字指定筛选条件24 # 案例4: 查询城市名中第二个字符为o的部门名和城市名25 select department_name, city26 from departments as d, locations as l27 where d.location_id = l.location_id28 and city like '_o%'; # and指定筛选条件, like是模糊查询, '_o%'中的_代表一个任意字符, %代表任意多个任意字符 29 # 3.添加分组子句的连接查询30 # 案例5: 查询每个城市部门的个数31 select count(*), city32 from locations as l, departments as d33 where l.location_id = d.location_id34 groupby city;# group by 子句用来分组35 # 案例6: 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资36 select department_name, manager_id, min(salary)37 from departments as d, employees as e38 where d.department_id = e.department_id39 and commission_pct isnotnull40 groupby department_name; 41 # 4.添加排序子句的连接查询42 # 案例7: 查询每个工种的工种名和员工的个数, 并且按员工个数降序排列43 select job_title, count(*)44 from jobs as j, employees as e45 where j.job_id = e.job_id46 groupby job_title47 order by count(*) desc;# order by 子句用来对字段排序, 默认升序, 加入desc关键字后未降序排列. 48 # 5.三张表或多张表进行连接查询49 # 案例8: 查询员工名, 部门名和所在的城市50 select last_name, department_name, city51 from employees as e, departments as d, locations as l52 where e.department_id=d.department_id and d.location_id=l.location_id # 三张表至少需要两个连接条件
1 # 非等值连接 2 # 1. 非等值连接查询案例数据准备 3 (在实现非等值连接查询案例前需要建立一张表--工资级别表,在SQLyong中执行以下SQL语句,即可获得该表) 4 CREATE TABLE job_grades 5 (grade_level VARCHAR(3), 6 lowest_sal int, 7 highest_sal int); 8 INSERT INTO job_grades 9 VALUES ('A',1000,2999);10 INSERT INTO job_grades11 VALUES ('B',3000,5999);12 INSERT INTO job_grades13 VALUES('C',6000,9999);14 INSERT INTO job_grades15 VALUES('D',10000,14999);16 INSERT INTO job_grades17 VALUES('E',15000,24999);18 INSERT INTO job_grades19 VALUES('F',25000,40000); 20 # 2.工资级别表解释:该表共有3个字段, 分别为grade_level(工资等级, 如A,B,C...), lowest_sal(某一工资级别对应的工资下线), highest_sal(某一工资级别对应的工资上线) 21 # 3.案例: 查询员工的工资和工资级别22 select salary, grade_level23 from employees as e, job_grades as g24 where salary between g.lowest_sal and g.heighest_sal;# 指定查询出的工资要在级别表中某个工资上下线的范围
1 # 自连接2 # 1.自连接:自连接是只一张表自己与自己连接, 一张表用为为两张表, 如下面的案例, 员工编号(employee_id)与领导编号(manager_id)在同一张表中, 所以先通过该表查询出员工的领导编号, 再利用同一张表的领导编号字段查找员工对应的领导名字 3 # 2.案例: 查询员工名和上级领导的名字4 select e.last_name, m.last_name5 from employees as e, employees as m;6 where e.manager_id = m.employee_id;