做网站是互联网开发吗,为什么做金融网站犯法,iis7 asp网站运行缓慢,沈阳市建设工程管理中心之前学过了基本的查询#xff0c;虽然已经够80%的使用场景了#xff0c;但是依旧需要了解剩下的20%。
一、多表笛卡尔积#xff08;多表查询#xff09; 以前我们使用基本查询的时候#xff0c;from后面就跟一张表名#xff0c;在多表查询这里#xff0c;from后面可以跟…之前学过了基本的查询虽然已经够80%的使用场景了但是依旧需要了解剩下的20%。
一、多表笛卡尔积多表查询 以前我们使用基本查询的时候from后面就跟一张表名在多表查询这里from后面可以跟多张表名。
这里有两张测试表一张是用户的成绩表还有一张是用户的信息表。 假设我们直接在from后面跟上这两张表 我们发现它们的组合方式就是拿其中一张表的某一条数据依次跟另一张表的所有数据进行组合。就是穷举组合。
但是我们发现如果id不相等的话查出来的结果是没有意义的所以我们可以加判断条件使查询结果合理并且id列是重复的我们可以指定显示哪些列。
select exam_result.id,name,chinese,qq,age from exam_result,user where exam_result.id user.id; 在mysql下一切皆为表。
比如我们再来查询年龄在13岁到15岁之间的。 二、自连接
自连接是指在同一张表连接查询。 这样子写是不行的。
我们可以对表进行重命名后再作查询。 案例1找到数学成绩比孙悟空低的同学 方法1可以用子查询的方式
select * from exam_result where math (select math from exam_result where name 孙悟空);方法2自连接。直接在同一张表里面自连接查询记得重命名。
select * from exam_result t1,exam_result t2 where t2.name孙悟空 and t1.matht2.math; 另外我们其实只需要t1表中的信息那么可以指定一下 三、子查询
子查询是指嵌入在其他sql语句中的select语句也叫嵌套查询。
单行子查询 比如之前的案例找到数学成绩比孙悟空低的同学
select * from exam_result where math (select math from exam_result where name 孙悟空); 因为在子查询那里查出来的条件是单列单行的所以叫做单行子查询 多行子查询
与单行不同的是虽然多行的子查询条件还是只有一列但是有多行。
这里主要用到三个关键字inallany。
以之前的员工表为例 in案例查询和10号部门的工作岗位相同的雇员的名字岗位工资部门号但是不包含10自己
select ename,job,sal,deptno from emp where job in (select distinct job from
emp where deptno10) and deptno10; all关键字案例显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename, sal, deptno from EMP where sal all(select sal from EMP where
deptno30); any关键字 显示工资比部门 30 的任意员工的工资高的员工的姓名、工资和部门号包含自己部门 的员工 select ename, sal, deptno from EMP where sal any(select sal from EMP where
deptno30); 多列子查询 单行子查询是指子查询只返回单列单行数据多行子查询是指返回单列多行数据都是针对单列而言的而多列子查询则是指查询返回多个列数据的子查询语句 案例 查询和 SMITH 的部门和岗位完全相同的所有雇员不含 SMITH 本人 select ename from EMP where (deptno, job)(select deptno, job from EMP
where enameSMITH) and ename SMITH; 总结任何时刻我们查出来的临时结构本质在逻辑上也是表结构。 在from子句中使用子查询
子查询语句出现在from子句中。这里要用到数据查询的技巧把一个子查询当做一个临时表使用。
案例显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
//获取各个部门的平均工资将其看作临时表
select ename, deptno, sal, format(asal,2) from EMP,
(select avg(sal) myavg, deptno dt from EMP group by deptno) tmp
where EMP.sal tmp.myavg and EMP.deptnotmp.dt; 案例查找每个部门工资最高的人的姓名、工资、部门、最高工资
select EMP.ename, EMP.sal, EMP.deptno, ms from EMP,
(select max(sal) mymax, deptno from EMP group by deptno) tmp
where EMP.deptnotmp.deptno and EMP.saltmp.mymax; 案例显示每个部门的信息部门名编号地址和人员数量
-- 1. 对EMP表进行人员统计
select count(*), deptno from EMP group by deptno;
-- 2. 将上面的表看作临时表
select DEPT.deptno, dname, dept_num, loc from DEPT,
(select count(*) dept_num, deptno from EMP group by deptno) tmp
where DEPT.deptnotmp.deptno; 这里不仅使用了子查询还使用了多表查询。
总结解决多表问题的本质就是想办法将多表转化成单表在mysql中所有的select问题全都可以转化成单表问题。多表查询的指导思想
四、合并查询
在实际应用中为了合并多个select的执行结果可以使用集合操作符 unionunion all。
跟多表查询还是不同的。
union
该操作符用于取得两个结果集的并集。当使用该操作符时会自动去掉结果集中的重复行。 如图上就是将第一个数学大于70的结果表跟第二个语文大于85的结果的表合并在一起。
union all就是不去掉重复的行。
五、表的内外连接拓展
1.内连接 内连接实际上就是利用 where 子句对两种表形成的笛卡儿积进行筛选我们前面学习的查询都是内连接也是在开发过程中使用的最多的连接查询。 语法 select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件 也就是说之前的笛卡尔积就是内连接的一种。 比如之前的员工表案例显示SMITH的名字和部门名称 之前的写法
select ename, dname from EMP, DEPT where EMP.deptnoDEPT.deptno and
enameSMITH;
标准内连接写法
select ename, dname from EMP inner join DEPT on EMP.deptnoDEPT.deptno and
enameSMITH;
其中and换成where也可以。 2.左外连接 外连接分为左外连接和右外连接 如果联合查询左侧的表完全显示我们就说是左外连接。 语法
select 字段名 from 表名1 left join 表名2 on 连接条件
先创建一个学生表和成绩表并插入数据
-- 建两张表
create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,jack),(2,tom),(3,kity),(4,nono);
create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8); 并且我们注意到id是有关联性的但是两张表的信息不是完全吻合的就是为了方便测试外连接。
案例查询所有学生的成绩如果这个学生没有成绩也要将学生的个人信息显示出来
select * from stu left join exam on stu.idexam.id; 我们发现3号和4号即便没有成绩也会显示出来。如果我们用内连接的话就不会显示出来了 3.右外连接
如果联合查询右侧的表完全显示我们就说是右外连接。
语法
select 字段 from 表名1 right join 表名2 on 连接条件;
其实从功能上来说不需要右外连接也行因为我们只需要把顺序换一下就可以用左外连接实现右外连接的功能。 案例 对 stu 表和 exam 表联合查询把所有的成绩都显示出来即使这个成绩没有学生与它对应也要 显示出来 select * from stu right join exam on stu.idexam.id;
