数据库的各种用法

请叫我李漂亮

已于 2023-11-21 16:11:53 修改

阅读量174

点赞数

文章标签： mysql 数据库

于 2022-12-27 11:30:16 首次发布

本文链接：https://blog.csdn.net/weixin_48229232/article/details/128453618

版权

1、执行sql文件

1.将sql文件中的utf8mb4_0900_ai_ci 全替换为 utf8_general_ci
2.将sql文件中的utf8mb4 全替换为 utf8
3.在mysql 5.7版新建一个数据库，字符集选择 utf8 ;排序规则选择 utf8_general_ci，
4.导入sql

2、更新字段或者新增字段的通用sql模版

更新
ALTER TABLE `proj_stakeholder`
change `up_admin_unit` `up_admin_unit` varchar(100) NULL DEFAULT NULL COMMENT '上级管理单位';
change `work_content` `work_content` varchar(600) NULL DEFAULT NULL COMMENT '工作内容',
change `involve_system` `involve_system` varchar(300) NULL DEFAULT NULL COMMENT '涉及子系统',
change `proj_need` `proj_need` varchar(600) NULL DEFAULT NULL COMMENT '对项目的需求',
change `proj_contribution` `proj_contribution` varchar(600) NULL DEFAULT NULL COMMENT '可为项目的贡献',

新增的

ALTER TABLE `proj_budget_cost_detail_change`
ADD COLUMN `COST_TYPE` varchar(64) NULL COMMENT '费用类型'

ALTER TABLE `out_contract_rechange_tax`
ADD COLUMN `NAME` varchar(32) NULL COMMENT '名称'

3、获取本月月份。MONTH(GETDATE())

获取当前年：YEAR(GETDATE())

获取本月天数：DAY(GETDATE())

例如：现在2023年10月 MONTH(GETDATE())：结果是10

可以用做查询某个字段比如时间为本月的：

month(main.JSC076_ZZWCDATE) = MONTH(GETDATE())

4、保留小数的 convert(decimal(10,2),值)

例如 count(main.JSC001_ID)是 10

convert(decimal(10,2),count(main.JSC001_ID)) ：10.00

修改所有一对一字段对应的比如A表的code字段对应B表的code字段。需要将A表的主键根据这个条件放到B表对应的字段的值上

UPDATE t_project tp LEFT JOIN sys_stru ss ON tp.stru_out_id = ss.OUT_STRU_ID set tp.stru_id = ss.STRU_ID

5、CASE WHEN 函数

CASE WHEN 条件 THEN 什么什么 else 什么什么 END

就是类似声明一个变量的字段用于存放数据库里没有这个字段只是显示

5.2、跟上面差不多。这个只是用了with

这是查询树形结构的sql

查询所有上级

--所有该条的所有上级
WITH TREE AS(
SELECT * FROM CBT130_FC_PROFESSUB_CATEGORY
WHERE CBC130_DELFLAG = '0' AND (该条数据的id)
UNION ALL
SELECT t.* FROM CBT130_FC_PROFESSUB_CATEGORY t,TREE
WHERE TREE.CBC130_PARENTID = t.id
)
SELECT TREE.* FROM TREE

查询所有下级

--查询该条的所有下级
WITH TREE AS(
SELECT * FROM CBT130_FC_PROFESSUB_CATEGORY
WHERE CBC130_DELFLAG = '0' AND (该条数据的id)
UNION ALL
SELECT t.* FROM CBT130_FC_PROFESSUB_CATEGORY t,TREE
WHERE TREE.id = t.CBC130_PARENTID and t.CBC130_DELFLAG = '0'
)
SELECT * FROM TREE;

6、例如按照当前年月区分查询各个类型的数量

SELECT YEAR(JSC001_WCDATE) AS year, MONTH(JSC001_WCDATE) AS month, COUNT(*) AS count,tab.JSC002_LB
FROM tab
GROUP BY YEAR(JSC001_WCDATE), MONTH(JSC001_WCDATE),tab.JSC002_LB
ORDER BY year DESC, month DESC

6.2、按照月份为字段查询各个类型的数量

SELECT YEAR(tab.JSC001_WCDATE),tab.JSC002_LB,
count(case when MONTH(tab.JSC001_WCDATE) = '1' then 1 else null end) one,
count(case when MONTH(tab.JSC001_WCDATE) = '2' then 1 else null end) two,
count(case when MONTH(tab.JSC001_WCDATE) = '3' then 1 else null end) three,
count(case when MONTH(tab.JSC001_WCDATE) = '4' then 1 else null end) four,
count(case when MONTH(tab.JSC001_WCDATE) = '5' then 1 else null end) five,
count(case when MONTH(tab.JSC001_WCDATE) = '6' then 1 else null end) six,
count(case when MONTH(tab.JSC001_WCDATE) = '7' then 1 else null end) seven,
count(case when MONTH(tab.JSC001_WCDATE) = '8' then 1 else null end) eight,
count(case when MONTH(tab.JSC001_WCDATE) = '9' then 1 else null end) nine,
count(case when MONTH(tab.JSC001_WCDATE) = '10' then 1 else null end) ten,
count(case when MONTH(tab.JSC001_WCDATE) = '11' then 1 else null end) eleven,
count(case when MONTH(tab.JSC001_WCDATE) = '12' then 1 else null end) twelve
FROM tab
GROUP BY YEAR(tab.JSC001_WCDATE),tab.JSC002_LB

6.3、按照类型为字段列出12个月对应的数量

select t.month,
count(case when tab.lb = '不按方案施工' then 1 else null end) 不按方案施工,
count(case when tab.lb = '不按图施工' then 1 else null end) 不按图施工
FROM
(
SELECT 1 AS month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION
SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
) AS t
LEFT JOIN (查出来当前月份类年) tab
ON t.month = tab.month
GROUP BY tab.month,t.month

这是tab里面应该查出来的

这是最终的要求结果

1、IFNULL() 函数

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

IFNULL(expression, alt_value)
如果第一个参数的表达式 expression 为 NULL，则返回第二个参数的备用值。

2、substring()函数

SUBSTRING（str，x，y）：str，代表字符串；x，代表是从第几位开始截取；y，代表截取几位数。

例子1：substring（‘abcdefgh’,3,2）结果为：cd

3、STR_TO_DATE() DATE_FORMAT(）

STR_TO_DATE()函数可能会根据输入和格式字符串返回DATE

数据库里面是datetime类型将输入的string转换成date类型查询

DATE_FORMAT(）数据库里面是datetime类型查出来的数据用string接收用这个转换

where  STR_TO_DATE(#{collectionTime}, '%Y-%m-%d %H:%i:%s')
select DATE_FORMAT(max(t1.collection_time), '%Y-%m-%d %H:%i:%s')

4、cast(数值 as decimal(10,2))

数据库类型是string 计算数值的话必须转换成int 或者double

这样才能使用函数例如：max(cast(数值 as decimal(10,2)))

后台接收使用 Object

转换：

第一个是数据库里面是string的值

第二个是数据库里面是double的值
Double beilu=Double.valueOf(String.valueOf(getday1.get("magnification")));
double jian1=Double.parseDouble(getday1.get("minTip").toString());

5、convert(ss2.ORGAN_ID,char) organId convert就是可以转换类型现在这个是把long 转换成了string

6 如果我使用多选查询搜索一个字符串拼接的数据需要怎么查

(1)（首先要把给的string拼接(1,2,3) 转为list 之后写循环）

用find_in_set 方法相当于是分隔查询 :find_in_set(1,p_aisle) or find_in_set(2,p_aisle)

就会查询字段存的 1,2,3中的单独每个

<if test="tCarPeriod.aisleList != null and tCarPeriod.aisleList.size() > 0">
    and
    <foreach collection="tCarPeriod.aisleList" item="pAisle" index="index" separator="or" open="(" close=")">
        find_in_set(#{pAisle}, p_aisle)
    </foreach>
</if>

7 查询时间表里存了开始时间还有结束时间

下拉传过来也是开始时间还有结束时间我怎么查询在这个范围内的有效期

（判断传过来的开始结束是否在我表里的开始时间或者传过来的开始结束在我表里的结束时间）

 <if test="tCarPeriod.createTime != null  and tCarPeriod.createTime != ''">and
( create_time (between #{tCarPeriod.createTime} and #{tCarPeriod.endTime})
     or end_time ( between #{tCarAisle.createTime} and #{tCarAisle.endTime}))
 </if>

8使用传过来的条件或者是展示自己输入条件判断的字段数据

select 
if(date_format(now(), '%Y-%m-%d')&lt; date_format(create_time, '%Y-%m-%d'), 0,
if(date_format(now(), '%Y-%m-%d')> date_format(end_time, '%Y-%m-%d'), 2,1) ) is_state

from 表

（上面是获取现在的时间判断我表里面是否在有效时间内有效时间两个字段开始和结束）

<if test="tCarPeriod.isState != null  and tCarPeriod.isState != ''">and
    if(date_format(now(), '%Y-%m-%d') &lt; date_format(t.create_time, '%Y-%m-%d'), 0,
    if(date_format(now(), '%Y-%m-%d')> date_format(t.end_time, '%Y-%m-%d'), 2,1) ) = #{tCarPeriod.isState}
</if>

这是搜索判断输入查询的哪个状态