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;