黑马程序员--SQL中常用函数类型

 

数据库中的数字函数

Abs（）：求绝对值

Ceiling():舍入到最大整数。3.33将被舍入为4

Floor（）：舍入到最小整数3.33将被舍入为3

Round（）：四舍五入。舍到离我半径最近的数。Round  半径

字符串函数：

Len（）：计算字符串的长度select FName，len（FName） from T_Employee

Lower（）、upper（）：转小写、大写

Ltrim():字符串左侧的空格去掉

rtrim():字符串右侧的空格去掉

两边如果都去掉的话，就用ltrim（rtrim（））

substring(string,start_position,length)参数string为主字符串，start_position为子字符串在主字符串中的起始位置，length为子字符串的最大长度。Select substring(‘abcdef`111’,3,2) from表

日期函数：

getdate（）：取得当前日期时间

dateadd(datepart,number,date),计算增加以后的日期。参数date为待计算的日期；参数number为增量；参数datepart为计量单位。

Dateadd（day，3，date）为计算日期date的3天后的日期。

Dateadd(month,-8,date)为计算日期date的8个月之前的日期

Datediff（datepart，startdate，enddate）：计算两个日期之间的差额。Datepart为计算单位，

统计不同工龄的员工的个数：

Select DateDiff(year,FInDate,getdate()),count(*) from T_Employee
Group by DateDiff(year,FInDate,getdate())

Datepart（datepart，date）：规范一个日期的特定部分

类型转换函数：

Cast(expression as date_type)               expression表达式

Convert(date_type,expression)             date_type类型

Select FIdnumber,

Right(FIdnumber,3) as 后3位

Cast（Right(FIdnumber,3) as  integer）as 后3位的整数形式。

Cast（Right(FIdnumber,3) as  integer）+1   as 后3位加1。

Convert(integer,right(FIdNumber,3))/2  as后3位除以2

From T_person

空值处理函数：

Isnull（expression，value）：如果expression不为空则返回expression，否则返回value。

Select isnull(FName,’佚名’) as 姓名 from T_Employee

Case函数用法1：

单值判断，相当于switch case

Case expression
When value1 then return value1
When value2 then return value2
When value3 then return value3
When value4 then return value4
When value5 then return value5
Else default return value
end

可以使用as来标明case中的种种情况

练习：

select(case when a>b then a
Else b
end),(case when b>c then b
else c
end)
from t

在这里不能够少了end

关于胜负计分的sql语句

Select Name,
Sum(
       Case Score
       When n’胜’ then 1
       Else 0
       end
)as 胜

sum(
       Case Score
       When n’负’ then 1
       Else 0
       end
)as 负
From T_Scores
Group by Name

练习

输入素有数据中通话时间最长的5条记录：order/by  datediff

输出所有数据中拨打长途号码（对方号码以0开头）的总时长：like、sum

输出本月通话总时长最多的前三个呼叫员的编号。Datediff（month……）、sum、order/by

Select callerNumber,TelNum,datediff(month,StartDateTime,getdate())
From T_CallRecords1
Select * from T_CallRecords1
Where datediff（month，startdatetime，getdate（））=0

Select top 3 CallerNumber from T_CallRecords1
Where datediff(month,) StartDateTime,getdate()=0
Group by CallerNumber
Order by Sum(datediff(second,StartDateTime,EndDateTime))  DESC

输出本月拨打电话次数最多的前三个呼叫员的编号group/by、count（*）

Select callerNumber,count(*)
From T_CallRecords1
Where datediff(month,StartDateTime,getdate())=0
Group by callerNumber
Order by count(*) DESC

汇总  {市内号码总时长}{长途号码总时长}

Select
Sum((
Case
When TelNum not like ‘0’ then datediff(second,StartDateTime,EndDateTime)
Else 0
end
))as  市内通话，

Sum((
Case
When TelNum  like ‘0’ then datediff (second,StartDateTime,EndDateTime)
Else 0
end
))as  长途通话
From T_CallRecords1

呼叫员编号、对方号码、通话时长

Select callerNumber,TelNum,datediff(second,StartDateTime,EndDateTime)
From T_CallRecords1
Union all
Select  ‘汇总’
Convert(varchar(50)),
Sum((
Case
When TelNum not like ‘0’ then datediff(second,StartDateTime,EndDateTime)
Else 0
end
))as  市内通话，

Sum((
Case
When TelNum  like ‘0’ then datediff (second,StartDateTime,EndDateTime)
Else 0
end
))as  长途通话
From T_CallRecords1

索引：index

全表扫描：对数据进行检索（select）效率最差的是全表扫描，就是一条条的找。

如果没有目录，查汉语字典就要一页一页的翻，而又了目录只要查询目录即可。为了提高检索的速度，可以为经常进行检索的列添加索引，相当于创建目录。

创建索引的方式，在表设计器中点击右键，选择“索引/键”->添加->在列中选择索引包含的列

使用索引能提高查询效率，但是索引也是占据空间的，而且添加、更新、删除数据的时候也需要同步更新索引，因此会降低insert、update、delete的速度。只在经常检索的字段上（where）创建索引。

（*）即使创建了索引，仍然有可能全表扫描，比如like、函数、类型转换等。

表连接join

（*）Inner Join、Left Join、Right Join

Select o.BillNo, c.Name, c.Age
From T_orders  as o
join
T_Customers as c on o.CustomerId=c.Id

要求显示所有年龄大于15岁的顾客购买的订单号、客户姓名、客户年龄

Select o.BillNo, c.Name, c.Age
From T_orders  as o  join T_Customers as c on o.CustomerId=c.Id
Where c.Age>15

要求显示年龄大于平均年龄的顾客购买的订单

Select o.BillNo, c.Name, c.Age
From T_orders  as o  join T_Customers as c on o.CustomerId=c.Id
Where c.Age>(select avg(Age) from T_Customers)

子查询：

将一个查询语句作为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当做结果集的查询语句被称为子查询。所有可以使用表的地方几乎都可以使用子查询来代替。

Select * from（select * from T2 where FAge<30）

单值作为子查询：

select 1 as f1,2（select min(FYearPublished) from T_Book）,
Select max (FYearPublished ) from T_Book)  as  f4
Select 1 as f1, （select min(FYearPublished), max (FYearPublished ) from T_Book）

只有返回且仅返回一行、一列数据的子查询才能当成单值子查询。下面的情况是错误的：

select 1 as f1,2,(select FYearPublished from T_Book)

如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合，

Select * from T_Reader
Where FYearOfJoin IN
(
Select FYearPublished from T_Book
)

限制结果集，返回第3行到第5行的数据：

Select * from
（
Select row_number() over(order by FSalary DESC) as rownum,
FNumber,FName,FSalary,FAge from T_Employee
）as  a
Where a.rownum>=3 and a.rownum<=5