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
欢迎访问每天进步网:https://meitianjinbu.cn/