Mysql必知必会挑战题和一些乱七八糟东西
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，并返回给查询用户
CUBE 和 ROLLUP 的区别：
CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
| 部门 | 员工 | 工资 |
|---|---|---|
| A | ZHANG | 100 |
| A | LI | 200 |
| A | WANG | 300 |
| A | ZHAO | 400 |
| A | DUAN | 500 |
| B | DUAN | 600 |
| B | DUAN | 700 |
SELECT 部门,员工,SUM(工资)AS TOTAL
FROM DEPART
GROUP BY 部门,员工
结果：
| 部门 | 员工 | 工资 |
|---|---|---|
| A | DUAN | 500 |
| B | DUAN | 1300 |
| A | LI | 200 |
| A | WANG | 300 |
| A | ZHANG | 100 |
| A | ZHAO | 400 |
SELECT 部门,员工,SUM(工资)AS TOTAL
FROM DEPART
GROUP BY 部门,员工 WITH ROLLUP
等价于下列SQL语句
SELECT 部门,员工,SUM(工资)AS TOTAL
FROM DEPART
GROUP BY 部门,员工
union
SELECT 部门,'NULL',SUM(工资)AS TOTAL
FROM DEPART
GROUP BY 部门
union
SELECT 'NULL','NULL',SUM(工资)AS TOTAL
FROM DEPART
结果：
| 部门 | 员工 | 工资 |
|---|---|---|
| A | DUAN | 500 |
| A | LI | 200 |
| A | WANG | 300 |
| A | ZHANG | 100 |
| A | ZHAO | 400 |
| A | NULL | 1500 |
| B | DUAN | 1300 |
| B | NULL | 1300 |
| NULL | NULL | 2800 |
| ROLLUP结果集中多了三条汇总信息：即部门A的合计，部门B的合计以及总合计。其中将部门B中的DUAN合计。 |
SELECT 部门,员工,SUM(工资)AS TOTAL
FROM DEPART
GROUP BY 部门,员工 WITH CUBE
CUBE的结果集是在 ROLLUP结果集的基础上多了5行，这5行相当于在ROLLUP结果集上在union 上以员工 （即CUBE）为 GROUP BY的结果。
结果：
| 部门 | 员工 | 工资 |
|---|---|---|
| A | DUAN | 500 |
| A | LI | 200 |
| A | WANG | 300 |
| A | ZHANG | 100 |
| A | ZHAO | 400 |
| A | NULL | 1500 |
| B | DUAN | 1300 |
| B | NULL | 1300 |
| NULL | DUAN | 1800 |
| NULL | LI | 200 |
| NULL | WANG | 300 |
| NULL | ZHANG | 100 |
| NULL | ZHAO | 400 |
--从多个表查数据
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')
删除的区别
当你不再需要该表时， 用 drop;
当你仍要保留该表，但要删除所有记录时， 用 truncate;
当你要删除部分记录时（always with a WHERE clause), 用 delete
--删除数据（删除内容不删除定义，不释放空间）
delete
from 表名
where 条件
--删除表
Drop table 表名
--清空表（删除内容、释放空间但不删除定义）
truncate table 表名
---从某个表中查询数据，插入另外的表中
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
expression；
--concat函数：字符串拼接
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;
--sum函数
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;
--where 条件中的子查询
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;
本文来自博客园，作者：红本本本，转载请注明原文链接：https://www.cnblogs.com/cxhong/p/14380576.html
浙公网安备 33010602011771号