宋子宪博客 - 关系型数据库
type: channel
link: https://songzixian.com/category/relationdatabase/
description: 关系型数据库
items: MySQL8快速导入百万级别数据,仅需要10多秒
items: Oracle 11G解决死锁详细教程
items: 开启MySQL binlog二进制日志配置教程
items: SQL常用基础命令
items: SQL 多变关联使用子查询去重
items: MySql的concat和group_concat的区别
items: MySql8之聚合函数
items: MySql8之子查询练习题
items: Mysql 基础表SQL
items: MySql8之子查询

MySQL8快速导入百万级别数据,仅需要10多秒
type: item
date: 2024-02-22T01:54:12+08:00
description: 1. 准备数据文件检查文件格式和结构：确保数据文件（如CSV,TXT,SQL）的格式与目标数据库表的列结构相匹配。文件应使用与数据库相同的字符编码，通常是UTF-8。检查并清除文件中的多余空格、换行符等。统计文件行数：在Linux或Unix-like系统中，使用wc -l /path/to/your/data.csv来计算文件中的行数。在Windows系统中，可以在命令提示符或PowerShell中使用Get-Content /path/to/your/data.csv | Measure-Object -Line。这可以帮助估计导入时间和调整数据库性能设置。示例文件路径：/path/to/your/data.csv2. 优化MySQL设置减少事务日志写入频率:-- 减少事务日志的写入频率，提高导入速度，但可能会降低数据安全性 SET GLOBAL innodb_flush_log_at_trx_commit = 0;禁用二进制日志同步:-- 禁用二进制日志同步，提高性能，但在崩溃时可能会丢失数据 SET GLOBAL sync_binlog = 0;增加InnoDB缓冲池大小:-- 增加InnoDB缓冲池大小，以适应数据导入的需要（根据服务器内存调整） SET GLOBAL innodb_buffer_pool_size = '1G';3. 使用LOAD DATA INFILE语句导入数据执行导入:-- 导入数据 LOAD DATA INFILE '/path/to/your/data.csv' INTO TABLE your_table FIELDS TERMINATED BY ',' -- 字段分隔符 ENCLOSED BY '"' -- 文本包围符 LINES TERMINATED BY '\n' -- 行分隔符 IGNORE 1 LINES; -- 如果文件包含头部标题行，则忽略调整命令：根据你的文件路径、表名和文件格式进行调整。4. 数据后处理检查数据完整性:-- 检查数据行数是否正确 SELECT COUNT(*) FROM your_table; -- 检查是否有明显的数据错误（如NULL值、格式错误等） SELECT * FROM your_table WHERE some_column IS NULL;5. 恢复MySQL设置恢复设置:-- 恢复事务日志的写入频率 SET GLOBAL innodb_flush_log_at_trx_commit = 1; -- 恢复二进制日志同步 SET GLOBAL sync_binlog = 1;注意事项数据备份：在进行大规模数据操作之前，务必备份好相关数据。权限问题：确保MySQL服务对数据文件有读取权限。安全性：临时调整的MySQL配置可能会影响数据库的稳定性和数据的完整性，在导入结束后应立即恢复。性能监控：在导入过程中监控服务器性能，确保资源不会被过度消耗。

Oracle 11G解决死锁详细教程
type: item
date: 2023-12-29T16:43:00+08:00
description: 死锁产成的原因死锁是指两个或多个会话在等待对方释放资源的情况，导致它们都无法继续执行。在数据库中，这通常发生在两个或多个事务各自持有一部分资源的同时尝试获取对方的资源。死锁产生的原因不当的事务设计：例如，两个事务以不同的顺序访问同样的资源。长时间持有锁：事务执行时间过长，导致锁被长时间持有。资源竞争：高并发环境下，多个事务竞争相同的资源。不足的系统资源：如CPU、内存不足，导致事务处理缓慢。应用程序设计：应用程序逻辑可能导致不必要的锁定。步骤 1: 确认死锁的存在检查数据库日志：查看alert.log文件，找到死锁相关的错误消息，例如ORA-00060。步骤 2: 识别造成死锁的会话查询锁定的会话：使用动态性能视图v$lock和v$session识别锁定的会话。SELECT s.sid, s.serial#, l.block FROM v$session s, v$lock l WHERE s.sid=l.sid AND l.type='TX';查找特定表的锁定情况：如果知道特定表，用v$locked_object和dba_objects视图确定锁定情况。SELECT l.session_id, o.object_name FROM v$locked_object l, dba_objects o WHERE l.object_id = o.object_id AND o.object_name = '表名';步骤 3: 分析和处理锁定会话会话详情：使用v$session获取锁定会话的详细信息。SELECT sid, serial#, username, status, schemaname, osuser, machine, terminal, program FROM v$session WHERE sid = [锁定会话的SID];执行中的SQL：查看锁定会话执行的SQL语句。SELECT sql_text FROM v$sql WHERE sql_id = (SELECT sql_id FROM v$session WHERE sid = [锁定会话的SID]);终止会话：如果需要，谨慎地终止锁定会话。ALTER SYSTEM KILL SESSION 'sid,serial#';步骤 4: 分析和预防事务设计：确保事务逻辑合理，避免不必要的资源竞争。SQL优化：优化涉及大量数据的查询，确保关键字段有索引。应用程序逻辑：检查应用程序代码，避免产生不必要的数据库锁。系统监控：定期监控数据库性能，尤其是锁和事务。注意事项在执行终止会话操作之前，确保了解其可能的影响。在处理生产环境问题时，应格外小心，以免造成系统中断或数据损失。对于复杂的数据库问题，建议与经验丰富的数据库管理员合作。

开启MySQL binlog二进制日志配置教程
type: item
date: 2023-12-15T13:41:00+08:00
description: 一、引言MySQL的二进制日志（binary log，简称binlog）是MySQL数据库中的一种日志文件，记录了对数据库执行更改的所有操作。这些日志对于数据库的恢复、复制和审计等方面至关重要。目的和作用数据恢复：二进制日志记录了数据库更改操作，可以用来恢复数据到特定时刻。复制支持：在MySQL复制设置中，二进制日志用于主服务器到从服务器的数据同步。审计：跟踪数据库更改，有助于审计和问题诊断。二、开启二进制日志步骤以下是在MySQL中开启二进制日志的详细步骤：步骤1：编辑配置文件MySQL的配置文件通常名为my.cnf（Linux）或my.ini（Windows）。Linux：配置文件通常位于/etc/mysql/my.cnf或/etc/my.cnf。使用文本编辑器打开文件，例如使用sudo nano /etc/mysql/my.cnf。Windows：配置文件通常位于C:\ProgramData\MySQL\MySQL Server X.Y\（X.Y是版本号）。使用文本编辑器打开文件。步骤2：添加二进制日志配置在[mysqld]部分中添加以下内容：[mysqld] log-bin=mysql-bin server-id=1log-bin定义了二进制日志文件的基本名称，MySQL将添加扩展名和序列号。server-id是服务器的唯一ID，在复制配置中尤其重要。确保每个服务器的ID唯一。步骤3：重启MySQL服务更改配置后，需要重启MySQL服务。Linux：使用命令systemctl restart mysql或sudo systemctl restart mysql重启服务。Windows：通过服务管理器或命令行工具重启MySQL服务。步骤4：验证配置登录MySQL后，运行以下命令来验证二进制日志是否启用：SHOW BINARY LOGS;如果返回了日志文件列表，则表示成功启用。三、注意事项磁盘空间：二进制日志可能占用大量空间，需要定期监控和管理。日志清理：可以设置expire_logs_days选项自动清理旧日志。安全性：保护好日志文件，因为它们包含敏感操作。测试环境：建议先在测试环境进行操作，以避免生产环境的潜在风险。四、结语开启MySQL的二进制日志是一项重要的操作，它不仅为数据恢复和复制提供支持，还有助于数据库的审计和监控。遵循正确的步骤并注意日志管理和安全性，可以确保数据库的稳定性和数据的安全性。

SQL常用基础命令
type: item
date: 2023-03-13T10:28:00+08:00
description: 概述SQL（Structured Query Language）是关系型数据库管理系统中的一种标准化语言，用于操作和管理数据库。以下是 SQL 中常用的命令：SELECT：从表中获取数据INSERT：向表中插入新的数据UPDATE：更新表中的数据DELETE：从表中删除数据CREATE：创建新的数据库或表ALTER：修改数据库或表的结构DROP：删除数据库或表INDEX：为表中的列创建索引GRANT：授权给用户或用户组访问数据库或表REVOKE：撤销用户或用户组的数据库或表访问权限JOIN: 链表查询以上是 SQL 中常用的命令，当然还有很多其他的命令和语法，需要根据实际情况进行学习和使用。以下是每个命令的简单示例：SELECT从表中获取数据，可以使用以下语法：SELECT column1, column2, ... FROM table_name WHERE condition;其中，column1, column2, ... 表示要获取的列名，table_name 表示要查询的表名，condition 表示查询条件。例如：SELECT * FROM customers WHERE city = 'Beijing';这个语句将从 customers 表中获取所有在 city 列中值为 Beijing 的行。INSERT向表中插入新的数据，可以使用以下语法：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);其中，table_name 表示要插入数据的表名，column1, column2, ... 表示要插入的列名，value1, value2, ... 表示要插入的值。例如：INSERT INTO customers (name, email, phone) VALUES ('Tom', 'tom@example.com', '123456789');这个语句将在 customers 表中插入一条新的记录，包括 name、email 和 phone 三个列的值。UPDATE更新表中的数据，可以使用以下语法：UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;其中，table_name 表示要更新的表名，column1 = value1, column2 = value2, ... 表示要更新的列和对应的新值，condition 表示更新条件。例如：UPDATE customers SET phone = '987654321' WHERE name = 'Tom';这个语句将更新 customers 表中所有 name 列的值为 Tom 的行的 phone 列的值为 987654321。DELETE从表中删除数据，可以使用以下语法：DELETE FROM table_name WHERE condition;其中，table_name 表示要删除数据的表名，condition 表示删除条件。例如：DELETE FROM customers WHERE email = 'tom@example.com';这个语句将从 customers 表中删除所有 email 列的值为 tom@example.com 的行。CREATE创建新的数据库或表，可以使用以下语法：CREATE DATABASE database_name;或者：CREATE TABLE table_name ( column1 datatype1, column2 datatype2, ... );其中，database_name 表示要创建的数据库名，table_name 表示要创建的表名，column1 datatype1, column2 datatype2, ... 表示要创建的列和对应的数据类型。例如：CREATE TABLE customers ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50), phone VARCHAR(20) );这个语句将创建一个名为 customers 的表，包括 id、name、email 和 phone 四个列，其中 id 列是主键。ALTER修改数据库或表的结构，可以使用以下语法：ALTER TABLE table_name ADD column_name datatype;或者：ALTER TABLE table_name MODIFY column_name datatype;其中，table_name 表示要修改的表名，column_name 表示…

SQL 多变关联使用子查询去重
type: item
date: 2022-08-22T19:32:00+08:00
description: 不去重状态select a.*, b.recon_amt from free_settlement_first a left join free_settlement_second b on a.settlement_first_id = b.settlement_first_id有2条数据出现了重复使用子查询去重select a.*, b.recon_amt from free_settlement_first a left join free_settlement_second b on a.settlement_first_id = b.settlement_first_id where not exists(select 1 from free_settlement_second b2 where a.settlement_first_id = b2.settlement_first_id and b.settlement_second_id > b2.settlement_second_id )

MySql的concat和group_concat的区别
type: item
date: 2022-07-27T14:55:00+08:00
description: 1、concat和group_concat都是用在sql语句中做拼接使用的，但是两者使用的方式不尽相同，concat是针对一行数据多个字段拼接，而group_concat是针对多行数据拼接，且group_concat自动生成逗号。concat示例Sql,把rule_id 和module_id拼接起来,中间用-隔开select concat(rule_id,'-',module_id) from t_engine_module_rule_relation where module_id = 1group_concatconcat示例Sql,把多行数据拼接起来,group_concatconcat自带,号隔开不适用group_concatconcat效果是这样的select module_id from t_engine_module_rule_relation使用group_concatconcat效果select group_concat(module_id) from t_engine_module_rule_relationconcat和group_concat组合使用select GROUP_CONCAT(DISTINCT CONCAT(c.company_name, '-', d.dept_name) SEPARATOR ',') AS company_dept_names from sys_company c left join sys_dept d on c.company_id = d.company_id

MySql8之聚合函数
type: item
date: 2022-05-02T17:43:00+08:00
description: 聚合函数1. 聚合函数介绍1.1 AVG和SUM函数可以对数值型数据使用AVG 和 SUM 函数。mysql> SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary) -> FROM employees -> WHERE job_id LIKE '%REP%'; +-------------+-------------+-------------+-------------+ | AVG(salary) | MAX(salary) | MIN(salary) | SUM(salary) | +-------------+-------------+-------------+-------------+ | 8272.727273 | 11500.00 | 6000.00 | 273000.00 | +-------------+-------------+-------------+-------------+1.2 MIN和MAX函数可以对任意数据类型的数据使用 MIN 和 MAX 函数。mysql> SELECT MIN(hire_date), MAX(hire_date) -> FROM employees; +----------------+----------------+ | MIN(hire_date) | MAX(hire_date) | +----------------+----------------+ | 1987-06-17 | 2000-04-21 | +----------------+----------------+1.3 COUNT函数COUNT(*)返回表中记录总数，适用于任意数据类型。mysql> SELECT COUNT(*) -> FROM employees -> WHERE department_id = 50; +----------+ | COUNT(*) | +----------+ | 45 | +----------+COUNT(expr) 返回expr不为NULL的记录总数。mysql> SELECT COUNT(commission_pct) -> FROM employees -> WHERE department_id = 50; +-----------------------+ | COUNT(commission_pct) | +-----------------------+ | 0 | +-----------------------+问题：用count(\*)，count(1)，count(列名)谁好呢?其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。问题：能不能使用count(列名)替换count(\*)?不要使用 count(列名)来替代count(*)，count(*) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。2. GROUP BY2.1 基本使用可以使用GROUP BY子句将表中的数据分成若干组SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];明确：WHERE一定放在FROM后面在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;包含在 GROUP BY 子句中的列不必包含在SELECT 列表中SELECT AVG(salary) FROM employees GROUP BY department_id;2.2 使用多个列分组SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id;2.3 GROUP BY中使用WITH ROLLUP使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。SELEC…

MySql8之子查询练习题
type: item
date: 2022-05-02T17:11:00+08:00
description: 首先需要创建表Mysql 基础表SQL https://songzixian.com/mysql/1792.html#1.查询和Zlotkey相同部门的员工姓名和工资 select last_name ,salary from employees where department_id = ( select d.department_id from employees d where last_name = 'Zlotkey' ) #2.查询工资比公司平均工资高的员工的员工号，姓名和工资。 select last_name,salary from employees where salary > ( select avg(salary) from employees ); #3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary select e.last_name , e.job_id ,e.salary from employees e where e.salary > all ( select salary from employees where job_id = 'SA_MAN' ); #4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 select department_id ,last_name from employees where department_id in ( select distinct department_id from employees where last_name like '%u%' ); #5.查询在部门的location_id为1700的部门工作的员工的员工号 select department_id from employees where department_id in ( select department_id from departments where location_id = 1700 ); #6.查询管理者是King的员工姓名和工资 select last_name ,salary ,manager_id from employees where manager_id in ( select manager_id from employees where last_name='King' and manager_id is not null ); #7.查询工资最低的员工信息: last_name, salary select last_name,salary from employees where salary = ( select min(salary) from employees ); #8.查询平均工资最低的部门信息 #方式一 select * from departments where department_id = ( #查询某个部门的平均工资等于临时表种的最低工资并返回department_id给上一级 select department_id from employees group by department_id having avg(salary) = ( # 从临时表获取最低的平均工资 select min(avg_sal) from ( # 统计部门的所有平均工资 select avg(salary) as avg_sal from employees group by department_id )t_dept_avg_sal #这是子查询临时表 ) ); #方式二 select * from departments where department_id = ( #返回department_id给上一层 select department_id from employees group by department_id # 查找小于或等于最低平均工资 having avg(salary) <= ALL ( # 统计部门平均工资 select avg(salary) from employees group by department_id ) ); #方式三 select * from departments where department_id = ( select department_id from employees group by department_id having avg(salary) = ( select avg(salary) as avg_sal from employees group by departme…

Mysql 基础表SQL
type: item
date: 2022-05-02T17:04:00+08:00
description: /* SQLyog Ultimate v12.08 (64 bit) MySQL - 5.7.28-log : Database - atguigudb ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/`sql_test` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `sql_test`; /*Table structure for table `countries` */ DROP TABLE IF EXISTS `countries`; CREATE TABLE `countries` ( `country_id` char(2) NOT NULL, `country_name` varchar(40) DEFAULT NULL, `region_id` int(11) DEFAULT NULL, PRIMARY KEY (`country_id`), KEY `countr_reg_fk` (`region_id`), CONSTRAINT `countr_reg_fk` FOREIGN KEY (`region_id`) REFERENCES `regions` (`region_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `countries` */ insert into `countries`(`country_id`,`country_name`,`region_id`) values ('AR','Argentina',2),('AU','Australia',3),('BE','Belgium',1),('BR','Brazil',2),('CA','Canada',2),('CH','Switzerland',1),('CN','China',3),('DE','Germany',1),('DK','Denmark',1),('EG','Egypt',4),('FR','France',1),('HK','HongKong',3),('IL','Israel',4),('IN','India',3),('IT','Italy',1),('JP','Japan',3),('KW','Kuwait',4),('MX','Mexico',2),('NG','Nigeria',4),('NL','Netherlands',1),('SG','Singapore',3),('UK','United Kingdom',1),('US','United States of America',2),('ZM','Zambia',4),('ZW','Zimbabwe',4); /*Table structure for table `departments` */ DROP TABLE IF EXISTS `departments`; CREATE TABLE `departments` ( `department_id` int(4) NOT NULL DEFAULT '0', `department_name` varchar(30) NOT NULL, `manager_id` int(6) DEFAULT NULL, `location_id` int(4) DEFAULT NULL, PRIMARY KEY (`department_id`), UNIQUE KEY `dept_id_pk` (`department_id`), KEY `dept_loc_fk` (`location_id`), KEY `dept_mgr_fk` (`ma…

MySql8之子查询
type: item
date: 2022-05-02T15:52:00+08:00
description: 子查询首先需要创建表Mysql 基础表SQL https://songzixian.com/mysql/1792.html1. 需求分析与问题解决1.1 实际问题题目：谁的工资比Abel高？#方式一： SELECT salary FROM employees WHERE last_name = 'Abel'; SELECT last_name,salary FROM employees WHERE salary > 11000; #方式二：自连接 SELECT e2.last_name,e2.salary FROM employees e1,employees e2 WHERE e1.last_name = 'Abel' AND e1.`salary` < e2.`salary` #方式三：子查询 SELECT last_name,salary FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name = 'Abel' );1.2 子查询的基本使用子查询（内查询）在主查询之前一次执行完成。子查询的结果被主查询（外查询）使用 。注意事项子查询要包含在括号内将子查询放在比较条件的右侧单行操作符对应单行子查询，多行操作符对应多行子查询1.3 子查询的分类分类方式1：我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询。分类方式2：我们按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。2. 单行子查询2.1 单行比较操作符操作符含义=equal to>greater than>=greater than or equal to<less than<=less than or equal to<>not equal to2.2 代码示例题目：返回公司工资最少的员工的last_name,job_id和salarySELECT last_name, job_id, salary FROM employees WHERE salary = ( SELECT MIN(salary) FROM employees );2.3 HAVING 中的子查询题目：查询最低工资大于50号部门最低工资的部门id和其最低工资SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > ( SELECT MIN(salary) FROM employees WHERE department_id = 50 );2.4 CASE中的子查询题目：显示员工的employee_id,last_name和location。其中，若员工department_id与location_id为1800的department_id相同，则location为’Canada’，其余则为’USA’。SELECT employee_id, last_name, ( CASE department_id WHEN ( SELECT department_id FROM departments WHERE location_id = 1800 ) THEN 'Canada' ELSE 'USA' END ) location FROM employees;2.5 子查询中的空值问题mysql> SELECT last_name, job_id -> FROM employees -> WHERE job_id = ( -> SELECT job_id -> FROM employees -> WHERE last_name = 'Haas' -> ); Empty set (0.01 sec)子查询不返回任何行2.6 非法使用子查询mysql> SELECT employee_id, last_name -> FROM employees -> WHERE salary = ( # 多行子查询使用单行比较符 -> SELECT MIN(salary) -> FROM employees -> GROUP BY department_id -> ); ERROR 1242 (21000): Subquery returns more than 1 row3. 多行子查询3.1 多行比较操作符操作符含义IN等于列表中的任意一个ANY需要和单行比较操作符一起使用，和子查询返回的某一个值比较ALL需要和单行比较操作符一起使用，和子查询返回的所有值比较SOME实际上是ANY的别名，作用相同，一般常使用ANY3.2 代码示例题目：查…