SQL基礎培訓專題培訓目標：了解SQL（select,update,insert）的基本寫法、復雜寫法，在有索引的情況下，如何寫出高效的SQL。第一章、實例環境一描述 學生成績管理系統課程信息表 xj_course（CrsID,CrsName, CrsNote ）學生信息表 xj_stu （StuID, ClsID, StuName, Birthday, Address, Tel, City ）教師信息表 xj_tea （TeaID, TeaName, address ）分數表 xj_score （StuID, ClsID, CrsID, TeaID, score ）課程所屬關系信息表 xj_clscrs（ClsID, CrsID,TeaID）建立環境實例 環境的建立： MySQL 4.1.10a-max-log, DB2版本。第二章基本SQL語法介紹2.1 Select 最基本的語句，MySQL的語法為： 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 是控制返回的數據重復行的。 selectdistinct City ,ClsID From xj_stuselect distinct ClsID Fromxj_stu2，into 選項能把這個結果導出到外面的文本文件中。 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，From tabl_list 列舉要檢索的數據表或者子查詢。 這里要講究的東西還挺多的， A,可以設置別名： From xj_stu S, xj_Cls C 。別名別重復、別是關鍵字就行。 B,可以把一個子查詢當作一個表。select * From (select distinct ClsID From xj_stu ) a 。 其實放在 From 后面的東西別理解成表，直接理解成一個結果集，一個通過各種手段構造的子查詢，這樣就為寫很多復雜的SQL作意識上的準備。 C,聯合查詢，就在這個中間體現了，一般的有: tableA A inner join tableB B on A.ID = B.ID 兩個都有的來連接。去掉inner也一樣。 tableA A left outer join tableB on A.ID = B.ID A中所有的連接起來,去掉outer也一樣。 D,USE/IGNORE/FORCE INDEX 來特別提醒MySQL來進行有些索引得取舍。4,where 子句 是用來限定條件或者建立 表、子查詢關聯的語句。5，limit 分頁中常用 LIMIT 子句可以被用于強制SELECT 語句返回指定的記錄數。LIMIT接受一個或兩個數字參數。參數必須是一個整數常量。如果給定兩個參數，第一個參數指定第一個返回記錄行的偏移量，第二個參數指定返回記錄行的最大數目。初始記錄行的偏移量是 0(而不是 1)： mysql SELECT * FROM table LIMIT 5,10; # 檢索記錄行 6-15為了檢索從某一個偏移量到記錄集的結束所有的記錄行，可以指定第二個參數為 -1： mysql SELECT * FROM table LIMIT 95,-1; # 檢索記錄行 96-last.如果只給定一個參數，它表示返回最大的記錄行數目：mysql SELECT * FROM table LIMIT 5; # 檢索前 5 個記錄行換句話說，LIMIT n 等價于 LIMIT 0,n。6，Group by 是用來進行統計分組合計的功能，后面接要進行統計分組的字段或者表達式。 Count(*)返回由一個 SELECT 語句檢索出來的記錄行中非 NULL 值的記錄總數目：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.)返回一個互不相同的非NULL 的值的總數目：select count(distinct stuid ) From xj_score; 27select count(* ) From xj_score ; 81在 MySQL 中，通過給出一個表達式列表，可以得到不包含 NULL 的不同的表達式組合的數目。 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 ; (結果條數為0)MIN(expr)MAX(expr)返回 expr 的最小或最大值。MIN() 和 MAX() 可以接受一個字符串參數；在這種情況下，它們將返回最小或最大的字符串傳下。查看章節 5.4.3 MySQL 如何使用索引。select stuID,min(score),max(score) From xj_score Group by StuID SUM(expr)返回 expr 的總和。注意，如果返回集中沒有從我任何記錄行，它將返回 NULL ！select sum(score) From xj_score whereStuID=001001select sum(score) From xj_score whereStuID=001001 Group by StuID 7，Order by 排序 可以指定多個字段排序，可以指定別名、需要進行排序，也可以進行表達式進行排序。 如果繼續排序的結果比較多，MySQL會利用 臨時文件進行排序。例如：select * From xj_stu whereClsID=CL001 Order by (case when StuName=zzthen 00000 else StuID end ) 就是要把 學生zz排在第一位，其他的按照學號來排列。 2.2 MySQL用于select 和where 子句的函數介紹/mysql_manual/06-3.html#Other_Functions大家可以自己去看，如果不屬性的話，最好每個都動手寫一個sql來試一下，加深一下感情。這里只列舉幾個常用的， (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 看看和下面的有什么區別：select stuID,avg(score) avgScore Fromxj_score scGroup by stuIDhaving avg(score) =85Order by 2 desc 哪個是正確的？ 2.4 列一下各班級各科目分數的前3名。要求結果為：班級、學號、姓名、科目名稱、成績、名次 。 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：語文及格人數、平均分、及格率，數學及格人數、平均分、及格率，英語及格人數、平均分、及格率。 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 年級排名 學號、姓名、語文、數學、英語、平均分、年級名次 (如果有一門沒有考則單科算分)做法有很多，以下是其中的一種： 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