MySQL查询语句大全

记录一下，博主在工作中遇到的各种SQL问题和心得，如有不对，敬请各位大神指正！

```sql

<!-- 查询多个id对应内容 -->

SELECT id,classid,title,titleurl FROM news WHERE classid in(102,126) or classid BETWEEN 131 and 155 ORDER BY id desc;

<!-- 模糊查询更新url格式 -->

UPDATE news_check set titleurl = CONCAT('/',id,'.html') WHERE titleurl like "%/oldhtml%" ORDER BY id DESC LIMIT 10;

<!-- 根据id回写其他关联字段 -->

UPDATE news_check set titleurl = CONCAT('/',id,'.html') WHERE classid =101 AND titleurl='' ORDER BY id DESC LIMIT 1;

<!-- 删除指定id内容 -->

DELETE FROM news_check WHERE (classid =3 and titleurl LIKE "%/xuexi%") LIMIT 1;

<!-- 联表更新titleurl字段值 -->

UPDATE `news` n

join newsclass c on n.classid=c.classid

set titleurl = CONCAT('www.baidu.com/',c.classpath,'/',n.id,'.html') WHERE id=570;

<!-- 联表写入字段值 -->

insert into news_index

(

  `id` ,

  `classid`,

  `checked`,

  `newstime`,

  `truetime`,

  `lastdotime`,

  `havehtml`

)

SELECT

  `id` ,

  `classid`,

   0 `checked`,

  UNIX_TIMESTAMP() `newstime`,

  UNIX_TIMESTAMP() `truetime`,

  UNIX_TIMESTAMP() `lastdotime`,

  0 `havehtml`

FROM `news_check`;

<!-- 联表更新指定字段值。用的inner join on -->

UPDATE `news` n

join newsclass c on n.classid=c.classid

set titleurl = CONCAT('www.baidu.com/',c.classpath,'/',n.id,'.html') WHERE id=570;

<!-- 联表查询指定字段 别名显示 -->

SELECT

i.tagid itag_id,

t.tagid tags_id,

t.tagname

FROM dp_itag i 

JOIN newstags t ON i.tagid = t.tagid;

<!-- 查询表A中有，表B中没有的数据：LEFT JOIN ON -->

SELECT a.* FROM a LEFT JOIN b ON a.a_id = b.b_id WHERE b.b_id IS NULL;

<!-- 查询表A中没有，表B中有的数据： RIGHT JOIN ON-->

SELECT b.* FROM a RIGHT JOIN b ON a.a_id = b.b_id WHERE a.a_id IS NULL;

<!-- 查询newstags中dp_itag没有的id数据，并写入到dp_itag -->

<!-- 注意:写入的字段要和查询出来的字段一一对应 -->

insert into dp_itag

(

  `tagid`

)

SELECT a.tagid FROM newstags a LEFT JOIN dp_itag b ON a.tagid = b.tagid WHERE b.tagid IS NULL ORDER BY tagid asc;

<!-- 模糊匹配 批量替换图片路径地址 -->

UPDATE `news` set titlepic = CONCAT('www.baidu.com',titlepic) where titlepic like "%/d/file%";

<!-- 更新news表字段信息 -->

UPDATE news set filename=id, titleurl = CONCAT(titleurl,id,'.html'), truetime=UNIX_TIMESTAMP(), lastdotime=UNIX_TIMESTAMP(), newstime=UNIX_TIMESTAMP() WHERE classid=23 LIMIT 1;

<!-- 同步news_index表字段信息 -->

INSERT INTO `zkaoedu`.`news_index`(`id`, `classid`, `checked`, `newstime`, `truetime`, `lastdotime`, `havehtml`) VALUES (20, 23, 1, 1620461227, 1620461227, 1620461227, 1);

<!-- mysql 查询转义字符处理，like查询1个\ 替换成4个\ ， =查询时一换二 -->

select * from table where text like '\\\\abc';

<!-- mysql replace时转义字符处理，like查询1个\ 替换成2个\ 即可-->

<!-- 其中table便为对应的表明，newstext为要更新的字段，REPLACE中第二个参数为将要被替换的内容，第三个字段为替换成的新内容 -->

UPDATE table set newstext = REPLACE(newstext,'src=\\"d/file','src=\\"/d/file') WHERE id=1;

<!-- 修改数据表自增id默认值 -->

ALTER TABLE `表名` AUTO_INCREMENT=1;

```