SQL

sql語句執(zhí)行順序

(8) SELECT 
(9)DISTINCT<select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2)         ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(11) LIMIT <limit_number>

(1) FROM:對FROM子句中的左表<left_table>和右表<right_table>執(zhí)行笛卡兒積抒寂，產(chǎn)生虛擬表VT1;
(2) ON: 對虛擬表VT1進(jìn)行ON篩選履澳，只有那些符合<join_condition>的行才被插入虛擬表VT2;
(3) JOIN: 如果指定了OUTER JOIN(如LEFT OUTER JOIN椅贱、RIGHT OUTER JOIN)，那么保留表中未匹配的行作為外部行添加到虛擬表VT2策严，產(chǎn)生虛擬表VT3。
如果FROM子句包含兩個(gè)以上的表饿敲，則對上一個(gè)連接生成的結(jié)果表VT3和下一個(gè)表重復(fù)執(zhí)行步驟1~步驟3妻导，直到處理完所有的表;
(4) WHERE: 對虛擬表VT3應(yīng)用WHERE過濾條件，只有符合<where_condition>的記錄才會(huì)被插入虛擬表VT4;
(5) GROUP By: 根據(jù)GROUP BY子句中的列，對VT4中的記錄進(jìn)行分組操作倔韭，產(chǎn)生VT5;
(6) CUBE|ROllUP: 對VT5進(jìn)行CUBE或ROLLUP操作术浪，產(chǎn)生表VT6; 
(7) HAVING: 對虛擬表VT6應(yīng)用HAVING過濾器，只有符合<having_condition>的記錄才會(huì)被插入到VT7;
(8) SELECT: 第二次執(zhí)行SELECT操作寿酌，選擇指定的列胰苏，插入到虛擬表VT8中;
(9) DISTINCT: 去除重復(fù)，產(chǎn)生虛擬表VT9;
(10) ORDER BY: 將虛擬表VT9中的記錄按照<order_by_list>進(jìn)行排序操作醇疼，產(chǎn)生虛擬表VT10;
(11) LIMIT: 取出指定街行的記錄硕并，產(chǎn)生虛擬表VT11，并返回給查詢用戶

SELECT
   Persons.LastName,
   Persons.FirstName,
   Orders.OrderNo
FROM
   Persons,
   Orders
WHERE
   Persons.Id_P = Orders.Id_P

SELECT
    Persons.LastName,
    Persons.FirstName,
    Orders.OrderNo
FROM
    Persons
INNER JOIN Orders ON
    Persons.Id_P = Orders.Id_P
ORDER BY
    Persons.LastName

除了我們在上面的例子中使用的 INNER JOIN（內(nèi)連接）秧荆，我們還可以使用其他幾種連接倔毙。
下面列出了您可以使用的 JOIN 類型，以及它們之間的差異乙濒。

    JOIN: 如果表中有至少一個(gè)匹配陕赃，則返回行                                 
    INNER JOIN 與 JOIN 是相同的。
    LEFT JOIN: 即使右表中沒有匹配颁股，也從左表返回所有的行
    RIGHT JOIN: 即使左表中沒有匹配么库，也從右表返回所有的行
    FULL JOIN: 只要其中一個(gè)表中存在匹配，就返回行

UPDATE
    表名
set
    VALUES1 = 'xxx',
    VALUE2 = 'xxxxx';

insert
    into
    表名(value1, value2)
values ('xx', 'xx')

---從某個(gè)表中查詢數(shù)據(jù)豌蟋，插入另外的表中
insert
    into
    table1(value1, 2)
select
    VALUES1,
    VALUES2
FROM
    table2

--復(fù)制整個(gè)表
SELECT
    *
INTO
    OrdersBackup
FROM
    Orders;

CREATE TABLE OrdersBackup AS
SELECT
    *
FROM
    Orders;

--where較select先執(zhí)行廊散，要使用select中的別名，解決方法是將含有別名列的查詢放入內(nèi)嵌視圖梧疲，就可以在外層查詢中引用別名列允睹。內(nèi)嵌視圖的別名為 X
select
    *
from
    (
    select
        ID as i, goodsno as no_1
    from
        ALU_ss )x
where
    i = '1';

select
    prod_name,
    prod_desc
from
    products
where
    prod_desc LIKE '%toy%'
    and prod_desc LIKE '%carrots%';

select
    prod_name,
    prod_desc
from
    products
where
    prod_desc LIKE '%toy%carrots%';

select
    vend_id,
    vend_name as vname,
    vend_address as vaddress,
    vend_city as vcity
from
    vendors
order by
    vend_name;

select
    prod_id,
    prod_price,
    prod_price*0.9 as sale_price
from
    products;

--復(fù)制表數(shù)據(jù)，已經(jīng)建好了表結(jié)構(gòu)幌氮，該語句只能復(fù)制表的結(jié)構(gòu)缭受。該語句不會(huì)創(chuàng)建和復(fù)制源相同的主鍵、索引该互、約束和觸發(fā)器米者。    
INSERT
    into
    newtable
select
    name,sex ---COLUMN
FROM
    sourcetable;
--復(fù)制表和表結(jié)構(gòu)。該語句不會(huì)創(chuàng)建和復(fù)制源相同的主鍵宇智、索引蔓搞、約束和觸發(fā)器。
insert
    into
    newtables
from
    sourcetables
where
    whereexpression随橘；

select
    cust_id,
    customer_name,
    CONCAT(UPPER(LEFT(cust_contact, 2)), LEFT(cust_city, 3)))as user_login
from
    customers;

select
    order_num,
    order_data
from
    orders
where
    year(order_data)= 2020
    and month(order_data) = 1;

select
    SUM(quantity)
from
    orderitems;

select
    SUM(quantity)
from
    orderitems
where
    prod_item = 'BR01';

select
    MAX(prod_price) as max_price
from
    products
where
    prod_price < = 10;

select
    order_num,
    COUNT(*) as order_lines
from
    orderitems
group by
    order_num
order by
    order_lines;

select
    prod_id,
    MIN(prod_price) as cheapest_item
from
    products
group by
    prod_id
order by
    cheapest_item ;

SELECT
    order_num
FROM
    OrderItems
GROUP BY
    order_num
HAVING
    SUM(quantity) >= 100
ORDER BY
    order_num;

SELECT
    order_num
FROM
    OrderItems
GROUP BY
    order_num    --group by 后只能放實(shí)際的列喂分，而不是用于執(zhí)行匯總計(jì)算的列。
HAVING
    SUM(item_price * quantity) >= 1000
ORDER BY
    order_num;

select
    cust_id
from
    orders
where
    order_num IN (
    select
        order_num
    from
        orderitems
    where
        item_price > = 10)

select
    cust_id,
    cust_date
from
    orders
where
    order_num in (
    select
        ORDER_num
    from
        orderitem
    where
        prod_id = 'BR01')
order by
cust_date;

select
    cust_email
from
    customers
where
    cust_id in (
    select
        cust_id,
    from
        orders
    where
        order_num in (
        select
            ORDER_num
        from
            orderitem
        where
            prod_id = 'BR01') );

----訂單表机蔗、顧客表蒲祈、項(xiàng)目表   in = exists
select
    cust_id,
    (
    select
        SUM(item_price*quantity)
    from
        orderitems
    where
        orderitems.order_num = orders.orders_num) as total_ordered
from
    orders
order by
    total_ordered desc;

select
    cust_id,
    SUM(i.item_price*i.quantity) as total_ordered
from
    orders o
join orderitems i on
    i.order_num = o.orders_num
order by
    total_ordered desc;

select
    prod_name,
    (
    select
        SUM(quantity)
    from
        orderitems
    where
        products.prod_id = orderitems.prod_id) as quant_sold
from
    products;

select
    cust_name,
    orders_num
from
    customers c
join orders o on
    o.cust_id = c.cust_id
order by
    cust_name,
    orders_num

SELECT
    cust_name,
    order_num,
    (
    SELECT
        Sum(item_price*quantity)
    FROM
        OrderItems
    WHERE
        Orders.order_num = OrderItems.order_num) AS OrderTotal
FROM
    Customers,
    Orders
WHERE
    Customers.cust_id = Orders.cust_id
ORDER BY
    cust_name,
    order_num;

select
    cust_name,
    orders_num,
    SUM(item_prices*quantity) as orderTotal
from
    orders o
join customers c on
    o.cust_id = c.cust_id
join orderitem i on
    o.order_num = i.order_num
order by
    cust_name,
    orders_num

SELECT
    cust_email
FROM
    Customers
INNER JOIN Orders ON
    Customers.cust_id = Orders.cust_id
INNER JOIN OrderItems ON
    Orders.order_num = OrderItems.order_num
WHERE
    prod_id = 'BR01';

select  --6
    cust_name,
    SUM(item_prices*quanirty) as total_price
from    --1
    orders
join orderitems on
    orderitems.order_num = orders.order_num
join customers on---3
    orders.cust_id = customers.cust_id  --2
GROUP BY  --4 
    cust_name
HAVING  --5
    SUM(item_prices*quanirty) >=1000
order BY  --7
    cust_name

select
    cust_name,
    order_num
from
    customers
join orders on
    orders.cust_id = custmers.cust_id;

select
    cust_name,
    order_num
from
    customers
left outer join orders on
    orders.cust_id = custmers.cust_id;

select
    prod_name,
    order_num
from
    products
left outer join orderitems on
    orderitems.prod_id = products.prod_id
order by
    prod_name

select
    vendors.vend_id,
    COUNT(prod_id)
from
    vendors
left outer join products on
    products.vend_id = vendors.vend_id
group by
    vendors.vend_id;

select
    prod_id,
    quantity
from
    orderitems
where
    quantity = 1000
union
select
    prod_id,
    quantity
from
    orderitems
where
    proid_id like 'BNBG%'
order by
    prod_id

select
    prod_id,
    quantity
from
    orderitems
where
    quantity = 1000
    or proid_id like 'BNBG%'
order by
    prod_id;

INSERT
    into
    customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_email)
values(1000000042, 'Ben''s Toys', '123 Main Street', 'Oak Park', 'MI', '48237', 'USA', 'ben@forta.com');

update
    vendors
set
    UPPER(vend_state)
where
    vend_country = 'USA'
UPDATE
    customers
set
    cust_state = UPPER(cust_state)
where
    cust_country = 'USA'

alter TABLE vendors add vend_web char(100) 

create view customerswithorders as
select
    Customers.cust_id,
    Customers.cust_name,
    Customers.cust_address,
    Customers.cust_city,
    Customers.cust_state,
    Customers.cust_zip,
    Customers.cust_country,
    Customers.cust_contact,
    Customers.cust_email
from
    customers
join orders on
    orders.cust_id = custers.id;

--同時(shí)使用內(nèi)置函數(shù) NEWID 和 TOP甘萧、ORDER BY 來返回一個(gè)隨機(jī)結(jié)果集。
select
    TOP 5 *
from
    zd_MAData_erp
order by
    newid();

--MY SQL把內(nèi)置函數(shù) RAND 和 LIMIT梆掸、ORDER BY 結(jié)合使用    來返回一個(gè)隨機(jī)結(jié)果集扬卷。


select
    ename,
    job
from
    zd_MAData_erp
order by
    rand()
limit 5

select id,
    case
        when MAName is not null then MAName
        else '空'
    end
from
    zd_MAData_erp;


select
    mc,
    coalesce(cfdd, '空')
from
    ERP_BXG_YCL_Inventoryclass;

select
    ename,
    sal,
    case
        when sal <= 2000 then 'UNDERPAID'
        when sal >= 4000 then 'OVERPAID'
        else 'OK'
    end as status
from
    emp

select
    bm ,
    mc ,
    gys
from
    ERP_BXG_YCL_Inventoryclass
order by
    2;

select
    bm ,
    mc ,
    gys
from
    ERP_BXG_YCL_Inventoryclass
order by
    SUBSTRING(mc,LEN(mc)-2,2) 

select
    CONCAT(bm , mc ) as data1
from
    ERP_BXG_YCL_Inventoryclass;