MySQL子查询

SELECT * FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Ernst');
SELECT last_name,job_id,salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 110) AND salary > (SELECT salary FROM employees WHERE employee_id = 120);
SELECT last_name,job_id,salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);
SELECT MIN(salary),department_id FROM employees GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 30);
SELECT last_name FROM employees WHERE department_id IN(SELECT DISTINCT department_id FROM departments WHERE location_id IN (1000,1800));
SELECT employee_id,last_name,salary FROM employees WHERE salary < ANY (SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';

SELECT * FROM employees WHERE (employee_id,salary) = (SELECT MIN(employee_id),MAX(salary) FROM employees);

SELECT d.*,(SELECT COUNT(*) FROM employees e WHERE e.department_id = d.`department_id`) AS 个数 FROM departments d;
SELECT (SELECT department_name FROM departments d INNER JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id = 110) 部门名;

SELECT av_dep.*,g.`grade_level` FROM (SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id) av_dep INNER JOIN job_grades g ON av_dep.ag BETWEEN lowest_sal AND highest_sal;

SELECT department_name FROM departments d WHERE EXISTS(SELECT * FROM employees e WHERE d.department_id = e.`department_id`);
SELECT bo.* FROM boys bo WHERE NOT EXISTS(SELECT boyfriend_id FROM beauty b WHERE bo.`id` = b.`boyfriend_id`);

SELECT * FROM employees LIMIT 0,10;
SELECT * FROM employees LIMIT 10;

SELECT * FROM employees WHERE email LIKE '%A%' UNION SELECT * FROM employees WHERE department_id > 100;