SQL Server2008语句查询实例
- 在S表中查询学生的出生年,显示学号、姓名、出生年:
SELECT SNO,SNAME,YEAR(GETDATE())-AGE FROM S;
- 从SC表中查询每门功课的平均分、最高分、最低分:
平均分:
SELECT SC.CNO,ROUND(AVG(SC.GRADE),2) AS ‘AVERAGE’ FROM S INNER JOIN SC ON S.SNO=SC.SNO GROUP BY CNO;
最高分:
SELECT SC.CNO,ROUND(MAX(SC.GRADE),2) AS ‘MAX’ FROM S INNER JOIN SC ON S.SNO=SC.SNO GROUP BY CNO;
最低分:
SELECT SC.CNO,ROUND(MIN(SC.GRADE),2) AS’MIN’ FROM S INNER JOIN SC ON S.SNO=SC.SNO GROUP BY CNO
- 查询S表中姓刘但名字中不含下划线的学生:SELECT SNAME FROM S WHERE SNAME LIKE ‘刘%’ AND SNAME NOT LIKE ‘%_%’ ESCAPE ‘’;
- 统计S表中名字只含两个字符的学生人数:
SELECT count(*)as’人数’ FROM S WHERE SNAME LIKE’—’; - 找出名字中含有英文字母的学生:
SELECT * from SWHERE SNAME like ‘%[A-Z&a-z]%’;