1 --题1.按每个表要求,为4个表增加唯一索引,写出方法或语句。 2 use sql_test 3 go 4 create index org_index on org(orgid); 5 create index branch_index on branch(orgid, brhid); 6 create index user_index on users(userid); 7 create index msg_index on msg(sendsno); 8 9 --题2.select 10 --a按照如下格式显示用户详细信息 11 -- userid,username,orgid,brhid,userprop,orgname,brhname,userpropname 12 --例: 1 ,员工1 ,0001 ,0101 ,0 ,公司1 ,销售1部,员 13 select ROW_NUMBER() OVER(order by c.brhid) as userid, a.username, a.orgid, a.brhid, a.userprop, b.orgname,c.bhname as brhname, 14 (case when userprop=‘0‘ then ‘经理‘ 15 when userprop=‘1‘ then ‘副经理‘ 16 when userprop=‘2‘ then ‘助理‘ 17 else ‘员工‘ end) as userpropname 18 from users a, org b, branch c 19 where a.orgid = b.orgid and b.orgid=c.orgid and c.brhid = a.brhid 20 21 --b使用左联按照如下格式显示用户详细信息,没有的字段内容用"-"显示 22 -- userid,username,orgid,brhid,userprop,orgname,brhname,userpropname 23 --例: 1 ,员工1 ,0001 ,0101 ,0 ,公司1 ,销售1部,员工 24 select (ROW_NUMBER() OVER(order by a.brhid)) as userid, isnull(a.username,‘-‘) as username, isnull(b.orgid,‘-‘) as orgid, 25 isnull(c.brhid,‘-‘) as brhid, isnull(a.userprop,‘-‘) as userprop,isnull(b.orgname,‘-‘) as orgname, isnull(c.bhname,‘-‘) as brhname, 26 isnull((case when userprop=‘0‘ then ‘经理‘ 27 when userprop=‘1‘ then ‘副经理‘ 28 when userprop=‘2‘ then ‘助理‘ 29 else ‘员工‘ end), ‘-‘) as userpropname 30 from users a left join org b on a.orgid = b.orgid left join branch c on b.orgid = c.orgid and a.brhid = c.brhid 31 32 --题3.insert 33 --通过msg表发送2条消息,消息格式如下: 34 --消息1发给全体人员:公司名+部门名+职务名+姓名+你好,您的用户号码为:userid。 35 --消息2发给员工:员工+姓名+你好,下午3点在部门助理(助理姓名+用户号码)工位集合,发送人:(经理姓名+用户号)。 36 37 --消息1:(注意insert后的select语句不能用括号括起来,会报语法错误) 38 --truncate table msg 39 insert into msg 40 select ROW_NUMBER() OVER(order by a.userid), GETDATE(), a.userid, b.orgname+‘+‘+c.bhname+‘+‘+ 41 (case when userprop=‘0‘ then ‘经理‘ 42 when userprop=‘1‘ then ‘副经理‘ 43 when userprop=‘2‘ then ‘助理‘ 44 else ‘员工‘ end)+‘+‘+a.username+‘+‘+‘你好,您的用户号码为:‘+‘+‘+a.userid, 1 45 from users a, org b, branch c where a.orgid=b.orgid and a.brhid=c.brhid and b.orgid=c.orgid 46 --消息2: 47 --truncate table msg 48 insert into msg 49 select ROW_NUMBER() OVER(order by a.userid), GETDATE(), a.userid, ‘员工‘+‘+‘+a.username+‘+‘+ 50 ‘你好,下午3点部门助理(‘+a.username+‘+‘+a.userid+‘)工位集合,发送人:(‘+a.username+‘+‘+userid+‘)。‘, 1 51 from users a, org b, branch c 52 where a.orgid=b.orgid and b.orgid=c.orgid and a.brhid=c.brhid and a.username like ‘%员工%‘ 53 select * from msg; 54 55 --题4.update 56 --0003公司各部门号码变更,研发用销售,销售用市场,市场用工程 ,工程用研发, 57 --按要求修改branch和users 58 if exists(select * from sys.objects where type=‘U‘ and name=‘branch_bak‘) 59 drop table branch_bak 60 if exists(select * from sys.objects where type=‘U‘ and name=‘users_bak‘) 61 drop table users_bak 62 select * into users_bak from users; 63 select * into branch_bak from branch; 64 65 --select SUBSTRING(bhname,3,1) from branch; 66 --select a.brhid,b.brhid from branch a, branch_bak b 67 --where a.orgid=‘0003‘ and b.orgid=‘0003‘ and substring(a.bhname, 3, 1)=substring(b.bhname, 3, 1) and a.bhname like ‘%工程%‘ and b.bhname like ‘%研发%‘ 68 update a set a.brhid=b.brhid from branch a, branch_bak b 69 where a.orgid=‘0003‘ and b.orgid=‘0003‘ and substring(a.bhname, 3, 1)=substring(b.bhname, 3, 1) and a.bhname like ‘%研发%‘ and b.bhname like ‘%销售%‘ 70 update a set a.brhid=b.brhid from branch a, branch_bak b 71 where a.orgid=‘0003‘ and b.orgid=‘0003‘ and substring(a.bhname, 3, 1)=substring(b.bhname, 3, 1) and a.bhname like ‘%销售%‘ and b.bhname like ‘%市场%‘ 72 update a set a.brhid=b.brhid from branch a, branch_bak b 73 where a.orgid=‘0003‘ and b.orgid=‘0003‘ and substring(a.bhname, 3, 1)=substring(b.bhname, 3, 1) and a.bhname like ‘%市场%‘ and b.bhname like ‘%工程%‘ 74 update a set a.brhid=b.brhid from branch a, branch_bak b 75 where a.orgid=‘0003‘ and b.orgid=‘0003‘ and substring(a.bhname, 3, 1)=substring(b.bhname, 3, 1) and a.bhname like ‘%工程%‘ and b.bhname like ‘%研发%‘ 76 77 --select * from users a, branch_bak b, branch c 78 --where a.brhid=b.brhid and b.bhname=c.bhname and a.orgid=‘0003‘ and b.orgid=‘0003‘ and c.orgid=‘0003‘ 79 update a set a.brhid=c.brhid from users a, branch_bak b, branch c 80 where a.brhid=b.brhid and b.bhname=c.bhname and a.orgid=‘0003‘ and b.orgid=‘0003‘ and c.orgid=‘0003‘ 81 82 83 --题5.delete 84 --删除发给0003公司人员msg表中,发送内容有"发送人"信息的记录。 85 delete from msg where msgtxt like ‘%发送人%‘ and userid like ‘0003%‘ 86 87 --题6.统计group by 88 --一条语句显示:机构名,部门名,经理数,副经理数,助理数,员工数,用户总数 89 select b.orgname as ‘机构名‘, c.bhname as ‘部门名‘, 90 sum(case when a.userprop=‘0‘ then 1 else 0 end) as ‘经理数‘, 91 sum(case when a.userprop=‘1‘ then 1 else 0 end) as ‘副经理数‘, 92 sum(case when a.userprop=‘2‘ then 1 else 0 end) as ‘助理数‘, 93 sum(case when a.userprop=‘3‘ then 1 else 0 end) as ‘员工数‘, 94 count(*) as ‘用户总数‘ 95 from users a, org b, branch c 96 where a.orgid=b.orgid and b.orgid=c.orgid and a.brhid=c.brhid 97 group by b.orgname, c.bhname 98 99 --题7.字符串操作substring,charindex,len 100 --从user表中的userid字段中取出用"-"号分隔的三部分内容。 101 --例如0001-0101-201: 第一部分,第二部分,第三部分,字符串总长度, 102 -- 0001 ,0101 ,201 ,13 103 104 --法一: 105 select substring(userid, 1, charindex(‘-‘, userid)-1) as ‘第一部分‘, 106 substring(userid, charindex(‘-‘,userid)+1, charindex(‘-‘,userid,charindex(‘-‘,userid)+1)-1-charindex(‘-‘,userid)) as ‘第二部分‘, 107 substring(userid, len(userid)-charindex(‘-‘,reverse(userid))+2, charindex(‘-‘,reverse(userid))-1) as ‘第三部分‘, 108 len(userid) as ‘字符串总长度‘ 109 from users order by userid; 110 --法二: 111 select substring(userid, 1, CHARINDEX(‘-‘, userid)-1) as ‘第一部分‘, 112 substring(userid,CHARINDEX(‘-‘, userid)+1,len(userid)-CHARINDEX (‘-‘, REVERSE(userid))-CHARINDEX(‘-‘, userid)) as ‘第二部分‘, 113 substring(REVERSE(userid), 1, charindex(‘-‘, reverse(userid))-1) as ‘第三部分‘, 114 len(userid) as ‘字符串总长度‘ from users order by userid 115 --法三: 116 select LEFT(userid, CHARINDEX(‘-‘, userid)-1) as ‘第一部分‘, 117 LEFT(RIGHT(userid, len(userid)-CHARINDEX(‘-‘, userid)),charindex(‘-‘, RIGHT(userid, len(userid)-CHARINDEX(‘-‘, userid)))-1) as ‘第二部分‘, 118 reverse(LEFT(REVERSE(userid), charindex(‘-‘, reverse(userid))-1)) as ‘第三部分‘, 119 LEN(userid) as ‘字符串总长度‘ from users order by userid 120 121 --题8.日期时间操作convert,datepart,datediff... 122 --a,从msg表中查询结果,格式如下: 123 -- 序号,日期,星期几,年,月,日,时,分,秒,毫秒,距现在时间间隔(小时),距现在时间间隔(分钟),距现在时间间隔(秒) 124 --select * from msg; 125 select sendsno as ‘序号‘, CONVERT(varchar(20),sendtime,102) as ‘日期‘,datename(W, sendtime) as ‘星期‘,DATEPART(YYYY,sendtime) as ‘年‘, 126 datepart(mm,sendtime) as ‘月‘,datepart(dd,sendtime) as ‘日‘,datepart(hh,sendtime) as ‘时‘,datepart(n,sendtime) as ‘分‘,datepart(s,sendtime) as ‘秒‘, 127 datepart(ms,sendtime) as ‘毫秒‘,datediff(HH,sendtime,getdate()) as ‘距现在的时间间隔(小时)‘,datediff(N,sendtime,getdate()) as ‘距现在的时间间隔(分钟)‘, 128 datediff(ss,sendtime,getdate()) as ‘距现在的时间间隔(秒)‘ 129 from msg; 130 --b,找出序号30消息发送时间前后小于等于1秒的全部记录。 131 select * from msg where abs(datediff(ss, (select sendtime from msg where sendsno=30), sendtime)) <= 1; 132 133 134 --题9.编写函数 fn_get_orgbrh_name 135 --select userid,username,orgid,brhid,dbo.fn_get_orgbrh_name(orgid,0) as orgname,dbo.fn_get_orgbrh_name(orgid,brhid) as brhname from users 136 --编写能够返回机构名和部门名称的函数,使用函数实现如下显示 137 -- userid,username,orgid,brhid,orgname,brhname 138 --例: 1 ,员工1 ,0001 ,0101 ,公司1 ,销售1部 139 if exists (select * from sysobjects where name=‘fn_get_orgbrh_name‘) 140 drop function fn_get_orgbrh_name 141 go 142 create function fn_get_orgbrh_name(@orgid_tmp char(5), @brhid_tmp char(5)) 143 returns varchar(20) 144 as 145 begin 146 declare @tmp varchar(20) 147 if(CAST(@brhid_tmp as int)=0) 148 select @tmp=orgname from sql_test..org where orgid=@orgid_tmp 149 --set @tmp=(select orgname from org where orgid=@orgid_tmp) --注意这样写必须加括号,否则语法错误 150 else 151 select @tmp=bhname from sql_test..branch where @orgid_tmp=orgid and @brhid_tmp=brhid 152 return @tmp 153 end 154 go 155 select userid,username,orgid,brhid,dbo.fn_get_orgbrh_name(orgid,0) as orgname,dbo.fn_get_orgbrh_name(orgid,brhid) as brhname from users 156 157 158 --题10.编写存储过程 up_sql_test_proc 159 --存储过程使用游标逐条判断,找出uses表中,员工编号中包含的机构和部门号码,与实际所属的机构和部门号码不一致的用户, 160 --同时发送msg表消息给该员工,消息内容为现有号码和机构部门的不一致情况的描述和通知该员工来变更资料, 161 --存储过程返回值格式:执行成功,不一致人数【10】人。 162 --注意:用户编号的组成是机构编码+部门编码+用户编号三部分组成。 163 164 --select * from msg 165 if exists (select * from sysobjects where name=‘up_sql_test_proc‘) 166 drop procedure up_sql_test_proc 167 go 168 169 create proc up_sql_test_proc 170 as 171 begin 172 if Cursor_Status(‘global‘,‘tmp_cursor‘)>0 173 deallocate usercursor 174 175 declare tmp_cursor cursor global scroll for select userid, orgid,brhid from users 176 declare @t1 varchar(20), @t2 varchar(20), @t3 varchar(20), @t4 varchar(20), @t5 varchar(20), @cnt int, @msg varchar(50) 177 open tmp_cursor 178 fetch next from tmp_cursor into @t1, @t2, @t3 179 set @cnt = 0 180 while(@@fetch_status = 0) 181 begin 182 set @t4=SUBSTRING(@t1, 1, CHARINDEX(‘-‘, @t1)-1) 183 set @t5=substring(@t1, charindex(‘-‘, @t1)+1, len(@t1)-charindex(‘-‘,reverse(@t1))-CHARINDEX(‘-‘, @t1)) 184 --print @t4 185 --print @t5 186 if(@t2 != @t4 or @t3 != @t5) 187 begin 188 set @cnt = @cnt+1 189 insert into msg values(1, GETDATE(), @t1, ‘你好,你的机构号码‘+@t2+‘和部门号码‘+@t3+‘有误,请及时更正!!‘, 1) 190 end 191 fetch next from tmp_cursor into @t1, @t2, @t3 192 end 193 close tmp_cursor 194 deallocate tmp_cursor 195 set @msg=‘执行成功,不一致人数【‘+convert(varchar(20), @cnt)+‘】人‘ 196 print @msg 197 end 198 GO 199 exec up_sql_test_proc