sql_study_01_based_component-CSDN博客

本文链接：https://blog.csdn.net/m0_51972677/article/details/140919071

基础部分-增删查改

一、在单一表中检索数据

AND

SELECT *
FROM order_items
WHERE order_id = 6
AND (quantity*unit_price) > 30;

LIKE和REGEXP
1. LIKE
  1. %代表任意数量的字符
  2. _代表一个字符
```
SELECT *
FROM customers
WHERE address LIKE '%trail%' OR address LIKE '%avenue%';
```
2. REGEXP 正则表达式
  
  ^elka$|ambur ：以elka开头或者结尾的或者包含ambur的
  1. ^ ：以什么开始
    1. ^elka ：以elka开始的字符串
  2. $ ：以什么结束
    1. elka$ ：以elka结尾的字符串
  3. | ：或
  4. 单字符串：包含这个字符串
    1. ambur 包含ambur的数据
```
SELECT * FROM customers
WHERE first_name REGEXP '^elka$|ambur';
```

LIMIT

LIMT offset,number
offset ：偏移量（默认为0）
number ：展示的数量

SELECT *
FROM customers LIMIT 0,3;
-- page1: 1 2 3
-- page2: 4 5 6
-- page3: 7 8 9

二、在多张表中检索数据

Joining Multiple Tables

INNER JOIN…ON…

内连接，INNER可以省略，查找是连接表共有的数据
1. JOIN customers c ON o.customer_id = c.customer_id
  
  customer ：表名
  
  o.customer_id = c.customer_id ：连接条件

-- Joining Multiple Tables
SELECT
       o.order_id,
       o.order_date,
       c.first_name,
       c.last_name,
       os.name AS status
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_statuses os ON o.status = os.order_status_id;

-- Joining Multiple Tables Exercise
USE sql_invoicing;
SELECT
       p.payment_id,
       c.name,
       i.invoice_date,
       i.invoice_total,
       p.date,
       p.amount,
       pm.name AS pay_method
FROM payments p
JOIN clients c ON p.client_id = c.client_id
JOIN invoices i ON p.invoice_id = i.invoice_id
JOIN payment_methods pm ON p.payment_method = pm.payment_method_id;

Compound Join Conditions

连接条件有多个
1. order_id 和 product_id 一起标识一个数据（双主键）

-- 复合连接条件Compound Join Conditions
USE sql_store;
SELECT *
FROM order_items oi
JOIN order_item_notes oin
    ON oi.order_id = oin.order_Id
    AND oi.product_id = oin.product_id;

Outer Joins

外连接包含左外连接和有外连接

LEFT JOIN

左外连接：左边连接表不符合条件的结果也会展示出来
RIGHT JOIN

右外连接：右边连接表不符合条件的结果会展示出来

-- LEFT JOIN
SELECT * FROM orders;
SELECT
    o.order_id,
    c.customer_id,
    o.customer_id,
    c.first_name
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

-- RIGHT JOIN
SELECT
    o.order_id,
    c.customer_id,
    o.customer_id,
    c.first_name
FROM customers c
RIGHT JOIN orders o
    ON c.customer_id = o.customer_id;

Outer Join Between Multiple Tables

多表外连接

将第一次外连接的结果在和其他表做外连接查询

一般只用一个外连接，保持规范（直接全用LEFT JOIN哈哈哈）

-- Outer Join Between Multiple Tables
SELECT
    c.customer_id,
    c.first_name,
    o.order_id,
    s.name AS shipper
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
LEFT JOIN shippers s
ON o.shipper_id = s.shipper_id
ORDER BY c.customer_id;

外连接和内连接可以混合使用

-- Outer Join Between Multiple Table Exercise
SELECT
    o.order_date,
    o.order_id,
    c.first_name,
    sh.name AS shipper,
    os.name AS status
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id
JOIN order_statuses os
    ON o.status = os.order_status_id
ORDER BY os.name;

Self Outer Joins

自己和自己外连接查询

-- Self Outer Joins
USE sql_hr;
SELECT
    e.employee_id,
    e.first_name,
    M.first_name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.reports_to = m.employee_id;

USING

代替ON的作用

ON oin.order_Id = oi.order_id AND oin.product_id = oi.product_id == USING (order_Id,product_id)

-- USING 字句
USE sql_store;
SELECT
    oin.product_id,
    oin.order_Id,
    oi.quantity
FROM order_item_notes oin
RIGHT JOIN order_items oi
    ON oin.order_Id = oi.order_id
    AND oin.product_id = oi.product_id;
SELECT
    oin.product_id,
    oin.order_Id,
    oi.quantity
FROM order_item_notes oin
RIGHT JOIN order_items oi
    USING (order_Id,product_id);

USING只能使用在两个表共有的字段名，不同字段名无法使用

ON p.payment_method = pm.payment_method_id

-- USING Exercise
USE sql_invoicing;
SELECT
    p.date,
    c.name AS client,
    p.amount,
    pm.name
FROM payments p
JOIN clients c
    USING (client_id)
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id;

NATURAL JOIN

自然连接：自动根据相同的列连接

-- Natural Join自动根据相同的列连接
SELECT
    p.date,
    c.name AS client,
    p.amount,
    pm.name
FROM payments p
NATURAL JOIN clients c
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id;

CROSS JOIN

交叉连接

每一列都和另外一张表的数据进行连接（笛卡尔积）

-- Cross Join
USE sql_store;
SELECT
    p.name AS product,
    s.name AS shipper
FROM products p
CROSS JOIN shippers s;
SELECT
       s.name AS shipper,
    p.name AS product

FROM  shippers s, products p
ORDER BY shipper;

UNION

连接查询结果（列的数量要一样，且列名以第一条语句的列名为准）

‘Bronze’ AS type ：新增一列名为 type 且值为 Bronze
‘Bronze’ ：新增一列名为 Bronze 且值为 Bronze

-- Union Exercise
SELECT
    customer_id,
    first_name,
    points,
    'Bronze' AS type
FROM customers
WHERE points < 2000
UNION
SELECT
    customer_id,
    first_name,
    points,
    'Sliver' AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT
    customer_id,
    first_name,
    points,
    'Gold' AS type
FROM customers
WHERE points > 3000
ORDER BY first_name;

三、插入、更新和删除数据

Insert Multiple Rows

插入多行数据

INSERT INTO products
VALUES (DEFAULT, 'product1', 25, 1.55),
       (DEFAULT, 'product2', 25, 1.55),
       (DEFAULT, 'product3', 25, 1.55),
       (DEFAULT, 'product4', 25, 1.55);

Inserting Hierarchical Rows

插入分层行

-- 插入分层行 Inserting Hierarchical Rows
-- LAST_INSERT_ID()获取最后插入行的ID
INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2024-08-02', 1);
INSERT INTO order_items
VALUES (LAST_INSERT_ID(), 2, 2, 2.98);

Creating a Copy Of Table

创建复制表

将SELECT 查询出来的结果复制到新表中（表没有把主键等属性复制）

-- Creating a Copy Of Table
-- CREATE TABLE
CREATE TABLE order_archived
SELECT *
FROM orders
WHERE order_date > '2020-01-01';

-- INSERT
INSERT INTO order_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01';

-- Creating a Copy Of Table Exercise
USE sql_invoicing;
CREATE TABLE invoicing_archived
SELECT i.invoice_id,
       c.name AS client,
       i.number,
       i.invoice_total,
       i.payment_total,
       i.payment_date,
       i.invoice_date,
       i.due_date
FROM invoices i
         JOIN clients c
              USING (client_id)
WHERE payment_date IS NOT NULL;

Using Sub queries In Updates

-- Using Sub queries In Updates
UPDATE invoices
SET payment_total = invoice_total * 0.5,
    payment_date  = due_date
WHERE client_id IN
      (SELECT client_id
       FROM clients
       WHERE state IN ('CA', 'OR'));