Mysql 查询语句（工作用）

修改城市名为统一名称

//临时使用语句，和主题无关。
CREATE TABLE Count_dishesAll_Pizza (
	SELECT
		City,
		COUNT( * ) AS CountCity 
	FROM
		SSD2018 
	GROUP BY
		City 
	ORDER BY
		CountCity DESC 
	);

//修改城市名为统一名称

UPDATE SSDTotal 
SET City = REPLACE ( city, 'Shanghai', '上海' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'shanghai', '上海' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Beijing', '北京' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'beijing', '北京' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Ningbo', '宁波' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'chongqing', '重庆' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Huzhou', '湖州' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Hohhot', '呼和浩特' );
UPDATE SSDTotal 
SET City = REPLACE ( city, ' Nanjing', '南京' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'guangzhou', '广州' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Qingdao', ' 青岛' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Shenzhen', '深圳' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Chengdu', '成都' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'chengdu', '成都' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Shenyang', '沈阳' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Guangzhou', '广州' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Wuhu', '芜湖' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Hangzhou', '杭州' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Zhongshan', '中山' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Taiyuan', '太原' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Shantou', '汕头' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Wuhan', '武汉' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Jinan', '济南' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Dongguan', '东莞' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Nanning', '南宁' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'wenzhou', '温州' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'changsha', '长沙' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'zhengzhou', '郑州' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Zhuhai', '珠海' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Fuzhou', '福州' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Suzhou2', '苏州' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Wuxi', '无锡' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Tianjin', '天津' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Suzhou', '苏州' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'nanning', '南宁' );
UPDATE SSDTotal 
SET City = REPLACE ( city, '广东省广州', '广州' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Nanjing', '南京' );
UPDATE SSDTotal 
SET City = REPLACE ( city, 'Guazhou', '瓜州' );
UPDATE SSDTotal 
SET City = REPLACE ( city, '四川郫县', '郫县' );
UPDATE SSDTotal 
SET City = REPLACE ( city, '通州区', '通州' );
UPDATE SSDTotal 
SET City = REPLACE ( city, '青岛胶州', '胶州' );
UPDATE SSDTotal 
SET City = REPLACE ( city, '市', '' );

# 修改 Mysql 运行模式
```sql
//修改 SQL 运行模式
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

数据分析

/* 查看表内 Billing Data 列表。*/ 
SELECT
`Billing Date`,
COUNT( `Billing Date` ) 
FROM
	SSD2018 
GROUP BY
	`Billing Date` 

/*查询某月客户聚合销量。*/
SELECT
	SaleOffice,
	CustomerName,
	round( sum( `Sale NET Value` ), 2 ) AS '2018-08' 
FROM
	SSD2018 
WHERE
	MONTH ( `Billing Date` ) = MONTH ( '2018-08-01' ) 
	AND YEAR ( `Billing Date` ) = YEAR ( curdate( ) ) 
GROUP BY
	CustomerName,
	SaleOffice 

/*查询某月*/
SELECT
	* 
FROM
	SSD2018 
WHERE
	MONTH ( `Billing Date` ) = MONTH ( '2018-08-01' ) 
	AND YEAR ( `Billing Date` ) = YEAR ( curdate( ) )

删除数据

/* 删除重复数据*/
DELETE 
FROM
	qsr 
WHERE
	id IN ( SELECT * FROM ( SELECT max( id ) AS id FROM qsr GROUP BY shop_id HAVING count( shop_id ) > 1 ) b );
	
/*删除某时间的数据*/
DELETE
FROM SSD2018
WHERE
`Billing Date`>='2018-09-01'

多表合并

INSERT IGNORE INTO ZSM01 
SELECT * FROM ZSM02;