sql语句实现查询实例

    说明：本内容衔接上次的内容

    七、内连接（两张表均有时，才可以实现），我觉得内连接实现的功能和用where连接两表时，实现的功能是一样的

 获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary(当前时间为9999-01-01)
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

实现语句：

select a.dept_no,b.emp_no,max(b.salary) as salary
from dept_emp a,salaries b
where a.emp_no=b.emp_no and a.to_date='9999-01-01' and b.to_date='9999-01-01'
group by a.dept_no;

或者是：

SELECT d.dept_no,s.emp_no,max(s.salary) AS salary

FROM  salaries AS s INNER JOIN dept_emp As d ON d.emp_no = s.emp_no

WHERE d.to_date = '9999-01-01'AND s.to_date = '9999-01-01'

GROUP BY d.dept_no;

 八、分组查询

 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

 这个创建表的方式刚开始没看懂，后来才知道是创建表的。（含义为：如果titles表不存在的话，就创建，否则，就不创建）

  实现语句：

select a.title,count(*) as t
from titles a
group by a.title
having count(a.title)>=2;

九、查询不重复数据的分组

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。
CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);

实现语句：

select a.title,count(distinct emp_no)
from titles a
group by a.title
having count(distinct emp_no)>=2;

十、奇数查询

查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

实现语句：

select a.* 
from employees a
where (a.emp_no%2=1) and a.last_name<>'Mary' 
order by a.hire_date desc;

十一、不用order by 语句实现排名第二的查询语句

查找当前薪水(to_date='9999-01-01')排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用order by
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

实现思路：

      本题做法很多，主要思想为多层SELECT嵌套与MAX()函数结合

1、先利用MAX()函数找出salaries中当前薪水最高者，即SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01'

2、再利用INNER JOIN连接employees与salaries表，限定条件为【同一员工】e.emp_no = s.emp_no、【当前】s.to_date = '9999-01-01'与【非薪水最高】s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')

3、在以上限制条件下找薪水最高者，即为所有员工薪水的次高者

实现语句：

SELECT e.emp_no, MAX(s.salary) AS salary, e.last_name, e.first_name 
FROM employees  e,salaries  s 
WHERE s.to_date = '9999-01-01' and  e.emp_no = s.emp_no
AND s.salary NOT IN (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01');

十二、连接实例

 查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

实现语句：（左连接实现多个表的查询）

select e.last_name,e.first_name,dep.dept_name
from employees e left join dept_emp de on e.emp_no=de.emp_no left join departments dep on de.dept_no=dep.dept_no;