SQL书写规范及常用SQL语句

常用的查询语句

SELECT * FROM 表名 【WHERE 条件 或 GROUP BY 字段名 HAVING】 ORDER BY 字段名 排序方式 LIMIT 初始值，数量;

SELECT fname,id FROM ttt WHERE id=1;
SELECT fname,id FROM ttt WHERE fname='bbb';
SELECT fname,id FROM ttt WHERE fname='bbb' OR fname='aaa';
SELECT fname,id FROM ttt WHERE fname IN('bbb' ,'aaa');
SELECT fname,id FROM ttt WHERE fname LIKE '%b%';
SELECT fname,id FROM ttt WHERE fname LIKE '%b';
SELECT fname,id FROM ttt WHERE fname LIKE '%b_';
SELECT Fname from ttt where id>1;
SELECT fname FROM ttt WHERE id>1 AND id<3; == SELECT fname FROM ttt WHERE id=2;
SELECT * FROM ttt ORDER BY id ASC或者DESC --排序功能，ASC正序，DESC倒序
SELECT * FROM ttt ORDER BY RAND() --随机取值
SELECT * FROM ttt ORDER BY RAND() LIMIT 1; --随机并且每次只显示一条
SELECT * FROM ttt LIMIT 1; --只显示一条
SELECT * FROM ttt ORDER BY id DESC LIMIT 1;
SELECT * FROM ttt LIMIT 3,2;
SELECT * FROM ttt ORDER BY id DESC LIMIT 3,1;
SELECT * FROM ttt ORDER BY id DESC,Fnum ASC;
SELECT * FROM test ORDER BY CONVERT(Fname USING gb2312); 中文排序
SELECT * FROM TTT GROUP BY Fnum;
SELECT A.Fid,B.Fname,B.Fsex FROM product_category_test A,t2 B WHERE A.Fid=B.Fid;

类似PHP的函数in_array()的功能
SELECT * FROM ttt WHERE FIND_IN_SET(1,Fcateids);

SELECT COUNT(Fid) AS totals FROM ttt;
AS后面的名称自定义，即别名

SELECT * FROM tb_school WHERE Fid IN(select fid from tb_users);

添加语句

INSERT INTO ttt(fname,fnum) VALUES('小崔',1);
INSERT INTO ttt(fname,fnum) VALUES('小王',1);
INSERT INTO ttt(fname,fnum) VALUES('小李',1);

更新语句

UPDATE ttt SET fname='小崔' WHERE 条件;
UPDATE ttt SET fname='小崔';
UPDATE ttt SET fname='cccc' WHERE id=4; --不存在的数据，表不会被做任何更改
UPDATE ttt SET Fnum=Fnum+1 WHERE id=1;

删除语句

DELETE FROM 表名 WHERE条件 --清空表，删除数据，不是删除表

DROP 表名 --删除表

联合查询

SELECT t.tid,t.subject,p.message FROM pre_forum_thread as t LEFT JOIN pre_forum_post as p on t.tid = p.tid WHERE (t.highlight > 0 AND t.subject <>'') order by t.tid DESC