写了6、7年SQL,有写复杂,有写简单,但总体而言,基础是统一的,。就像编程之需要会加减乘除,用MySQL懂这些SQL就足够了。
目录
MySQL 查询语句是用于从数据库中检索数据的语句。以下是一些常见的 MySQL 查询语句,并附详细说明和示例:
1. SELECT 查询
基本查询
SELECT column1, column2,...FROM table_name;
示例:
SELECT first_name, last_name
FROM employees;
*从
employees
表中选择
first_name
和
last_name
字段。*
查询所有列
SELECT*FROM table_name;
示例:
SELECT*FROM employees;
*从
employees
表中选择所有列。*
带条件的查询
SELECT column1, column2,...FROM table_name
WHERE condition;
示例:
SELECT first_name, last_name
FROM employees
WHERE department ='Sales';
*从
employees
表中选择
Sales
部门的
first_name
和
last_name
字段。*
2. WHERE 子句
**使用
AND
和
OR
逻辑运算符**
SELECT column1, column2,...FROM table_name
WHERE condition1 AND condition2 OR condition3;
示例:
SELECT first_name, last_name
FROM employees
WHERE department ='Sales'AND salary >50000;
*从
employees
表中选择
Sales
部门且薪水大于 50000 的员工的
first_name
和
last_name
字段。*
3. ORDER BY 子句
按指定列排序
SELECT column1, column2,...FROM table_name
ORDERBY column1 ASC|DESC, column2 ASC|DESC,...;
示例:
SELECT first_name, last_name
FROM employees
ORDERBY last_name ASC, first_name DESC;
*按
last_name
升序和
first_name
降序排列
employees
表中的数据。*
4. GROUP BY 子句
按指定列分组并聚合数据
SELECT column1,COUNT(*)FROM table_name
GROUPBY column1;
示例:
SELECT department,COUNT(*)FROM employees
GROUPBY department;
*按
department
列对
employees
表中的数据进行分组,并计算每个部门的员工数量。*
5. HAVING 子句
对分组后的结果进行过滤
SELECT column1,COUNT(*)FROM table_name
GROUPBY column1
HAVING condition;
示例:
SELECT department,COUNT(*)FROM employees
GROUPBY department
HAVINGCOUNT(*)>5;
筛选出员工数量大于 5 的部门。
6. JOIN 查询
内连接
SELECTcolumnsFROM table1
INNERJOIN
table2
ON table1.column= table2.column;
示例:
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNERJOIN departments
ON employees.department_id = departments.department_id;
*将
employees
表和
departments
表进行内连接,选择
first_name
、
last_name
和
department_name
。*
左连接
SELECTcolumnsFROM table1
LEFTJOIN table2
ON table1.column= table2.column;
示例:
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFTJOIN departments
ON employees.department_id = departments.department_id;
*从
employees
表中选择所有记录,并匹配
departments
表中的记录,如果没有匹配到则返回 NULL。*
右连接
SELECTcolumnsFROM table1
RIGHTJOIN table2
ON table1.column= table2.column;
示例:
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHTJOIN departments
ON employees.department_id = departments.department_id;
*从
departments
表中选择所有记录,并匹配
employees
表中的记录,如果没有匹配到则返回 NULL。*
7. 子查询
在 SELECT 中使用子查询
SELECT column1
FROM table_name
WHERE column2 =(SELECTcolumnFROM another_table
WHERE condition);
示例:
SELECT first_name, last_name
FROM employees
WHERE department_id =(SELECT department_id
FROM departments
WHERE department_name ='Sales');
*从
employees
表中选择
Sales
部门的所有员工。*
在 FROM 子句中使用子查询
SELECT subquery.column1, subquery.column2
FROM(SELECT column1, column2
FROM table_name
WHERE condition)AS subquery;
示例:
SELECT sub.first_name, sub.last_name
FROM(SELECT first_name, last_name
FROM employees
WHERE department_id =1)AS sub;
*从子查询结果中选择
department_id
为 1 的员工的
first_name
和
last_name
。*
8. INSERT INTO 查询
插入数据
INSERTINTO table_name (column1, column2, column3,...)VALUES(value1, value2, value3,...);
示例:
INSERTINTO employees (first_name, last_name, department_id, salary)VALUES('John','Doe',3,50000);
*向
employees
表中插入一条新记录。*
9. UPDATE 查询
更新数据
UPDATE table_name
SET column1 = value1, column2 = value2,...WHERE condition;
示例:
UPDATE employees
SET salary =55000WHERE last_name ='Doe';
*更新
last_name
为 ‘Doe’ 的员工的
salary
为 55000。*
10. DELETE 查询
删除数据
DELETEFROM table_name
WHERE condition;
示例:
DELETEFROM employees
WHERE last_name ='Doe';
*删除
last_name
为 ‘Doe’ 的员工记录。*
11. LIMIT 子句
限制结果集
SELECT column1, column2,...FROM table_name
LIMIT number;
示例:
SELECT first_name, last_name
FROM employees
LIMIT10;
选择前 10 条员工记录。
12. DISTINCT 关键字
去重查询
SELECTDISTINCT column1, column2,...FROM table_name;
示例:
SELECTDISTINCT department_id
FROM employees;
*选择
employees
表中所有不同的
department_id
。*
这些示例涵盖了 MySQL 中的基本查询语句及其常见用法。通过这些示例,你可以构建更复杂的查询来满足不同的数据检索需求。
版权归原作者 数据小羊 所有, 如有侵权,请联系我们删除。