413关系数据库标准语言SQL.ppt

《413关系数据库标准语言SQL.ppt》由会员分享，可在线阅读，更多相关《413关系数据库标准语言SQL.ppt（38页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、4.1.3关系数据库标准语言关系数据库标准语言SQLnSQL（StructuredQueryLanguage)n1建立表nCREATETABLEtable_namen(column_namedata-typeconsraint,)建立表nCREATETABLEemployeen(eidCHAR(6),nnameVARCHAR(8)NOTNULL,nageNUMERIC(3,0),ncountryVARCHAR(10)NULL)建立表nCREATETABLEemployeen(eidCHAR(6)NOTNULLPRIMARYKEY,nnameVARCHAR(8)NOTNULL),nageNUME

2、RIC(3,0),ncountryVARCHAR(10)NULLn)2插入记录nINSERT INTO 表名 列名表）VALUES（值表）nINSERT INTO employee n(eid,name,age,country)nVALUES(3018,zhang,28,China)nINSERTINTOemployeenVALUES(3056,wang,35,USA)nINSERTINTOemployeen(eid,name,country)nVALUES(4025,li,Russia)二、插入记录3、修改记录nUPDATE表名nSET字段名=表达式nWHERE条件nUPDATEemploy

3、eenSETname=LiMingnWHEREeid=4025nUPDATEemployeenSETname=ZhangLi,nage=36,ncountry=USAnWHEREeid=3056n可以修改多个字段的值。如果不带WHERE修改所有的记录。4、删除记录nDELETE表名nWHERE条件nDELETEemployeenWHEREeid=3018n5、查询记录nSELECT表达式nFROM表名表nWHERE条件nGROUPBY字段名表nHAVING条件nORDERBY字段名表（1）举例nSELECT pub_id,pub_name,countrynFROMpublishersnSELE

4、CT*nFROMpublishers（2）删除重复的行DISTICTnSELECTcity,statenFROMauthersnSELECTDISTICTcity,statenFROMauthers（3）计算表达式nnSELECTtitle_id,type,price,price_price*0.3nFROMtitlennSELECTau_lname+.+au_fname,city+.+statenFROMauthors（4）筛选条件n（a）条件：n=!=nprice45.3ncountry=ChinanNOTANDORnCountry=ChinaANDage35（b）举例nSELECT ti

5、tle_id,type,price,price-price*0.3nFROMtitlesnWHEREprice-price*0.318nSELECTpub_id,pub_namenFROMpublishersnWHEREcountry=USAANDstate=CA(c)LIKEn列名NOTLIKE模式n%:任意字符串,_:任意一个字符nnSELECTau_lname+.+au_fname,city+.+statenFROMauthorsnWHEREau_fnameLIKED%nSELECTau_lname+.+au_fname,city+.+statenFROMauthorsnWHEREau_

6、idLIKE72_-%(5)结果排序nORDERBY字段表n通常返回的结果是随机排列的。nSELECTstor_name,citynFROMstoresnORDERBYstore_namenSELECTstor_name,citynFROMstoresnORDERBYstore_nameDESCnnSELECTstor_name,citynFROMstoresnORDERBYstore_nameASCnSELECTstor_name,citynFROMstoresnORDERBYstor_name，cityn用多个列排列。nnSELECTstor_name,citynFROMstoresnOR

7、DERBYstaten用不出现的列。nSUM（expression）nAVG（expression）nMIN（expression）nMAX（expression）nCOUNT（expression）nCOUNT（*）(6)统计函数 nSELECTAVG(price)nFROMtitlesnnSELECTMAX(price)nFROMtitlesnWHEREtype=businessnSELECTAVG(qty),SUM(qty),COUNT(DISTICTstor_id)nFROMsalesnnSELECTCOUNT（*）nFROMpublishersnWHEREstate=CA(7)结果分

8、组nGROUPBY分组列名表nSELECTtype,AVG(price),SUM(price),COUNT(*)nFROMtitlesnWHERE type in (business,mod_cook,trad_cook)nGROUPBYtypenSELECT type,pub_id,AVG(price),SUM(price),COUNT(*)nFROMtitlesnWHERE type in (business,mod_cook,trad_cook)nGROUPBYtype，pub_idnGOnGROUPBY分组列名表nHAVING条件n对GROUP的分组结果再进一步筛选。nSELECTty

9、pe,AVG(price),SUM(price),COUNT(*)nFROMtitlesnWHEREprice$10nGROUPBYtype使用HAVINGnSELECTtype,AVG(price),SUM(price),COUNT(*)nFROMtitlesnWHEREprice$10nGROUPBYtypenHAVINGAVG(price)$20使用HAVING(8)多表查询n（a）笛卡尔乘积nSELECTtitle_id,au_fnamenFROMtitles,authorsnnSELECTtitles.title_id,titles.title,titleauthor.au_idnF

10、ROMtitles,titleauthor（b）别名nSELECTt.title_id,t.title,ta.au_idnFROMtitlest,titleauthortannSELECTt.title_id,t.title,ta.au_idnFROMtitlest,titleauthortanWHEREt.title_id=ta.title_id（c）筛选nSELECTt.title_id,a.au_idnFROMtitlest,titleauthorta,authorsanWHEREt.title_id=ta.title_idandta.au_id=a.au_id6视图n(1）视图的定义n

11、SQL建立视图的语句格式为：nCREATE VIEW(，)nAS nCREATEVIEWau_viewnASnSELECTau_id,au_fname,au_lnamenFROMauthorsnCREATEVIEWauthor_titlenASnSELECTt.title_id,a.au_idnFROMtitlest,titleauthorta,authorsanWHEREt.title_id=ta.title_idandta.au_id=a.au_id(2)视图查询nSELECT*nFROMau_viewnSELECTt.title_id,a.au_idnFROMtitlest,titlea

12、uthorta,authorsanWHEREt.title_id=ta.title_idandta.au_id=a.au_id（3）删除视图nDROPVIEW视图名nnDROPVIEWa_authors7、索引n（1）索引类型n按照存储位置：聚集索引和非聚集索引。n按照关键字值是否唯一：唯一索引和非唯一所以。（2）建立索引nCREATE UNIQUE CLUSTERED|NOCLUSTEREDINDEX索引名nON表名（列名表）nCREATEUNIQUEINDEXid_indexnONauthors(au_id)nCREATEINDEXid_indexnONauthors(au_fnameDESC,au_id)nCREATECLUSTEREDINDEXid_indexnONauthors(au_fnameDESC,au_id)（3）删除索引nDROPINDEX表名.索引名nnDROPINDEXauthors.id_index8、数据库安全性n（1）添加数据库用户nSP_GRANTDBACCESS有登录权的帐户名nSP_GRANTDBACCESSlixinnn（2）删除数据库用户nSP_REVOKEDBACCESS帐户名nSP_REVOKEDBACCESSlili