深圳华为SQL总结Word文档下载推荐.docx

1、匹配1个任意字符%b_sy%-like的查询中转义字符escape的用法-空值检索where price is null -查找价格为null的记录where price is not null -查找价格不为null的记录-限制记录行检索:top 关键字限制结果集中返回的行数select top 3 * from titles -查找前3本图书select top 3 * from titlesorder by price desc -查找价格最贵的3本书distinct 关键字 限制重复行select distinct state from authors -查找所有著书作者的州名-按书店

2、编号和定单号分组统计销量select stor_id,ord_num,sum（qty） from salesgroup by stor_id,ord_numwith cube-按图书类别和出版社统计图书销量select type,pub_id,sum（advance） from titlesgroup by type,pub_id2、多表查询与系统函数select * from authors,titleauthorselect * from titleauthorinner join titleauthoron authors.au_id=titleauthor.au_idinner joi

3、n authorsinner join titleson titleauthor.title_id=titles.title_idleft join titleauthorleft join authorsright outer join authorsjoin titlesfull outer join authorsright join titlesselect au_id ,au_fname from authorsunionselect title_id,price from titlesunion select charindex（abc,cccabcfffabc）select pa

4、tindex（%abc%aaaccabcaaccabcselect dateadd（year,10,2005-2-5select datediff（month,2005-1-12005-2-25select datepart（month,select getdate（）select power（2,3）select isdate（205-2-25select isnull（sdddfgdfselect isnull（null,fdsfselect isnumeric（a123select convert（char（10）,getdate（）,108）3、子查询select stor_id fr

5、om saleswhere qty （ select avg（qty） from sales ）select au_id,au_fname from authorswhere au_id in （select au_id from titleauthor group by au_id having count（au_id）=2where au_id not in （select au_id from titleauthor）where not exists select au_id from titleauthor where authors.au_id=titleauthor.au_idse

6、lect * from salesselect * from storesselect title_id,title,pricefrom titleswhere price 2504、数据库管理create database mydataon primary（ name=mydata, filename=c:aaamydata.mdf size=5mb, maxsize=50, filegrowth=5log onmydatalogaaamydatalog.ldf filegrowth=5 alter database mydataadd file mydata2aaamydata2.ndfs

7、elect * from sysfilesselect * from sysfiles1select * from sysfilegroupsadd filegroup group1remove filegroup group1modify name= mydata2modify file maxsize=150create database mydata1mydata1aaamydata1.mdf）,mydata11aaamydata11.ndffilegroup group1mydata12aaamydata12.ndfmydata13aaamydata13.ndfmydatalog1aa

8、amydatalog1.ldfmydatalog2aaamydatalog2.ldfalter database mydata1modify filegroup group1 defaultdbcc shrinkdatabase（mydata1,80,truncateonly）dbcc shrinkfile（mydata1,3,truncateonly）drop database mydata1sp_helpdb mydata2sp_databasessp_rename mydata3databasesp_dboption select into/bulkcopytrue5、表的管理与数据类型

9、create table students sno int identity（1000,1） primary key, sname varchar（20） not null, age int not null, address varchar（30）select * from studentscreate table #ttselect * from #ttcreate table #tttselect * into #tt1 from studentsselect * into tt1 from studentsselect * from #tt1select au_id,state fro

10、m （select * from authors） salter table studentsadd phone varchar（11）drop column addressalter column age tinyintsp_help studentsdrop table #ttstudentsstusselect * from stussp_help stusdelete stusinsert stus values（zhan san,20,65025215insert students values（johnsondfdsafinsert students（sname,age） valu

11、es（john,20）-insert students（age） values（20）set identity_insert stu1 oninsert students（sno,sname,age,address） values（1006,dfdscfset identity_insert students offinsert students（sname,age,address） values（select * into stu1 from studentsselect * from stu1sp_help stu1delete stu1insert stu1 select * from

12、studentsselect * into authors1 from authorsselect * from authors1alter table authors1drop column au_fnameadd 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,M from authorsupdate authors1set state=CC,cit

13、y=bkdelete authors1truncate table authors1sp_addtype phonetype,varchar（8）sp_addtype age ,tinyintsp_droptype ageselect * from systypes6、数据完整性 sid varchar（18） not nullsp_help students3create table students1 sno int identity（1000,1） constraint pk_sno primary key,create table students2 sno int identity（

14、1000,1） ,alter table students2add constraint pk_sno2 primary key（sno）create table students3 sid varchar（18） uniquecreate table students4 sid varchar（18） constraint uk_sid unique not nullsp_help students4create table scores1 sno1 int not null constraint fk_sno1 foreign key（sno1） references students4（

15、sno）, cno char（5） not null, grade float, constraint pk_smo_cno primary key（sno1,cno）sp_help scores1sp_help titleauthordelete authorswhere au_id=172-32-1176update authorsset au_id=111-11-1111213-46-8915alter table titleauthordrop constraint FK_titleauth_au_id_0519C6AFadd constraint fk11 foreign key（a

16、u_id） references authors（au_id） on delete cascade on update cascadecreate table students5 sex char（2） not null constraint che1 check （sex in （F）, sid varchar（18） constraint uk_sid1 unique not nullsp_help students5create table students6 age int not null default 18, sex char（2） not null constraint che

17、11 check （sex in （ sid varchar（18） constraint uk_sid11 unique not nullsp_help students7create table students7 sex char（2） not null constraint che21 check （sex in （ sid varchar（18） constraint uk_sid21 unique not nullalter table students7add constraint def1 default 18 for agedrop constraint def1create

18、 rule rule_ageas age between 0 and 255sp_bindrule rule_age,students7.agesp_unbindrule ,futureonlydrop rule rule_agecreate default def_ageas 18sp_bindefault def_age,sp_unbindefault drop default def_age7、视图与索引if exists（select * from sysobjects where name=v_author and xtype=V drop view v_authorgocreate view v_authorwith encryptionas select au_id,state,city with check optionselect * from v_authorupdate v_authorset city=bj2