SQL基础培训专题.docx

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