mysql 常用语句

mysql函数参考-https://www.yiibai.com/mysql/functions.html

---

mysql group by 报错：ONLY_FULL_GROUP_BY

select @@global.sql_mode; # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

 # 删除 ONLY_FULL_GROUP_BY
set @@global.sql_model='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

mysql 常用语句

字符串包含, 参考链接: https://www.cnblogs.com/ClassNotFoundException/p/8459044.html
select * from 表名 where locate(字符,字段);

表聚合(自创名), 参考链接: https://www.runoob.com/mysql/mysql-join.html
select t1.xx, t2.xx from t1 left join t2 on t1.xx=t2.xx;

查询当天的记录量-组合-包含

select concat(current_date,' ', current_time);
select * from mytable where timeInfo like concat(current_date, '%') order by timeinfo desc;

找出字段带有字母的记录

-- 找出字段带有字母的记录
select * from tableName where clientPhone REGEXP '[[:alpha:]]';

找出字段长度等于7的记录

-- 找出字段长度等于7的记录
select * from tableName where length(clientPhone)=7 and clientId like '5%';

组合带区号的电话号码

-- 提取字段前3个字符
select left(clientId, 3) from tableName where length(clientPhone)=7 and clientId like '5%';

-- 组合带区号的电话号码
select concat('0', left(clientId, 3), '-', clientPhone)  from tableName where length(clientPhone)=7 and clientId like '5%'

如果col1为空则取col2的语句

SELECT id, 
case 
	when col1 is not null then col1 else col2
end
as colNew
from table04_house;

进阶，

如果clo1不为空，则截取 元 之前的字符，
如果clo1为空，则截取clo2 元 之前的字符，
否则 为空

SELECT houseid,
case
	when price like '%元%' then substring_index(price, '元', 1)
	when price_company like '%元%' then  substring_index(price_company, '元', 1)
	else ''
end as price_new
FROM table03_house;

删除重复字段

--- 新建pk列，默认值为 ''
update myTable set pk='';

--- 修改pk列='公司名_职位名_薪资'
update myTable set pk=concat(name, '_', title, '_', salary) where pk='';

--- 遍历pk列，找到重复行的最小的id
select min(id) from myTable group by pk;

--- 删除整个表中id不在最小id列的记录
delete from myTable where id not in (select min(id) from myTable group by pk);
-- 我这里遇到error-"1093 - You can't specify target table 'table_zhaopin_baidu_jobinfo' for update in FROM clause"

-- 经过[查询](https://blog.csdn.net/qq_29672495/article/details/72668008，说借用结果表，于是有了下面这行
delete from myTable where id not in (select newid from (select min(id) as newid from myTable group by pk));

-- error - "1248 - Every derived table must have its own alias"，说每个结果集都应该有别名
delete from myTable where id not in (select newid from (select min(id) as newid from myTable group by pk)t);

复杂语句

SELECT * from (
	SELECT * from table_xyc WHERE donghao like '%18%'
)t1 LEFT JOIN (
	SELECT * from dx_xyc WHERE addr like '祥源城18幢%'
)t2 
on LOCATE(t1.lbPartNO,t2.addr)
ORDER BY t1.lbPartNO;

字符串拼接，

1、http://c.biancheng.net/mysql/concat.html
2、https://www.cnblogs.com/lanqingzhou/p/8275399.html

SELECT CONCAT('MySQL','5.7'); # MySQL5.7
SELECT CONCAT('MySQL',NULL);  # Null

字符串分割，https://www.cnblogs.com/staticed/p/8549230.html

str = 'www.baidu.com';
SELECT substring_index('www.baidu.com','.', 1);    #www
SELECT substring_index('www.baidu.com','.', 2);    #www.baidu 
SELECT substring_index('www.baidu.com','.', -1);   #com 
SELECT substring_index('www.baidu.com','.', -2);   #baidu.com 
SELECT substring_index(substring_index('www.baidu.com','.', -2), '.', 1);  #baidu

字符串替换，https://blog.csdn.net/pan_junbiao/article/details/86524612

SELECT REPLACE('www.baidu.com','baidu','aliyun');         # www.aliyun.com
SELECT REPLACE('www.baidu.com','.baidu.','-aliyun-');    # www-aliyun-com

带变量的字符串查询

set @tempName='世界花园';
SELECT * from table01_project where projectName like CONCAT(@tempName, '%');

分组统计个数

select num,count(*) AS counts from test_a GROUP BY num;

# 分类统计 # https://www.cnblogs.com/lr393993507/p/9791907.html
select mc,
	count(case when xh='大号' then 1 end) as 大号,
	count(case when xh='中号' then 1 end) as 中号,
	count(case when xh='小号' then 1 end) as 小号
from tb_test_count
group by mc

查询分组平均值，并保留2位小数倒序显示

select buidingID, round(avg(price), 2) as price_avg
 from tableInfo 
 group by buildingID
order by price_avg desc;

带变量的字符串修改

set @tempName='世界花园';
update table01_project set projectName=@tempName where projectName like CONCAT(@tempName, '%');

varchar类型转换int类型

select cast(price as DECIMAL(10, 2)) as price;
可能会遇到Incorrect DECIMAL value: '0' for column '' at row -1，这是由于数据输入校验参考，
可以先将空值赋值为0：update table03 set price=0 where price is null or price='';

修改、调整 一个表内列内值的顺序

以t1为基表，t2为辅表，修改t1的值，t1、t2分别是同一个表的不同查询集

update table1 t1, table1 t2
set t1.new=t2.old
where t1.id=t2.id;

update 
phonemark_baidu t1, 
(SELECT * from phonemark_baidu where mark not like '%电信%' and other1 not like '%标记%') as t2 
set
t1.phone = t2.mark,
t1.mark = '商家',
t1.other1 = t2.phone
WHERE t1.rnum=t2.rnum;

mysql 修改行号+1

思路：
1、找出每一行的lineNo；
2、将行号+1赋值给lineNo；

因为列属性为vchar，所以要先将char转为int后才能+1 SELECT CONVERT('00032', SIGNED);=32
有因为行号的格式为000012，所以格式化int为6位填充格式的vchar SELECT LPAD(12, 6, 0);= 000012
1、查询所有行号：select lineNo from table
1.1、查询所有行号，并转为int：select CONVERT(lineNo, SIGNED) from table
1.2、将查询到的行号+1select CONVERT(lineNo, SIGNED)+1 from table
1.3、将行号+1后的int转为char LPAD((select CONVERT(lineNo, SIGNED)+1 from table), 6, 0)
2、修改行号=原始行号+1
update table set lineNo=

---

更新视图

alter view view_xyc as 
(
	select * from table_xyc	
);

创建视图

以t1为基表，t2为辅表，修改t1的值，t1、t2分别是同一个表的不同查询集

-- 创建一个视图
这个视图包含百度、搜狗、360的表
3个表具有相同列格式
rnum	phone	mark	other1	other2	other3	other4	other5	other6

1、百度表中 只要 mark 列不包含 '电信'，都属于被标记
2、360表中 只要 other2 列 中不是 '归属地数据由360手机卫士提供' , 都属于被标记
3、搜狗表中 只要 other1 列 不是'查询', 都属于被标记

以百度表为基准，聚合搜狗、360表
1、筛选出 百度标记：

SELECT * from phonemark_baidu where mark not like '%电信%';

1.1、将商家筛选出调整列顺序：
	update 
	phonemark_baidu t1, 
	(SELECT * from phonemark_baidu where mark like '0%') as t2 
	set
	t1.mark = t2.other2
	WHERE t1.rnum=t2.rnum;
	-- -- 调整手机号的列顺序

1.2、将phone写原mark，将mark写入商家，将other1写入店名(原phone)
	以t1为基表，t2为辅表，修改t1的值
	update 
	phonemark_baidu t1, 
	(SELECT * from phonemark_baidu where mark not like '%电信%' and other1 not like '%标记%') as t2 
	set
	t1.phone = t2.mark,
	t1.mark = '商家',
	t1.other1 = t2.phone
	WHERE t1.rnum=t2.rnum;
	
2、筛选出 搜狗标记：
SELECT * from phonemark_sougou where other1 not like '%查询%';

3、筛选出 360标记：
SELECT * from phonemark_360 where other2 not like '%360%';


4、聚合三个表为一个表
SELECT * from phonemark_baidu;
SELECT * from phonemark_sougou;
SELECT * from phonemark_360;

SELECT t_360.rnum, t_360.phone, t_360.mark, t_baidu.baidu, t_baidu.sougou, t_360.other2 as c360 from
phonemark_360 t_360 left JOIN
(
	SELECT t1.rnum, t1.phone, t1.mark as baidu, t_sougou.other1 as sougou from 
	phonemark_sougou t_sougou RIGHT JOIN phonemark_baidu t1
	on t1.rnum=t_sougou.rnum
) t_baidu 
on t_baidu.rnum=t_360.rnum;

---