首页 > 数据库 >SQL实战宝典：快速上手数据库查询与优化

SQL实战宝典：快速上手数据库查询与优化

时间：2024-07-22 18:27:32浏览次数：16  
标签：语句 name 数据库 表中 宝典 SQL table id SELECT

文章目录

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

相关文章

  • MySql if not exists 使用详解
     IFNOTEXISTS可以用于创建表或者数据库的语句中，用于避免重复创建。创建表时使用IFNOTEXISTS：如果你不确定表是否存在，你可以在创建新表时使用IFNOTEXISTS，这样如果表已经存在，SQL语句将不会执行创建表的操作，也不会报错。CREATETABLEIFNOTEXISTStable_name......
  • MySQL 学习笔记 基础（DQL，DCL，函数）
    SQL-DQL SQL-DQL-介绍DQL英文全称是DataQueryLanguage（数据查询语言），用来查询数据库中表的记录。查询关键字：SELECT SQL-DQL-语法SELECT字段列表FROM表名列表WHERE条件列表GROUPBY分组字段列表HAVING分组后条件列表ORDERBY排序字段......
  • C#使用PostgreSQL及其衍生产品GaussDB时（Npgsql.EntityFrameworkCore.PostgreSQL）过程中
      PostgreSQ作为开源免费的数据库，现在正在火热的占据市场，它衍生产品，比如GaussDB在国产化替代中使用比较多，然而它们或多或少存在一个兼容问题，或者说是版本之间的问题，所以这里记录几个在使用过程中碰到的问题，做个笔记，后续有新的问题就再记录。  问题一：0A000:DISCARDstatement......
  • [极客大挑战 2019]BabySQL
    [极客大挑战2019]BabySQL判断类型SQL关键字绕过，这道题可以双写绕过。username=1'or1=1#password=1'or1=1#得到报错：YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMariaDBserverversionfortherightsyntaxtousenear'1=......
  • Oracle 到 MySQL 函数替换方案汇总
    常用函数和语法转换  NVL函数Oracle语法:NVL(COUNT(*),0)MySQL语法:IFNULL(COUNT(*),0) 转字符串 Oracle语法:to_char(字段)MySQL语法:CONVERT(字段,CHAR) Rownum递增 Oracle语法:SELECTrownumnumFROMSYS_ENUMMySQL语法:SELECT(@i:=@i......
  • sql拦截器的应用
    问题：在实际开发当中，日志log的打印无法直接对sql层出现的问题进行整而概之的显示，使得开发中对sql层的具体变动无法直观的查看。当出现sql层方法报错后，需要不断地打log逐条分析错误，再进入sql层分析问题。解决方案：利于mybatis的sql拦截器工具进行对sql的拦截以及日志展示其具体变化......
  • sql server 事务日志释放空间
    您收到的错误消息表明数据库'EastRiver'的事务日志已满，导致数据库操作失败。要解决这个问题，可以按照以下步骤操作：1.备份事务日志首先，备份事务日志以释放空间：BACKUPLOG[EastRiver]TODISK=N'C:\Backup\EastRiver_log.bak'GO2.收缩事务日志文件备份日志后，可以使用DBCC......
  • 数据库_duckdb_读取csv创建表等案例
    duckdbduckdb读取csv格式文件duckdb连接数据库-创建表duckdb将数据转换为dataframedataframeapplymergegroupby代码示例#!/usr/bin/envpython3#-*-coding:UTF-8-*-#---------------------------importduckdbimportosif__name__=="__main__":......
  • Mysql的主从同步时延问题
    主从同步延迟可以通过一些策略减少，从而减低对业务的影响避免大事务和长事务大事务和长事务会严重影响主从同步的效率，例如大规模的DELETE操作会生成大量的binlog，导致从库复制速度跟不上。解决：通过分批操作缓解，例如一次1000条，减少每次操作的时间，减少对主从同步的影响。启用从库......
  • CentOS 7 安装MySQL
    一、前言这篇博客使用CentOS7系统进行操作，安装过程参考本人另一篇博客CentOS7的下载与安装所有操作都在root用户下进行二、下载MySQL的下载地址：https://downloads.mysql.com/archives/community/版本选择随意，这里以MySQL:8.0.28为例系统选择RedHatEnter......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99