SQL INSERT UPDATE DELETE

表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
-- 表invoices
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
| invoice_id | number      | client_id | invoice_total | payment_total | invoice_date | due_date   | payment_date |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
|          1 | 91-953-3396 |         2 |        101.79 |          0.00 | 2019-03-09   | 2019-03-29 | NULL         |
|          2 | 03-898-6735 |         5 |        175.32 |          8.18 | 2019-06-11   | 2019-07-01 | 2019-02-12   |
|          3 | 20-228-0335 |         5 |        147.99 |          0.00 | 2019-07-31   | 2019-08-20 | NULL         |
|          4 | 56-934-0748 |         3 |        152.21 |          0.00 | 2019-03-08   | 2019-03-28 | NULL         |
|          5 | 87-052-3121 |         5 |        169.36 |          0.00 | 2019-07-18   | 2019-08-07 | NULL         |
|          6 | 75-587-6626 |         1 |        157.78 |         74.55 | 2019-01-29   | 2019-02-18 | 2019-01-03   |
|          7 | 68-093-9863 |         3 |        133.87 |          0.00 | 2019-09-04   | 2019-09-24 | NULL         |
|          8 | 78-145-1093 |         1 |        189.12 |          0.00 | 2019-05-20   | 2019-06-09 | NULL         |
|          9 | 77-593-0081 |         5 |        172.17 |          0.00 | 2019-07-09   | 2019-07-29 | NULL         |
|         10 | 48-266-1517 |         1 |        159.50 |          0.00 | 2019-06-30   | 2019-07-20 | NULL         |
|         11 | 20-848-0181 |         3 |        126.15 |          0.03 | 2019-01-07   | 2019-01-27 | 2019-01-11   |
|         13 | 41-666-1035 |         5 |        135.01 |         87.44 | 2019-06-25   | 2019-07-15 | 2019-01-26   |
|         15 | 55-105-9605 |         3 |        167.29 |         80.31 | 2019-11-25   | 2019-12-15 | 2019-01-15   |
|         16 | 10-451-8824 |         1 |        162.02 |          0.00 | 2019-03-30   | 2019-04-19 | NULL         |
|         17 | 33-615-4694 |         3 |        126.38 |         68.10 | 2019-07-30   | 2019-08-19 | 2019-01-15   |
|         18 | 52-269-9803 |         5 |        180.17 |         42.77 | 2019-05-23   | 2019-06-12 | 2019-01-08   |
|         19 | 83-559-4105 |         1 |        134.47 |          0.00 | 2019-11-23   | 2019-12-13 | NULL         |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+

-- 结构
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| invoice_id    | int          | NO   | PRI | NULL    |       |
| number        | varchar(50)  | NO   |     | NULL    |       |
| client_id     | int          | NO   | MUL | NULL    |       |
| invoice_total | decimal(9,2) | NO   |     | NULL    |       |
| payment_total | decimal(9,2) | NO   |     | 0.00    |       |
| invoice_date  | date         | NO   |     | NULL    |       |
| due_date      | date         | NO   |     | NULL    |       |
| payment_date  | date         | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+

-- 表client
+-----------+-------------+--------------------------+---------------+-------+--------------+
| client_id | name        | address                  | city          | state | phone        |
+-----------+-------------+--------------------------+---------------+-------+--------------+
|         1 | Vinte       | 3 Nevada Parkway         | Syracuse      | NY    | 315-252-7305 |
|         2 | Myworks     | 34267 Glendale Parkway   | Huntington    | WV    | 304-659-1170 |
|         3 | Yadel       | 096 Pawling Parkway      | San Francisco | CA    | 415-144-6037 |
|         4 | Kwideo      | 81674 Westerfield Circle | Waco          | TX    | 254-750-0784 |
|         5 | Topiclounge | 0863 Farmco Road         | Portland      | OR    | 971-888-9129 |
+-----------+-------------+--------------------------+---------------+-------+--------------+

-- 结构
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| client_id | int         | NO   | PRI | NULL    |       |
| name      | varchar(50) | NO   |     | NULL    |       |
| address   | varchar(50) | NO   |     | NULL    |       |
| city      | varchar(50) | NO   |     | NULL    |       |
| state     | char(2)     | NO   |     | NULL    |       |
| phone     | varchar(50) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

-- 表orders
+----------+-------------+------------+--------+------------+
| order_id | customer_id | order_date | status | shipper_id |
+----------+-------------+------------+--------+------------+
|        1 |           6 | 2019-01-30 |      1 |       NULL |
|        2 |           7 | 2018-08-02 |      2 |          4 |
|        3 |           8 | 2017-12-01 |      1 |       NULL |
|        4 |           2 | 2017-01-22 |      1 |       NULL |
|        5 |           5 | 2017-08-25 |      2 |          3 |
|        6 |          10 | 2018-11-18 |      1 |       NULL |
|        7 |           2 | 2018-09-22 |      2 |          4 |
|        8 |           5 | 2018-06-08 |      1 |       NULL |
|        9 |          10 | 2017-07-05 |      2 |          1 |
|       10 |           6 | 2018-04-22 |      2 |          2 |
+----------+-------------+------------+--------+------------+

-- 结构
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| order_id    | int      | NO   | PRI | NULL    | auto_increment |
| customer_id | int      | NO   |     | NULL    |                |
| order_date  | date     | NO   |     | NULL    |                |
| shipper_id  | smallint | YES  |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+

-- 表customers
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| customer_id | first_name | last_name  | birth_date | phone        | address                | city             | state | points |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
|           1 | Babara     | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace         | Waltham          | MA    |   2273 |
|           2 | Ines       | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton          | VA    |    947 |
|           3 | Freddi     | Boagey     | 1985-02-07 | 719-724-7869 | 251 Springs Junction   | Colorado Springs | CO    |   2967 |
|           4 | Ambur      | Roseburgh  | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace    | Orlando          | FL    |    457 |
|           5 | Clemmie    | Betchley   | 1973-11-07 | NULL         | 5 Spohn Circle         | Arlington        | TX    |   3675 |
|           6 | Elka       | Twiddell   | 1991-09-04 | 312-480-8498 | 7 Manley Drive         | Chicago          | IL    |   3073 |
|           7 | Ilene      | Dowson     | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing    | Nashville        | TN    |   1672 |
|           8 | Thacher    | Naseby     | 1993-07-17 | 941-527-3977 | 538 Mosinee Center     | Sarasota         | FL    |    205 |
|           9 | Romola     | Rumgay     | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail        | Visalia          | CA    |   1486 |
|          10 | Levy       | Mynett     | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue         | Atlanta          | GA    |    796 |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+

-- 结构
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| customer_id | int         | NO   | PRI | NULL    | auto_increment |
| first_name  | varchar(50) | NO   |     | NULL    |                |
| last_name   | varchar(50) | NO   |     | NULL    |                |
| birth_date  | date        | YES  |     | NULL    |                |
| phone       | varchar(50) | YES  |     | NULL    |                |
| address     | varchar(50) | NO   |     | NULL    |                |
| city        | varchar(50) | NO   |     | NULL    |                |
| state       | char(2)     | NO   |     | NULL    |                |
| points      | int         | NO   |     | 0       |                |
+-------------+-------------+------+-----+---------+----------------+

创建表复制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 创建一个新表invoices_archive
-- 里面包括invoices表与client表，并将client_id替换为client_name，只输出已经支付的订单(due_date不为空)

CREATE TABLE invoice_archive AS
SELECT i.invoice_id, i.number, c.name, i.invoice_total, i.payment_total, i.invoice_date, i.due_date
FROM invoices i
JOIN clients c USING (client_id)
WHERE payment_date IS NOT NULL;
+------------+-------------+-------------+---------------+---------------+--------------+------------+
| invoice_id | number      | name        | invoice_total | payment_total | invoice_date | due_date   |
+------------+-------------+-------------+---------------+---------------+--------------+------------+
|          6 | 75-587-6626 | Vinte       |        157.78 |         74.55 | 2019-01-29   | 2019-02-18 |
|         11 | 20-848-0181 | Yadel       |        126.15 |          0.03 | 2019-01-07   | 2019-01-27 |
|         15 | 55-105-9605 | Yadel       |        167.29 |         80.31 | 2019-11-25   | 2019-12-15 |
|         17 | 33-615-4694 | Yadel       |        126.38 |         68.10 | 2019-07-30   | 2019-08-19 |
|          2 | 03-898-6735 | Topiclounge |        175.32 |          8.18 | 2019-06-11   | 2019-07-01 |
|         13 | 41-666-1035 | Topiclounge |        135.01 |         87.44 | 2019-06-25   | 2019-07-15 |
|         18 | 52-269-9803 | Topiclounge |        180.17 |         42.77 | 2019-05-23   | 2019-06-12 |
+------------+-------------+-------------+---------------+---------------+--------------+------------+

子查询条件更新表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 对表customers中points大于3000，并且在orders中下过订单的客户，为他的订单comments设置为Gold

UPDATE orders 
SET comments = 'Gold' 
WHERE customer_id IN (
    SELECT DISTINCT customer_id 
    FROM customers 
    WHERE points > 3000
    );

+----------+-------------+------------+--------+-----------------------------------------------+--------------+------------+
| order_id | customer_id | order_date | status | comments                                      | shipped_date | shipper_id |
+----------+-------------+------------+--------+-----------------------------------------------+--------------+------------+
|        1 |           6 | 2019-01-30 |      1 | Gold                                          | NULL         |       NULL |
|        2 |           7 | 2018-08-02 |      2 | NULL                                          | 2018-08-03   |          4 |
|        3 |           8 | 2017-12-01 |      1 | NULL                                          | NULL         |       NULL |
|        4 |           2 | 2017-01-22 |      1 | NULL                                          | NULL         |       NULL |
|        5 |           5 | 2017-08-25 |      2 | Gold                                          | 2017-08-26   |          3 |
|        6 |          10 | 2018-11-18 |      1 | Aliquam erat volutpat. In congue.             | NULL         |       NULL |
|        7 |           2 | 2018-09-22 |      2 | NULL                                          | 2018-09-23   |          4 |
|        8 |           5 | 2018-06-08 |      1 | Gold                                          | NULL         |       NULL |
|        9 |          10 | 2017-07-05 |      2 | Nulla mollis molestie lorem. Quisque ut erat. | 2017-07-06   |          1 |
|       10 |           6 | 2018-04-22 |      2 | Gold                                          | 2018-04-23   |          2 |
+----------+-------------+------------+--------+-----------------------------------------------+--------------+------------+

DELETE 删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
DELETE FROM table
WEHRE 条件;

DELETE FROM invoices 
WHERE client_id = (
    SELECT client_id 
    FROM clients 
    WHERE name = 'Myworks'
    );
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
| invoice_id | number      | client_id | invoice_total | payment_total | invoice_date | due_date   | payment_date |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
|          2 | 03-898-6735 |         5 |        175.32 |          8.18 | 2019-06-11   | 2019-07-01 | 2019-02-12   |
|          3 | 20-228-0335 |         5 |        147.99 |          0.00 | 2019-07-31   | 2019-08-20 | NULL         |
|          4 | 56-934-0748 |         3 |        152.21 |          0.00 | 2019-03-08   | 2019-03-28 | NULL         |
|          5 | 87-052-3121 |         5 |        169.36 |          0.00 | 2019-07-18   | 2019-08-07 | NULL         |
|          6 | 75-587-6626 |         1 |        157.78 |         74.55 | 2019-01-29   | 2019-02-18 | 2019-01-03   |
|          7 | 68-093-9863 |         3 |        133.87 |          0.00 | 2019-09-04   | 2019-09-24 | NULL         |
|          8 | 78-145-1093 |         1 |        189.12 |          0.00 | 2019-05-20   | 2019-06-09 | NULL         |
|          9 | 77-593-0081 |         5 |        172.17 |          0.00 | 2019-07-09   | 2019-07-29 | NULL         |
|         10 | 48-266-1517 |         1 |        159.50 |          0.00 | 2019-06-30   | 2019-07-20 | NULL         |
|         11 | 20-848-0181 |         3 |        126.15 |          0.03 | 2019-01-07   | 2019-01-27 | 2019-01-11   |
|         13 | 41-666-1035 |         5 |        135.01 |         87.44 | 2019-06-25   | 2019-07-15 | 2019-01-26   |
|         15 | 55-105-9605 |         3 |        167.29 |         80.31 | 2019-11-25   | 2019-12-15 | 2019-01-15   |
|         16 | 10-451-8824 |         1 |        162.02 |          0.00 | 2019-03-30   | 2019-04-19 | NULL         |
|         17 | 33-615-4694 |         3 |        126.38 |         68.10 | 2019-07-30   | 2019-08-19 | 2019-01-15   |
|         18 | 52-269-9803 |         5 |        180.17 |         42.77 | 2019-05-23   | 2019-06-12 | 2019-01-08   |
|         19 | 83-559-4105 |         1 |        134.47 |          0.00 | 2019-11-23   | 2019-12-13 | NULL         |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
Linux
SQL INSERT UPDATE DELETE
http://www.kirisamkekano.com/2023/08/26/SQL2/
作者
Ame
发布于
2023年8月26日
许可协议