mysql数据库基本用法及mysql语句疑难问题处理方法

1、where and  link

SELECT * FROM `yiqi_product` WHERE cid = '116' and name LIKE '%钢结构%'

2、where的多条件查询

SELECT * FROM `yiqi_randoms` WHERE type = 'duanyu' AND `group` = '-'

3、内容替换

UPDATE yiqi_product SET name=REPLACE(name, '＊', ''); 

批量替换多个字符

update yiqi_product set 
content=replace(content,'最佳',''), 
content=replace(content,'最具',''), 
content=replace(content,'最爱','')

4、查询包含

SELECT * FROM `yiqi_article` WHERE locate('[sf]',title)>0 AND cid="4"

5、查询不包含

SELECT * FROM `yiqi_article` WHERE locate('[sf]',title)=0 AND cid="4"

6、查询不等于数据

SELECT name FROM `yiqi_product` WHERE <>195 and 196 and 199 and 198 and 219 and 200 and 201 and 203 and 204

7、查询user表中，user_name字段值重复的数据及重复次数

select user_name,count(*) as count from user group by user_name having count>1;

8、多表联查

SELECT * FROM `yiqi_article` INNER JOIN `yiqi_permanent` ON yiqi_article.aid=yiqi_permanent.objectid WHERE aid="3"

9、根据条件更新数据

UPDATE yiqi_article SET cid = '226' WHERE title LIKE '%aaa%'

10、根据字数筛选数据

SELECT * FROM `yiqi_randoms` WHERE type="duanyu" and length(value)<=150

11、批量给某一列所有值加前缀

update `site_article` set `title` = concat('[k1]',convert(title,char));

update `yiqi_article` set `name` = concat('[sf][fz]',convert(name,char)) where cid="";

update yiqi_permanent set filename = concat (filename,.1) WHERE perid>45606 AND perid <45693

12、批量更新

update han_randoms set count=0;

UPDATE site_article SET `allshow` =REPLACE(allshow, '0', '1'); 

13、删除指定数据

DELETE FROM yiqi_randoms WHERE type='duanyu'

14、添加新字段

ALTER TABLE `han_users` ADD `userback` LONGTEXT NULL AFTER `status`;

15、修改表名命令

rename table 旧表名 to 新表名

16、指定搜索范围

update yiqi_permanent set filename = concat (filename,.1) WHERE perid>45606 AND perid <45693

17、mysql语句中有引号的问题解决方案

方法：将单引号（'）和双引号（"）替换成转移单引号（\'）和转移双引号（\"）即可

UPDATE zblog_post SET log_Content=REPLACE(log_Content, "class=\"tt_article5_pic\" style=\"text-align: center;\"", "class=\"tt_article5_pic\"")

18、赋值（把seotitle里面的数据赋值给title）

UPDATE han_article SET title = seotitle

19、指定列按照中文字数进行排序

SELECT * FROM `yiqi_randoms` WHERE `group` = "分组名称" ORDER BY length(`value`) DESC