thinkphp中的内置操作数据库与mysql中的函数汇总

8.4.4 Model类
getModelName() 获取当前Model的名称
getTableName() 获取当前Model的数据表名称
switchModel(type,vars=array()) 动态切换模型
table() 设置当前操作的数据表
field() 设置要查询的数据字段
where() 设置查询或者操作条件
data(data) 设置数据对象
order(order) 设置排序
limit(limit) 查询限制
page(page) 查询分页
join(join) 进行JOIN查询
having(having) 进行having查询
group(group) 进行group查询
lock(lock) 查询锁定
distinct(distinct) 唯一性查询
count(field) 记录统计
sum(field) 总数查询
min(field) 最小值查询
max(field) 最大值查询

avg(field) 平均值查询
_initialize() 模型初始化方法
_facade(data) 对保存到数据库的数据进行处理
_before_write(&data) 写入数据前的回调方法 包括新增和更新
add(data='',options=array()) 新增数据
_before_insert(&data,options) 写入数据前的回调方法
_after_insert(data,options) 写入数据后的回调方法
selectAdd(fields='',table='',options=array()) 通过Select方式添加记录
save(data='',options=array()) 更新数据到数据库
_before_update(&data,options) 更新数据前的回调方法
_after_update(data,options) 更新成功后的回调方法
delete(options=array()) 删除数据
_after_delete(data,options) 删除成功后的回调方法
select(options=array()) 查询数据集
_after_select(&resultSet,options) 查询成功后的回调方法
findAll(options=array()) select方法的别名
_options_filter(&options) 表达式过滤回调方法
find(options=array()) 查询数据
_after_find(&result,options) 查询成功的回调方法
setField(field,value,condition='') 设置记录的某个字段值
setInc(field,condition='',step=1) 字段值增长
setDec(field,condition='',step=1) 字段值减少
getField(field,condition='',sepa=' ') 获取某个字段值
create(data='',type='') 创建数据对象

autoCheckToken(data) 表单令牌验证

query(sql) 执行原生SQL查询

execute(sql='') 执行原生SQL操作

startTrans() 启动事务

commit() 提交事务

rollback() 事务回滚

getError() 获取模型的错误信息

getDbError() 获取数据库的错误信息

getLastInsID() 获取最后执行的SQL语句

getPk() 获取主键名称

getDbFields() 获取数据表的字段信息

regex(value,rule) 使用正则验证数据

setProperty(name,value) 设置模型的属性值

2.1版新增方法:

db(linkNum,config='') 切换当前数据库连接

高级模型类AdvModel

topN(count,options=array()) 查询满足条件的前N个记录

getN(position=0,options=array()) 查询符合条件的第N条记录

0 表示第一条记录 -1 表示最后一条记录

first(options=array()) 获取满足条件的第一条记录

last(options=array()) 获取满足条件的最后一条记录

returnResult(data,type='') 返回指定的数据类型

setLazyInc(field,condition='',step=1,lazyTime=0) 字段值延迟增长

setLazyDec(field,condition='',step=1,lazyTime=0) 字段值延迟减少

addConnect(config,linkNum=NULL) 增加数据库连接

delConnect(linkNum) 删除数据库连接

closeConnect(linkNum) 关闭数据库连接

switchConnect(linkNum,name='') 切换数据库连接

patchQuery(sql=array()) 批处理执行SQL语句

getPartitionTableName(data=array()) 得到分表的的数据表名

 

 

——————————————————————————————————————————————————————————————————————————

 

《MySQL函数大全》

[color=red]字符串函数[/color]
ASCII(str) 
　返回字符串str的第一个字符的ASCII值(str是空串时返回0)
mysql> select ASCII('2');
　　-> 50
mysql> select ASCII(2);
　　-> 50
mysql> select ASCII('dete');
　　-> 100

ORD(str) 
　如果字符串str句首是单字节返回与ASCII()函数返回的相同值。
　如果是一个多字节字符,以格式返回((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]
mysql> select ORD('2');
　　-> 50
 
CONV(N,from_base,to_base) 
　对数字N进制转换,并转换为字串返回(任何参数为NULL时返回NULL,进制范围为2-36进制,当to_base是负数时N作为有符号数否则作无符号数,CONV以64位点精度工作)
mysql> select CONV("a",16,2);
　　-> '1010'
mysql> select CONV("6E",18,8);
　　-> '172'
mysql> select CONV(-17,10,-18);
　　-> '-H'
mysql> select CONV(10+"10"+'10'+0xa,10,10);
　　-> '40'
 
BIN(N) 
　把N转为二进制值并以字串返回(N是BIGINT数字,等价于CONV(N,10,2))
mysql> select BIN(12);
　　-> '1100'

OCT(N) 
　把N转为八进制值并以字串返回(N是BIGINT数字,等价于CONV(N,10,8))
mysql> select OCT(12);
　　-> '14'
 
HEX(N) 
　把N转为十六进制并以字串返回(N是BIGINT数字,等价于CONV(N,10,16))
mysql> select HEX(255);
　　-> 'FF'
 
CHAR(N,...) 
　返回由参数N,...对应的ASCII代码字符组成的一个字串(参数是N,...是数字序列,NULL值被跳过) 
mysql> select CHAR(77,121,83,81,'76');
　　-> 'MySQL'
mysql> select CHAR(77,77.3,'77.3');
　　-> 'MMM'
 
CONCAT(str1,str2,...) 
　把参数连成一个长字符串并返回(任何参数是NULL时返回NULL) 
mysql> select CONCAT('My', 'S', 'QL');
　　-> 'MySQL'
mysql> select CONCAT('My', NULL, 'QL');
　　-> NULL
mysql> select CONCAT(14.3);
　　-> '14.3'

LENGTH(str) 
OCTET_LENGTH(str)
CHAR_LENGTH(str)
CHARACTER_LENGTH(str)
　返回字符串str的长度(对于多字节字符CHAR_LENGTH仅计算一次)
mysql> select LENGTH('text');
　　-> 4
mysql> select OCTET_LENGTH('text');
　　-> 4

LOCATE(substr,str) 
POSITION(substr IN str) 
　返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)
mysql> select LOCATE('bar', 'foobarbar');
　　-> 4
mysql> select LOCATE('xbar', 'foobar');
　　-> 0
  
LOCATE(substr,str,pos)
　返回字符串substr在字符串str的第pos个位置起第一次出现的位置(str不包含substr时返回0)
mysql> select LOCATE('bar', 'foobarbar',5);
　　-> 7

INSTR(str,substr) 
　返回字符串substr在字符串str第一次出现的位置(str不包含substr时返回0)
mysql> select INSTR('foobarbar', 'bar');
　　-> 4
mysql> select INSTR('xbar', 'foobar');
　　-> 0

mysql中INSTR函数的用法

INSTR(字段名, 字符串) 这个函数返回字符串在某一个字段的内容中的位置, 没有找到字符串返回0，否则返回位置（从1开始） SELECT * FROM tblTopic ORDER BY INSTR( topicTitle, 'ha' ) > 0 DESC SELECT INSTR( topicTitle, 'ha' ) FROM tblTopic

mysql中使用instr配合IN排序

将instr结果作为一列，按其排序

select id,1 from world_guide where id = 32
union
select * from
(select id, instr('30,35,31,',id+',') as d from
world_blog where id in (30,35,31) order by d) as t;

输出

+----+---+
| id | 1 |
+----+---+
| 32 | 1 |
| 30 | 1 |
| 35 | 4 |
| 31 | 7 |
+----+---+
4 rows in set, 6 warnings (0.02 sec)

LPAD(str,len,padstr) 
　用字符串padstr填补str左端直到字串长度为len并返回
mysql> select LPAD('hi',4,'??');
　　-> '??hi'
 
RPAD(str,len,padstr) 
　用字符串padstr填补str右端直到字串长度为len并返回
mysql> select RPAD('hi',5,'?');
　　-> 'hi???'

LEFT(str,len) 
　返回字符串str的左端len个字符
mysql> select LEFT('foobarbar', 5);
　　-> 'fooba'

RIGHT(str,len) 
　返回字符串str的右端len个字符 
mysql> select RIGHT('foobarbar', 4);
　　-> 'rbar'

SUBSTRING(str,pos,len) 
SUBSTRING(str FROM pos FOR len) 
MID(str,pos,len) 
　返回字符串str的位置pos起len个字符(使用FROM的丑陋语法是ANSI SQL92标准)
mysql> select SUBSTRING('Quadratically',5,6);
　　-> 'ratica'

SUBSTRING(str,pos) 
SUBSTRING(str FROM pos) 
　返回字符串str的位置pos起的一个子串
mysql> select SUBSTRING('Quadratically',5);
　　-> 'ratically'
mysql> select SUBSTRING('foobarbar' FROM 4);
　　-> 'barbar'

SUBSTRING_INDEX(str,delim,count) 
　返回从字符串str的第count个出现的分隔符delim之后的子串(count为正数时返回左端,否则返回右端子串)
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', 2);
　　-> 'www.mysql'
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', -2);
　　-> 'mysql.com'

LTRIM(str) 
　返回删除了左空格的字符串str
mysql> select LTRIM('  barbar');
　　-> 'barbar'

RTRIM(str) 
　返回删除了右空格的字符串str
mysql> select RTRIM('barbar   ');
　　-> 'barbar'

TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) 
　返回前缀或后缀remstr被删除了的字符串str(位置参数默认BOTH,remstr默认值为空格)
mysql> select TRIM('  bar   ');
　　-> 'bar'
mysql> select TRIM(LEADING 'x' FROM 'xxxbarxxx');
　　-> 'barxxx'
mysql> select TRIM(BOTH 'x' FROM 'xxxbarxxx');
　　-> 'bar'
mysql> select TRIM(TRAILING 'xyz' FROM 'barxxyz');
　　-> 'barx'

SOUNDEX(str) 
　返回str的一个同音字符串(听起来“大致相同”字符串有相同的同音字符串,非数字字母字符被忽略,在A-Z外的字母被当作元音)
mysql> select SOUNDEX('Hello');
　　-> 'H400'
mysql> select SOUNDEX('Quadratically');
　　-> 'Q36324'
 
SPACE(N) 
　返回由N个空格字符组成的一个字符串
mysql> select SPACE(6);
　　-> '      '
 
REPLACE(str,from_str,to_str) 
　用字符串to_str替换字符串str中的子串from_str并返回
mysql> select REPLACE('www.mysql.com', 'w', 'Ww');
　　-> 'WwWwWw.mysql.com'

REPEAT(str,count) 
　返回由count个字符串str连成的一个字符串(任何参数为NULL时返回NULL,count<=0时返回一个空字符串)
mysql> select REPEAT('MySQL', 3);
　　-> 'MySQLMySQLMySQL'
 
REVERSE(str) 
　颠倒字符串str的字符顺序并返回
mysql> select REVERSE('abc');
　　-> 'cba'

INSERT(str,pos,len,newstr) 
　把字符串str由位置pos起len个字符长的子串替换为字符串newstr并返回
mysql> select INSERT('Quadratic', 3, 4, 'What');
　　-> 'QuWhattic'

ELT(N,str1,str2,str3,...) 
　返回第N个字符串(N小于1或大于参数个数返回NULL)
mysql> select ELT(1, 'ej', 'Heja', 'hej', 'foo');
　　-> 'ej'
mysql> select ELT(4, 'ej', 'Heja', 'hej', 'foo');
　　-> 'foo'

FIELD(str,str1,str2,str3,...) 
　返回str等于其后的第N个字符串的序号(如果str没找到返回0)
mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
　　-> 2
mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
　　-> 0

FIND_IN_SET(str,strlist) 
　返回str在字符串集strlist中的序号(任何参数是NULL则返回NULL,如果str没找到返回0,参数1包含","时工作异常)
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
　　-> 2
 
MAKE_SET(bits,str1,str2,...)
　把参数1的数字转为二进制,假如某个位置的二进制位等于1,对应位置的字串选入字串集并返回(NULL串不添加到结果中)
mysql> SELECT MAKE_SET(1,'a','b','c');
　　-> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
　　-> 'hello,world'
mysql> SELECT MAKE_SET(0,'a','b','c');
　　-> ''

EXPORT_SET(bits,on,off,[separator,[number_of_bits]]) 
　按bits排列字符串集,只有当位等于1时插入字串on,否则插入off(separator默认值",",number_of_bits参数使用时长度不足补0而过长截断)
mysql> select EXPORT_SET(5,'Y','N',',',4)
　　-> Y,N,Y,N

LCASE(str)
LOWER(str) 
　返回小写的字符串str
mysql> select LCASE('QUADRATICALLY');
　　-> 'quadratically'
 
UCASE(str) 
UPPER(str) 
　返回大写的字符串str
mysql> select UCASE('quadratically');
　　-> 'QUADRATICALLY'

LOAD_FILE(file_name) 
　读入文件并且作为一个字符串返回文件内容(文件无法找到,路径不完整,没有权限,长度大于max_allowed_packet会返回NULL)
mysql> UPDATE table_name SET blob_column=LOAD_FILE("/tmp/picture") WHERE id=1;

[color=red]数学函数[/color]
ABS(N)
　返回N的绝对值
mysql> select ABS(2);  
　　-> 2  
mysql> select ABS(-32);  
　　-> 32  
 
SIGN(N)
　返回参数的符号(为-1、0或1)
mysql> select SIGN(-32);  
　　-> -1  
mysql> select SIGN(0);  
　　-> 0  
mysql> select SIGN(234);  
　　-> 1

MOD(N,M)  
　取模运算,返回N被M除的余数(同%操作符)  
mysql> select MOD(234, 10);  
　　-> 4  
mysql> select 234 % 10;  
　　-> 4  
mysql> select MOD(29,9);  
　　-> 2

FLOOR(N)
　返回不大于N的最大整数值
mysql> select FLOOR(1.23);  
　　-> 1  
mysql> select FLOOR(-1.23);  
　　-> -2

CEILING(N)
　返回不小于N的最小整数值
mysql> select CEILING(1.23);  
　　-> 2  
mysql> select CEILING(-1.23);  
　　-> -1

ROUND(N,D)
　返回N的四舍五入值,保留D位小数(D的默认值为0)
mysql> select ROUND(-1.23);  
　　-> -1  
mysql> select ROUND(-1.58);  
　　-> -2  
mysql> select ROUND(1.58);  
　　-> 2  
mysql> select ROUND(1.298, 1);  
　　-> 1.3  
mysql> select ROUND(1.298, 0);  
　　-> 1

EXP(N)
　返回值e的N次方(自然对数的底)
mysql> select EXP(2);  
　　-> 7.389056  
mysql> select EXP(-2);  
　　-> 0.135335

LOG(N)
　返回N的自然对数
mysql> select LOG(2);  
　　-> 0.693147  
mysql> select LOG(-2);  
　　-> NULL

LOG10(N)
　返回N以10为底的对数
mysql> select LOG10(2);  
　　-> 0.301030  
mysql> select LOG10(100);  
　　-> 2.000000  
mysql> select LOG10(-100);  
　　-> NULL

POW(X,Y)  
POWER(X,Y)  
　返回值X的Y次幂
mysql> select POW(2,2);  
　　-> 4.000000  
mysql> select POW(2,-2);  
　　-> 0.250000

SQRT(N)
　返回非负数N的平方根
mysql> select SQRT(4);  
　　-> 2.000000  
mysql> select SQRT(20);  
　　-> 4.472136

PI()  
　返回圆周率 
mysql> select PI();  
　　-> 3.141593

COS(N)
　返回N的余弦值
mysql> select COS(PI());
　　-> -1.000000

SIN(N)
　返回N的正弦值 
mysql> select SIN(PI());  
　　-> 0.000000

TAN(N)
　返回N的正切值
mysql> select TAN(PI()+1);  
　　-> 1.557408

ACOS(N)
　返回N反余弦(N是余弦值,在-1到1的范围,否则返回NULL)
mysql> select ACOS(1);  
　　-> 0.000000  
mysql> select ACOS(1.0001);  
　　-> NULL  
mysql> select ACOS(0);  
　　-> 1.570796

ASIN(N)
　返回N反正弦值
mysql> select ASIN(0.2);  
　　-> 0.201358  
mysql> select ASIN('foo');  
　　-> 0.000000

ATAN(N)
　返回N的反正切值
mysql> select ATAN(2);  
　　-> 1.107149  
mysql> select ATAN(-2);  
　　-> -1.107149  
ATAN2(X,Y)  
　返回2个变量X和Y的反正切(类似Y/X的反正切,符号决定象限)
mysql> select ATAN(-2,2);  
　　-> -0.785398  
mysql> select ATAN(PI(),0);  
　　-> 1.570796

COT(N)
　返回X的余切
mysql> select COT(12);  
　　-> -1.57267341  
mysql> select COT(0);  
　　-> NULL

RAND()
RAND(N)  
　返回在范围0到1.0内的随机浮点值(可以使用数字N作为初始值)
mysql> select RAND();  
　　-> 0.5925  
mysql> select RAND(20);  
　　-> 0.1811  
mysql> select RAND(20);  
　　-> 0.1811  
mysql> select RAND();  
　　-> 0.2079  
mysql> select RAND();  
　　-> 0.7888

DEGREES(N)
　把N从弧度变换为角度并返回
mysql> select DEGREES(PI());  
　　-> 180.000000

RADIANS(N)
　把N从角度变换为弧度并返回 
mysql> select RADIANS(90);  
　　-> 1.570796

TRUNCATE(N,D)  
　保留数字N的D位小数并返回
mysql> select TRUNCATE(1.223,1);  
　　-> 1.2  
mysql> select TRUNCATE(1.999,1);  
　　-> 1.9  
mysql> select TRUNCATE(1.999,0);  
　　-> 1

LEAST(X,Y,...)  
　返回最小值(如果返回值被用在整数(实数或大小敏感字串)上下文或所有参数都是整数(实数或大小敏感字串)则他们作为整数(实数或大小敏感字串)比较,否则按忽略大小写的字符串被比较)
mysql> select LEAST(2,0);  
　　-> 0  
mysql> select LEAST(34.0,3.0,5.0,767.0);  
　　-> 3.0  
mysql> select LEAST("B","A","C");  
　　-> "A"

GREATEST(X,Y,...)  
　返回最大值(其余同LEAST())
mysql> select GREATEST(2,0);  
　　-> 2  
mysql> select GREATEST(34.0,3.0,5.0,767.0);  
　　-> 767.0  
mysql> select GREATEST("B","A","C");  
　　-> "C"

[color=red]时期时间函数[/color]

DAYOFWEEK(date)  
　返回日期date是星期几(1=星期天,2=星期一,……7=星期六,ODBC标准)
mysql> select DAYOFWEEK('1998-02-03');  
　　-> 3

WEEKDAY(date)  
　返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。  
mysql> select WEEKDAY('1997-10-04 22:23:00');  
　　-> 5  
mysql> select WEEKDAY('1997-11-05');  
　　-> 2

DAYOFMONTH(date)  
　返回date是一月中的第几日(在1到31范围内)  
mysql> select DAYOFMONTH('1998-02-03');  
　　-> 3

DAYOFYEAR(date)  
　返回date是一年中的第几日(在1到366范围内)  
mysql> select DAYOFYEAR('1998-02-03');  
　　-> 34

MONTH(date)  
　返回date中的月份数值  
mysql> select MONTH('1998-02-03');  
　　-> 2

DAYNAME(date)  
　返回date是星期几(按英文名返回)
mysql> select DAYNAME("1998-02-05");  
　　-> 'Thursday'

MONTHNAME(date)  
　返回date是几月(按英文名返回)
mysql> select MONTHNAME("1998-02-05");  
　　-> 'February'

QUARTER(date)  
　返回date是一年的第几个季度  
mysql> select QUARTER('98-04-01');  
　　-> 2

WEEK(date,first) 
　返回date是一年的第几周(first默认值0,first取值1表示周一是周的开始,0从周日开始)
mysql> select WEEK('1998-02-20');  
　　-> 7  
mysql> select WEEK('1998-02-20',0);  
　　-> 7  
mysql> select WEEK('1998-02-20',1);  
　　-> 8

YEAR(date)  
　返回date的年份(范围在1000到9999)  
mysql> select YEAR('98-02-03');  
　　-> 1998

HOUR(time)  
　返回time的小时数(范围是0到23) 
mysql> select HOUR('10:05:03');  
　　-> 10

MINUTE(time)  
　返回time的分钟数(范围是0到59)  
mysql> select MINUTE('98-02-03 10:05:03');  
　　-> 5

SECOND(time)  
　返回time的秒数(范围是0到59) 
mysql> select SECOND('10:05:03');  
　　-> 3

PERIOD_ADD(P,N)  
　增加N个月到时期P并返回(P的格式YYMM或YYYYMM)  
mysql> select PERIOD_ADD(9801,2);  
　　-> 199803

PERIOD_DIFF(P1,P2)  
　返回在时期P1和P2之间月数(P1和P2的格式YYMM或YYYYMM)
mysql> select PERIOD_DIFF(9802,199703);  
　　-> 11

DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)  
ADDDATE(date,INTERVAL expr type)  
SUBDATE(date,INTERVAL expr type)
　对日期时间进行加减法运算
　(ADDDATE()和SUBDATE()是DATE_ADD()和DATE_SUB()的同义词,也可以用运算符+和-而不是函数
　date是一个DATETIME或DATE值,expr对date进行加减法的一个表达式字符串type指明表达式expr应该如何被解释
　[type值 含义 期望的expr格式]:
　SECOND 秒 SECONDS  
　MINUTE 分钟 MINUTES  
　HOUR 时间 HOURS  
　DAY 天 DAYS  
　MONTH 月 MONTHS  
　YEAR 年 YEARS  
　MINUTE_SECOND 分钟和秒 "MINUTES:SECONDS"  
　HOUR_MINUTE 小时和分钟 "HOURS:MINUTES"  
　DAY_HOUR 天和小时 "DAYS HOURS"  
　YEAR_MONTH 年和月 "YEARS-MONTHS"  
　HOUR_SECOND 小时, 分钟， "HOURS:MINUTES:SECONDS"  
　DAY_MINUTE 天, 小时, 分钟 "DAYS HOURS:MINUTES"  
　DAY_SECOND 天, 小时, 分钟, 秒 "DAYS HOURS:MINUTES:SECONDS"
　expr中允许任何标点做分隔符,如果所有是DATE值时结果是一个DATE值,否则结果是一个DATETIME值)
　如果type关键词不完整,则MySQL从右端取值,DAY_SECOND因为缺少小时分钟等于MINUTE_SECOND)
　如果增加MONTH、YEAR_MONTH或YEAR,天数大于结果月份的最大天数则使用最大天数)  
mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;  
　　-> 1998-01-01 00:00:00  
mysql> SELECT INTERVAL 1 DAY + "1997-12-31";  
　　-> 1998-01-01  
mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;  
　　-> 1997-12-31 23:59:59  
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 SECOND);  
　　-> 1998-01-01 00:00:00  
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 DAY);  
　　-> 1998-01-01 23:59:59  
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND);  
　　-> 1998-01-01 00:01:00  
mysql> SELECT DATE_SUB("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND);  
　　-> 1997-12-30 22:58:59  
mysql> SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1 10" DAY_HOUR);
　　-> 1997-12-30 14:00:00  
mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);  
　　-> 1997-12-02  
mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");  
　　-> 1999  
mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");  
　　-> 199907  
mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");  
　　-> 20102

TO_DAYS(date)  
　返回日期date是西元0年至今多少天(不计算1582年以前)
mysql> select TO_DAYS(950501);  
　　-> 728779  
mysql> select TO_DAYS('1997-10-07');  
　　-> 729669

FROM_DAYS(N)  
　给出西元0年至今多少天返回DATE值(不计算1582年以前) 
mysql> select FROM_DAYS(729669);  
　　-> '1997-10-07'

DATE_FORMAT(date,format)  
　根据format字符串格式化date值
　(在format字符串中可用标志符:
　%M 月名字(January……December)  
　%W 星期名字(Sunday……Saturday)  
　%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）  
　%Y 年, 数字, 4 位  
　%y 年, 数字, 2 位  
　%a 缩写的星期名字(Sun……Sat)  
　%d 月份中的天数, 数字(00……31)  
　%e 月份中的天数, 数字(0……31)  
　%m 月, 数字(01……12)  
　%c 月, 数字(1……12)  
　%b 缩写的月份名字(Jan……Dec)  
　%j 一年中的天数(001……366)  
　%H 小时(00……23)  
　%k 小时(0……23)  
　%h 小时(01……12)  
　%I 小时(01……12)  
　%l 小时(1……12)  
　%i 分钟, 数字(00……59)  
　%r 时间,12 小时(hh:mm:ss [AP]M)  
　%T 时间,24 小时(hh:mm:ss)  
　%S 秒(00……59)  
　%s 秒(00……59)  
　%p AM或PM  
　%w 一个星期中的天数(0=Sunday ……6=Saturday ）  
　%U 星期(0……52), 这里星期天是星期的第一天  
　%u 星期(0……52), 这里星期一是星期的第一天  
　%% 字符% )
mysql> select DATE_FORMAT('1997-10-04 22:23:00','%W %M %Y');  
　　-> 'Saturday October 1997'  
mysql> select DATE_FORMAT('1997-10-04 22:23:00','%H:%i:%s');  
　　-> '22:23:00'  
mysql> select DATE_FORMAT('1997-10-04 22:23:00','%D %y %a %d %m %b %j');  
　　-> '4th 97 Sat 04 10 Oct 277'  
mysql> select DATE_FORMAT('1997-10-04 22:23:00','%H %k %I %r %T %S %w');  
　　-> '22 22 10 10:23:00 PM 22:23:00 00 6'

TIME_FORMAT(time,format)
　和DATE_FORMAT()类似,但TIME_FORMAT只处理小时、分钟和秒(其余符号产生一个NULL值或0)

CURDATE()   
CURRENT_DATE()
　以'YYYY-MM-DD'或YYYYMMDD格式返回当前日期值(根据返回值所处上下文是字符串或数字)  
mysql> select CURDATE();  
　　-> '1997-12-15'  
mysql> select CURDATE() + 0;  
　　-> 19971215

CURTIME()  
CURRENT_TIME()
　以'HH:MM:SS'或HHMMSS格式返回当前时间值(根据返回值所处上下文是字符串或数字)    
mysql> select CURTIME();  
　　-> '23:50:26'  
mysql> select CURTIME() + 0;  
　　-> 235026

NOW()  
SYSDATE()  
CURRENT_TIMESTAMP()
　以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回当前日期时间(根据返回值所处上下文是字符串或数字)   
mysql> select NOW();  
　　-> '1997-12-15 23:50:26'  
mysql> select NOW() + 0;  
　　-> 19971215235026

UNIX_TIMESTAMP()  
UNIX_TIMESTAMP(date)  
　返回一个Unix时间戳(从'1970-01-01 00:00:00'GMT开始的秒数,date默认值为当前时间)
mysql> select UNIX_TIMESTAMP();  
　　-> 882226357  
mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');  
　　-> 875996580

FROM_UNIXTIME(unix_timestamp)  
　以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回时间戳的值(根据返回值所处上下文是字符串或数字)   
mysql> select FROM_UNIXTIME(875996580);  
　　-> '1997-10-04 22:23:00'  
mysql> select FROM_UNIXTIME(875996580) + 0;  
　　-> 19971004222300

FROM_UNIXTIME(unix_timestamp,format)  
　以format字符串格式返回时间戳的值
mysql> select FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');  
　　-> '1997 23rd December 03:43:30 x'

SEC_TO_TIME(seconds)  
　以'HH:MM:SS'或HHMMSS格式返回秒数转成的TIME值(根据返回值所处上下文是字符串或数字)   
mysql> select SEC_TO_TIME(2378);  
　　-> '00:39:38'  
mysql> select SEC_TO_TIME(2378) + 0;  
　　-> 3938

TIME_TO_SEC(time)  
　返回time值有多少秒  
mysql> select TIME_TO_SEC('22:23:00');  
　　-> 80580  
mysql> select TIME_TO_SEC('00:39:38');  
　　-> 2378

《SQL函数大全》

SQL Server字符串处理函数大全

select语句中只能使用sql函数对字段进行操作（链接sql server），

select 字段1 from 表1 where 字段1.IndexOf("云")=1;
这条语句不对的原因是indexof（）函数不是sql函数，改成sql对应的函数就可以了。
left（）是sql函数。
select 字段1 from 表1 where charindex（'云',字段1）=1; 字符串函数对二进制数据、字符串和表达式执行不同的运算。此类函数作用于CHAR、VARCHAR、 BINARY、 和VARBINARY 数据类型以及可以隐式转换为CHAR 或VARCHAR的数据类型。可以在SELECT 语句的SELECT 和WHERE 子句以及表达式中使用字符串函数。
常用的字符串函数有：

一、字符转换函数
1、ASCII()
返回字符表达式最左端字符的ASCII 码值。在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用，否则会出错。
2、CHAR()
将ASCII 码转换为字符。如果没有输入0 ~ 255 之间的ASCII 码值，CHAR（） 返回NULL 。
3、LOWER()和UPPER()
LOWER()将字符串全部转为小写;UPPER()将字符串全部转为大写。
4、STR()
把数值型数据转换为字符型数据。
STR (<float_expression>[，length[， <decimal>]])
length 指定返回的字符串的长度，decimal 指定返回的小数位数。如果没有指定长度，缺省的length 值为10， decimal 缺省值为0。
当length 或者decimal 为负值时，返回NULL;
当length 小于小数点左边（包括符号位）的位数时，返回length 个*;
先服从length ，再取decimal ;
当返回的字符串位数小于length ，左边补足空格。
二、去空格函数
1、LTRIM() 把字符串头部的空格去掉。

2、RTRIM() 把字符串尾部的空格去掉。

三、取子串函数
1、left() 
LEFT (<character_expression>， <integer_expression>)
返回character_expression 左起 integer_expression 个字符。

2、RIGHT() 
RIGHT (<character_expression>， <integer_expression>)
返回character_expression 右起 integer_expression 个字符。

3、SUBSTRING()
SUBSTRING (<expression>， <starting_ position>， length)
返回从字符串左边第starting_ position 个字符起length个字符的部分。

四、字符串比较函数
1、CHARINDEX()
返回字符串中某个指定的子串出现的开始位置。
CHARINDEX (<’substring_expression’>， <expression>)
其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式。如果没有发现子串，则返回0 值。
此函数不能用于TEXT 和IMAGE 数据类型。
2、PATINDEX()
返回字符串中某个指定的子串出现的开始位置。
PATINDEX (<’%substring _expression%’>， <column_ name>)其中子串表达式前后必须有百分号“%”否则返回值为0。
与CHARINDEX 函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于CHAR、 VARCHAR 和TEXT 数据类型。

五、字符串操作函数
1、QUOTENAME()
返回被特定字符括起来的字符串。
QUOTENAME (<’character_expression’>[， quote_ character]) 其中quote_ character 标明括字符串所用的字符，缺省值为“[]”。
2、REPLICATE()
返回一个重复character_expression 指定次数的字符串。
REPLICATE (character_expression integer_expression) 如果integer_expression 值为负值，则返回NULL 。

3、REVERSE()
将指定的字符串的字符排列顺序颠倒。
REVERSE (<character_expression>) 其中character_expression 可以是字符串、常数或一个列的值。

4、REPLACE()
返回被替换了指定子串的字符串。
REPLACE (<string_expression1>， <string_expression2>， <string_expression3>) 用string_expression3 替换在string_expression1 中的子串string_expression2。

4、SPACE()
返回一个有指定长度的空白字符串。
SPACE (<integer_expression>) 如果integer_expression 值为负值，则返回NULL 。

5、STUFF()
用另一子串替换字符串指定位置、长度的子串。
STUFF (<character_expression1>， <start_ position>， <length>，<character_expression2>)
如果起始位置为负或长度值为负，或者起始位置大于character_expression1 的长度，则返回NULL 值。
如果length 长度大于character_expression1 中 start_ position 以右的长度，则character_expression1 只保留首字符。
六、数据类型转换函数
1、CAST()
CAST (<expression> AS <data_ type>[ length ])

2、CONVERT()
CONVERT (<data_ type>[ length ]， <expression> [， style])

1）data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。
2）length用于指定数据的长度，缺省值为30。
3）把CHAR或VARCHAR类型转换为诸如INT或SAMLLINT这样的INTEGER类型、结果必须是带正号或负号的数值。
4）TEXT类型到CHAR或VARCHAR类型转换最多为8000个字符，即CHAR或VARCHAR数据类型是最大长度。
5）IMAGE类型存储的数据转换到BINARY或VARBINARY类型，最多为8000个字符。
6）把整数值转换为MONEY或SMALLMONEY类型，按定义的国家的货币单位来处理，如人民币、美元、英镑等。
7）BIT类型的转换把非零值转换为1，并仍以BIT类型存储。
8）试图转换到不同长度的数据类型，会截短转换值并在转换值后显示“+”，以标识发生了这种截断。
9）用CONVERT（）函数的style 选项能以不同的格式显示日期和时间。style 是将DATATIME 和SMALLDATETIME 数据转换为字符串时所选用的由SQL Server 系统提供的转换样式编号，不同的样式编号有不同的输出格式。
七、日期函数
1、day(date_expression) 
返回date_expression中的日期值

2、month(date_expression)
返回date_expression中的月份值

3、year(date_expression)
返回date_expression中的年份值

4、DATEADD()
DATEADD (<datepart>， <number>， <date>)
返回指定日期date 加上指定的额外日期间隔number 产生的新日期。
5、DATEDIFF()
DATEDIFF (<datepart>， <date1>， <date2>)
返回两个指定日期在datepart 方面的不同之处，即date2 超过date1的差距值，其结果值是一个带有正负号的整数值。

6、DATENAME()
DATENAME (<datepart>， <date>)
以字符串的形式返回日期的指定部分此部分。由datepart 来指定。

7、DATEPART()
DATEPART (<datepart>， <date>)
以整数值的形式返回日期的指定部分。此部分由datepart 来指定。
DATEPART (dd， date) 等同于DAY (date)
DATEPART (mm， date) 等同于MONTH (date)
DATEPART (yy， date) 等同于YEAR (date)

8、GETDATE()
以DATETIME 的缺省格式返回系统当前的日期和时间。

--聚合函数
use pubs
Go
select avg(distinct price)  --算平均数
from titles
where type='business'
go 
use pubs
go
select max(ytd_sales)  --最大数
from titles
go 

use pubs
go
select min(ytd_sales) --最小数
from titles
go 

use pubs
go
select type,sum(price),sum(advance)  --求和
from titles
group by type
order by type
go 

use pubs
go 
select count(distinct city)  --求个数
from authors
go 

use pubs
go
select stdev(royalty) --返回给定表达式中所有值的统计标准偏差
from titles
go 

use pubs
go
select stdevp(royalty) --返回表达式中所有制的填充统计标准偏差
from titles
go 

use pubs
go
select var(royalty) --返回所有值的统计方差
from titles
go 

use pubs
go
select varp(royalty) --返回所有值的填充的统计方差
from titles
go 

--数学函数 

select sin(23.45),atan(1.234),rand(),PI(),sign(-2.34) --其中rand是获得一个随机数
--配置函数
SELECT @@VERSION --获取当前数据库版本
SELECT @@LANGUAGE --当前语言
--时间函数
select getdate() as 'wawa_getdate' --当前时间
select getutcdate() as 'wawa_getutcdate' --获取utc时间
select day(getdate()) as 'wawa_day' --取出天
select month(getdate()) as 'wawa_month' --取出月
select year(getdate()) as 'wawa_year' --取出年
select dateadd(d,3,getdate()) as wawa_dateadd --加三天,注意'd'表示天,'m'表示月,'yy'表示年,下面一样
select datediff(d,'2004-07-01','2004-07-15') as wawa_datediff --计算两个时间的差
select datename(d,'2004-07-15') as wawa_datename --取出时间的某一部分
select datepart(d,getdate()) as wawa_datepart  --取出时间的某一部分,和上面的那个差不多
--字符串函数
select ascii(123) as '123',ascii('123') as '"123"',ascii('abc') as '"abc"' --转换成ascii码
select char(123),char(321),char(-123) --根据ascii转换成字符
select lower('ABC'),lower('Abc'),upper('Abc'),upper('abc') --转换大小写
select str(123.45,6,1), str(123.45,2,2) --把数值转换成字符串
select ltrim('    "左边没有空格"')  --去空格
select rtrim('"右边没有空格"     ') --去空格
select ltrim(rtrim('   "左右都没有空格"    ')) --去空格
select left('sql server',3),right('sql server',6) --取左或者取右 

use pubs
select au_lname,substring(au_fname,1,1) --取子串
from authors
order by au_lname 

select charindex('123','abc123def',2) --返回字符串中指定表达式的起始位置
select patindex('123','abc123def'),patindex('%123%','abc123def') --返回表达式中某模式第一次出现的起始位置
select quotename('abc','{'),quotename('abc') --返回由指定字符扩住的字符串
select reverse('abc'),reverse('上海') --颠倒字符串顺序
select replace('abcdefghicde','cde','xxxx') --返回呗替换了指定子串的字符串
select space(5),space(-2) 

--系统函数
select host_name() as 'host_name',host_id() as 'host_id',user_name() as 'user_name',user_id() as 'user_id',db_name() as 'db_name'
--变量的定义使用
--声明局部变量
declare @mycounter int
declare @last_name varchar(30),@fname varchar(20),@state varchar(2) --一下声明多个变量
--给变量赋值
use northwind
go
declare @firstnamevariable varchar(20),
 @regionvariable varchar(30)
set @firstnamevariable='anne' --可以用set,也可以用select给变量赋值,微软推荐用set,但select在选择一个值直接赋值时很有用
set @regionvariable ='wa' 

select lastname,firstname,title  --用声明并赋值过的变量构建一个Select语句并查询
from employees
where firstname= @firstnamevariable or region=@regionvariable
go
--全局变量
select @@version  --返回数据库版本
select @@error  --返回最后的一次脚本错误
select @@identity  --返回最后的一个自动增长列的id 

--while,break,continue的使用
--首先计算所有数的平均价格,如果低于30的话进入循环让所有的price翻倍,
--里面又有个if来判断如果最大的单价还大于50的话,退出循环,否则继续循环,知道最大单价大于50就break出循环,呵呵,
--我分析的应该对吧.
use pubs
go
while (select avg(price) from titles) <$30 
begin
 update titles
  set price=price*2
  select max(price) from titles
  if(select max(price) from titles) >$50
  break
  else
  continue
end
print 'too much for the marker to bear' 

--事务编程经典例子
--begin transaction是开始事务,commit transaction是提交事务,rollback transaction是回滚事务
--这个例子是先插入一条记录,如果出现错误的话就回滚事务,也就是取消,并直接return(返回),如果没错的话就commit 提交这个事务了哦
--上面的那个return返回可以返回一个整数值,如果这个值是0的话就是执行的时候没出错,如果出错了就是一个负数,
--这个return也可以用在存储过程中,可用用 exec @return_status= pro_name来获取这个值
use pubs
go
begin tran mytran
 insert into stores(stor_id,stor_name)
  values('333','my books')
 go
 insert into discounts(discounttype,stor_id,discount)
  values('清仓甩卖','9999',50.00)
 if @@error<>0
  begin
   rollback tran mytran
   print '插入打折记录出错'
   return
  end
commit tran mytran 

--事务处理的保存点示例
--做了事务保存点后可以rollback(回滚)到指定的保存点,不至于所有的操作都不能用
use pubs
go
select * from stores
begin transaction testsavetran
 insert into stores(stor_id,stor_name)
  values('1234','W.Z.D Book')
 save transaction before_insert_data2
 go
 insert into stores(stor_id,stor_name)
  values('5678','foreat Books')
 go
rollback transaction before_insert_data2
select * from stores 

--存储存储过程
use pubs
if exists(select name from sysobjects where name= 'proc_calculate_taxes' and type='P')
 drop procedure proc_calculate_taxes
go
create procedure proc_calculate_taxes (@p1 smallint=42,@p2 char(1),@p3 varchar(8)='char')
as
select *
from titles
--执行过程
EXECUTE PROC_CALCULATE_TAXES @P2='A'