查询出生年份
select Sname,2014-Sage
FROM Student;
无法识别出2014-Sage这个列名
小写字母表示
select Sname,'YEAR OF BIRTH:',2014-Sage,LOWER(Sdept)
FROM Student;
改变列标题查询
select Sname NAME,'YEAR OF BIRTH:' BIRTH,2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
FROM Student;
查询选修课程学生学号
select Sno
from SC;
取消重复
select DISTINCT Sno
from SC;
带限制条件的查询
select Sname
FROM Student
WHERE Sdept='CS';
去掉重复且带有限制的查询
select DISTINCT Sno
FROM SC
WHERE Grade<90;
带有范围的查询
select Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
使用IN来规定范围的查询
select Sname,Sdept,Sage
FROM Student
WHERE Sdept IN('CS','MA','I
使用LIKE来限制的查询
select Sno,Sname,Sdept,Sage
FROM Student
WHERE Sno LIKE'201215121';
用%的查询
select Sno,Sname,Sdept,Sage
FROM Student
WHERE Sname LIKE'刘%';
用_的查询
select Sno,Sname,Sdept,Sage
FROM Student
WHERE Sname LIKE'张_';
注意数据库字符集为ASCII时一个汉字需要两个_,当字符集为GBK时只需要一个_
同时带有_和%的查询
select Sno,Sname,Sdept,Sage
FROM Student
WHERE Sname LIKE'_成%';
查询某项为空值
select Sno,Cno
FROM SC
WHERE Grade IS NULL;
使用AND来连接查询条件的查询
select Sno,Sname
FROM Student
WHERE Sdept='CS'AND Sage<20;
使用OR来连接查询条件的查询
select Sno,Sname
FROM Student
WHERE Sdept='CS'OR Sdept='MA';
使用ORDER BY对查询进行升序或降序排列
默认为升序,升序(ASC)降序(DESC)
select Sno,Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC;
聚集函数COUNT(*)查询学生总人数
select COUNT(*)
FROM Student;
AVG查询选修某一门课的学生平均成绩
select AVG(Grade)
FROM SC
WHERE Cno='1';
MAX查询选修某一门课程的最高分数
select MAX(Grade)
FROM SC
WHERE Cno='2';
在查询时如果指定了DISTINCT短语,则表示取消指定列中的重复值,否则默认为ALL短语即不取消重复值。
常用查询条件
查询条件 | 谓词 |
---|---|
比较 | =,>,<,>=,<=,!=,<>,!>,!<;NOT+上述比较运算符 |
确定范围 | BETWEEN AND,NOT BETWEEN AND |
确定集合 | IN,NOT IN |
字符匹配 | LIKE,NOT LIKE |
空值 | IS NULL,IS NOT NULL |
多重条件(逻辑运算) | AND,OR,NOT |
本次单表查询实验到此结束