MySQL02_22.7.27

1、连接查询：

1、根据连接的方式分为：

   内连接：

        等值连接

        非等值连接

        自连接

    外连接：

        左连接

        右连接

2、表与表之间连接 加入限定条件 避免笛卡尔现象

92语法 缺点：表的连接条件和进一步的筛选条件都放在where后面 结构不清晰

mysql> select ename,dname from emp,dept where emp.deptno = dept.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
//原理 第一张表中的一个数据对第二张表进行匹配 对所有数据进行匹配（并列） 匹配次数并没有减少 56次 但是结果只有 14个 筛选出符合条件的 效率低

 

mysql> select e.ename,d.dname
    -> from emp e,dept d
    -> where e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
//加入限定条件 并另起别名 提高效率 在调出表时赋予别名

3、内连接之等值连接

99语法 优点（表连接的条件是独立出来的 连接之后还需要进一步筛选就在后面继续添加where

查询每个员工所在部门名称 显示员工名+部门名

mysql> select e.ename,d.dname
    -> from emp e
    -> join dept d
    -> on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

4、内连接之非等值连接

找出每个员工的薪资等级 要求显示 员工名 薪资 薪资等级

mysql> select e.ename,e.sal,s.grade
    -> from emp e
    -> join salgrade s
    -> on e.sal between s.losal and s.hisal;//条件不是一个等量关系
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.00 sec)

5、内连接之自连接 (一张表看做两张表)

查询员工的上级领导 要去显示员工名+对应领导名

mysql> select a.ename as '员工名',b.ename as '领导名'
    -> from emp a
    -> join emp b
    -> on a.mgr = b.empno;
+-----------+-----------+
| 员工名    | 领导名    |
+-----------+-----------+
| SMITH     | FORD      |
| ALLEN     | BLAKE     |
| WARD      | BLAKE     |
| JONES     | KING      |
| MARTIN    | BLAKE     |
| BLAKE     | KING      |
| CLARK     | KING      |
| SCOTT     | JONES     |
| TURNER    | BLAKE     |
| ADAMS     | SCOTT     |
| JAMES     | BLAKE     |
| FORD      | JONES     |
| MILLER    | CLARK     |
+-----------+-----------+
13 rows in set (0.00 sec)

6、外连接

from emp e right join dept d (right 右边为主表 )

        1、两张表

mysql>  select e.ename,d.dname
    -> from emp e right join dept d
    -> on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| MILLER | ACCOUNTING |
| KING   | ACCOUNTING |
| CLARK  | ACCOUNTING |
| FORD   | RESEARCH   |
| ADAMS  | RESEARCH   |
| SCOTT  | RESEARCH   |
| JONES  | RESEARCH   |
| SMITH  | RESEARCH   |
| JAMES  | SALES      |
| TURNER | SALES      |
| BLAKE  | SALES      |
| MARTIN | SALES      |
| WARD   | SALES      |
| ALLEN  | SALES      |
| NULL   | OPERATIONS |
+--------+------------+
15 rows in set (0.00 sec)

        2、三张表

找出每个员工的部门名称 以及工资等级 要求显示 员工名、部门名、薪资、薪资等级、老板名

mysql> select e.ename,e.sal,d.dname,s.grade,l.ename
    -> from emp e
    -> join dept d
    -> on e.deptno = d.deptno
    -> join salgrade s
    -> on e.sal between s.losal and s.hisal
    -> left join emp l
    -> on e.mgr = l.empno;
+--------+---------+------------+-------+-------+
| ename  | sal     | dname      | grade | ename |
+--------+---------+------------+-------+-------+
| SMITH  |  800.00 | RESEARCH   |     1 | FORD  |
| ALLEN  | 1600.00 | SALES      |     3 | BLAKE |
| WARD   | 1250.00 | SALES      |     2 | BLAKE |
| JONES  | 2975.00 | RESEARCH   |     4 | KING  |
| MARTIN | 1250.00 | SALES      |     2 | BLAKE |
| BLAKE  | 2850.00 | SALES      |     4 | KING  |
| CLARK  | 2450.00 | ACCOUNTING |     4 | KING  |
| SCOTT  | 3000.00 | RESEARCH   |     4 | JONES |
| KING   | 5000.00 | ACCOUNTING |     5 | NULL  |
| TURNER | 1500.00 | SALES      |     3 | BLAKE |
| ADAMS  | 1100.00 | RESEARCH   |     1 | SCOTT |
| JAMES  |  950.00 | SALES      |     1 | BLAKE |
| FORD   | 3000.00 | RESEARCH   |     4 | JONES |
| MILLER | 1300.00 | ACCOUNTING |     2 | CLARK |
+--------+---------+------------+-------+-------+
14 rows in set (0.00 sec)

2、子查询 （嵌套select语句）

select

...(select)

from

...(select)

where

...(select)

1、where语句出现select语句

mysql> select ename,sal from emp where sal > (select min(sal) from emp);
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
13 rows in set (0.00 sec)

2、from语句中的子查询

        1、将from后子查询的结果看做一张临时表

        1、找出每个岗位 平均工资 的薪资等级

        1、找出每个岗位的平均工资

mysql> select job,avg(sal) from emp group by job;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| CLERK     | 1037.500000 |
| SALESMAN  | 1400.000000 |
| MANAGER   | 2758.333333 |
| ANALYST   | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+
5 rows in set (0.00 sec)

2、将上表看做临时表

mysql> select t.*,s.grade
    -> from (select job,avg(sal) as avgsal from emp group by job) t
    -> join salgrade s
    -> on t.avgsal between s.losal and s.hisal;
+-----------+-------------+-------+
| job       | avgsal      | grade |
+-----------+-------------+-------+
| CLERK     | 1037.500000 |     1 |
| SALESMAN  | 1400.000000 |     2 |
| MANAGER   | 2758.333333 |     4 |
| ANALYST   | 3000.000000 |     4 |
| PRESIDENT | 5000.000000 |     5 |
+-----------+-------------+-------+
5 rows in set (0.00 sec)

3、union合并查询结果集

1、union 减少匹配次数 完成拼接动作

        1、查询工作岗位是manager和salesman的员工

mysql> select ename,job from emp where job = 'manager'
    -> union
    -> select ename,job from emp where job ='salesman';
+--------+----------+
| ename  | job      |
+--------+----------+
| JONES  | MANAGER  |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)

4、limit

1、将查询结果集的一部分取出来 （limit a,b)从下标a开始 b条记录 起始位置+长度

mysql> select ename,sal
    -> from emp
    -> order by sal desc
    -> limit 2,3;
+-------+---------+
| ename | sal     |
+-------+---------+
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
3 rows in set (0.00 sec)

2、每页显示3条记录 ：

        第一页 limit 0,3

        第二页 limit 3,3

        第三页 limit 6,3

        第四页 limit 9,3

每页显示pagesize条记录 :

        第pagesize页 limit （pageNo -1) * pagesize ,pagesize

public static void main(String[] args){
    //用户提交一个页码及要求显示的页数
    int pageNo = 5;
    int pagesize = 10;
    int startIndex = (pageNo - 1) * pagesize;
    String sql = "select ... limit" + startIndex + "," + "pagesize";
}

DQL 语句总结：

        select ...                5

        from ...                  1

        where ...               2条件筛选

        group by ...           3分组

        having ...              4过滤

        order by ...           6排序输出

        limit ...                  7取