码农之家 - SQL
type: channel
link: http://www.liyx.fun/index.php/category/SQL
items: Windows端MySQL data目录迁移
items: SQL IN 多字段 行匹配
items: MySql数据库计算工作日函数
items: Linux下自动备份Mysql数据库
items: Windows下自动备份Mysql数据库
items: Oracle 从身份证计算年龄
items: oracle expdp 远程备份数据库
items: Oracle 导入导出dmp文件
items: 查看Oracle数据库用户连接数
items: 全国行政区域数据库

Windows端MySQL data目录迁移
type: item
date: 2025-11-06T15:36:00+08:00
description: 1、停止mysql服务2、复制数据库Data文件夹到其它目录，比如从 C:ProgramDataMySQLMySQL Server 5.7Data 复制到 D:MySQLData3、新data目录下除了 文件夹 以及 ibdata1 这个文件，全部删掉4、更改mysql的服务属性，将登陆身份改成本地5、创建软链接或修改my.ini配置文件5.1、将原Data文件夹改名，创建软链接。 mklink /j "C:\ProgramData\MySQL\MySQL Server 5.7\Data" "D:\MySQLData" 5.2、修改C:ProgramDataMySQLMySQL Server 5.7my.ini配置文件中的datadir路径为新Data文件夹6、启动mysql服务，确认运行正常后再删除原Data文件夹参考：https://blog.csdn.net/a123gsfd/article/details/79999838

SQL IN 多字段 行匹配
type: item
date: 2024-08-02T15:12:00+08:00
description: SQL IN 匹配多个字段的用法：select * from t where (a, b) in ((1, 2), (3, 4)) select t1.a,t2.b from t1 left join t2 on t1.c=t2.d where (t1.a, t2.b) in ((1, 2), (3, 4))

MySql数据库计算工作日函数
type: item
date: 2023-09-04T17:38:33+08:00
description: SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for hq_date 日期表 -- ---------------------------- DROP TABLE IF EXISTS `hq_date`; CREATE TABLE `hq_date` ( `id` mediumint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `date` date NOT NULL COMMENT '日期', `day_of_week` tinyint NULL DEFAULT NULL COMMENT '星期几（周日开始，1-7）', `holiday` enum('0','1') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '0' COMMENT '是否为假期', `name_of_holiday` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '假期名称', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `ix_hq_date_date`(`date`) USING BTREE ) ENGINE = MyISAM AUTO_INCREMENT = 5001 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '日期表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Function structure for CalcOverDays 计算超期天数 -- ---------------------------- DROP FUNCTION IF EXISTS `CalcOverDays`; delimiter ;; CREATE FUNCTION `CalcOverDays`(`begindate` date,`enddate` date) RETURNS int(11) BEGIN DECLARE workdays INT DEFAULT 0; -- 工作日天数 DECLARE min_date date DEFAULT begindate; -- 日期表最小日期 DECLARE max_date date DEFAULT enddate; -- 日期表最大日期 DECLARE weekcnt INT DEFAULT 0; -- 两日期的周数差 DECLARE beginweek INT DEFAULT 1; -- 开始日期的星期 DECLARE endweek INT DEFAULT 1; -- 结束日期的星期 IF begindate < enddate THEN SELECT min(date),max(date) into min_date,max_date FROM hq_date; IF begindate < min_date OR enddate > max_date THEN IF begindate < min_date THEN SET beginweek = DAYOFWEEK(begindate); SET endweek = DAYOFWEEK(IF(min_date > enddate, enddate, DATE_SUB(min_date, INTERVAL 1 DAY))); SET weekcnt = timestampdiff(week,begindate,IF(min_date > enddate, enddate, DATE_SUB(min_date, INTERVAL 1 DAY))); IF weekcnt = 0 THEN SET workdays = workdays + endweek - beginweek + 1 - case when (beginweek > 1 and endweek < 7) then 0 when (beginweek = 1 and endweek =7) then 2 else 1 end; ELSE SET workdays = workdays + (weekcnt - 1) * 5 + case when beginweek = 1 then 5 when beginweek = 7 …

Linux下自动备份Mysql数据库
type: item
date: 2023-04-23T11:44:00+08:00
description: 备份数据库所有非系统库，可选备份docker还是本地的mysql，压缩包可包含路径或不包含路径，自动删除7天前的备份文件首先创建 mysql.sh 备份脚本#!/bin/bash databases=($(docker exec mysql mysql -uroot -pPassword -e "show databases")) basepath='/tmp/mysql/backup/' if [ ! -d "$basepath" ]; then mkdir -p "$basepath" fi for db in ${databases[*]} do if | $db == "information_schema" || $db == "performance_schema" || $db == "mysql" || $db == "sys" ; then continue fi if [ ! -d "$basepath$db" ]; then mkdir -p "$basepath$db" fi # docker模式备份 docker exec mysql mysqldump -uroot -pW-z3DSOd --single-transaction $db > $basepath$db/$db-$(date +%Y%m%d).sql # 正常模式备份 # mysqldump -uroot -pW-z3DSOd --single-transaction $db > $basepath$db/$db-$(date +%Y%m%d).sql # 压缩包内带目录 # tar zPvf $basepath$db/$db.$(date +%Y%m%d).tar.gz $basepath$db/$db-$(date +%Y%m%d).sql # 压缩包内不带目录 tar zcvf $basepath$db/$db.$(date +%Y%m%d).tar.gz -C$basepath$db/ $db-$(date +%Y%m%d).sql # 删除7天前的备份 # find $basepath$db* -mtime +7 -name '*.tar.gz' -exec rm -rf {} \; # 只保留最新的7个备份 find $basepath$db* -name '*.tar.gz' | sort -t"." -k2n | head -n -7 | xargs rm -rf done rm -rf $basepath/*/*.sql 然后是添加执行权限chmod +x /tmp/mysql/mysql.sh 最后是设置定时执行脚本crontab -e 在最后添加一行，每天定时执行0 2 * * * /tmp/mysql/mysql.sh 参考：https://cloud.tencent.com/developer/article/2027981

Windows下自动备份Mysql数据库
type: item
date: 2023-03-22T14:38:00+08:00
description: 首先要安装mysql，然后采用bat脚本和定时任务的策略。编写备份脚本，然后在windows添加定时任务，脚本会删除过期的备份文件。@echo off echo 设置MySql数据库的连接信息 set host=127.0.0.1 set port=3306 set user=root set pass=xxxxxx echo 设置要备份的MySql数据库名称 set dbname=testdb echo 获取当天的日期格式，例如：2022122031300 set hour=%time:~0,2% if "%time:~0,1%"==" " set hour=0%time:~1,1% set backup_date=%Date:~0,4%%Date:~5,2%%Date:~8,2%%hour%%Time:~3,2%%Time:~6,2% echo 设置备份文件的路径 set backupfile=F:\backData\%dbname%-%backup_date%.sql echo 使用mysqldump对指定的MySql进行备份 echo 注意路径中有空格的要加上双引号 "D:\software\mysql\mysql-8.0.13-winx64\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname% > %backupfile% echo 删除过期文件，这里是超过30天就删除 rem forfiles /p F:\backData /s /m *.sql /d -30 /c "cmd /c del @file /f" echo 删除过期文件，这里是保留最新的10条记录 for /F "skip=10 eol=: delims=" %F in ('dir /b /o-d F:\backData\*.sql') do @del "%F" 参考：https://blog.csdn.net/xu_hui123/article/details/128395287

Oracle 从身份证计算年龄
type: item
date: 2023-01-30T11:11:00+08:00
description: CREATE OR REPLACE FUNCTION AGE_IN_IDCARD ( parameter VARCHAR2 ) RETURN NUMBER IS val DATE; BEGIN IF parameter is NULL THEN RETURN -1; ELSE val := CASE WHEN LENGTH(parameter) <= 15 THEN TO_DATE( '19'||NVL( SUBSTR( parameter, 7, 6 ), 'a' ), 'yyyy-MM-dd' ) ELSE TO_DATE( NVL( SUBSTR( parameter, 7, 8 ), 'a' ), 'yyyy-MM-dd' ) END; END IF; RETURN TRUNC( ( TO_CHAR( SYSDATE, 'yyyyMMdd' ) - TO_CHAR( val, 'yyyyMMdd' ) ) / 10000 ); EXCEPTION WHEN OTHERS THEN RETURN - 1; END;

oracle expdp 远程备份数据库
type: item
date: 2022-08-18T14:42:00+08:00
description: 两台数据库服务器，一台是oarcle11g (IP：192.168.1.31)，另一台是oracle19c，需要把数据从11g导入到19c。由于11g的数据库有使用到自动表分区，exp导出报错，只能使用expdp命令了。现在首先要做的是把数据从11g导出到19c的服务器上。第一步：在19c上创建dblink，执行SQL语句创建 link_to_11g，这里的用户名和密码是11g上的用户，确保这个用户拥有exp_full_database，最好是用system用户；CREATE PUBLIC database link link_to_11g CONNECT TO username IDENTIFIED BY "password" using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.31)(PORT=1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))'; 第二步：在19c上创建DIRECTORY，执行SQL语句创建 expdp_dir；CREATE DIRECTORY expdp_dir AS 'D:/databak/'; 第三步：在19c服务器上创建expdp的parfile文件 D:\exp.par，userid为19c的用户，要拥有exp_full_database权限，最好用system用户，network_link和directory是前两步创建的，dumpfile是导出的文件名，logfile是日志文件名，schemas是11g的用户名，示例内容如下：userid=system/"manager" directory=expdp_dir dumpfile=expdp.dump logfile=expdp.log schemas=USER_1,USER_2,USER_3 network_link=link_to_11g 第四步：在19c服务器上打开CMD，执行导出命令：expdp parfile=D:\exp.par 第五步：在19c服务器上打开CMD，执行导入命令：impdp system/"manager" directory=expdp_dir dumpfile=expdp.dump logfile=impdp.log full=yes 参考：https://blog.csdn.net/weixin_31688465/article/details/113398278

Oracle 导入导出dmp文件
type: item
date: 2022-07-08T09:12:00+08:00
description: --创建用户create user username identified by password;--给用户权限grant all privileges to username;--给用户dba权限grant connect,resource,dba to username;--导入备份文件imp username/password file = C:/dmp/username_20220607.dmp log = C:/dmp/username_20220607.log full = y--导入指定用户到指定用户imp BM/bm123@ORCL fromuser=BM touser=BM2 file = C:/dmp/username_20220607.dmp log = C:/dmp/username_20220607.log ignore=y--导出指定用户exp tc/"tc_19" owner=ZM file=/usr/local/share/xjtc.dmp log = /usr/local/share/xjtc.dmp.log--远程导出exp tc/"tc_19"@172.16.0.3:1521/orcl file=/usr/local/share/xjtc.dmp log = /usr/local/share/xjtc.dmp.log full=y--远程导入imp tc/"erp-ch"@172.16.0.3:1521/orcl file = C:/tc.dmp log = C:/tc.dmp.log full = y statistics=nonelinux安装oracle客户端参考： https://blog.csdn.net/yuki5233/article/details/115724257临时环境变量，输入以下命令起效。export ORACLE_HOME=/usr/lib/oracle/12.2/client64export TNS_ADMIN=$ORACLE_HOME/network/adminexport NLS_LANG='simplified chinese_china'.ZHS16GBKexport LD_LIBRARY_PATH=$ORACLE_HOME/libexport PATH=$ORACLE_HOME/bin:$PATH

查看Oracle数据库用户连接数
type: item
date: 2022-06-10T10:55:00+08:00
description: select username,machine,osuser,count(1) cnt from v$session where username is not null group by username,machine,osuser ORDER BY count(1) desc;

全国行政区域数据库
type: item
date: 2022-01-20T15:23:00+08:00
description: CREATE TABLE "SYS_CHINA_REGION" ( "OID" NUMBER(6,0) NOT NULL PRIMARY KEY, "CODE" NVARCHAR2(10) NOT NULL, "P_CODE" NVARCHAR2(10) NOT NULL, "NAME" NVARCHAR2(60) NOT NULL, "TYPE" NVARCHAR2(2) NOT NULL ) ; COMMENT ON COLUMN "SYS_CHINA_REGION"."OID" IS '主键'; COMMENT ON COLUMN "SYS_CHINA_REGION"."CODE" IS '地区代码'; COMMENT ON COLUMN "SYS_CHINA_REGION"."P_CODE" IS '上级地区代码'; COMMENT ON COLUMN "SYS_CHINA_REGION"."NAME" IS '区域名称'; COMMENT ON COLUMN "SYS_CHINA_REGION"."TYPE" IS '省市区类别 P/C/A'; COMMENT ON TABLE "SYS_CHINA_REGION" IS '地区表'; -- ---------------------------- -- Records of SYS_CHINA_REGION -- ---------------------------- INSERT INTO "SYS_CHINA_REGION" VALUES ('326', '610600', '610000', '延安市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('327', '610700', '610000', '汉中市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('328', '610800', '610000', '榆林市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('329', '610900', '610000', '安康市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('330', '611000', '610000', '商洛市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('331', '620100', '620000', '兰州市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('332', '620200', '620000', '嘉峪关市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('333', '620300', '620000', '金昌市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('334', '620400', '620000', '白银市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('335', '620500', '620000', '天水市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('336', '620600', '620000', '武威市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('337', '620700', '620000', '张掖市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('338', '620800', '620000', '平凉市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('339', '620900', '620000', '酒泉市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('340', '621000', '620000', '庆阳市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('341', '621100', '620000', '定西市', 'C'); INSERT INTO "SYS_CHINA_REGION" VALUES ('342', '621200', '620000', '陇南市', 'C'); INSERT INTO "SYS_CHINA_REGION"…