《sql——习题集》由会员分享，可在线阅读，更多相关《sql——习题集（27页珍藏版）》请在金锄头文库上搜索。
1、SQL 习题集习题集数据库拆表原则数据库拆表原则1.简单字段中的复合字段：解决方法：向上打通，或拆成两表简单字段中的复合字段：解决方法：向上打通，或拆成两表 2.完全依赖：知道完全依赖：知道 a 就能知道就能知道 b,返过来就不一定返过来就不一定.实现：将同一个主题的东西放在一起实现：将同一个主题的东西放在一起 3.无传递依赖。将传递依赖转为直接依赖。原因：减少插入冗余。减少增删异常。无传递依赖。将传递依赖转为直接依赖。原因：减少插入冗余。减少增删异常。 4.当出现一对多时不能横拆，一定拆成两个表，特别的固定范围可以横拆当出现一对多时不能横拆，一定拆成两个表，特别的固定范围可以横拆(签到管理签
2、到管理) 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字段枚举
3、字段枚举 查询教师的查询教师的 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)把一个检索结果作为查询字段把一个检索结果作为查询字段 本学校的师生比例本学校的师
4、生比例 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 在聚合统计函数中统计不重复指定字段在聚合统计函数中统计
5、不重复指定字段 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 en
6、d ) 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 (
7、 select 教师教师_ID, 教师名教师名, 出生日期出生日期, 系系_ID, 性别性别_ID, 职称职称_ID from dbo.教师教师 )lin sqlserver2000 中临时表通常要起别名中临时表通常要起别名 where lin.教师教师_ID 、 =、 、 = select 教师教师_ID from dbo.教师教师 where 教师教师_ID=1 and 教师教师_ID=1 并且并且 教师教师_ID any ( select 教师教师_ID from dbo.教师认课教师认课 ) 9. all 全部全部 相当于相当于 大于最大大于最大 max select * from d
8、bo.教师教师 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
9、 by 性别性别_ID,系系_ID 3 多表查询中的分组应用多表查询中的分组应用 select count(*) as rs,系名系名 from dbo.学生学生 xs inner join dbo.系系 xi on xs.系系_ID = xi.系系_IDgroup by 系名系名 4 对于计算字段的分组应用对于计算字段的分组应用 查询出计算机系学生数和其它系的学生数查询出计算机系学生数和其它系的学生数 select count(*),( case when 系系_ID=1 then jsjx else qt end ) from dbo.学生学生 group by ( case when 系
10、系_ID=1 then jsjx else qt end ) 5 分组条件分组条件 having 的用法的用法 系人数在系人数在 10 人以上的系人以上的系 select count(*) as shu ,系系_ID from dbo.学生学生 group by 系系_ID having count(*) 10 或写成或写成 select * from ( select count(*) as shu ,系系_ID from dbo.学生学生 group by 系系_ID ) linshi where linshi.shu 10order by 集锦集锦 1.对单个字段的排序对单个字段的排序(
11、asc 升序可省略升序可省略 desc 降序降序) select 教师教师_ID, 教师名教师名, 出生日期出生日期, 系系_ID, 性别性别_ID, 职称职称_ID from dbo.教师教师 order by 教师名教师名 2.多字段的排序（先按第一个字段排序，如果相同再按第二个字段排序）多字段的排序（先按第一个字段排序，如果相同再按第二个字段排序） 写法写法 1。 select 教师教师_ID, 教师名教师名, 出生日期出生日期, 系系_ID, 性别性别_ID, 职称职称_ID from dbo.教师教师 order by 性别性别_ID, 系系_ID desc 写法写法 2。 sele
12、ct 教师教师_ID , 教师名教师名, 出生日期出生日期, 系系_ID as xxx , 职称职称_ID from dbo.教师教师 order by 4, 性别性别_ID desc 4 代表代表 select 后面后面 第四个字段第四个字段 3.按笔画排序按笔画排序 select 教师教师_ID, 教师名教师名, 出生日期出生日期, 系系_ID, 性别性别_ID, 职称职称_ID from dbo.教师教师 order by 教师名教师名 Collate Chinese_PRC_Stroke_ci_as desc4.order by 可按给定表达式计算结果排序可按给定表达式计算结果排序 按
13、教师年龄大小降序排序按教师年龄大小降序排序 select 教师教师_ID, 教师名教师名, 出生日期出生日期, 系系_ID, 性别性别_ID, 职称职称_ID from dbo.教师教师 order by DATEDIFF( yy, 出生日期出生日期,getdate() ) desc 将本年入学新生排在前面，老生排在后面将本年入学新生排在前面，老生排在后面 select 学生学生_ID, 姓名姓名, 出生日期出生日期, 性别性别_ID, 籍贯籍贯_ID, 系系_ID, 届届, 班级班级, ( case when 届届 = 2006 then 1 else 0 end ) from dbo.学生
14、学生 order by ( case when 届届 = 2006 then 1 else 0 end ) desc 5.随机排序随机排序 order by newid()其它：其它： -union 合并合并 将本校学生与教师名字全部查询出来（去掉重复）将本校学生与教师名字全部查询出来（去掉重复） 。 select 教师名教师名 from dbo.教师教师union select 姓名姓名 from dbo.学生学生 将本校学生与教师名字全部查询出来（不去掉重复）将本校学生与教师名字全部查询出来（不去掉重复） 。 select 姓名姓名 from dbo.学生学生union all selec
15、t 姓名姓名 from dbo.学生学生常用函数常用函数 -数学函数数学函数 select ABS(-9)-绝对值绝对值 select CEILING(9.00001)-比输入值大的最小整数比输入值大的最小整数 select FLOOR(9.9)-比输入值小的最大整数比输入值小的最大整数 select PI()-派派 select POWER(2,3)-第一个参数的第二个参数次方第一个参数的第二个参数次方 select SQRT(9)-平方根平方根 select SIGN(0)-返回参数的正负返回参数的正负 select ROUND( 892.35,1,7 ) select ROUND( 892.35,1,0 )select 1+FLOOR( LOG10(48091) ) -随机数随机数 select FLOOR(RAND()*10) -字符串函数字符串函数- select LEFT(aaaaa,2)-左截取左截取 select RIGHT(123456,2)-右截取右截取 select LEN(1)-字符长度字符长度 select LOWER(aaBN)-全小写全小写 select UPPER(aaBN)-全大写全大写 select rtrim( LTRIM( aaa ) )+*-
