sql完整數(shù)據(jù)庫操作、存儲過程、登錄判斷,增刪改查(精)

1、create database AA go use AA go create table Student ( sid int primary key, sname nvarchar(20, sex nv archar(20, birthday datetime, class nv archar(10, pwd nv archar(10 gocreate table Course ( cid int identity(1,1 primary key, cname nvarchar(20 go create tableScore ( sid int, cid int, score int, pri

2、mary key(sid,cid go in sert into Course values(C# insertinto Course values(English insert into Student values(100 張三:男,1990-1- 12,一班,123insert into Student values(1002 李四,女,1990420, 班,456 insert into Studentvalues(1003 王五,男,1991-10-11,二班,789 insert into Student values(1004，趙六,男，1992-8-5,二班,101 inser

3、t into Student values(1005 天齊,男, 1992-5-5,三班,120 in sertinto Score values(1001,1,65 in sert into Score values(1001,2,60 in sert into Scorevalues(1002,1,50 in sert into Score values(1002,2,40 insert into Score values(1003,1,75insert into Score values(1003,2,60 insert into Score values(1004,1,72 inser

4、t into Scorevalues(1004,2,45 select * from student select sname from stude nt select sid,s name fromstude nt select * from stude nt order by sid desc -desc 降序 asc 升序 select * from stude ntorder by sex,class asc select * from stude nt order by birthday desc select * from stude ntwhere sid=1001 select

5、 * from stude nt where sex 男, and birthday1991-1-1 select * fromstudent where birthday between 1990-1-1 and 1991-1-1 select * from student wheresname like %五% -%是通配符 select distinct(class from student -去除重復(fù)項 selectcount(sid from student select count(sid from stude nt where sid=1001 and pwd=123 selec

6、tcoun t(sid,class from stude nt group by class select coun t(sid,sex from stude nt group by sexselect coun t(sid,sex,class from stude nt group by sex,class select coun t(sid,class fromstude nt where se|= group by class select sum(score from score select avg(score fromscore where sid=1001 select max(

7、score,cid from score group by cid select avg(score,cidfrom score group by cid hav ing avg(score60 select avg(score,sid,cid from score group bysid,cid hav ing avg(score59 select * from score where score = (select max(score from score select * fromstude nt where birthday = (select min( birthday from s

8、tude nt select * from stude nt select *from course select * from score select coun t(sid as Y_N from stude nt where sid=1001 andpwd=123-select avg(score as avg from score where sid=1001 -select coun t(sid,class from stude ntgroup by class -select coun t(sid,sex from stude nt group by sex -select cou

9、n t(sid,class,sexfrom stude nt group by class,sex -select coun t(sid,class from stude nt where sex=男g(shù)roupby class -內(nèi)連接 select studen t.sid,stude nt.s name,stude nt.class,score.score from stude nt inner join score onstude nt.sid = score.sid -三表鏈接 selectstude nt.sid,stude nt.s name,course.c name,score

10、.score from score inner joi n stude nt onstude nt.sid=score.sid inner join course on score.cid=course.cid where stude nt.s name 張三-用右外表查詢(以右表為基準(zhǔn))selectstude nt.sid,stude nt.s name,stude nt.class,score.score from stude nt right outer join scoreon stude nt.sid=score.sid select stude nt.sid,stude nt.s

11、name,stude nt.class,score.score fromstude nt left outer join score on stude nt.sid=score.sid where score.score is nul 交叉鏈接 (笛卡爾集合(少用 select student.sid,student.sname,score.score from student cross join score -嵌套查詢 -查詢最高分的學(xué)生 select sid,sname from student where sid in (select sid fromscore where score

12、 = (select max(score from score 總分最高的學(xué)生學(xué) 號 select sidfrom( select top 1 sum(score as s,sid from score group by sid order by s desc a -統(tǒng)計每門課程最高分的學(xué)生的學(xué)號select score.sid,score.cid,score.score from score,(select max(score as m,cid from score group by cid b where score.cid=b.cid andscore.score= b.m -查班級平均

13、分 select avg(c.score,c.class from (select a.class,b.score fromstudent a,score b where a.sid=b.sid c group by class 牛人平均成績 select avg(c.score,c.sidfrom (select a.sid,b.score from stude nt a,score b where a.sid=b.sid c group by sid -單科最高分的同學(xué)學(xué)號和姓名和班級selectdistinct(d.sid,d.sname,d.class from ( select c.

14、sid,c.sname,c.class from student c,( selectsid,score from score,( select max(score as s,cid from score group by cid a where a.s =score.score b where c.sid=b.sid d declare i int -i nt i =0; declare s nv archar(10 -string s;set i = 1; -i = 10; -set s = AAAAAAA -s = AAAAAAA while i59 then 及格else 不及格end

15、 from score -print i -本地測試用 print -print s - -if i=0 -if 條件語句-begin-print BBBBBBBBBBBBBBBBBBBBBBBB- end -else -beg in -pri ntSSSSSSSSSSSSS -e nd 使用存儲過程判斷用戶登錄信息 alter proc sp_login sid int,-輸入?yún)?shù) pwd nvarchar(20,-輸入?yún)?shù) s nvarchar(20 output -輸出參數(shù) as declare i intset i = (select count(1 from student wher

16、e sid=sid and pwd=pwd -return i if i = 1set s =合法用戶else set s = 非法用戶declare s nvarchar(20 exec sp_login1001,123,s output print s if(select min(score from score where sid=100190 print 學(xué)生1001 成績?nèi)績?yōu)秀else if(select min( score from score where sid = 100159 print 學(xué)生 1001 成績?nèi)考案馿lse print 學(xué)生 1001 有成績不及格-為表

17、創(chuàng) 建具有不同字段名的視圖 create view v_stu(sid,s name,sex as select sid,s name,sex from student -查看視圖數(shù)據(jù) select * from v_stu -使用存儲過程實現(xiàn)從表及聯(lián)刪除 alter proc sp_deletesid int as delete from score where sid = sid delete from stude nt where sid = sid execsp_delete 1001 使用存儲過程添加學(xué)生信息 -添加- 開始alter proc sp_add sid int, sn

18、ame nvarchar(20, sex nv archar(10, birthday datetime, class nv archar(10, pwd nvarchar(10, i int, r n varchar(10 output 一 個 過程可以有多個輸出參數(shù)但只有一個返回值- 輸出參數(shù)是任何類型 as if i = 0 beginif not exists (select sid from student where sid=sid begin insert into studentvalues(sid,sname,sex,birthday,class,pwd set 添加成功en

19、d else set r=重復(fù)添加end else begin update student set sname =sn ame,sex=sex,birthday=birthday,class=class,pwd=pwd where sid=sid ifrowcount 0 set r =修改成功else set r =修改無效end declare r int exec r =sp_add 1011 李四,男,1990-1-12, 一班,123 pri nt r-查找所有學(xué)生分?jǐn)?shù)- 一個- alter proc sp_selectAIIStude ntScore as select stud

20、e nt.sid,stude nt.sname,course.c name,score.score from score right join stude nt onstude nt.sid=score.sid left join course on score.cid=course.cid- exec sp_selectAIIStude ntScore select * from stude nt delete from stude nt wheresid= 0- 條件查詢學(xué)生分?jǐn)?shù) - 一個-create proc sp_selectstude ntscore sid int, sname

21、nv archar(10 as if sid0 begi n selectstude nt.sid,stude nt.s name,course.c name,score.score from score inner joi n stude nt onstude nt.sid=score.sid inner join course on score.cid=course.cid where stude nt.sid=sidend else beg in select stude nt.sid,stude nt.s name,course.c name,score.score from scor

22、einner join stude nt on stude nt.sid=score.sid inner join course on score.cid=course.cid wherestude nt.s name like %+s name+% end-exec sp_selectstude ntscore 1001, exec sp_selectstudentscor 三三 ，- -查找與刪除學(xué)生-一個- alter proc sp_operstudent sid int, i int as if i = 0 select * from student wheresid = sid e

23、lse delete from stude nt where sid = sid-登錄- 一個- alter procsp_logi n sid int, pwd nv archar(20 as declare i int set i = (select coun t(1 fromstude nt where sid = sid and pwd = pwd return i-查看學(xué)生分?jǐn)?shù)信息-alter proc sp_viewstude nt sid int, sum int output, avg int output, pid int output asdeclare cur curso

24、r for select avg(score p, sum(score s,sid from score group by sid order by sdesc ope n cur declare p int, s int, sid2 int, i int set i = 1 fetch from cur into p,s,sid2 while fetch_status = 0 begi n if sid2 = sid beg in set pid = i set sum= s set avg = p end set i = i + 1 fetch from cur into p, s,sid

25、2 end close curdeallocate cur select cn ame,score from score a,course b where a.cid = b.cid and a.sid =sid declare avg in t,pid in t,sum int exec sp_viewstude nt 1003 ,pid output,sumoutput,avg output print sum print avg print pid- 結(jié)束- -使用存儲過程添加新課程信息，并輸入新課程的-IDalter proc sp_addCoursecn ame nv archar(

26、10 as insert into course values(c name return ide ntity declare iint exec i = sp_addCourse PHP print i select * from course 實現(xiàn)數(shù)據(jù)表分頁查詢 -頁碼=總行數(shù) /每頁行數(shù) page -每頁行數(shù) size -總行數(shù) -paixu linshibiao zhua nyon gha nshuselect top 5 * from (select top (9-(2-1*5 * from employees order by employeeid desc a orderby a

27、.employeeid alter proc sp_page page in t 頁碼 table varchar(10,-表名稱 orderbyvarchar(10,-排序字段名 size int as declare sql varchar(500 -放 sql 語句字符串 set sql= declare count int set sql = sql + set co unt =(select coun t(1 from + table + ”set sql = sql + select top + str(size + * from (select top (cou nt - ( +

28、 convert(varchar(10,page + -1* +str(size+ * from + table + order by + orderby + desc a + order by a.+ orderby exec (sql print sql exec sp_page1,employees,employeeid,3 函數(shù) declare str varchar(10 set str = 1234 declare i intset i = 1234 -pri nt le n( str print len(ltrim(str(i print substring(str,1,2 pr

29、intfloor(rand(*1000 - print getdate(- Datetime.Now print dateadd(day,2,getdate( printdateadd(day,2,2011-10-20 往后添加 時間 print datediff(day,2011-10-25,2012-12-10-時間差 TimeSpa n. days print datepart(year,2011-10-25-獲取時間格式中的一部分(即年，月或日) print datepart(month,2011-10-25 print datepart(day,2011-10-25 print co

30、nvert( nvarchar(10,datepart(year,2011-10-25+ / + con vert( nvarchar(10,datepart(mo nth,2011-10-25 + / +con vert( nvarchar(10,datepart(day,2011-10-25按出生年份統(tǒng)計學(xué)生人數(shù) -sid counts yearsselect coun t(sid,datepart(year,birthday from stude nt group by datepart(year,birthday select* from table declare table n v

31、archar(10 set table = stude nt exec(select * from +table create proc sp_AA table varchar(10 as exec (select * from + table exec sp_AAstudent-編寫通用版的分頁存儲過程 -自定義函數(shù) create function fun(i int returns nvarchar(10 as beg in return convert(nv archar(10,i end print dbo.fun( 10-dbo 當(dāng)前數(shù)據(jù)庫里有效-定義函數(shù) 輸入姓名后返回學(xué)號 cre

32、ate function fun 2(s name nv archar(10 returns intas beg in declare i int set i = (select sid from stude nt where sn ame=s name return iend print dbo.fu n2 張三-查詢學(xué)號，課程號，分?jǐn)?shù) 同時在分?jǐn)?shù)欄將60 的成績直接輸出為不及格-學(xué)號課程分?jǐn)?shù)-1001 195 -1002 1 不及格 alter fun cti on fun 3(score int returns nv archar(10 as beg in declarer nvarch

33、ar(10 if score60 set r 二二不及格else set r = convert(nv archar(10,scoreretur n r end select sid,cid,dbo.f un 3(score from scored- 同一列，輸出 Employees 表中的 last name 和 first name 字段 create fun ctio n fun4( last name varchar(10,first namevarchar(10 returns varchar(20 as begi n return last name +-+first name e

34、nd selectemployeeid,dbo.fu n4(last name,first name,title from employees -計算 order details 表 每條訂單的總價 select * from order details create fun ctio n fun 5( un itprice mon ey,quantity smalli nt,disco unt real returns smalli nt as beg in return un itprice * qua ntity * (1- disco unt end select *,dbo.fun5

35、(unitprice,quantity,discount from order details-游標(biāo) 不占用物理內(nèi)存， 全 咅E是臨時文件 declare curl cursor for select sid,sname,class from studentopen curl declare sid int, sn ame n varchar(10,class nv archar(10,i int set i = 1fetch from curl into sid,sname,class -fetch 取游標(biāo)所在的行的值 whilefetch_status = 0 beg in print c

36、onvert(nv archar(10,i+. +conv ert( nv archar(10,sid+,+s name+,+class set i =i + 1 fetch from cur1 in tosid,sname,class end close cur1 deallocate cur1 給所有分?jǐn)?shù)v60 的人加送 10 分declare cur2 cursor for select * from score ope n cur2 declare sid in t,cid in t,score intfetch from cur2 into sid,cid,score while f

37、etch_status = 0 -0 語句成功-1 語句失敗或行不在結(jié)果集中 -2 提取的行不存在 begin if score 1 insert into #table1 values(sid,s name -pri nt convert(nvarchar(10,sid+ +s name fetch from cur3 into sid,s name end close cur3 deallocatecur3 select * from #table1 exec sp_Fi nd 觸發(fā)器-級聯(lián)刪除學(xué)生信息 alter trigger t_delete onstude nt for delete a