用SQL语句操作数据

--查询年级编号为1的学生信息
SELECT * FROM dbo.Student
WHERE GradeId=1

--查询多个表的数据
SELECT * FROM dbo.Grade,dbo.Student
WHERE Grade.GradeId=Student.GradeId

--查询年级编号为‘Y2’的学生信息
SELECT Student.StudentName,Grade.GradeName
FROM dbo.Student,dbo.Grade
WHERE Student.GradeId=Grade.GradeId AND  Grade.GradeName='Y2'

--查询学生姓名和分数
SELECT dbo.Student.StudentName,dbo.Result.StudentResult
FROM dbo.Student,dbo.Result
WHERE Student.StudentNo=Result.StudentNo

--查询学生编号和科目名称
SELECT dbo.Student.StudentNo,dbo.Subject.SubjectName
FROM dbo.Subject,dbo.Student
WHERE dbo.Student.GradeId=dbo.Subject.Subjectld

--查询学生（姓名）在那个科目考了多少分
SELECT dbo.Student.StudentName ,dbo.Subject.SubjectName ,dbo.Result.StudentResult
FROM dbo.Student,dbo.Subject,dbo.Result
WHERE dbo.Student.StudentNo=dbo.Result.StudentNo  AND dbo.Subject.Subjectld=dbo.Result.Subjectld
AND dbo.Result.StudentResult <=60

--如何查询多表中的数据（多表联查）
--1.明确查询的列和表，找到表与表之间的关系，加限定条件

--当AS别名放入在列里代表列的别名，放在表中代表标的替换名称
SELECT StudentNo AS '学生编号',StudentName AS '学生姓名'FROM dbo.Student WHERE GradeId=1

SELECT s.StudentName,g.GradeName FROM dbo.Student AS s,dbo.Grade AS g WHERE s.GradeId=g.GradeId

--使用+拼接列名
SELECT StudentName+'-----'+ Sex AS '姓名性别' FROM dbo.Student

--查询空值的列
SELECT * FROM Student Where Email IS NULL

--常亮列
SELECT StudentName AS '学生姓名', '10010' AS '邮编' FROM dbo.Student

--查询固定行数
SELECT TOP 20 * FROM dbo.Student

--查询百分比显示行数    PERCENT
SELECT TOP 20 PERCENT * FROM dbo.Student

--数据排序：降序（desc）    升序（asc） 默认升序
--注意事项：1.ORDER BY 在WHERE 后面 2.多个用 "," 隔开，3.顺序：从前往后
SELECT * FROM dbo.Result WHERE SubjectId=1 ORDER BY StudentResult DESC,ExamDate ASC