数据库技术基础2ppt课件

.,1,4.1.3关系数据库标准语言SQL,SQL（StructuredQueryLanguage)1建立表CREATETABLEtable_name(column_namedata-typeconsraint,),.,2,建立表,CREATETABLEemployee(eidCHAR(6),nameVARCHAR(8)NOTNULL,ageNUMERIC(3,0),countryVARCHAR(10)NULL),.,3,建立表,CREATETABLEemployee(eidCHAR(6)NOTNULLPRIMARYKEY,nameVARCHAR(8)NOTNULL),ageNUMERIC(3,0),countryVARCHAR(10)NULL),.,4,2插入记录,INSERTINTO表名列名表）VALUES（值表）INSERTINTOemployee(eid,name,age,country)VALUES(3018,zhang,28,China),.,5,INSERTINTOemployeeVALUES(3056,wang,35,USA)INSERTINTOemployee(eid,name,country)VALUES(4025,li,Russia),二、插入记录,.,6,3、修改记录,UPDATE表名SET字段名=表达式WHERE条件UPDATEemployeeSETname=LiMingWHEREeid=4025,.,7,UPDATEemployeeSETname=ZhangLi,age=36,country=USAWHEREeid=3056可以修改多个字段的值。如果不带WHERE修改所有的记录。,.,8,4、删除记录,DELETE表名WHERE条件DELETEemployeeWHEREeid=3018,.,9,5、查询记录,SELECT表达式FROM表名表WHERE条件GROUPBY字段名表HAVING条件ORDERBY字段名表,.,10,（1）举例,SELECTpub_id,pub_name,countryFROMpublishersSELECT*FROMpublishers,.,11,（2）删除重复的行DISTICT,SELECTcity,stateFROMauthersSELECTDISTICTcity,stateFROMauthers,.,12,（3）计算表达式,SELECTtitle_id,type,price,price_price*0.3FROMtitleSELECTau_lname+.+au_fname,city+.+stateFROMauthors,.,13,（4）筛选条件,（a）条件：=!=price45.3country=ChinaNOTANDORCountry=ChinaANDage35,.,14,（b）举例,SELECTtitle_id,type,price,price-price*0.3FROMtitlesWHEREprice-price*0.318SELECTpub_id,pub_nameFROMpublishersWHEREcountry=USAANDstate=CA,.,15,(c)LIKE,列名NOTLIKE模式%:任意字符串,_:任意一个字符SELECTau_lname+.+au_fname,city+.+stateFROMauthorsWHEREau_fnameLIKED%,.,16,SELECTau_lname+.+au_fname,city+.+stateFROMauthorsWHEREau_idLIKE72_-%,.,17,(5)结果排序,ORDERBY字段表通常返回的结果是随机排列的。SELECTstor_name,cityFROMstoresORDERBYstore_name,.,18,SELECTstor_name,cityFROMstoresORDERBYstore_nameDESCSELECTstor_name,cityFROMstoresORDERBYstore_nameASC,SELECTstor_name,cityFROMstoresORDERBYstor_name，city用多个列排列。SELECTstor_name,cityFROMstoresORDERBYstate用不出现的列。,.,20,SUM（expression）AVG（expression）MIN（expression）MAX（expression）COUNT（expression）COUNT（*）,(6)统计函数,.,21,SELECTAVG(price)FROMtitlesSELECTMAX(price)FROMtitlesWHEREtype=business,.,22,SELECTAVG(qty),SUM(qty),COUNT(DISTICTstor_id)FROMsalesSELECTCOUNT（*）FROMpublishersWHEREstate=CA,.,23,(7)结果分组,GROUPBY分组列名表SELECTtype,AVG(price),SUM(price),COUNT(*)FROMtitlesWHEREtypein(business,mod_cook,trad_cook)GROUPBYtype,SELECTtype,pub_id,AVG(price),SUM(price),COUNT(*)FROMtitlesWHEREtypein(business,mod_cook,trad_cook)GROUPBYtype，pub_idGO,GROUPBY分组列名表HAVING条件对GROUP的分组结果再进一步筛选。SELECTtype,AVG(price),SUM(price),COUNT(*)FROMtitlesWHEREprice$10GROUPBYtype,使用HAVING,SELECTtype,AVG(price),SUM(price),COUNT(*)FROMtitlesWHEREprice$10GROUPBYtypeHAVINGAVG(price)$20,使用HAVING,.,27,(8)多表查询,（a）笛卡尔乘积SELECTtitle_id,au_fnameFROMtitles,authorsSELECTtitles.title_id,titles.title,titleauthor.au_idFROMtitles,titleauthor,.,28,（b）别名,SELECTt.title_id,t.title,ta.au_idFROMtitlest,titleauthortaSELECTt.title_id,t.title,ta.au_idFROMtitlest,titleauthortaWHEREt.title_id=ta.title_id,.,29,（c）筛选,SELECTt.title_id,a.au_idFROMtitlest,titleauthorta,authorsaWHEREt.title_id=ta.title_idandta.au_id=a.au_id,.,30,6视图,(1）视图的定义SQL建立视图的语句格式为：CREATEVIEW(，)AS,.,31,CREATEVIEWau_viewASSELECTau_id,au_fname,au_lnameFROMauthorsCREATEVIEWauthor_titleASSELECTt.title_id,a.au_idFROMtitlest,titleauthorta,authorsaWHEREt.title_id=ta.title_idandta.au_id=a.au_id,.,32,(2)视图查询,SELECT*FROMau_viewSELECTt.title_id,a.au_idFROMtitlest,titleauthorta,authorsaWHEREt.title_id=ta.title_idandta.au_id=a.au_id,.,33,（3）删除视图,DROPVIEW视图名DROPVIEWa_authors,.,34,7、索引,（1）索引类型按照存储位置：聚集索引和非聚集索引。按照关键字值是否唯一：唯一索引和非唯一所以。,.,35,（2）建立索引,CREATEUNIQUECLUSTERED|NOCLUSTEREDINDEX索引名ON表名（列名表）CREATEUNIQUEINDEXid_indexONauthors(au_id),.,36,CREATEINDEXid_indexONauthors(au_fnameDESC,au_id)CREATECLUSTEREDINDEXid_indexONauthors(au_f