mysql基础（二）-数据库增、删、改、查

mysql基础（二）-数据库增、删、改、查

1、搜索

这是一段我遇见的较为复杂的查询sql，结合这段sql 对其中的命令进行说明。

SELECT
	sup_dept_name supDeptName,
	sum(
	CASE
			WHEN rent_days < 15 THEN
			( original_value + net_value )/ 2 * 0.003 * 15 
			WHEN (rent_days <= 180 and rent_days >= 15) THEN
			( original_value + net_value )/ 2 * 0.06 * 1 ELSE ( original_value + net_value )/ 2 * 0.055 * 1 
		END 
		) AS totalRent 
	FROM
		(
		SELECT
			d.instru_ledger_id,
			l.instru_name,
			l.original_value / 100 original_value,
			l.lifespan,
			m.borrow_time,
			m.sup_dept_name,
			r.return_time,
			l.original_value / 100- ( l.original_value / 100 / l.lifespan )* timestampdiff(
				MONTH,
				l.instore_date,
			curdate()) AS net_value,
			timestampdiff( DAY, borrow_time, CASE WHEN return_time IS NULL THEN date_format( '20221022', '%y%m%d' ) ELSE return_time END ) AS rent_days,
			timestampdiff( MONTH, borrow_time, CASE WHEN return_time IS NULL THEN date_format( '20221022', '%y%m%d' ) ELSE return_time END )+ 1 AS rent_month 
		FROM
			instru_borrow_act_main m
			LEFT JOIN instru_borrow_act_detail d ON m.id = d.instru_borrow_act_main_id
			LEFT JOIN instrument_ledger l ON d.instru_ledger_id = l.id
			LEFT JOIN instrument_return r ON d.instru_borrow_act_main_id = r.instru_borrow_act_main_id 
			AND d.instru_ledger_id = r.instru_ledger_id 
		WHERE
			r.start_borrow_time <= date_format( '20221022', '%y%m%d' ) 
			AND r.return_time IS NULL 
		) T 
GROUP BY
	sup_dept_name;

查询结果如下

1）、select

select * from instru_borrow_act_main m
1、select 后为查询列名，为 “*” 时表示查询所有列
2、from 后为表名
3、instru_borrow_act_main m 中的 m 表示为表instru_borrow_act_main重命一个别名。
4、字段中sup_dept_name AS supDeptName ,AS表示对sup_dept_name重命一个别名，效果与上一条中的m类似。

2）、left join

LEFT JOIN 表示联表，用于多表查询，具体用法见下面

select a.*(a表中的所有字段) 
from tablenameA(表格名称) a(表格命名)
left join tablenameB(表格名称)   b (表格命名)
on  a.bid = b.id (将b表中id中与a表中bid中的字段值相同的数据连接起来，并展示)
and b.status = '1'  （增加联表时的筛选条件）
left join tablename B   c  (联表时可以用不同的字段联同一张表，命名时必须遵守唯一性)
on a.cid = c.id 

3）、where

用于过滤不满足条件的数据

WHERE
			r.start_borrow_time <= date_format( '20221022', '%y%m%d' ) // 须符合时间在此之前
			AND r.return_time IS NULL  // 须符合该字段为空

其中and、or等等为逻辑运算符，见下表

4）、GROUP BY

GROUP BY为聚合函数,下面这个语句的意思是，数据totalRent 按照sup_dept_name分类统计

GROUP BY
	sup_dept_name;

其他地方应用可以是按照sup_dept_name，将数据放在一起

5）、Case when then end

Case when then end 为条件控制语句

		CASE
			WHEN rent_days < 15 THEN ( original_value + net_value )/ 2 * 0.003 * 15 
			WHEN (rent_days <= 180 and rent_days >= 15) THEN ( original_value + net_value )/ 2 * 0.06 * 1 
			ELSE ( original_value + net_value )/ 2 * 0.055 * 1 
		 END 

case 代表条件案例
when后面跟 判断条件
then后面跟 满足条件后执行的操作
else后面表示条件均不满足 执行的操作
end 代表案例结束

6）、函数 sum(）表示 求和

一般放置在字段中。

7）、函数 date_format(）表示 格式转换

date_format( '20221022', '%y%m%d' ) 

表示将%y%m%d 格式时间 转换成 系统标准时间。

8）、函数 curdate()表示 当前时间

2、插入

查询数据尽可能简单,下面这段sql表示向sys_user表中插入两条数据

// 这是一段在系统中查询用户的代码
insert into sys_user(user_id,dept_id,create_time)
values(('123','333',sysdate()),('222','666',sysdate()))

3、更改

下面这段sql表示将表sys_user 中 user_id 等于 '123’的数据中的 dept_id 列 修改为 ‘456’

update  sys_user set dept_id='456' 
where user_id = '123'

注：若没有where 则 更改表中所有数据，需特别小心。

4、delete

下面这段sql 表示 删除sys_user 中 user_id 等于 123 的数据，若没有where 则 删除表中所有数据，需特别小心。

delete from sys_user 
where user_id = '123'