1.SQL语法分为SQL92语法和SQL99语法
#sql92 mysql> select e.ename,d.dname from emp as e,dept as d where e.deptno=d.deptno;
#sql99 mysql> select e.ename,d.dname from emp as e inner join dept as d on e.deptno=d.deptno;(inner可省略)
sql92:没有内外连接的概念
sql99:分为
内连接:会把能匹配的上的记录查出来,没有主次之分。若有没有匹配上的记录则不返回
等值连接:on 后面的值相等
select e.ename,d.dname from emp as e inner join dept as d on e.deptno=d.deptno;(inner可省略)
非等值连接:on 后面的值可以是区间
select e.ename,e.sal,s.grade from emp as e inner join salgrade as s on e.sal between s.losal and s.hisal;(inner可省略)
自连接:自己连接自己,把自己看成多张表
select a.ename as '员工名',b.ename as '领导名' from emp as a inner join emp as b on a.mgr=b.empno;
和
外连接:会有表是主表有表是副表,主要查出主表中的数据,次要的查出副表中的数据,若有没有和主表中的数据匹配上,副表会自动模拟出null来对应。
左外连接(左连接):表示左边的表是主表。
select a.ename as '员工名',b.ename as '领导名' from emp as a left outer join emp as b on a.mgr=b.empno;(outer可省略)
右外连接(右连接):表示右面的表是主表。
select a.ename as '员工名',b.ename as '领导名' from emp as b right outer join emp as a on a.mgr=b.empno;(outer可省略)
#等值连接
mysql> select * from emp;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.02 sec)
mysql> select e.ename,d.dname from emp as e,dept as d where e.deptno=d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+------------+
14 rows in set (0.00 sec)
mysql> select e.ename,d.dname from emp as e join dept as d on e.deptno=d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+------------+
14 rows in set (0.00 sec)
#非等值连接
mysql> select * from emp;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.03 sec)
mysql> select e.ename,e.sal,s.grade from emp as e join salgrade as s on e.sal between s.losal and s.hisal;
+--------+------+-------+
| ename | sal | grade |
+--------+------+-------+
| SMITH | 800 | 1 |
| ALLEN | 1600 | 3 |
| WARD | 1250 | 2 |
| JONES | 2975 | 4 |
| MARTIN | 1250 | 2 |
| BLAKE | 2850 | 4 |
| CLARK | 2450 | 4 |
| SCOTT | 3000 | 4 |
| KING | 5000 | 5 |
| TURNER | 1500 | 3 |
| ADAMS | 1100 | 1 |
| JAMES | 950 | 1 |
| FORD | 3000 | 4 |
| MILLER | 1300 | 2 |
+--------+------+-------+
14 rows in set (0.00 sec)
mysql> select e.ename,e.sal,s.grade from emp as e join salgrade as s on e.sal between s.losal and s.hisal order by s.grade;
+--------+------+-------+
| ename | sal | grade |
+--------+------+-------+
| ADAMS | 1100 | 1 |
| SMITH | 800 | 1 |
| JAMES | 950 | 1 |
| MARTIN | 1250 | 2 |
| WARD | 1250 | 2 |
| MILLER | 1300 | 2 |
| TURNER | 1500 | 3 |
| ALLEN | 1600 | 3 |
| JONES | 2975 | 4 |
| BLAKE | 2850 | 4 |
| CLARK | 2450 | 4 |
| FORD | 3000 | 4 |
| SCOTT | 3000 | 4 |
| KING | 5000 | 5 |
+--------+------+-------+
14 rows in set (0.00 sec)
#自连接
mysql> select * from emp;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)
mysql> select a.ename as '员工名',b.ename as '领导名' from emp as a inner join emp as 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)
#左连接
mysql> select a.ename as '员工名',b.ename as '领导名' from emp as a left join emp as b on a.mgr=b.empno;
+-----------+-----------+
| 员工名 | 领导名 |
+-----------+-----------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+-----------+-----------+
14 rows in set (0.00 sec)
#右连接
mysql> select a.ename as '员工名',b.ename as '领导名' from emp as b right join emp as a on a.mgr=b.empno;
+-----------+-----------+
| 员工名 | 领导名 |
+-----------+-----------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+-----------+-----------+
14 rows in set (0.00 sec)
#找到dept.DEPTNO没有emp.ENAME
mysql> select * from emp;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> select e.*,d.* from emp e join dept d on e.deptno=d.deptno;
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 30 | SALES | CHICAGO |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 30 | SALES | CHICAGO |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 30 | SALES | CHICAGO |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 30 | SALES | CHICAGO |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 30 | SALES | CHICAGO |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 30 | SALES | CHICAGO |
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
14 rows in set (0.00 sec)
mysql> select e.*,d.* from emp e right join dept d on e.deptno=d.deptno where e.job is null;
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 40 | OPERATIONS | BOSTON |
+-------+-------+------+------+----------+------+------+--------+--------+------------+--------+
1 row in set (0.00 sec)
mysql> select d.* from emp e right join dept d on e.deptno=d.deptno where e.job is null;
+--------+------------+--------+
| DEPTNO | DNAME | LOC |
+--------+------------+--------+
| 40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.00 sec)
2.三个表联查
找出每一个员工的部门名称、工资等级(内连接够用)
mysql> select e.ename,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal order by s.grade;
mysql> select * from emp;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)
mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
mysql> select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)
mysql> select e.ename,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal;
+--------+------------+-------+
| ename | dname | grade |
+--------+------------+-------+
| SMITH | RESEARCH | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCH | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | ACCOUNTING | 4 |
| SCOTT | RESEARCH | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCH | 4 |
| MILLER | ACCOUNTING | 2 |
+--------+------------+-------+
14 rows in set (0.00 sec)
mysql> select e.ename,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal order by s.grade;
+--------+------------+-------+
| ename | dname | grade |
+--------+------------+-------+
| SMITH | RESEARCH | 1 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| MILLER | ACCOUNTING | 2 |
| MARTIN | SALES | 2 |
| WARD | SALES | 2 |
| TURNER | SALES | 3 |
| ALLEN | SALES | 3 |
| FORD | RESEARCH | 4 |
| CLARK | ACCOUNTING | 4 |
| JONES | RESEARCH | 4 |
| SCOTT | RESEARCH | 4 |
| BLAKE | SALES | 4 |
| KING | ACCOUNTING | 5 |
+--------+------------+-------+
14 rows in set (0.00 sec)
找出每一个员工的部门名称、工资等级、以及上级领导(内连接不够用)
mysql> select e.ename,d.dname,s.grade ,e1.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 e1 on e.mgr = e1.empno order by s.grade;
+--------+------------+-------+-------+
| ename | dname | grade | ename |
+--------+------------+-------+-------+
| JAMES | SALES | 1 | BLAKE |
| SMITH | RESEARCH | 1 | FORD |
| ADAMS | RESEARCH | 1 | SCOTT |
| MILLER | ACCOUNTING | 2 | CLARK |
| MARTIN | SALES | 2 | BLAKE |
| WARD | SALES | 2 | BLAKE |
| TURNER | SALES | 3 | BLAKE |
| ALLEN | SALES | 3 | BLAKE |
| CLARK | ACCOUNTING | 4 | KING |
| SCOTT | RESEARCH | 4 | JONES |
| BLAKE | SALES | 4 | KING |
| FORD | RESEARCH | 4 | JONES |
| JONES | RESEARCH | 4 | KING |
| KING | ACCOUNTING | 5 | NULL |
+--------+------------+-------+-------+
内连接和外连接
最新推荐文章于 2023-09-14 15:22:06 发布