MySql----------------1代码展示

#房屋类型表
CREATE TABLE IF NOT EXISTS hos_house(hmid INT PRIMARY KEY auto_increment COMMENT'出租房屋编号',
                                    uid INT NOT NULL COMMENT'客户编号',
																		sid INT NOT NULL COMMENT'街道编号',
																		htid INT NOT NULL COMMENT'房屋类型编号',
																		price DECIMAL(20)  NOT NULL DEFAULT 0 COMMENT '每月租金',
																		topic VARCHAR(20) NOT NULL COMMENT'标题',
																		contents VARCHAR(20) NOT NULL COMMENT'描述');																			
																			
#添加表数据
INSERT INTO hos_house(hmid,uid,sid,htid,price,topic,contents)VALUES(null,6,4,4,10000,'北京','北京天安门'),																																		(null,4,2,3,8000,'南京','南京北路'),
																																		(null,2,4,3,4000,'东京','东京西路'),	
																																		(null,3,3,2,3000,'上海','浦东新区'),
																																		(null,1,2,1,2000,'河南','郑州'),
																																		(null,2,1,4,1000,'海南','海南海滩'),
																																		(null,4,1,2,1000,'北京','故宫'),
																																		(null,5,2,3,1009,'北京','长城'),
																																		(null,4,3,1,200,'郑州','金水区'),
																																		(null,6,4,4,100,'北京','体育馆');

#显示所有员工的姓名，部门号和部门名称
SELECT e.last_name,e.department_id,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;


#员工信息表
SELECT * FROM employees;
#工资等级表
SELECT * FROM job_grades;
#查询员工工资等级
SELECT e.first_name,e.last_name,salary,j.grade_level
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

#查询员工姓名员工id及管理者的姓名id  自连接
SELECT com.employee_id,com.last_name,man.manager_id,man.last_name
FROM employees com,employees man
WHERE com.manager_id = man.employee_id;

SELECT com.employee_id,com.last_name,man.employee_id,man.last_name
FROM employees com,employees man
WHERE com.manager_id = man.employee_id;



#排序
SELECT employee_id,last_name,salary
FROM employees
ORDER BY employee_id ASC;
#降序
SELECT employee_id,last_name,salary
FROM employees
ORDER BY employee_id DESC;

#分页
SELECT employee_id,last_name,salary
FROM employees
ORDER BY employee_id
LIMIT 100,3;



SELECT * FROM job_grades;
SELECT * FROM employees;
SELECT * FROM departments;


#查询所有员工last_name,department_name 左外链接
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;

#查询员工last_name,department_name
SELECT last_name,department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

#内链接
SELECT employee_id,department_name
FROM employees e JOIN departments d 
ON e.department_id = d.department_id;
#左外链接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id;
#右外链接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d 
ON e.department_id = d.department_id;
#  
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id;
WHERE d.department_id IS NULL;
#
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id;
WHERE e.department_id IS NULL;
#满外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id;
UNION ALL
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id;
WHERE e.department_id IS NULL;


#查询工资比公司平均工资高员工及工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (SELECT AVG(salary)FROM employees);

#查询工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name,job_id,salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary >ALL (
                SELECT salary
								FROM employees
								WHERE job_id = 'SA_MAN'
								);



#1，查询员工工资大于本部门平均工资的员工的lastname,salary,department_id 相关子查询

SELECT last_name,salary,department_id
FROM employees e1 
WHERE salary > (
                 SELECT AVG(salary)
								 FROM employees e2 
								 WHERE e2.department_id = e1.department_id);


#2，在from中声明子查询
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id

SELECT e1.last_name,e1.salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) asal
										FROM employees
										GROUP BY department_id) tb_e2												
WHERE e1.department_id = tb_e2.department_id	
AND e1.salary > tb_e2.asal;										


#查询员工的id,salary,按照department_name排序 在order by 中声明子查询
SELECT employee_id,salary
FROM employees e 
ORDER BY(
            SELECT department_name
						FROM departments d 
						WHERE e.department_id = d.department_id)ASC;


SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2<=(SELECT COUNT(1)
          FROM job_history j
					WHERE e.employee_id = j.employee_id);

# 1.显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name,e.department_id,d.department_name
FROM employees e 
LEFT JOIN departments d 
ON e.department_id = d.department_id;

# 2.查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,location_id
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`department_id` = 90;


SELECT job_id,location_id
FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.department_id = 90;


# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT last_name,department_name,d.location_id,city 
FROM employees e 
LEFT JOIN departments d 
ON e.department_id = d.department_id
LEFT JOIN locations l 
ON d.location_id = l.location_id
WHERE commission_pct IS NOT NULL;


# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT last_name,job_id,e.department_id,department_name,city
FROM employees e,departments d ,locations l
WHERE e.department_id = d.department_id
AND  d.location_id = l.location_id
AND l.city = 'Toronto';


# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT department_name, street_address, last_name, job_id, salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE department_name = 'Executive'




# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100


SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager,
mgr.employee_id "Mgr#"
FROM employees emp
LEFT OUTER JOIN employees mgr
ON emp.manager_id = mgr.employee_id;


# 7.查询哪些部门没有员工
SELECT d.department_id
FROM departments d LEFT JOIN employees e 
ON e.department_id = d.department_id
WHERE e.department_id IS NULL

# 8. 查询哪个城市没有部门
SELECT l.location_id,l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL

# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND d.`department_name` IN ('Sales','IT');





#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE last_name = 'Zlotkey')


#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id,last_name,salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary)
FROM employees)


#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary >ALL (SELECT salary
FROM employees
WHERE job_id = 'SA_MAN')

#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id =ANY (SELECT DISTINCT department_id    #DISTINCT 去重语句
FROM employees
WHERE last_name LIKE '%u%');


#5.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE location_id = 1700)


#6.查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE last_name = 'king'
);

#7.查询工资最低的员工信息: last_name, salary
SELECT last_name,salary
FROM employees
WHERE salary = (SELECT MIN(salary)
FROM employees)


#8.查询平均工资最低的部门信息

SELECT *
FROM departments
WHERE department_id = (SELECT department_id
FROM employees 
GROUP BY department_id
HAVING AVG(salary) = (SELECT MIN(avg_sa)
											FROM (SELECT AVG(salary) avg_sa 
																FROM employees
																GROUP BY department_id) avg_ta )
)




SELECT *
FROM departments
WHERE department_id = (SELECT department_id
FROM employees 
GROUP BY department_id
HAVING AVG(salary) <=ALL (SELECT AVG(salary) avg_sa 
																FROM employees
																GROUP BY department_id)
)




SELECT *
FROM departments
WHERE department_id = (SELECT department_id
FROM employees 
GROUP BY department_id
HAVING AVG(salary) =(
												SELECT AVG(salary) avg_sa 
												FROM employees
												GROUP BY department_id
												ORDER BY avg_sa
												LIMIT 0,1		)

);


SELECT d1.*
FROM departments d1 ,(
											SELECT department_id d,AVG(salary) a
											FROM employees
											GROUP BY department_id
											ORDER BY a
											LIMIT 0,1) tab_a_b
WHERE d1.department_id = tab_a_b.d;





#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）


SELECT d1.*,(SELECT AVG(salary)FROM employees WHERE  department_id = d1.department_id )svg_sal
FROM departments d1 ,(
											SELECT department_id d,AVG(salary) a
											FROM employees
											GROUP BY department_id
											ORDER BY a
											LIMIT 0,1) tab_a_b
WHERE d1.department_id = tab_a_b.d;


#10.查询平均工资最高的 job 信息

SELECT *
FROM jobs
WHERE job_id = (
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (
SELECT MAX(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY job_id) t_job_avg_sal));


SELECT j.* 
FROM jobs j,(
SELECT job_id,AVG(salary) avg_sal
FROM employees
GROUP BY job_id
ORDER BY avg_sal DESC
LIMIT 0,1 )t_job_avg_sal
WHERE j.job_id = t_job_avg_sal.job_id

#11.查询平均工资高于公司平均工资的部门有哪些?

SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary)>(
										SELECT AVG(salary)
										FROM employees)


#12.查询出公司中所有 manager 的详细信息
#方式一 自连接
#方式二 IN
#方式三 EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1 
WHERE EXISTS(
               SELECT*
							 FROM employees e2 
							 WHERE e1.employee_id = e2.employee_id);



#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
SELECT MIN(salary)
FROM employees
WHERE department_id = (SELECT department_id
												FROM employees
												GROUP BY department_id
												HAVING MAX(salary) = (
																						SELECT MIN(max_sal)
																						FROM(
																						SELECT MAX(salary) max_sal 
																						FROM employees
																						GROUP BY department_id) t_dept_max_sal))


																						
SELECT MIN(salary)
FROM employees e , (
SELECT department_id,MAX(salary) max_sal 
FROM employees
GROUP BY department_id
ORDER BY max_sal ASC
LIMIT 0,1
)t_dept_max_sal 
WHERE e.department_id = t_dept_max_sal.department_id;



#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (SELECT DISTINCT manager_id
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT MAX(avg_sal)
FROM (
				SELECT AVG(salary) avg_sal 
				FROM employees 
				GROUP BY department_id) t_dept
)
));

SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees e,(
SELECT department_id,AVG(salary) avg_sal 
FROM employees
GROUP BY department_id
ORDER BY avg_sal  DESC
LIMIT 0,1) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id);

#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
SELECT department_id
FROM departments
WHERE department_id NOT IN(
SELECT DISTINCT department_id
FROM employees
WHERE job_id = 'ST_CLERK');


SELECT department_id
FROM departments d 
WHERE NOT EXISTS (
							SELECT *
							FROM employees e 
							WHERE d.department_id = e.department_id
							AND e.job_id = 'ST_CLERK')