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;

Mysql必知必会挑战题和一些乱七八糟东西