一、简单查询
1、无条件查询
SELECT *FROM student
2、查询指定字段
SELECT sclass,snumb FROM student
SELECT DISTINCT sclass FROM student
SELECT sage+1 AS next_year FROM student--查询年龄值加一作为next_year字段的值,仅仅是显示名字
SELECT snum,sname INTO new_student FROM student
二、按条件查询
SELECT * FROM student WHERE sclasss = '电气' AND sage>=18 AND sage <=20
三、使用特殊运算关系符查询
1、 IN, NOT IN
SELECT *FROM Student WHERE province IN('江苏',‘上海’)
SELECT *FROM Student WHERE stel IS NULL
SELECT *FROM Student WHERE sage BETWEEN 18 AND 20
%,匹配0个或多个字符
_, 匹配一个任意字符
[],匹配集合中的任意一个字符 [ABC]
[^], 不匹配集合中的任意一个字符 [^ABC]
SELECT *FROM Student WHERE name LIKE '%雨%'
SELECT *FROM Student WHERE sname LIKE '[^曹李]%'
四、对查询结果排序
ASC:升序排列(默认值)
DESC:降序排列
SELECT sname,sage FROM Student WHERE sclass ='航天' ORDER BY sage DESC, snumb ASC
五、函数查询
1、统计函数
SUM():求和函数
AVG():求平均函数
MIN() / MAX():求最大最小值函数
COUNT():计数函数
SELECT MAX(sage),MIX(sage),AVG(sage) , COUNT(*) FROM Student
SELECT CONVERT(VARCHAR,120) --将120转化为字符串
SELECT GETDATE()--获取当前时间
DATEADD(year,0,'2010/5/10 10:10:10.100') --三个参数分别为增加的part,增加的值和需要增加的日期 part 可以取 year month day hour second
SELECT DATANAME(year,sbirthday) FROM student WHERE class ="radio" --查询radio班级中学生生日的年份
4、数学函数和字符串函数
六、分组查询
SELECT AVG(sage),COUNT(*) FROM Student GROUP BY sclass --计算出student表中的每个班的平均年龄和人数
七、多表查询
1、无条件查询
两个数据表直接排列组合,很多数据没有意义
2、等值连接
<pre name="code" class="sql"><pre name="code" class="sql">SELECT * FROM score stul WHERE score.numb = stul.numb
3、不等值连接
SELECT numb,cnum,score,grade FROM score,grade WHERE score.score BETWEEN grade.gstart AND grade.gend
SELECT * FROM score INNER JOIN stul ON score.numb = stul.numb
5、外连接 OUTER JOIN
外连接分为左外连接和右外连接,当没有对应项的时候,左外连接会显示表1中的项,右外连接会显示表二中的对应项(表2 为 JION后面的那一项)
SELECT * FROM score LEFT OUTER JOIN stul ON stul.numb = score.numb
八、子查询
SELECT sclass FROM student2 GROUP BY sclass HAVING COUNT(*)<5 --查询人数小于5的班级
SELECT snumb,sname,sclass FROM student2 WHERE sclass IN (SELECT sclass FROM student2 GROUP BY sclass HAVING COUNT(*)<5) --查询嵌套