sql数据库语言练习，增删改查[通俗易懂]

代码100分#语句
use sql_store;#使用哪种数据库
select 
    last_name, 
    first_name, 
    points, 
    points +10 as "discount factor"
from customers
limit 3 #限制输出内容
/**
+------------+------------+--------+-----------------+
| last_name  | first_name | points | discount factor |
+------------+------------+--------+-----------------+
| MacCaffrey | Babara     |   2323 |            2333 |
| Brushfield | Ines       |    997 |            1007 |
| Boagey     | Freddi     |   3017 |            3027 |
+------------+------------+--------+-----------------+
**/

#代码
use sql_store;#使用哪种数据库
select * from order_item_notes;
/**
+---------+----------+------------+-------------+
| note_id | order_Id | product_id | note        |
+---------+----------+------------+-------------+
|       1 |        1 |          2 | first note  |
|       2 |        1 |          2 | second note |
|       3 |        1 |          2 | first note  |
+---------+----------+------------+-------------+

**/
#代码
select 
    distinct note #去除重复
from order_item_notes
/**
+-------------+
| note        |
+-------------+
| first note  |
| second note |
+-------------+
**/

代码100分#代码
 use sql_store;#使用哪种数据库
 select 
     * 
 from customers 
 where points >3000#where birth_date > "1992-01-01"#where state = "va" /**
 +-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+
 | customer_id | first_name | last_name | birth_date | phone        | address        | city      | state | points |
 +-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+
 |           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 |
 +-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+
 **/
 #代码 逻辑链接 and两个条件同时成立，or两个条件一个成立即可
 select * 
 from customers
 where points >= 1000 and points <= 3000
 /**
 +-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
 | 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 |
 |           3 | Freddi     | Boagey     | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO    |   2967 |
 |           7 | Ilene      | Dowson     | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing  | Nashville        | TN    |   1672 |
 |           9 | Romola     | Rumgay     | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail      | Visalia          | CA    |   1486 |
 +-------------+------------+------------+------------+--------------+----------------------+------------------+-------+--------+
 **/
 select
     *
 from orders
 where order_date >= "2018-01-02" and status >1 or status = 2
 /**
 +----------+-------------+------------+--------+-----------------------------------------------+--------------+------------+
 | order_id | customer_id | order_date | status | comments                                      | shipped_date | shipper_id |
 +----------+-------------+------------+--------+-----------------------------------------------+--------------+------------+
 |        2 |           7 | 2018-08-02 |      2 | NULL                                          | 2018-08-03   |          4 |
 |        5 |           5 | 2017-08-25 |      2 |                                               | 2017-08-26   |          3 |
 |        7 |           2 | 2018-09-22 |      2 | NULL                                          | 2018-09-23   |          4 |
 |        9 |          10 | 2017-07-05 |      2 | Nulla mollis molestie lorem. Quisque ut erat. | 2017-07-06   |          1 |
 |       10 |           6 | 2018-04-22 |      2 | NULL                                          | 2018-04-23   |          2 |
 +----------+-------------+------------+--------+-----------------------------------------------+--------------+------------+
 **/
 #代码 in where state in ("va", "GA", "FL")== where state = "VA" or state = "GA" or state = "FL"
 select * 
 from customers
 #where state = "VA" or state = "GA" or state = "FL"
 where state in ("va", "GA", "FL") /**
 +-------------+------------+------------+------------+--------------+------------------------+----------+-------+--------+
 | customer_id | first_name | last_name  | birth_date | phone        | address                | city     | state | points |
 +-------------+------------+------------+------------+--------------+------------------------+----------+-------+--------+
 |           2 | Ines       | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton  | VA    |    947 |
 |           4 | Ambur      | Roseburgh  | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace    | Orlando  | FL    |    457 |
 |           8 | Thacher    | Naseby     | 1993-07-17 | 941-527-3977 | 538 Mosinee Center     | Sarasota | FL    |    205 |
 |          10 | Levy       | Mynett     | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue         | Atlanta  | GA    |    796 |
 +-------------+------------+------------+------------+--------------+------------------------+----------+-------+--------+
 **/

use sql_store;#使用哪种数据库
#查询customers中last_name的第二个字母为y的所有信息
select * 
from customers
where last_name like "_y%"
/**
+-------------+------------+-----------+------------+--------------+----------------+---------+-------+--------+
| customer_id | first_name | last_name | birth_date | phone        | address        | city    | state | points |
+-------------+------------+-----------+------------+--------------+----------------+---------+-------+--------+
|          10 | Levy       | Mynett    | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA    |    796 |
+-------------+------------+-----------+------------+--------------+----------------+---------+-------+--------+
**/
#查询customers中phone 带有9的所有信息，并限制输出3行信息
select *
from customers
#where address like "%trail%" or
    #address like "%avenue%"
where phone like "%9%"
limit 3
/**
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
| 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 |
+-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+
**/
#regexp用法
select *
from customers
#where last_name like "%field%"
#where last_name regexp "field"#含有
#where last_name regexp "^field"#开头
#where last_name regexp "field$" #结尾
#where last_name regexp "field|mac|rose"#或者
#where last_name regexp "[gim]e"#ge/ie/me 取一个元素
#where last_name regexp "[a-h]e"#ae/……/he 范围

#order by 分组 数据显示限制为3条
select *
from customers
#order by first_name desc#按照first_name分组并倒叙
order by first_name #按照first_name分组
limit 3
/**
+-------------+------------+------------+------------+--------------+---------------------+-----------+-------+--------+
| customer_id | first_name | last_name  | birth_date | phone        | address             | city      | state | points |
+-------------+------------+------------+------------+--------------+---------------------+-----------+-------+--------+
|           4 | Ambur      | Roseburgh  | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando   | FL    |    457 |
|           1 | Babara     | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace      | Waltham   | MA    |   2273 |
|           5 | Clemmie    | Betchley   | 1973-11-07 | NULL         | 5 Spohn Circle      | Arlington | TX    |   3675 |
+-------------+------------+------------+------------+--------------+---------------------+-----------+-------+--------+
**/
select *, quantity * unit_price as total_price
from order_items
where order_id = 2
order by quantity * unit_price desc
limit 3
/**
+----------+------------+----------+------------+-------------+
| order_id | product_id | quantity | unit_price | total_price |
+----------+------------+----------+------------+-------------+
|        2 |          1 |        2 |       9.10 |       18.20 |
|        2 |          4 |        4 |       1.66 |        6.64 |
|        2 |          6 |        2 |       2.94 |        5.88 |
+----------+------------+----------+------------+-------------+
**/

#代码 联合orders 和customers 查询order_id, o.customer_id, last_name, first_name
select order_id, o.customer_id, last_name, first_name
from orders o
join customers 
    on o.customer_id = customers.customer_id
/**
+----------+-------------+------------+------------+
| order_id | customer_id | last_name  | first_name |
+----------+-------------+------------+------------+
|        4 |           2 | Brushfield | Ines       |
|        7 |           2 | Brushfield | Ines       |
|        5 |           5 | Betchley   | Clemmie    |
|        8 |           5 | Betchley   | Clemmie    |
|        1 |           6 | Twiddell   | Elka       |
|       10 |           6 | Twiddell   | Elka       |
|        2 |           7 | Dowson     | Ilene      |
|        3 |           8 | Naseby     | Thacher    |
|        6 |          10 | Mynett     | Levy       |
|        9 |          10 | Mynett     | Levy       |
+----------+-------------+------------+------------+
**/

#联合orders /customers  和order_statuses 查询
use sql_store;
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 os.order_status_id = o.status
/**
+----------+------------+------------+------------+-----------+
| order_id | order_date | first_name | last_name  | status    |
+----------+------------+------------+------------+-----------+
|        1 | 2019-01-30 | Elka       | Twiddell   | Processed |
|        3 | 2017-12-01 | Thacher    | Naseby     | Processed |
|        4 | 2017-01-22 | Ines       | Brushfield | Processed |
|        6 | 2018-11-18 | Levy       | Mynett     | Processed |
|        8 | 2018-06-08 | Clemmie    | Betchley   | Processed |
|        2 | 2018-08-02 | Ilene      | Dowson     | Shipped   |
|        5 | 2017-08-25 | Clemmie    | Betchley   | Shipped   |
|        7 | 2018-09-22 | Ines       | Brushfield | Shipped   |
|        9 | 2017-07-05 | Levy       | Mynett     | Shipped   |
|       10 | 2018-04-22 | Elka       | Twiddell   | Shipped   |
+----------+------------+------------+------------+-----------+
**/
#联合payments /clients 和payment_methods 查询p.date, p.invoice_id, #p.amount, c.name, pm.name
use sql_invoicing;
select p.date, p.invoice_id, p.amount, c.name, pm.name
from payments p
join clients c
    on p.client_id = c.client_id
join payment_methods pm
    on p.payment_method = pm.payment_method_id
/**
+------------+------------+--------+-------------+-------------+
| date       | invoice_id | amount | name        | name        |
+------------+------------+--------+-------------+-------------+
| 2019-02-12 |          2 |   8.18 | Topiclounge | Credit Card |
| 2019-01-03 |          6 |  74.55 | Vinte       | Credit Card |
| 2019-01-11 |         11 |   0.03 | Yadel       | Credit Card |
| 2019-01-26 |         13 |  87.44 | Topiclounge | Credit Card |
| 2019-01-15 |         15 |  80.31 | Yadel       | Credit Card |
| 2019-01-15 |         17 |  68.10 | Yadel       | Credit Card |
| 2019-01-08 |         18 |  32.77 | Topiclounge | Credit Card |
| 2019-01-08 |         18 |  10.00 | Topiclounge | Cash        |
+------------+------------+--------+-------------+-------------+
**/
#右连接customers 和orders 查询customer_id，first_name，order_id
use sql_store;
select c.customer_id, c.first_name, o.order_id
from customers c
right join orders o#left join orders o
    on c.customer_id = o.customer_id
order by c.customer_id
/**
+-------------+------------+----------+
| customer_id | first_name | order_id |
+-------------+------------+----------+
|           2 | Ines       |        4 |
|           2 | Ines       |        7 |
|           5 | Clemmie    |        5 |
|           5 | Clemmie    |        8 |
|           6 | Elka       |        1 |
|           6 | Elka       |       10 |
|           7 | Ilene      |        2 |
|           8 | Thacher    |        3 |
|          10 | Levy       |        6 |
|          10 | Levy       |        9 |
+-------------+------------+----------+
**/
#左连接orders ，shippers ，customers ，order_statuses
use sql_store;
select o.order_date, o.order_id,c.first_name as customer, s.name, os.name as status
from orders o
left join shippers s
    on o.shipper_id = s.shipper_id
left join customers c
    on c.customer_id = o.customer_id
join order_statuses os
    on o.status = os.order_status_id
/**
+------------+----------+----------+------------------------+-----------+
| order_date | order_id | customer | name                   | status    |
+------------+----------+----------+------------------------+-----------+
| 2019-01-30 |        1 | Elka     | NULL                   | Processed |
| 2017-12-01 |        3 | Thacher  | NULL                   | Processed |
| 2017-01-22 |        4 | Ines     | NULL                   | Processed |
| 2018-11-18 |        6 | Levy     | NULL                   | Processed |
| 2018-06-08 |        8 | Clemmie  | NULL                   | Processed |
| 2018-08-02 |        2 | Ilene    | Mraz, Renner and Nolan | Shipped   |
| 2017-08-25 |        5 | Clemmie  | Satterfield LLC        | Shipped   |
| 2018-09-22 |        7 | Ines     | Mraz, Renner and Nolan | Shipped   |
| 2017-07-05 |        9 | Levy     | Hettinger LLC          | Shipped   |
| 2018-04-22 |       10 | Elka     | Schinner-Predovic      | Shipped   |
+------------+----------+----------+------------------------+-----------+
**/
#using连接orders ，customers ，shippers 查询order_id, first_name, shipper
#注意连接的两张表必须有相同的字段
use sql_store;
select o.order_id, c.first_name, sh.name as shipper
from orders o
join customers c
    using (customer_id)
left join shippers sh
    using (shipper_id)
/**
+----------+------------+------------------------+
| order_id | first_name | shipper                |
+----------+------------+------------------------+
|        1 | Elka       | NULL                   |
|        2 | Ilene      | Mraz, Renner and Nolan |
|        3 | Thacher    | NULL                   |
|        4 | Ines       | NULL                   |
|        5 | Clemmie    | Satterfield LLC        |
|        6 | Levy       | NULL                   |
|        7 | Ines       | Mraz, Renner and Nolan |
|        8 | Clemmie    | NULL                   |
|        9 | Levy       | Hettinger LLC          |
|       10 | Elka       | Schinner-Predovic      |
+----------+------------+------------------------+
**/
#from shippers sh cross join products p == from shippers sh, products p
use sql_store;
select sh.name as shipper, p.name as product    
from shippers sh 
cross join products p #from shippers sh, products p
order by sh.name desc
limit 3
/**
+-----------------------------+---------------------------+
| shipper                     | product                   |
+-----------------------------+---------------------------+
| Waters, Mayert and Prohaska | Foam Dinner Plate         |
| Waters, Mayert and Prohaska | Lettuce - Romaine, Heart  |
| Waters, Mayert and Prohaska | Pork - Bacon,back Peameal |
+-----------------------------+---------------------------+
**/
#union连接两个查询
use sql_store;
select order_id, order_date, "Axtive" as status
    from orders 
    where order_date >= "2019-01-01"
union
select order_id, order_date, "Archived" as status
    from orders 
    where order_date < "2019-01-01"
limit 5
/**
+----------+------------+----------+
| order_id | order_date | status   |
+----------+------------+----------+
|        1 | 2019-01-30 | Axtive   |
|        2 | 2018-08-02 | Archived |
|        3 | 2017-12-01 | Archived |
|        4 | 2017-01-22 | Archived |
|        5 | 2017-08-25 | Archived |
+----------+------------+----------+
**/

#customers 中插入一行数据
use sql_store;
insert into customers (customer_id, first_name,last_name,birth_date,phone,address,city,state,points)
values (default, "mark", "reaper", "1991-01-01", "15690679736", "address", "city", "CA", 200)

#shippers 中插入两行数据
use sql_store;
insert into shippers (name)
values ("Shippers1"), ("Shippers2")

#products 中插入两行数据
use sql_store;
insert into products (name,quantity_in_stock, unit_price)
values ("Product1", 10, 1.95), ("Product2", 1, 8.95)

#orders 中插入一行数据
use sql_store;
insert into orders (customer_id, order_date, status)
values (1, "2019-01-02", 1)

#order_items中插入一行数据
insert into order_items
values (LAST_INSERT_ID(),11,1,2.98),(LAST_INSERT_ID(),2,1,2.88)
    
#将orders中order_date <"2019-01-01"的数据插入到orders_archived 中
insert into orders_archived 
select * from orders
where order_date <"2019-01-01"

#invoices中invoice_id 为 1的payment_total改为100，payment_date 改为2019-03-02
use sql_invoicing;
update invoices
set payment_total = 100, payment_date = "2019-03-02"
where invoice_id = 1

#删除orders_archived中customer_id 为 7的数据
use sql_store;
delete from orders_archived
where customer_id = 7