SQL语言和数据分析方法

1、SQL语言和数据分析方法2015-11数据定义语言DDL数据操纵语言DML数据查询语言DQL数据控制语言DCLSELECT * from tbl_ClassSELECT ClassName FROM tbl_ClassSELECT TOP 5 * FROM tbl_ClassSELECT DISTINCT Student_OrderNum ,Exam_OrderNum FROM tbl_GradeSELECT函数聚合函数配置函数加密函数 游标函数日期和时间函数数学函数元数据函数排名函数行集函数安全函数字符串函数系统函数系统统计函数 文本和图像函数执行的操作是将多个值合并为一个值。例如 COUN

2、T、SUM、MIN 和 MAX。SELECT COUNT(*) from tbl_Grade SELECT MAX(Grade) 最高分,MIN(Grade) 最低分,AVG(Grade) 平均分 from tbl_Grade聚合函数可以更改日期和时间的值。SELECT DATEADD(month, 1, 2006-08-30) DATEADD(Month,3,GETDATE()DATEPART(year,GETDATE()日期和时间函数SELECT 2006-04-25T15:50:59.997 AS UnconvertedText, CAST(2006-04-25T15:50:59.997

3、 AS datetime) AS UsingCast, CONVERT(datetime, 2006-04-25T15:50:59.997, 126) AS UsingConvertFrom_ISO8601 ;GO 类型转换函数CAST 和 CONVERT SELECT FLOOR(123.45), FLOOR(-123.45), FLOOR($123.45)SELECT ABS(-1.0), ABS(0.0), ABS(1.0)SELECT RAND(100), RAND(), RAND() 数学函数Select SUBSTRING(中华人民共和国,4,2)Select LEN(中华人民共和

4、国)字符串函数SELECT * FROM tbl_Student where StudentName=王聪 SELECT * FROM tbl_Student where StudentName like 王% SELECT * FROM tbl_Student where StudentName like 王% and StudentSex=男 SELECT * FROM tbl_Student where StudentName like 王% and StudentBirthday between 1993-05-01 and 1993-11-01WHERESELECT * FROM t

5、bl_Student order by Class_OrderNum asc ,StudentBirthday descOrder by SELECT Student_OrderNum ,sum(Grade) 总分 ,AVG(Grade) 平均分 ,COUNT(*) 科目数 FROM tbl_Grade group by Student_OrderNum HAVING sum(Grade) 800 ;GROUP BYselect TeacherName,TeacherSex,TeacherAge,TeacherRank,年龄类别=casewhen TeacherAge=30 and Teach

6、erAge49 then 老年教师end from tbl_teachercaseINNER 指定返回所有匹配的行对。放弃两个表中不匹配的行。如果未指定任何联接类型，此设置为默认设置。LEFT 指定在结果集中包括左表中所有不满足联接条件的行，除了由内部联接返回所有的行之外，还将另外一个表的输出列设置为 NULL。联接SELECT * FROM TestDB.dbo.tbl_teacher ajoin tbl_ClassAdmin b on a.OrderNum=b.Teacher_OrderNum内连接select a.*,b.StudentName FROM tbl_Grade aleft

7、join tbl_Student b on a.Student_OrderNum=b.OrderNum左联接select a.*,b.StudentName FROM tbl_Grade aleft join tbl_Student b on a.Student_OrderNum=b.OrderNum and b.StudentName=娄丹杰select a.*,b.StudentName FROM tbl_Grade ainner join tbl_Student b on a.Student_OrderNum=b.OrderNum and b.StudentName=娄丹杰比较SELEC

8、T * FROM TestDB.dbo.tbl_teacher where TeacherName like 王%union allSELECT * FROM TestDB.dbo.tbl_teacher where TeacherName like 王%SELECT * FROM TestDB.dbo.tbl_teacher where TeacherName like 王%Union SELECT * FROM TestDB.dbo.tbl_teacher where TeacherName like 王%联合UNION ALL select * from (SELECT * FROM t

9、bl_teacher ) aselect * from (SELECT Student_OrderNum ,sum(Grade) 总分 ,AVG(Grade) 平均分 ,COUNT(*) 科目数 FROM tbl_Grade group by Student_OrderNum ) a where 总分800嵌套查询SELECT GUID,OrderNum,ClassName ,(select COUNT(*) from TestDB.dbo.tbl_Student b where b.Class_OrderNum=a.OrderNum ) 班级人数 FROM TestDB.dbo.tbl_Cl

10、ass a嵌套查询SELECT * FROM TestDB.dbo.tbl_teacher where OrderNum in (select Teacher_OrderNum from TestDB.dbo.tbl_ClassAdmin )嵌套查询select * into #AAA from tbl_Gradeselect * into #BBB from (SELECT Student_OrderNum ,sum(Grade) 总分 ,AVG(Grade) 平均分 ,COUNT(*) 科目数 FROM tbl_Grade group by Student_OrderNum ) asele

11、ct * from #BBB where 总分800保存结果集和临时表明确需求确定数据来源研究数据库结构设计分析方法编写语句并测试数据分析使用嵌套查询实现使用中间表分步实现使用游标编程使用开发工具编程复杂语句的编写查询某个班级的所有课程和对应的授课老师实例1班级表 tbl_Class班级和教师表tbl_ClassTeacher教师表tbl_teacher科目表tbl_Subjectselect tbl_Subject.SubjectName,tbl_teacher.TeacherNamefrom tbl_ClassTeacher join tbl_Teacher on tbl_ClassTeacher.Teacher_OrderNum=tbl_Teacher.OrderNumjoin tbl_Subject on tbl_Teacher.Subject_OrderNum=tbl_Subject.OrderNumw