SQL必备:50个高效实用的列表查询命令解析与应用案例

在数据库管理中,SQL(Structured Query Language)是进行数据查询、更新、插入和删除等操作的重要工具。列表查询是SQL中最基础也是最为常用的操作之一。下面,我将详细介绍50个高效实用的SQL列表查询命令,并附上应用案例,帮助您更好地理解和运用这些命令。
1. SELECT
命令解析
:SELECT用于从数据库中检索数据。
应用案例
:
SELECT * FROM employees;
这条命令将检索
employees
表中的所有列。
2. WHERE
命令解析
:WHERE用于指定查询条件。
应用案例
:
SELECT * FROM employees WHERE age > 30;
这条命令将检索
employees
表中年龄大于30岁的所有记录。
3. ORDER BY
命令解析
:ORDER BY用于对查询结果进行排序。
应用案例
:
SELECT * FROM employees ORDER BY salary DESC;
这条命令将按工资降序排列
employees
表中的记录。
4. GROUP BY
命令解析
:GROUP BY用于对查询结果进行分组。
应用案例
:
SELECT department, COUNT(*) FROM employees GROUP BY department;
这条命令将按部门分组并计算每个部门的人数。
5. HAVING
命令解析
:HAVING用于对分组后的结果进行筛选。
应用案例
:
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;
这条命令将仅检索员工数量超过5人的部门。
6. DISTINCT
命令解析
:DISTINCT用于消除查询结果中的重复行。
应用案例
:
SELECT DISTINCT department FROM employees;
这条命令将检索
employees
表中所有不同的部门。
7. LIKE
命令解析
:LIKE用于执行模式匹配查询。
应用案例
:
SELECT * FROM employees WHERE name LIKE ‘A%’;
这条命令将检索所有以’A’开头的员工名称。
8. IN
命令解析
:IN用于指定多个可能的值。
应用案例
:
SELECT * FROM employees WHERE department IN (‘HR’, ‘IT’);
这条命令将检索
HR
和
IT
部门的所有员工。
9. BETWEEN
命令解析
:BETWEEN用于指定查询的值范围。
应用案例
:
SELECT * FROM employees WHERE age BETWEEN 20 AND 40;
这条命令将检索年龄在20到40岁之间的所有员工。
10. INNER JOIN
命令解析
:INNER JOIN用于结合两个或多个表中的记录。
应用案例
:
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
这条命令将检索员工姓名和对应的部门名称。
11. LEFT JOIN
命令解析
:LEFT JOIN用于返回左表的所有记录,即使右表中没有匹配的记录。
应用案例
:
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;
这条命令将检索所有员工及其对应的部门名称,即使某些员工没有分配到部门。
12. RIGHT JOIN
命令解析
:RIGHT JOIN用于返回右表的所有记录,即使左表中没有匹配的记录。
应用案例
:
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;
这条命令将检索所有部门及其对应的员工,即使某些部门没有员工。
13. FULL JOIN
命令解析
:FULL JOIN用于返回左表和右表的所有记录,即使在另一个表中没有匹配的记录。
应用案例
:
SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.id;
这条命令将检索所有员工和部门的组合,包括那些在另一个表中没有匹配的记录。
14. UNION
命令解析
:UNION用于合并两个或多个SELECT语句的结果集。
应用案例
:
SELECT name FROM employees WHERE department = ‘HR’
UNION
SELECT name FROM employees WHERE department = ‘IT’;
这条命令将检索
HR
和
IT
部门的所有员工。
15. UNION ALL
命令解析
:UNION ALL也用于合并两个或多个SELECT语句的结果集,但与UNION不同,它会包括重复的记录。
应用案例
:
SELECT name FROM employees WHERE department = ‘HR’
UNION ALL
SELECT name FROM employees WHERE department = ‘IT’;
这条命令将检索
HR
和
IT
部门的所有员工,包括重复的记录。
16. EXISTS
命令解析
:EXISTS用于检查子查询中是否有结果。
应用案例
:
SELECT * FROM employees e
WHERE EXISTS (SELECT * FROM departments d WHERE e.department_id = d.id);
这条命令将检索所有有部门分配的员工。
17. NOT EXISTS
命令解析
:NOT EXISTS用于检查子查询中没有结果。
应用案例
:
SELECT * FROM employees e
WHERE NOT EXISTS (SELECT * FROM departments d WHERE e.department_id = d.id);
这条命令将检索所有没有部门分配的员工。
18. INSERT INTO
命令解析
:INSERT INTO用于向数据库表中插入新记录。
应用案例
:
INSERT INTO employees (name, age, department) VALUES (‘John Doe’, 25, ‘IT’);
这条命令将在
employees
表中插入一个新记录。
19. UPDATE
命令解析
:UPDATE用于修改数据库表中的记录。
应用案例
:
UPDATE employees SET age = 26 WHERE name = ‘John Doe’;
这条命令将更新
John Doe
的年龄为26岁。
20. DELETE
命令解析
:DELETE用于从数据库表中删除记录。
应用案例
:
DELETE FROM employees WHERE name = ‘John Doe’;
这条命令将从
employees
表中删除名为
John Doe
的记录。
21. ALTER TABLE
命令解析
:ALTER TABLE用于修改表结构。
应用案例
:
ALTER TABLE employees ADD COLUMN email VARCHAR(100);
这条命令将在
employees
表中添加一个名为
email
的新列。
22. DROP TABLE
命令解析
:DROP TABLE用于删除表。
应用案例
:
DROP TABLE IF EXISTS employees;
这条命令将删除名为
employees
的表。
23. CREATE TABLE
命令解析
:CREATE TABLE用于创建新表。
应用案例
:
CREATE TABLE departments (
id INT PRIMARY KEY,
department_name VARCHAR(100)
);
这条命令将创建一个名为
departments
的新表。
24. TRUNCATE TABLE
命令解析
:TRUNCATE TABLE用于删除表中的所有记录。
应用案例
:
TRUNCATE TABLE employees;
这条命令将删除
employees
表中的所有记录。
25.背靠背查询(Subqueries)
命令解析
:背靠背查询(Subqueries)用于在一个SELECT语句中嵌套另一个SELECT语句。
应用案例
:
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
这条命令将检索工资高于平均工资的员工姓名和工资。
26. JOIN背靠背查询(Nested Joins)
命令解析
:JOIN背靠背查询用于在多个JOIN操作中嵌套另一个JOIN操作。
应用案例
:
SELECT employees.name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.id
JOIN locations ON departments.location_id = locations.id
WHERE locations.city = ‘New York’;
这条命令将检索位于纽约的部门及其员工名称。
27. EXISTS背靠背查询(Nested Subqueries with EXISTS)
命令解析
:EXISTS背靠背查询用于在EXISTS子查询中嵌套另一个EXISTS子查询。
应用案例
:
SELECT name
FROM employees
WHERE EXISTS (SELECT * FROM departments WHERE employees.department_id = departments.id
AND departments.location_id = (SELECT location_id FROM locations WHERE city = ‘New York’));
这条命令将检索位于纽约的部门的所有员工姓名。
28. IN背靠背查询(Nested Subqueries with IN)
命令解析
:IN背靠背查询用于在IN子查询中嵌套另一个IN子查询。
应用案例
:
SELECT name
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location_id IN (SELECT id FROM locations WHERE city = ‘New York’));
这条命令将检索位于纽约的部门的所有员工姓名。
29. NOT EXISTS背靠背查询(Nested Subqueries with NOT EXISTS)
命令解析
:NOT EXISTS背靠背查询用于在NOT EXISTS子查询中嵌套另一个NOT EXISTS子查询。
应用案例
:
SELECT name
FROM employees
WHERE NOT EXISTS (SELECT * FROM departments WHERE employees.department_id = departments.id
AND NOT EXISTS (SELECT * FROM locations WHERE departments.location_id = locations.id
AND locations.city = ‘New York’));
这条命令将检索不在纽约的部门的所有员工姓名。
30. CASE语句(Conditional Logic)
命令解析
:CASE语句用于在查询中添加条件逻辑。
应用案例
:
SELECT name,
CASE
WHEN age < 30 THEN 'Young' WHEN age BETWEEN 30 AND 50 THEN 'Middle-aged' ELSE 'Senior' END AS age_group FROM employees; 这条命令将根据员工的年龄将他们分为不同的年龄段。 31. COALESCE函数(Handling NULL Values) 命令解析 :COALESCE函数用于返回参数列表中的第一个非NULL值。 应用案例 : SELECT name, COALESCE(email, 'No email provided') AS email FROM employees; 这条命令将检索员工的姓名和电子邮件地址,如果电子邮件地址为NULL,则显示”No email provided”。 32. COUNT函数(Counting Rows) 命令解析 :COUNT函数用于计算查询结果集中的行数。 应用案例 : SELECT COUNT(*) AS total_employees FROM employees; 这条命令将计算 employees 表中的总员工数。 33. SUM函数(Summing Values) 命令解析 :SUM函数用于计算查询结果集中数值列的总和。 应用案例 : SELECT SUM(salary) AS total_salary FROM employees; 这条命令将计算 employees 表中所有员工的工资总和。 34. AVG函数(Calculating Average Values) 命令解析 :AVG函数用于计算查询结果集中数值列的平均值。 应用案例 : SELECT AVG(salary) AS average_salary FROM employees; 这条命令将计算 employees 表中所有员工的平均工资。 35. MIN函数(Finding Minimum Values) 命令解析 :MIN函数用于找出查询结果集中数值列的最小值。 应用案例 : SELECT MIN(salary) AS minimum_salary FROM employees; 这条命令将找出 employees 表中所有员工工资的最小值。 36. MAX函数(Finding Maximum Values) 命令解析 :MAX函数用于找出查询结果集中数值列的最大值。 应用案例 : SELECT MAX(salary) AS maximum_salary FROM employees; 这条命令将找出 employees 表中所有员工工资的最大值。 37. DATE函数(Date and Time Functions) 命令解析 :DATE函数用于处理日期和时间数据。 应用案例 : SELECT name, hire_date, DATE(hire_date) AS hire_date_only FROM employees; 这条命令将检索员工的姓名、入职日期和仅日期的入职日期。 38. CONCAT函数(Concatenating Strings) 命令解析 :CONCAT函数用于连接字符串。 应用案例 : SELECT name, CONCAT(first_name, ' ', last_name) AS full_name FROM employees; 这条命令将检索员工的姓名和全名。 39. UPPER函数(Converting to Uppercase) 命令解析 :UPPER函数用于将字符串转换为 uppercase。 应用案例 : SELECT name, UPPER(name) AS name_uppercase FROM employees; 这条命令将检索员工的姓名和转换为大写的姓名。 40. LOWER函数(Converting to Lowercase) 命令解析 :LOWER函数用于将字符串转换为 lowercase。 应用案例 : SELECT name, LOWER(name) AS name_lowercase FROM employees; 这条命令将检索员工的姓名和转换为小写的姓名。 41. LENGTH函数(Calculating String Length) 命令解析 :LENGTH函数用于计算字符串的长度。 应用案例 : SELECT name, LENGTH(name) AS name_length FROM employees; 这条命令将检索员工的姓名和姓名的长度。 42. SUBSTRING函数(Extracting Substrings) 命令解析 :SUBSTRING函数用于提取字符串中的子字符串。 应用案例 : SELECT name, SUBSTRING(name, 1, 3) AS first_three_letters FROM employees; 这条命令将检索员工的姓名和姓名的前三个字母。 43. TRIM函数(Removing Spaces) 命令解析 :TRIM函数用于从字符串的两端删除空格。 应用案例 : SELECT name, TRIM(name) AS trimmed_name FROM employees; 这条命令将检索员工的姓名和从姓名两端删除空格后的姓名。 44. REVERSE函数(Reversing Strings) 命令解析 :REVERSE函数用于反转字符串。 应用案例 : SELECT name, REVERSE(name) AS reversed_name FROM employees; 这条命令将检索员工的姓名和反转后的姓名。 45. CURRENT_DATE函数(Current Date and Time) 命令解析 :CURRENT_DATE函数用于获取当前的日期。 应用案例 : SELECT CURRENT_DATE AS current_date; 这条命令将检索当前的日期。 46. CURRENT_TIME函数(Current Time and Date) 命令解析 :CURRENT_TIME函数用于获取当前的时间。 应用案例 : SELECT CURRENT_TIME AS current_time; 这条命令将检索当前的时间。 47. CURRENT_TIMESTAMP函数(Current Date and Time) 命令解析 :CURRENT_TIMESTAMP函数用于获取当前的日期和时间。 应用案例 : SELECT CURRENT_TIMESTAMP AS current_timestamp; 这条命令将检索当前的日期和时间。 48. INTERVAL函数(Adding or Subtracting Time) 命令解析 :INTERVAL函数用于向日期和时间值添加或减去时间间隔。 应用案例 : SELECT name, hire_date, hire_date + INTERVAL '1' YEAR AS hire_date_next_year FROM employees; 这条命令将检索员工的姓名、入职日期和下一年入职日期。 49. NOW()函数(Current Date and Time) 命令解析 :NOW()函数用于获取当前的日期和时间。 应用案例 : SELECT NOW() AS current_datetime; 这条命令将检索当前的日期和时间。 50. NEXTVAL函数(Sequences) 命令解析 :NEXTVAL函数用于获取序列中下一个值。 应用案例 : CREATE SEQUENCE employee_seq START WITH 1 INCREMENT BY 1; INSERT INTO employees (id, name, age, department) VALUES (NEXTVAL('employee_seq'), 'John Doe', 25, 'IT'); 这条命令将创建一个序列并使用它来插入一个新记录到 employees 表中。 以上是50个高效实用的SQL列表查询命令的解析与应用案例。通过学习和实践这些命令,您可以更加熟练地使用SQL进行数据查询,从而提高数据库管理的效率。