课堂笔记1

select empname ,job,hiredate,bonus,dept_name from employee as em left join deparepartment as de on em.empno = de.dept_manager;
select empname,job ,hiredate,salary,bonus,dept_name from employee as em inner join department as de on em.empno = de.dept_manager
    -> ;
select empname , job,hiredate,salary,dept_name ,dept_manager from employee
as em right outer join department as de on em.empno=de.dept_manager;
select empname ,job,hiredate,bonus,dept_name from employee as em left join de                                             ;
+-----------+----------+------------+-------+-----------+
| empname   | job      | hiredate   | bonus | dept_name |
+-----------+----------+------------+-------+-----------+
| 张三      | engineer | 2021-12-30 | 20000 | NULL      |
| 李四      | engineer | 2021-11-30 | 20000 | NULL      |
| 王五      | 保洁     | 2011-11-30 | 10000 | 后勤部    |
| 赵六      | 保洁     | 2013-11-30 |  3000 | NULL      |
| 孙六      | 保洁     | 2015-11-30 |  3000 | NULL      |
| 钱八      | engineer | 2017-11-30 | 30000 | NULL      |
| 刘能      | engineer | 2018-11-30 | 32000 | 科技部    |
| lily      | 行政     | 2018-10-30 |  2000 | NULL      |
| 李静      | 行政     | 2018-10-30 |  2000 | NULL      |
| 王平      | 行政     | 2018-10-30 |  2000 | NULL      |
| 小红      | 行政     | 2018-10-30 |  5000 | 行政部    |
| 赵丽丽    | 财务     | 2017-10-30 |  4000 | NULL      |
| 紫薇      | 财务     | 2015-10-30 |  6000 | 财务部    |
+-----------+----------+------------+-------+-----------+
13 rows in set (0.00 sec)
select em.empno, em.empname,de.dept_name from employee as em inner join department as
de on em.empno=de.dept_manager;
+-------+---------+-----------+
| empno | empname | dept_name |
+-------+---------+-----------+
|     3 | 王五    | 后勤部    |
|     7 | 刘能    | 科技部    |
|    11 | 小红    | 行政部    |
|    13 | 紫薇    | 财务部    |
+-------+---------+-----------+
4 rows in set (0.00 sec)
select em.empno, em.empname,de.dept_name,em.hiredate from employee as em inner join deppartment as de on em.empno=de.dept_manager;
+-------+---------+-----------+------------+
| empno | empname | dept_name | hiredate   |
+-------+---------+-----------+------------+
|     3 | 王五    | 后勤部    | 2011-11-30 |
|     7 | 刘能    | 科技部    | 2018-11-30 |
|    11 | 小红    | 行政部    | 2018-10-30 |
|    13 | 紫薇    | 财务部    | 2015-10-30 |
+-------+---------+-----------+------------+
4 rows in set (0.00 sec)
select em.empno, em.empname,de.dept_name,em.hiredate from employee as em inner join de+-------+---------+-----------+------------+
| empno | empname | dept_name | hiredate   |
+-------+---------+-----------+------------+
|     3 | 王五    | 后勤部    | 2011-11-30 |
|     7 | 刘能    | 科技部    | 2018-11-30 |
|    11 | 小红    | 行政部    | 2018-10-30 |
|    13 | 紫薇    | 财务部    | 2015-10-30 |
+-------+---------+-----------+------------+
4 rows in set (0.00 sec)
select empname, salary from employee where salary =(select min(salary) from employee);

+---------+--------+
| empname | salary |
+---------+--------+
| 赵六    |   5000 |
| 孙六    |   5000 |
+---------+--------+
2 rows in set (0.11 sec)
select empname, salary from employee where salary >(select avg(salary) from  employee);
;
+---------+--------+
| empname | salary |
+---------+--------+
| 张三    |  15000 |
| 李四    |  15000 |
| 钱八    |  15000 |
| 刘能    |  16000 |
| 紫薇    |  12000 |
+---------+--------+
5 rows in set (0.00 sec)
 select * from employee where salary > all(select salary from  employee where dept_id=104);
+-------+---------+----------+------------+--------+-------+---------+
| empno | empname | job      | hiredate   | salary | bonus | dept_id |
+-------+---------+----------+------------+--------+-------+---------+
|     1 | 张三    | engineer | 2021-12-30 |  15000 | 20000 |     102 |
|     2 | 李四    | engineer | 2021-11-30 |  15000 | 20000 |     102 |
|     6 | 钱八    | engineer | 2017-11-30 |  15000 | 30000 |     102 |
|     7 | 刘能    | engineer | 2018-11-30 |  16000 | 32000 |     102 |
+-------+---------+----------+------------+--------+-------+---------+
4 rows in set (0.00 sec)
select empname, job,(select dept_name from department as de where de.dept_id=em.dept_id) as dept_name from employee as em;
+-----------+----------+-----------+
| empname   | job      | dept_name |
+-----------+----------+-----------+
| 张三      | engineer | 科技部    |
| 李四      | engineer | 科技部    |
| 王五      | 保洁     | 后勤部    |
| 赵六      | 保洁     | 后勤部    |
| 孙六      | 保洁     | 后勤部    |
| 钱八      | engineer | 科技部    |
| 刘能      | engineer | 科技部    |
| lily      | 行政     | 行政部    |
| 李静      | 行政     | 行政部    |
| 王平      | 行政     | 行政部    |
| 小红      | 行政     | 行政部    |
| 赵丽丽    | 财务     | 财务部    |
| 紫薇      | 财务     | 财务部    |
+-----------+----------+-----------+
13 rows in set (0.10 sec)
select emp.empname, emp.job,de.dept_name from employee  emp inner join department  de
on emp.dept_id=de. dept_id;
+-----------+----------+-----------+
| empname   | job      | dept_name |
+-----------+----------+-----------+
| 王五      | 保洁     | 后勤部    |
| 赵六      | 保洁     | 后勤部    |
| 孙六      | 保洁     | 后勤部    |
| 张三      | engineer | 科技部    |
| 李四      | engineer | 科技部    |
| 钱八      | engineer | 科技部    |
| 刘能      | engineer | 科技部    |
| lily      | 行政     | 行政部    |
| 李静      | 行政     | 行政部    |
| 王平      | 行政     | 行政部    |
| 小红      | 行政     | 行政部    |
| 赵丽丽    | 财务     | 财务部    |
| 紫薇      | 财务     | 财务部    |
+-----------+----------+-----------+
13 rows in set (0.00 sec)
select emp.empname, emp.job,de.dept_name from employee  emp inner join department  deon emp.dept_id=de. dept_id;
+-----------+----------+-----------+
| empname   | job      | dept_name |
+-----------+----------+-----------+
| 王五      | 保洁     | 后勤部    |
| 赵六      | 保洁     | 后勤部    |
| 孙六      | 保洁     | 后勤部    |
| 张三      | engineer | 科技部    |
| 李四      | engineer | 科技部    |
| 钱八      | engineer | 科技部    |
| 刘能      | engineer | 科技部    |
| lily      | 行政     | 行政部    |
| 李静      | 行政     | 行政部    |
| 王平      | 行政     | 行政部    |
| 小红      | 行政     | 行政部    |
| 赵丽丽    | 财务     | 财务部    |
| 紫薇      | 财务     | 财务部    |
+-----------+----------+-----------+
13 rows in set (0.00 sec)

mysql> select empname, emp.job,de.dept_name from employee as emp inner join department as deon emp.dept_id=de. dept_id;
+-----------+----------+-----------+
| empname   | job      | dept_name |
+-----------+----------+-----------+
| 王五      | 保洁     | 后勤部    |
| 赵六      | 保洁     | 后勤部    |
| 孙六      | 保洁     | 后勤部    |
| 张三      | engineer | 科技部    |
| 李四      | engineer | 科技部    |
| 钱八      | engineer | 科技部    |
| 刘能      | engineer | 科技部    |
| lily      | 行政     | 行政部    |
| 李静      | 行政     | 行政部    |
| 王平      | 行政     | 行政部    |
| 小红      | 行政     | 行政部    |
| 赵丽丽    | 财务     | 财务部    |
| 紫薇      | 财务     | 财务部    |
+-----------+----------+-----------+
13 rows in set (0.00 sec)
 select empname ,job,hiredate ,salary,bonus, (select dept_name from department where department.dept_id=employee.dept_id)as dept_name from employee where empno in (select dept_manager from department);
+---------+----------+------------+--------+-------+-----------+
| empname | job      | hiredate   | salary | bonus | dept_name |
+---------+----------+------------+--------+-------+-----------+
| 王五    | 保洁     | 2011-11-30 |  10000 | 10000 | 后勤部    |
| 刘能    | engineer | 2018-11-30 |  16000 | 32000 | 科技部    |
| 小红    | 行政     | 2018-10-30 |  10000 |  5000 | 行政部    |
| 紫薇    | 财务     | 2015-10-30 |  12000 |  6000 | 财务部    |
+---------+----------+------------+--------+-------+-----------+
4 rows in set (0.21 sec)