mysql学习实践

-- 【 等值连接 sql99标准 】
-- 案例1.查询员工名、部门名
SELECT last_name,department_name
from employees e
INNER JOIN departments d
on e.department_id = d.department_id;-- 案例2.查询名字中包含e的员工名和工种名
SELECT last_name,job_title
from employees e
INNER JOIN jobs j
on e.job_id = j.job_id
WHERE e.last_name LIKE '%e%';-- 案例3.查询部门个数>3的城市名和部门个数（分组+筛选）
SELECT city,COUNT(*) 部门个数
from departments d
INNER JOIN locations l
ON d.location_id=l.location_id
GROUP BY city
HAVING COUNT(*)>3;-- 案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT department_name,COUNT(*) 员工个数
FROM employees e
INNER JOIN departments d
ON d.department_id = e.department_id
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) desc;
---------------------------------------------------------------------------------
-- 【 非等值连接 】---------------------------------------------------------------------------------
-- 【 自连接 】
-- 案例5.查询员工的名字和上级的名字
SELECT e.last_name, m.last_name 上级名字
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`;

2、外连接
应用场景：用于查询一个表中有，另一个表没有的记录
特点：

• 外连接的查询结果为主表中所有记录
  • 如果从表中有和它匹配的，则显示匹配的值
  • 如果从表中没有和它匹配的，则显示null
  • 外连接查询结果=内连接结果+主表中有而从表中没有的记录
• 左外连接，left join左边的是主表
• 右外连接，right join右边是主表
• 左外和右外交换两个表的顺序，可以实现同样的效果
• 全外连接=内连接的结果+表1中有但表2没有+表2中有但表1没有的

-- 1)、左外连接：left【outer】
-- 查询没有男朋友的女生名SELECT b.nameFROM beauty bLEFT JOIN boys boON b.`boyfriend_id` = bo.`id`WHERE bo.`id` IS NULL;-- 2)、右外连接：right 【outer】
-- 查询没有男朋友的女生名
SELECT b.name
FROM boys bo
RIGHT JOIN beauty b
ON bo.`id` = b.`boyfriend_id`
WHERE bo.`id` IS NULL-- 3)、全外连接：full【outer】
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id` = bo.id;

3、交叉连接：cross

 SELECT b.*,bo.*FROM beauty bCROSS JOIN boys bo;

查找重复数据

查找重复数据的字段值以及重复的次数

SELECT project_code, COUNT(*) AS count
FROM business_order
GROUP BY project_code
HAVING count > 1;

如果你只想查找重复数据，而不需要知道重复的次数，可以简化查询如下

SELECT project_code
FROM order_info
GROUP BY project_code
HAVING COUNT(*) > 1;

根据某个字段查询重复的数据，并取id最大的那条数据（用于商机列表展示）

SELECTMAX( id ) id,project_code
FROMbusiness_order 
WHEREproject_code = 'WZ23080203' 
GROUP BYproject_code 
-- ORDER BY id DESC;

将逗号分隔的字符串进行切割并统计个数

SELECT LENGTH(your_column) - LENGTH(REPLACE(your_column, ',', '')) + 1 AS count
FROM your_table;

这个查询使用了 REPLACE 函数将逗号替换为空字符串，然后计算原始字符串长度与替换后的字符串长度之差，并加上1，从而得到逗号分隔的字符串中的元素个数。

计算某个字段之和大于|等于某个值，更新审核状态

审批人员有两个，auditor = ‘101,102’ 的时候，代表两人均已审核通过，将status更改为审核通过状态
你可以使用UPDATE语句来同时判断 count 等于3的情况，并将 status 字段更改为1。以下是一个示例的MySQL查询：

UPDATE your_table
SET status = 1
WHERE LENGTH(your_column) - LENGTH(REPLACE(your_column, ',', '')) + 1 = 3;

根据前端传过来的List，进行更新、插入和删除操作

1. 首先，从数据库中获取与前端传来的List相关的所有数据。
2. 遍历前端传来的List，对于每个元素：
   • 如果数据库中存在该元素，执行更新操作。
   • 如果数据库中不存在该元素，执行插入操作。
3. 遍历数据库中的数据，对于每个元素：
   • 如果该元素不在前端传来的List中，执行删除操作。

// 前端传过来的List
List<YourObject> frontendList = ...;// 从数据库中获取相关数据
List<YourObject> dbList = yourDao.getAll();// 更新数据库中已存在的数据，插入不存在的数据
frontendList.forEach(frontendObj -> {boolean found = dbList.stream().anyMatch(dbObj -> dbObj.getId() == frontendObj.getId());if (found) {// 执行更新操作yourDao.update(frontendObj);} else {// 执行插入操作yourDao.insert(frontendObj);}
});// 删除未更新的数据
dbList.forEach(dbObj -> {boolean found = frontendList.stream().anyMatch(frontendObj -> frontendObj.getId() == dbObj.getId());if (!found) {// 执行删除操作yourDao.delete(dbObj.getId());}
});

使用Lambda表达式可以更简洁地遍历集合并进行条件判断。在上述代码中，我们使用 forEach 方法遍历前端传来的List和数据库中的数据，并使用 stream 方法结合 anyMatch 方法来判断是否存在相应的数据。根据判断结果执行更新、插入和删除操作。