MySQL笔记 数学函数 多表查询 all any操作符

-- 查看那个用户在连接数据库，会显示用户名和ip地址
select user() from dual;
-- 查看在使用那个数据库
select database()
-- md5加密 
select md5('csj') from dual; 
-- 查看mysql中有哪些用户   
select * from mysql.user

select * from emp;
-- 显示每种岗位的雇员总数、平均工资
select count(*),avg(sal) from emp group by job

-- 显示雇员总数以及未获得补助的人数
select count(*),count(if(comm is null,1,null)) from emp;

-- 显示管理者人数
select mgr from emp
select count(distinct mgr) from emp;

-- 显示官员工资的最大差额
select max(sal)-min(sal) as chae from emp;

-- 统计各个部门的平均工资，并且是大于1000的，并且按照平均工资从到到底排序，去除前两行
select deptno,avg(sal) from emp group by deptno having avg(sal)>1000 order by avg(sal) desc limit 0,2 
-- 公式
-- select column1,column2,... from table
-- 	group by
-- 	having
-- 	order by 
-- 	limit
	
-- 多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集
select ename,sal,dept.deptno from emp,dept where emp.deptno=dept.deptno order by emp.deptno desc

-- 笛卡尔集
select * from emp,dept where emp.deptno=dept.deptno

-- 显示部门号为10的部门名、员工名和工资
select ename,sal,dname,emp.deptno from emp,dept where emp.deptno=dept.deptno and emp.deptno=10

-- 显示各个员工的姓名、工资以及工资的类别
select ename,sal,grade from emp,salgrade where sal between losal and hisal 

-- 多行子查询
-- 显示与SMITH同一部门的所有员工
select * from emp where deptno=(select deptno from emp where ename='SMITH')
-- 查询和部门10的工作相同的雇员的名字、岗位、工资和部门号但不包含部门10的员工
select ename,job,sal,deptno from emp where job in(select distinct job from emp where deptno=10) and deptno<>10

-- **all any操作符**
-- 显示工资比部门30的的所有员工的工资高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30)
select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30)
-- 显示工资比部门30的的任意员工的工资高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30)
select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30)
-- 查找每个部门工资高于本部门平均工资的个人资料
-- 这个是错的，子查询会有好几个结果
select * from emp group by deptno having sal>(select avg(sal) from emp group by deptno)
select deptno,avg(sal) as avg_sal from emp group by deptno
select * from emp,(select deptno,avg(sal) as avg_sal from emp group by deptno) temp where emp.deptno=temp.deptno and emp.sal>temp.avg_sal order by emp.deptno
-- 查找每个部门的信息（部门名，编号，地址）
select count(*) from emp group by deptno
select dname,dept.deptno,loc,tmp.per_num as '人数' from dept,(select count(*) as per_num,deptno from emp group by deptno) tmp where dept.deptno=tmp.deptno