1.按时间拼接字符串修改库里面字段
UPDATE communitytemp set dbName = CONCAT(‘sofang20_house_‘,year(timeCreate)),tbName = CONCAT(‘houserent_‘,extract(YEAR_MONTH from timeCreate)) WHERE saleRentType = ‘rent‘;
UPDATE communitytemp set dbName = CONCAT(‘sofang20_house_‘,year(timeCreate)),tbName = CONCAT(‘housesale_‘,extract(YEAR_MONTH from timeCreate)) WHERE saleRentType = ‘sale‘
2.联表根据a表字段值修改b表字段
update communitytemp as a INNER JOIN houserent as b on a.hId = b.id set a.dbName = ‘sofang20_oldhouse‘ ,a.tbName = ‘houserent‘ WHERE a.saleRentType = ‘rent‘;
3.分表语句
DROP TABLE if exists student_key_innodb;
CREATE TABLE student_key_innodb(
id int UNSIGNED not null auto_increment,
name varchar(16) not null DEFAULT ‘‘,
birthday date not null DEFAULT ‘0000-00-00‘,
timecreate timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT ‘创建时间‘,
timeupdate timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT ‘更新时间‘,
primary key (id,birthday)
)ENGINE=INNODB DEFAULT CHARSET=UTF8
#PARTITION by key(id) partitions 12;#key 取余分表
#PARTITION by hash(month(birthday)) partitions 12; #hash取余分表
#range,list
#rang范围年分表
#partition by range(year(birthday))(
# partition age_old VALUES LESS THAN (1970),
# partition age_70 VALUES LESS THAN (1980),
# partition age_80 VALUES LESS THAN (1990),
# partition age_90 VALUES LESS THAN (2000),
# partition age_new VALUES LESS THAN MAXVALUE
#)
#rang范围月分表
PARTITION BY LIST(MONTH(birthday))(
PARTITION p_chun VALUES IN (3,4,5),
PARTITION p_xia VALUES IN (6,7,8),
PARTITION p_qiu VALUES IN (9,10,11),
PARTITION p_dong VALUES IN (12,1,2)
);
4.嵌套子查询
SELECT c.id from sofang20_newhouse.community as c WHERE c.id not in (SELECT a.id from sofang20_newhouse.community as a INNER JOIN community as b on a.id = b.id where a.name = b.name)
5.查询一个字段中是否包含另一个字段中的值
SELECT * FROM bdcityinfo WHERE LOCATE(cityName,bdcityName)>0 or LOCATE(cityName,bddistrict)>0
6.mysql字符串截取查询
select c.id,c.name,c.fullname,c0.name as n2 from city c right join
(select * from city0 where disabled=0 and (SUBSTR(`code` FROM 5 FOR 2)=‘00‘ and SUBSTR(`code` FROM 3 FOR 4)<>‘0000‘
or substring(`code`, 1, 4) in (select substring(`code`, 1, 4) from city0 where (substring(`code`, 3, 4) = ‘9000‘) or code=‘500200‘)) and substring(`code`, 3, 4)<>‘9000‘ and `code` <> ‘500200‘) c0
on c.fullname = c0.name;
7.同一个表查询指定字符串是否存在，存在修改
UPDATE sofang20_house_2017.houserent_201703
SET state = 0
WHERE
id IN (
SELECT
a.id from
(
SELECT
id
FROM
sofang20_house_2017.houserent_201703
WHERE
state = 1
AND (
locate(‘升值潜力无限‘, title) > 0
OR locate(‘商住两用‘, title) > 0
OR locate(‘学区房‘, title) > 0
OR locate(‘商住两用‘, title) > 0
OR locate(‘买一层送一层‘, title) > 0
OR locate(‘全款收房‘, title) > 0
OR locate(
‘升值潜力无限‘,
`describe`
) > 0
OR locate(‘商住两用‘, `describe`) > 0
OR locate(‘学区房‘, `describe`) > 0
OR locate(‘商住两用‘, `describe`) > 0
OR locate(
‘买一层送一层‘,
`describe`
) > 0
)
) AS a
);
7.找到指定字符串后替换成相应字符
UPDATE houserent
SET title = REPLACE (title, ‘学区房‘, ‘***‘),
`describe` = REPLACE (
`describe`,
‘学区房‘,
‘***‘
)
WHERE
state = 1
AND (
locate(‘学区房‘, title) > 0
OR locate(‘学区房‘, `describe`) > 0
)
8.批量修改楼盘信息sql
#SELECT enterpriseshopId from sofang20_user.enterprises WHERE id = (SELECT id FROM sofang20_user.users WHERE mobile= ‘15717502687‘);
UPDATE sofang20_newhouse0.community AS a
SET a.uId = (
SELECT
id
FROM
sofang20_user.users
WHERE
mobile = ‘15717502687‘
),
a.enterpriseshopId = (
SELECT
enterpriseshopId
FROM
sofang20_user.enterprises
WHERE
id = (
SELECT
id
FROM
sofang20_user.users
WHERE
mobile = ‘15717502687‘
)
)
WHERE
a.id IN (
SELECT
b.id
FROM
(
SELECT
id
FROM
sofang20_newhouse.community
WHERE
`name` IN (
‘弘德西街好莱城‘,
‘惠天然梅岭国际‘,
‘佳兆业云顶梅溪湖‘,
‘天祥水晶湾‘,
‘奥克斯缔壹城‘,
‘恩瑞御西湖‘,
‘天麓尚层‘,
‘金科世界城‘,
‘阳光100凤凰街‘,
‘洋湖时代广场‘,
‘和顺洋湖壹号‘,
‘正旺U公馆‘,
‘裕天国际商汇中心‘,
‘东能华府‘,
‘湘江雅颂居‘,
‘五江天街‘,
‘万科魅力之城‘,
‘新世界广场‘,
‘金谷豪庭‘,
‘香江红星家具建材博览中心‘,
‘高岭国际商贸城‘,
‘福晟钱隆国际‘,
‘富兴世界金融中心‘,
‘泊富国际广场‘ ‘中茂城‘,
‘新城国际花都‘,
‘润和紫郡‘,
‘澳海澜庭‘,
‘澳海谷山府‘,
‘润和又一城‘,
‘悦禧国际山庄‘,
‘金地三千府‘,
‘富基世纪公园‘,
‘荣盛岳麓峰景‘,
‘金桥国际市场集群‘,
‘澳海望洲府‘
)
) AS b
);
UPDATE sofang20_newhouse.community AS a
SET a.state = 1,
a.uId = (
SELECT
id
FROM
sofang20_user.users
WHERE
mobile = ‘15717502687‘
),
a.enterpriseshopId = (
SELECT
enterpriseshopId
FROM
sofang20_user.enterprises
WHERE
id = (
SELECT
id
FROM
sofang20_user.users
WHERE
mobile = ‘15717502687‘
)
)
WHERE
a.id IN (
SELECT
b.id
FROM
(
SELECT
id
FROM
sofang20_newhouse.community
WHERE
`name` IN (
‘弘德西街好莱城‘,
‘惠天然梅岭国际‘,
‘佳兆业云顶梅溪湖‘,
‘天祥水晶湾‘,
‘奥克斯缔壹城‘,
‘恩瑞御西湖‘,
‘天麓尚层‘,
‘金科世界城‘,
‘阳光100凤凰街‘,
‘洋湖时代广场‘,
‘和顺洋湖壹号‘,
‘正旺U公馆‘,
‘裕天国际商汇中心‘,
‘东能华府‘,
‘湘江雅颂居‘,
‘五江天街‘,
‘万科魅力之城‘,
‘新世界广场‘,
‘金谷豪庭‘,
‘香江红星家具建材博览中心‘,
‘高岭国际商贸城‘,
‘福晟钱隆国际‘,
‘富兴世界金融中心‘,
‘泊富国际广场‘ ‘中茂城‘,
‘新城国际花都‘,
‘润和紫郡‘,
‘澳海澜庭‘,
‘澳海谷山府‘,
‘润和又一城‘,
‘悦禧国际山庄‘,
‘金地三千府‘,
‘富基世纪公园‘,
‘荣盛岳麓峰景‘,
‘金桥国际市场集群‘,
‘澳海望洲府‘
)
) AS b
);
10.跟新分销商400电话
UPDATE sofang20_newhouse.community AS b
INNER JOIN sofang20_user.promotionmarketing_extcode AS a on b.id = a.communityId
SET b.consultingMobile = CONCAT(
a.bigcode400,
‘转‘,
a.extcode
)
WHERE
a.uId = 1927455
AND a.bigcode400 = 4008196888
AND b.cityId = 101
AND b.consultingMobile = ‘‘
11.按城市批量修改楼盘用户id和分销商id
UPDATE sofang20_newhouse0.community AS a
SET a.uId = (
SELECT
id
FROM
sofang20_user.users
WHERE
mobile = ‘18595253788‘
),
a.enterpriseshopId = (
SELECT
enterpriseshopId
FROM
sofang20_user.enterprises
WHERE
id = (
SELECT
id
FROM
sofang20_user.users
WHERE
mobile = ‘18595253788‘
)
),
a.auditState = 100
WHERE
a.id IN (
SELECT
b.id
FROM
(
SELECT
id
FROM
sofang20_newhouse.community
WHERE
cityId = 49
) AS b
);
UPDATE sofang20_newhouse.community AS a
SET a.uId = (
SELECT
id
FROM
sofang20_user.users
WHERE
mobile = ‘18688615858‘
),
a.enterpriseshopId = (
SELECT
enterpriseshopId
FROM
sofang20_user.enterprises
WHERE
id = (
SELECT
id
FROM
sofang20_user.users
WHERE
mobile = ‘18688615858‘
)
),
a.state = 2
WHERE
a.id IN (
SELECT
b.id
FROM
(
SELECT
id
FROM
sofang20_newhouse.community
WHERE
cityId = 144
) AS b
);
12.更新pc和app400电话
UPDATE sofang20_oldhouse.foreclosurehouse
SET mobile400App = CONCAT(
‘4008199000‘
‘转‘,
SUBSTR(mobile400,locate(‘转‘, mobile400)+1,10)
)
WHERE locate(‘4008196888‘, mobile400) > 0
ALTER TABLE `houserentimage_201708` ADD INDEX `ix_rentImage_filename` (`fileName`) USING HASH ;
13.app400电话
UPDATE sofang20_newhouse0.community AS b
INNER JOIN sofang20_user.promotionmarketing_extcode AS a ON b.id = a.communityId
SET b.consultingMobileApp = CONCAT(
a.bigcode400,
‘转‘,
a.extcode
)
WHERE
a.bigcode400 = 4008199000
AND b.consultingMobileApp = ‘‘
UPDATE sofang20_newhouse.community set state=1 WHERE id in (SELECT a.id from (SELECT id from sofang20_newhouse.community WHERE state = 3) as a);
//修改楼盘户型特色解析
UPDATE communityroom SET feature = ‘‘ WHERE id in (SELECT c.id from (SELECT a.id FROM `communityroom` as a LEFT JOIN community as b ON a.communityId = b.id WHERE b.cityId = 49 and a.feature <> ‘‘) as c);
//修改楼盘点评
UPDATE communitycomment SET `comment` = ‘‘ WHERE id in (SELECT c.id from (SELECT a.id FROM `communitycomment` as a LEFT JOIN community as b ON a.communityId = b.id WHERE b.cityId = 49 and a.`comment` <> ‘‘) as c);
//聚合查询
SELECT
a.*,(SELECT count(b.id) FROM businessarea as b WHERE b.cityAreaId = a.id and b.deleteState = 1 ) as num
FROM
(
SELECT
c. NAME AS cname,
ca. NAME,
ca.id,
c.id AS cid
FROM
`city` AS `c`
LEFT JOIN `cityarea` AS `ca` ON `ca`.`cityId` = `c`.`id`
WHERE
`c`.`id` IN (0, 1, 101)
AND `ca`.`deleteState` = 1
GROUP BY
`ca`.`name`
ORDER BY
`ca`.`timeLocUpdate` DESC
) AS a
//If 查询
SELECT
a.id,
a.realName,
a.mobile,
a.company,
b.`name` AS cityAreaName,
c.`name` AS businessName,
CASE mainbusiness
WHEN ‘1|2|3|4|5‘ THEN
‘新房|二手房|租房|写字楼|商铺‘
WHEN ‘2|3|4|5‘ THEN
‘二手房|租房|写字楼|商铺‘
WHEN ‘1|2|3|4‘ THEN
‘新房|二手房|租房|写字楼‘
WHEN ‘1|2‘ THEN
‘新房|二手房‘
WHEN ‘1|3‘ THEN
‘新房|租房‘
WHEN ‘1|4‘ THEN
‘新房|写字楼‘
WHEN ‘1|5‘ THEN
‘新房|商铺‘
WHEN ‘2|3‘ THEN
‘二手房|租房‘
WHEN ‘2|4‘ THEN
‘二手房|写字楼‘
WHEN ‘2|5‘ THEN
‘二手房|商铺‘
WHEN ‘3|4‘ THEN
‘租房|写字楼‘
WHEN ‘3|5‘ THEN
‘租房|商铺‘
WHEN ‘3|4‘ THEN
‘租房|写字楼‘
WHEN ‘4|5‘ THEN
‘写字楼|商铺‘
WHEN ‘1|2|3‘ THEN
‘新房|二手房|租房‘
WHEN ‘1|2|4‘ THEN
‘新房|二手房|写字楼‘
WHEN ‘1|2|5‘ THEN
‘新房|二手房|商铺‘
WHEN ‘1|3|4‘ THEN
‘新房|租房|写字楼‘
WHEN ‘1|3|5‘ THEN
‘新房|租房|商铺‘
WHEN ‘1|4|5‘ THEN
‘新房|写字楼|商铺‘
WHEN ‘2|3|4‘ THEN
‘二手房|租房|写字楼‘
WHEN ‘2|3|5‘ THEN
‘二手房|租房|商铺‘
WHEN ‘3|4|5‘ THEN
‘租房|写字楼|商铺‘
WHEN ‘1‘ THEN
‘新房‘
WHEN ‘2‘ THEN
‘二手房‘
WHEN ‘3‘ THEN
‘租房‘
WHEN ‘4‘ THEN
‘写字楼‘
WHEN ‘5‘ THEN
‘商铺‘
ELSE
‘‘
END AS testCol
FROM
brokers AS a
LEFT JOIN sofang20_newhouse.cityarea AS b ON a.cityAreaId = b.id
LEFT JOIN sofang20_newhouse.businessarea AS c ON a.businessAreaId = c.id
WHERE
a.cityId = 49
AND a.idcardState = 1 ORDER BY id DESC;
/**批量更新字段**/
1、.replace into 批量更新
replace into test_tbl (id,dr) values (1,‘2‘),(2,‘3‘),...(x,‘y‘);（慎用，必须带主键）
2. insert into test_tbl (id,dr) values (1,‘2‘),(2,‘3‘),...(x,‘y‘) on duplicate key update dr=values(dr);
3.创建临时表，先更新临时表，然后从临时表中update
create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values (0,‘gone‘), (1,‘xx‘),...(m,‘yy‘);
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;
4、使用mysql 自带的语句构建批量更新
UPDATE categories
SET dingdan = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END,
title = CASE id
WHEN 1 THEN ‘New Title 1‘
WHEN 2 THEN ‘New Title 2‘
WHEN 3 THEN ‘New Title 3‘
END
WHERE id IN (1,2,3)
//例子
$display_order = array(
1 => 4,
2 => 1,
3 => 2,
4 => 3,
5 => 9,
6 => 5,
7 => 8,
8 => 9
);
$ids = implode(‘,‘, array_keys($display_order));
$sql = "UPDATE categories SET display_order = CASE id ";
foreach ($display_order as $id => $ordinal) {
$sql .= sprintf("WHEN %d THEN %d ", $id, $ordinal);
}
$sql .= "END WHERE id IN ($ids)";
echo $sql;
//存储过程
DROP PROCEDURE IF EXISTS updateSubway;
DELIMITER //
CREATE PROCEDURE updateSubway()
BEGIN
DECLARE t_error INTEGER DEFAULT 0;
DECLARE sublineId INT;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
START TRANSACTION;
INSERT INTO `sofang2017_newhouse`.`subwayline` ( #插入地铁14号线西段
`id`,
`cityId`,
`name`,
`timeUpdate`,
`centerLng`,
`centerLat`,
`centerLevel`,
`shorthand`,
`description`,
`lineprofile`
)
VALUES
(
NULL,
‘1‘,
‘地铁14号线西段‘,
‘2016-08-03 16:42:43‘,
116.259835,
39.870755,
‘13‘,
‘dt14hxxd‘,
‘地铁14号线西段(西局-张郭庄)‘,
NULL
);
SET sublineId = (SELECT LAST_INSERT_ID());
UPDATE `sofang2017_newhouse`.subwayline SET `name` = ‘地铁14号线东段‘,description=‘地铁14号线东段(北京南站--善各庄)‘,shorthand=‘dt14hxdd‘ WHERE id = 11;
UPDATE `sofang2017_newhouse`.subwaystation SET lineId = sublineId,`lineName`=‘地铁14号线西段‘ WHERE lineId = 11 AND `name` in (‘西局‘,‘七里庄‘,‘大井‘,‘郭庄子‘,‘大瓦窑‘,‘园博园‘,‘张郭庄‘);
UPDATE `sofang2017_newhouse`.subwaystation SET `lineName`=‘地铁14号线东段‘ WHERE lineId = 11;
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
START TRANSACTION;
UPDATE sofang2017_oldhouse.community
SET subwayLineId = REPLACE (
subwayLineId,
‘11‘,
sublineId
),
subwayLine = REPLACE (
subwayLine,
‘地铁14号线‘,
‘地铁14号线西段‘
)
WHERE
id IN (
SELECT
a.id
FROM
(
SELECT
id
FROM
sofang2017_oldhouse.community
WHERE
cityId = 1
AND locate(11, subwayLineId)>0
AND (
locate(238, subwayStationId) > 0
OR locate(242, subwayStationId) > 0
OR locate(239, subwayStationId) > 0
OR locate(241, subwayStationId) > 0
OR locate(243, subwayStationId) > 0
OR locate(237, subwayStationId) > 0
OR locate(240, subwayStationId) > 0
)
) AS a
);
UPDATE sofang2017_oldhouse.community
SET subwayLine = REPLACE (
subwayLine,
‘地铁14号线‘,
‘地铁14号线东段‘
)
WHERE
id IN (
SELECT
a.id
FROM
(
SELECT
id
FROM
sofang2017_oldhouse.community
WHERE
cityId = 1
AND locate(11, subwayLineId)>0
) AS a
);
IF t_error = 1 THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
select t_error;
END;
//
DELIMITER;
CALL updateSubway();
//400通话统计
SELECT
dni AS ‘被叫号码‘,
extcode AS ‘分机号‘,
ani AS ‘呼叫电话‘,
CONCAT(proname, ‘,‘, cityname) AS ‘呼叫地区‘,
callresultname AS ‘呼叫结果‘,
callertime as ‘通话时长(秒)‘,
pickupdate as ‘通话开始时间‘
FROM
promotionmarketing_statistics
WHERE
custid IN (14757346, 14757347);
//if语句
SELECT
id,
IF (
LENGTH(roomStr) = 7,
CONCAT(
substring(roomStr, 1, 3),
substring(roomStr, 6,2),
substring(roomStr, 4, 2)
),
CONCAT(
substring(roomStr, 1, 3),
substring(roomStr, 6, 2),
substring(roomStr, 4, 2),
substring(roomStr, 8)
)
) FROM sofang20_house_2018.housesale_201803
WHERE
tpId = 0 AND LENGTH(roomStr) >= 7 AND UNIX_TIMESTAMP(timeCreate) < UNIX_TIMESTAMP(‘2018-03-19 17:00:00‘) ORDER BY id DESC
UPDATE housesale_201803
SET roomStr =
IF (
LENGTH(roomStr) = 7,
CONCAT(
substring(roomStr, 1, 3),
substring(roomStr, 6,2),
substring(roomStr, 4, 2)
),
CONCAT(
substring(roomStr, 1, 3),
substring(roomStr, 6, 2),
substring(roomStr, 4, 2),
substring(roomStr, 8)
)
)
WHERE
tpId = 0 AND LENGTH(roomStr) >= 7 AND UNIX_TIMESTAMP(timeCreate) < UNIX_TIMESTAMP(‘2018-03-19 17:00:00‘) ORDER BY id DESC;
#创建用户并授权
1.创建用户
CREATE USER ssoUser@localhost IDENTIFIED BY ‘MxdpiLEjdfk2VsrG‘;
2.授权
说明：给主机为192.168.10.1的用户john分配可对数据库test的hr表进行select,insert,update,delete,create,drop等操作的权限，并设定口令为123。
grant all privileges on test.* to joe@192.168.10.1 identified by ‘123‘;
说明：给主机为192.168.10.1的用户john分配可对数据库test所有表进行所有操作的权限，并设定口令为123。
grant all privileges on *.* to john@192.168.10.1 identified by ‘123‘;
说明：给主机为192.168.10.1的用户john分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。
grant all privileges on *.* to ssoUser@localhost identified by ‘MxdpiLEjdfk2VsrG‘;
#sql语句导出csv文件
mysql -hphpad_db_mysql_offline_s01 -P3306 --default-character-set=gbk -ulinkstars_user -p linkstarsdb -e "select a.goods_id,a.goods_name,sum(a.goods_count) as count,sum(a.goods_count*a.goods_price),sum(a.commission_real),a.commission_type,a.commission_type_level_2,a.commission_type_level_3 from cps_order_detail_2018_1 as a inner join cps_order_2018_1 as b on a.order_id=b.id and a.adv_uid = 600233 and a.uid = 600223 and a.goods_name like ‘%床垫%‘ and a.order_time >= UNIX_TIMESTAMP(‘2018-01-01 00:00:00‘) and a.order_time < UNIX_TIMESTAMP(‘2018-07-01 00:00:00‘) and b.status != -1 group by goods_id order by count desc"> /data/tmp/chuangdian1.csv
原文：https://www.cnblogs.com/dfh168/p/10721847.html