数据库 DML INSERT UPDATE DELETE SELECT

数据的增删改查

增删改查，又称为 CRUD，数据库基本操作中的基本操作。

插入数据

INSERT INTO 语句用于向表中插入新记录。

插入完整的行

  1. INSERT INTO user
  2. VALUES (10, 'root', 'root', 'xxxx@163.com');

插入行的一部分

  1. INSERT INTO user(username, password, email)
  2. VALUES ('admin', 'admin', 'xxxx@163.com');

插入查询出来的数据

  1. INSERT INTO user(username)
  2. SELECT name
  3. FROM account;

更新数据

UPDATE 语句用于更新表中的记录。

  1. UPDATE user
  2. SET username='robot', password='robot'
  3. WHERE username = 'root';

删除数据

DELETE 语句用于删除表中的记录。
TRUNCATE TABLE 可以清空表，也就是删除所有行。

删除表中的指定数据

  1. DELETE FROM user
  2. WHERE username = 'robot';

清空表中的数据

  1. TRUNCATE TABLE user;

查询数据

SELECT 语句用于从数据库中查询数据。
DISTINCT 用于返回唯一不同的值。它作用于所有列，也就是说所有列的值都相同才算相同。
LIMIT 限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

  • ASC ：升序（默认）

  • DESC ：降序

    查询单列

    1. SELECT prod_name
    2. FROM products;

    查询多列

    1. SELECT prod_id, prod_name, prod_price
    2. FROM products;

    查询所有列

    1. ELECT *
    2. FROM products;

    查询不同的值

    1. SELECT DISTINCT
    2. vend_id FROM products;

    限制查询结果

    1. -- 返回前 5 行
    2. SELECT * FROM mytable LIMIT 5;
    3. SELECT * FROM mytable LIMIT 0, 5;
    4. -- 返回第 3 ~ 5 行
    5. SELECT * FROM mytable LIMIT 2, 3

    子查询

    子查询是嵌套在较大查询中的 SQL 查询。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。

  • 子查询可以嵌套在 SELECT，INSERT，UPDATE 或 DELETE 语句内或另一个子查询中。

  • 子查询通常会在另一个 SELECT 语句的 WHERE 子句中添加。
  • 可以使用比较运算符，如 >，<，或 =。比较运算符也可以是多行运算符，如 IN，ANY 或 ALL。
  • 子查询必须被圆括号 () 括起来。

  • 内部查询首先在其父查询之前执行，以便可以将内部查询的结果传递给外部查询。

    子查询的子查询

    1. SELECT cust_name, cust_contact
    2. FROM customers
    3. WHERE cust_id IN (SELECT cust_id
    4.                 FROM orders
    5.                 WHERE order_num IN (SELECT order_num
    6.                                     FROM orderitems
    7.                                     WHERE prod_id = 'RGAN01'));

    WHERE

  • WHERE 子句用于过滤记录，即缩小访问数据的范围。

  • WHERE 后跟一个返回 true 或 false 的条件。
  • WHERE 可以与 SELECT，UPDATE 和 DELETE 一起使用。
  • 可以在 WHERE 子句中使用的操作符 | 运算符 | 描述 | | —- | —- | | = | 等于 | | <> | 不等于。注释：在 SQL 的一些版本中，该操作符可被写成 != | | > | 大于 | | < | 小于 | | >= | 大于等于 | | <= | 小于等于 | | BETWEEN | 在某个范围内 | | LIKE | 搜索某种模式 | | IN | 指定针对某个列的多个可能值 |

SELECT 语句中的 WHERE 子句

  1. SELECT * FROM Customers
  2. WHERE cust_name = 'Kids Place';

UPDATE 语句中的 WHERE 子句

  1. UPDATE Customers
  2. SET cust_name = 'Jack Jones'
  3. WHERE cust_name = 'Kids Place';

DELETE 语句中的 WHERE 子句

  1. DELETE FROM Customers
  2. WHERE cust_name = 'Kids Place';

IN 和 BETWEEN

  • IN 操作符在 WHERE 子句中使用，作用是在指定的几个特定值中任选一个值。

  • BETWEEN 操作符在 WHERE 子句中使用，作用是选取介于某个范围内的值。

    IN 示例

    1. SELECT *
    2. FROM products
    3. WHERE vend_id IN ('DLL01', 'BRS01');

    BETWEEN 示例

    1. SELECT *
    2. FROM products
    3. WHERE prod_price BETWEEN 3 AND 5;

    AND、OR、NOT

  • AND、OR、NOT 是用于对过滤条件的逻辑处理指令。

  • AND 优先级高于 OR，为了明确处理顺序，可以使用 ()。
  • AND 操作符表示左右条件都要满足。
  • OR 操作符表示左右条件满足任意一个即可。

  • NOT 操作符用于否定一个条件。

    AND 示例

    1. SELECT prod_id, prod_name, prod_price
    2. FROM products
    3. WHERE vend_id = 'DLL01' AND prod_price <= 4;

    OR 示例

    1. SELECT prod_id, prod_name, prod_price
    2. FROM products
    3. WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

    NOT 示例

    1. SELECT *
    2. FROM products
    3. WHERE prod_price NOT BETWEEN 3 AND 5;

    LIKE

  • LIKE 操作符在 WHERE 子句中使用，作用是确定字符串是否匹配模式。

  • 只有字段是文本值时才使用 LIKE。
  • LIKE 支持两个通配符匹配选项：% 和 _。
  • 不要滥用通配符，通配符位于开头处匹配会非常慢。
  • % 表示任何字符出现任意次数。

  • _ 表示任何字符出现一次。

    % 示例

    1. SELECT prod_id, prod_name, prod_price
    2. FROM products
    3. WHERE prod_name LIKE '%bean bag%';

    _ 示例

    1. SELECT prod_id, prod_name, prod_price
    2. FROM products
    3. WHERE prod_name LIKE '__ inch teddy bear';

    连接和组合

    连接（JOIN）

  • 如果一个 JOIN 至少有一个公共字段并且它们之间存在关系，则该 JOIN 可以在两个或多个表上工作。

  • 连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。
  • JOIN 保持基表（结构和数据）不变。
  • JOIN 有两种连接类型：内连接和外连接。
  • 内连接又称等值连接，使用 INNER JOIN 关键字。在没有条件语句的情况下返回笛卡尔积。
    • 自连接可以看成内连接的一种，只是连接的表是自身而已。
  • 自然连接是把同名列通过 = 测试连接起来的，同名列可以有多个。
  • 内连接 vs 自然连接
    • 内连接提供连接的列，而自然连接自动连接所有同名列。
  • 外连接返回一个表中的所有行，并且仅返回来自次表中满足连接条件的那些行，即两个表中的列是相等的。外连接分为左外连接、右外连接、全外连接（Mysql 不支持）。
    • 左外连接就是保留左表没有关联的行。
    • 右外连接就是保留右表没有关联的行。

  • 连接 vs 子查询

    • 连接可以替换子查询，并且比子查询的效率一般会更快。

      内连接（INNER JOIN）

      1. SELECT vend_name, prod_name, prod_price
      2. FROM vendors INNER JOIN products
      3. ON vendors.vend_id = products.vend_id;

      自连接

      1. SELECT c1.cust_id, c1.cust_name, c1.cust_contact
      2. FROM customers c1, customers c2
      3. WHERE c1.cust_name = c2.cust_name
      4. AND c2.cust_contact = 'Jim Jones';

      自然连接（NATURAL JOIN）

      1. SELECT *
      2. FROM Products
      3. NATURAL JOIN Customers;

      左连接（LEFT JOIN）

      1. SELECT customers.cust_id, orders.order_num
      2. FROM customers LEFT JOIN orders
      3. ON customers.cust_id = orders.cust_id;

      右连接（RIGHT JOIN）

      1. SELECT customers.cust_id, orders.order_num
      2. FROM customers RIGHT JOIN orders
      3. ON customers.cust_id = orders.cust_id;

      组合（UNION）

  • UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行。

  • UNION 基本规则
    • 所有查询的列数和列顺序必须相同。
    • 每个查询中涉及表的列的数据类型必须相同或兼容。
    • 通常返回的列名取自第一个查询。
  • 默认会去除相同行，如果需要保留相同行，使用 UNION ALL。
  • 只能包含一个 ORDER BY 子句，并且必须位于语句的最后。
  • 应用场景
    • 在一个查询中从不同的表返回结构数据。
    • 对一个表执行多个查询，按一个查询返回数据。

组合查询

  1. SELECT cust_name, cust_contact, cust_email
  2. FROM customers
  3. WHERE cust_state IN ('IL', 'IN', 'MI')
  4. UNION
  5. SELECT cust_name, cust_contact, cust_email
  6. FROM customers
  7. WHERE cust_name = 'Fun4All';

JOIN vs UNION

  • JOIN vs UNION

    • JOIN 中连接表的列可能不同，但在 UNION 中，所有查询的列数和列顺序必须相同。
    • UNION 将查询之后的行放在一起（垂直放置），但 JOIN 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。

      排序和分组

      ORDER BY

  • ORDER BY 用于对结果集进行排序。

    • ASC ：升序（默认）
    • DESC ：降序
  • 可以按多个列进行排序，并且为每个列指定不同的排序方式

指定多个列的排序方向

  1. SELECT * FROM products
  2. ORDER BY prod_price DESC, prod_name ASC;

GROUP BY

  • GROUP BY 子句将记录分组到汇总行中。
  • GROUP BY 为每个组返回一个记录。
  • GROUP BY 通常还涉及聚合：COUNT，MAX，SUM，AVG 等。
  • GROUP BY 可以按一列或多列进行分组。

  • GROUP BY 按分组字段进行排序后，ORDER BY 可以以汇总字段来进行排序。

    分组

    1. SELECT cust_name, COUNT(cust_address) AS addr_num
    2. FROM Customers GROUP BY cust_name;

    分组后排序

    1. SELECT cust_name, COUNT(cust_address) AS addr_num
    2. FROM Customers GROUP BY cust_name
    3. ORDER BY cust_name DESC;

    HAVING

  • HAVING 用于对汇总的 GROUP BY 结果进行过滤。

  • HAVING 要求存在一个 GROUP BY 子句。
  • WHERE 和 HAVING 可以在相同的查询中。
  • HAVING vs WHERE
    • WHERE 和 HAVING 都是用于过滤。
    • HAVING 适用于汇总的组记录；而 WHERE 适用于单个记录。

      使用 WHERE 和 HAVING 过滤数据

      1. SELECT cust_name, COUNT(*) AS num
      2. FROM Customers
      3. WHERE cust_email IS NOT NULL
      4. GROUP BY cust_name
      5. HAVING COUNT(*) >= 1;