MySQL之复合查询与内外连接

目录

一：基本查询

 二:多表查询

三:自连接

四:子查询 

 1.单行子查询

2.多行子查询

3 多列子查询

4.在from子句中使用子查询

5. 合并查询 

五：表的内外连接

1.内连接

2.外连接 

---

一：基本查询

(1)查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

mysql> select* from emp where (sal>500 or job='MANAGER')and ename like'J%';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007900 | JAMES | CLERK   | 7698 | 1981-12-03 00:00:00 |  950.00 | NULL |     30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)

mysql> select* from emp where (sal>500 or job='MANAGER')and substring(ename,1,1)='J';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007900 | JAMES | CLERK   | 7698 | 1981-12-03 00:00:00 |  950.00 | NULL |     30 |
+--------+-------+---------+------+---------------------+---------+------+--------+

(2)按照部门号升序而雇员的工资降序排序 

mysql> select* from emp order by deptno asc,sal desc;

(3)使用年薪进行降序排序

mysql> select ename,deptno,sal*12+ifnull(comm,0) 年薪 from emp order by 年薪 desc;

(4)显示工资最高的员工的名字和工作岗位

mysql> select ename,job from emp where sal=(select max(sal) from emp);

(5)显示工资高于平均工资的员工信息 

mysql> select ename,sal from emp where sal>(select avg(sal) from emp);

(6)显示每个部门的平均工资和最高工资 

mysql> select deptno, format(avg(sal),2),max(sal) from emp group by deptno;

(7)显示平均工资低于2000的部门号和它的平均工资 

#having是对聚合后的统计数据做条件筛