use Student
select * from Students
select * from Course
select * from Department
select * from Score
--查询studnet表中的数据
select StuId,StuName,StuSex,StuAge,StuBornDate,
StuPhone,StuAddress,DeptId from Students
--只要学生表 和 姓名
select StuId,StuName from Students
--as 指定别名
SELECT CourseId AS '课程编号',CourseName AS '课程名称',Credit as '学分'
FROM Course
--去除重复
SELECT distinct StuId from Score
--查询前五条信息
SELECT top 5 StuId,StuName,StuAge,StuPhone from Students
--查询前五条,并指定别名
SELECT top 5 StuId as '编号',CourseId AS '课程编号',
ExamDate AS '考试日期',ExamScore as '考试成绩'
from Score
--男同学编号，性别年龄
SELECT StuId,StuName,StuAge
from Students
where StuSex=1
--男同学编号，性别年龄,和21岁以上的
SELECT StuId,StuName,StuAge,StuSex
FROM Students
WHERE StuSex=1 and StuAge>=21
--课程编号1001 操作系统大于70分的学生编号
SELECT StuId,ExamScore
FROM Score
WHERE CourseId='C001' AND ExamScore>60
--课程编号c002 和c003 成绩的学生编号,OR 字的使用
SELECT StuId FROM Score
WHERE CourseId='C002' or CourseId='C003'
--使用通配符like % _
--% 包括0个或更多个任意字符串
-- _ 可以代表任意一个字符
-- [] 指定的范围或集合内的任何单个字符
-- [^] 不能早指定的范围或集合内的任何单个字符
--% 号的使用
SELECT StuId,StuName,StuAge,StuAddress
FROM Students
WHERE StuName LIKE '李%'
-- 系别包含 信息 二字的信息
SELECT DeptId,DeptName FROM Department
WHERE DeptName LIKE '%信息%'
--手机号以186开头 以 234 结尾的
SELECT StuId,StuName,StuPhone
FROM Students
WHERE StuPhone LIKE '186#4'
--使用通配符[] 刘张开头的编号 姓名年龄
SELECT StuId,StuName,StuAge
FROM Students
WHERE StuName like '[刘，张]%'
--betweent and 的使用
SELECT StuId,StuName,StuSex,StuBornDate
FROM Students
WHERE StuBornDate BETWEEN '1997-01-01' AND '1998-12-31'
--使用 not beween and 的使用
SELECT StuId,StuName,StuSex,StuBornDate
FROM Students
WHERE StuBornDate NOT BETWEEN '1997-01-01' AND '1998-12-31'
SELECT StuId,StuName,StuSex,StuBornDate
FROM Students
WHERE StuAge BETWEEN 20 AND 27
--is null 和 is not null判断是否为空值
SELECT StuId,StuName,StuAge,StuAddress
FROM Students
WHERE StuAddress IS NOT NULL
SELECT StuId,StuName,StuAge,StuAddress
FROM Students
WHERE StuAddress IS NULL
-- 使用IN NOT IN 关键字 1001 1002 的学生编号，姓名，和系别编号
SELECT StuId,StuName,DeptId
FROM Students
WHERE DeptId IN(1001,1002)
SELECT StuId,StuName,DeptId
FROM Students
WHERE DeptId NOT IN(1001,1002)
--NOT IN
SELECT * FROM Students
WHERE StuName NOT IN('张三','李四','王五')
--年龄排序
--降序
SELECT *
FROM Students
ORDER BY StuAge
--升序
SELECT * FROM Students
ORDER BY StuAge ASC
--成绩相同时 按照降序排列S
SELECT *
FROM Score
ORDER BY ExamScore DESC,StuId ASC
