SQL基礎(chǔ)培訓(xùn)專(zhuān)題.docx

SQL基礎(chǔ)培訓(xùn)專(zhuān)題培訓(xùn)目標(biāo)：了解SQL（select,update,insert）的基本寫(xiě)法、復(fù)雜寫(xiě)法，在有索引的情況下，如何寫(xiě)出高效的SQL。第一章、實(shí)例環(huán)境一描述 學(xué)生成績(jī)管理系統(tǒng)課程信息表 xj_course（CrsID,CrsName, CrsNote ）學(xué)生信息表 xj_stu （StuID, ClsID, StuName, Birthday, Address, Tel, City ）教師信息表 xj_tea （TeaID, TeaName, address ）分?jǐn)?shù)表 xj_score （StuID, ClsID, CrsID, TeaID, score ）課程所屬關(guān)系信息表 xj_clscrs（ClsID, CrsID,TeaID）建立環(huán)境實(shí)例 環(huán)境的建立： MySQL 4.1.10a-max-log, DB2版本。第二章基本SQL語(yǔ)法介紹2.1 Select 最基本的語(yǔ)句，MySQL的語(yǔ)法為： Select All | Distinct select_list into outFile FileName export_options |into dumfile fileName From tabl_list Wherewhere_expr Group by Column desc|asc Having where_expr Order by ColName asc|desc limit n,m For update |Lock in share mode 2.1.1,distinct 是控制返回的數(shù)據(jù)重復(fù)行的。 selectdistinct City ,ClsID From xj_stuselect distinct ClsID Fromxj_stu2，into 選項(xiàng)能把這個(gè)結(jié)果導(dǎo)出到外面的文本文件中。 mysqlselect * into outfile wangxl.txt From xj_stu ;Query OK, 27 rows affected (0.00 sec)mysql select * into dumpfilexj_stu.txt From xj_stu ;ERROR 1172 (42000): Result consisted ofmore than one rowmysql select * into dumpfilexj_stu.txt From xj_stu where stuid=STU001 ;Query OK, 1 row affected (0.00 sec)rootfxs001 /# find / -namexj_stu.txt/usr/local/mysql-max-4.1.10a-pc-linux-gnu-i686/data/sqldb/xj_stu.txt3，F(xiàn)rom tabl_list 列舉要檢索的數(shù)據(jù)表或者子查詢(xún)。 這里要講究的東西還挺多的， A,可以設(shè)置別名： From xj_stu S, xj_Cls C 。別名別重復(fù)、別是關(guān)鍵字就行。 B,可以把一個(gè)子查詢(xún)當(dāng)作一個(gè)表。select * From (select distinct ClsID From xj_stu ) a 。 其實(shí)放在 From 后面的東西別理解成表，直接理解成一個(gè)結(jié)果集，一個(gè)通過(guò)各種手段構(gòu)造的子查詢(xún)，這樣就為寫(xiě)很多復(fù)雜的SQL作意識(shí)上的準(zhǔn)備。 C,聯(lián)合查詢(xún)，就在這個(gè)中間體現(xiàn)了，一般的有: tableA A inner join tableB B on A.ID = B.ID 兩個(gè)都有的來(lái)連接。去掉inner也一樣。 tableA A left outer join tableB on A.ID = B.ID A中所有的連接起來(lái),去掉outer也一樣。 D,USE/IGNORE/FORCE INDEX 來(lái)特別提醒MySQL來(lái)進(jìn)行有些索引得取舍。4,where 子句 是用來(lái)限定條件或者建立 表、子查詢(xún)關(guān)聯(lián)的語(yǔ)句。5，limit 分頁(yè)中常用 LIMIT 子句可以被用于強(qiáng)制SELECT 語(yǔ)句返回指定的記錄數(shù)。LIMIT接受一個(gè)或兩個(gè)數(shù)字參數(shù)。參數(shù)必須是一個(gè)整數(shù)常量。如果給定兩個(gè)參數(shù)，第一個(gè)參數(shù)指定第一個(gè)返回記錄行的偏移量，第二個(gè)參數(shù)指定返回記錄行的最大數(shù)目。初始記錄行的偏移量是 0(而不是 1)： mysql SELECT * FROM table LIMIT 5,10; # 檢索記錄行 6-15為了檢索從某一個(gè)偏移量到記錄集的結(jié)束所有的記錄行，可以指定第二個(gè)參數(shù)為 -1： mysql SELECT * FROM table LIMIT 95,-1; # 檢索記錄行 96-last.如果只給定一個(gè)參數(shù)，它表示返回最大的記錄行數(shù)目：mysql SELECT * FROM table LIMIT 5; # 檢索前 5 個(gè)記錄行換句話說(shuō)，LIMIT n 等價(jià)于 LIMIT 0,n。6，Group by 是用來(lái)進(jìn)行統(tǒng)計(jì)分組合計(jì)的功能，后面接要進(jìn)行統(tǒng)計(jì)分組的字段或者表達(dá)式。 Count(*)返回由一個(gè) SELECT 語(yǔ)句檢索出來(lái)的記錄行中非 NULL 值的記錄總數(shù)目：insert into xj_cls values(9999,tempClass)select count(S.ClsID) From xj_stu S left join xj_cls C on S.ClsID =C.ClsID 28select count(ClsName) From xj_stu S left join xj_cls C on S.ClsID =C.ClsID 27select count(*) From xj_stu S left join xj_cls C on S.ClsID =C.ClsID 28COUNT(DISTINCT expr,expr.)返回一個(gè)互不相同的非NULL 的值的總數(shù)目：select count(distinct stuid ) From xj_score; 27select count(* ) From xj_score ; 81在 MySQL 中，通過(guò)給出一個(gè)表達(dá)式列表，可以得到不包含 NULL 的不同的表達(dá)式組合的數(shù)目。 AVG(expr)返回 expr 的平均值：select stuID,avg(score) From xj_score Group by StuID ; select stuID,avg(score) From xj_score where stuid=12345 Group by StuID ; (結(jié)果條數(shù)為0)MIN(expr)MAX(expr)返回 expr 的最小或最大值。MIN() 和 MAX() 可以接受一個(gè)字符串參數(shù)；在這種情況下，它們將返回最小或最大的字符串傳下。查看章節(jié) 5.4.3 MySQL 如何使用索引。select stuID,min(score),max(score) From xj_score Group by StuID SUM(expr)返回 expr 的總和。注意，如果返回集中沒(méi)有從我任何記錄行，它將返回 NULL ！select sum(score) From xj_score whereStuID=001001select sum(score) From xj_score whereStuID=001001 Group by StuID 7，Order by 排序 可以指定多個(gè)字段排序，可以指定別名、需要進(jìn)行排序，也可以進(jìn)行表達(dá)式進(jìn)行排序。 如果繼續(xù)排序的結(jié)果比較多，MySQL會(huì)利用 臨時(shí)文件進(jìn)行排序。例如：select * From xj_stu whereClsID=CL001 Order by (case when StuName=zzthen 00000 else StuID end ) 就是要把 學(xué)生zz排在第一位，其他的按照學(xué)號(hào)來(lái)排列。 2.2 MySQL用于select 和where 子句的函數(shù)介紹/mysql_manual/06-3.html#Other_Functions大家可以自己去看，如果不屬性的話，最好每個(gè)都動(dòng)手寫(xiě)一個(gè)sql來(lái)試一下，加深一下感情。這里只列舉幾個(gè)常用的， (Case when then when .then else end )select stuID ,( case when score =90 then A when score = 80 andscore = 70 andscore 80 then C when score =90 then A when score = 80 andscore = 70 andscore 80 then C when score =90 then 1 else 0 end ) A ,sum( case when score = 80 and score = 70 and score 80 then 1 else 0 end ) C ,sum( case when score =85Group by stuIDOrder by 2 desc 看看和下面的有什么區(qū)別：select stuID,avg(score) avgScore Fromxj_score scGroup by stuIDhaving avg(score) =85Order by 2 desc 哪個(gè)是正確的？ 2.4 列一下各班級(jí)各科目分?jǐn)?shù)的前3名。要求結(jié)果為：班級(jí)、學(xué)號(hào)、姓名、科目名稱(chēng)、成績(jī)、名次 。 selecta.ClsID,a.stuID,stuName,CrsName,a.score,rank From (select ClsID,CrsID,stuID,score ,(selectcount(*) From xj_score s2 where s2.ClsID=s1.ClsID and s2.CrsID=s1.CrsID ands2.score =s1.score) Rank Fromxj_score s1) a ,xj_stu s,xj_course ccwhere Rank =60 then 1 else 0 end) GCnt, count(*) CntTotal, avg(sc.score) avgScore, sum(case when sc.score=60 then 1 else 0 end)/Count(*) GCntPercentFrom xj_score sc, xj_course ccwhere sc.CrsID =cc.CrsIDGroup by sc.CrsIDOrder by GCntPercent desc CrsID CrsName GCnt CntTotal avgScore GCntPercent - - - - - - Crs003 English 24 27 75.31963 0.89 Crs001 Chinese 17 25 72.3864 0.68 Crs002 Maths 18 27 75.348889 0.67 格式2：語(yǔ)文及格人數(shù)、平均分、及格率，數(shù)學(xué)及格人數(shù)、平均分、及格率，英語(yǔ)及格人數(shù)、平均分、及格率。 selectmax(Case when CrsName=Chinese then GCnt else 0 end ) ChGCnt,max(Case when CrsName=Chinese then avgScore else 0 end) ChAvg,max(Case when CrsName=Chinese then GCntPercent else 0 end )ChGPercent,max(Case when CrsName=Maths then GCnt else 0 end ) MaGCnt,max(Case when CrsName=Maths then avgScore else 0 end) MaAvg,max(Case when CrsName=Maths then GCntPercent else 0 end )MaGPercent,max(Case when CrsName=English then GCnt else 0 end ) EnGCnt,max(Case when CrsName=English then avgScore else 0 end) EnAvg,max(Case when CrsName=English then GCntPercent else 0 end )EnGPercent From (select sc.CrsID, cc.CrsName, sum(case when sc.score=60 then 1 else 0 end) GCnt, count(*) CntTotal, avg(sc.score) avgScore, sum(case when sc.score=60 then 1 else 0 end)/Count(*) GCntPercentFrom xj_score sc, xj_course ccwhere sc.CrsID =cc.CrsIDGroup by sc.CrsID) a ChGCnt ChAvg ChGPercent MaGCnt MaAvg MaGPercent EnGCnt EnAvg EnGPercent - - - - - - - - - 17 72.3864 0.68 18 75.348889 0.67 24 75.31963 0.89 1record(s) selected Fetch MetaData: 0/ms Fetch Data: 0/ms 2.8 年級(jí)排名 學(xué)號(hào)、姓名、語(yǔ)文、數(shù)學(xué)、英語(yǔ)、平均分、年級(jí)名次 (如果有一門(mén)沒(méi)有考則單科算分)做法有很多，以下是其中的一種： selectHA.STUID,HA.STUNAME,HA.CHSCORE,HA.MASCORE,HA.ENSCORE,HB.AVGSCORE,HB.RANKFrom(select sc.stuID,s.StuName,sum(case when cc.CrsName=Chinese then score else 0 end ) Chscore,sum(case when cc.CrsName=Maths then score else 0 end ) Mascore,sum(case when cc.CrsName=English then score else 0 end ) Enscore Fromxj_score sc,xj_course cc,xj_stu swhere sc.CrsID =cc.CrsID and sc.StuID =s.StuIDGroup by sc.stuID,s.StuName ) HA, (select A.stuID,A.avgScore,( select count(*) From (select sc.stuID,sum(sc.score)/3 avgScore From xj_score scGroup by sc.StuID