mysql数据库基础知识(2)

1.笛卡尔积:多张表关联的时候,如果没有加条件限制,就会出现的一一相连的情况,这种情况的出现,如果数据庞大,会直接导致系统崩溃
  
如何避免:添加where条件过滤
select * from emp;
select * from dept;
--查询两张表的数据
正常的表关联
select * from emp,dept;
select * from emp,dept where emp.deptno=dept.deptno;

2.别名:在做表关联查询的时候,如果出现表名过长过多,或者两个表的字段名一样的情况,那么我们可以给这些表或者字段名起另外一个名字

--查询每个员工在哪个部门上班?
select e.ename 姓名,d.deptno,d.dname
from emp e,dept d
where e.deptno=d.deptno;
--查询出罗志祥是在哪里上班?
--查询出宋吉吉是在哪个部门上班?
select e.ename 名字,d.local 地点 from emp e,dept d where e.deptno=d.deptno and e.ename='宋吉吉';
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and e.ename='罗志祥';

3.and :并且
   or:或者

--查询出职位是Actor或者20号部门的员工信息
select * from emp where job='actor' or deptno=20;
--查询出20号部门,并且薪水大于10000或者
职位是singer的员工信息

4.判断是不是null,必须使用is或者is not
--查看哪些员工没有奖金?
select ename,comm from emp where comm is not null;

5.模糊查询
    %:表示0个或者多个字符(任意个)
    _:表示单子字符
    like:像...一样

--查询姓罗的员工信息
select * from emp where ename like '罗%';

--查询出名字的第二个字是吉的员工信息
select * from emp where ename like '_吉%';

--查询出名字中包含三的员工信息
select * from emp where ename like '%三%';

6.in():在什么里面,在大部分情况下等同于or
--查看10号或者20号部门的员工信息

select * from emp where deptno in(10,20);

7.between...and:在..之间
注意:会包含两端的值

--查看薪资在10000-20000之间的员工信息
select * from emp where salary>1000 and salary<10000;
select * from emp where salary between 1000 and 10000;

8.all和any
注意:这两个单词不能单独存在,必须和>或者<连用
>any:大于所有结果集中最小的即可
<any:小于所有结果集中最大的即可
>all:大于所有结果集中最大的即可
<all:小于所有结果集中最小的即可

create table emp1(
    salary double(8,2)
)
insert into emp1 values(10000);
--查询出emp表中薪资要大于emp1表中
    所有薪资的员工信息
--子查询:查询当中还有查询
select * from emp where 
salary>any(select salary from emp1);

9.distinct:去除重复

--查看公司有哪些职位
select distinct job from emp;

10.order by:排序
    -desc:按照降序排列
    -asc:按照升序排列
注意:如果不写排序方式,默认就是asc,按照升序排列
order by 通常是写在sql语句的最后

--查看员工的信息,并且按照薪资的降序排列
    select * from emp order by salary asc;
--查看薪资大于5000的员工信息,并且按照奖金的降序排列
select * from emp where salary>5000 order by comm desc;

11.聚合函数:又叫做多行函数或者分组函数
        聚合函数是对结果集中某些字段的值进行统计的
max(),min(),avg(),sum()等

--查询出公司中最高的工资是多少?
select max(salary) from emp;

--查询出公司中工资最高的是谁?
select ename from emp where salary=(select max(salary) from emp);

--查询出公司中谁的年薪最少?
select ename from emp where (salary*13+ifnull(comm,0)*12)
=(select min(salary*13+ifnull(comm,0)*12) from emp);

select * from emp;

--查看公司的平均薪资是多少?
select avg(salary) from emp;

--查看公司的平均奖金
select avg(comm) from emp;

注意:聚合函数是会自动的忽略null值的,如果值为null,
会自动的跳过这条数据的,不会计算在内

12.count():统计个数的

--查看公司有多少个员工?
select count(*) from emp;

--查看公司几个人有奖金?
select count(comm) from emp;
select * from emp;

13.group by:分组函数
    将结果集按照某一个字段相同的数据记录看做是一组
注意:在select中出现的字段,必须出现在group by中,否则查出来的数据就不对
反过来,出现在group by中的字段不一定要出现在select中

--查看每个部门的平均薪资?
select avg(salary) from emp group by deptno;

--查看同部门,同职位的员工的平均薪资
    select deptno,job,avg(salary) from emp group by deptno,job;//这里有表关联

--查看公司的平均薪资,前提是,该部门的平均薪资大于5000
select d.av from
(select avg(salary) av from emp group by deptno) d
where d.av>5000; 

where:是一个约束声明,他是在查询结果出来之前进行条件约束和过滤
having:是一个过滤声明,他是在查询结果出来之后进行条件约束和过滤

having通常是跟在group by后面使用的
select avg(salary) from emp group by deptno
having avg(salary)>5000;

--查看部门的平均薪资要高于9000的部门
  中的最高薪资和最低薪资
select deptno,max(salary),min(salary) from emp group by deptno
having avg(salary)>9000;
 

14.join...on
内连接
外连接
   -左外连接:left join on
    以join左边的表为驱动表,那么驱动表中有的字段在右边的表中
  没有数据的会以默认值显示
     -右外连接:right join on

select * from emp e,dept d where
e.deptno=d.deptno;

select * from emp e inner join dept d on
e.deptno=d.deptno;

select * from emp e left join dept d
on e.deptno=d.deptno;

select * from emp e right join dept d
on e.deptno=d.deptno;
select * from dept;

1.查看吴签的领导是谁?
select ename from emp where 
empno=(select manager from emp where ename='吴签');

select e2.ename from emp e1,emp e2
where e1.manager=e2.empno and e1.ename='吴签';

2.查看大于公司的平均薪资的员工信息
select ename,job from emp where 
salary>(select avg(salary) from emp);

3.查看薪资在5000-15000之间的员工姓名及工作地点
select e.ename,d.local from emp e,dept d where 
e.deptno=d.deptno and e.salary between 5000 and 15000;

4.查看20和30号部门的员工信息
select * from emp where 
deptno=20 or deptno=30;

5.查看公司有多少个部门?
select count(distinct deptno) from emp;

6.按照员工入职日期的降序进行排列,查看每个员工的信息
select * from emp order by hiredate desc;

7.查看每个部门的最高薪资,前提是这个部门的
平均薪资大于8000(该薪资包含奖金)
select max(salary) from emp group by 
deptno having avg(salary)>8000;

8.查看所有奖金不为null的员工的信息
select * from emp where comm is not null;

9.查看职位的第三个字母为c的员工的信息
select * from emp where job like '__c%';

10.查看没有奖金的员工的个数
select count(*) from emp where comm is null;

11.查看每个部门的最低薪水是多少?前提是,该部门的最低薪水要比30号部门的最低薪水高
select min(salary) from emp group by deptno
having min(salary)>(select min(salary) from emp
where deptno=30);

12.查看比自己所在的部门的平均薪资要高的员工信息
select * from emp e where salary>(select avg(salary)
from emp d group by deptno having d.deptno=e.deptno);

select * from emp e ,
(select avg(salary) av,deptno dp from emp t group by deptno) d
 where e.deptno=d.dp having salary>av;

select * from emp e ,
(select avg(salary) av,deptno dp from emp t group by deptno) d
 where e.deptno=d.dp and e.salary>d.av;

13.查看2018年前入职且薪水大于6000的员工`
select * from emp where hiredate<'2018'
and salary>6000;