Mysql实战

实战：

SQL语句类别：

• DDL语句：数据定义语言（定义不同的数据段，数据库，表，列，索引等数据对象）eg.create, drop, alter等
• DML语句：数据操纵语句（用于添加，删除，更新和查询数据库记录，并检查数据完整性）eg.insert, delete, update和select
• DCL语句 ：数据控制语句（控制不同的数据段直接的许可和访问级别）                                    eg.grant, revoke

语法注意：

条件表达式：

连接：

• LEFT JOIN（左连接）原理：返回左表的所有行，右表匹配不到的行会返回 NULL。

• 连接条件不严格，导致产生笛卡尔积，即每个记录被多次错误匹配，结果数据重复放大

表 A 有 3 行，表 B 有 4 行，连接时如果没有严格的条件，就会生成 3 * 4 = 12 行的组合，导致每个元素被重复计算。

分组：

• 不分组无聚合：逐行返回原始记录。
• 不分组聚合：对整个结果集进行一次聚合运算。
• 分组后不聚合：会返回每个分组中的第一条记录
• 分组后聚合：按列分组，对每个分组分别进行聚合计算。

具体语法：

DDL语句

DML语句

1. 基本查询语句

SELECT 语句

• SELECT: 用于从数据库中选择数据。

  SELECT column1, column2 FROM table_name;

• DISTINCT: 消除查询结果中的重复值。

  SELECT DISTINCT column1 FROM table_name;

• WHERE: 用于根据特定条件过滤数据。

  SELECT column1 FROM table_name WHERE condition;

JOIN语句 

JOIN 用于在查询中组合多个表的数据，根据相关列匹配行。

• INNER JOIN: 只返回两表中满足条件的匹配行。

  SELECT a.column1, b.column2
  FROM table1 a
  INNER JOIN table2 b ON a.id = b.id;
  

• LEFT JOIN (或 LEFT OUTER JOIN): 返回左表的所有记录，即使在右表中没有匹配，也会返回(必须有连接条件)

  SELECT a.column1, b.column2
  FROM table1 a
  LEFT JOIN table2 b ON a.id = b.id;
  

• RIGHT JOIN (或 RIGHT OUTER JOIN): 返回右表的所有记录，即使在左表中没有匹配。

  SELECT a.column1, b.column2
  FROM table1 a
  RIGHT JOIN table2 b ON a.id = b.id;
  

• FULL OUTER JOIN: 返回两表中所有匹配和不匹配的行。

  SELECT a.column1, b.column2
  FROM table1 a
  FULL OUTER JOIN table2 b ON a.id = b.id;
  

• CROSS JOIN: 返回两表的笛卡尔积（所有可能的组合）。

  SELECT a.column1, b.column2
  FROM table1 a
  CROSS JOIN table2 b;
  

函数 

常见的聚合函数

这些函数常与 GROUP BY 一起使用：

• COUNT()：计算分组中的行数。

  • 示例：COUNT(*) 计算行数。

• SUM()：计算分组内列的总和。

  • 示例：SUM(salary) 计算工资总和。

• AVG()：计算分组内列的平均值。

  • 示例：AVG(salary) 计算平均工资。

• MIN()：返回分组内列的最小值。

  • 示例：MIN(salary) 返回最低工资。

• MAX()：返回分组内列的最大值。

  • 示例：MAX(salary) 返回最高工资

字符串操作函数

1. LENGTH() / LEN()：计算字符串长度。

   SELECT LENGTH('text');  -- MySQL / PostgreSQL
   SELECT LEN('text');     -- SQL Server
   

2. CONCAT()：拼接多个字符串。

   SELECT CONCAT('Hello', 'World');  -- 输出: 'HelloWorld'
   

3. SUBSTRING() / SUBSTR()：截取子字符串。

   SELECT SUBSTRING('Hello', 1, 3);  -- 输出: 'Hel'
   

4. INSTR() / CHARINDEX()：查找子字符串位置。

   SELECT INSTR('Hello World', 'World');  -- 输出: 7

5. REPLACE()：替换字符串中的内容。

   SELECT REPLACE('Hello World', 'World', 'SQL');  -- 输出: 'Hello SQL'
   

6. UPPER() / LOWER()：转换为大写或小写。

   SELECT UPPER('hello');  -- 输出: 'HELLO'
   

7. TRIM()：去除字符串两端的空格。

   SELECT TRIM('  text  ');  -- 输出: 'text'
   

8. LEFT() / RIGHT()：从左或右提取字符。

   SELECT LEFT('Hello', 2);  -- 输出: 'He'
   

9. STRCMP()：比较两个字符串。

   SELECT STRCMP('abc', 'def');  -- 输出: -1
   

10. REPEAT()：重复字符串指定次数。

SELECT REPEAT('Hello', 3);  -- 输出: 'HelloHelloHello'

数学函数

1. ROUND()：四舍五入到指定小数位。

   • 示例：ROUND(123.456, 2) → 123.46

2. CEIL()/CEILING()：向上取整，返回大于等于数值的最小整数。

   • 示例：CEIL(123.45) → 124

3. FLOOR()：向下取整，返回小于等于数值的最大整数。

   • 示例：FLOOR(123.45) → 123

4. ABS()：返回数值的绝对值。

   • 示例：ABS(-123) → 123

5. MOD()：取余数（模运算）。

   • 示例：MOD(10, 3) → 1

6. POWER()：返回数值的指定次幂。

   • 示例：POWER(2, 3) → 8

7. SQRT()：返回数值的平方根。

   • 示例：SQRT(25) → 5

8. EXP()：返回 e 的指定次幂。

   • 示例：EXP(1) → 2.71828

9. LN()：返回数值的自然对数（以 e 为底）。

   • 示例：LN(2.71828) → 1

10. LOG()：返回数值的指定底数的对数。

    • 示例：LOG(10, 100) → 2

11. SIGN()：返回数值的符号（1, 0, -1）。

    • 示例：SIGN(-25) → -1

12. TRUNCATE()：截断数值到指定小数位（不四舍五入）。

    • 示例：TRUNCATE(123.4567, 2) → 123.45

13. PI()：返回圆周率 π。

    • 示例：PI() → 3.141593

14. RADIANS() / DEGREES()：角度与弧度转换。

    • 示例：RADIANS(180) → 3.141593

15. RAND()：返回 0 到 1 之间的随机数。

    • 示例：RAND() → 0.123456

条件表达式

分组：

1. GROUP BY 

GROUP BY 语法用于将结果按某列或多列分组，通常与聚合函数（如 COUNT, SUM, AVG, MIN, MAX 等）结合使用。

基本语法：

SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
GROUP BY column1;

• column1：用于分组的列。
• AGGREGATE_FUNCTION(column2)：应用于每个分组的聚合函数，如 COUNT、SUM、AVG 等。

2. GROUP BY 和 HAVING

• HAVING 是一个过滤器，类似于 WHERE，但用于在分组后过滤数据。它用于根据聚合函数的结果进行筛选，而 WHERE 用于在分组之前对原始数据进行筛选。

示例：

SELECT department, COUNT(*) AS num_employees
FROM Employees
GROUP BY department
HAVING COUNT(*) > 10;  -- 只保留员工数大于10的部门

3. GROUP BY 的多列使用

可以根据多个列进行分组，每个列的组合将形成一个独特的分组：

SELECT department, job_title, COUNT(*)
FROM Employees
GROUP BY department, job_title;

• 在此示例中，查询根据 department 和 job_title 组合进行分组。

4. 与 ORDER BY 结合

• GROUP BY 的结果可以通过 ORDER BY 排序。ORDER BY 通常用于对查询结果的聚合值或分组列进行排序。

示例：

SELECT department, COUNT(*) AS num_employees
FROM Employees
GROUP BY department
ORDER BY num_employees DESC;

5. GROUP BY 与 JOIN 结合

GROUP BY 可以与 JOIN 一起使用，在连接多个表后根据特定列分组并执行聚合操作。

示例：

SELECT department, COUNT(*) AS num_employees
FROM Employees
GROUP BY department
ORDER BY num_employees DESC;

• 这个查询将返回每个顾客的访问次数，结合了 JOIN 和 GROUP BY。

6. GROUP BY 与 DISTINCT 的区别

• GROUP BY：用于对结果进行分组，并对分组后的数据进行聚合。
• DISTINCT：用于去除查询结果中的重复行，但不会对数据进行分组。

示例：

-- 使用 DISTINCT
SELECT DISTINCT customer_id FROM Visits;

-- 使用 GROUP BY
SELECT customer_id FROM Visits GROUP BY customer_id;

虽然两者可以在某些情况下返回相似结果，但 GROUP BY 主要用于聚合和分组，而 DISTINCT 主要用于去重。

排序：

1. 基本排序：升序和降序

-- 按 salary 升序排列 (默认升序)
SELECT * FROM Employees
ORDER BY salary ASC;

-- 按 salary 降序排列
SELECT * FROM Employees
ORDER BY salary DESC;

• ASC：升序排列（默认值）。
• DESC：降序排列。

2. 按多个列排序

-- 先按 department 升序排列，再按 salary 降序排列
SELECT * FROM Employees
ORDER BY department ASC, salary DESC;

• 可以先按一个列排序，若相同则按第二个列继续排序。

3. 按表达式排序

-- 计算年薪并按年薪降序排序
SELECT employee_id, salary * 12 AS annual_salary
FROM Employees
ORDER BY annual_salary DESC;

• 按计算结果或表达式排序。

4. 使用列的别名排序

-- 按别名 annual_salary 排序
SELECT employee_id, salary * 12 AS annual_salary
FROM Employees
ORDER BY annual_salary DESC;

• 列可以使用别名，ORDER BY 可以按别名排序。

5. 按列的顺序编号排序

-- 按查询结果中的第3列 salary 降序排序
SELECT first_name, last_name, salary
FROM Employees
ORDER BY 3 DESC;

• 可以通过列在 SELECT 语句中的顺序编号进行排序。

6. 处理 NULL 值的排序

-- 按 salary 排序，空值放在最后
SELECT * FROM Employees
ORDER BY salary ASC NULLS LAST;

• 默认情况下，NULL 视为最小值，使用 NULLS FIRST 或 NULLS LAST 来控制其位置。

7. 结合 LIMIT 使用

-- 获取工资最高的前5名员工
SELECT * FROM Employees
ORDER BY salary DESC
LIMIT 5;

• 限制查询返回的行数，结合 ORDER BY 实现分页或获取前几名记录。

8. 与聚合函数和 GROUP BY 结合

-- 按部门统计员工人数，并按人数降序排列
SELECT department, COUNT(*) AS num_employees
FROM Employees
GROUP BY department
ORDER BY num_employees DESC;

• 可以结合 GROUP BY 和聚合函数，对分组后的结果进行排序。

9. 与子查询结合

-- 对子查询结果先按 salary 排序，再按 employee_id 排序
SELECT * 
FROM (SELECT employee_id, salary FROM Employees ORDER BY salary DESC) AS Subquery
ORDER BY employee_id;

• ORDER BY 可以在子查询中使用，或者在外层查询中对子查询的结果进行排序。

10. 与窗口函数结合

-- 按 salary 排序为每个员工分配排名
SELECT employee_id, salary, 
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM Employees;

• 在使用窗口函数时，ORDER BY 定义窗口排序规则。

判断：

1. IS NULL 和 IS NOT NULL

• IS NULL 和 IS NOT NULL 是最常用的 IS 运算符之一，专门用于检查某个列的值是否为 NULL。
• 用途：在 SQL 中，NULL 代表缺失或未知的值。为了避免等号（=）和 NULL 的错误比较，SQL 语言提供了 IS NULL 和 IS NOT NULL 来进行相应的比较。

-- 查询列值为 NULL 的记录
SELECT * FROM table_name WHERE column_name IS NULL;

-- 查询列值不为 NULL 的记录
SELECT * FROM table_name WHERE column_name IS NOT NULL;

2. 布尔值比较 (IS TRUE / IS FALSE)

• IS TRUE, IS FALSE 和 IS UNKNOWN 是用于与布尔表达式进行比较的运算符。通常在 SQL 中布尔表达式返回 TRUE、FALSE 或 UNKNOWN（当涉及 NULL 时）。

使用场景：

• 当你需要明确检查一个布尔表达式的结果是否为 TRUE 或 FALSE 时，你可以使用 IS TRUE 或 IS FALSE。
• 当你想知道一个布尔表达式是否无法确定时（可能涉及 NULL），你可以使用 IS UNKNOWN。

-- 检查布尔条件是否为 TRUE
SELECT * FROM table_name WHERE boolean_condition IS TRUE;

-- 检查布尔条件是否为 FALSE
SELECT * FROM table_name WHERE boolean_condition IS FALSE;

-- 检查布尔条件是否为 UNKNOWN (当结果涉及 NULL)
SELECT * FROM table_name WHERE boolean_condition IS UNKNOWN;

3. IS DISTINCT FROM 和 IS NOT DISTINCT FROM

• IS DISTINCT FROM 和 IS NOT DISTINCT FROM 是一些数据库系统（如 PostgreSQL）特有的运算符，用于处理 NULL 的比较。这种运算符的好处是，它可以将 NULL 视为一个可比较的值，因此不会因为 NULL 比较而出错。

使用场景：

• 通常在比较两列时，当其中一列包含 NULL 时，使用 = 无法进行有效的比较。如果你想要忽略这种限制，可以使用 IS DISTINCT FROM。
• IS DISTINCT FROM 可以用来处理某一列与 NULL 的等式比较，而不会受 NULL 的不可比较性影响。

-- 如果两列的值不同（包括其中一个为 NULL），则返回 TRUE
SELECT * FROM table_name WHERE column1 IS DISTINCT FROM column2;

-- 如果两列的值相同（包括都是 NULL），则返回 TRUE
SELECT * FROM table_name WHERE column1 IS NOT DISTINCT FROM column2;