字符串函数，日期函数，类型转换等

时间：2022-06-01 22:19:11


查询总结
基本查询


1、select * from department


2、distinct 去除重复行 
  top 5
  top 10 percent


3、聚合函数

       --avg() 平均值 
  --sum  求和
  --count  求行数
  --min 求最小值
  --max 求最大值



4、带条件查询

        select 列 from 表 where 条件
between...and...
in (1,3,5)  lie = 1 or lie =3


   模糊查询 
   like  %代表任意多字符  _代表任意单个字符   [0-9][a-Z]不区分大小写 [acd]  匹配范围内的单个字符


5、查找null值 
is null   is not null=================


6、对查询排序 order by 列名 asc升序(可省略)
   desc 降序


7、难点====分组查询group by...having


在分组查询中，查询的列名
必须出现在group by后或者在聚合函数中


--查询平均工资大于两千块钱的部门

select department_id,avg(wages)
from employee


where department_id  is not null

group by department_id

having avg(wages)>2000

以上都是要掌握的，下面是要了解的



8、同时插入多条语句


把查询到的结果插入到现有表中

insert into 现有表  
select 字段  from 表



把现有表的数据插入到一个新表

select 字段
  into 新表
 from 表

--把现有表的数据插入到新表（表不能存在）

--select * into newStudent from student

--把现有表的数据复制到一个已存在的表

--insert into backupStudent select * from students


9、合并查询union 去除重复项

union all 不去除重复项 所以效率高

一次插入多条数据


insert into Score(studentId,english,math)

select 1,80,100 union

select 2,60,80


函数

1、ISNULL(expression,value)     
如果expression不为null返回expression表达式的值，否则返回value的值

2、字符串函数

LEN() ：计算字符串长度

LOWER() 、UPPER () ：转小写、大写

LTRIM()：字符串左侧的空格去掉 

RTRIM () ：字符串右侧的空格去掉 

LTRIM(RTRIM('   bb     '))
LEFT()、RIGHT()  
截取字符串
SUBSTRING(string,start_position,length)
参数string为主字符串，start_position为子字符串在主字符串中的起始位置（从1开始），length为子字符串的最大长度。SELECT  SUBSTRING('abcdef111',2,3) 
REPLACE(string,oldstr,newstr)

3、日期函数
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 为计量单位，可取值参考DateAdd。

DATEPART (datepart,date)：返回一个日期的特定部分 整数

DATENAME(datepart,date):返回日期中指定部分 字符串
        YEAR() MONTH() DAY()

4、类型转换函数

CAST ( expression AS data_type)
case(score as varchar(10))

CONVERT ( data_type, expression[,style])

取日期部分
2012-12-21

1、CONVERT(varchar(100),getdate(),23)

2、CONVERT(varchar(10),getdate(),21)




嵌套查询(子查询)
子查询返回的值不止一个。

--当子查询跟随在 =、!=、<、<=、>、>= 之后，或 子查询用作表达式时，这种情况是不允许的。



在子查询中，一搬我们会使用in 代替 =使用
select employee_id,employee_name
from employee
where department_id in
(select department_id 
from department where department_name='销售部')
连接查询
内连接  inner join...on...

查询满足on后面条件的数据

外连接
左连接
left join...on...
先查出左表中的所有数据
再使用on后面的条件对数据过滤
右连接
right join...on...
先查出右表中的所有数据
再使用on后面的条件对数据过滤


（*）交叉连接 
cross join 没有on
第一个表的每一行和后面表的每一行进行连接
没有条件。是其它连接的基础



字符串函数（*）


LEN() ：计算字符串长度 LOWER() 、UPPER () ：转小写、大写 LTRIM()：字符串左侧的空格去掉 RTRIM () ：字符串右侧的空格去掉 LTRIM(RTRIM('         bb        ')) LEFT()、RIGHT() 截取取字符串 SUBSTRING(string,start_position,length)

参数string为主字符串，start_position为子字符串在主字符串中的起始位置，length为子字符串的最大长度。SELECT SUBSTRING('abcdef111',2,3)

日期函数

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为计量单位，可取值参考DateAdd。 统计不同入学年数的学生个数：select DateDiff(year,sInDate,getdate()) ,count(*) from student Group by DateDiff(year,sInDate,getdate()) DATEPART (datepart,date)：返回一个日期的特定部分 统计学生的生日年份个数：select DatePart(year,sBirthday),count(*) from student group by DatePart(year, sBirthday)

Datepart可选值

取值  别名  说明

year  yy,yyyy 年份

quarter  qq,q 季度

month  mm,m 月份

dayofyear dy,y 当年度的第几天

day  dd,d 日

week  wk,ww 当年度的第几周

weekday  dw,w 星期几

hour  hh 小时

minute  mi,n 分

second  ss,s 秒

millisecond  ms 毫秒

类型转换函数

CAST ( expression AS data_type) CONVERT ( data_type, expression) Select ‘您的班级编号’+ 1 错误这里+是数学运算符 SELECTFIdNumber, CAST(RIGHT(sNo,3)ASINTEGER)as后三位的整数形式, CAST(RIGHT(sNo,3)ASINTEGER)+1as后三位加1, CONVERT(INTEGER,RIGHT(sNo,3))/2as后三位除以2 FROMstudent

select '平均成绩是'+ cast(30 asvarchar(3))

select cast(9.85 as int) 舍去小数

ROUND()  4舍5入

空值处理函数

执行备注中的代码 ISNULL(expression,value) ：如果expression不为空则返回expression，否则返回value。 select studentId,isnull(english,0) from score

--字符串函数
select len('213')


select len(sName),sName from student


select lower('adfCE')
select upper('asdf23')


select '    abc   '
select ltrim('  abc  ')
select rtrim('  abc  ') + '123'
select ltrim(rtrim('   abc   ')) + '123'


--
select left('张三',1)
--ip1551616414


select right('ip1551616414',len('ip1551616414')-2) + 1
--起始位置从1开始
select substring('ip1551616414',1,2)


--查询班级内所有学生的姓
select distinct left(sName,1) from student


--日期函数
select getdate()


select dateadd(day,3,getdate())


select dateadd(month,-1,getdate())


select dateadd(year,-1,getdate())


select dateadd(quarter,1,getdate());
select dateadd(week,1,getdate());




select year(getdate())
select month(getdate())
select day(getdate())


--当前月的销售记录
select * from xsjl 
where month(date) = month(getdate()) and year(date) = year(getdate())


--datediff
select datediff(day,getdate(),'2012-12-21')
select datediff(second,getdate(),'2012-12-21')


--求当月销售记录
select * from xsjl
where datediff(month,date,getdate())=0


--统计不同生日年份的学生个数
select count(*),year(sBirthday)
from student
group by year(sBirthday)


--求每个年龄有多少个学生
select DateDiff(year,sBirthday,getdate()),count(*) 
from student 
Group by DateDiff(year,sBirthday,getdate())


--datename datepart
select datename(year,getdate()) --字符串
select datepart(year,getdate()) --整数


--一年中的第几天
select datename(dayofyear,getdate())
--一年中的第几周
select datename(week,getdate())


select datename(weekday,getdate())
select datepart(weekday,getdate())






--类型转换
--问题
select '平均分数' + 123
select avg(english) from score
select * from student




select '平均分数' + cast(23.56 as varchar(5))
select cast(right(sNo,3) as int)+1 from student




--不4舍5入
select cast (89.6 as int)
--4舍5入
select cast (round(89.6,0) as int)


select cast(89.239345 as decimal(10,2))
select cast(avg(english) as decimal(10,2)) from score


--convert
select convert(int,89.63934)
select convert(decimal(10,2),89.64789)
select convert(decimal(10,2),avg(english)) from score




select convert(varchar(30),getdate(),20)
select convert(varchar(20),getdate(),21)
select convert(varchar(30),getdate(),23)


select convert(varchar(10),getdate(),108)


select convert(varchar(10),getdate(),20)
select sId, sName, sAge, sNo, convert(varchar(10),sBirthday,20), sClassId, sSex, convert(varchar(10),sIntime,108), sPhone
from student




--isnull
select * from score


select studentId,isnull(english,0)
from score


select avg(english) from score
select sum(english)/count(sId) from score


select cast(avg(isnull(english,0)) as decimal(10,2)) from score



练习

l创建一张表，记录电话呼叫员的工作流水，记录呼叫员编号、对方号码、通话开始时间、通话结束时间。建表、插数据等最后都自己写SQL语句。 l要求： •输出所有数据中通话时间最长的5条记录。orderby datediff •输出所有数据中拨打长途号码（对方号码以0开头）的总时长。like、sum •输出本月通话总时长最多的前三个呼叫员的编号。 • •输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)

CREATE TABLE [CallRecords]

(

  [Id][int]NOT NULL identity(1,1),

  [CallerNumber][nvarchar](50),--三位数字

  [TelNum][varchar](50),

  [StartDateTime][datetime]NULL,

  [EndDateTime][datetime]NULL  --结束时间要大于开始时间,默认当前时间

)

--主键约束

alter table [CallRecords]

add constraint PK_CallRecords    primary key(id)

--检查约束

alter table [CallRecords]

add constraint CK_CallRecords     check(CallerNumberlike '[0-9][0-9][0-9]')

alter table [CallRecords]

add constraint CK_CallRecords_EndDateTime    check(EndDateTime>StartDateTime)

--默认约束

alter table [CallRecords]

add constraint DF_CallRecords    default(getdate())    for   EndDateTime

INSERT [dbo].[CallRecords]([CallerNumber],[TelNum],[StartDateTime],[EndDateTime])VALUES ('001', '0208888888', CAST(0x00009DAF00A4CB80 AS DateTime),CAST(0x00009DAF00A62E94 ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber],[TelNum],[StartDateTime],[EndDateTime])VALUES ('001', '0208888888', CAST(0x00009DB000D63BC0 AS DateTime),CAST(0x00009DB000D68DC8 ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber],[TelNum],[StartDateTime],[EndDateTime])VALUES ('001', '89898989', CAST(0x00009DB000E85C60 AS DateTime),CAST(0x00009DB000E92F50 ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber],[TelNum],[StartDateTime],[EndDateTime])VALUES ('002', '98987676', CAST(0x00009DB2015BB7A0 AS DateTime),CAST(0x00009DB2015C4DA0 ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber],[TelNum],[StartDateTime],[EndDateTime])VALUES ('002', '02188839389', CAST(0x00009DA4014C9C70 AS DateTime),CAST(0x00009DA4014E0308 ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber],[TelNum],[StartDateTime],[EndDateTime])VALUES ('001', '767676766', CAST(0x00009DB400DAA0C0 AS DateTime),CAST(0x00009DB400DD5FE0 ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber],[TelNum],[StartDateTime],[EndDateTime])VALUES ('003', '0227864656', CAST(0x00009DB200B9AB40 AS DateTime),CAST(0x00009DB200B9FC1C ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber],[TelNum],[StartDateTime],[EndDateTime])VALUES ('003', '676765777', CAST(0x00009DB8014042B8 AS DateTime),CAST(0x00009DB80141804C ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber],[TelNum],[StartDateTime],[EndDateTime])VALUES ('001', '89977653', CAST(0x00009D9A00FB9898 AS DateTime),CAST(0x00009D9A00FE6118 ASDateTime));

INSERT [dbo].[CallRecords]([CallerNumber],[TelNum],[StartDateTime],[EndDateTime])VALUES ('004', '400400400', CAST(0x00009D9A00FB9898 AS DateTime),CAST(0x00009D9A00FE6118 ASDateTime));

--查询通话时间最长的条记录

select datediff(second,StartDateTime,EndDateTime)   from   CallRecords

select top 5 datediff(second,StartDateTime,EndDateTime),Id,CallerNumber,TelNum,StartDateTime,EndDateTime

from CallRecords

order bydatediff(second,StartDateTime,EndDateTime)    desc

--查询长途的通话总时长

select sum(datediff(second,StartDateTime,EndDateTime))  from  CallRecords

where TelNumlike '0%'

--查询本月通话总时长最多的前三个呼叫员的编号

select top 3 [CallerNumber],sum(datediff(ss,[StartDateTime],[EndDateTime]))

from   CallRecords

--where year(StartDateTime)= year(getdate())and month(StartDateTime)=month(getdate()) 

where datediff(month,[StartDateTime],'2010-07-1')= 0

group by [CallerNumber]

order by sum(datediff(ss,[StartDateTime],[EndDateTime]))desc

--查询本月拨打电话次数最多的前三个呼叫员的编号

select top 3 [CallerNumber],count(*) 

 from    CallRecords

where    datediff(month,[StartDateTae],'2010-07-1')= 0

group by [CallerNumber]

order by count(*) desc




标签：

相关文章