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'