sql 常用函数

SQL avg() SQL count() SQL first() SQL last() SQL max() SQL min() SQL sum()

SQL Having SQL ucase() SQL lcase() SQL len() SQL round() SQL format()

不存在就创建

Java代码  

1. CREATE TABLE IF NOT EXISTS `basegraphics` (  
2.   `id` int(10) NOT NULL AUTO_INCREMENT,  
3.   `content` text NOT NULL COMMENT '文本内容',  
4.   `src` varchar(100) NOT NULL COMMENT '图片地址',  
5.   `width` varchar(30) NOT NULL COMMENT '图片宽度',  
6.   `height` varchar(30) NOT NULL COMMENT '图片高度',  
7.   `createtime` int(10) unsigned NOT NULL DEFAULT '0',  
8.   PRIMARY KEY (`id`)  
9. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;   

DISTINCT 替换GROUP by

Java代码  

1. select count(1) from (select order_id FROM `sales_flat_order_item` GROUP by order_id)a;  
2. SELECT count(DISTINCT order_id) FROM `sales_flat_order_item`;  

show create table name 即可查看建表sql语句

SHOW COLUMNS FROM  package;
上一条

Java代码  

1. SELECT * FROM A WHERE id < $id ORDER BY id DESC LIMIT  1    

下一条

Java代码  

1. SELECT * FROM A WHERE id > $id ORDER BY id ASC LIMIT  1  

INSERT INTO Persons VALUES ('1, 'Bill', 'Xuanwumen 10', 'Beijing')

如果有字段自增和默认值可写成：

INSERT INTO Persons VALUES ('Bill', 'Xuanwumen 10')；
说明：两张关联表，删除主表中已经在副表中没有的信息

Java代码  

1. delete from info where not exists ( select * from infobz where info.infid=infobz.infid )   

LPAD (str,len,padstr)

返回字符串 str, 其左边由字符串padstr 填补到len 字符长度。假如str 的长度大于len, 则返回值被缩短至 len 字符。

Java代码  

1. mysql> SELECT LPAD('hi',4,'?');-> '??hi'  
2. mysql> SELECT LPAD('hi',1,'?');-> 'h'  

RPAD(str,len,padstr)
返回字符串str, 其右边被字符串 padstr填补至len 字符长度。假如字符串str 的长度大于 len,则返回值被缩短到与 len 字符相同长度。

INSERT(str,pos,len,newstr)
返回字符串 str, 其子字符串起始于 pos 位置和长期被字符串 newstr取代的len 字符。 如果pos 超过字符串长度，则返回值为原始字符串。 假如len的长度大于其它字符串的长度，则从位置pos开始替换。若任何一个参数为null，则返回值为NULL。

Java代码  

1. mysql> SELECT INSERT('Quadratic', 3, 4, 'What');-> 'QuWhattic'  
2. mysql> SELECT INSERT('Quadratic', -1, 4, 'What');-> 'Quadratic'  
3. mysql> SELECT INSERT('Quadratic', 3, 100, 'What');-> 'QuWhat'  

LENGTH(str)
返回值为字符串str 的长度，单位为字节。一个多字节字符算作多字节。这意味着 对于一个包含5个2字节字符的字符串， LENGTH() 的返回值为 10, 而 CHAR_LENGTH()的返回值则为5。

Java代码  

1. mysql> SELECT LENGTH('text');-> 4  

REPEAT(str,count)
返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。若 count <= 0,则返回一个空字符串。若str 或 count 为 NULL，则返回 NULL 。

Java代码  

1. mysql> SELECT REPEAT('MySQL', 3);-> 'MySQLMySQLMySQL'  

测试表数据 

GREATEST（求最大值）和LEAST（求最小值）

Java代码  

1. mysql> SELECT c_id, GREATEST( num1, num2 ) AS max, num1, num2 from comment where  
2.  num1 != "" and num2 != "";  
3. +------+------+------+------+  
4. | c_id | max  | num1 | num2 |  
5. +------+------+------+------+  
6. |    1 |   21 |   21 |   12 |  
7. |    2 |  219 |  133 |  219 |  
8. |    3 |   67 |   67 |   16 |  
9. +------+------+------+------+  
10. 3 rows in set (0.00 sec)  
11.   
12. mysql> SELECT c_id, LEAST( num1, num2 ) AS max, num1, num2 from comment where nu  
13. m1 != "" and num2 != "";  
14. +------+------+------+------+  
15. | c_id | max  | num1 | num2 |  
16. +------+------+------+------+  
17. |    1 |   12 |   21 |   12 |  
18. |    2 |  133 |  133 |  219 |  
19. |    3 |   16 |   67 |   16 |  
20. +------+------+------+------+  
21. 3 rows in set (0.00 sec)  

INTERVAL函数

Java代码  

1. mysql> select * from comment where   datetime  <= (SELECT now( ) - INTERVAL 10 h  
2. our AS time_start );  //10个小时前评论的数据  
3. +------+------+-------+------------+---------------------+------+------+  
4. | c_id | u_id | name  | content    | datetime            | num1 | num2 |  
5. +------+------+-------+------------+---------------------+------+------+  
6. |    1 |    1 | test  | 2222222211 | 2010-11-10 15:16:00 |   21 |   12 |  
7. |    2 |    1 | test2 | tank       | 2010-11-10 15:01:00 |  133 |  219 |  
8. |    3 |    2 | tank  | zhangy     | 2010-11-10 15:11:00 |   67 |   16 |  
9. +------+------+-------+------------+---------------------+------+------+  
10. 3 rows in set (0.00 sec)  

REGEXP函数

Java代码  

1. mysql> SELECT * FROM `comment` WHERE content REGEXP '[0-9]+';  
2. +------+------+------+------------+---------------------+------+------+  
3. | c_id | u_id | name | content    | datetime            | num1 | num2 |  
4. +------+------+------+------------+---------------------+------+------+  
5. |    1 |    1 | test | 2222222211 | 2010-11-10 15:16:00 |   21 |   12 |  
6. +------+------+------+------------+---------------------+------+------+  
7. 1 row in set (0.00 sec)  

IFNULL(expr1,expr2) 
如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。 IFNULL()返回一个数字或字符 串值，取决于它被使用的上下文环境 。

Java代码  

1. mysql > select IFNULL(1,0);  
2. -> 1  
3. mysql> select IFNULL(0,10);  
4. -> 0  
5. mysql> select IFNULL(1/0,10);  
6. -> 10  
7. mysql> select IFNULL(1/0,'yes');  
8. -> 'yes'  

SELECT ifnull (SUM(qty),0) as sum from table

判断字段值是否为null或者空串''

Java代码  

1. SELECT * FROM `table_name' WHERE ifnull(col_name,'')='';   

IF(expr1,expr2,expr3) 
如果expr1是TRUE(expr1<>0且expr1<>NULL)，那么IF()返回expr2，否则它返回expr3。 IF()返回一个数字或字符串值，取决于它被使用的上下文。

Java代码  

1. mysql> select IF(1>2,2,3);  
2. -> 3  
3. mysql> select IF(1<2,'yes','no');  
4. -> 'yes'  
5. mysql> select IF(strcmp('test','test1'),'yes','no');  
6. -> 'no'  

expr1作为整数值被计算，它意味着如果你正在测试浮点或字符串值，你应该使用一个比较操作来做。

Java代码  

1. mysql> select IF(0.1,1,0);  
2. -> 0  
3. mysql> select IF(0.1<>0,1,0);  
4. -> 1  

在上面的第一种情况中，IF(0.1)返回0，因为0.1被变换到整数值, 导致测试IF(0)。这可能不是你期望的。在第二种情况中，比较测试原来的浮点值看它是否是非零，比较的结果被用作一个整数。

select * from stock_moves where  abs (qty)=1140

MySQL的concat 函数可以连接一个或者多个字符串,MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL

Java代码  

1. mysql> SELECT CONCAT(2,' test');  
2. -> '2 test'  

concat_ws() 函数, 表示concat with separator,即有分隔符的字符串连接如连接后以逗号分隔

Java代码  

1. mysql> SELECT CONCAT_WS(';',2,' test');  
2. -> '2;test'  

COALESCE() 函数表示可以返回参数中的第一个非空表达式，当你有N个参数时选取第一个非空值（从左到右）。

Java代码  

1. mysql->select coalesce(null,"carrot","apple")  
2. ->carrot  
3. mysql-> select coalesce(1,"carrot","apple")  
4. ->1  

select coalesce(a,b,c);
如果a==null,则选择b；

如果b==null,则选择c；

如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。这和LOCATE()的双参数形式相同，除非参
数的顺序被颠倒。

Java代码  

1. mysql> SELECT INSTR('foobarbar', 'bar');  
2. -> 4  

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) ,
SUBSTRING(str FROM pos FOR len)
不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格
式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格
式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字
符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一
个负值。

Java代码  

1. mysql> SELECT SUBSTRING('Quadratically',5);  
2. -> 'ratically'  
3. mysql> SELECT SUBSTRING('foobarbar' FROM 4);  
4. -> 'barbar'  
5. mysql> SELECT SUBSTRING('Quadratically',5,6);  
6. -> 'ratica'  
7. mysql> SELECT SUBSTRING('Sakila', -3);  
8. -> 'ila'  

SUBSTRING_INDEX(str,delim,count) 
在定界符 delim 以及count 出现前，从字符串str返回自字符串。若count为正值,则返回最终
定界符(从左边开始)左边的一切内容。若count为负值，则返回定界符（从右边开始）右边
的一切内容。

Java代码  

1. mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);  
2. -> 'www.mysql'  
3. mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);  
4. -> 'mysql.com'  

Java代码  

1. CREATE FUNCTION substr_count(x varchar(255), delim varchar(12)) returns int  
2. return (length(x)-length(REPLACE(x, delim, ''))) // select substr_count('ABAC','A');  

MAKE_SET(bits,str1,str2,...)
返回一个设定值 (一个包含被‘,’号分开的字字符串的字符串) ，由在bits 组中具有相应的
比特的字符串组成。str1 对应比特 0, str2 对应比特1,以此类推。str1, str2, ...中的 NULL值不
会被添加到结果中。

Java代码  

1. mysql> SELECT MAKE_SET(1,'a','b','c');  
2. -> 'a'  
3. mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');  
4. -> 'hello,world'  
5. mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');  
6. -> 'hello'   

LEFT(str,len)
返回从字符串str 开始的len 最左字符。

Java代码  

1. mysql> SELECT LEFT('foobarbar', 5);-> 'fooba'  

ELT(N,str1,str2,str3,...)   
如果N=   1，返回str1，如果N=   2，返回str2，等等。如果N小于1或大于参数个数，返回NULL。ELT()是FIELD()反运算。

Java代码  

1. select   ELT(1,   'ej ',   'Heja ',   'hej ',   'foo ');  //->   'ej '  

REPLACE(str,from_str,to_str)
返回字符串str 以及所有被字符串to_str替代的字符串from_str 。

Java代码  

1. mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');  
2. -> 'WwWwWw.mysql.com'   

update   set   DATE   =   replace(replace (replace(DATE,   "年 ",   "- "),   "月 ",   "- "),   "日 ",   " ");

GROUP_CONCAT() 是MySQL数据库提供的一个函数，通常跟GROUP BY一起用,
1.例如:

Java代码  

1. select student_id,group_concat(courses_id order by courses_id desc) as courses_id from student_courses group by student_id;  
2. 　　+------------+-------------+  
3. 　　| student_id | courses_id  |  
4. 　　+------------+-------------+  
5. 　　|          2 | 5,4,3            |    

2.当然分隔符还可以自定义，默认是以“,”作为分隔符，若要改为“|||”，则使用SEPARATOR来指定，例如：

Java代码  

1. SELECT student_id, GROUP_CONCAT(courses_id SEPARATOR '|||') AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;  
2. +------------+---------+  
3. | student_id | courses |  
4. +------------+---------+  
5. |   2        | 3|||4|||5    |  
6.  +------------+---------+       

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

UNION 指令的目的是将两个 SQL 语句的结果合并起来。从这个角度来看， UNION 跟 JOIN 有些许类似，因为这两个指令都可以由多个表格中撷取资料。 UNION 的一个限制是两个 SQL 语句所产生的栏位需要是同样的资料种类。另外，当我们用 UNION 这 个指令时，我们只会看到不同的资料值 (类似 SELECT DISTINCT )。

UNION 的语法如下：

[SQL 语句 1]
UNION
[SQL 语句 2]

而我们要找 出来所有有营业额 (sales) 的日子。要达到这个目的，我们用以下的 SQL 语句：

SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales

使用CASE WHEN进行字符串替换处理

Java代码  

1. mysql> select * from sales;  
2. +-----+------------+--------+--------+--------+------+------------+  
3. | num | name       | winter | spring | summer | fall | category   |  
4. +-----+------------+--------+--------+--------+------+------------+  
5. |   1 | Java       |   1067 |    200 |    150 |  267 | Holiday    |  
6. |   2 | C          |    970 |    770 |    531 |  486 | Profession |  
7. |   3 | JavaScript |     53 |     13 |     21 |  856 | Literary   |  
8. |   4 | SQL        |    782 |    357 |    168 |  250 | Profession |  
9. |   5 | Oracle     |    589 |    795 |    367 |  284 | Holiday    |  
10. |   6 | MySQL      |    953 |    582 |    336 |  489 | Literary   |  
11. |   7 | Cplus      |    752 |    657 |    259 |  478 | Literary   |  
12. |   8 | Python     |     67 |     23 |     83 |  543 | Holiday    |  
13. |   9 | PHP        |    673 |     48 |    625 |   52 | Profession |  
14. +-----+------------+--------+--------+--------+------+------------+  
15. 9 rows in set (0.01 sec)  
16.   
17. mysql> SELECT name AS Name,  
18.     -> CASE category  
19.     -> WHEN "Holiday" THEN "Seasonal"  
20.     -> WHEN "Profession" THEN "Bi_annual"  
21.     -> WHEN "Literary" THEN "Random" END AS "Pattern"  
22.     -> FROM sales;  
23. +------------+-----------+  
24. | Name       | Pattern   |  
25. +------------+-----------+  
26. | Java       | Seasonal  |  
27. | C          | Bi_annual |  
28. | JavaScript | Random    |  
29. | SQL        | Bi_annual |  
30. | Oracle     | Seasonal  |  
31. | MySQL      | Random    |  
32. | Cplus      | Random    |  
33. | Python     | Seasonal  |  
34. | PHP        | Bi_annual |  
35. +------------+-----------+  
36. 9 rows in set (0.00 sec)