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

[TOC]

mysql数据库操作使用技巧

【1】数据库迁移

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

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

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

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

执行：

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

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

执行：

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

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

执行：

INSERT INTO 目标表 （目标字段1，目标字段2，字段1, 字段2,...）
select 目标字段1，目标字段2，字段1, 字段2,... FROM来源表

目标字段1，目标字段2：这是目标表比来源表多出的字段

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

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

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

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

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

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

方式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%';

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

​ 修改后查看当前值登录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

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

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'

数据库开发设计说明

字段设计

常见设计技巧

业务分析参考