SQL书写规范及常用SQL语句

时间：2023-03-09 16:45:26
SQL书写规范及常用SQL语句

常用的查询语句

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

SELECT fname,id FROM ttt WHERE id=;

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>;

SELECT fname FROM ttt WHERE id> AND id<; == SELECT fname FROM ttt WHERE id=;

SELECT * FROM ttt ORDER BY id ASC或者DESC --排序功能，ASC正序，DESC倒序

SELECT * FROM ttt ORDER BY RAND() --随机取值

SELECT * FROM ttt ORDER BY RAND() LIMIT ; --随机并且每次只显示一条

SELECT * FROM ttt LIMIT ; --只显示一条

SELECT * FROM ttt ORDER BY id DESC LIMIT ;

SELECT * FROM ttt LIMIT ,;

SELECT * FROM ttt ORDER BY id DESC LIMIT ,;

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(,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('小崔',);

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

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

更新语句

UPDATE ttt SET fname='小崔' WHERE 条件;

UPDATE ttt SET fname='小崔';

UPDATE ttt SET fname='cccc' WHERE id=; --不存在的数据，表不会被做任何更改

UPDATE ttt SET Fnum=Fnum+ WHERE id=;

删除语句

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 >  AND t.subject <>'') order by t.tid DESC

相关文章