《SQL经典实例》 第二、三章 排序和使用多张表

1.排序

--随机返回前几行  order by NEWID()
select top 3 * from GeeSun_Role order by NEWID()

--查找Null值   is null /  非null值   is not null

--null值转换为实际值  coalesce(createtor,'未知')  有值时直接返回该列的值，值为null时返回指定值
select coalesce(createtor,'未知') from geesun_role

--模式查找   PLCNO在（1，2，3）范围内 角色名以'生产'开头或以'师'结尾的记录
select * from geesun_role where PLCRoleNo in (1,2,3) and (RoleName like '生产%' or RoleName like '%师')

--查询结果排序   order by   多字段排序 在order by 子句中用逗号隔开
    --按字串排序  使用order by substring(RoleName,0,3)

--对同时包含字母和数字的数据进行排序 使用replace()和translate()进行操作
            TRANSLATE()函数的行为类似于调用多个REPLACE()函数。

--批处理时处理null值  如：需要将null值排在列的前面或后面

--根据依赖于数据的键进行排序

2.合并

--合并多各行集   表不需要有相同的键，但他们的数据类型必须相同。 union all(不会剔除重复的行)  union （会自动剔除重复的行）。但union使用时可能引发排序以用于消除重复的行，与使用 union all+distinct 效果大致相同 ，除非必要，否则不要轻易使用Union 和distinct

select * from geesun_role where ID <3
union all
select * from geesun_role where ID>3

--合并相关的行  join(inner)   显式两表记录的笛卡尔积
select * from geesun_role as a
inner join GeeSun_User as b
on a.id=b.RoleId

--查找两张表中相同的行  两张表合并后按条件查询。
select * from V1 inner join V2 on V1.id=V2.id where V1.id=3

--从一张表中筛选出没有出现在另一张表中的行（去重）   Except
select RoleName from V1
except
select RoleName from V2

--从一张表中检索另一张表中没有对应行的行
select top 10 * from GeeSun_User as a
left join GeeSun_Role as b
on a.RoleId=b.ID
where b.ID is null

--在查询中添加连接  不影响其他连接 先用内联获取笛卡尔积，再用外联，获取部分记录的格外列的值

--避免并识别笛卡尔积   在From字句的表直接执行连接，以返回正确的结果集
select * from GeeSun_User as a
left join GeeSun_Role as b
on a.RoleId=b.ID
where a.UserName='吴佳琦'

select * from GeeSun_User as a,GeeSun_Role as b
where a.UserName='吴佳琦' and a.RoleId=b.ID

--以上两种SQL 结果一致

--同时使用连接和聚合：查询部门编号为10的员工的薪水和奖金
select e.empno,
       e.ename,
       e.sal,
       e.deptno,
       e.sal*case when eb.type = 1 then .1
                  when eb.type = 2 then .2
                  else .3
             end as bonus
 from emp e, emp_bonus eb
where e.empno = eb.empno
  and e.deptno = 10


select 
e.empno,
       e.ename,
       e.sal,
       e.deptno,
       e.sal*case when eb.type = 1 then .1
                  when eb.type = 2 then .2
                  else .3
             end as bonus
from emp_bonus as eb
left join emp as e
on eb.empno=e.EMPNO
where DEPTNO=10

--以上两种SQL 结果一致

--3.10  使用外连接和聚合  计算部门编号为10的所有员工的薪水和奖金  （特别点：使用distinct去除重复记录）
select d.DEPTNO,sum(distinct e.SAL),sum(e.sal*case when eb.type = 1 then .1
                  when eb.type = 2 then .2
                  else .3
             end) from DEPT as d
left join EMP as e
on d.DEPTNO=e.DEPTNO
left join emp_bonus as eb
on e.EMPNO=eb.empno
where d.deptno=10
group by d.DEPTNO

--3.11 返回多张表中不匹配的行   查询不存在员工的部门
select * from DEPT as d 
left join emp as e
on d.DEPTNO=e.DEPTNO
where e.DEPTNO is null

    查询不存在员工的部门和不存在部门的员工。既需要使用full join 全外连接

--3.12 在运算和比较中使用null
       null与任何值包括null都不相等，需要做判断时，需要使用coalesce将null值进行一次转换。
    

3.