Server Sql 多表查询、子查询和分页

时间:2022-01-23 03:42:08

一、多表查询:根据特定的连接条件从不同的表中获取所需的数据

  多表查询语法:

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

 

  但要注意where 不要省了,省略where 即为笛卡尔集

  笛卡尔集的产生条件:省略连接条件,连接条件无效

  笛卡尔集的造成的影响: 第一个表中的所有行与第二个表中的所有行相连接,数据无效。

    两张表间有一个相同的字段,才好进行有效的多表查询。

  使用表别名和表前缀:

SELECT t1.column, t2.column
FROM table1 t1, table2 t2
WHERE t1.column1 = t2.column2;

    查询时列名前,加表名或表别名前辍(如果字段在两个表中是唯一的可以不加)

  为表名定义别名,可以简化SQL书写,格式:“from 表名 别名”

    如:from emp e,dept d

   建议使用表的别名及表前缀,使用表别名可以简化查询,而使用表前缀则可以提高查询性能。

  多表查询的另一种写法:

  联接:内联接 —— inner join

select emp.column,dept.column
from emp  join dept 
on(emp.column= dept.column)

   注释:INNER JOIN 与 JOIN 是相同的 。

  联接:外联接 —— outer join ( left outer join, right outer join)

select emp.column,dept.column
from emp 
left join dept 
on(emp.column= dept.column) 
where dept.column is null

  注释:可省略outer,以左表为主,右表中只显示匹配左表的数据。

二、子查询就是位于SELECTUPDATE、或DELETE语句中内部的查询

  子查询的分类:

  独立子查询 —— 独立子查询可以独立执行,不受外部查询的影响

  相关子查询 —— 引用外部SQL语句中的一列或多列,需要与外部查询联合起来确定。

  子查询语法: 

SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list FROM table);

  子查询(内部查询)在执行主查询之前执行一次,然后主查询(外部查询)会使用该子查询的结果

  子查询的规则:  

  将子查询括在括号中

    将子查询放置在比较条件的右侧

    只有在执行排序Top-N分析时,子查询中才需要使用ORDER BY 子句

    单行运算符用于单行子查询,多行运算符用于多行子查询

  (1)单行子查询:

  即子查询只返回一行结果,使用单行的比较运算符:= ,>, >= ,< , <= ,<>

select ename,job from emp
where 
empno = (select empno from emp where mgr = 7902 );

  使用聚合函数的子查询

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

  HAVING子句中使用子查询

select deptno,min(sal) from emp 
group by deptno
having 
min(sal) > (select min(sal)  from emp where deptno = 20);

  在FROM 子句中使用子查询

select ename,job,sal  
from (select * from emp) as t1;

-- 注意:from子查询后边一定要跟别名

 

单行子查询中的常见错误:

  1. 子查询的结果返回多于一行

  2. 子查询中不能包含ORDER BY子句

  3. 子查询内部没有返回行,如下语句可以正确执行,但没有数据返回

select empno,ename from emp 
where 
sal = (select sal from emp where deptno = 20);
--Error 子句中无返回行,或返回多行均为错误。
select empno,ename from emp
where 
sal >(select avg(sal) from emp order by empno);
--Error  子句中不能包含order by

  (2)多行子查询:

  即子查询返回多行结果 —— 返回结果要采用多行比较运算符(in <not in>、any、all、some)。

  在多行子查询中使用IN 操作符

select empno,ename,job from emp 
where 
sal in (select max(sal) from emp group by deptno);

 --IN 给定的值是否与子查询或列表中的任一值相匹配 就成立。

 --NOT IN 给定的值是否与子查询或列表中的所有值都不匹配 就成立。

 注意:要防止子查询返回空值,因为只要空值成为子查询的一部分,就不能用notin运算符。

select empno,ename,job 
from emp 
where 
sal < any(select avg(sal) from emp group by deptno);

 --ALL 对所有数据都满足条件,整个条件才成立

 --ANY 只要有一条数据满足条件,整个条件就成立

(3)多列子查询:

  子查询只返回多列结果,多列子查询分为:成对比较多列子查询、非成对比较多列子查询

  成对比较 —— 查询工资为部门最高的记录  

select * from scott.emp 
where (sal,job) 
in(select max(sal),job from scott.emp group by job);

  非成对比较 —— 实现了与上述类似的功能

select * from scott.emp 
where 
sal in (select max(sal) from scott.emp group by job) 
and job in (select distinct job from scott.emp);

 

  (4)相关子查询:

 

    子查询中使用了主查询中的某些字段,

 

  相关子查询(也称为重复子查询)的查询中,子查询依靠外部查询获得值。这意味着子查询是重复执行的,主查询选择的每一行均执行一次子查询。

 

  --查询工资高于同一部门的员工的部门号,姓名,工资

 

select deptno,ename,sal from emp outer 
where 
sal >(select avg(sal) from emp inner where inner.deptno = outer.deptno);

 

  (5) 嵌套子查询:即位于子查询内部的子查询,嵌套可达32层(实际工作中嵌套超过两层就要被打PP了-.-),然而应尽量避免使用嵌套子查询,使用表连接的查询性能会更高。

三、分页

  方法1:适用于 SQL Server 2000/2005

SELECT TOP 10 *
FROM tableName
WHERE id 
NOT IN (
    SELECT TOP 10*(pageNum-1) id FROM tableName ORDER BY id
)
ORDER BY id

  方法2:适用于 SQL Server 2000/2005

SELECT TOP pageSize * 
FROM 
(
    SELECT ROW_NUMBER() OVER (ORDER BY id) AS rows,* FROM tableName
 ) aliasName 
WHERE rows > pageSize*(currentPage-1)

-- pageSize 每页显示条数
-- currentPage 当前页数
-- aliasName 意指别名