where和limit
在生產(chǎn)環(huán)境中如果查詢語(yǔ)句不加限制桩砰，可能會(huì)拉跨mysql
- dbeaver 自動(dòng)有l(wèi)imit,默認(rèn)200.
- DBA/IT kill (show processlist) 如果這么解決有可能還是不行
- JDBC 如果確實(shí)要拿1kw數(shù)據(jù)黑毅，不同寫(xiě)法肯定不行，需要用流式寫(xiě)法瓤檐。
數(shù)據(jù)準(zhǔn)備
create table emp (
empno numeric(4) not null comment '員工號(hào)',
ename varchar(10) comment '員工姓名',
job varchar(9) comment '工作',
mgr numeric(4) comment '上級(jí)編號(hào)',
hiredate datetime comment '受雇日期',
sal numeric(7, 2) comment '薪金',
comm numeric(7, 2) comment '傭金',
deptno numeric(2) comment '部門(mén)編號(hào)'
) 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.求員工表的各個(gè)部門(mén)的薪水和
select
deptno,
sum(sal) as ss
from emp
group by deptno;
3.求員工表的各個(gè)部門(mén)的薪水和、員工數(shù)、平均薪資
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的是哪個(gè)部門(mén)凳干？
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;
語(yǔ)法總結(jié)：
1.聚合函數(shù) sum count avg max min
2.分組語(yǔ)法 select xxx,sum(yyy) from t group by xxx
3.group by出現(xiàn)的字段 務(wù)必出現(xiàn)在 select 后面
4.注意區(qū)分sum count
5.having 過(guò)濾 等價(jià)于 子表+where
綜合總結(jié):
select -> from -> where -> group by -> order by -> limit
6.left join語(yǔ)法 (以左表為主 a<--b a數(shù)據(jù)最全 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數(shù)據(jù)最全 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 結(jié)果不去重復(fù) union去重復(fù) 注意：數(shù)量相同 類(lèi)型相同
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被济，哪些部門(mén)的哪些職業(yè)的薪水和救赐，最高1位的職業(yè)是什么？
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;
