跳至主要內容

开发笔记-数据库开发技巧

holic-x...大约 5 分钟数据库碎片化

开发笔记-数据库开发技巧

[TOC]

mysql数据库操作使用技巧

【1】数据库迁移

a.将一个数据表中的数据插入到另一张数据表中

条件:2张表的字段一致,且插入全部数据

执行:INSERT INTO 目标表 SELECT * FROM 来源表;

insert into static_data select * from industry;

条件:只导入指定字段到数据表

执行

INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2,... FROM 来源表;

-- 示例
insert into static_data(node_code,node_name,node_pid) select code,name,pid from industry;

条件:只导入目标表中不存在的记录

执行

INSERT INTO 目标表 (字段1, 字段2, ...) SELECT字段1, 字段2, ... FROM来源表 
WHERE not exists (select * from目标表 where 目标表.比较字段 =来源表.比较字段); 

image-20201004121711849

条件:导入的目标表字段比来源表的字段多,将来源表的数据导入再加上几个字段组成目标表的数据

执行

INSERT INTO 目标表 (目标字段1,目标字段2,字段1, 字段2,...select 目标字段1,目标字段2,字段1, 字段2,... FROM来源表

目标字段1,目标字段2:这是目标表比来源表多出的字段

image-20201004121759323

​ 分析:此处表 insertTest里本身没有a1,a2两个字段名,当使用这个查询语句时,会查出 insertTest表的所有字段值,并在表数据的前面加上了列名为a1,a2的字段,并且列名为a1的值全为a1,列名为a2的值全为a2,并且a1,a2不能为变量,如果是变量,sql语句会把它当做表字段,而表中不存在这个字段,会报错。即可以向一个表中查询不存在的列名,这里不存在的列名必须是实际值或占位符,不能是变量

【2】数据库表与EXCEL的转换

方式1:借助数据库操作工具将数据导出成EXCEL表格

​ 在指定数据库选中指定导出的表,右键选择“导出向导”

image-20201004122140738

​ 随后选定导出格式,点击下一步执行导出操作

image-20201004122217346

​ 根据需求勾选导出指定表的栏位和其他附加信息(列的标题等),随后点击开始即可

方式2:借助SQL语句将筛选后的数据导出成EXCEL表格

​ 第一步:确定sql语句,将查询后的结果导出成excel的数据

SELECT * from 表名 where 查询条件into outfile '/usr/local/mysql/1.sql';

可能会出现的问题:The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

出现的原因是因为在安装MySQL的时候限制了导入与导出的目录权限只能在规定的目录下才能导入,因此需要通过命令查看 secure-file-priv 当前的值,在指定导出目录下导出excel文件,参考如下

通过 mysql –u数据库用户名 –p数据库密码 进入mysql命令行
输入show variables like '%secure%';

image-20201004122515704

​ 其次,亦可通过修改默认导出路径实现。修改方法:在mysql的安装路径下找到(windows系统)my.ini linux系统则为my.cnf,在mysqld 下面添加或修改secure-file-priv = 文件导出路径

image-20201004122528134

​ 修改后查看当前值登录mysql命令行再输入show variables like '%secure%';随后重启mysql服务并重新执行第一步的语句。若出现Can't create/write to file (Errcode: 13 - Permission denied)错误,则是因为对应路径的文件夹权限不够,改变文件夹权限即可

方式3:借助JAVA代码实现将数据表数据导出到EXCEL表格

​ 此处java将DB与EXCEL操作工具结合使用实现数据导出

​ 参考链接:https://blog.csdn.net/weidong_y/article/details/81944878

【3】数据库触发器

应用场景-员工行为排查线上化系统:每次用户完成问卷的时候触发检验,如果存在用户还未完成或完成状态异常(针对被驳回的记录)的数据则问卷发布状态为“已发布”状态,如果指定所有用户均已完成指定问卷,则问卷发布状态相应调整为“已完成”状态

触发器定义

delimiter ###
create trigger after_update_isdo after update on check_record for each row
begin
    # 获取指定问卷处于未完成-0或异常状态被驳回-2的人数
  select count(*) from check_record cr where (cr.is_do !='1' and cr.is_do !='3') and cr.questionnaire_id = new.questionnaire_id into @num;
   # 如果num=0,则说明所有指定用户均已完成该问卷,修改问卷发布标识
  if @num = 0 then
     update questionnaire set publish_status = '2' where questionnaire_id = new.questionnaire_id;
  end if;
  if @num > 0 then
     update questionnaire set publish_status = '1' where questionnaire_id = new.questionnaire_id;
  end if;
end
###
delimiter ;

# 查看所有触发器
show triggers;

# 删除指定trigger
drop trigger after_update_isdo;

【4】字段类型说明

​ MySQL中,BLOB类型的字段用于存储二进制数据,BLOB是个类型系列,包括:TinyBlob、Blob、MediumBlob、LongBlob,这几个类型之间的唯一区别是在存储文件的最大大小上不同;MySQL的四种BLOB类型大小(单位:字节):TinyBlob(max: 255)、Blob(max: 65K)、MediumBlob(max: 16M)、LongBlob(max: 4G)

oracle数据库操作使用技巧

历史流程数据筛选调整:oracle嵌套case when

image-20200807095242955

子系统关联基表查找数据,采用下述方式如果关联不到子系统则查找失败

image-20200812171115647

select  ui.* 
	, r.role_key         "roleKey",
      r.role_name        "roleName"

from (select 
			   u.user_id          "userId",
			   bu.user_id          "baseUserId",
               u.user_num         "userNum",
               bu.user_name       "userName",
               bu.primary_class   "primaryClass",
               bu.secondary_class "secondaryClass",
               u.uass_account     "uassAccount",
               u.phone            "phone",
               u.user_status      "user_status",
               u.logic_del        "logic_del",
               u.create_time      "create_time",
               u.modify_time      "modify_time"             
        from fhyd_user u
        left join mip_base_user bu on bu.user_num = u.user_num)ui

 left join mip_user_role ur on ui."baseUserId" = ur.user_id
 left join mip_role r on r.role_id = ur.role_id
 left join mip_sub_system sys on sys.system_id = r.system_id

 where SYS.SYSTEM_identify = 'FHYD'
     --   select * from mip_sub_system where  = 'FHYD'

数据库开发设计说明

字段设计

常见设计技巧

业务分析参考

评论
  • 按正序
  • 按倒序
  • 按热度
Powered by Waline v3.1.3