SQL常用函数-CSDN博客

*RIGHT()

select right('1234',2)
结果：34
反回字符右边的指定长度的字符

*LEN()

select LEN('2123') 
结果  4取长度

*REPLICATE()

按指定次数重复字符表达式。 

语法 

REPLICATE ( character_expression, integer_expression) 


参数 
character_expression 
字符数据型的字母数字表达式，或者可以隐式转换为 nvarchar 或 ntext 的其他数据类型的字母数字表达式。 

integer_expression 
可以隐式转换为 int 的表达式。如果 integer_expression 为负，将返回空字符串。 

返回值 
nvarchar 或 ntext

1 ：Select Replicate('abc',2) ----------------abcabc

2 ：Select Replicate（'abc',-2) ----------------null

3 ：Select Replicate('abc',0)--------------- 无

*QUOTE()

 QuoteName函数返回被特定字符括起来的字符串

例   Select QuoteName （'abc[ ]def','{')

     结果： {abc[ ]def}

      Select QuoteName （'abc[ ]def')

     结果： [abc[ ]def]

默认情况下是[]

*STUFF()

*CASE WHEN THEN END的用法

	查看修改密码和挂失结果
	SELECT cardid 卡号,curID 货币,savingName 储蓄种类,opendate 开户日期,openmoney 开户金额,balance 余额,pass 密码,
    	case IsReportLoss WHEN 1 THEN '挂失'  WHEN 0 THEN '未挂失' ELSE NULL end 是否挂失, 
   	customerName 客户姓名
	FROM CardInfo, Deposit, UserInfo
	WHERE CardInfo.savingID=Deposit.savingID and CardInfo.customerID = UserInfo.customerID

*DATEDIFF()的用法

	语法:DATEDIFF ( datepart , startdate , enddate )
	返回类型:int
	作用:用于返回起始日期和结束日期之间的时间差

*DATEPART()的用法

	例:select datepart(millisecond,'2012-8-13 12:00:12.123456')
	datepart用于返回日期的一部分
	yyyy,year,yy
	quarter,qq,q季
	month,mm,m
	day,dd,d几号
	dayofyear,dy,y一年中的第几天
	week第几个星期
	weekday星期几注意这里星期天是1,星期六是7
	hour
	minute
	second
	millisecond,ms毫秒,返回3位数字,返回123
	microsecond,mcs微妙,返回6位,返回123456
	nanosecond 返回9位,返回123456000

*DATENAME()的用法

	例:SELECT DATENAME(hour, '2007-06-01')
	和datepart差不多,weekday用datename(weekday,'2007-7-7')返回的是星期几

*把datetime转化为varchar

	1.120格式或者20格式,2012-12-06
	convert(char(10),a.unvalid_date,120)
	convert(char(10),a.unvalid_date,20)


	2.112格式 20120606
	convert(char(10),a.unvalid_date,112)


	3.111格式 2012/12/11
	convert(varchar(20),getdate(),111)


	4.将字符串转化为datetime类型
	CONVERT(datetime,'2012-01-01',120)
	convert(date,'2012-01-01',120)


	//参数说明
	char(10):转化为的类型
	a.unvalid_date:需要转化的日期类型
	112:格式参数

*默认日期 default(getdate()) 注意后面加的是 for [birthday]

*select isnull(a,0.0) 用于替换Null值,如果a为空的话则用后面的值替换

*查看所影响的行数:@@ROWCOUNT

* 前面补0

select RIGHT(REPLICATE('0',20)+CAST(user_id AS varchar(20)),20) AS 'user_id' from e_learning_record

*sqlserver中ddl等?

	1.DDL
	　	数据库模式定义语言DDL(Data Definition Language)
		常用的DDL语句:
		CREATE DATABASE
		CREATE TABLE
		ALTER TABLE
		DROP TABLE
		CREATE VIEW
		ALTER VIEW
		DROP VIEW
		TRUNCATE TABLE(删除表的内容 关于truancate和delete的区别:truncated不能使用rollback回滚)
		GRANT
		REVOKE
	2.DML
		DML = Data Manipulation Language，数据操纵语言
		INSERT 
		DELETE
		UPDATE
		SELECT
	3.DCL
		DCL=Data Control language,数据控制语言
		COMMIT
		ROLLBACK

*rollup

	SELECT a, b, c, SUM ( <expression> )
	FROM T
	GROUP BY ROLLUP (a,b,c) //效果和group by a,b,c with rollup一致
	会为 (a, b, c)、(a, b) 和 (a) 值的每个唯一组合生成一个带有小计的行。还将计算一个总计行。
	1.
	select orderId,score,SUM(score) from T group by orderId,score with rollup
	orderId score sum
	1	1.00	1.00
	1	2.00	2.00
	1	NULL	3.00
	2	1.10	1.10
	2	6.00	6.00
	2	NULL	7.10
	3	1.10	1.10
	3	7.00	7.00
	3	8.00	8.00
	3	NULL	16.10
	4	1.00	1.00
	4	NULL	1.00
	NULL	NULL	27.20
	//首先小计orderId,再总计全部的
	
	2.
	select id,orderId,score,SUM(score) from T group by id,orderId,score with rollup
	id  orderId  score sum
	1	1	2.00	2.00
	1	1	NULL	2.00
	1	NULL	NULL	2.00
	2	1	1.00	1.00
	2	1	NULL	1.00
	2	NULL	NULL	1.00
	3	2	6.00	6.00
	3	2	NULL	6.00
	3	NULL	NULL	6.00
	4	2	1.10	1.10
	4	2	NULL	1.10
	4	NULL	NULL	1.10
	5	3	8.00	8.00
	5	3	NULL	8.00
	5	NULL	NULL	8.00
	6	3	1.10	1.10
	6	3	NULL	1.10
	6	NULL	NULL	1.10
	7	3	7.00	7.00
	7	3	NULL	7.00
	7	NULL	NULL	7.00
	8	4	1.00	1.00
	8	4	NULL	1.00
	8	NULL	NULL	1.00
	NULL	NULL	NULL	27.20
	//首先小计id为1的,然后小计id,orderid,最后总计
	
	
	3.
	CREATE TABLE DEPART 
	(部门 char(10),员工 char(6),工资 int) 
	INSERT INTO DEPART SELECT 'A','ZHANG',100 
	INSERT INTO DEPART SELECT 'A','LI',200 
	INSERT INTO DEPART SELECT 'A','WANG',300 
	INSERT INTO DEPART SELECT 'A','ZHAO',400 
	INSERT INTO DEPART SELECT 'A','DUAN',500 
	INSERT INTO DEPART SELECT 'B','DUAN',600 
	INSERT INTO DEPART SELECT 'B','DUAN',700
	
		SELECT 部门,员工,SUM(工资)AS TOTAL 
		FROM DEPART 
		GROUP BY  部门,员工  WITH ROLLUP
		查询结果:
		A             DUAN       500 
		A             LI             200 
		A             WANG      300 
		A             ZHANG     100 
		A             ZHAO       400 
		A             NULL        1500 
		B             DUAN       1300 
		B             NULL       1300 
		NULL      	  NULL        2800
		//先小计了部门A的工资,再小计部门B的工资,最后总计
		
		等价于:
		SELECT 部门,员工,SUM(工资)AS TOTAL 
		FROM DEPART 
		GROUP BY 部门,员工 
		union 
		SELECT 部门,'NULL',SUM(工资)AS TOTAL 
		FROM DEPART 
		GROUP BY  部门 
		union 
		SELECT 'NULL','NULL',SUM(工资)AS TOTAL 
		FROM DEPART

*有如下表(LogonRecords)记录:

	ID		 Name		Email 				LastLogon
	100 		test4		test4@yahoo.cn		2007-11-25 16:31:26
	13 		test1		test1@yahoo.cn		2007-3-22 16:27:07
	19 		test1		test1@yahoo.cn		2007-10-25 14:13:46
	42		test1		test1@yahoo.cn		2007-11-20 14:20:10
	45		test2		test2@yahoo.cn		2007-4-25 14:17:39
	49		test2		test2@yahoo.cn		2007-5-25 14:22:36
	问题：	用一句sql语句查询出每个用户最近一次登录的记录
	注：每个用户只显示一条最近登录的记录
	结果:
	编号	姓名		邮箱				登录时间
	100		test4		test4@yahoo.cn		2007-11-25 16:31:26.000
	49		test2		test2@yahoo.cn		2007-05-25 14:22:36.000
	42		test1		test1@yahoo.cn		2007-11-20 14:20:10.000
	select * from LogonRecord as l
	where date in(select MAX(date) from LogonRecord as l2 group by name having l2.name=l.name )

*如何获取指定行数的sql语句

	1.比如获取3-6行的数据 m最大行 n最小行
	select top 4 * from NewsInfo where Id not in (select  top 2 Id from NewsInfo) 
	select top m-n+1 * from NewsInfo where Id not in (select top n-1 Id from NewsInfo)
	
	2.或者先按降序排列前20个,然后把20个倒转找到前10个,最后把这10个倒转
	select top 10 * from (
		select top 10 * from (
			select top 20 * from NewsInfo order by Id desc
		)as temp order by Id desc
	)as temp2 order by Id asc