sql语言和数据分析方法.pptVIP

查询某个班级的所有课程和对应的授课老师 select tbl_Subject.SubjectName,tbl_teacher.TeacherName from tbl_ClassTeacher join tbl_Teacher on tbl_ClassTeacher.Teacher_OrderNum=tbl_Teacher.OrderNum join tbl_Subject on tbl_Teacher.Subject_OrderNum=tbl_Subject.OrderNum where Class_OrderNum='0010' 显示出所有没有教课的老师的姓名 select * from tbl_Teacher where OrderNum not in (select Teacher_OrderNum from tbl_ClassTeacher) 2015-11 数据定义语言DDL 数据操纵语言DML 数据查询语言DQL 数据控制语言DCL SELECT * from tbl_Class SELECT [ClassName] FROM [tbl_Class] SELECT TOP 5 * FROM [tbl_Class] SELECT DISTINCT [Student_OrderNum] ,[Exam_OrderNum] FROM [tbl_Grade] 聚合函数 配置函数 加密函数 游标函数 日期和时间函数 数学函数 元数据函数 排名函数 行集函数 安全函数 字符串函数 系统函数 系统统计函数 文本和图像函数 执行的操作是将多个值合并为一个值。例如 COUNT、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' AS datetime) AS UsingCast, CONVERT(datetime, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ; GO 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('中华人民共和国') 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-01' SELECT * FROM [tbl_Student] order by Class_OrderNum asc ,StudentBirthday desc SELECT [Student_OrderNum] ,sum([Grade]) 总分 ,AVG(Grade) 平均分 ,COUNT(*) 科目数 FROM [tbl_Grade] group by [Student_OrderNum] HAVING sum([Grade]) > 800 ; select TeacherName,TeacherSex,TeacherAge,TeacherRank, [年龄类别]=case when TeacherAge<30 then '青年教师' when TeacherAge>