SQL学习：聚合函数、通配符、联合查询与连接查询

SQL学习笔记第三天：聚合函数、通配符、联合查询与连接查询

今天是我学习SQL的第三天，主要学习了聚合函数、通配符使用、联合查询(UNION)以及各种连接查询(JOIN)。下面我将详细记录今天的学习内容。

一、聚合函数(Aggregate Functions)

聚合函数是对一组值执行计算并返回单一值的函数。常用的聚合函数包括：

-- 1. 取得员工人数
SELECT COUNT(*) FROM `employee`;
-- COUNT(*)计算表中的记录总数

SELECT COUNT(`sup_id`) FROM `employee`;
-- COUNT(column)计算特定列非NULL值的数量

-- 2. 取得所有出生在1970-01-01之后的女性员工人数
SELECT COUNT(*) 
FROM `employee` 
WHERE `birth_date` > '1970-01-01' AND `sex` = 'F';
-- 结合WHERE条件进行计数

-- 3. 取得员工的平均薪水
SELECT AVG(`salary`) FROM `employee`;
-- AVG()计算平均值

-- 4. 取得所有员工薪水的总和
SELECT SUM(`salary`) FROM `employee`;
-- SUM()计算总和

-- 5. 取得薪水最高的员工
SELECT MAX(`salary`) FROM `employee`;
-- MAX()找最大值

-- 6. 取得薪水最低的员工
SELECT MIN(`salary`) FROM `employee`;
-- MIN()找最小值

二、通配符(Wildcards)

通配符用于在WHERE子句中搜索特定模式的数据：

-- % 匹配任意数量的字符（包括零个字符）
-- _ 匹配单个字符

-- 1.1 取得电话号码末尾为335的客户
SELECT * FROM `client` WHERE `phone` LIKE '%335';

-- 1.2 取得电话号码开头为335的客户
SELECT * FROM `client` WHERE `phone` LIKE '254%';

-- 1.3 取得电话号码中间为335的客户
SELECT * FROM `client` WHERE `phone` LIKE '%354%';

-- 2. 取得姓艾的客户
SELECT * FROM `client` WHERE `client_name` LIKE '艾%';

-- 3. 取得生日在十二月的员工(前面有五个_表示2000-)
SELECT * FROM `employee` WHERE `birth_date` LIKE '_____12%';

-- 3.1 取得生日在10日的员工
SELECT * FROM `employee` WHERE `birth_date` LIKE '________10';

三、联合查询(UNION)

UNION操作符用于合并两个或多个SELECT语句的结果集：

-- 注意：UNION查询的列数必须相同，且对应列的数据类型要兼容

-- 1. 员工名字union客户名字（错误示例，列数不匹配）
SELECT `name`, `sex` FROM `employee`
UNION
SELECT `client_name` FROM `client`
UNION
SELECT `branch_name` FROM `branch`;

-- 修正后的正确写法
SELECT `name` AS `names` FROM `employee`
UNION
SELECT `client_name` FROM `client`
UNION
SELECT `branch_name` FROM `branch`;

-- 2. 员工id + 员工名字 union 客户id + 客户名字
SELECT `emp_id` AS `total_id`, `name` AS `total_name`
FROM `employee`
UNION 
SELECT `client_id`, `client_name`
FROM `client`;

-- 3. 员工薪水 union 销售金额
SELECT `salary` AS `total_money` FROM `employee`
UNION
SELECT `total_sales` FROM `works_with`;

四、连接查询(JOIN)

JOIN用于根据两个或多个表中的列之间的关系，从这些表中查询数据：

-- 首先添加一个测试数据
INSERT INTO `branch` VALUES (4,'偷懒',NULL); 

-- 1. 取得所有部门经理的名字（左连接）
SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee` LEFT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
-- LEFT JOIN返回左表(employee)的所有记录，即使右表(branch)中没有匹配

-- 2. 取得所有部门经理的名字（右连接）
SELECT `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name`
FROM `employee` RIGHT JOIN `branch`
ON `employee`.`emp_id` = `branch`.`manager_id`;
-- RIGHT JOIN返回右表(branch)的所有记录，即使左表(employee)中没有匹配

-- 清理测试数据
DELETE FROM `branch` WHERE `branch_id` = 4;
-- 修复数据
INSERT INTO `branch` VALUES(1, '行政', 207);

五、子查询(Subquery)

子查询是嵌套在其他SQL查询中的查询：

-- 1. 找出研发部门的经理名字
SELECT `name`
FROM `employee`
WHERE `emp_id` = (
    SELECT `manager_id`
    FROM `branch`
    WHERE `branch_name` = '研发'
);
-- 先执行内层查询找到研发部门的经理ID，再执行外层查询

-- 2. 找出一位客户销售金额超过50000的员工名字
SELECT `name`
FROM `employee` 
WHERE `emp_id` IN(
    SELECT `emp_id`
    FROM `works_with`
    WHERE `total_sales` > 50000
);
-- 使用IN因为内层查询可能返回多个结果

六、ON DELETE约束

定义外键时可以指定ON DELETE行为：

CREATE TABLE `branch`(
    `branch_id` INT PRIMARY KEY,
    `branch_name` VARCHAR(20),
    `manager_id` INT,
    FOREIGN KEY (`manager_id`) 
    REFERENCES `employee` (`emp_id`) 
    ON DELETE SET NULL
    -- 当引用的员工被删除时，manager_id设为NULL
);

CREATE TABLE `works_with` (
    `emp_id` INT,
    `client_id` INT,
    `total_sales` INT,
    PRIMARY KEY(`emp_id`,`client_id`),
    FOREIGN KEY(`emp_id`) 
    REFERENCES `employee` (`emp_id`) 
    ON DELETE CASCADE,
    -- 当员工被删除时，相关记录也被删除
    FOREIGN KEY(`client_id`) 
    REFERENCES `client`(`client_id`) 
    ON DELETE CASCADE
    -- 当客户被删除时，相关记录也被删除
);

学习总结

今天的学习内容涵盖了SQL中许多重要概念：

1. 聚合函数：用于对数据进行统计计算，如计数、求和、平均值等
2. 通配符：LIKE配合%和_可以实现灵活的模式匹配查询
3. UNION：合并多个查询结果，但要注意列数和数据类型必须匹配
4. JOIN：多表连接查询，特别是LEFT JOIN和RIGHT JOIN的区别
5. 子查询：嵌套查询，可以作为条件或数据源
6. ON DELETE：定义外键约束时的级联操作