数据查询基础
--查询年级编号为1的学生记录:INSERT UPDATE DELETE
--基本语法:SELECT 列名 FROM 表名 [WHERE 条件表达式] [ORDER BY]
SELECT StudentNo,StudentName FROM Student WHERE GradeId=1
--基本语法:SELECT 列名 FROM 表名 [WHERE 条件表达式] [ORDER BY]
SELECT StudentNo,StudentName FROM Student WHERE GradeId=1
--查询学生姓名和年级名称
--查询多个表中数据时:1.明确查询的列 2.明确查询的表 3.WHERE 找表与表之间的共同列(主外键关系)
SELECT Student.StudentName,Grade.GradeName FROM Student,Grade WHERE Student.GradeId=Grade.GradeId
--查询多个表中数据时:1.明确查询的列 2.明确查询的表 3.WHERE 找表与表之间的共同列(主外键关系)
SELECT Student.StudentName,Grade.GradeName FROM Student,Grade WHERE Student.GradeId=Grade.GradeId
--查询年级名称为'Y2'的所有学生
SELECT Student.StudentName,Grade.GradeName FROM Student,Grade WHERE Grade.GradeName='Y2' and Student.GradeId=Grade.GradeId
--查询学生姓名和分数
SELECT Student.StudentName,Result.StudentResult FROM Student,Result WHERE Student.StudentNo=Result.StudentNo
SELECT Student.StudentName,Result.StudentResult FROM Student,Result WHERE Student.StudentNo=Result.StudentNo
--查询学生编号和所考科目名称
SELECT Result.StudentNo,Subject.SubjectName FROM Result,Subject WHERE Result.SubjectId=Subject.SubjectId
SELECT Result.StudentNo,Subject.SubjectName FROM Result,Subject WHERE Result.SubjectId=Subject.SubjectId
--查询学生(姓名)在哪个科目考了多少分
SELECT Student.StudentName,Subject.SubjectName,Result.StudentResult FROM Student,Subject,Result
WHERE Student.StudentNo=Result.StudentNo AND Subject.SubjectId=Result.SubjectId AND Result.StudentResult<=60
SELECT Student.StudentName,Subject.SubjectName,Result.StudentResult FROM Student,Subject,Result
WHERE Student.StudentNo=Result.StudentNo AND Subject.SubjectId=Result.SubjectId AND Result.StudentResult<=60
--如何查询多表中的数据(多表联查)
--1.明确查询的列和查询的表 2.找到表与表之间的关系 3.加其他限定条件
--当AS别名放入到列的时候代表:列的替换名称
--当AS放入到表名时:代表表的替换名称,可以用表的替换名称.表的列名
SELECT StudentNo AS '学生编号',StudentName AS '学生姓名' FROM Student WHERE GradeId=1
SELECT s.StudentName,g.GradeName FROM Student AS s,Grade AS g WHERE s.GradeId=g.GradeId
--当AS放入到表名时:代表表的替换名称,可以用表的替换名称.表的列名
SELECT StudentNo AS '学生编号',StudentName AS '学生姓名' FROM Student WHERE GradeId=1
SELECT s.StudentName,g.GradeName FROM Student AS s,Grade AS g WHERE s.GradeId=g.GradeId
--使用+拼接列名
SELECT StudentName+'-'+Sex AS '姓名性别' FROM Student
SELECT StudentName+'-'+Sex AS '姓名性别' FROM Student
--查询空值的列
SELECT * FROM Student WHERE Email IS NULL
SELECT * FROM Student WHERE Email IS NULL
--常量列
SELECT StudentName AS '学生姓名','10010' AS '邮编' FROM Student
SELECT StudentName AS '学生姓名','10010' AS '邮编' FROM Student
--查询固定行数
SELECT TOP 20 * FROM Student
SELECT TOP 20 * FROM Student
--查询百分比固定行数
SELECT TOP 20 PERCENT * FROM Student
SELECT TOP 20 PERCENT * FROM Student
--数据排序:降序拍列(desc) 升序排列(asc):默认的
--注意:1.ORDER BY 在WHERE条件后 2.多列排序时之间用","分割 3.多列排列时根据从前往后的规则进行排序
-- 4.使用表达式排序时原有数据不会更改
SELECT * FROM Result WHERE SubjectId=1 ORDER BY StudentResult DESC,ExamDate ASC