数据库(数据表查询、索引、视图)
一、数据查询
SELECT命令的格式与基本使用
基本格式:
Select select_list [into Newtable]
From表1[,表2……]
[where 条件]
[group by 分组条件]
[having 筛选条件]
[order by 排序表达式 [desc / asc][,….]]
1.简单查询
(1)查询所有列:select * from student
(2)查询部分列:select sno,sname from student
(3)去掉重复元组:select distinct sdept from student
(4)限制返回的行数(top n / top n percent):select top 5 * from student
(5)计算列——无列名:select sno,sname,YEAR(getdate())-year(sbirthday) from student
(6)查询结果起别名:三种方法:1.列名 as 别名 2.别名=列名 3.列名 别名:select sno as 学号,姓名=sname,YEAR(getdate())-year(sbirthday) 年龄 from student
2.条件查询
(1)比较大小:select sno,grade from sc where cno=‘C101’
(2)多重条件查询 (条件间根据语义进行用NOT、AND、OR):select * from sc where cno='C101 and grade>=85
(3)确定范围:SELECT * FROM SC WHERE grade BETWEEN 85 AND 100
(4)模糊查询(like):select sno,sname from student where sname like ‘_力%’
匹配符:
%:匹配任意个任意字符
_(下划线) :匹配单个任意字符
[ ]:匹配中括号中的任意个字符
[^ ]:匹配除中括号中的任意个字符
(5)空值查询:select * from sc where grade is not null
3.查询的排序
当需要对查询结果排序时,应该使用ORDER BY子句,ORDER BY子句必须出现在其他子句之后。排序方式可以指定,DESC为降序,ASC为升序,缺省时为升序。
例:
select sno as 学号,姓名=sname,YEAR(getdate())-year(sbirthday) 年龄 from stud