常用SQL
1.COUNT ,SUM,MAX,MIN函数
1.1 查找表中记录数目
SELECT COUNT(id) FROM table;
(其中为NULL的不计数)
1.2查找表中不重复的记录数目
SELECT COUNT(DISTINCT id) FROM table;
1.3查找数据总数
SELECT COUNT(*) FROM table;
2.LIMIT
2.1 限制记录数
SELECT id FROM table LIMIT 2;(前两条)
SELECT id FROM table LIMIT 1,2(从第二条开始取两条 2,3 两条记录)
3.ORDER BY
3.1 排序
SELECT id FROM table ORDER BY id;(默认->顺序,DESC->倒序 )
4.WHERE,HAVING,LIKE,IN(筛选数据记录)
4.1
SELECT id FROM table WHERE id>=1;
SELECT name FROM table WHERE name>='m';(查出名字首字母在'm'到'z'之间)
SELECT name FROM table WHERE name LIKE '%er%';(包含'er')(此查询在表大的时候较慢)
SELECT id,name FROM table WHERE id IN (2,3,4);
4.2 HAVING
当WHERE和HAVING 一同出现时,HAVING 对WHERE 的结果做进一步的筛选.HAVING优点是可以作用于有关字段的数学计算结果.
5.多表查询
5.1 SELECT name,age FROM table1,table2 WHERE table1.id = table2.id;
5.2 SELECT DISTINCT name,age FROM table1,table2 WHERE table1.id = table2.id;(DISTINCT 确保重复的数据只输出一次)
6.合并查询(UNION)
6.1
(SELECT * FROM t1 ORDER BY id LIMIT 10)
UNION
(SELECT * FROM t2 ORDER BY id LIMIT 10)
ORDER BY age LIMIT 5
(先从t1,t2中分别选出最多10条记录并把它们合并起来,然后从这些记录里选取最多5条记录)
7.分组统计GROUP BY
8.合并字符串CONCAT
SELECT CONCAT(firstname," ",secondname) FROM t1;
9.截取字符串SUBSTR
SELECT SUBSER(name,1,3)FROM t1;
(提取名字前三个字符)
10.确定字符串的长度CHAR_LENGTH(S)字符串中的字符个数,LENGTH()字符串的字节长度
SELECT CHAR_LENGTH(name) FROM t1;
SELECT LENGTH(name) FROM t1;
使用latin1字符集的串来说上面两个函数返回值永远相等
如果使用的是UNICODE字符集或另外一种多字节字符集,单个字符的字节长度往往都大于1个字节.
11.IF
select if(char_length(name)>2,concat(left(name,2),"...",right(name,1)),name) from t1;
IF(a,b,c) 对表达式a求值,为真->返回b,为假->返回c.
对名字长度<=2的按原来名字输出,
对名字长度>2的.取名字前2个长度+"..."+名字最后一个长度,输出
12.存储改变的字符串:
UPDATE t1 SET name = REPLACE(name,'"','\'');
替换t1表中的name中所有"为'
UPDATE t1 SET name = LEFT(name,LOCATE(" ",name),-1) WHERE LOCATE(" ",name)>1;
更新为第一个空格前的单词.LOCATE()返回值是匹配模板在字符串里的位置.
13.对字符串做二进制比较:
SELECT 'a'='A',BINARY 'A' = 'a','A'=BINARY 'a';
结果:1,0,0
14.模板匹配LIKE,REGEXP
LIKE:'_'任意单个字符
'%'0-N个字符
15.日期和时间
SELECT COUNT(*) FROM t1 WHERE ts BETWEEN '2008-08-08 08:08:08' AND '2009-09-09 09:09:09';
SELECT COUNT(*) FROM t1 WHERE ts BETWEEN '2008/08/08 08:08:08' AND '2009/09/09 09:09:09';
SELECT COUNT(*) FROM t1 WHERE ts BETWEEN '20080808080808' AND '20090909090909';
以上三条等效;
16.判断是否为空 ISNULL,IFNULL
select if(ISNULL(name),concat(id,"....",sex),name) from t1 where id=21;
IF中ISNULL为真->concat(id,"....",sex)
IF中ISNULL为假->name
IFNULL(e1,e2)
e1==NULL ->e2
e1!=NULL -> e1
17.CASE分支
1.1 查找表中记录数目
SELECT COUNT(id) FROM table;
(其中为NULL的不计数)
1.2查找表中不重复的记录数目
SELECT COUNT(DISTINCT id) FROM table;
1.3查找数据总数
SELECT COUNT(*) FROM table;
2.LIMIT
2.1 限制记录数
SELECT id FROM table LIMIT 2;(前两条)
SELECT id FROM table LIMIT 1,2(从第二条开始取两条 2,3 两条记录)
3.ORDER BY
3.1 排序
SELECT id FROM table ORDER BY id;(默认->顺序,DESC->倒序 )
4.WHERE,HAVING,LIKE,IN(筛选数据记录)
4.1
SELECT id FROM table WHERE id>=1;
SELECT name FROM table WHERE name>='m';(查出名字首字母在'm'到'z'之间)
SELECT name FROM table WHERE name LIKE '%er%';(包含'er')(此查询在表大的时候较慢)
SELECT id,name FROM table WHERE id IN (2,3,4);
4.2 HAVING
当WHERE和HAVING 一同出现时,HAVING 对WHERE 的结果做进一步的筛选.HAVING优点是可以作用于有关字段的数学计算结果.
5.多表查询
5.1 SELECT name,age FROM table1,table2 WHERE table1.id = table2.id;
5.2 SELECT DISTINCT name,age FROM table1,table2 WHERE table1.id = table2.id;(DISTINCT 确保重复的数据只输出一次)
6.合并查询(UNION)
6.1
(SELECT * FROM t1 ORDER BY id LIMIT 10)
UNION
(SELECT * FROM t2 ORDER BY id LIMIT 10)
ORDER BY age LIMIT 5
(先从t1,t2中分别选出最多10条记录并把它们合并起来,然后从这些记录里选取最多5条记录)
7.分组统计GROUP BY
8.合并字符串CONCAT
SELECT CONCAT(firstname," ",secondname) FROM t1;
9.截取字符串SUBSTR
SELECT SUBSER(name,1,3)FROM t1;
(提取名字前三个字符)
10.确定字符串的长度CHAR_LENGTH(S)字符串中的字符个数,LENGTH()字符串的字节长度
SELECT CHAR_LENGTH(name) FROM t1;
SELECT LENGTH(name) FROM t1;
使用latin1字符集的串来说上面两个函数返回值永远相等
如果使用的是UNICODE字符集或另外一种多字节字符集,单个字符的字节长度往往都大于1个字节.
11.IF
select if(char_length(name)>2,concat(left(name,2),"...",right(name,1)),name) from t1;
IF(a,b,c) 对表达式a求值,为真->返回b,为假->返回c.
对名字长度<=2的按原来名字输出,
对名字长度>2的.取名字前2个长度+"..."+名字最后一个长度,输出
12.存储改变的字符串:
UPDATE t1 SET name = REPLACE(name,'"','\'');
替换t1表中的name中所有"为'
UPDATE t1 SET name = LEFT(name,LOCATE(" ",name),-1) WHERE LOCATE(" ",name)>1;
更新为第一个空格前的单词.LOCATE()返回值是匹配模板在字符串里的位置.
13.对字符串做二进制比较:
SELECT 'a'='A',BINARY 'A' = 'a','A'=BINARY 'a';
结果:1,0,0
14.模板匹配LIKE,REGEXP
LIKE:'_'任意单个字符
'%'0-N个字符
15.日期和时间
SELECT COUNT(*) FROM t1 WHERE ts BETWEEN '2008-08-08 08:08:08' AND '2009-09-09 09:09:09';
SELECT COUNT(*) FROM t1 WHERE ts BETWEEN '2008/08/08 08:08:08' AND '2009/09/09 09:09:09';
SELECT COUNT(*) FROM t1 WHERE ts BETWEEN '20080808080808' AND '20090909090909';
以上三条等效;
16.判断是否为空 ISNULL,IFNULL
select if(ISNULL(name),concat(id,"....",sex),name) from t1 where id=21;
IF中ISNULL为真->concat(id,"....",sex)
IF中ISNULL为假->name
IFNULL(e1,e2)
e1==NULL ->e2
e1!=NULL -> e1
17.CASE分支
