数据仓库009 - SQL命令实战 - where GROUP BY join 部门综合案例

一、where条件

WHERE 子句中主要的运算符，可以在 WHERE 子句中使用，如下表：

运算符	描述
=	等于
<>	不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式
IN	指定针对某个列的多个可能值

二、SQL GROUP BY 语法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

where 
and or
= <>
like
left|right|inner join     

sum count avg….group by
order by …[desc|asc]
limit 10

all
any

三、SQL连接查询

　　1、左、右和内连接的图示：

　　2、join查询

　　1)、左连接：join的左连接查询，以左边的表为主，为全；右表是匹配的，匹配到就显示，匹配不到就null

select a.*,b.* from a left join b as bb
on a.id=b.id

输出结果：

　　2)、右连接：右连接，以右边的表为主，为全；左表是匹配的，匹配到就显示，匹配不到就nul1

select a.*,b.* from a right join b on a.id=b.id；

　　3)、内连接：内连接，根据id关联，两张表数据都存在的显示，不存在的不显示

select a.*,b.* from a inner join b on a.id=b.id；

　3、连接综合案例

　　案列需求1：利用聚合函数，求出年龄和

select sum(age)from rzdata;  #数值求和sum(id)
select count(*)from rzdata;  #数值求和求条数1+1+1=3

　　应该注意：1、少用count（* ）,应该将任一列替换*， count（0）表示第一列；

　　　　　　　2、算age数据多少 sum（age）。count(age)表示查询age的个数，age 表示age<20的记录值，sum（age）表示age的和；

　　　　 3、count（distinct id）去掉重复记录的条数，效果对比，如：

　　需求2：求各个名称的年龄和 sum(age)

　　方法一：

select sum(age)from rzdata group by name

　　方法二：

select * from rzdata;

select name,sum(age) from rzdata group by name;

　　需求3：利用子查询，注意别名，求年龄>20的，各个名称的年龄和sun（age）

方法一：

select t. name, sum(t. age) from select * from rzdata where age>20
) as t group by t. name;

方法二：

select name,sum(age) from rzdata where age>20
group by name;

　　需求4：求各个名称的年龄和大于25

select name,sum(age) from rzdata group by name having sum(age)>25;

表级别名，尽量少用子查询

select * from select name, sum(age) as sumage from rzdata group by name
) as t where t. sumage >25

　　4、排序：

select * from ruozedata.rzdata;

select * from ruozedata.rzdata order by age desc;

　　　　　　按照名字排序：

select * from ruozedata.rzdata order by name desc;

　　　　#desc倒序
　　　　#asc升序默认，可以不加该参数

select * from ruozedata.rzdata limit 2

select * from ruozedata.rzdata order by age desc limit 2

　　小结：

select yyyyy from zzz where xxx group by xxx having xxx order by xxx limit xxx;

　　上述的关键词，顺序不能颠倒

四、案例

　　数据准备：

create database raodidb;
grant all privileges on raodidb.* to raodiuser@'%' identified by '123456';
flush privileges;

use raodidb;

--部门表
dept部门表(deptno部门编号/dname部门名称/loc地点)
create table dept (
    deptno numeric(2),
    dname varchar(14),
    loc varchar(13)
);

insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept values (20, 'RESEARCH', 'DALLAS');
insert into dept values (30, 'SALES', 'CHICAGO');
insert into dept values (40, 'OPERATIONS', 'BOSTON');

--工资等级表
salgrade工资等级表(grade 等级/losal此等级的最低/hisal此等级的最高)
create table salgrade (
    grade numeric,
    losal numeric,
    hisal numeric
);

insert into salgrade values (1, 700, 1200);
insert into salgrade values (2, 1201, 1400);
insert into salgrade values (3, 1401, 2000);
insert into salgrade values (4, 2001, 3000);
insert into salgrade values (5, 3001, 9999);


--员工表
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
工资 ＝ 薪金 ＋ 佣金

create table emp (
    empno numeric(4) not null,
    ename varchar(10),
    job varchar(9),
    mgr numeric(4),
    hiredate datetime,
    sal numeric(7, 2),
    comm numeric(7, 2),
    deptno numeric(2)
);



insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, null, 20);
insert into emp values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into emp values (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, null, 20);
insert into emp values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

创建数据库、表、插入数据

　　案例需求1：查询出部门编号为30的所有员工的编号和姓名

select empno,ename from emp where deptno=30;

案例需求2：找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。

select * from emp where(deptno=10 and job='MANAGER') or (deptno=20 and job='SALESMAN');

案例需求3：查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序

select * from emp order by sal desc,hiredate asc;

案例需求4：列出最低薪金大于1500的各种工作及从事此工作的员工人数。【各、每 gro by】

　　　　思路：首先查出，最低薪金大于1500的

　　　　　　误区1：null字段计算，比如相加nu11+任何数值=nu11

　　　　误区2：下列没有列出最低薪金：

　　　　最终解题：

　　　　　　　　having可以来自其他字段的数值

select job,count(job)from emp group by job having min(sal+ifnull(comm,0))>1500;

　　　　注意：

select xxx,yyyy,count(job) from emp group by xxx,yyyy;

　　group by后面的xxx,yyyy 应该和前面 select xxx,yyyy一致

　　案例需求5：列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

select ename from emp where deptno=
(select deptno from dept where dname='SALES');

案例需求6：查询姓名以S开头的\以5结尾\包含S字符\第二个字母为L _

案例需求7：查询每种工作的最高工资、最低工资、人数

select max(sal+ifnull(comm,0)) as maxsal,min(sal+ifnul1(comm,0)) as minsal, count(empno) cno from emp group by job;

案例需求8：列出薪金高于公司平均薪金的所有员工号，员工姓名，所在部门名称，上级领导，工资，工资等级

select
e. ename, 
e. deptno,d. dname, 
e. mgr,
m.ename, 
e.sal+ifnull(e.comm,0) as sal,
s. grade 
from emp e 
left join dept d on e. deptno=d. deptno
 left join emp m on e. mgr=m.empno 
left join salgrade s on (e.sal+ifnull(e.comm,0)) between s.losal and s.hisal
 where(e.sal+ifnull(e.comm,0)) > (select avg(sal+ifnull(comm,e)) from emp);

案例需求9：列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。

select 
e.ename,
e.sal+ifnull(e.comm,0)
e.deptno,
d.dname 
from emp e, dept  d
where e.deptno =d.deptno 
and sal+ifnull(comm,0) > all(select sal+ifnull(comm,0) from emp where deptno=30);

案例需求10：列出薪金高于在部门30工作的任何一个员工的薪金的员工姓名和薪金、部门名称。

select 
e.ename,
e.sal+ifnull(e.comm,0)
e.deptno,
d.dname 
from emp e, dept  d
where e.deptno =d.deptno 
and sal+ifnull(comm,0) > any(select sal+ifnull(comm,0) from emp where deptno=30);

　　补充：

　　　　在大数据生产上join，第一反应on的字段一定先抽样检查null条数，其中a left join b on a.aid=b.bid ，若bid 或者aid 有null ，笛卡尔积前，先抽样

select aid,count(aid) from a;

　　　　　　使用该语句进行过滤：select * from a where aid is not null

posted @ 2019-11-24 23:49 王者★孤傲阅读(6770) 评论(0) 编辑收藏举报

刷新页面返回顶部

王者★孤傲

数据仓库009 - SQL命令实战 - where GROUP BY join 部门综合案例

一、where条件

二、SQL GROUP BY 语法

三、SQL连接查询

1、左、右和内连接的图示：

2、join查询

1)、左连接：join的左连接查询，以左边的表为主，为全；右表是匹配的，匹配到就显示，匹配不到就null

2)、右连接：右连接，以右边的表为主，为全；左表是匹配的，匹配到就显示，匹配不到就nul1

3)、内连接：内连接，根据id关联，两张表数据都存在的显示，不存在的不显示

3、连接综合案例

案列需求1：利用聚合函数，求出年龄和

需求2：求各个名称的年龄和 sum(age)

需求3：利用子查询，注意别名，求年龄>20的，各个名称的年龄和sun（age）

需求4：求各个名称的年龄和大于25

4、排序：

小结：

四、案例

数据准备：

案例需求1：查询出部门编号为30的所有员工的编号和姓名

案例需求2：找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。

案例需求3：查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序

案例需求4：列出最低薪金大于1500的各种工作及从事此工作的员工人数。 【各、每 gro by】

误区1：null字段计算，比如相加nu11+任何数值=nu11

误区2：下列没有 列出最低薪金 ：

最终解题：

注意：

案例需求5：列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

案例需求6：查询姓名以S开头的\以5结尾\包含S字符\第二个字母为L _

案例需求7：查询每种工作的最高工资、最低工资、人数

案例需求8：列出薪金高于公司平均薪金的所有员工号，员工姓名，所在部门名称，上级领导，工资，工资等级

案例需求9：列出薪金 高于 在部门30工作的所有 员工的薪金的员工姓名和薪金、部门名称。

案例需求10：列出薪金 高于 在部门30工作的任何一个员工的薪金的员工姓名和薪金、部门名称。

补充：

公告

　　1、左、右和内连接的图示：

　　2、join查询

　　1)、左连接：join的左连接查询，以左边的表为主，为全；右表是匹配的，匹配到就显示，匹配不到就null

　　2)、右连接：右连接，以右边的表为主，为全；左表是匹配的，匹配到就显示，匹配不到就nul1

　　3)、内连接：内连接，根据id关联，两张表数据都存在的显示，不存在的不显示

　3、连接综合案例

　　案列需求1：利用聚合函数，求出年龄和

　　需求2：求各个名称的年龄和 sum(age)

　　需求3：利用子查询，注意别名，求年龄>20的，各个名称的年龄和sun（age）

　　需求4：求各个名称的年龄和大于25

　　4、排序：

　　小结：

　　数据准备：

　　案例需求1：查询出部门编号为30的所有员工的编号和姓名

案例需求4：列出最低薪金大于1500的各种工作及从事此工作的员工人数。【各、每 gro by】

　　　　　　误区1：null字段计算，比如相加nu11+任何数值=nu11

　　　　误区2：下列没有列出最低薪金：

　　　　最终解题：

　　　　注意：

　　案例需求5：列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。

案例需求9：列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。

案例需求10：列出薪金高于在部门30工作的任何一个员工的薪金的员工姓名和薪金、部门名称。

　　补充：