数据库之多表查询

关于Struts2 + spring + hibernate进Q群: 130529143交流。
有偿技术支持Q群：398162181 

--查询含有下划线的：

SQL> select last_name FROM employees WHERE last_name LIKE  '%\_%' ESCAPE '\';

select  last_name || '赚了' || to_char(salary, 'L999,999.999') || '但是他想赚' || to_char(salary * 2.5, 'L999,999.999') "理想的工资是实际的2.5倍"

from employees order by months_between(sysdate, hire_date) desc

select  last_name, job_id, decode(job_id, 'AD_PRES', 'A',
                                          'ST_MAN', 'B',
                                          'IT_PROG', 'C',
                                          'SA_REP', 'D',
                                          'ST_CLERK', 'F') "Grade"
from employees order by "Grade" asc



SELECT  last_name, job_id,CASE job_id WHEN 'AD_PRES'THEN 'A'
                                      WHEN 'ST_MAN' THEN 'B'
                                      WHEN 'IT_PROG' THEN 'C'
                                      WHEN 'SA_REP'THEN 'D'
                                      WHEN 'ST_CLERK' THEN 'F'
                                      ELSE 'G' END "Grade"
from employees order by "Grade" ASC



SELECT employee_id, last_name, salary, grade_level
FROM employees e, job_grades j
WHERE e.salary between j.lowest_sal AND j.highest_sal

--自连结
SELECT emp.last_name, man.last_name, man.salary, man.email
FROM  employees emp, employees man 
WHERE emp.manager_id = man.employee_id AND LOWER(emp.last_name) = 'chen'



--左外连接（在右表中加（+），除返回符合条件的行，还返左表中不符合条件的行 ）
--右外连接（在左表中加（+）除返回符合条件的行，还返右表中不符合条件的行）
SELECT employee_id, last_name, department_name
FROM  employees e, departments d
WHERE e.department_id = d.department_id(+)



--满外连接
SELECT employee_id, last_name,d.department_id, department_name
FROM  employees e 
FULL OUTER JOIN departments d
ON e.department_id = d.department_id



--左外连接
SELECT employee_id, last_name,d.department_id, department_name
FROM  employees e 
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id



--右外连接
SELECT employee_id, last_name,d.department_id, department_name
FROM  employees e 
RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id



--两个表的  列名与数据类型  需相同
SELECT employee_id, last_name,department_id, department_name
FROM  employees join departments
USING (department_id)



--使用JOIN 多表查询
SELECT employee_id, last_name,d.department_id, department_name
FROM  employees e JOIN departments d
ON e.department_id = d.department_id



--使用JOIN 多表查询
SELECT employee_id, last_name,d.department_id, department_name, l.city 
FROM  employees e 
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l 
ON d.location_id = l.location_id



select department_id, avg(salary)
from employees 
where department_id in (40,60)
group by department_id



--不是组函数的列都要出现在group by 中 
select department_id, job_id, avg(salary)
from employees 
group by department_id, job_id



--不能在where子句中使用组函数，where要紧跟 from 之后
--可以在having子句中使用组函数， having 无要求

select avg(salary), locations.city 
from employees e, departments, locations
where departments.department_id = e.department_id 
and departments.location_id = locations.location_id
group by locations.city



select manager_id, min(salary)
from employees e
where manager_id is not null 
having min(salary) >= 6000
group by e.manager_id



select department_name, d.location_id, count(employee_id), avg(salary)
from employees e, departments d, locations l
where e.department_id(+) = d.department_id and d.location_id = l.location_id
group by department_name, d.location_id



--查询公司在1995-1998年每年雇佣的人数
select count(employee_id) "Total", 
count(decode(to_char(hire_date, 'yyyy'), '1995', 1, null)) "1995",
count(decode(to_char(hire_date, 'yyyy'), '1996', 1, null)) "1996",
count(decode(to_char(hire_date, 'yyyy'), '1997', 1, null)) "1997",
count(decode(to_char(hire_date, 'yyyy'), '1998', 1, null)) "1998"
from employees e
where to_char(hire_date, 'yyyy') in ('1995', '1996', '1997', '1998')



--子查询
select last_name
from employees 
where salary > (select salary from employees where last_name = 'Abel')


--子查询
select last_name, salary, email 
from employees 
where employee_id = (select manager_id from employees where last_name = 'Chen')



--子查询:查询过滤条件是另一个查询的结果
select last_name, job_id, salary
from employees 
where job_id = (select job_id from employees where employee_id = 141)
and salary > (select salary from employees where employee_id = 143)



select last_name, job_id, salary
from employees 
where job_id = (select job_id 
                from employees 
                where employee_id = 141)
and salary > (select salary 
              from employees 
              where employee_id = 143)



select employee_id, last_name, job_id, salary
from employees 
where job_id != 'IT_PROG' 
and salary < any (
               select salary 
               from employees 
               where job_id = 'IT_PROG')

--查询平均工资最低的部门信息
select department_id, department_name, city
from departments d, locations l
where department_id = (
                      select department_id
                      from employees
                      having avg(salary) = (
                                     select min(avg(salary)) 
                                     from employees 
                                     group by department_id
                                     )
                      group by department_id 
                      )
and d.location_id = l.location_id



--3.以department_id 为条件查询部门的信息 
select d.*, (select avg(salary) from employees where department_id = d.department_id) --查询平均工资最低部门的平均工资
from departments d 
where d.department_id = (
                      --2 查询平均工资最低部门的department_id
                      select department_id
                      from employees
                      having avg(salary) = (
                                     --1.查询各部门最少的平均工资
                                     select min(avg(salary)) 
                                     from employees 
                                     group by department_id
                                     )
                      group by department_id 
                      )





select j.* 
from jobs j
where job_id = (
                select job_id
                from employees


                having avg(salary) = (
                                      select max(avg(salary)) 
                                      from employees
                                      group by job_id
                                      )
                group by job_id
               )



--查询平均工资高于公司平均工资的部门
select department_id, avg(salary) 
from employees 
group by department_id
having avg(salary) > (
                      select avg(salary)
                      from employees
                      )




select * 
from employees 
where employee_id in (
                     select manager_id
                     from  employees
                     )



select * 
from employees
where employee_id = (
                    select distinct manager_id 
                    from departments
                    where department_id=(
                                          select department_id
                                          from employees
                                          having avg(salary) = (
                                                             --查询部门的平均工资的最大值
                                                              select max(avg(salary))
                                                              from employees 
                                                              group by department_id
                                                               )
                                          group by department_id
                                          )
                    )




select  * 
from employees
where salary = (
                select max(salary)
                from employees
                where to_char(hire_date, 'yyyy') = '1999' 
                )
to_char(hire_date, 'yyyy') = '1999'




--查询各个部门中工资比本部门平均工资高的员工的信息
select e1.employee_id, e1.last_name, e1.salary
from employees e1
where salary > (
                select avg(salary)
                from employees e2
                where e1.department_id = e2.department_id
                group by department_id
               ) 





--查询在location_id 为1700的部门工作的员工的工号和姓名
select employee_id, last_name
from employees 
where department_id in (
                        select department_id
                        from departments
                        where location_id = 1700
                       ) 




--查询管理者是King 的员工姓名和工资
select employee_id, last_name, salary
from employees 
where manager_id in (
                    select employee_id
                    from employees
                    where last_name = 'King'
                    ) 

多表查询总结：

SQL语句的多表查询方式：


例如：按照department_id查询employees(员工表)和departments(部门表)
的信息。
方式一(通用型):SELECT ... FROM ... WHERE
SELECT e.last_name,e.department_id,d.department_name
FROM employees e,departments d
where e.department_id = d.department_id


方式二：SELECT ... FROM ... NATURAL JOIN ...
有局限性：会自动连接两个表中相同的列(可能有多个:department_id和manager_id)
SELECT last_name,department_id,department_name
FROM employees
NATURAL JOIN departments


方式三：SELECT ... JOIN ... USING ...
有局限性：好于方式二，但若多表的连接列列名不同，此法不合适
SELECT last_name,department_id,department_name
FROM employees
JOIN departments
USING(department_id)


方式四：SELECT ... FROM ... JOIN ... ON ...
常用方式，较方式一，更易实现外联接(左、右、满)
SELECT last_name,e.department_id,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id

--内连接
    1）
    --等值连接
    --不等值连接
    2）
    --非自连接
    --自连接


--外连接
    --左外连接、右外连接、满外连接

多列子查询：

--传统查询
select employee_id, manager_id, department_id  
from employees e1 
where manager_id in ( 
                    select manager_id 
                    from employees 
                    where employee_id in (141, 174)
                    ) 
and department_id in ( 
                      select department_id 
                      from employees 
                      where employee_id in (141, 174)
                     )
and employee_id not in (141, 174)












--多列子查询

--传统查询
select employee_id, manager_id, department_id  
from employees e1 
where manager_id in ( 
                    select manager_id 
                    from employees 
                    where employee_id in (141, 174)
                    ) 
and department_id in ( 
                      select department_id 
                      from employees 
                      where employee_id in (141, 174)
                     )
and employee_id not in (141, 174)



--多列子查询
select employee_id, manager_id, department_id  
from employees e1 
where (manager_id, department_id) in ( 
                    select manager_id, department_id 
                    from employees 
                    where employee_id in (141, 174)
                    ) 
and employee_id not in (141, 174)

关于Struts2 + spring + hibernate进Q群: 130529143交流。
有偿技术支持Q群：398162181