手机单页网站教程,成都网页制作服务好,精准营销数据,做国际贸易的网站目录
前言
开篇语
准备工作
案例准备
多表查询
笛卡尔积
等值连接
外连接
内连接
自连接
子查询
存在和所有
含于
分页查询
建表语句
结束语 前言
开篇语
本篇继续讲解MySQL的一些基础的操作——数据字段的查询中的多表查询和分页查询#xff0c;与单表查询…目录
前言
开篇语
准备工作
案例准备
多表查询
笛卡尔积
等值连接
外连接
内连接
自连接
子查询
存在和所有
含于
分页查询
建表语句
结束语 前言
开篇语
本篇继续讲解MySQL的一些基础的操作——数据字段的查询中的多表查询和分页查询与单表查询相比这些在面试时会更加常用。
准备工作
软件【参考版本】Navicat Premium 16 / SQLyog - 32 bit
语言MySQL
版本信息MySQL5.7
案例准备
本次的案例为四张表创建的语句比较长放在本章的最后请自行跳转至对应位置复制创建。
参考库结构—— dept部门表结构4行3列—— emp员工表结构14行8列—— 多表查询
笛卡尔积
概念
笛卡尔积查询的结果是两张被查询的表的列数相加行数相乘。
比如说一张三列四行的表和一张四列六行的表查询在不进行过滤去重的条件下查询的结果就是一张七列二十四行的新表相同的列不会合并。
案例
1.员工表emp和部门表dept的笛卡尔积 select emp.*,dept.* from emp,dept 可以看到多表查询的from语句里不止有一张表。查询的结果是11*56相同的列deptno部门编号并未被合并。 等值连接
概念
当过滤条件中涉及到两张表的某一条件对应相同时就可以使用等值连接查询。
而其余的情况就是非等值连接比如使用betwen and等作为过滤条件。
关键字 案例
1.使用等值连接显示员工的编号姓名部门编号部门名 select empno, ename, e.deptno, dname from emp e, dept d where e.deptno d.deptno order by e.deptno 由于部门编号实际上在两张表中都有所以我们可以将该列作为等值连接的过滤条件。
注意由于这里有两张表所以要指定使用哪一张表的部门编号否则会提醒你不知道要找的是哪一张表的这一列—— 外连接
概念
外连接实际上分为左外连接和右外连接两种一般来说以左表作为基准就是左外连接反之就是右外连接。
那么什么是以左表为基准呢实际上就是左表中有的条件右表中可能是没有的但是我们需要把右表强行对应拼到左表中如果拼接位置找不到对应的数据则结果里的该位置处为NULL。
即以谁为基准就应当保证结果中有谁的全部数据而不保证另一侧。
关键字 LEFT JOIN ON RIGHT JOIN ON 案例
1.外连接 查询 员工编号 员工姓名 部门编号 部门名称
这里以左外连接为例—— select empno, ename, e.deptno, d.dname from emp e left join dept d on e.deptno d.deptno order by e.deptno 可以看到外连接和笛卡尔积查询的结果非常相似只是把并列的两张表变成了外连接然后把过滤条件放到ON中了。
实质上笛卡尔积查询和外连接查询可以相互转化。
内连接
概念
当数据项既满足左外连接又满足右外连接即为两表有关联不会补NULL的数据项时就是内连接了。
在数学上我们也可以把内连接的结果看做是两表的交集。
关键字 INNER JOIN ON 案例
1.内连接 查询 员工编号 员工姓名 部门编号 部门名称 select empno,ename,e.deptno, d.dname from emp e inner join dept d on e.deptno d.deptno order by e.deptno 自连接
概念
自连接实际上就是相当于将自身复制一份然后让自身和复制表之间实现连接。
案例
1.使用自连接显示XXX的上级是XXX这种格式对于没有上级的显示为“无” select concat(u.ename, 的上级是, ifnull(boss.ename, 无)) 附属关系 from emp u left join emp boss on u.mgr boss.empno; 实际上这里就是将一张表作为员工表取的是员工姓名
而部分员工是有下属的所以员工表也可以作为上级表使用两表的连接条件就是员工表的mgr编号与上级表的empno编号一致。
而部分员工可能没有上级比如说KING的mgr为NULL说明员工表实际上是外连接中的基准表。
使用IFNULL()来处理NULL的问题否则整个varchar的值在拼接后会变为NULL。
子查询
概念
子查询的作用是查询条件未知的事物将查询的结果作为父查询的条件使用。
一般来说子查询有下面的要点—— 1) 子查询与父查询可以针对不同的表也可以针对同一张表 2) 子查询与父查询在传参时参数数量、类型和含义要相同 说白了子查询可以看做一个组参数或代表一张表。
但是子查询的效率是很低的应当尽量少用。
案例
1.查询出高于10号部门的平均工资的员工信息 select * from emp where sal (select avg(sal) from emp where deptno 10) 实际上我们需要做两次查表的操作第一次是查询所有员工的信息父查询第二次是查询10号部门的平均工资子查询。
然后将子查询的结果avg(sal)作为父查询的过滤条件完成查询需求。
2.查询出有哪些部门的平均工资高于30号部门的平均工资部门编号 部门名称 平均工资 select e.deptno, dname, avg(sal) from emp e, dept d where e.deptno 30 and e.deptno d.deptno group by e.deptno, dname having avg(sal) (select avg(sal) from emp where deptno 30) 对于多行函数AVG()该过滤条件需要添加到HAVING分组过滤关键字中。
不要忘记两表公有的列一定要指定是哪一张表的该列。
3.查询各部门中工资比本部门平均工资高的员工的员工号 姓名 工资 select empno, ename, sal from emp e, (select avg(sal) avsa, deptno from emp group by deptno) f where e.deptno f.deptno and sal avsa 子查询的结果实际上也可以看成一张表为了使这张新表 f 能够与员工表 e 有关联我们可以为新表添加一个公共列deptno。
存在和所有
概念
存在是指在结果集中至少有一个结果符合条件就符合
所有是指结果集的所有结果都符合条件才符合。
关键字 ANY() ALL() 案例
1.查询出比20号部门任一员工薪资高的员工信息 select * from emp where sal any(select sal from emp where deptno 30) 这里涉及到员工表和只有30号部门员工的子表所以用到的是子查询。
而过滤条件里要求查询的工资比任一结果高即至少有一个符合最低薪资即可所以可以使用ANY关键字。
2.查询出比30号部门任何员工薪资高的员工信息 select * from emp where sal all(select sal from emp where deptno 30) 这里要求比任何30号部门员工工资都高所以使用ALL关键字。
实际上在部分情况下ANY和ALL与MIN()和MAX()函数的作用是相同。
含于
概念
含于是指某个些筛选内容包含在结果集中筛选内容需要与结果集中内容对应。
关键字 IN() 案例
1.查询工作和工资与MARTIN完全相同的员工信息 select * from emp where (job, sal) in(select job, sal from emp where ename MARTIN) 我们的子查询查询出了两列的内容而筛选条件job和sal与这两列一一对应。
分页查询
概念
分页查询是指查询指定表中的某一条至某一条的行。
关键字 LIMIT start, number 其中start为开始的索引值从0开始number为查询的条数。
案例
1.查询工资排名第4到8名的员工的信息 select * from emp order by sal limit 3, 5 建表语句
员工表
CREATE TABLE emp (empno INT(4) NOT NULL COMMENT 员工编号,ename VARCHAR(10) DEFAULT NULL COMMENT 员工姓名,job VARCHAR(9) DEFAULT NULL COMMENT 员工岗位,mgr INT(4) DEFAULT NULL COMMENT 领导编号,hiredate DATE DEFAULT NULL COMMENT 入职时间,sal DECIMAL(7,2) DEFAULT NULL COMMENT 工资,comm DECIMAL(7,2) DEFAULT NULL COMMENT 奖金,deptno INT(2) DEFAULT NULL COMMENT 部门编号,PRIMARY KEY (empno)
) ENGINEINNODB DEFAULT CHARSETutf8
员工表数据
INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7369, SMITH, CLERK, 7902,1980-12-17, 800, NULL, 20);INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7499, ALLEN, SALESMAN, 7698, 1981-02-20, 1600, 300, 30);INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7521, WARD, SALESMAN, 7698, 1981-02-22, 1250, 500, 30);INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7566, JONES, MANAGER, 7839, 1981-04-02, 2975, NULL, 20);INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7654, MARTIN, SALESMAN, 7698, 1981-09-28, 1250, 1400, 30);INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7698, BLAKE, MANAGER, 7839, 1981-05-01, 2850, NULL, 30);INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7782, CLARK, MANAGER, 7839, 1981-06-09, 2450, NULL, 10);INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7788, SCOTT, ANALYST, 7566, 1987-06-13, 3000, NULL, 20);INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7839, KING, PRESIDENT, NULL, 1981-11-17, 5000, NULL, 10);INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7844, TURNER, SALESMAN, 7698, 1981-09-08, 1500, 0, 30);INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7876, ADAMS, CLERK, 7788, 1987-06-13, 1100, NULL, 20);INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7900, JAMES, CLERK, 7698, 1981-12-03, 950, NULL, 30);INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7902, FORD, ANALYST, 7566, 1981-12-03, 3000, NULL, 20);INSERT INTO emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7934, MILLER, CLERK, 7782, 1982-01-23, 1300, NULL, 10);
部门表
CREATE TABLE dept(deptno INT(2) PRIMARY KEY NOT NULL,dname VARCHAR(14),loc VARCHAR(13)
);
部门表数据
INSERT INTO dept (DEPTNO, DNAME, LOC)
VALUES (10, ACCOUNTING, NEW YORK);INSERT INTO dept (DEPTNO, DNAME, LOC)
VALUES (20, RESEARCH, DALLAS);INSERT INTO dept (DEPTNO, DNAME, LOC)
VALUES (30, SALES, CHICAGO);INSERT INTO dept (DEPTNO, DNAME, LOC)
VALUES (40, OPERATIONS, BOSTON);结束语
本期内容到此结束。关于本系列的其他博客可以查看我的MySQL专栏。
本系列的博客主要是记录学习经历并总结阶段的知识点。全篇的操作过程由笔者完成并核验在部分定义上有参考其他的内容。本身也是新手多多包涵。
期待与你在下一期博客中再次相遇
——放了挺久的【H2O2】
