The operations in SQLServer2005(SQL)(二)
5
//限行查询
[sql]
Select top 5 * from users order by age desc
Select top 5 percent * from users
//经典例子：按工资从高到低的排序检索从第六名开始一共五人信息
[sql]
Select top 5 salary from employee where id not in(select top 5 id from users order by salary desc) order by salary desc
//保持数据的唯一
[sql]
Select distinct eName fromemployee--保持整行数据的唯一性
//联合查询，上下字段的个数必须一致，且数据类型相容
[sql]
Select name,age from users
Union all--默认会将完全重复的数据合并，all可以阻止合并
Select name,5 from users2
//(联合查询的运用)报表的制作
[sql]
Select ‘正式工最大年龄’,max(fAge) from T_employee
Union all
Select ‘正式工最小年龄’,min(fAge) from T_employee
Union all
Select ‘临时工最小年龄’,min(fAge) from T_tempEmployee
Union all
Select ‘临时工最大年龄’,max(fAge) from T_tempEmployee
Select FNumber,FSalary from T_Employee
Union all
Select ‘工资合计’,sum(Fsalary) from T_Employee
//数据库函数
[sql]
Select ABS(-5)--绝对值5
Select ceiling(5.2) --大于5.2的最小整数
Select floor(-3.5)--小于-3.5的最大整数
Select round(3.1415926,3)--四舍五入，指定取舍位3，结果为3.1420000
Select len(‘abc’)--3
Select lower(‘ABC’)--abc
Select upper(‘abc’)--ABC
Select ltrim(‘ china ’)--china
Select rtrim(‘ china ’)-- china
Select substring(‘yuanshenjian’,3,5)--开始位置为3，长度为5
//日期函数
[sql]
Select getdate();--取得当前日期
Select daeAdd(day,5,getdate())--当前时间天数加3
Select dateDiff(day,’1990-08-02’,getdate())--1990-08-02距离当前时间的天数
Select datePart(year,getDate())--返回一个日期的特定部分
//经典语句
[sql]
Select dateDiff(year,FinDate,getDate()), count(*) from T_Employee
Group by dateDiff(year,FinDate,getDate())
Having count(*)>2
//类型转换
[sql]
Select cast (‘123’asint),cast(‘2012-11-23’as datetime)
Select convert(datetime,’2012-11-23’),convert(varchar(50),123)
//流控函数,如果FName为null,赋值为“佚名”
[sql]
Select isnull(FName,’佚名’) as 姓名 from T_Employee
//单值判断
[sql]
Select FName,
(
case Flevel
when 1 then‘普通客户’
when 2 then‘会员’
when 3 then‘VIP’
else ‘未知客户类型’
end--一定要加end
)as 客户类型
from T_Customer
//l练习：表中有A,B,C三列，但A大于B时选A，否则选B，但B大于C时
选B，否则选C
[sql]
Select
(
case
when A>B then A else B
end
),
(
case
when B>C then B else C
end
)
From player
//练习二
[sql]
Select Name as 队名,
sum(
case scores
when ‘胜’ then 1
else 0
end
)as 胜,
sum(
case scores
when ‘负’ then 1
else 0
end
)as 负
from Team
group by Name
//数据库的创建
[sql]
if exsits(select * from sys.database when [name]=’market’)
drop database market
create database market
on
(
name=’market.mdf’,
filename=’E:\Microsoft\market.mdf’,
size=5,
maxsize=555,
filegrowth=55
)
log on
(
name=’market’,
filename=’market.ldf’,
size=5,
maxsize=55,
filegrowth=55%
)
www.2cto.com
//表的创建
[sql]
if exists(select * from sys.objects where[name]=’employee’)
drop table employee
create table employee
(
eId varchar(5) not null primary key,
eSex bit not null defa