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.