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 * FROM employees
WHERE hire_date = (SELECT MAX(hire_date) FROM employees WHERE );
这里注意max min等函数属于对结果进行的统计,因此我们不能将其直接作为条件(如hire_date=MAX(hire_date)),可以另起语句
SELECT * FROM employees WHERE
hire_date=(SELECT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2,1);
这里,注意ORDER BY 后面需要指定按什么排序,ORDER BY hire_date DESC,这里指按入职降序排序,DESC降序,ASC升序,不指定升降默认升序 Limit 用法 :LIMIT index,count 即从第几个开始(序号从0开始),拿多少个.
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) 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 salaries.emp_no,salaries.salary,salaries.from_date,salaries.to_date,dept_manager.dept_no
from salaries inner join dept_manager
on dept_manager.emp_no = salaries.emp_no
and dept_manager.to_date = '9999-01-01'
and salaries.to_date = '9999-01-01';
SELECT e.last_name,e.first_name ,d.dept_no
FROM dept_emp d LEFT JOIN employees e
WHERE d.emp_no=e.emp_no;
SELECT e.last_name,e.first_name,d.dept_no
FROM employees e LEFT JOIN dept_emp d
on d.emp_no=e.emp_no;
注意: INNER JOIN 两边表同时有对应的数据,即任何一边缺失数据就不显示。 LEFT JOIN 会读取左边数据表的全部数据,即便右边表无对应数据。 RIGHT JOIN 会读取右边数据表的全部数据,即便左边表无对应数据。
SELECT e.emp_no,s.salary FROM employees e LEFT JOIN salaries s
ON e.emp_no=s.emp_no
WHERE s.from_date=e.hire_date
ORDER BY e.emp_no DESC;
SELECT s.emp_no ,COUNT(s.salary) FROM salaries s
GROUP BY s.emp_no
HAVING COUNT(s.salary)>15;
SELECT s.salary FROM salaries s
WHERE s.to_date='9999-01-01'
GROUP BY s.salary
ORDER BY s.salary DESC;
这里注意,虽然在select里用 distinct去重,但是大表一般用distinct效率不高,大数据量的时候都禁止用distinct,建议用group by解决重复问题。 另外: WHERE语句在GROUP BY语句之前,SQL会在分组之前计算WHERE语句。 HAVING语句在GROUP BY语句之后,SQL会在分组之后计算HAVING语句。
SELECT d.dept_no,d.emp_no,s.salary FROM dept_manager d LEFT JOIN salaries s ON d.emp_no=s.emp_no
WHERE d.to_date='9999-01-01' AND s.to_date='9999-01-01';
注意,这里where必须是两个条件,如果值判断 s.to_date='9999-01-01' ,那么可能得到的部门管理者不是想求得,因为部分管理者可能离职
select d.Name,e.Name ,e.Salary from Employee e ,Department d
on e.DepartmentId=d.Id where
e.Salary<(select max(Salary) from Employee e1 where e1.DepartmentId = e.DepartmentId ) ;