MySQL的一些基本操作

2023-10-31

现在有的时候线上数据不能直接操作IDE工具，SQL是避免不了的，而且即使是开发也会用到一些语句，将常用的聚集在一起。

一字段

a 表结构修改
（1）增加字段
TABLE关键字不能少

ALTER TABLE xxx_order ADD `order_id` bigint(20) DEFAULT NULL COMMENT '订单ID'

（2）修改字段
这里要注意修改字段，也要把信息给全，不能说你只想改类型，结果只给一个类型

这里修改的关键字是MODIFY

alter table xxx_order MODIFY `order_id` bigint(20) NOT NULL NULL COMMENT '订单ID'

（3）删除字段

关键字是DROP COLUMN

ALTER TABLE  xxx_order DROP COLUMN `order_id`

二表数据

a 表数据修改
基本的修改就不做示例，
这里展示的是关联另一个表的数据，然后更新表中的数据

UPDATE ams_employee_login_log aell ,ams_employee ae SET aell.merchant_id = ae.merchant_id WHERE aell.employee_id = ae.id

再来一个带case when 和ifnull的

18:16
UPDATE ams_material_inventory_order_detail amiod,
ams_stock_order aso,
ams_material_inventory_order amio 
SET amiod.congeal_count =  IFNULL(aso.congeal_count,0),
amiod.commit_stock_count = (CASE WHEN amiod.commit_stock_count IS NULL THEN NULL ELSE amiod.commit_stock_count - IFNULL(aso.congeal_count,0) END),
amiod.except_stock_count = ( amiod.except_stock_count -  IFNULL(aso.congeal_count,0) ) 
WHERE
  amiod.material_id = aso.material_id 
  AND amiod.storage_id = aso.storage_id 
  AND amiod.merchant_id = aso.merchant_id 
  AND aso.data_status = 1 
  AND amiod.data_status = 1 
  AND amio.data_status = 1 
  AND amio.inventory_status IN (0,1) 
  AND aso.congeal_count IS NOT NULL 
  AND amiod.inventory_order_id = amio.id 
  AND amiod.inventory_order_id = 298

b 表数据删除
这里说的是物理删除，平时数据尽量不要删做物理删除，但有些数据无关紧要或者是脏数据可以直接物理删除，就是从数据库删掉。

这里删除整行数据直接delete不需要像是select加个*

删除部分的话，可以是delete xxx from XXX table

DELETE FROM ams_user_login_log WHERE user_id IN (10000084502,10000084504,10000084508,10000084610)

c 表数据的新增
INSERT INTO xxx_customer (merchant_id , data_status , customer_no , external_customer_no , customer_name , bind_state , create_time , create_user , update_time , update_user , company_id , sync_rent_type , mall_account )
VALUES ( 1002013 , 1 , ‘LXCC-1000-20190514-00298’ ,‘UQFV5X’ , ‘才华有限科技有限公司’ ,2, NOW(), -1, NOW(), -1 , NOW(), -1, NULL, NULL, ‘13828842494’ );

三关于统计的部分

统计维度分组

关于函数的使用，sum，div这些统计类的函数要搭配好group by来使用有奇效，比如说我想统计各个订单类型的维度的订单价格总和：

这里涉及到一个是订单类型 order_type 一个是amount 金额

如何统计

select
order_type,
order_type_name,
sum(amount)，
from order
order by order_type

有的时候我们会涉及到多个统计维度，group by就是一个非常好的工具，配合sum div这些

统计分组后的总数

有时候我们要统计的分组的总数，假设多个维度分组出现的结果肯能很多，我们想统计这个分组的总数，这时候我们count就失效了，如下面的情况：

SELECT
	aosd.count,
	SUM(aosd.count) AS material_count,
	aosd.out_storage_amount,
	SUM(aosd.out_storage_amount) AS material_amount,
	aosd.total_amount,
	SUM(aosd.total_amount) AS total_material_amount,
	aosd.merchant_id,
	aos.company_id,
	ac.company_name,
	aos.department_id,
	ao.organization_name AS department_name,
	ae.employee_name,
	ae.employee_code,
	aos.employee_id,
	aosd.count,
	aosd.total_amount,
	amc.category_name AS category_name,
	ama.material_name,
	ama.unit,
	aos.out_storage_code,
	ast.storage_name,
	aos.out_storage_date,
	ama.brand,
	ama.model,
	amc.id
FROM
	ams_out_storage_detail aosd
LEFT JOIN ams_out_storage aos ON aosd.out_storage_id = aos.id
LEFT JOIN ams_company ac ON aos.company_id = ac.id
LEFT JOIN ams_organization ao ON aos.department_id = ao.id
LEFT JOIN ams_employee ae ON aos.employee_id = ae.id
LEFT JOIN ams_material_archives ama ON ama.id = aosd.material_archives_id
LEFT JOIN ams_material_category amc ON ama.category_id = amc.id
LEFT JOIN ams_storage ast ON aos.storage_id = ast.id
WHERE
	aosd.data_status = 1
AND aosd.merchant_id = 372
AND aos.out_storage_date >= '2021-3-11 0:00:00'
AND aos.out_storage_date <= '2022-3-11 0:00:00'
AND aos.out_storage_status IN (1, 5)
GROUP BY
	aos.employee_id,
	aos.employee_id,
	amc.category_name,
	ama.material_name,
	ama.unit,
	aos.out_storage_code,
	ast.storage_name,
	aos.out_storage_date
ORDER BY
	aos.employee_id,
	aos.employee_id,
	amc.category_name,
	ama.material_name,
	ama.unit,
	aos.out_storage_code,
	ast.storage_name,
	aos.out_storage_date

导出的结果如下：
在这里插入图片描述
我想统计他的条数怎么做
加一个select sum(1) from ( 查询）别名的形式

select sum(1) from (

SELECT
	aosd.count,
	SUM(aosd.count) AS material_count,
	aosd.out_storage_amount,
	SUM(aosd.out_storage_amount) AS material_amount,
	aosd.total_amount,
	SUM(aosd.total_amount) AS total_material_amount,
	aosd.merchant_id,
	aos.company_id,
	ac.company_name,
	aos.department_id,
	ao.organization_name AS department_name,
	ae.employee_name,
	ae.employee_code,
	aos.employee_id,
	aosd.count AS detail_count,
	amc.category_name AS category_name,
	ama.material_name,
	ama.unit,
	aos.out_storage_code,
	ast.storage_name,
	aos.out_storage_date,
	ama.brand,
	ama.model,
	amc.id
FROM
	ams_out_storage_detail aosd
LEFT JOIN ams_out_storage aos ON aosd.out_storage_id = aos.id
LEFT JOIN ams_company ac ON aos.company_id = ac.id
LEFT JOIN ams_organization ao ON aos.department_id = ao.id
LEFT JOIN ams_employee ae ON aos.employee_id = ae.id
LEFT JOIN ams_material_archives ama ON ama.id = aosd.material_archives_id
LEFT JOIN ams_material_category amc ON ama.category_id = amc.id
LEFT JOIN ams_storage ast ON aos.storage_id = ast.id
WHERE
	aosd.data_status = 1
AND aosd.merchant_id = 372
AND aos.out_storage_date >= '2021-3-11 0:00:00'
AND aos.out_storage_date <= '2022-3-11 0:00:00'
AND aos.out_storage_status IN (1, 5)
GROUP BY
	aos.employee_id,
	aos.employee_id,
	amc.category_name,
	ama.material_name,
	ama.unit,
	aos.out_storage_code,
	ast.storage_name,
	aos.out_storage_date
ORDER BY
	aos.employee_id,
	aos.employee_id,
	amc.category_name,
	ama.material_name,
	ama.unit,
	aos.out_storage_code,
	ast.storage_name,
	aos.out_storage_date
) a

四 SQL优化

创建索引，一般可以已解决大多数慢SQL的问题，一种是建表的时候直接创建索引，另一种是对已存在表创建索引。

alter table table_name add index index_name(column)

五 MySQL的case when

有时候我们需要把一些字段筛选出来，做个条件处理比如说根据分数算等级，根据性别字段直接设置成汉字和英文。
但是目前很多晚上的内容看起来很别扭，我会举两个例子

第一个例子是我实地测过的一个

SELECT
	(
	CASE
			
			WHEN amiod.except_stock_count = 0 THEN
			'新增盘盈' 
			WHEN amiod.except_stock_count > amiod.commit_stock_count THEN
			'盘亏' 
			WHEN amiod.commit_stock_count > amiod.except_stock_count THEN
			'盘盈' ELSE '正常' 
		END 
		) AS status_text,
		amio.inventory_order_no,
		amiod.except_stock_count,
		amiod.commit_stock_count,
		aso.id,
		aso.stock_count,
		aso.congeal_count,
		amiod.material_id,
		amiod.storage_id,
		aso.id AS stock_id,
		amiod.merchant_id,
		amiod.inventory_order_id,
		amio.inventory_status,
		amio.data_status 
	FROM
		ams_material_inventory_order_detail amiod
		LEFT JOIN ams_material_inventory_order amio ON amiod.inventory_order_id = amio.id
		LEFT JOIN ams_stock_order aso ON amiod.material_id = aso.material_id 
		AND amiod.storage_id = aso.storage_id 
		AND amiod.merchant_id = aso.merchant_id 
		AND aso.data_status = 1 
	WHERE
		amiod.data_status = 1 
		AND amio.data_status = 1 
		AND amio.inventory_status = 1 
		AND amiod.commit_stock_count IS NOT NULL 
	AND amiod.except_stock_count != amiod.commit_stock_count 
	LIMIT 10000

这里因为字段太多，我搞了个简化版的，关于SQL case when语句区分性别(因为不像上面那个直接关联那么多，你自己建一个新的表都可以)

select (CASE WHEN sex='1' THEN '男' WHEN sex='0' THEN '女' ELSE '保密' END) as sex_text
from user where data_status = 1;

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql