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()
不存在就创建
- CREATE TABLE IF NOT EXISTS `basegraphics` (
- `id` int(10) NOT NULL AUTO_INCREMENT,
- `content` text NOT NULL COMMENT '文本内容',
- `src` varchar(100) NOT NULL COMMENT '图片地址',
- `width` varchar(30) NOT NULL COMMENT '图片宽度',
- `height` varchar(30) NOT NULL COMMENT '图片高度',
- `createtime` int(10) unsigned NOT NULL DEFAULT '0',
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
DISTINCT 替换GROUP by
- select count(1) from (select order_id FROM `sales_flat_order_item` GROUP by order_id)a;
- SELECT count(DISTINCT order_id) FROM `sales_flat_order_item`;
show create table name 即可查看建表sql语句
SHOW COLUMNS FROM package;
上一条
- SELECT * FROM A WHERE id < $id ORDER BY id DESC LIMIT 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')；
说明：两张关联表，删除主表中已经在副表中没有的信息
- delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
LPAD (str,len,padstr)
返回字符串 str, 其左边由字符串padstr 填补到len 字符长度。假如str 的长度大于len, 则返回值被缩短至 len 字符。
- mysql> SELECT LPAD('hi',4,'?');-> '??hi'
- 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。
- mysql> SELECT INSERT('Quadratic', 3, 4, 'What');-> 'QuWhattic'
- mysql> SELECT INSERT('Quadratic', -1, 4, 'What');-> 'Quadratic'
- mysql> SELECT INSERT('Quadratic', 3, 100, 'What');-> 'QuWhat'
LENGTH(str)
返回值为字符串str 的长度，单位为字节。一个多字节字符算作多字节。这意味着 对于一个包含5个2字节字符的字符串， LENGTH() 的返回值为 10, 而 CHAR_LENGTH()的返回值则为5。
- mysql> SELECT LENGTH('text');-> 4
REPEAT(str,count)
返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。若 count <= 0,则返回一个空字符串。若str 或 count 为 NULL，则返回 NULL 。
- mysql> SELECT REPEAT('MySQL', 3);-> 'MySQLMySQLMySQL'
测试表数据
GREATEST（求最大值）和LEAST（求最小值）
- mysql> SELECT c_id, GREATEST( num1, num2 ) AS max, num1, num2 from comment where
- num1 != "" and num2 != "";
- +------+------+------+------+
- | c_id | max | num1 | num2 |
- +------+------+------+------+
- | 1 | 21 | 21 | 12 |
- | 2 | 219 | 133 | 219 |
- | 3 | 67 | 67 | 16 |
- +------+------+------+------+
- 3 rows in set (0.00 sec)
- mysql> SELECT c_id, LEAST( num1, num2 ) AS max, num1, num2 from comment where nu
- m1 != "" and num2 != "";
- +------+------+------+------+
- | c_id | max | num1 | num2 |
- +------+------+------+------+
- | 1 | 12 | 21 | 12 |
- | 2 | 133 | 133 | 219 |
- | 3 | 16 | 67 | 16 |
- +------+------+------+------+
- 3 rows in set (0.00 sec)
INTERVAL函数
- mysql> select * from comment where datetime <= (SELECT now( ) - INTERVAL 10 h
- our AS time_start ); //10个小时前评论的数据
- +------+------+-------+------------+---------------------+------+------+
- | c_id | u_id | name | content | datetime | num1 | num2 |
- +------+------+-------+------------+---------------------+------+------+
- | 1 | 1 | test | 2222222211 | 2010-11-10 15:16:00 | 21 | 12 |
- | 2 | 1 | test2 | tank | 2010-11-10 15:01:00 | 133 | 219 |
- | 3 | 2 | tank | zhangy | 2010-11-10 15:11:00 | 67 | 16 |
- +------+------+-------+------------+---------------------+------+------+
- 3 rows in set (0.00 sec)
REGEXP函数
- mysql> SELECT * FROM `comment` WHERE content REGEXP '[0-9]+';
- +------+------+------+------------+---------------------+------+------+
- | c_id | u_id | name | content | datetime | num1 | num2 |
- +------+------+------+------------+---------------------+------+------+
- | 1 | 1 | test | 2222222211 | 2010-11-10 15:16:00 | 21 | 12 |
- +------+------+------+------------+---------------------+------+------+
- 1 row in set (0.00 sec)
IFNULL(expr1,expr2)
如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。 IFNULL()返回一个数字或字符 串值，取决于它被使用的上下文环境 。
- mysql > select IFNULL(1,0);
- -> 1
- mysql> select IFNULL(0,10);
- -> 0
- mysql> select IFNULL(1/0,10);
- -> 10
- mysql> select IFNULL(1/0,'yes');
- -> 'yes'
SELECT ifnull (SUM(qty),0) as sum from table
判断字段值是否为null或者空串''
- SELECT * FROM `table_name' WHERE ifnull(col_name,'')='';
IF(expr1,expr2,expr3)
如果expr1是TRUE(expr1<>0且expr1<>NULL)，那么IF()返回expr2，否则它返回expr3。 IF()返回一个数字或字符串值，取决于它被使用的上下文。
- mysql> select IF(1>2,2,3);
- -> 3
- mysql> select IF(1<2,'yes','no');
- -> 'yes'
- mysql> select IF(strcmp('test','test1'),'yes','no');
- -> 'no'
expr1作为整数值被计算，它意味着如果你正在测试浮点或字符串值，你应该使用一个比较操作来做。
- mysql> select IF(0.1,1,0);
- -> 0
- mysql> select IF(0.1<>0,1,0);
- -> 1
在上面的第一种情况中，IF(0.1)返回0，因为0.1被变换到整数值, 导致测试IF(0)。这可能不是你期望的。在第二种情况中，比较测试原来的浮点值看它是否是非零，比较的结果被用作一个整数。
select * from stock_moves where abs (qty)=1140
MySQL的concat 函数可以连接一个或者多个字符串,MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL
- mysql> SELECT CONCAT(2,' test');
- -> '2 test'
concat_ws() 函数, 表示concat with separator,即有分隔符的字符串连接如连接后以逗号分隔
- mysql> SELECT CONCAT_WS(';',2,' test');
- -> '2;test'
COALESCE() 函数表示可以返回参数中的第一个非空表达式，当你有N个参数时选取第一个非空值（从左到右）。
- mysql->select coalesce(null,"carrot","apple")
- ->carrot
- mysql-> select coalesce(1,"carrot","apple")
- ->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()的双参数形式相同，除非参
数的顺序被颠倒。
- mysql> SELECT INSTR('foobarbar', 'bar');
- -> 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 使用一
个负值。
- mysql> SELECT SUBSTRING('Quadratically',5);
- -> 'ratically'
- mysql> SELECT SUBSTRING('foobarbar' FROM 4);
- -> 'barbar'
- mysql> SELECT SUBSTRING('Quadratically',5,6);
- -> 'ratica'
- mysql> SELECT SUBSTRING('Sakila', -3);
- -> 'ila'
SUBSTRING_INDEX(str,delim,count)
在定界符 delim 以及count 出现前，从字符串str返回自字符串。若count为正值,则返回最终
定界符(从左边开始)左边的一切内容。若count为负值，则返回定界符（从右边开始）右边
的一切内容。
- mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
- -> 'www.mysql'
- mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
- -> 'mysql.com'
- CREATE FUNCTION substr_count(x varchar(255), delim varchar(12)) returns int
- 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值不
会被添加到结果中。
- mysql> SELECT MAKE_SET(1,'a','b','c');
- -> 'a'
- mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
- -> 'hello,world'
- mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
- -> 'hello'
LEFT(str,len)
返回从字符串str 开始的len 最左字符。
- mysql> SELECT LEFT('foobarbar', 5);-> 'fooba'
ELT(N,str1,str2,str3,...)
如果N= 1，返回str1，如果N= 2，返回str2，等等。如果N小于1或大于参数个数，返回NULL。ELT()是FIELD()反运算。
- select ELT(1, 'ej ', 'Heja ', 'hej ', 'foo '); //-> 'ej '
REPLACE(str,from_str,to_str)
返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
- mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
- -> 'WwWwWw.mysql.com'
update set DATE = replace(replace (replace(DATE, "年 ", "- "), "月 ", "- "), "日 ", " ");
GROUP_CONCAT() 是MySQL数据库提供的一个函数，通常跟GROUP BY一起用,
1.例如:
- select student_id,group_concat(courses_id order by courses_id desc) as courses_id from student_courses group by student_id;
- +------------+-------------+
- | student_id | courses_id |
- +------------+-------------+
- | 2 | 5,4,3 |
2.当然分隔符还可以自定义，默认是以“,”作为分隔符，若要改为“|||”，则使用SEPARATOR来指定，例如：
- SELECT student_id, GROUP_CONCAT(courses_id SEPARATOR '|||') AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
- +------------+---------+
- | student_id | courses |
- +------------+---------+
- | 2 | 3|||4|||5 |
- +------------+---------+
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进行字符串替换处理
- mysql> select * from sales;
- +-----+------------+--------+--------+--------+------+------------+
- | num | name | winter | spring | summer | fall | category |
- +-----+------------+--------+--------+--------+------+------------+
- | 1 | Java | 1067 | 200 | 150 | 267 | Holiday |
- | 2 | C | 970 | 770 | 531 | 486 | Profession |
- | 3 | JavaScript | 53 | 13 | 21 | 856 | Literary |
- | 4 | SQL | 782 | 357 | 168 | 250 | Profession |
- | 5 | Oracle | 589 | 795 | 367 | 284 | Holiday |
- | 6 | MySQL | 953 | 582 | 336 | 489 | Literary |
- | 7 | Cplus | 752 | 657 | 259 | 478 | Literary |
- | 8 | Python | 67 | 23 | 83 | 543 | Holiday |
- | 9 | PHP | 673 | 48 | 625 | 52 | Profession |
- +-----+------------+--------+--------+--------+------+------------+
- 9 rows in set (0.01 sec)
- mysql> SELECT name AS Name,
- -> CASE category
- -> WHEN "Holiday" THEN "Seasonal"
- -> WHEN "Profession" THEN "Bi_annual"
- -> WHEN "Literary" THEN "Random" END AS "Pattern"
- -> FROM sales;
- +------------+-----------+
- | Name | Pattern |
- +------------+-----------+
- | Java | Seasonal |
- | C | Bi_annual |
- | JavaScript | Random |
- | SQL | Bi_annual |
- | Oracle | Seasonal |
- | MySQL | Random |
- | Cplus | Random |
- | Python | Seasonal |
- | PHP | Bi_annual |
- +------------+-----------+
- 9 rows in set (0.00 sec)
