数据库基础——SQL基础查询_sql数据库基础查询-优快云博客

本文链接：https://blog.youkuaiyun.com/woshihlf/article/details/149096133

参考资料：https://blog.youkuaiyun.com/weixin_43896929/article/details/120750965

数据库基础——SQL基础查询

使用as关键字起别名

 mysql> select deptno,dname as deptname from dept;
      +--------+------------+
      | deptno | deptname   |
      +--------+------------+
      |     10 | ACCOUNTING |
      |     20 | RESEARCH   |
      |     30 | SALES      |
      |     40 | OPERATIONS |
      +--------+------------+

注意：只是将显示的查询结果列名显示为deptname，原表列名还是叫：dname
记住：select语句是永远都不会进行修改操作的。（因为只负责查询）

<> 或!= 不等于

查询薪资不等于800的员工姓名和编号？
  select empno,ename from emp where sal != 800;
  select empno,ename from emp where sal <> 800; // 小于号和大于号组成的不等号

逻辑运算符优先级

在SQL中，当AND和OR同时出现时，存在优先级问题：

AND的优先级高于OR，类似于数学中乘法优先于加法
执行顺序是从左到右，但会先计算所有AND条件，再计算OR条件

示例说明

-- 条件：工资大于1000并且部门是20，或者部门是30的所有员工
-- 由于AND优先级高，实际执行顺序是：(sal > 1000 AND deptno = 20) OR deptno = 30
SELECT * FROM emp WHERE sal > 1000 AND deptno = 20 OR deptno = 30;

-- 如果想改变优先级，让OR先执行，需要加括号
-- 条件：工资大于1000，并且(部门是20或者30)的员工
SELECT * FROM emp WHERE sal > 1000 AND (deptno = 20 OR deptno = 30);

最佳实践：当不确定优先级时，使用括号明确指定执行顺序。

IN 运算符

IN相当于多个OR条件的简写形式：

-- 这两种写法等价
SELECT * FROM emp WHERE deptno = 10 OR deptno = 20 OR deptno = 30;
SELECT * FROM emp WHERE deptno IN (10, 20, 30);

-- NOT IN 表示不在这个范围内
SELECT * FROM emp WHERE deptno NOT IN (10, 30);

NOT 运算符

NOT用于取反，常用于IS或IN条件：

-- 查询佣金不为空的员工
SELECT * FROM emp WHERE comm IS NOT NULL;

-- 查询不在10和30部门的员工
SELECT * FROM emp WHERE deptno NOT IN (10, 30);

LIKE 模糊查询

LIKE用于模式匹配，支持两个通配符：

%：匹配任意多个字符（包括零个字符）
_：匹配任意单个字符

示例详解

-- 1. 找出名字以T结尾的
SELECT ename FROM emp WHERE ename LIKE '%T';
-- 匹配：Scott (以t结尾)，不匹配：Turner

-- 2. 找出名字以K开始的
SELECT ename FROM emp WHERE ename LIKE 'K%';
-- 匹配：King，不匹配：Blake

-- 3. 找出第二个字母是A的
SELECT ename FROM emp WHERE ename LIKE '_A%';
-- 匹配：James (J-A-m-e-s)，不匹配：Adams (A-d-a-m-s)

-- 4. 找出第三个字母是R的
SELECT ename FROM emp WHERE ename LIKE '__R%';
-- 匹配：Turner (T-u-R-n-e-r)，不匹配：Miller (M-i-L-l-e-r)

注意事项

LIKE匹配默认不区分大小写（取决于数据库配置）

如果要匹配%或_本身，需要使用转义字符：

-- 查找包含"20%"的数据
SELECT * FROM products WHERE description LIKE '%20\%%' ESCAPE '\';

模糊查询通常会导致全表扫描，在大表上性能较差

SQL分组查询(Group By)详解

基本概念

分组查询是将表中的数据按照一个或多个列的值进行分类，然后对每个分组进行聚合计算（如求和、平均值、最大值等）。

基本语法

SELECT 
    分组字段, 
    聚合函数(字段)
FROM 
    表名
[WHERE 条件]
GROUP BY 
    分组字段
[HAVING 分组后条件]
[ORDER BY 排序字段];

核心规则

SELECT后面的字段限制：
- 只能包含：
  - GROUP BY子句中出现的字段
  - 聚合函数（如SUM, AVG, MAX, MIN, COUNT等）
- 不能包含其他非分组字段
为什么有这个限制：
- 分组后，每个分组只返回一行结果
- 非分组字段可能有多个值，无法确定显示哪一个

错误示例分析

-- 错误示例（在Oracle中会报错）
SELECT ename, job, SUM(sal) FROM emp GROUP BY job;

问题分析：

按job分组后，每个job分组可能对应多个ename
无法确定应该显示哪个ename，所以Oracle会报错
MySQL宽松模式下会显示第一个遇到的ename，但这通常不是我们想要的结果

正确用法示例

示例1：每个部门的平均薪资

SELECT 
    deptno, 
    AVG(sal) AS avg_salary
FROM 
    emp
GROUP BY 
    deptno;

示例2：每个部门不同岗位的最高薪资

SELECT 
    deptno, 
    job, 
    MAX(sal) AS max_salary
FROM 
    emp
GROUP BY 
    deptno, job;

这里deptno,job两个字段联合分组，相当于把这两个字段组合起来作为分组依据。

HAVING子句

HAVING用于对分组后的结果进行过滤，类似于WHERE但作用于分组后的数据。

WHERE与HAVING的区别

特性	WHERE	HAVING
执行时机	在分组前过滤数据	在分组后过滤数据
可否用聚合	不能直接使用聚合函数	可以使用聚合函数
性能	优先使用，效率更高	后使用，效率较低
使用场景	过滤原始数据	过滤分组后的结果

示例：找出平均薪资大于2000的部门

SELECT 
    deptno, 
    AVG(sal) AS avg_salary
FROM 
    emp
GROUP BY 
    deptno
HAVING 
    AVG(sal) > 2000;

完整SQL执行顺序

FROM：确定数据来源表
WHERE：对原始数据进行筛选
GROUP BY：按照指定字段分组
HAVING：对分组后的结果进行筛选
SELECT：选择要显示的列
ORDER BY：对最终结果排序

实际应用技巧

多列分组：当需要更细粒度的分组时，可以使用多个字段组合分组

SELECT 
    deptno, 
    job, 
    COUNT(*) AS employee_count
FROM 
    emp
GROUP BY 
    deptno, job;

分组后排序：

SELECT 
    deptno, 
    AVG(sal) AS avg_salary
FROM 
    emp
GROUP BY 
    deptno
ORDER BY 
    avg_salary DESC;

WHERE和HAVING组合使用：

-- 先筛选出薪资>1000的员工，再按部门分组，最后筛选出平均薪资>2000的部门
SELECT 
    deptno, 
    AVG(sal) AS avg_salary
FROM 
    emp
WHERE 
    sal > 1000
GROUP BY 
    deptno
HAVING 
    AVG(sal) > 2000;

常见错误

SELECT包含非分组字段：

-- 错误：ename不是分组字段
SELECT ename, deptno, AVG(sal) FROM emp GROUP BY deptno;

在WHERE中使用聚合函数：

-- 错误：不能在WHERE中使用聚合函数
SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal) > 2000 GROUP BY deptno;

混淆GROUP BY和ORDER BY：

-- 错误：GROUP BY不是用来排序的
SELECT deptno, AVG(sal) FROM emp GROUP BY AVG(sal);

掌握分组查询是SQL进阶的重要一步，理解这些概念后，你就能处理更复杂的数据分析需求了。

SQL数据处理函数详解

数据处理函数是SQL中用于对数据进行转换、计算和格式化的工具，可以分为单行处理函数和多行处理函数两大类。

一、单行处理函数

单行处理函数的特点：一个输入对应一个输出，即对每一行数据单独处理。

1. 字符串处理函数

(1) LOWER/UPPER - 大小写转换

-- 转换为小写
SELECT LOWER(ename) AS lowercase_name FROM emp;

-- 转换为大写
SELECT UPPER(ename) AS uppercase_name FROM emp;

(2) SUBSTR - 字符串截取

-- 语法：SUBSTR(字符串, 起始位置, 长度)
-- 注意：SQL中字符串索引通常从1开始

-- 获取名字的第一个字母
SELECT SUBSTR(ename, 1, 1) AS first_letter FROM emp;

-- 获取名字的前三个字母
SELECT SUBSTR(ename, 1, 3) AS name_prefix FROM emp;

(3) CONCAT - 字符串连接

-- 连接员工编号和姓名
SELECT CONCAT(empno, '-', ename) AS emp_info FROM emp;

-- 实际应用：生成邮箱地址
SELECT CONCAT(LOWER(ename), '@company.com') AS email FROM emp;

(4) LENGTH - 字符串长度

-- 获取员工姓名的长度
SELECT ename, LENGTH(ename) AS name_length FROM emp;

(5) TRIM - 去除空格

-- 去除两边空格
SELECT * FROM emp WHERE ename = TRIM('   KING');

-- 去除左边空格(LTRIM)或右边空格(RTRIM)
SELECT LTRIM('   Hello') AS left_trimmed;
SELECT RTRIM('Hello   ') AS right_trimmed;

2. 数值处理函数

(1) ROUND - 四舍五入

-- 保留整数
SELECT ROUND(1236.567, 0) AS result;  -- 1237

-- 保留1位小数
SELECT ROUND(1236.567, 1) AS result;  -- 1236.6

-- 保留到十位
SELECT ROUND(1236.567, -1) AS result; -- 1240

(2) RAND - 随机数

-- 生成0-1之间的随机数
SELECT RAND() AS random_num;

-- 生成0-100的随机整数
SELECT ROUND(RAND()*100, 0) AS random_int;

3. 条件处理函数

(1) CASE WHEN - 条件判断

-- 根据不同职位调整工资显示
SELECT 
    ename,
    job, 
    sal AS old_salary,
    CASE job 
        WHEN 'MANAGER' THEN sal*1.1 
        WHEN 'SALESMAN' THEN sal*1.5 
        ELSE sal 
    END AS new_salary
FROM emp;

(2) IFNULL - 空值处理

-- 将NULL值替换为0
SELECT 
    ename, 
    sal, 
    comm, 
    IFNULL(comm, 0) AS commission,
    sal + IFNULL(comm, 0) AS total_income
FROM emp;

4. 日期处理函数

(1) DATE_FORMAT - 日期格式化

-- 将日期格式化为YYYY-MM-DD形式
SELECT ename, DATE_FORMAT(hiredate, '%Y-%m-%d') AS hire_date FROM emp;

-- 常用格式符号：
-- %Y 四位年份
-- %y 两位年份
-- %m 月份(01-12)
-- %d 日(01-31)
-- %H 小时(00-23)
-- %i 分钟(00-59)
-- %s 秒(00-59)

(2) STR_TO_DATE - 字符串转日期

-- 将字符串转换为日期类型
SELECT STR_TO_DATE('2023-01-15', '%Y-%m-%d') AS date_value;

二、多行处理函数（分组函数）

多行处理函数的特点：多个输入对应一个输出，通常与GROUP BY一起使用。

1. 常用分组函数

-- 计数
SELECT COUNT(*) AS total_employees FROM emp;

-- 求和
SELECT SUM(sal) AS total_salary FROM emp;

-- 平均值
SELECT AVG(sal) AS avg_salary FROM emp;

-- 最大值
SELECT MAX(sal) AS max_salary FROM emp;

-- 最小值
SELECT MIN(sal) AS min_salary FROM emp;

2. 分组函数注意事项

必须先分组后使用：如果没有明确分组，则默认整表为一组
自动忽略NULL值：不需要特别处理NULL

COUNT的区别：

-- 统计有佣金的人数(忽略NULL)
SELECT COUNT(comm) FROM emp;

-- 统计总行数(不忽略NULL)
SELECT COUNT(*) FROM emp;

不能在WHERE中直接使用：

-- 错误写法
SELECT ename FROM emp WHERE sal > AVG(sal);

-- 正确写法(使用子查询)
SELECT ename FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

三、DISTINCT关键字

用于去除查询结果中的重复记录。

基本用法

-- 单列去重
SELECT DISTINCT job FROM emp;

-- 多列联合去重
SELECT DISTINCT job, deptno FROM emp;

注意事项

DISTINCT必须放在所有字段前面
DISTINCT对NULL值也会去重

可以与分组函数结合使用

-- 计算不同职位的数量
SELECT COUNT(DISTINCT job) AS job_types FROM emp;

四、函数综合应用示例

-- 综合应用：格式化员工信息，计算奖金，统计部门数据
SELECT 
    deptno,
    COUNT(*) AS emp_count,
    CONCAT('Dept-', deptno) AS dept_name,
    ROUND(AVG(sal), 2) AS avg_salary,
    SUM(CASE WHEN job = 'SALESMAN' THEN sal*1.5 ELSE sal END) AS total_compensation
FROM 
    emp
WHERE 
    LENGTH(ename) > 4
GROUP BY 
    deptno
HAVING 
    AVG(sal) > 2000
ORDER BY 
    avg_salary DESC;

掌握这些数据处理函数可以极大地提高SQL查询的灵活性和数据处理能力，是进行复杂数据分析和报表生成的基础。

SQL连接查询详解

连接查询是SQL中最重要和最常用的功能之一，它允许我们从多个表中获取关联数据。下面我将详细讲解各种连接类型，并提供丰富的示例。

一、连接查询基本概念

连接查询是将多个表中的数据通过关联条件组合在一起的查询方式。主要分为两大类：

内连接(INNER JOIN)：只返回满足连接条件的记录
外连接(OUTER JOIN)：返回主表所有记录，从表不满足条件的显示为NULL

二、内连接详解

1. 等值连接

等值连接是最常见的连接类型，使用等号(=)作为连接条件。

示例1：查询员工及其部门信息

-- SQL92语法(老式写法)
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- SQL99语法(推荐)
SELECT e.ename, d.dname
FROM emp e
INNER JOIN dept d
ON e.deptno = d.deptno;

示例2：查询在NEW YORK工作的员工

SELECT e.ename, e.job, d.loc
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.loc = 'NEW YORK';

2. 非等值连接

连接条件不是等号，而是其他比较运算符(BETWEEN, >, <等)。

示例：查询员工薪资等级

SELECT e.ename, e.sal, s.grade
FROM emp e
JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;

3. 自连接

将同一张表当作两张表来连接查询。

示例：查询每个员工及其经理名字

SELECT 
    a.ename AS employee_name,
    b.ename AS manager_name
FROM emp a
JOIN emp b
ON a.mgr = b.empno;

三、外连接详解

外连接会保留主表的所有记录，即使从表中没有匹配的记录。

1. 左外连接(LEFT JOIN)

保留左表(主表)的所有记录，右表不匹配的显示NULL。

示例：查询所有部门及员工(包括没有员工的部门)

SELECT d.deptno, d.dname, e.ename
FROM dept d
LEFT JOIN emp e
ON d.deptno = e.deptno;

2. 右外连接(RIGHT JOIN)

保留右表(主表)的所有记录，左表不匹配的显示NULL。

示例：查询所有员工及部门信息(包括未分配部门的员工)

SELECT e.ename, d.dname
FROM emp e
RIGHT JOIN dept d
ON e.deptno = d.deptno;

3. 左右连接转换

任何右连接都可以转换为左连接，只需调换表顺序：

-- 这两个查询等价
SELECT e.ename, d.dname FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno;
SELECT d.dname, e.ename FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno;

四、多表连接

可以连接三张或更多表，支持混合使用内外连接。

示例1：查询员工、部门及薪资等级

SELECT e.ename, d.dname, s.grade
FROM emp e
JOIN dept d ON e.deptno = d.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

示例2：复杂多表连接(包含内外连接混合)

SELECT 
    e.ename AS employee,
    m.ename AS manager,
    d.dname AS department,
    s.grade AS salary_grade
FROM emp e
LEFT JOIN emp m ON e.mgr = m.empno  -- 左连接获取经理信息(允许经理为NULL)
JOIN dept d ON e.deptno = d.deptno  -- 内连接获取部门信息
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;  -- 内连接获取薪资等级

五、连接查询注意事项

性能考虑：连接操作可能很耗资源，应确保连接条件字段有索引

歧义列名：多表连接时，相同列名需用表名或别名限定

SELECT e.ename, d.deptno  -- 错误，deptno在两张表都存在
SELECT e.ename, d.deptno AS dept_no  -- 正确

连接条件：确保连接条件合理，避免产生笛卡尔积(不加条件的连接)
NULL处理：外连接中不匹配的列会显示NULL，注意处理

六、特殊连接场景

1. 全外连接(FULL OUTER JOIN)

返回左右两表的全部记录，不匹配的显示NULL。MySQL不直接支持，但可通过UNION实现：

SELECT e.ename, d.dname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno
UNION
SELECT e.ename, d.dname
FROM emp e
RIGHT JOIN dept d ON e.deptno = d.deptno
WHERE e.empno IS NULL;

2. 交叉连接(CROSS JOIN)

返回两表的笛卡尔积(所有可能的组合)，慎用：

-- 以下两种写法等价
SELECT e.ename, d.dname FROM emp e CROSS JOIN dept d;
SELECT e.ename, d.dname FROM emp e, dept d;

掌握这些连接查询技术，你就能处理绝大多数多表数据查询需求了。实际应用中，建议先明确需要哪些数据，再设计合适的连接方式。

SQL LIMIT 子句详解

LIMIT 是 SQL 中用于限制查询结果数量的子句，特别常用于分页查询。下面我将详细讲解 LIMIT 的使用方法、分页实现原理以及 SQL 语句的执行顺序。

一、LIMIT 基本用法

1. 基本语法格式

SELECT 列名 FROM 表名 LIMIT [offset,] row_count;

或

SELECT 列名 FROM 表名 LIMIT row_count OFFSET offset;

2. 常用示例

(1) 查询前N条记录

-- 查询前5名员工
SELECT ename, sal FROM emp ORDER BY sal DESC LIMIT 5;

-- 等价写法
SELECT ename, sal FROM emp ORDER BY sal DESC LIMIT 0, 5;

(2) 从指定位置开始查询

-- 从第3条记录开始，查询5条记录(即第3-7条)
SELECT ename, sal FROM emp ORDER BY sal DESC LIMIT 2, 5;

-- 等价写法(OFFSET语法)
SELECT ename, sal FROM emp ORDER BY sal DESC LIMIT 5 OFFSET 2;

二、分页查询实现

1. 分页基本公式

// Java中的分页计算
int pageNo = 3;    // 当前页码
int pageSize = 10; // 每页记录数
int startIndex = (pageNo - 1) * pageSize;

// SQL语句
String sql = "SELECT * FROM table LIMIT " + startIndex + ", " + pageSize;

2. 分页示例

假设每页显示3条记录：

-- 第1页 (记录1-3)
SELECT ename, job FROM emp ORDER BY empno LIMIT 0, 3;

-- 第2页 (记录4-6)
SELECT ename, job FROM emp ORDER BY empno LIMIT 3, 3;

-- 第3页 (记录7-9)
SELECT ename, job FROM emp ORDER BY empno LIMIT 6, 3;

3. 实际应用案例

-- 分页查询薪资最高的员工(每页5条)
-- 第一页
SELECT ename, sal FROM emp ORDER BY sal DESC LIMIT 0, 5;

-- 第二页
SELECT ename, sal FROM emp ORDER BY sal DESC LIMIT 5, 5;

-- 第三页
SELECT ename, sal FROM emp ORDER BY sal DESC LIMIT 10, 5;

三、SQL语句执行顺序

完整的SELECT语句结构：

SELECT 
    [DISTINCT] 列名
FROM
    表名
[WHERE 条件]
[GROUP BY 分组字段]
[HAVING 分组后条件]
[ORDER BY 排序字段]
[LIMIT 分页参数];

执行顺序：

FROM - 确定数据来源
WHERE - 对原始数据过滤
GROUP BY - 分组
HAVING - 对分组结果过滤
SELECT - 选择要显示的列
ORDER BY - 排序
LIMIT - 限制结果数量

执行顺序示例

SELECT 
    deptno, COUNT(*) AS emp_count
FROM
    emp
WHERE
    sal > 1000
GROUP BY
    deptno
HAVING
    COUNT(*) > 2
ORDER BY
    emp_count DESC
LIMIT 2;

执行步骤解析：

从emp表获取数据
筛选sal > 1000的员工
按deptno分组
筛选员工数>2的部门
选择显示deptno和计数结果
按员工数降序排序
只返回前2条记录

四、LIMIT 高级用法

1. 与子查询结合使用

-- 查询薪资第6-10高的员工
SELECT ename, sal
FROM emp
ORDER BY sal DESC
LIMIT 5, 5;

2. 随机抽样数据

-- 随机获取5条记录
SELECT ename, job FROM emp ORDER BY RAND() LIMIT 5;

3. 分页查询优化

对于大数据表，使用WHERE条件优化分页：

-- 效率低的写法
SELECT * FROM large_table LIMIT 1000000, 10;

-- 效率高的写法(假设id是主键且有序)
SELECT * FROM large_table 
WHERE id > (SELECT id FROM large_table ORDER BY id LIMIT 1000000, 1)
ORDER BY id LIMIT 10;