#多表查询 /* 分类 ：按年代分类 sql99 （不支持全外连接） 内连接 ： 等值连接 非等值连接 自链接 外连接 ： 左外连接 右连接 全外连接 交叉连接 */ SELECT name,boyname from boys,beauty where boys.id=beauty.boyfriend_id
SELECT last_name ,department_name from employees,departments where employees.department_id=departments.department_id
SELECT * from employees where ISNULL(department_id)
#如果起了别名就不要用表名去限定
#查询城市名中第二个字符为O的城市名和部门名 SELECT city,department_name from departments d,locations l where l.location_id=d.location_id and l.city like ‘_o%’
#查询每个城市的部门个数 SELECT count(*),city from departments d,locations l where l.location_id=d.location_id GROUP BY city
#查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资 SELECT department_name,d.manager_id,min(salary) from departments d,employees e where d.department_id=e.department_id and commission_pct is not null GROUP BY d.manager_id #增加排序 每个工种的工种名和员工个数，并按照员工个数降序 SELECT job_title,count() from jobs j,employees e WHERE j.job_id=e.job_id GROUP BY j.job_title ORDER BY count() desc
#非等值连接 SELECT salary ,grade_level from employees,job_grades where salary BETWEEN lowest_sal and highest_sal ORDER BY grade_level desc
#自连接 SELECT e.employee_id, e.last_name,m.employee_id,m.last_name from employees e , employees m where e.manager_id=m.employee_id
#显示员工表的最大工资，工资平均值 SELECT max(salary) ,AVG(salary) from employees
#查询员工表的emp_id,job_id,last_name,按照dept_id降序，salary升序 SELECT employee_id ,job_id ,last_name from employees ORDER BY department_id desc ,salary asc
#查询员工表的job_id 中包含a 和 e 并且a在e前面 SELECT job_id from employees where job_id LIKE ‘%a%e%’;
select TRIM( now() )
#内连接 SELECT last_name ,department_name from employees e inner JOIN departments d on e.department_id=d.department_id
select city,count() from locations l inner join departments d on l.location_id=d.location_id GROUP BY city HAVING count()>3
SELECT department_name ,count() “员工个数” from departments d inner join employees e on e.department_id=d.department_id GROUP BY department_name HAVING count()>3 ORDER BY count(*) desc #多表连接 SELECT last_name ,department_name,job_title FROM employees e inner join departments d on e.department_id=d.department_id inner join jobs j on e.job_id=j.job_id ORDER BY department_name desc
#非等值连接 SELECT salary ,grade_level from employees e join job_grades j on e.salary BETWEEN lowest_sal and highest_sal
#自连接查询员工的名字上级的名字 SELECT e.last_name ,m.last_name from employees e join employees m on e.manager_id =m.employee_id where e.last_name like ‘%k%’
##外连接 用于查询一个表中右一个表没有 SELECT beauty.name from beauty left outer join boys on beauty.boyfriend_id=boys.id where boys.id is null
#查询那个部门没有员工 SELECT d.*,e.employee_id from departments d left join employees e on e.department_id=d.department_id where
SELECT d.department_name ,count() from departments d inner join employees e on e.department_id=d.department_id GROUP BY department_name HAVING count()>0
#查询那个城市没有部门 SELECT city,departments.* from locations LEFT join departments on locations.location_id=departments.department_id where departments.location_id is null
#查询部门名为sal，it的员工信息 SELECT e.*,d.department_name from employees e right join departments d on e.department_id=d.department_id where d.department_name in (‘sal’,‘it’)
