SQL习题集
数据库拆表原则
1. 简单字段中的复合字段：解决方法：向上打通，或拆成两表
2. 完全依赖：知道a就能知道b,返过来就不一定.实现：将同一个主题的东西放在一起 3. 无传递依赖。将传递依赖转为直接依赖。原因：减少插入冗余。减少增删异常。 4. 当出现一对多时不能横拆，一定拆成两个表，特别的固定范围可以横拆(签到管理) 5. 多对多必有中间表
6. 代码表，大量重复的或枚举型可以用代码表. 7. 能计算的字段不要 数据定义语句DDL create alter drop 数据控制语句DCL grant deny revoke 数据操纵语句DML
select update insert delete 一.SQL Server 2000 Select 变化集锦
1． 查询所有字段(效率低)
查询所有的老师 select *
from dbo.教师 2．字段枚举 查询教师的ID，及姓名两项
select 教师_ID,教师名 from dbo.教师
3．字段取别名方法有二
1）as可以省略
select 教师名 as teacher from dbo.教师 2）
select teacher = 教师名 from dbo.教师
4.字段的可计算性 1) 简单字段计算
教师工资的10% select 工资*0.1 from dbo.教师 2)把一个检索结果作为查询字段
本学校的师生比例 select ( select count(*) from dbo.教师 ) /1.0/
( select count(*) from dbo.学生 )
5 取检索结果的前几个
select top 3 教师_ID from dbo.教师
6 取检索结果总数的百分比
select top 30 percent 教师_ID from dbo.教师
7 去掉重复记录 去掉的是完全重复
select distinct 性别_ID from dbo.教师
8 在聚合统计函数中统计不重复指定字段
select count(distinct 系_ID ) from dbo.教师
9 case when then else end在seelct中的应用
从教师表中查询，将性别_id是1的显示为男,是2的显示为女 select ( case when 性别_ID=1 then '男' when 性别_ID=2 then '女' end )
from dbo.教师 横向查询男女人数
select sum( case when 性别_ID=1 then 1 else 0 end ) as 'nan' , sum( case when 性别_ID=2 then 1 else 0 end ) as 'nv'
from dbo.教师
10 可以重复列出表的的字段
select *,*,* from 教师 11 检索结果放常量
select 教师名, '出生于',出生日期 from dbo.教师
-----------------------------------
From变化集锦 1．从一个表中检索 select *
from dbo.教师
2 从一个检索结果中再检索即从临时表中检索
select * from ( select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师
)lin –sqlserver2000中临时表通常要起别名 where lin.教师_ID <4
3.从多表中检索，构成迪卡尔乘积（效率低）
select dbo.教师.*,系名 from dbo.教师,dbo.系
where dbo.教师.系_ID=dbo.系.系_ID 4 多表的联接查询 1)左连接（尊重左边）
select *
from dbo.系 left join dbo.教师 on dbo.系.系_ID=dbo.教师.系_ID
2）右连接（尊重右边）
select *
from dbo.系 right join dbo.教师 on dbo.系.系_ID=dbo.教师.系_ID
3)内连接（都不尊重,即两边都得有）下例为两个以上表的连接写法 select *
from dbo.系 inner join dbo.教师
on dbo.系.系_ID=dbo.教师.系_ID inner join dbo.教师认课 on dbo.教师.教师_ID=dbo.教师认课.教师_ID 4)全连接（都尊重）
select * from dbo.系 full join dbo.教师 on dbo.系.系_ID=dbo.教师.系_ID
5. 给被检索表起别名 注意：起了别名就一定用别名引用字段
错误的
select dbo.教师.教师名 from dbo.教师 js 正确的
select js.教师名 from dbo.教师 js where集锦
1． 常规关系运算 >、 >=、 <、 <=、 !=、 <>、 =
select 教师_ID from dbo.教师 where 教师_ID<>1 只检索表结构不要任何数据
Select * From dbo.教师 Where 1!=1 2． 常规逻辑运算 not 、and 、or
select 教师_ID from dbo.教师
where not( 教师_ID>=1 and 教师_ID<=8 )
3.区间 教师_ID>=1 并且 教师_ID<=8
select 教师_ID from dbo.教师
where 教师_ID between 1 and 8
4.检索null值
select 教师_ID from dbo.教师
where 出生日期 is null
5.关于字符的模糊查询 1)姓张的所有老师 select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID
from dbo.教师
where 教师名 like '%张' --%代表任意多个字 2)姓张的但名字只有两个字的老师
select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID
from dbo.教师
where 教师名 like '_张' --_ 只代表一个字 3)名字中含有 国 字的老师
select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师
where 教师名 like '%国%'
4)教师名的第一个字母在a-m之间的教师
select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师
where 教师名 like '[a-m]%'
5)教师名的第一个字母在 不在 a-m之间的教师
select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师
where 教师名 like '[^a-m]%'
6)教师名的第一个字母是a或b或m的教师
select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师
where 教师名 like '[a,b,m]%'
7) 教师名的第一个字母是a到k之间或o到x之间的教师 select 教师_ID, 教师名, 出生日期, 系_ID, 性别_ID, 职称_ID from dbo.教师
where 教师名 like '[a-k,o-x]%'
6．关于IN的用法 1)枚举型
select *
from dbo.教师
where 教师_ID in ( 1,3,5)
select *
from dbo.教师
where 教师_ID not in ( 1,3,5)
2)嵌套子查询型 有课上的老师 select *
from dbo.教师 where 教师_ID in ( select 教师_ID from dbo.教师认课 )
7. exists 判断子查询是否有结果（强调有无，不关心具体是什么）
select *
from dbo.教师
where exists --存在 (
select *
from dbo.教师认课
where dbo.教师认课.教师_ID=dbo.教师.教师_ID )
8. any 其中一个
教师ID大于所有 教师ID中任意一个的教师(相当于大于最小的教师ID) select *
from dbo.教师
where 教师_ID > any ( select 教师_ID from dbo.教师认课 )
9. all 全部
相当于 大于最大max select *
from dbo.教师
where 教师_ID > all (
select 教师_ID
from dbo.教师认课 ) Group by 集锦
1.group by 通常与聚合函数(avg ,sum ,count,max,min)配合使用
查询出学生表中男女生人数 select count(*) as renshu from dbo.学生 group by 性别_ID 2针对多字段的分组
每个系的男女生人数
select count(*) as renshu from dbo.学生
group by 性别_ID,系_ID 3 多表查询中的分组应用 select count(*) as rs,系名
from dbo.学生 xs inner join dbo.系 xi on xs.系_ID = xi.系_ID