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

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

《深圳华为SQL总结Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《深圳华为SQL总结Word文档下载推荐.docx（34页珍藏版）》请在冰豆网上搜索。

匹配1个任意字符

%b_sy%'

-----like的查询中转义字符escape的用法

-----空值检索

wherepriceisnull-------查找价格为null的记录

wherepriceisnotnull-------查找价格不为null的记录

-----限制记录行检索:

top关键字限制结果集中返回的行数

selecttop3*fromtitles--查找前3本图书

selecttop3*fromtitles

orderbypricedesc--查找价格最贵的3本书

distinct关键字限制重复行

selectdistinctstatefromauthors--查找所有著书作者的州名

-----按书店编号和定单号分组统计销量

selectstor_id,ord_num,sum（qty）fromsales

groupbystor_id,ord_num

withcube

---按图书类别和出版社统计图书销量

selecttype,pub_id,sum（advance）fromtitles

groupbytype,pub_id

2、多表查询与系统函数

select*fromauthors,titleauthor

select*fromtitleauthor

innerjointitleauthor

onauthors.au_id=titleauthor.au_id

innerjoinauthors

innerjointitles

ontitleauthor.title_id=titles.title_id

leftjointitleauthor

leftjoinauthors

rightouterjoinauthors

jointitles

fullouterjoinauthors

rightjointitles

selectau_id,au_fnamefromauthors

union

selecttitle_id,pricefromtitles

union

selectcharindex（'

abc'

'

cccabcfffabc'

）

selectpatindex（'

%abc%'

aaaccabcaaccabc'

selectdateadd（year,10,'

2005-2-5'

selectdatediff（month,'

2005-1-1'

2005-2-25'

selectdatepart（month,'

selectgetdate（）

selectpower（2,3）

selectisdate（'

205-2-25'

selectisnull（'

sdd'

dfgdf'

selectisnull（null,'

fdsf'

selectisnumeric（'

a123'

selectconvert（char（10）,getdate（）,108）

3、子查询

selectstor_idfromsales

whereqty>

（

selectavg（qty）fromsales

）

selectau_id,au_fnamefromauthors

whereau_idin

（selectau_idfromtitleauthor

groupbyau_id

havingcount（au_id）>

=2

whereau_idnotin

（selectau_idfromtitleauthor）

wherenotexists

selectau_idfromtitleauthor

whereauthors.au_id=titleauthor.au_id

select*fromsales

select*fromstores

selecttitle_id,title,price

fromtitles

whereprice<

20andtitle_idin

selecttitle_idfromsales

wherestor_idin

selectstor_idfromstores

wherestate='

CA'

groupbytitle_id

havingsum（qty）*price>

250

4、数据库管理

createdatabasemydata

onprimary

（

name='

mydata'

filename='

c:

\aaa\mydata.mdf'

size=5mb,

maxsize=50,

filegrowth=5

logon

mydatalog'

\aaa\mydatalog.ldf'

filegrowth=5

alterdatabasemydata

addfile

mydata2'

\aaa\mydata2.ndf'

select*fromsysfiles

select*fromsysfiles1

select*fromsysfilegroups

addfilegroupgroup1

removefilegroupgroup1

modifyname=mydata2

modifyfile

maxsize=150

createdatabasemydata1

mydata1'

\aaa\mydata1.mdf'

）,

mydata11'

\aaa\mydata11.ndf'

filegroupgroup1

mydata12'

\aaa\mydata12.ndf'

mydata13'

\aaa\mydata13.ndf'

mydatalog1'

\aaa\mydatalog1.ldf'

mydatalog2'

\aaa\mydatalog2.ldf'

alterdatabasemydata1

modifyfilegroupgroup1default

dbccshrinkdatabase（mydata1,80,truncateonly）

dbccshrinkfile（mydata1,3,truncateonly）

dropdatabasemydata1

sp_helpdbmydata2

sp_databases

sp_rename'

mydata3'

database'

sp_dboption'

selectinto/bulkcopy'

true'

5、表的管理与数据类型

createtablestudents

snointidentity（1000,1）primarykey,

snamevarchar（20）notnull,

ageintnotnull,

addressvarchar（30）

select*fromstudents

createtable#tt

select*from#tt

createtable##ttt

select*into#tt1fromstudents

select*intott1fromstudents

select*from#tt1

selectau_id,statefrom

（select*fromauthors）s

altertablestudents

addphonevarchar（11）

dropcolumnaddress

altercolumnagetinyint

sp_helpstudents

droptable#tt

students'

stus'

select*fromstus

sp_helpstus

deletestus

insertstusvalues（'

zhansan'

20,'

65025215'

insertstudentsvalues（'

johnson'

dfdsaf'

insertstudents（sname,age）values（'

john'

20）

--insertstudents（age）values（20）

setidentity_insertstu1on

insertstudents（sno,sname,age,address）values（1006,'

dfdscf'

setidentity_insertstudentsoff

insertstudents（sname,age,address）values（'

select*intostu1fromstudents

select*fromstu1

sp_helpstu1

deletestu1

insertstu1select*fromstudents

select*intoauthors1fromauthors

select*fromauthors1

altertableauthors1

dropcolumnau_fname

addsexchar

（2）

insertauthors1（au_id,au_lname,phone,address,city,state,zip,contract,sex）selectau_id,au_lname,phone,address,city,state,zip,contract,'

M'

fromauthors

updateauthors1

setstate='

CC'

city='

bk'

deleteauthors1

truncatetableauthors1

sp_addtypephonetype,'

varchar（8）'

sp_addtypeage,tinyint

sp_droptypeage

select*fromsystypes

6、数据完整性

sidvarchar（18）notnull

sp_helpstudents3

createtablestudents1

snointidentity（1000,1）constraintpk_snoprimarykey,

createtablestudents2

snointidentity（1000,1）,

altertablestudents2

addconstraintpk_sno2primarykey（sno）

createtablestudents3

sidvarchar（18）unique

createtablestudents4

sidvarchar（18）constraintuk_siduniquenotnull

sp_helpstudents4

createtablescores1

sno1intnotnullconstraintfk_sno1foreignkey（sno1）referencesstudents4（sno）,

cnochar（5）notnull,

gradefloat,

constraintpk_smo_cnoprimarykey（sno1,cno）

sp_helpscores1

sp_helptitleauthor

deleteauthors

whereau_id='

172-32-1176'

updateauthors

setau_id='

111-11-1111'

213-46-8915'

altertabletitleauthor

dropconstraintFK__titleauth__au_id__0519C6AF

addconstraintfk11foreignkey（au_id）referencesauthors（au_id）ondeletecascadeonupdatecascade

createtablestudents5

sexchar

（2）notnullconstraintche1check（sexin（'

F'

））,

sidvarchar（18）constraintuk_sid1uniquenotnull

sp_helpstudents5

createtablestudents6

ageintnotnulldefault18,

sexchar

（2）notnullconstraintche11check（sexin（'

sidvarchar（18）constraintuk_sid11uniquenotnull

sp_helpstudents7

createtablestudents7

sexchar

（2）notnullconstraintche21check（sexin（'

sidvarchar（18）constraintuk_sid21uniquenotnull

altertablestudents7

addconstraintdef1default18forage

dropconstraintdef1

createrulerule_age

as@agebetween0and255

sp_bindrulerule_age,'

students7.age'

sp_unbindrule'

futureonly

droprulerule_age

createdefaultdef_age

as18

sp_bindefaultdef_age,'

sp_unbindefault'

dropdefaultdef_age

7、视图与索引

ifexists（select*fromsysobjectswherename='

v_author'

andxtype='

V'

dropviewv_author

go

createviewv_author

withencryption

as

selectau_id,state,city

withcheckoption

select*fromv_author

updatev_author

setcity='

bj'

2