MySql进阶

MySql进阶

一、基本查询

1.1 SELECT

查询

//指定列
SELECT column1, column2, ...
FROM table_name;
//查询所有
SELECT * FROM table_name;

1.2 WHERE

条件查询

SELECT column1, column2, ...
FROM table_name
WHERE condition;

大于 >

大于等于 >=

小于 <

小于等于 <=

不等于 <>或!=

范围 between...and...

范围取值 in(...,...,...)

模糊查询 like

NULL is null

并 and或&&

或 or或||

非 not或!

例句1：查询部门为SALES且月薪大于5000的员工的名字

 SELECT first_name, last_name FROM employees WHERE department = 'Sales' AND salary > 50000;

例句2：查询部门为SALES且或月薪大于5000的员工的名字

SELECT first_name, last_name FROM employees WHERE department = 'Sales' OR department = 'Marketing';

例句3：查询部门为SALES或MARKETING的员工的名字

SELECT first_name, last_name FROM employees WHERE department IN ('Sales', 'Marketing');

1.3模糊查询

查姓名第一个是A的：

select * from user where name like 'A%'

查询姓名第一个是A和B的：

select * from user where name like 'A%' and name like '%B%'

既有又有：

select * from user where name like '%A%B%'

任意三个字内有A的

select * from user where name like '%_A_%'
select * from user where name like '%A__%'
select * from user where name like '%__A%'

只有三个字且有A

select * from user where name like 'A_'
select * from user where name like '_A'

使用 [ ]

这个查询会返回名字为“赵小明”，“李小明”，“孙小明”的用户，而不是名字为“赵李孙小明”的用户。

SELECT * FROM user WHERE realname LIKE '[赵李孙]小明';

使用 [^ ]

这个查询会排除名字为“陈A飞”，“陈B飞”，“陈C飞”，“陈D飞”的用户，返回“陈E飞”，“陈F飞”或其他“陈”姓且中间字符不是“A”到“D”的用户。

SELECT * FROM user WHERE realname LIKE '陈[^ABCD]飞';

1.4排序ORDER BY 子句

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

递增：ASC

递减：DESC

1.5分组GROUP BY

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;

SELECT department, COUNT(*) FROM employees GROUP BY department;

这个查询会返回每个部门的名称和每个部门中的员工数量。例如：

department	COUNT(*)
Sales	10
IT	15
HR	5

1.6HAVING子句

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1
HAVING aggregate_function(column2) condition;

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000;

这个查询会返回每个部门的名称和平均工资，并且仅包括那些平均工资超过 50000 的部门。例如：

department	AVG(salary)
Sales	52000
IT	55000

二、联合查询

2.1 INNER JOIN

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

SELECT employees.first_name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

应用：

• 获取员工及其所在部门的信息。
• 当需要两个表之间存在匹配关系时使用，例如用户和订单表之间的匹配订单信息。

2.2LEFT JOIN

用途： LEFT JOIN 返回左表中的所有记录，即使右表中没有匹配的记录。如果右表中没有匹配记录，结果中包含 NULL。

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

SELECT employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

应用：

• 获取所有员工及其所属部门的信息，即使某些员工没有分配到任何部门。
• 当需要左表中的所有记录以及可能的匹配记录时使用，例如用户及其可能的订单信息。

2.3RIGHT JOIN

用途： RIGHT JOIN 返回右表中的所有记录，即使左表中没有匹配的记录。如果左表中没有匹配记录，结果中包含 NULL。

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

SELECT employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

应用：

• 获取所有部门及其员工的信息，即使某些部门没有任何员工。
• 当需要右表中的所有记录以及可能的匹配记录时使用，例如所有产品及其可能的销售信息。

2.4FULL JOIN

MySQL 不直接支持 FULL JOIN，但可以通过 UNION 将 LEFT JOIN 和 RIGHT JOIN 的结果合并来实现。FULL JOIN 返回两个表中的所有记录，当两边没有匹配时返回 NULL。

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column
UNION
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

SELECT employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id
UNION
SELECT employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.department_id;

三、子查询

3.1 子查询作为结果集

用途： 子查询可以作为 WHERE 子句中的结果，用于进一步过滤主查询的结果。

SELECT column1, column2, …
FROM table_name
WHERE column_name OPERATOR (SELECT column_name FROM table_name WHERE condition);

3.2 子查询作为表

用途： 子查询可以作为 FROM 子句中的表，用于进一步处理查询结果。

SELECT column1, column2, …
FROM (SELECT column1, column2, … FROM table_name WHERE condition) AS subquery;

四、视图

基础

视图是基于 SQL 查询结果的虚拟表。它并不存储数据，而是存储 SQL 查询语句。当访问视图时，系统会执行该查询并返回结果。

CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;

示例： 假设我们有一个 employees 表和一个 departments 表，我们可以创建一个视图来显示每个员工的名字和他们所在的部门：

CREATE VIEW employee_departments AS
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

使用视图:

查询视图： 视图可以像表一样被查询：

SELECT * FROM employee_departments;

更新视图： 某些情况下，可以通过视图来更新基础表中的数据，但视图必须满足一定的条件，如没有使用聚合函数、没有使用 DISTINCT 等。

UPDATE employee_departments
SET department_name = 'Marketing'
WHERE first_name = 'John' AND last_name = 'Doe';

视图的优势:

简化复杂查询： 视图可以简化复杂的查询，将复杂的 SQL 语句封装成一个视图，使查询更加简洁和易读。

数据安全性： 视图可以限制用户对敏感数据的访问。例如，可以创建一个不包含工资信息的视图，以供特定用户使用：

数据独立性： 视图可以帮助保持数据独立性，隐藏数据库表的结构变化。即使基础表的结构发生变化，只需修改视图的定义，而无需修改所有使用该视图的查询。

逻辑数据集： 视图可以创建逻辑上的数据集，帮助将数据组织成有意义的结构。例如，创建一个视图来显示每个部门的员工数量：

视图的实际应用：

报表生成： 视图可以用于生成复杂的报表，简化报表生成的 SQL 查询。例如，创建一个销售报表视图，包含每个销售人员的销售额：

CREATE VIEW sales_report AS
SELECT s.salesperson_id, sp.name, SUM(o.amount) AS total_sales
FROM salespersons sp
JOIN orders o
ON sp.salesperson_id = o.salesperson_id
GROUP BY s.salesperson_id, sp.name;

数据转换： 视图可以用于数据转换和格式化。例如，创建一个视图来显示日期格式化后的订单信息：

CREATE VIEW formatted_orders AS
SELECT order_id, DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date, amount
FROM orders;

权限管理： 视图可以用于细粒度的权限管理。通过视图控制用户只能访问特定的数据列或行。例如，创建一个不包含工资信息的员工视图，只允许普通用户访问：

CREATE VIEW employee_basic_info AS
SELECT first_name, last_name, department_id
FROM employees;

五、函数

5.1聚合

计数：

SELECT COUNT(*) FROM employees WHERE department = 'Sales';

求和：

SELECT SUM(salary) FROM employees WHERE department = 'Sales';

均值：

SELECT AVG(salary) FROM employees WHERE department = 'Sales';

最大最小：

SELECT MAX(salary), MIN(salary) FROM employees WHERE department = 'Sales';

5.2字符串函数：

CONCAT(): 连接两个或多个字符串

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

SUBSTRING(): 提取子字符串

SELECT SUBSTRING(first_name, 1, 3) AS short_name FROM employees;

LENGTH(): 返回字符串长度

SELECT LENGTH(first_name) AS name_length FROM employees;

UPPER() 和 LOWER(): 将字符串转换为大写或小写

SELECT UPPER(first_name) AS upper_name FROM employees;

TRIM(): 去除字符串两端的空格

SELECT TRIM(first_name) AS trimmed_name FROM employees;

REPLACE(): 替换字符串中的子字符串

SELECT REPLACE(first_name, 'a', 'o') AS replaced_name FROM employees;

5.3日期函数

NOW(): 返回当前日期和时间

SELECT NOW() AS current_datetime;

CURDATE() 和 CURTIME(): 返回当前日期和时间

SELECT CURDATE() AS current_date;
SELECT CURTIME() AS current_time;

DATE_FORMAT(): 格式化日期

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS formatted_date;

DATEDIFF(): 计算两个日期之间的差异

SELECT DATEDIFF('2023-12-31', '2023-01-01') AS days_difference;

ATE_ADD() 和 DATE_SUB(): 增加或减少日期

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week;
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH) AS last_month;

5.4数学函数

ABS(): 返回绝对值

SELECT ABS(-10) AS absolute_value;

ROUND(): 四舍五入

SELECT ROUND(123.456, 2) AS rounded_value;

CEIL() 和 FLOOR(): 向上取整和向下取整

SELECT CEIL(123.456) AS ceil_value;
SELECT FLOOR(123.456) AS floor_value;

POWER(): 返回数值的幂

SELECT POWER(2, 3) AS power_value;

SQRT(): 返回平方根

SELECT SQRT(16) AS sqrt_value;

5.5转换函数

转换函数用于将数据从一种类型转换为另一种类型。

CAST(): 将数据类型显式转换为另一种数据类型

SELECT CAST(salary AS CHAR) AS salary_str FROM employees;

CONVERT(): 将数据类型转换为另一种数据类型

SELECT CONVERT(salary, CHAR) AS salary_str FROM employees;

OR()**: 向上取整和向下取整

SELECT CEIL(123.456) AS ceil_value;
SELECT FLOOR(123.456) AS floor_value;

POWER(): 返回数值的幂

SELECT POWER(2, 3) AS power_value;

SQRT(): 返回平方根

SELECT SQRT(16) AS sqrt_value;

5.5转换函数

转换函数用于将数据从一种类型转换为另一种类型。

CAST(): 将数据类型显式转换为另一种数据类型

SELECT CAST(salary AS CHAR) AS salary_str FROM employees;

CONVERT(): 将数据类型转换为另一种数据类型

SELECT CONVERT(salary, CHAR) AS salary_str FROM employees;