常用的基础sql语句

#最近温习一下sql语句整理一下做为php经常会使用的一些语句
#代码如下：

#查询类语句--where子语句查询及常见函数 sum,avg,max,min,count,UNIX_TIMESTAMP,FROM_UNIXTIME使用

SELECT * FROM `os_article`;#查询所有数据(数据大时慎用！)
SELECT * FROM `os_article` WHERE title like "%bbb"; #like 模糊查询 左匹配 将搜索以字母bbb结尾的所有字符串
SELECT * FROM `os_article` WHERE title like "bbb%"; #like 模糊查询 右匹配 将搜索以字母bbb开头的所有字符串
SELECT *,FROM_UNIXTIME(UNIX_TIMESTAMP(create_time)) FROM `os_article` WHERE title like "%bbb%"; #like 模糊查询 全匹配 将搜索以字母bbb开头或者结尾的所有字符串
SELECT FIND_IN_SET('2','1,2,22,4');#FIND_IN_SET应用场景
SELECT * FROM `os_article` WHERE FIND_IN_SET('nginx', title); #查询内容必须有逗号
SELECT * FROM `os_article` WHERE id = 1 or id=3; #普通带条件精确查询。id in(1,3)
SELECT * FROM `os_article` WHERE cid=6 or cid=7 and piece=1;#查询cid为6,7的数据中 piece是1的数据
SELECT count(title) FROM `os_article`; #统计表总行数
SELECT sum(piece) as sum_piece FROM `os_article` where id in(1,3); #求和;
SELECT avg(piece) as avg_piece FROM `os_article` where id in(1,2); #求平均值;
SELECT max(piece) as max_piece FROM `os_article` where id in(1,3); # 求最大值;
SELECT min(piece) as min_piece FROM `os_article` where id in(1,3);#求最小值;
SELECT * FROM `os_article` WHERE piece = (SELECT min(piece) as min_piece FROM `os_article` where id in(1,3)) and id in(1,3); # 求最小值最取出最小值所在的行的所有列的值;

#查询类语句之group by子语句及group_concat,having用法

SELECT * FROM `os_article` GROUP BY cid;#分组语句
SELECT sum(piece) as cid_count,cid FROM `os_article` GROUP BY cid; #查询每个cid分组中piece值的总和！
SELECT sum(piece) as cid_count,cid FROM `os_article` GROUP BY cid HAVING cid_count =1 ORDER BY cid desc; #查询每个cid分组中piece值的总和！
SELECT cid,group_concat(id) as id_string FROM `os_article` GROUP BY cid; #查询每个cid分组中有哪些id！
SELECT cid,group_concat(id separator ':') as id_string FROM `os_article` GROUP BY cid; #查询每个cid分组中有哪些id！

#LEFT JOIN ON 子语句

SELECT os1.name,os2.id,os2.cid,os2.title FROM `os_category` as os1 LEFT JOIN `os_article` as os2 ON os1.id = os2.cid;
SELECT os1.name,os2.id,os2.cid,os2.title FROM `os_category` as os1 LEFT JOIN `os_article` as os2 ON os2.cid = os1.id;
#更新类语句

UPDATE `os_article` SET title= 'nginx是一种方便的代理' WHERE title like '%nginx%'; #把整个字段全部替换;
UPDATE `os_article` SET title = 'aaa' WHERE id =1; #把整个字段全部替换;
UPDATE `os_article` SET `title`=replace(`title`, 'nginx', 'mysql')  where title like '%nginx%'; # replace(列名称，要替换列中的字符串值，替换成什么样的值)
SET @num := 0;
update `os_article` set piece=(@num := @num + 1) where cid=9 ORDER BY id ASC;#字段增段加1

#插入类语句

INSERT INTO `os_article`(cid,title) value(9,'cccc');#指定字段与值，增加一条数据进表。
INSERT INTO `os_article`(cid,title) (SELECT id,name FROM `wnw_test`); #查询另一张表的数据，一次性插入数据。

#删除语句
TRUNCATE `wnw_test`;#删除全表数据---id递增重置为1
DELETE FROM `wnw_test` #删除全表数据---id递增值不重置

#查看视频有详细的运行sql过程

常用的基础sql语句