打开机器第一步可以查看下MySQL有没有开机自启动,如下说明开机自启动是成功的。
SQL语法:
一、聚合函数
sum() max() min() count()
二、聚合语法
group by having
所用到的资料链接如下:
链接:https://pan.baidu.com/s/1pgLGV9Z3WHokeavH6-uQRw
提取码:ohqd
复制这段内容后打开百度网盘手机App,操作更方便哦
字段类型 | 释义 |
---|---|
numeric(10,2) | 字段是数字型,长度是10,小数位为2 |
创建表
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)
);
解释:这是一张员工表,empno(员工号)、 ename(员工姓名)、 job(工作)、 mgr(上级编号)、 hiredate(受雇日期)、 sal(薪金)、 comm(佣金)、 deptno(部门编号)
需求一:求所有员工的薪水和
select sum(sal) from emp;
select sum(sal) as salsum from emp; //将显示的sal作一个别名为salsum
需求二:求每个部门员工的薪水和
select deptno,sum(sal) from emp
group by deptno;
需求三:每个部门每个岗位的薪水和
select deptno,job,sum(sal)
from emp
group by deptno,job;
需求四:求每个部门的每个岗位的所有人的薪水,总人数
select deptno,job,sum(sal) as salsum,
count(deptno) as pnum
from emp
group by deptno,job;
需求五:列出薪水和大于1500的各个部门
1、select deptno,job,sum(sal)
from emp
group by deptno,job having sum(sal) > 1500;
2、做一个子查询:
select * from (select deptno,sum(sal) as salsum
from emp
group by deptno) as t where salsum > 1500;
总结:groupby后面出现多少字段,在select后面也需要一摸一样。
二、join语法
left join
原始数据:
创建表A、创建表B:
create table testa(
aid int,aname varchar(100)
);
create table testb(
bid int,bname varchar(100),bage int
);
a表插入数据:
insert into testa values(1,"john");
insert into testa values(2,"sail");
insert into testa values(3,"ron");
insert into testa values(4,"earth");
insert into testa values(5,"saber");
b表插入数据:
insert into testb values(1,"john",15);
insert into testb values(2,"sail",17);
insert into testb values(3,"ron",19);
insert into testb values(4,"earth",24);
insert into testb values(5,"saber",28);
insert into testb values(7,"pk",30);
insert into testb values(8,"fox",35);
LEFT JOIN:是以左边的表为主
select
a.*,b.*
from testa as a
left join testb as b on a.aid = b.bid;
//J哥的记忆方法,a <- b,就是以左表数据为主,右表来匹配左表数据的
RIRHT JOIN:是以右表为主左表来进行匹配
INNER JOIN:等值连接,必须左右两边数据相等才可以连接。
右连接:
左连接: