-- =======root=======
-- 创建用户
CREATE USER xxx@'%' IDENTIFIED BY'1234';
-- 赋予权限
GRANT ALL ON warehouse.* TO xxx@'%';
-- =======xxx=======
-- 查看表
USE warehouse
SELECT * FROM w_list;
SELECT * FROM w_people;
SELECT * FROM w_supplier;
SELECT * FROM w_wwarehouse;
INSERT INTO w_people VALUES
('WH2','E6','2000')
-- 5. 检索六月之后的所有订单
SELECT l_date FROM w_list
WHERE MONTH(l_date)>=6;
-- 8. 检索仓库是“WH1”或“WH2”并且面积大于400的城市
SELECT w_id,w_city,w_area FROM w_wwarehouse
WHERE w_id IN ('WH1','WH2')AND w_area>400;
SELECT w_id,w_city,w_area FROM w_wwarehouse
WHERE (w_id='WH1' OR w_id='WH2') AND w_area>400;
/*
选择查询---分组查询
语法:select 字段列表/表达式 from 表名字
【where 条件】
group by 分组条件字段 having 分组查询之后筛选条件
解释:对数据按照某一个字段条件进行分组,然后按组单独处理数据
对数据分类处理
语法中的两个条件:
where 条件:在分组之前进行数据筛选
having 条件:在分组之后进行数据筛选
关键:组函数/聚合函数的使用
count(字段) 统计查询数据的条数
sum(字段) 求和
avg(字段) 求平均值
max(字段) 求最大值
min(字段) 求最小值
分组处理的语法
备注:只要进行分组查询,那么查询语句中的组函数计算的是每一个一个组的信息,不是查询到的所有
不进行分组,组函数对应的是所有的数据处理
*/
-- 聚合函数的使用
-- 统计订单信息
-- 语法 count(x)根据x进行数据统计
-- count(distinct x)根据x进行统计,并且去重
SELECT * FROM w_list
SELECT COUNT(DISTINCT s_id) FROM w_list
-- 统计S4供应商提供的订单
SELECT COUNT(*) FROM w_list WHERE s_id='S4'
-- 求和函数的查询
-- 查询一个月公司需要发多少钱的工资
SELECT w_id,p_id,p_wages FROM w_people
SELECT SUM(p_wages) FROM w_people
-- 平均值函数
-- 获取公司所有员工的平均工资
SELECT AVG(p_wages) FROM w_people
-- 求最大值和最小值
-- 获取工资最高的人的信息/工资最低的人的信息
SELECT MAX(p_wages) FROM w_people ;
SELECT MIN(p_wages) FROM w_people ;
SELECT MAX(p_wages),MIN(p_wages) FROM w_people ;
-- 获取公司工资水平最大差距
SELECT MAX(p_wages)-MIN(p_wages) FROM w_people ;
-- 获取仓库号是WH5的员工的最高工资和最低工资以及平均工资
SELECT COUNT(w_id) FROM w_wwarehouse WHERE w_id='WH5'
SELECT MAX(w_area) FROM w_wwarehouse
GROUP BY w_id='WH5'
-- 获取每一个仓库对应员工的平均工资,总工资数,最高工资最低工资
SELECT w_id, COUNT(w_id) FROM w_wwarehouse
GROUP BY w_id -- 获取每一个仓库的人数
SELECT w_id,AVG(w_area) FROM w_wwarehouse
GROUP BY w_id -- 获取平均工资
SELECT w_id,MAX(w_area) FROM w_wwarehouse
GROUP BY w_id -- 获取最高工资
SELECT w_id,MIN(w_area) FROM w_wwarehouse
GROUP BY w_id -- 获取最低工资
-- 计算公司总共有多少平仓库面积
SELECT SUM(w_area) FROM w_wwarehouse
-- 计算上海仓库的平均面积
SELECT AVG(w_area)
-- 获取仓库最大的面积,最小面积,平均面积
SELECT MAX(w_area),MAX(w_area),AVG(w_area)FROM w_wwarehouse
-- 每一个仓库大于等于1230的所有员工的平均工资
SELECT w_id, COUNT(*) ,AVG(p_wages) FROM w_people
WHERE p_wages>=1230 GROUP BY w_id;
-- 每一个仓库工资大于等于1230的员工的平均资格中的平均工资大于1300的组
SELECT w_id,AVG(p_wages),COUNT(*) FROM w_people
WHERE p_wages>=1230 GROUP BY w_id
HAVING AVG(p_wages)>1300;
-- 获取每一个城市仓库总面积,要求面积小于400不参与统计
SELECT w_city, SUM(w_area),COUNT(*)
FROM w_wwarehouse
WHERE w_area>=400
GROUP BY w_city
-- 获取工资总数大于3000的仓库编号和员工平均工资,最高工资
SELECT w_id,AVG(p_wages),MAX(p_wages)
FROM w_people
GROUP BY w_id
HAVING SUM(p_wages)>3000
-- 分组的应用
-- 获取每一个人的总分和平均分
-- 绩效考核
/*
多表查询
多个数据表关联获取想要的数据,然后形成一张表
*/
/*
联表查询
解释:多个表关联查询获取的数据形成一个表
多表查询的关键:表与表之间的练习---主外键
联表查询的分类:
内连接:inner join 严格的链接查询,只获取满足条件的
外连接:lefe/right outer join 不严格的查询,主表获取所有信息,附表获取满足条件的
交叉链接:cross join 获取满足条件的信息,也获取表中的不满足条件的信息
集合查询:union
*/
/*
联表查询的条件:
先梳理数据库表的关系
w_list(p_id,s_id,l_id#,l_date)
w_people(w_id,p_id#,p_wages)
w_supplier(s_id#,s_name,s_add)
w_wwarehouse(w_id#,w_city,w_area)
表之间的关系
E-R图【entry-relative实体联系图】
实体:每一个表就是一个实体,对应一个业务数据
联系:关系,表示一个实体与另一个实体的关系
关系分类:一对一,一对多,多对一,多对多(转化成一对多,出现中间表)
属性:实体中包含的描述字段的信息
E-R图有什么元素:
实体:矩形
属性:椭圆
联系:菱形
关联:实线
主码:后面加#
外键:下划线
通过E-R图可以确定哪些表可以关联
找到主表---起始表,其他的都是副表,向主表靠拢
*/
-- 获取每一个员工对应的仓库所属城市,对应面积
-- 分析:
-- 主表---员工信息;副表---仓库信息
-- 内连接语法:select 表1.字段,表2.字段 from 主表 inner join 副表 on 表1.主外键关联条件
SELECT peo.w_id,peo.p_id,peo.p_wages,wh.w_id,wh.w_city,wh.w_area
FROM w_people peo INNER JOIN w_wwarehouse wh
ON peo.w_id=wh.w_id
SELECT peo.w_id,peo.p_id,peo.p_wages,wh.w_id,wh.w_city,wh.w_area
FROM w_people peo JOIN w_wwarehouse wh
ON peo.w_id=wh.w_id
SELECT peo.w_id,peo.p_id,peo.p_wages,wh.w_id,wh.w_city,wh.w_area
FROM w_people peo,w_wwarehouse wh
WHERE peo.w_id=wh.w_id
-- 获取员工对应的订单信息,供应商信息,仓库信息
SELECT w_people.*,w_wwarehouse.*,w_list.*,w_supplier.*
FROM w_people INNER JOIN w_wwarehouse ON w_people.w_id=w_wwarehouse.w_id
INNER JOIN w_list ON w_people.p_id=w_list.p_id
INNER JOIN w_supplier ON w_list.s_id=w_supplier.s_id
-- 获取每一个仓库所对应的员工信息
SELECT w_wwarehouse.*,w_people.*
FROM w_wwarehouse LEFT JOIN w_people ON w_wwarehouse.w_id=w_people.w_id
/*
外连接
左外连接:left outer join on 左边是主表,对于主表,不获取匹配信息,不匹配也能获取,没有信息用null填充
右外连接:right outer join on 右边是主表
*/
-- 获取每一个订单对应的供应商信息
SELECT w_list.*,w_supplier.*
FROM w_list LEFT OUTER JOIN w_supplier ON w_list.s_id=w_supplier.s_id
-- 获取每一个供应商对应的订单信息
SELECT w_supplier.*,w_list.*
FROM w_supplier RIGHT OUTER JOIN w_list ON w_supplier.s_id=w_list.s_id
-- 获取每一个员工对应的订单信息
SELECT w_people.*,w_list.*
FROM w_people LEFT OUTER JOIN w_list ON w_people.p_id=w_list.p_id
-- 获取每一个订单对应的员工信息
SELECT w_list.*,w_people.*
FROM w_list LEFT OUTER JOIN w_people ON w_list.p_id=w_people.p_id
-- 获取每一个员工的订单信息以及所属供应商信息
SELECT w_people.*,w_list.*,w_supplier.*
FROM w_people INNER JOIN w_list ON w_people.p_id=w_list.p_id
INNER JOIN w_supplier ON w_list.s_id=w_supplier.s_id
-- 交叉联接 cross join 获取笛卡尔积
-- 数据上看,表A的条数*表B的条件
-- 主表中的每一条信息都与副表中的信息合并,主表与副表的信息两两对应组合
SELECT w_wwarehouse.*,w_people.*
FROM w_wwarehouse CROSS JOIN w_people
SELECT COUNT(*) FROM w_wwarehouse CROSS JOIN w_people -- 30
/*
集合查询(并集,交集,差集)
union 并集
INTERSECTS 交集 8.0之后支持
except 差集 8.0之后支持
*/
-- 查看平均工资大于1300,并且面积大于400的仓库信息
SELECT w_id wh FROM w_people GROUP BY w_id HAVING AVG(p_wages)>=1400
UNION
SELECT w_id wh FROM w_wwarehouse WHERE w_area>=400
/*
子查询(多条查询语句的组合查询)
一个查询语句的结果作为另一个查询语句的条件或者表
注意:子查询作为from后的表,无要求
子查询作为条件,要求子查询select后面的字段只能有一个
单值查询:子查询的结果就一个值,使用单值的条件判断 = <> < > like
多值查询:子查询的结果不止一个,用 in 做判断
*/
-- 例如
SELECT w_id wh FROM w_wwarehouse WHERE w_area>=400
AND w_id IN
(SELECT w_id wh FROM w_people GROUP BY w_id HAVING AVG(p_wages)>=1400)
-- 29.找出在北京仓库里工作的职工
SELECT * FROM w_people
WHERE w_id IN(
SELECT w_id FROM w_wwarehouse WHERE w_city!='北京')
SELECT * FROM w_people
WHERE w_id =(
SELECT w_id FROM w_wwarehouse WHERE w_city='北京')
-- 30.求广州和上海仓库职工的总工资
SELECT w_id,SUM(p_wages) FROM w_people GROUP BY w_id
HAVING w_id IN(
SELECT w_id FROM w_wwarehouse WHERE w_city IN('广州','上海'))
-- 31.求所有职工工资都大于1210的仓库的平均面积
SELECT AVG(w_area) FROM w_wwarehouse
HAVING w_area IN(
SELECT w_people.*,w_wwarehouse.*
FROM w_people INNER JOIN w_wwarehouse ON w_people.w_id=w_wwarehouse.w_id
WHERE w_people.p_wages>1210)
-- 32.求上海仓库中职工的最高工资
-- 33.订单数大于或等于2的职工的工资
/*
分页查询 limit关键字
有两种语法格式
ORDER BY 字段(主键) limit 值,表示去前几条数据
ORDER BY 字段(主键) limit x,y 表示从表中第x条开始取y条数据【分页查询】
*/
-- 获取前五个员工信息
SELECT * FROM w_people ORDER BY p_id LIMIT 3
-- 获取从第五条开始获取两条
SELECT * FROM w_people ORDER BY w_id LIMIT 2,4
-- 分页查询:每一列显示条数
-- 考虑:每一列显示条数,4+当前是第几页
-- select 字段 from 表 order by 字段 limit 页数-1*每页条数,条数
SELECT * FROM w_people ORDER BY w_id LIMIT 4,2
/**
数据库---查询*
查询分类:
投影查询:基本查询:查看所有字段和信息
表达式:进行运算符操作
去重:对结果进行去重和别名
函数查询:数学相关
字符串
日期
其他
选择查询:where条件查询
条件运算符+逻辑运算符
组函数和分组函数
复杂查询:联表(主外键关系)
子查询
集合查询
分页查询
*/