-- =======root=======
-- 创建用户
CREATE USER xxx@'%' IDENTIFIED BY'1234';
CREATE USER yyy@'%'
-- 赋予权限
GRANT ALL ON warehouse.* TO xxx@'%';
GRANT ALL ON warehouse.emp TO yyy@'%';
-- =======xxx=======
USE warehouse
SHOW TABLES
-- 3.检索职工关系中的所有信息
-- 4.检索供应商关系中的所有信息
-- 5.检索六月之后的所有订单[指定1996年6月之后]
SELECT * FROM w_list WHERE l_date>'1996-06-01'
SELECT * FROM w_list WHERE TIMESTAMPDIFF(DAY,'1996-06-01',l_date)>0;
-- 6.检索面积大于400的仓库
SELECT * FROM w_wwarehouse WHERE w_area>400
-- 7.检索哪些职工的工资多于1210
SELECT * FROM w_people WHERE p_wages>1210
-- 8.检索仓库是“WH1”或“WH2”并且面积大于400的城市
SELECT * FROM w_wwarehouse WHERE w_id IN('WH1','WH2')AND w_area>400 -- 逻辑查询
-- 9.找出仓库面积在400到600的仓库
SELECT * FROM w_wwarehouse WHERE w_area>=400 AND w_area<=600
SELECT * FROM w_wwarehouse WHERE w_area BETWEEN 400 AND 600
-- 10.找出名中包含 “厂”的所有供应商的名
SELECT * FROM w_supplier WHERE s_id LIKE'%厂%'
SELECT * FROM w_supplier WHERE s_id REGEXP'厂'
-- 11.找出不在西安的供应商
SELECT * FROM w_supplier WHERE s_add!='西安'
-- 12.找出不在北京的仓库
SELECT * FROM w_wwarehouse WHERE w_area!='北京'
-- 13.按工资降序排列出所有职工的信息
SELECT * FROM w_people ORDER BY p_wages DESC
-- 14.先按仓库号升序排列,再按工资降序排列
SELECT * FROM w_people ORDER BY w_id,p_wages DESC
-- 15.在仓库表中统计一下有几个仓库
SELECT COUNT(*) FROM w_wwarehouse
-- 16.在职工表中统计一下有几个仓库
SELECT COUNT(DISTINCT w_id)FROM w_wwarehouse
-- 17.求总的仓库面积
SELECT SUM(w_area) FROM w_wwarehouse
-- 聚合查询
-- 18.每个职工的订单数
SELECT w_people.*,oco.p_wages,IFNULL(oco.ocon,0)
FROM w_people
LEFT OUTER JOIN
(SELECT w_id,COUNT(*) ocon FROM p_id GROUP BY w_list) oco
ON w_people.w_id=oco.p_wages
-- 19.订单数大于3的职工
-- 多表查询
-- 20.找出在面积大于400的仓库中工作的职工
SELECT w_people.*,w_wwarehouse.* FROM w_people,w_wwarehouse
WHERE w_people.w_id=w_wwarehouse.w_id AND w_wwarehouse.w_area>400
-- 21.找出在北京工作的职工和他们的工资情况
SELECT w_people.p_wages,w_wwarehouse.w_city FROM w_people,w_wwarehouse
WHERE w_people.w_id=w_wwarehouse.w_id AND w_wwarehouse.w_city='北京'
-- 22.找出工资大于1215的职工和他们所在的城市
SELECT w_people.p_wages,w_wwarehouse.w_city FROM w_people,w_wwarehouse
WHERE w_people.w_id=w_wwarehouse.w_id AND w_people.p_wages>1215
-- 子查询
-- 23.哪些城市至少有一个订单(从仓库角度考虑)
SELECT w_wwarehouse.w_city FROM w_wwarehouse
WHERE w_wwarehouse.w_id IN
(SELECT DISTINCT w_id FROM w_list,w_people
WHERE w_list.p_id=w_people.p_id)
-- 24.找出没有任何订单的城市
SELECT w_wwarehouse.w_city FROM w_wwarehouse
WHERE w_wwarehouse.w_id IN
(SELECT DISTINCT w_id FROM w_list,w_people
WHERE w_list.p_id!=w_people.p_id)
-- 25.找出和E4有同样工资的所有职工
SELECT * FROM w_people
WHERE p_id!='E4' AND p_wages=(
SELECT p_wages FROM w_people WHERE p_id='E4')
-- 26.找出仓库面积大于400的仓库的所有职工
SELECT * FROM w_people
WHERE w_id IN(SELECT w_id FROM w_wwarehouse WHERE w_area>400)
/*
mysql 高级应用-----视图
什么事视图(view)?
看得见的东西
为什么需要视图?
复杂的查询语句需要简化,用视图可以共享sql语句---共享性
表中的一些字段在分享的时候不被其他用户看见,只让用户获取某些数据----隐私性
将子查询进行分装,以此来简化查询的操作----方便性
目的:保存sql语句,简化调用执行过程,隐藏数据
视图是可以实现功能的空间,用来存储要做的事,方便调用
视图概述
存储在数据库的虚表,虚表中没有数据,只有映射关系,真正的数据在视图关联的实际的表中
视图的创建语法
create view 视图 as 查询语句
使用视图和使用表是一样的
*/
-- 创建一个视图存储工资大于1250的员工以及信息
CREATE VIEW emp AS SELECT * FROM w_people WHERE p_wages>1200
-- 查看视图
SELECT * FROM information_schema.VIEWS
DESC emp
-- 获取视图映射数据
SELECT * FROM emp WHERE p_id='E1'
--