MySQL8快速导入百万级别数据,仅需要10多秒
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配置可能会影响数据库的稳定性和数据的完整性，在导入结束后应立即恢复。性能监控：在导入过程中监控服务器性能，确保资源不会被过度消耗。
一、引言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（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 表示要添加或修改的列名，datatype 表示对应的数据类型。例如：ALTER TABLE customers ADD address VARCHAR(100);这个语句将在 customers 表中添加一个名为 address 的列，数据类型为 VARCHAR(100)。DROP删除数据库或表，可以使用以下语法：DROP DATABASE database_name;或者：DROP TABLE table_name;其中，database_name 表示要删除的数据库名，table_name 表示要删除的表名。例如：DROP TABLE customers;这个语句将删除名为 customers 的表。INDEX为表中的列创建索引，可以使用以下语法：CREATE INDEX index_name ON table_name (column_name);其中，index_name 表示索引的名称，table_name 表示要创建索引的表名，column_name 表示要创建索引的列名。例如：CREATE INDEX idx_customers_city ON customers (city);这个语句将在 customers 表中为 city 列创建一个名为 idx_customers_city 的索引。GRANT授权给用户或用户组访问数据库或表，可以使用以下语法：GRANT permission ON object TO user;其中，permission 表示授权的权限，object 表示授权的对象，user 表示被授权的用户或用户组。例如：GRANT SELECT, INSERT ON customers TO user1;这个语句将授权 user1 用户对 customers 表执行 SELECT 和 INSERT 操作的权限。REVOKE撤销用户或用户组的数据库或表访问权限，可以使用以下语法：REVOKE permission ON object FROM user;其中，permission 表示要撤销的权限，object 表示要撤销权限的对象，user 表示要被撤销权限的用户或用户组。例如：REVOKE SELECT, INSERT ON customers FROM user1;这个语句将撤销 user1 用户对 customers 表执行 SELECT 和 INSERT 操作的权限。JOINSQL中的JOIN用于将两个或多个表中的数据合并在一起，并根据它们之间的关系返回结果集。以下是一些JOIN类型的说明和示例：INNER JOININNER JOIN（内连接）：返回两个表中共有的行。例如，假设我们有两个表：students和classes，students表包含学生的信息（学生ID，姓名，年龄，班级ID），classes表包含班级信息（班级ID，班级名称）。我们可以使用INNER JOIN将两个表连接起来，并返回每个学生所在的班级名称：SELECT students.name, classes.class_name FROM students INNER JOIN classes ON students.class_id = classes.class_id;LEFT JOINLEFT JOIN（左连接）：返回左表中的所有行以及右表中匹配的行。例如，假设我们有两个表：orders和customers，orders表包含订单信息（订单ID，客户ID，订单日期），customers表包含客户信息（客户ID，客户姓名）。我们可以使用LEFT JOIN将两个表连接起来，并返回每个订单的客户姓名，如果客户ID在customers表中不存在，则返回NULL：SELECT orders.order_id, customers.customer_name FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id;RIGHT JOINRIGHT JOIN（右连接）：返回右表中的所有行以及左表中匹配的行。例如，假设我们有两个表：products和orders，products表包含产品信息（产品ID，产品名称，产品价格），orders表包含订单信息（订单ID，产品ID，订单日期）。我们可以使用RIGHT JOIN将两个表连接起来，并返回每个产品的订单数量，如果产品ID在orders表中不存在，则返回NULL：SELECT products.product_name, COUNT(orders.order_id) AS order_count FROM products RIGHT JOIN orders ON products.product_id = orders.product_id GROUP BY products.product_name;FULL OUTER JOINFULL OUTER JOIN（全外连接）：返回左表和右表中的所有行。例如，假设我们有两个表：customers和orders，customers表包含客户信息（客户ID，客户姓名），orders表包含订单信息（订单ID，客户ID，订单日期）。我们可以使用FULL OUTER JOIN将两个表连接起来，并返回每个客户的订单数量，如果客户ID在orders表中不存在，则返回NULL，如果订单ID在customers表中不存在，则返回NULL：SELECT customers.customer_name, COUNT(orders.order_id) AS order_count FROM customers FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customers.customer_name;
不去重状态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 )
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
聚合函数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关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。SELECT department_id,AVG(salary) FROM employees WHERE department_id > 80 GROUP BY department_id WITH ROLLUP;注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。3. HAVING3.1 基本使用SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000;非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数。mysql> SELECT department_id, AVG(salary) -> FROM employees -> WHERE AVG(salary) > 8000 -> GROUP BY department_id; ERROR 1111 (HY000): Invalid use of group function结论：当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。但是，建议大家声明在WHERE中。3.2 WHERE和HAVING的对比 优点缺点WHERE先筛选数据再关联，执行效率高不能使用分组中的计算函数进行筛选HAVING可以使用分组中的计算函数在最后的结果集中进行筛选，执行效率较低4. SELECT的执行过程4.1 查询的结构#方式1： SELECT ...,....,... FROM ...,...,.... WHERE 多表的连接条件 AND 不包含组函数的过滤条件 GROUP BY ...,... HAVING 包含组函数的过滤条件 ORDER BY ... ASC/DESC LIMIT ...,... #方式2： SELECT ...,....,... FROM ... JOIN ... ON 多表的连接条件 JOIN ... ON ... WHERE 不包含组函数的过滤条件 AND/OR 不包含组函数的过滤条件 GROUP BY ...,... HAVING 包含组函数的过滤条件 ORDER BY ... ASC/DESC LIMIT ...,... #其中： #（1）from：从哪些表中筛选 #（2）on：关联多表查询时，去除笛卡尔积 #（3）where：从表中筛选的条件 #（4）group by：分组依据 #（5）having：在统计结果中再次筛选 #（6）order by：排序 #（7）limit：分页4.2 SELECT执行顺序你需要记住 SELECT 查询时的两个顺序：1. 关键字的顺序是不能颠倒的：SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...2.SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：#FROM ...,...-> ON -> (LEFT/RIGNT JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMITSELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5 FROM player JOIN team ON player.team_id = team.team_id # 顺序 1 WHERE height > 1.80 # 顺序 2 GROUP BY player.team_id # 顺序 3 HAVING num > 2 # 顺序 4 ORDER BY num DESC # 顺序 6 LIMIT 2 # 顺序 7在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。4.3 SQL 的执行原理SELECT 是先执行 FROM 这一步的。在这个阶段，如果是多张表联查，还会经历下面的几个步骤：首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；添加外部行。如果我们使用的是左连接、右连接或者全连接，就会涉及到外部行，也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。当然如果我们操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据。当我们拿到了查询数据表的原始数据，也就是最终的虚拟表vt1，就可以在此基础上再进行WHERE阶段 。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表vt2。然后进入第三步和第四步，也就是GROUP和HAVING阶段 。在这个阶段中，实际上是在虚拟表 vt2 的基础上进行分组和分组过滤，得到中间的虚拟表vt3和vt4。当我们完成了条件筛选部分之后，就可以筛选表中提取的字段，也就是进入到SELECT和DISTINCT阶段 。首先在 SELECT 阶段会提取想要的字段，然后在 DISTINCT 阶段过滤掉重复的行，分别得到中间的虚拟表vt5-1和vt5-2。当我们提取了想要的字段数据之后，就可以按照指定的字段进行排序，也就是ORDER BY阶段 ，得到虚拟表vt6。最后在 vt6 的基础上，取出指定行的记录，也就是LIMIT阶段 ，得到最终的结果，对应的是虚拟表vt7。当然我们在写 SELECT 语句的时候，不一定存在所有的关键字，相应的阶段就会省略。同时因为 SQL 是一门类似英语的结构化查询语言，所以我们在写 SELECT 语句的时候，还要注意相应的关键字顺序，所谓底层运行的原理，就是我们刚才讲到的执行顺序。
首先需要创建表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 department_id order by avg_sal asc limit 1 ) ); #方式四 select * from departments d, ( #查询员工的平均工资,并且按降序排序取一条数据 select department_id, avg(salary) as avg_sal from employees group by department_id order by avg_sal asc limit 1 )t_dept_avg_sal #作为一个临时表 #两张表关联 where d.department_id = t_dept_avg_sal.department_id ; #9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询） #方式一 select d.* , ( select avg(salary) from employees where department_id = d.department_id ) as avg_salary from departments d # 关联最低平均工资的部门 where department_id = ( #查询最低的平均工资department_id select department_id from employees group by department_id having avg(salary) = ( ##查询最低的平均工资 select min(salary) from ( #查询部门员工平均工资 select avg(salary) as salary from employees group by department_id )t_dept_avg_sal ) ); #方式二 select d.* ,( select avg(salary) from employees where department_id = d.department_id ) as salary from departments d where d.department_id = ( select department_id from employees group by department_id having avg(salary) <= ALL ( #查询平均工资 select avg(salary) from employees group by department_id ) ); #10.查询平均工资最高的 job 信息 #方式一 select * from jobs where job_id = ( #查询最大的平均工资的job_id select job_id from employees group by job_id having avg(salary) = ALL ( # 查询最大的平均工资 select max(salary) from ( #查询平均工资 select avg(salary) as salary from employees group by job_id ) t_dept_avg_sal ) ); #方式二 select * from jobs where job_id = ( # 查询最大的平均工资 select job_id from employees group by job_id having avg(salary) >= all ( # 查询平均工资 select avg(salary) from employees group by job_id ) ); #方式三 select * from jobs where job_id = ( select job_id from employees group by job_id having avg(salary) = ( select avg(salary) as salary from employees group by job_id order by salary desc limit 0,1 ) ); #方式四 select j.* from jobs j,( select job_id, avg(salary) as salary from employees group by job_id order by salary desc limit 0,1 )t_job_avg_sal where j.job_id = t_job_avg_sal.job_id ; #11.查询平均工资高于公司平均工资的部门有哪些? select * from departments where department_id in ( select department_id from employees where department_id is not null group by department_id having avg(salary) > ( select avg(salary) from employees ) ); #12.查询出公司中所有 manager 的详细信息 #方式一 select distinct mgr.employee_id, mgr.last_name ,mgr.job_id ,mgr.department_id from employees emp join employees mgr on emp.manager_id = mgr.employee_id ; #方式2：子查询 select employee_id ,last_name,job_id ,department_id from employees where employee_id in ( select distinct manager_id from employees ); #方式三 select employee_id ,last_name,job_id ,department_id from employees e1 where exists( select * from employees e2 where e1.employee_id = e2.manager_id ); #13.各个部门中 最高工资中最低的那个部门的 最低工资是多少? select salary from employees #方式一 where department_id = ( select department_id from employees group by department_id having max(salary) = ( #查询最低工资的部门 select min(max_sal) from ( # 查詢各个部门得到最高工資 select max(salary) as max_sal from employees group by department_id ) t_dept_amx_sal ) ); #方二 select salary from employees where department_id = (( select department_id from employees group by department_id having max(salary) <= all( select max(salary) from employees group by department_id ) ) ); #方式三 select min(salary) from employees where department_id = ( select department_id from employees group by department_id having max(salary) = ( select max(salary) as max_sal from employees group by department_id order by max_sal asc limit 0 ,1 ) ); #方式四 select min(salary) from employees e,( select department_id, max(salary) max_sal from employees group by department_id order by max_sal asc limit 0,1 ) t_dept_max_sal where e.department_id =t_dept_max_sal.department_id ; #14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary #方式一 select last_name, department_id, email, salary from employees where employee_id = any ( select distinct manager_id from employees where department_id = ( select department_id from employees group by department_id having avg(salary) = ( select max(salary) from ( select avg(salary) as salary from employees group by department_id ) t_dept_avg_sal ) ) ); #方式二. select last_name, department_id, email, salary from employees where employee_id = any ( select distinct manager_id from employees where 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 last_name, department_id, email, salary from employees where employee_id in ( select distinct manager_id from employees e,( select department_id ,avg(salary) avg_sal from employees group by department_id order by avg_sal desc limit 0 ,1 ) t_dept_avg_sal where e.department_id = t_dept_avg_sal.department_id ); #15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号 select department_id ##查询不job_id不包含ST_CLERK from departments where department_id not in ( select distinct department_id from employees where job_id = 'ST_CLERK' ); #方式二 select department_id from departments d where not exists( select * from employees e where e.department_id = d.department_id ); #16. 选择所有没有管理者的员工的last_name # 方式一 select last_name from employees emp where not exists( select * from employees mgr where emp.manager_id = mgr.employee_id ); #17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan' #方式一 select employee_id ,last_name,phone_number, salary from employees where manager_id = ( select employee_id from employees where last_name = 'De Haan' ); #方式二 select employee_id ,last_name,phone_number, salary from employees e1 where exists( select employee_id from employees e2 where e1.manager_id = e2.employee_id and last_name = 'De Haan' ); #18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询） #方式1：使用相关子查询 select department_id, last_name,salary from employees e1 where salary >( select avg(salary) as salary from employees e2 where e2.department_id = e1.department_id ); # 方式二 在FROM中声明子查询 select e.last_name,e.salary,e.department_id from employees e ,( select department_id,avg(salary) as salary from employees group by department_id )t_dept_avg_sal where e.department_id = t_dept_avg_sal.department_id and e.salary > t_dept_avg_sal.salary ; #方式2：在FROM中声明子查询 SELECT e.last_name,e.salary,e.department_id FROM employees e,( SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id) t_dept_avg_sal WHERE e.department_id = t_dept_avg_sal.department_id AND e.salary > t_dept_avg_sal.avg_sal ; #19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询） select department_name from departments d where 5 < ( select count(*) from employees e where e.department_id = d.department_id ); #20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询） select country_id from locations l where 2 < ( select count(*) from departments d where l.location_id = d.location_id );
子查询首先需要创建表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 代码示例题目：查询平均工资最低的部门id#方式1： SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) = ( SELECT MIN(avg_sal) FROM ( SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id ) dept_avg_sal ); #方式2： SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary) <= ALL ( SELECT AVG(salary) avg_sal FROM employees GROUP BY department_id );MySQL中聚合函数是不能嵌套使用的。3.3 空值问题mysql> SELECT last_name -> FROM employees -> WHERE employee_id NOT IN ( -> SELECT manager_id -> FROM employees -> ); Empty set (0.01 sec)4. 相关子查询4.1 相关子查询执行流程如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。4.2 代码示例题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id方式一：相关子查询SELECT last_name, salary, department_id FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department_id = e.department_id );方式二：在 FROM 中使用子查询SELECT last_name,salary,e1.department_id FROM employees e1,( SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id ) e2 WHERE e1.`department_id` = e2.department_id AND e2.dept_avg_sal < e1.`salary`;在ORDER BY 中使用子查询：题目：查询员工的id,salary,按照department_name排序SELECT employee_id,salary FROM employees e ORDER BY ( SELECT department_name FROM departments d WHERE e.`department_id` = d.`department_id` );结论：在SELECT中，除了GROUP BY 和 LIMIT之外，其他位置都可以声明子查询！4.3 EXISTS与NOT EXISTS关键字关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。如果在子查询中不存在满足条件的行：条件返回 FALSE继续在子查询中查找如果在子查询中存在满足条件的行：不在子查询中继续查找条件返回 TRUENOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。题目：查询公司管理者的employee_id，last_name，job_id，department_id信息方式一：SELECT employee_id, last_name, job_id, department_id FROM employees e1 WHERE EXISTS ( SELECT * FROM employees e2 WHERE e2.manager_id = e1.employee_id );方式二：自连接SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id FROM employees e1 JOIN employees e2 WHERE e1.employee_id = e2.manager_id;方式三：SELECT employee_id,last_name,job_id,department_id FROM employees WHERE employee_id IN ( SELECT DISTINCT manager_id FROM employees );题目：查询departments表中，不存在于employees表中的部门的department_id和department_nameSELECT department_id,department_name FROM departments d WHERE NOT EXISTS ( SELECT * FROM employees e WHERE d.`department_id` = e.`department_id` );题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。
流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。函数用法IF(value,value1,value2)如果value的值为TRUE，返回value1，否则返回value2IFNULL(value1, value2)如果value1不为NULL，返回value1，否则返回value2CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2.... [ELSE resultn] END相当于Java的if...else if...else...CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END相当于Java的switch...case...IFSELECT IF(1 > 0,'正确','错误') ->正确 IFNULLSELECT IFNULL(null,'Hello Word') ->Hello WordCASE WHENSELECT CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END ->1 > 0流程控制/* IF单条件使用 //**如果大于等于8000算高工资,否则算低工资*/ select last_name ,salary ,if(salary >= 8000 ,'高工资' ,'低工资') from employees;IF多条件使用/**如果 commission_pct不等于null,那就取它本身的值,如果等于null,则给它默认为0*/ select last_name ,commission_pct ,if(commission_pct is not null ,commission_pct ,0) 'details', /**并且计算它一年赚了多少钱*/ salary * 12 * ( 1 +if(commission_pct is not null ,phone_number ,0)) 'annual_sal' from employeesIFNULL使用/** 判断commission_pct为null 则输出0*/ select last_name ,commission_pct ,ifnull(commission_pct,0) 'details' from employees CASE WHEN 使用基本语法CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2.... [ELSE resultn] END 相当于Java的if...else if...else...示例SQL1SELECT last_name ,salary , /** 判断salary >= 15000 = 高薪 salary >=10000 ='种薪 salary >=8000 ='底薪' 否则等于 贫困户 END:代表结束 */ CASE WHEN salary >=15000 THEN '高薪' WHEN salary >=10000 THEN '中新' WHEN salary >=8000 THEN '底薪' ELSE '贫困户' END 'Salary'from employees ;示例SQL2需求：/** 查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其 工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数。 */ select employee_id ,last_name ,department_id ,salary, CASE department_id WHEN 10 THEN salary * 1.1 WHEN 20 THEN salary * 1.2 WHEN 20 THEN salary * 1.3 ELSE salary * 1.4 END "details" /**details是别名*/ from employees where department_id in(10 ,20 ,30) ;示例SQL3/** 查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其 工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数。 */ select employee_id ,last_name ,department_id ,salary, CASE department_id WHEN 10 THEN salary * 1.1 WHEN 20 THEN salary * 1.2 WHEN 20 THEN salary * 1.3 ELSE salary * 1.4 END "details" /**details是别名*/ from employees where department_id in(10 ,20 ,30) ;示例SQL4SELECT oid, `status`, CASE `status` WHEN 1 THEN '未付款' WHEN 2 THEN '已付款' WHEN 3 THEN '已发货' WHEN 4 THEN '确认收货' ELSE '无效订单' END FROM t_order; 示例SQL5多个Count统计,统计未发货,已发货和已结完的订单SELECT count(CASE WHEN state = 0 THEN 'noCount' END) AS noCount, /** 未发货*/ count(CASE WHEN state = 1 THEN 'yesCount' END) AS yesCount, /**已发货*/ count(CASE WHEN state = 2 THEN 'finishCount' END) AS finishCount /**已完结*/ FROM `order` ; from LOAN.LOAN_CASE_CENTRE loan_case_centre 输出 +-------+--------+-----------+ |noCount|yesCount|finishCount| +-------+--------+-----------+ |2 |4 |1 | +-------+--------+-----------+