where和limit
在生产环境中如果查询语句不加限制，可能会拉跨mysql
- dbeaver 自动有limit,默认200.
- DBA/IT kill (show processlist) 如果这么解决有可能还是不行
- JDBC 如果确实要拿1kw数据，不同写法肯定不行，需要用流式写法。
数据准备
create table emp (
empno numeric(4) not null comment '员工号',
ename varchar(10) comment '员工姓名',
job varchar(9) comment '工作',
mgr numeric(4) comment '上级编号',
hiredate datetime comment '受雇日期',
sal numeric(7, 2) comment '薪金',
comm numeric(7, 2) comment '佣金',
deptno numeric(2) comment '部门编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';
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);
create table testa(aid int,aname varchar(100),address varchar(100));
create table testb(bid int,bname varchar(100),age int);
create table testc(cid int,sal int);
insert into testa values(1,'x1','sh');
insert into testa values(2,'x2','hz');
insert into testa values(3,'x3',null);
insert into testa values(4,'x4','bj');
insert into testa values(5,'x5','gz');
insert into testb values(1,'x1',10);
insert into testb values(2,'x2',11);
insert into testb values(3,'x3',12);
insert into testb values(4,'x4',16);
insert into testb values(7,'x7',19);
insert into testb values(8,'x8',22);
insert into testb values(9,'x9',24);
insert into testb values(10,'x10',44);
案例演示
1.求员工表所有人的薪水和
select
sum(sal) as ss
from emp;
2.求员工表的各个部门的薪水和
select
deptno,
sum(sal) as ss
from emp
group by deptno;
3.求员工表的各个部门的薪水和、员工数、平均薪资
select
deptno,
sum(sal) as ssum,
count(ename) as pcount,
sum(sal)/count(ename) as meansal1,
avg(sal) as meansal2
from emp
group by deptno;
4.找薪水和＞9000的是哪个部门？
select
deptno,
sum(sal) as ssal
from emp
group by deptno
having ssal>9000;
5.子查询
select
*
from
(select
deptno,
sum(sal) as ssal
from emp
group by deptno) as t where t.ssal>9000;
语法总结：
1.聚合函数 sum count avg max min
2.分组语法 select xxx,sum(yyy) from t group by xxx
3.group by出现的字段 务必出现在 select 后面
4.注意区分sum count
5.having 过滤 等价于 子表+where
综合总结:
select -> from -> where -> group by -> order by -> limit
6.left join语法 (以左表为主 a<--b a数据最全 b是匹配 匹配多少算多少 on就是匹配条件)
select
a.*,
b.*
from testa as a
left join testb as b on a.aid=b.bid
7.right join 以右表为主 a-->b b数据最全 a是匹配 匹配多少算多少 on就是匹配条件
select
a.*,
b.*
from testa as a
right join testb as b on a.aid=b.bid
8.inner join 两表的交集 on就是匹配条件
select
a.*,
b.*
from testa as a
inner join testb as b on a.aid=b.bid;
9.full join mysql不支持，用左连接 +union + 右连接
select
a.*,
b.*
from testa as a
left join testb as b on a.aid=b.bid
union
select
a.*,
b.*
from testa as a
right join testb as b on a.aid=b.bid
10.union all 结果不去重复 union去重复 注意：数量相同 类型相同
select aid from testa
union all
select bid from testb;
select aid from testa
union
select bid from testb;
select bid as id from testb
union
select aid from testa;
11.分组求topN，哪些部门的哪些职业的薪水和，最高1位的职业是什么？
drop view sal;
create view sal
as
select
deptno,job,
sum(sal+ifnull(comm,0)) as sal
from emp
group by deptno,job;
select * from sal;
select
a.*
from sal a
where
(
select count(*) from sal b
where a.deptno=b.deptno
and a.sal<b.sal
) =0
order by a.deptno;
网友评论