数据库中函数
--选出A表中的两条数据，插入c表中
SELECT TOP 2 * INTO C FROM A
TRUNCATE TABLE B --清空表
--根据type从不同的表中选出想要的数据
select case when type=1 then sdealer.[name] else custom.[name] end from seale
left join custom on seale.fk_custom=custom.pkid
left join sdealer on seale.fk_custom=sdealer.pkid
--同上，运用子查询
select case when type=1 then (select [name] from sdealer where pkid=seale.fk_custom)
else (select [name] from custom where pkid=seale.fk_custom) end as CName from seale
--选出日期之间的差距为5的数据
select * from a where datediff(minute,'2012-01-02 11:30:00.230',getdate())>5
select getdate() AS 当前日期
SELECT TOP 2 * INTO C FROM A
TRUNCATE TABLE B --清空表
--根据type从不同的表中选出想要的数据
select case when type=1 then sdealer.[name] else custom.[name] end from seale
left join custom on seale.fk_custom=custom.pkid
left join sdealer on seale.fk_custom=sdealer.pkid
--同上，运用子查询
select case when type=1 then (select [name] from sdealer where pkid=seale.fk_custom)
else (select [name] from custom where pkid=seale.fk_custom) end as CName from seale
--选出日期之间的差距为5的数据
select * from a where datediff(minute,'2012-01-02 11:30:00.230',getdate())>5
select getdate() AS 当前日期
---获取本月的最后一天
select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
--时间相差的小时
SELECT DATEDIFF(hour,'2012-01-02 11:30:00.230',getdate())
--except除去重复的数据
select pkid from b except(select pkid from a)
--随机选出3条数据
select top 3 * from b order by newid()
--开始
drop table temp
select distinct qq into temp from b
select * from temp
--结束
--格式日期
select convert(nvarchar(20),getDate(),120)
--选出123在fasdf123sdfas中的开始位置，从第二位置开始
select charindex('123','fasdf123sdfas',2)
--倒叙
select reverse('hello')
--选出三个空格
select space(3)
--@@全局变量
select @@version
select @@error
--like匹配查询里面的内容是不区分大小写
SELECT * FROM A WHERE [name] LIKE '%B%'
SELECT DISTINCT ADDRESS , * FROM A ORDER BY PKID DESC
SELECT COUNT(DISTINCT ADDRESS) AS TOTALCOUNT FROM A
SELECT SUM(PKID)AS [SUM] FROM A
SELECT AVG(PKID) AS AVERAGE FROM A
SELECT ((SELECT SUM(PKID) FROM A)/(SELECT COUNT(*) FROM A)) AS AVERAGE
SELECT * FROM A ORDER BY PKID DESC
SELECT MAX(PHONE) AS MAXNUMBER FROM A
SELECT MIN(PKID) AS MINNUMBER FROM A
--日期函数
select getdate() AS 系统日期
select 当前日期 = convert(nvarchar(10),getdate(),120)
select 当前时间=convert(nvarchar(8),getdate(),114)
select dateadd(month,2,getdate()) AS 当前月份加2
select datediff(day,'2012-02-1','2012-02-2') AS 相差天数
select datepart(month,'2012-2-1') AS 取出月份
select 今天 = datename(weekday,'2012-2-1')
SELECT 今天 = DATENAME(DW,'2012-2-2')
select datepart(weekday,'2012-2-1') AS 星期几
select year('2012-2-1')
SELECT 今年第几周= DATENAME(WEEK,'2012-2-1'),
今天是周几=DATENAME(WEEKDAY,'2012-2-1')
--day of year == dy or y
SELECT 一年中的第几天 = DATEPART(DY,'2012-2-1')
SELECT CONVERT(NVARCHAR(20),GETDATE(),120)
SELECT 去掉空格和冒号= REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),GETDATE(),120),'-',''),' ',''),':','')
select replace('well12345 to you','12345','come') AS 替换后 --RESULT wellcome to you
SELECT STUFF('ADF',2,3,'QWE') AS 置换后 --结果 AQWE
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
SELECT DATEDIFF(M,0,GETDATE())
---其他函数
SELECT APP_NAME() AS 当前会话的应用程序
SELECT @@IDENTITY AS 最后插入的表示列
SELECT USER_NAME() AS 用户名
SELECT @@CONNECTIONS AS 连接的次数
SELECT CEILING(2.1) AS 大于次数 --RESULT 3
SELECT FLOOR(3.9) AS 小于此数 --RESULT 3
SELECT ROUND(3.52348701337346,4) AS 四舍五入 --RESULT 3.52350000000000
--str(a,b,c)转换数字为字符串。 a,是要转换的字符串。b是转换以后的长度，c是小数位数
SELECT STR(ROUND(3.34534523534,3),5,4) AS 转换为字符串 --RESULT 3.345
---通配符[],要匹配的字符
SELECT * FROM [LOGIN]
WHERE loginName LIKE'[a-w][a-w][a-w][a-w]'
-- 通配符[^],无需匹配的字符
SELECT * FROM [LOGIN]
--选择登录名以ad开头，且第三个字母不是n的所有用户
WHERE loginName LIKE 'ad[^n]%'
--时间相差的小时
SELECT DATEDIFF(hour,'2012-01-02 11:30:00.230',getdate())
--except除去重复的数据
select pkid from b except(select pkid from a)
--随机选出3条数据
select top 3 * from b order by newid()
--开始
drop table temp
select distinct qq into temp from b
select * from temp
--结束
--格式日期
select convert(nvarchar(20),getDate(),120)
--选出123在fasdf123sdfas中的开始位置，从第二位置开始
select charindex('123','fasdf123sdfas',2)
--倒叙
select reverse('hello')
--选出三个空格
select space(3)
--@@全局变量
select @@version
select @@error
--like匹配查询里面的内容是不区分大小写
SELECT * FROM A WHERE [name] LIKE '%B%'
SELECT DISTINCT ADDRESS , * FROM A ORDER BY PKID DESC
SELECT COUNT(DISTINCT ADDRESS) AS TOTALCOUNT FROM A
SELECT SUM(PKID)AS [SUM] FROM A
SELECT AVG(PKID) AS AVERAGE FROM A
SELECT ((SELECT SUM(PKID) FROM A)/(SELECT COUNT(*) FROM A)) AS AVERAGE
SELECT * FROM A ORDER BY PKID DESC
SELECT MAX(PHONE) AS MAXNUMBER FROM A
SELECT MIN(PKID) AS MINNUMBER FROM A
--日期函数
select getdate() AS 系统日期
select 当前日期 = convert(nvarchar(10),getdate(),120)
select 当前时间=convert(nvarchar(8),getdate(),114)
select dateadd(month,2,getdate()) AS 当前月份加2
select datediff(day,'2012-02-1','2012-02-2') AS 相差天数
select datepart(month,'2012-2-1') AS 取出月份
select 今天 = datename(weekday,'2012-2-1')
SELECT 今天 = DATENAME(DW,'2012-2-2')
select datepart(weekday,'2012-2-1') AS 星期几
select year('2012-2-1')
SELECT 今年第几周= DATENAME(WEEK,'2012-2-1'),
今天是周几=DATENAME(WEEKDAY,'2012-2-1')
--day of year == dy or y
SELECT 一年中的第几天 = DATEPART(DY,'2012-2-1')
SELECT CONVERT(NVARCHAR(20),GETDATE(),120)
SELECT 去掉空格和冒号= REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(20),GETDATE(),120),'-',''),' ',''),':','')
select replace('well12345 to you','12345','come') AS 替换后 --RESULT wellcome to you
SELECT STUFF('ADF',2,3,'QWE') AS 置换后 --结果 AQWE
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
SELECT DATEDIFF(M,0,GETDATE())
---其他函数
SELECT APP_NAME() AS 当前会话的应用程序
SELECT @@IDENTITY AS 最后插入的表示列
SELECT USER_NAME() AS 用户名
SELECT @@CONNECTIONS AS 连接的次数
SELECT CEILING(2.1) AS 大于次数 --RESULT 3
SELECT FLOOR(3.9) AS 小于此数 --RESULT 3
SELECT ROUND(3.52348701337346,4) AS 四舍五入 --RESULT 3.52350000000000
--str(a,b,c)转换数字为字符串。 a,是要转换的字符串。b是转换以后的长度，c是小数位数
SELECT STR(ROUND(3.34534523534,3),5,4) AS 转换为字符串 --RESULT 3.345
---通配符[],要匹配的字符
SELECT * FROM [LOGIN]
WHERE loginName LIKE'[a-w][a-w][a-w][a-w]'
-- 通配符[^],无需匹配的字符
SELECT * FROM [LOGIN]
--选择登录名以ad开头，且第三个字母不是n的所有用户
WHERE loginName LIKE 'ad[^n]%'
--@@CONNECTIONS 返回自上次启动以来尝试连接的次数，无论成功还是失败
SELECT GETDATE() AS 'DATE',
@@CONNECTIONS AS 'CONCOUNT'
---更改登录密码
ALTER LOGIN 登录名 WITH PASSWORD='更改的密码'
--选出数据库中所有的函数
SELECT definition, type
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id
AND type IN ('FN', 'IF', 'TF');SELECT RAND(12)
SELECT GETDATE() AS 'DATE',
@@CONNECTIONS AS 'CONCOUNT'
---更改登录密码
ALTER LOGIN 登录名 WITH PASSWORD='更改的密码'
--选出数据库中所有的函数
SELECT definition, type
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id
AND type IN ('FN', 'IF', 'TF');SELECT RAND(12)
