Mysql连接查询DQL之sql92语法

缺乏、安全感 2023-06-30 09:12 6阅读 0赞

## 连接查询（多表查询） ##

当查询的字段来自于多张表的时候用的是连接查询

## 内连接 ##

**1.等值连接**

查询员工名和对应的部门名

select e.ename，d.departname 
    from employee e,department d 
    where e.departmentid = d.departmentid

查询有奖金的员工名和部门名

//加筛选
    select e.ename，d.departname 
    from employee e,department d 
    where e.departmentid=d.departmentid and e.commission is not null

查询城市名中第二个字符为o的部门名和城市名

//加筛选
    select d.departname,c.city 
    from department d,cities c 
    where d.locationid=c.locationid and c.city like '_o%'

查询每个城市的部门个数

//添加分组
    select count(*),c.city 
    from department d,cities c 
    where d.locationid=c.locationid 
    group by city;

查询出有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

//添加分组
    select d.departname,d.managerid,min(salary) 
    from depaetment d,employee e
    where d.departid=e.departid and commission is not null 
    group by d.departname,d.managerid

查询每个工种的工种名和员工的个数，并且按照员工个数降序排序

//加上排序
    select job_title,count(*) 
    from employee e,jobs j 
    where e.jobid = j.jobid 
    group by job_title 
    order by count(*) desc;

查询员工名，部门名和所在的城市,并查询带有s开头的城市，按照部门名升序

select lastname,departname,city 
    from employee e,department d,cities c
    where e.departid = d.departid and d.departid=c.departid and city like 's%' 
    group by departname asc;

> 多表的的等值连接的结果就是交集部分  
> n表连接，至少需要n-1个连接的条件  
> 多表连接的顺序没有要求  
> 一般要为表起别名  
> 可以搭配所有的子句比如筛选，排序，分组

**2.非等值连接(用的不太多)**  
查询出员工的工资和工资级别

select salary，grade_level 
    from employee e,jobgrades j 
    where salary between j.low and j.high 
    and grage_level = 'A';

**3.自连接(一张表按照多张表操作)**  
查询员工和上级的名称

select e.eid,e.lastname,m.eid,m.lastname 
    from employee e,employee m 
    where e.managerid = m.eid;