設為首頁

收藏本站

導覽首頁 | 新登場    ◇聯盟溫泉 | 民宿 | 人力銀行 | 女性 |

類型:mysql_article

Mysql中筛选数据常用的几个语句
使用的SQL语句在此记录一下:

查询字段长度大于某值

select * from article where length(title) > 50
查询字段长度小于某值

select * from article where length(title) < 24
将字段里的英文字母转大写

UPDATE article SET title = UCASE(title)
去掉字段内容首尾空格

update article set title = rtrim(ltrim(title))
批量替换某字段里的字符串

UPDATE article SET `title` = REPLACE(`title`,'aaa','AAA')
筛选某字段重复的内容(保留较新)

SELECT * FROM article WHERE ID not IN (SELECT MAX(Id) FROM article GROUP BY `title`)
筛选某字段重复的内容(保留较旧)

SELECT * FROM article WHERE ID not IN (SELECT MIN(Id) FROM article GROUP BY `title`)
筛选某字段为NULL的内容

select * from article where `title` IS null
删除筛选出来的查询结果

delete from article where id in (
  select e. id from (SELECT * FROM article WHERE ID not IN (SELECT MAX(Id) FROM article GROUP BY `title`)) e
)
删除大量数据后将自增ID恢复到某值

alter table article AUTO_INCREMENT = 123456
……待续

104休閒信箱 2.3.0 © 104mm.com 2001 - 2021. 您尚未登錄
Page generated in 0.02476192 seconds with 3 Queries