MySql 查询实例

USE  testdb;
CREATE DATABASE testdb CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE emp(
    empno INT AUTO_INCREMENT PRIMARY KEY,
    ename VARCHAR(20) NOT NULL,
    sal DOUBLE ,
    deptno INT,
    mgr INT
);
CREATE TABLE dept(
    deptno INT AUTO_INCREMENT PRIMARY KEY,
    dname VARCHAR(50),
    loc VARCHAR(50)
);

#drop database testdb;
#drop table emp;
#drop table dept;

SELECT * FROM emp;
SELECT * FROM dept;

INSERT INTO emp(ename,sal,deptno,mgr) VALUE("张三",100.0,1,8); 
INSERT INTO emp(ename,sal,deptno,mgr) VALUE("李四",200.0,1,8); 
INSERT INTO emp(ename,sal,deptno,mgr) VALUE("王二",300.0,1,8); 
INSERT INTO emp(ename,sal,deptno,mgr) VALUE("麻子",400.0,1,8); 
INSERT INTO emp(ename,sal,deptno,mgr) VALUE("lini",100.0,2,9); 
INSERT INTO emp(ename,sal,deptno,mgr) VALUE("big",100.0,2,9); 
INSERT INTO emp(ename,sal,deptno,mgr) VALUE("duog",2100.0,2,9); 
INSERT INTO emp(ename,sal,deptno) VALUE("boss",1200.0,3); 
INSERT INTO emp(ename,sal,deptno) VALUE("mgr",1000.0,4); 

INSERT INTO dept(dname,loc) VALUE("研发部","501");
INSERT INTO dept(dname,loc) VALUE("财务部","502");
INSERT INTO dept(dname,loc) VALUE("项目管理","503");
INSERT INTO dept(dname,loc) VALUE("财政管理","504");

#查找所有员工的信息，部门信息,领导信息
#emp表:远信息；
#dept:部门信息
#emp:领导信息
#关联条件
#e.deptno = d.deptno
#e.mgr = m.empno
SELECT e.empno,e.ename,e.sal FROM emp e;
SELECT e.empno,e.ename,e.sal,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno;
SELECT e.empno,e.ename,e.sal,d.dname,d.loc,m.ename FROM emp e,dept d,emp m WHERE e.deptno=d.deptno AND e.mgr=m.empno;
#查找部门名称，部门人数，平均工资，最高工资，最低工资
#dept:部门名称
#emp:统计部门人数，平均工资，最高工资，最低工资
#关联条件
#e.deptno=d.deptno
SELECT deptno dptno,COUNT(empno) COUNT,AVG(sal) AVG,MAX(sal) MAX,MIN(sal) MIN FROM emp GROUP BY deptno;
SELECT d.deptno,d.dname,dt.count,dt.avg,dt.max,dt.min FROM dept d,(SELECT deptno dptno,COUNT(empno) COUNT,AVG(sal) AVG,MAX(sal) MAX,MIN(sal) MIN FROM emp GROUP BY deptno) dt WHERE d.deptno=dt.dptno;
#比李四工资高的同部门雇员信息
#emp:查出用户信息
#emp:李四的工资与deptno
#关联条件:
#e.deptno=m.deptno
SELECT sal,deptno FROM emp WHERE ename="李四";
SELECT e.empno,e.ename,e.sal FROM emp e,(SELECT sal,deptno FROM emp WHERE ename="李四") m WHERE e.deptno=m.deptno AND e.sal>m.sal;
#工资高于本部门平均工资的雇员信息
#emp:雇员信息
#emp:统计平均工资
#关联条件：
#e.deptno=d.deptno
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
SELECT e.empno,e.ename,e.sal,tm.avg,e.deptno FROM emp e ,(SELECT deptno,AVG(sal) AVG FROM emp GROUP BY deptno) tm WHERE e.sal>=tm.avg AND e.deptno=tm.deptno;
#工资比李四或mgr工资高的雇员信息，部门信息,部门人数
#emp：雇员信息
#dept:部门信息
#emp:部门人数
#关联条件
#e.deptno=d.deptno
SELECT sal,deptno FROM emp WHERE ename IN ("李四","mgr");
SELECT e.empno,e.ename,e.deptno FROM emp e WHERE e.sal >ANY(SELECT sal FROM emp WHERE ename IN ("李四","mgr"));
SELECT e.empno,e.ename,e.deptno,d.dname FROM emp e,dept d WHERE e.sal >ANY(SELECT sal FROM emp WHERE ename IN ("李四","mgr")) AND e.deptno = d.deptno;
SELECT COUNT(empno),deptno FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE ename IN ("李四","mgr")) GROUP BY deptno ;
SELECT e.empno,e.ename,e.deptno,d.dname,dc.count FROM emp e,dept d,(SELECT COUNT(empno) COUNT,deptno FROM emp WHERE sal >ANY(SELECT sal FROM emp WHERE ename IN ("李四","mgr")) GROUP BY deptno) dc WHERE e.sal >ANY(SELECT sal FROM emp WHERE ename IN ("李四","mgr")) AND e.deptno = d.deptno AND e.deptno=dc.deptno AND e.ename<>"李四" AND e.ename<>"mgr";