深圳华为SQL总结.doc

深圳华为SQL总结 1-更改列标题的三种方法select au_id作者编号,作者姓名=au_lname,phone as电话from authors-where条件查询select*from authors where state=ca-查询住在加州的作者-between.and.条件查询select*from titles where pricebetween10and20-查找图书价格在10至20元间的图书-逻辑运算符、关系运算符的应用select*from titles where price=10and price(select avg(qty)from sales)select au_id,au_fname from authors where au_id in(select au_id from titleauthor group by au_id havingcount(au_id)=2)select au_id,au_fname from authors where au_id notin(select au_id from titleauthor)select au_id,au_fname from authors wherenot exists(select au_id from titleauthor where authors.au_id=titleauthor.au_id)select*from salesselect*from storesselect title_id,title,price from titleswhere price250)4create databasemydata on primary(name=mydata,filename=c:aaamydata.mdf,size=5mb,maxsize=50,filegrowth=5)log on(name=mydatalog,filename=c:aaamydatalog.ldf,size=5mb,filegrowth=5)alter databasemydata addfile(name=mydata2,filename=c:aaamydata2.ndf,size=5mb,maxsize=50,filegrowth=5)select*from sysfilesselect*from sysfiles1select*from sysfilegroupsalter databasemydata addfilegroup group1alter databasemydata removefilegroup group1alter databasemydata modifyname=mydata2alter databasemydata modifyfile(name=mydata,maxsize=150)create databasemydata1onprimary(name=mydata1,filename=c:aaamydata1.mdf,size=5mb,maxsize=50,filegrowth=5),(name=mydata11,filename=c:aaamydata11.ndf,size=5mb,maxsize=50,filegrowth=5),filegroup group1(name=mydata12,filename=c:aaamydata12.ndf,size=5mb,maxsize=50,filegrowth=5),(name=mydata13,filename=c:aaamydata13.ndf,size=5mb,maxsize=50,filegrowth=5)log on(name=mydatalog1,filename=c:aaamydatalog1.ldf,size=5mb,filegrowth=5),(name=mydatalog2,filename=c:aaamydatalog2.ldf,size=5mb,filegrowth=5)alter databasemydata1modify filegroupgroup1default dbshrinkdatabase(mydata1,80,truncateonly)db shrinkfile(mydata1,3,truncateonly)drop databasemydata1sp_helpdb mydata2sp_databases sp_renamemydata2,mydata3,databasesp_dboptionmydata3,select into/bulkcopy,true5create table students(sno intidentity(1000,1)primary key,sname varchar (20)not null,age int not null,address varchar (30)select*from studentscreate table#tt(sno intidentity(1000,1)primary key,sname varchar (20)not null,age intnot null,address varchar (30)select*from#tt create table#ttt(sno intidentity(1000,1)primary key,sname varchar (20)not null,age intnot null,address varchar (30)select*into#tt1from studentsselect*into tt1from studentsselect*from#tt1select au_id,state from(select*from authors)sselect*from#tt alter tablestudentsadd phonevarchar (11)alter tablestudents drop column addressalter tablestudents altercolumn agetinyint select*from studentssp_help studentsdrop table#tt sp_renamestudents,stusselect*from stussp_help stusdelete stusinsert stusvalues(zhan san,20,65025215)select*from studentsinsert studentsvalues(johnson,20,dfdsaf)insert students(sname,age)values(john,20)-insert students(age)values (20)set identity_insert stu1on insert students(sno,sname,age,address)values(1006,johnson,20,dfdscf)set identity_insert studentsoff insertstudents(sname,age,address)values(johnson,20,dfdscf)select*from studentsselect*into stu1from studentsselect*from stu1sp_help stu1delete stu1insertstu1select*from studentsselect*into authors1from authors select*from authors1alter table authors1dropcolumnau_fname alter tableauthors1add sex char (2)insert authors1(au_id,au_lname,phone,address,city,state,zip,contract,sex)select au_id,au_lname,phone,address,city,state,zip,contract,Mfrom authorsupdate authors1set state=CC,city=bkwhere state=CAdelete authors1where state=CCtruncate tableauthors1select*from authors1sp_addtype phoype,varchar (8)sp_addtype age,tinyint sp_droptype ageselect*from systypes6create tablestudents(sno intidentity(1000,1)primary key,sname varchar (20)not null,age intnot null,sid varchar (18)not null)sp_help students3create tablestudents1(sno intidentity(1000,1)constraint pk_sno primary key,sname varchar (20)not null,age intnot null,sid varchar (18)not null)create tablestudents2(sno intidentity(1000,1),sname varchar (20)not null,age intnot null,sid varchar (18)not null)alter tablestudents2add constraint pk_sno2primary key(sno)create tablestudents3(sno intidentity(1000,1)primary key,sname varchar (20)not null,age intnot null,sid varchar (18)unique)create tablestudents4(sno intidentity(1000,1)primary key,sname varchar (20)not null,age intnot null,sid varchar (18)constraint uk_sid unique not null)sp_help students4create tablescores1(sno1intnot null constraintfk_sno1foreign key(sno1)references students4(sno),o char (5)not null,grade float,constraintpk_smo_o primary key(sno1,o)sp_help scores1sp_help titleauthor select*from authors select*from titleauthordelete authors where au_id=172-32-1176update authors set au_id=111-11-1111where au_id=213-46-8915alter table titleauthor drop constraint FK_titleauth_au_id_0519C6AF altertable titleauthoradd constraintfk11foreign key(au_id)references authors(au_id)on deletecascade onupdate cascadecreate tablestudents5(sno intidentity(1000,1)primary key,sname varchar (20)not null,age intnot null,sex char (2)not nullconstraint che1check(sex in(M,F),sid varchar (18)constraint uk_sid1unique not null)sp_help students5create tablestudents6(sno intidentity(1000,1)primary key,sname varchar (20)not null,age intnot nulldefault18,sex char (2)not nullconstraint che11check(sex in(M,F),sid varchar (18)constraint uk_sid11unique not null)sp_help students7create tablestudents7(sno intidentity(1000,1)primary key,sname varchar (20)not null,age intnot null,sexchar (2)not nullconstraint che21check(sex in(M,F),sid varchar (18)constraint uk_sid21unique notnull)altertablestudents7add constraintdef1default18for agealtertablestudents7dropconstraintdef1create rulerule_age asage between0and255sp_bindrule rule_age,students7.agesp_unbindrulestudents7.age,futureonly sp_help students7drop rulerule_age createdefault def_age as18sp_bindefault def_age,students7.agesp_unbindefaultstudents7.agedrop defaultdef_age7if exists(select*from sysobjectswhere name=v_authorand xtype=V)drop view v_author go create view v_author withencryption as select au_id,state,city from authors where state=CAwith checkoption select*from v_authorselect*from authorsupdate v_author setcity=bjwhere au_id=213-46-8915sp_helptext v_authorselect text from sysments where id=(select id from sysobjectswhere name=v_authorand xtype=V)insert v_author values(222-22-2222,CA,fg)sp_help if exists(select*from sysobjectswhere name=v_author1and xtype=V)drop viewv_author1go create viewv_author1as selectau_id,au_lname,au_fname,phone,contract,city from authors select*from v_author1insert v_author1values(111-11-1111,fgdf,fhf,100111-1111,1,kh)select*from authorsif exists(select*from sysobjectswhere name=v_author2and xtype=V)drop viewv_author2go create viewv_author2as selectauthors.au_id,au_lname,au_fname,phone,contract,city,titleauthor.au_id au_id1,title_id from authors jointitleauthor onauthors.au_id=titleauthor.au_id sp_help titleauthorselect*from v_author2-insert v_author2values(211-11-1111,fgdf,fhf,xx11-1111,1,kh,211-11-1111,BU1032)insert v_author2(au_id,au_lname,au_fname,phone,contract,city)values(211-11-1111,fgdf,fhf,xx11-1111,1,kh)insert v_author2(au_id1,title_id)values(211-11-1111,BU1032)-update v_author2-set au_lname=AAAAAAAAAAA,title_id=BU1032-where au_id=172-32-1176update v_author2set au_lname=AAAAAAAAAAAwhere au_id=172-32-1176update v_author2set title_id=BU1032where au_id=172-32-1176create tablet2(sno intprimarykey,sid varchar (18)unique notnull,age intnotnull)sp_help t2create indexindex_age ont2(age desc)with pad_index,fillfactor=15create tableproduct createtablet1(sno intprimarykey,sid varchar (18)uniquenotnull,sname varchar (20)notnull,age intnotnull)create viewview_t1with schemabindingas select sno,sname,age fromdbo.t1create uniqueclustered indexsno_index onview_t1(sno)create indexsname_index onview_t1(sname)8SQL selectversion selectservername printerror raiserror(hello,12,1)select*from master.sysmessages whereerror50000-sp_addmessage50001,3,hello world,lang=english/*sp_addmessage50002,3,你好,lang=english*/sp_help waitfor delay00:00:05select*from authorsdeclaren int,name varchar (20)selectn=contract,name=au_fname from authorswhere au_id=527-72-3246ifn=1printcan contractelse printcan notcontractprintname selectau_id,state1=case statewhenCAthen加州whenKSthen肯莎州elseunkownend from authors selectau_id,state1=case when state=CAthen加州whenstate=KSthen肯莎州elseunkownend from authorsselect*from scoresselectsno,A001=sum(case wheno=A001then scoreelse0end),A002=sum(case wheno=A002then scoreelse0end),A003=sum(case wheno=A003then scoreelse0end)from scoresgroupbysno declaren intwhile(n20or(select max(price)from titles)50return while1=1begin update titles setprice=price+0.5if(select avg(price)from titles)20or(select max(price)from titles)50break endselect*from authorswher state=CAselect*from salesgo update authors set state=AAwhere au_id=172-32-1176update sales set qty=qty+5where title_id=BU1032go if exists(select*from sysobjectsname=aaand xtype=v)drop viewaa go createviewaa asselect*from sales9if exists(select*from sysobjectswhere name=searchauthorand xtype=P)drop proceduresearchauthor go create proc searchauthor withencryption asselect*fromauthors go exec searchauthor sp_helptext searchauthorselecttextfromsysmentswhereid=(select idfrom sysobjectswhere name=searchauthorand xtype=P)if exists(select*from sysobjectswhere name=searchauthor1and xtype=P)drop proceduresearchauthor1gocreate proc searchauthor1state char (2)asselect*fromauthorswhere state=state go execsearchauthor1CAif exists(select*from sysobjectswhere name=searchauthor2and xtype=P)drop proceduresearchauthor2gocreate procsearchauthor2price1money,price2money asselect*from titleswherepricebetweenprice1andprice2go execsearchauthor210,20select*fromtitleauthorif exists(select*from sysobjectswhere name=Addauthorand xtype=P)drop procedureAddauthor gocreate procAddauthorau_id char (11),title_id char (6),au_ord int,royaltyper intas insert titleauthor values(au_id,title_id,au_ord,royaltyper)iferror0return-1else return1go sp_help titleauthorinserttitleauthorvalues(172-32-1176,BU1032,1,1000)exec Addauthor172-32-1176,BU1111,1,10declarea intexeca=Addauthor172-32-1176,PC1035,1,10printa declarea intexeca=Addauthor111-11-1176,PC1035,1,10printa if exists(select*from sysobjectswhere name=Searchtitlesand xtype=P)drop procedureSearchtitles gocreate procSearchtitlestitle_id char (6),title varchar (20)output,price moneyoutput asselecttitle=title,price=price from titleswhere title_id=title_id godeclaretitle varchar (20),price moneyexec SearchtitlesBU1032,title output,price outputprinttitle+convert(varchar (10),price)select*from titlesif exists(select*from sysobjectswhere name=Inserttitlesand xtype=P)drop procedureInserttitles gocreate procInserttitlestitle_id char (6),title varchar (80),type varchar (20),pub_id char (4),price money,advance money,royalty int,ytd_sales int,notes varchar (200),pubdate datetime,avg_price moneyoutput asinsert titlesvalues(title_id,title,type,pub_id,price,advance,royalty,ytd_sales,notes,pubdate)selectavg_price=avg(price)fromtitlesiferror0return-1000else return100go select*fromtitlesdeclaren int,avgprice moneyexecn=InserttitlesBU9988,good book,business,1389,30,5000,10,4500,very good,xx-3-22,avgprice outputprintn=+convert(char (10),n)+avgprice=+convert(char (20),avgprice)10create function(title_id char (6)returns varchar (80)as begindeclaretitle varchar (80)selecttitle=title fromtitleswheretitle_id=title_id returntitle endselect dbo.SearchBook(bu1032)-查找图书的版税不符合标准版税的图书，并将其版税更改为标准版税-written by王传标,xx.11.12if exists(select*from sysobjectswhere name=Correct_royalty)drop procedureCorrect_royalty gocreate procedureCorrect_royalty asbegin setnocount ondeclaretitle_id char (6),title varchar (80),type varchar (12),pub_id char (4)declareprice money,advance money,royalty int,royalty1int,ytd_sales intcreatetable#t(title_id char (6),title varchar (80),type varchar (12),pub_id char (4),price money,advance money,royalty int,royalty1int,ytd_sales int)declare title_cur cursorfor selecttitle_id,title,type,pub_id,price,advance,royalty,ytd_sales fromtitleswhereytd_sales isnotnullopen title_cur fetch next fromtitle_cur intotitle_id,title,type,pub_id,price,advance,royalty,ytd_sales while(fetch_status=0)begin selectroyalty1=royalty fromroysched wheretitle_id=title_id and(ytd_sales betweenlorange andhirange)ifroyaltyroyalty1begin insert#t values(title_id,title,type,pub_id,price,advance,royalty,royalty1,ytd_sales)updatetitlesset royalty=royalty1where currentof title_cur endfetch next fromtitle_cur intotitle_id,title,type,pub_id,price,advance,royalty,ytd_sales endclose title_cur deallocatetitle_cur select*from#t endgoexecCorrect_royalty11if exists(select*from sysobjectswhere name=ins_authorsand xtype=TR)drop trigger ins_authors gocreate triggerins_authors onauthors for insert as declareau_id char (11),au_fname varchar (20),state char (2)printinsert newauthorsselectau_id=au_id,au_fname=au_fname,state=state frominserted printau_id:+au_id+au_fname=+au_fname+state=+state goselect*fromauthorsinsert authorsvalues(222-11-1111,gd,gdg,101111-1111,dfggf,vff,CA,12345,1)select*into authors1fromauthorsif exists(select*from sysobjectswhere name=ins_authors1and xtype=TR)drop triggerins_authors1gocreate triggerins_authors1onauthors1forinsert as declareau_id char (11),au_fname varchar (20),state char (2)printinsert newauthorsdeclare cur_auth cursorfor selectau_id,au_fname,state frominserted opencur_auth fetchnext from cur_auth intoau_id,au_fname,state whilefetch_status=0begin printau_id:+au_id+au_fname=+au_fname+state=+state fetchnext fromcur_auth intoau_id,au_fname,state endclose cur_auth deallocatecur_auth goinsert authors1select*fromauthorsif exists(select*from sysobjectswhere name=del_authorsand xtype=TR)drop triggerdel_authors gocreate triggerdel_authorsonauthors1for deleteas declareau_id char (11),au_fname varchar (20),state char (2)printdelete authorsselectau_id=au_id,au_fname=au_fname,state=state fromdeleted printau_id:+au_id+au_fname=+au_fname+state=+state goselect*fromauthors1delete authors1where au_id=111-11-1111truncate tableauthors1if exists(select*from sysobjectswhere name=upd_authorsand xtype=TR)drop triggerupd_authorsgocreate triggerupd_authorsonauthors1for update,insertasdeclareau_id char (11),au_fname varchar (20),state char (2)printdelete authorsselectau_id=au_id,au_fname=au_fname,state=state fromdeleted printau_id:+au_id+au_fname=+au_fname+state=+state printinsert authorsselectau_id=au_id,au_fname=au_fname,state=state frominserted printau_id:+au_id+au_fname=+au_fname+state=+state goselect*fromauthors1update authors1set state=KKwhere au_id=111-11-1111sp_helptrigger authors1sp_helptext upd_authorsselect*fromtitleauthorselect*from salescreateviewv_auth_sales asselectau_id,titleauthor.title_id,au_ord,royaltyper,stor_id,ord_num,ord_date,qty,payterms,sales.ti