文章目录
SQL 速成手册
SQL(Structured Query Language,结构化查询语言)是一种用于管理和操作关系型数据库的标准化编程语言。
SQL 被广泛用于数据库系统中(如 MySQL、PostgreSQL、Oracle、SQL Server),用于执行查询、更新数据、管理数据库结构和控制数据库访问权限。
SQL 的主要功能
1、基本查询语句
-
SELECT
- 用于从数据库中选择数据,返回结果集。 -
WHERE
- 用于过滤记录,指定查询条件。 -
ORDER BY
- 用于对结果集进行排序,可以按升序或降序排列。 -
DISTINCT
- 用于返回唯一不同的值,去除重复记录。 -
LIMIT
- 用于指定返回的记录数量,常用于分页。
代码示例
假设我们有一个名为 employees
的数据库表,这个表有以下列:id
, first_name
, last_name
, email
, job_title
, 和 salary
。
下面是一个示例 SQL 查询语句,它将从 employees
表中选择所有员工的名字和薪水,但只包括那些薪水大于 50000 的员工,并按薪水降序排序,最后只返回前 10 条记录:
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC
LIMIT 10;
这条 SQL 语句将执行以下操作:
SELECT first_name, last_name, salary
指定要从表中选择哪些列。FROM employees
指定要从哪个表中选择数据。WHERE salary > 50000
是一个过滤条件,只选择薪水超过 50000 的记录。ORDER BY salary DESC
将结果按照薪水从高到低排序。LIMIT 10
限制返回的结果数量为 10 条记录。
这就是一个基本的 SQL 查询语句的例子。你可以根据实际的数据库表结构和需求调整这个查询语句。
2、表操作语句
-
CREATE TABLE
- 用于创建新表,定义表的列和数据类型。 -
ALTER TABLE
- 用于修改现有表的结构,例如添加、删除或修改列。 -
DROP TABLE
- 用于删除表及其所有数据。
代码示例
创建新表 (CREATE TABLE)
假设我们要创建一个叫做 customers
的新表,该表包含客户的基本信息,我们可以这样写:
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
phone_number VARCHAR(15),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
这条语句将创建一个具有以下特性的表:
id
列是整数类型,自动递增,并且是主键。first_name
和last_name
是必填的字符串字段。email
字段也是字符串类型,而且是唯一的(不允许重复)。phone_number
是可选的电话号码字段。created_at
字段自动存储记录创建的时间戳。
修改现有表 (ALTER TABLE)
如果我们想在 customers
表中添加一个新的列 address
,可以使用以下语句:
ALTER TABLE customers
ADD COLUMN address VARCHAR(255);
这将在 customers
表中增加一个 address
字段,用于存储客户的地址信息。
删除表 (DROP TABLE)
如果决定不再需要 customers
表,可以使用以下语句来删除它:
DROP TABLE customers;
这条语句会永久删除 customers
表以及其中的所有数据,请谨慎使用。
这些就是使用 SQL 进行表操作的基本语法示例。在实际应用中,确保在修改或删除表之前备份数据,以防意外丢失重要信息。
3、数据操作语句
-
INSERT INTO
- 用于向表中插入新记录。 -
UPDATE
- 用于修改表中的现有记录。 -
DELETE
- 用于删除表中的记录。
代码示例
插入新记录 (INSERT INTO)
假设我们想要向 customers
表中插入一条新记录,可以使用下面的 SQL 语句:
INSERT INTO customers (first_name, last_name, email, phone_number)
VALUES ('John', 'Doe', '[email protected]', '123-456-7890');
这条语句将在 customers
表中插入一个新的客户记录,其姓名为 John Doe,电子邮件为 [email protected],电话号码为 123-456-7890。
修改现有记录 (UPDATE)
如果我们发现 John Doe 的电话号码有误,需要更新他的电话号码,可以使用以下 SQL 语句:
UPDATE customers
SET phone_number = '555-123-4567'
WHERE email = '[email protected]';
这条语句将会把 email
为 ‘[email protected]’ 的客户的 phone_number
更新为 ‘555-123-4567’。
删除记录 (DELETE)
如果 John Doe 要求从我们的数据库中删除他的个人信息,我们可以使用以下 SQL 语句:
DELETE FROM customers
WHERE email = '[email protected]';
这条语句将会从 customers
表中删除所有 email
为 ‘[email protected]’ 的记录。
请记住,在执行任何 UPDATE
或 DELETE
操作之前,最好先使用 SELECT
语句检查要修改或删除的记录是否正确,以避免错误地更改或删除数据。
4、函数与聚合操作
-
COUNT
- 用于返回匹配条件的行数。 -
SUM
- 用于返回列的总和。 -
AVG
- 用于返回列的平均值。 -
MIN
- 用于返回列的最小值。 -
MAX
- 用于返回列的最大值。
代码示例
让我们继续使用一个假设的 orders
表格来演示如何使用这些聚合函数。假设 orders
表包含以下列:order_id
, customer_id
, product_id
, quantity
, order_date
和 total_price
。
返回匹配条件的行数 (COUNT)
如果你想要计算有多少个不同的顾客下过订单,你可以使用以下 SQL 语句:
SELECT COUNT(DISTINCT customer_id) AS NumberOfCustomers
FROM orders;
返回列的总和 (SUM)
如果你想要计算所有订单的总销售额,你可以使用以下 SQL 语句:
SELECT SUM(total_price) AS TotalSales
FROM orders;
返回列的平均值 (AVG)
如果你想要计算每个订单的平均销售金额,你可以使用以下 SQL 语句:
SELECT AVG(total_price) AS AverageOrderValue
FROM orders;
返回列的最小值 (MIN)
如果你想要找到最便宜的订单,即具有最低总价格的订单,你可以使用以下 SQL 语句:
SELECT MIN(total_price) AS CheapestOrderPrice
FROM orders;
返回列的最大值 (MAX)
如果你想要找到最昂贵的订单,即具有最高总价格的订单,你可以使用以下 SQL 语句:
SELECT MAX(total_price) AS MostExpensiveOrderPrice
FROM orders;
请注意,这些 SQL 查询假设了 orders
表中的列名和数据类型。在实际应用中,你需要根据你的具体数据库结构调整这些查询。
5、子查询与联接
-
INNER JOIN
- 用于返回两个表中都存在的匹配记录。 -
LEFT JOIN
- 用于返回左表中的所有记录和右表中的匹配记录。 -
RIGHT JOIN
- 用于返回右表中的所有记录和左表中的匹配记录。 -
FULL JOIN
- 用于返回两个表中所有记录,不管是否匹配。 -
SUBQUERY
- 用于在查询语句中嵌套另一个查询。
代码示例
我们可以使用两个假设的表格 employees
和 departments
来演示 SQL 中的联接(JOIN)和子查询(SUBQUERY)。
假设 employees
表格有以下列:
employee_id
first_name
last_name
department_id
而 departments
表格有以下列:
department_id
department_name
INNER JOIN 示例
如果你想要获取所有员工以及他们所属部门的名称,你可以使用 INNER JOIN:
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
LEFT JOIN 示例
如果你想要获取所有员工的信息,即使他们没有分配到任何部门,你可以使用 LEFT JOIN:
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
RIGHT JOIN 示例
如果你想要获取所有部门的信息,包括那些没有员工的部门,你可以使用 RIGHT JOIN:
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
FULL JOIN 示例
如果你想要获取所有员工和所有部门的信息,无论他们是否匹配,你可以使用 FULL JOIN(注意:不是所有的数据库系统都支持 FULL JOIN,有些需要使用 UNION 或者 LEFT JOIN 和 RIGHT JOIN 结合):
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
FULL JOIN departments d ON e.department_id = d.department_id;
SUBQUERY 示例
如果你想要获取属于“销售”部门的所有员工,你可以使用一个子查询来找到“销售”部门的 ID,然后使用这个 ID 来过滤员工列表:
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE department_name = 'Sales'
);
在实际应用中,你可能需要根据具体的数据表结构和需求调整这些查询。
6、高级操作
-
UNION
- 用于合并两个或多个 SELECT 语句的结果集。 -
CASE
- 用于实现条件逻辑,返回不同的值。 -
INDEX
- 用于创建索引以加速查询。
代码示例
让我们继续使用之前提到的 employees
和 departments
表格来演示 SQL 中的 UNION
, CASE
, 和 CREATE INDEX
。
UNION 示例
假设我们有两个表格 employees
和 managers
,并且它们都有 first_name
和 last_name
列。如果我们想列出所有员工和经理的名字,可以使用 UNION
:
SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM managers;
请注意,UNION
会自动去除重复的行。如果你想包含所有行,包括重复项,可以使用 UNION ALL
:
SELECT first_name, last_name FROM employees
UNION ALL
SELECT first_name, last_name FROM managers;
CASE 示例
假设你想根据员工的部门 ID 来分类他们的职位。使用 CASE
语句可以实现这一点:
SELECT
employee_id,
first_name,
last_name,
department_id,
CASE
WHEN department_id = 1 THEN 'Manager'
WHEN department_id = 2 THEN 'Developer'
WHEN department_id = 3 THEN 'Analyst'
ELSE 'Unknown'
END AS position
FROM employees;
在这个例子中,CASE
语句检查 department_id
的值,并根据该值返回相应的职位名称。
CREATE INDEX 示例
为了加速对 employees
表中按 last_name
字段的查询,你可以创建一个索引:
CREATE INDEX idx_employees_last_name ON employees (last_name);
这将创建一个名为 idx_employees_last_name
的索引,它将基于 last_name
字段对数据进行排序,从而加快搜索速度。
请注意,在生产环境中,创建索引需要考虑其对写入操作性能的影响,因为索引会占用额外的磁盘空间并可能降低写入速度。因此,索引策略通常需要仔细规划和测试。
7、性能优化与安全性
EXPLAIN - 用于查看查询的执行计划,帮助优化查询。
TRANSACTION - 用于处理事务,确保数据的一致性和完整性。
GRANT - 用于授予用户特定的数据库权限。
REVOKE - 用于撤销用户的数据库权限。
代码示例
EXPLAIN 示例
假设我们有一个 employees
表,并且我们想知道下面这个查询的执行计划:
EXPLAIN SELECT * FROM employees WHERE department_id = 1;
这条命令将显示数据库优化器计划如何执行查询,包括它是否使用了索引以及数据读取的方式等。
TRANSACTION 示例
如果你想要在一系列操作中保持数据的一致性,可以使用事务。例如,先更新 employees
表中的记录,然后更新 departments
表中的记录。如果第一个操作成功但第二个失败,你可能希望回滚所有的更改。这是使用事务的一种方式:
BEGIN TRANSACTION;
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 1;
UPDATE departments SET budget = budget + 5000 WHERE id = 1;
COMMIT;
如果在 COMMIT;
前面的任何操作失败,你可以使用 ROLLBACK;
而不是 COMMIT;
来取消所有更改。
GRANT 示例
如果你想要给一个用户(比如 johndoe
)授权,允许他们选择 employees
表的数据,可以使用 GRANT
:
GRANT SELECT ON employees TO johndoe;
这将允许 johndoe
用户查询 employees
表中的数据。
REVOKE 示例
如果后来决定不再让 johndoe
用户访问 employees
表,可以使用 REVOKE
撤销先前授予的权限:
REVOKE SELECT ON employees FROM johndoe;
这样,johndoe
将不再能够查询 employees
表中的数据。
这些命令对于数据库的性能优化和安全性至关重要。EXPLAIN
可以帮助理解查询效率,而事务、权限授予和撤销则有助于确保数据的安全和一致性。
基本查询语句
SELECT
:用于从数据库中选择数据。
SELECT column1, column2 FROM table_name;
column1
, column2
表示列名,table_name
表示表名。
WHERE
:用于过滤记录。
SELECT column1, column2 FROM table_name WHERE condition;
ORDER BY
:用于排序结果集。
SELECT column1, column2 FROM table_name
ORDER BY column1 [ASC|DESC];
ASC
(Ascending):表示升序排序,即从小到大排列(默认排序方式)。DESC
(Descending):表示降序排序,即从大到小排列。
DISTINCT
:用于返回唯一不同的值。
SELECT DISTINCT column1 FROM table_name;
LIMIT
:用于指定返回的记录数量。
SELECT column1 FROM table_name LIMIT number;
表操作语句
CREATE TABLE
:用于创建新表。
CREATE TABLE table_name (
column1 datatype PRIMARY KEY,
column2 datatype,
column3 datatype
);
-
column1
,column2
,column3
:表的列名。 -
datatype
:这列的数据类型,比如INT
表示整数,VARCHAR(255)
表示最大长度为255的字符串等。 -
PRIMARY KEY
:这是一个约束,用来指定哪列是主键。主键是表中用来唯一标识每行数据的列。在上面的例子中,column1
被指定为主键。
ALTER TABLE
:用于修改现有表结构。
-- 向 table_name 表添加名为 column_name 的新列,数据类型为 datatype
ALTER TABLE table_name ADD column_name datatype;
-- 从 table_name 表删除名为 column_name 的列
ALTER TABLE table_name DROP COLUMN column_name;
-- 修改 table_name 表中 column_name 列的数据类型为 datatype
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
DROP TABLE
:用于删除表。
DROP TABLE table_name;
数据操作语句
INSERT INTO
:用于向表中插入数据。
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
这个语句将向 table_name
表中插入一行数据,其中 column1
列的值设为 value1
,column2
列的值设为 value2
。
如果列名列表中包含表中的所有列,则不需要指定列名,可以直接使用INSERT INTO table_name VALUES (...);
的语法。
UPDATE
:用于更新表中的数据。
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
在 table_name
表中,找到满足 condition
条件的行,并将这些行的 column1
列更新为 value1
,column2
列更新为 value2
。
DELETE
:用于删除表中的数据。
DELETE FROM table_name WHERE condition;
函数与聚合操作
COUNT
:用于返回匹配条件的行数。
SELECT COUNT(column_name) FROM table_name;
SUM
:用于返回列的总和。
SELECT SUM(column_name) FROM table_name;
AVG
:用于返回列的平均值。
SELECT AVG(column_name) FROM table_name;
MIN
:用于返回列的最小值。
SELECT MIN(column_name) FROM table_name;
MAX
:用于返回列的最大值。
SELECT MAX(column_name) FROM table_name;
子查询与联接
INNER JOIN
:用于返回两个表中都存在的记录。
SELECT column1, column2 FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
从 table1
和 table2
中选择满足连接条件的行,返回 column1
和 column2
这两列的数据。
内连接只有当两个表中都有匹配的行时,结果才会包含这些行。
LEFT JOIN
:用于返回左表中的所有记录和右表中的匹配记录。
SELECT column1, column2 FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
从 table1
选择所有行,并包括 table2
中与 table1
指定列值匹配的行,如果 table2
中没有匹配的行,那么结果集中 table2
的列将显示为 NULL
。
RIGHT JOIN
:用于返回右表中的所有记录和左表中的匹配记录。
SELECT column1, column2 FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
从 table2
选择所有行,并包括 table1
中与 table2
指定列值匹配的行,如果 table1
中没有匹配的行,那么结果集中 table1
的列将显示为 NULL
。
右连接查询通常用于当 table2
是主要的数据源,而 table1
是补充数据源时。
FULL JOIN
:用于返回两个表中所有记录,不管是否匹配。
SELECT column1, column2 FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;
从 table1
和 table2
中选择所有行,包括两个表中所有匹配和不匹配的行,如果某个表中没有匹配的行,那么结果集中该表的列将显示为 NULL
。
全外连接查询通常用于需要从两个表中获取所有数据,而不考虑它们之间是否存在匹配关系的情况。
SUBQUERY
:用于在查询语句中嵌套另一个查询。
SELECT column1 FROM table_name
WHERE column2 = (SELECT column2 FROM table_name2 WHERE condition);
从 table_name
表中选择那些其 column2
列的值等于子查询返回的值的行的 column1
列。
高级操作
UNION
:用于合并两个或多个 SELECT 语句的结果集。
SELECT column1 FROM table_name1
UNION SELECT column1 FROM table_name2;
将 table_name1
和 table_name2
两个表中 column1
列的值合并为一个结果集,并且结果集中不会有重复的行。
CASE
:用于实现条件逻辑。
SELECT column1,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result
END
FROM table_name;
在查询 table_name
表时,根据列 column1
或其他列的值与 condition1
或 condition2
的匹配情况,返回相应的 result1
或 result2
,如果都不匹配,则返回 result
。
CASE
表达式允许你在 SQL 查询中根据多个条件进行数据转换或选择不同的值。
INDEX
:用于创建索引以加速查询。
CREATE INDEX index_name ON table_name (column1, column2);
在 table_name
表上创建一个名为 index_name
的索引。
性能优化与安全性
EXPLAIN
:用于查看查询的执行计划。
EXPLAIN SELECT column1 FROM table_name WHERE condition;
TRANSACTION
:用于处理事务。
START TRANSACTION;
UPDATE table_name SET column1 = value1 WHERE condition;
COMMIT;
GRANT
:用于授予用户权限。
GRANT SELECT ON database_name.table_name TO 'user'@'host';
REVOKE:用于撤销用户权限。
REVOKE SELECT ON database_name.table_name FROM 'user'@'host';
标签:语句,name,数据库,表中,宝典,SQL,table,id,SELECT
From: https://blog.csdn.net/weixin_68020300/article/details/140603618