-- 选出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 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 GETDATE() AS 'DATE',@@CONNECTIONS AS 'CONCOUNT'---更改登录密码ALTER LOGIN 登录名 WITH PASSWORD='更改的密码'--选出数据库中所有的函数SELECT definition, type FROM sys.sql_modules AS mJOIN sys.objects AS o ON m.object_id = o.object_id AND type IN ('FN', 'IF', 'TF');SELECT RAND( 12)