TOP
top 可以限制查询的记录条数
例:
select top 1 * from table1;
表示查询表中的第一条记录
percent 可以限制选查询面百分之多少的记录
例:
select top 50 percent * from table;
表示查询表中前面百分之50的记录
LIKE
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式
例:
select * from people where PeopleName like '%李';
可以查询出people中PeopleName 以‘李’结尾的记录
select * from people where PeopleName like '李%';
可以查询出people中PeopleName 以‘李’开头的记录
select * from people where PeopleName like '%李%';
可以查询出people中PeopleName 包含‘李’的记录
select * from people where PeopleName not like '%李%';
可以查询出people中PeopleName 不包含‘李’的记录
通配符
'%' 可以替代0个或者多个字符
'_' 可以替代1个字符
[character-character]通配符:指定范围内的任何单个字符
[^]通配符:不在列表或范围内的任何单个字符
例:%
select * from people where PeopleName like ‘赵%’;
查询出people 表中PeopleName 以‘赵’开头的所有记录
select * from people where PeopleName like ‘%赵’;
查询出people表中PeopleName 以‘赵’结尾的所有记录
select * from people where PeopleName like ‘%赵%’;
查询出people表中PeopleName 包含‘赵’的所有记录
例:_
select * from people where PeopleName like '赵_';
查询出people表中PeopleName 以‘赵’开头,且只有两个字的记录
select * from people where PeopleName like '_赵';
查询出people表中PeopleName 以‘赵’结尾,且只有两个字的记录
例:[character-character]
select * from people where PeopleName like '[李赵]%';
查询出people表中PeopleName 以‘李'或'赵’开头的记录
select * from people where PeopleName like '[a-d]%';
查询出people表中PeopleName 第一个字母为a-d之间的记录
例:[^]
select * from people where PeopleName ‘[^李赵]%’;
查询出people表中PeopleName第一个字不是'李‘、'赵'的记录
select * from people where PeopleName like '[^a-d]%';
查询出people表中PeopleName 第一个字母不是a-d之间的记录
IN 操作符
IN 操作符允许在 WHERE 子句中规定多个值
例:
select * from people where PeopleName in ('赵四','许多');
查询出people 表中PeopleName 为'赵四'或者'许多'的所有记录
BETWEEN 操作符
BETWEEN 操作符用于选取介于两个值之间的数据范围内的值,这些值可以是数值、文本或者日期
例:
select * from people where PeopleAge between 20 and 30;
查询出people 中PeopleAge 在20到30之间的所有记录
select * from people where PeopleName between 'A' and 'F';
查询出people 中PeopleAge 在20到30之间的所有记录
select * from people where PeopleBir between '2000-1-1 00:01:00' and '2010-1-1 00:01:00';
查询出people 中,PeopleBir 在2000-1-1 00:01:00到2000-1-1 00:01:00之间的记录
NOT BETWEEN
select * from people where PeopleAge not between 0 and 20;
查询出people 中PeopleAge 不在0到20之间的记录
带有 IN 的 BETWEEN 操作符实例
select * from people where (PeopleAge between 20 and 30) AND PeopleName not in ('李五','赵四');
查询出people 中PeopleAge 在20到30之间且PeopleName不是'李五'、'赵四'的记录
注意:在不同的数据库中between and操作会产生不同的操作结果。在SQL Sever中,between and 操作查询出来的结果包含了两端的值。
别名
可以为表名称或者列名称指定别名,以下情况时用别名会很方便
- 在查询中涉及超过一个表
- 在查询中使用了函数
- 列名称很长或者可读性差
- 需要把两个列或者多个列结合在一起
例:为列名指定别名
select PeopleName as Name,PeopleAge as Age from C;
select P.PeopleName,P.PeopleAge,C.Name FROM people AS P inner join class as C on P.ClasID=C.ClasID;