SQL
sql语句执行顺序
(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>执行笛卡儿积，产生虚拟表VT1;
(2) ON: 对虚拟表VT1进行ON筛选，只有那些符合<join_condition>的行才被插入虚拟表VT2;
(3) JOIN: 如果指定了OUTER JOIN(如LEFT OUTER JOIN、RIGHT OUTER JOIN)，那么保留表中未匹配的行作为外部行添加到虚拟表VT2，产生虚拟表VT3。
如果FROM子句包含两个以上的表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1~步骤3，直到处理完所有的表;
(4) WHERE: 对虚拟表VT3应用WHERE过滤条件，只有符合<where_condition>的记录才会被插入虚拟表VT4;
(5) GROUP By: 根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5;
(6) CUBE|ROllUP: 对VT5进行CUBE或ROLLUP操作，产生表VT6;
(7) HAVING: 对虚拟表VT6应用HAVING过滤器，只有符合<having_condition>的记录才会被插入到VT7;
(8) SELECT: 第二次执行SELECT操作，选择指定的列，插入到虚拟表VT8中;
(9) DISTINCT: 去除重复，产生虚拟表VT9;
(10) ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10;
(11) LIMIT: 取出指定街行的记录，产生虚拟表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（内连接），我们还可以使用其他几种连接。
下面列出了您可以使用的 JOIN 类型，以及它们之间的差异。
JOIN: 如果表中有至少一个匹配，则返回行
INNER JOIN 与 JOIN 是相同的。
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行
UPDATE
表名
set
VALUES1 = 'xxx',
VALUE2 = 'xxxxx';
insert
into
表名(value1, value2)
values ('xx', 'xx')
---从某个表中查询数据，插入另外的表中
insert
into
table1(value1, 2)
select
VALUES1,
VALUES2
FROM
table2
--复制整个表
SELECT
*
INTO
OrdersBackup
FROM
Orders;
CREATE TABLE OrdersBackup AS
SELECT
*
FROM
Orders;
--where较select先执行，要使用select中的别名，解决方法是将含有别名列的查询放入内嵌视图，就可以在外层查询中引用别名列。内嵌视图的别名为 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;
--复制表数据，已经建好了表结构，该语句只能复制表的结构。该语句不会创建和复制源相同的主键、索引、约束和触发器。
INSERT
into
newtable
select
name,sex ---COLUMN
FROM
sourcetable;
--复制表和表结构。该语句不会创建和复制源相同的主键、索引、约束和触发器。
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 后只能放实际的列，而不是用于执行汇总计算的列。
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') );
----订单表、顾客表、项目表 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;
--同时使用内置函数 NEWID 和 TOP、ORDER BY 来返回一个随机结果集。
select
TOP 5 *
from
zd_MAData_erp
order by
newid();
--MY SQL把内置函数 RAND 和 LIMIT、ORDER BY 结合使用 来返回一个随机结果集。
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;
