【数据库】【查询】

USE STU008

查询

查整表

SELECT *FROM student008//*可以查全部信息

查列

SELECT Sno,Sname FROM student008

结果去重

SELECT DISTINCT Class FROM student008

AS变查询标题

SELECT Sno AS '学号',Sname AS'姓名'FROM student008

最大值，最小值，平均值

SELECT MAX(Degree),AVG(Degree),MIN(DEgree)

FROM Score008

条件查询

SELECT * FROM student008

 WHERE Class ='95033'

SELECT * FROM student008
 WHERE Sname like '王%'

SELECT * FROM student008
 WHERE YEAR(GETDATE())-YEAR([Sbirthday]) IN (21,25,22)

结果排序

SELECT * FROM Score008

WHERE Degree IS not NULL

ORDER BY Sno,Cno DESC

多表查询

SELECT s.Sno,Sname,c.Cno,Cname,Degree from student008 s join Score008 sc

On s.Sno=sc.Sno join course008 c on c.Cno=sc.Cno

//On s.Sno=sc.Sno用这个连接

有null也匹配

SELECT s.Sno,s.Sname,

c.Cno,c.Cname,

t.Tname AS 任课教师,

sc.Degree

FROM student008 s

FULL JOIN Score008 sc ON s.Sno=sc.Sno

FULL JOIN Course008 c ON sc.Cno=c.Cno

FULL JOIN teacher008 t ON c.Tno=t.Tno;