SQL(Structured Query Language, 结构化查询语言)是一种用于管理和操作关系数据库的标准语言。无论是在数据分析、软件开发还是数据库管理中,SQL 都扮演着至关重要的角色。本详细指南将系统地介绍SQL的基本语法和常用操作,涵盖数据查询、数据操作、数据定义和数据控制等关键方面。SQL指南更多内容,请查询
什么是SQL?
SQL由IBM在1970年代开发,并被多家数据库系统(如MySQL、PostgreSQL、SQL Server、Oracle)采用和扩展。通过SQL,可以执行以下操作:
- 查询数据
- 插入、更新和删除数据
- 创建和修改数据库结构
- 访问和控制数据库权限
SQL 基本语法
1. 数据查询(SELECT)
数据查询是SQL中最常用的操作。SELECT
语句用于从数据库中检索数据。
基本语法:
SELECT column1, column2, ...
FROM table_name
WHERE condition(s)
ORDER BY column1 [ASC|DESC];
示例:
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales'
ORDER BY last_name ASC;
常用操作:
- 选择所有列:
SELECT * FROM employees;
- 限制结果数量:
SELECT first_name, last_name FROM employees LIMIT 10;
- 使用别名:
SELECT first_name AS fname, last_name AS lname FROM employees;
2. 数据过滤(WHERE)
WHERE
子句用于根据特定条件过滤数据。
基本语法:
SELECT column1, column2
FROM table_name
WHERE condition;
示例:
SELECT first_name, last_name
FROM employees
WHERE salary > 50000 AND department = 'Sales';
常用操作:
- 逻辑运算符:
AND
,OR
,NOT
SELECT * FROM products WHERE price > 100 AND category = 'Electronics';
- 比较运算符:
=
,!=
,>
,<
,>=
,<=
SELECT * FROM students WHERE age >= 18;
- 范围与模式匹配:
BETWEEN
,LIKE
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'; SELECT * FROM users WHERE email LIKE '%example.com';
3. 数据聚合(GROUP BY)
GROUP BY
将行数据组合到汇总行中,通常与聚合函数(如COUNT
、SUM
、AVG
、MAX
、MIN
)一起使用。
基本语法:
SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
WHERE condition
GROUP BY column1;
示例:
SELECT department, COUNT(*) AS num_employees
FROM employees
WHERE salary > 50000
GROUP BY department;
4. 数据排序(ORDER BY)
ORDER BY
子句用于对结果集排序,可以按升序(ASC)或降序(DESC)排序。
基本语法:
SELECT column1, column2
FROM table_name
ORDER BY column1 [ASC|DESC];
示例:
SELECT first_name, last_name
FROM employees
ORDER BY last_name DESC;
5. 连接(JOIN)
JOIN
操作用于根据相关列将来自两个或多个表中的记录组合。
常见的JOIN类型:
-
内连接(INNER JOIN):
SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
-
左连接(LEFT JOIN):
SELECT orders.order_id, customers.customer_name FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id;
-
右连接(RIGHT JOIN):
SELECT orders.order_id, customers.customer_name FROM orders RIGHT JOIN customers ON orders.customer_id = customers.customer_id;
-
全连接(FULL OUTER JOIN):
SELECT orders.order_id, customers.customer_name FROM orders FULL OUTER JOIN customers ON orders.customer_id = customers.customer_id;
6. 子查询(Subquery)
子查询是嵌套在其他查询(如SELECT
、INSERT
、UPDATE
或DELETE
语句)中的查询,可以返回单条或多条数据。
基本语法:
SELECT column1
FROM table_name
WHERE column2 = (SELECT column FROM table_name WHERE condition);
示例:
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');
7. 插入数据(INSERT)
INSERT
语句用于将新记录插入到表中。
基本语法:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
示例:
INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('John', 'Doe', 'Sales', 60000);
8. 更新数据(UPDATE)
UPDATE
语句用于修改表中的现有记录。
基本语法:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
示例:
UPDATE employees
SET salary = 70000
WHERE employee_id = 123;
9. 删除数据(DELETE)
DELETE
语句用于删除表中的记录。
基本语法:
DELETE FROM table_name
WHERE condition;
示例:
DELETE FROM employees
WHERE employee_id = 123;
10. 创建和修改表(CREATE TABLE / ALTER TABLE)
CREATE TABLE
用于创建新表,ALTER TABLE
用于修改现有表。
创建表基本语法:
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);
示例:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);
修改表基本语法:
ALTER TABLE table_name
ADD column_name datatype;
示例:
ALTER TABLE employees
ADD hire_date DATE;
11. 数据库权限和用户管理(GRANT / REVOKE)
管理数据库权限和用户是确保数据安全的关键部分。
创建用户基本语法:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
示例:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';
授予权限基本语法:
GRANT privilege ON database.table TO 'username'@'host';
示例:
GRANT SELECT, INSERT ON mydatabase.* TO 'newuser'@'localhost';
撤销权限基本语法:
REVOKE privilege ON database.table FROM 'username'@'host';
示例:
REVOKE INSERT ON mydatabase.* FROM 'newuser'@'localhost';
结论
SQL 是强大而灵活的数据库管理和操作语言。通过本详细指南,您已经掌握了SQL的基本语法和常见操作,包括数据查询、数据操作、数据定义和数据控制。熟练掌握这些基础知识,无论是进行数据分析、应用开发还是数据库管理,都能够得心应手。
如果您希望进一步深入学习,可以参考以下资源:
希望这篇指南对您有所帮助。如果有任何疑问或需要进一步详细解释的内容,请随时提问。
标签:指南,name,employees,语法,SQL,WHERE,id,SELECT From: https://blog.csdn.net/weixin_58606202/article/details/142365616