MySQL简单实例

#创建数据库和表CREATE DATABASE company;USE company;#部门表CREATE TABLE dept(deptno INT(3) PRIMARY KEY,dname VARCHAR(14),loc VARCHAR(13));#雇员表#drop table emp;CREATE TABLE emp(empno INT(4) NOT NULL PRIMARY KEY,ename VARCHAR(10),job VARCHAR(10),mgr INT(4), hiredate DATETIME,sal DOUBLE,comm DOUBLE,deptno INT(4),FOREIGN KEY(deptno) REFERENCES dept(deptno));#工资级别CREATE TABLE salgrade(grade INT(3) PRIMARY KEY,losal INT(3),hisal INT(3));#插入数据进行初始化USE company;#往部门表中插入数据INSERT INTO dept VALUES(10,'Accounting','New York');INSERT INTO dept VALUES(20,"Reserch","Dallas");INSERT INTO dept VALUES(30,"dales","chicago");INSERT INTO dept VALUES(40,'Operations','boston');INSERT INTO dept VALUES(50,'Admin','Washing');SELECT * FROM dept;#往雇员表中插入数据INSERT INTO emp VALUE(7396,'Smith','Clerk',7902,'1980-12-17',800,0,20);INSERT INTO emp VALUES(7499,'Allen',"Salesman",7698,'1981-2-10',1600,300,30);INSERT INTO emp VALUES(7844,'Turner','Salesman',7499,'1981-9-8',1500,0,30);INSERT INTO emp VALUES(7698,'Tom','Manager',0,'1981-9-8',6100,600,40);INSERT INTO emp VALUES(7876,'Adams','Clerk',7900,'1987-5-23',1100,0,20);INSERT INTO emp VALUES(7900,'James','Clerk',7698,'1981-12-3',2400,0,30);INSERT INTO emp VALUES(7902,'Ford','Analyst',7698,'1981-12-3',3000,NULL,20);INSERT INTO emp VALUES(7901,'Kik','Clerk',7900,'1981-12-3',1900,0,30);SELECT * FROM emp;#往工资级别表中插数据 INSERT INTO salgrade VALUES(1,700,1200);INSERT INTO salgrade VALUES(2,1201,1400);INSERT INTO salgrade VALUES(3,1401,2000);INSERT INTO salgrade VALUES(4,2001,3000);INSERT INTO salgrade VALUES(5,3001,5000);INSERT INTO salgrade VALUES(6,5001,10000);SELECT * FROM salgrade;#max,min,avg,sum,count#1.查询雇员表中工资最高的雇员的员工号，姓名，工资和部门号SELECT empno,ename,sal,deptno FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);#2.单条查询语句#薪水大于1200的雇员，按照部门编号进行分组，分组后的平均薪水必须大于1500，查询各分组的平均工资，按照工资的倒序进行排列SELECT AVG(sal)avg_sal,deptno FROM emp WHERE sal>1200 GROUP BY deptno HAVING avg_sal >1500 ORDER BY avg_sal DESC;#3.等值连接#查找每个雇员和所在的部门名SELECT ename,dname FROM emp,dept WHERE(emp.deptno=dept.deptno);#推荐(on 中写连接条件，where中写过滤条件)SELECT ename,dname FROM emp JOIN dept ON(emp.deptno=dept.deptno);#4.非等值连接#查询每个雇员姓名及其工资所在的等级SELECT ename,grade FROM emp e JOIN salgrade s ON(e.sal BETWEEN s.losal AND s.hisal);#5.查询雇员名第2个字母不是a的雇员的姓名，所在的组名，工资所在的等级#三张表的连接查询（先连接，再加上where语句进行过滤）SELECT ename,dname,grade FROM emp e JOIN dept d ON(e.deptno=d.deptno) JOIN salgrade s ON(e.sal BETWEEN s.losal AND s.hisal) WHERE ename NOT LIKE '_a%';#6.查询每个雇员和其经理的名字#自连接SELECT e1.ename,e2.ename FROM emp e1,emp e2 WHERE(e1.mgr=e2.empno);#推荐使用(join)SELECT e1.ename,e2.ename FROM emp e1 JOIN emp e2 ON(e1.mgr=e2.empno);#7.查询每个雇员和其经理的名字（包括老板，老板之上没有经理）#左外连接（会把左表中不符合条件的也显示出来）SELECT e1.ename,e2.ename FROM emp e1 LEFT JOIN emp e2 ON(e1.mgr=e2.empno);#8.查询每个雇员的名字及其所在部门的名字，（包括没有雇员的部门）#右连接（会把右表中不符合条件的也显示出来）SELECT ename,dname FROM emp e RIGHT JOIN dept d ON(e.deptno=d.deptno);#9.子查询1：查询每个部门中工资最高的姓名，薪水和部门编号#注意:查询两张表时，当列名相同时，进行表连接时需要用e.deptno来区分SELECT ename,sal,e.deptno FROM emp e JOIN (SELECT MAX(sal)max_sal,deptno FROM emp GROUP BY deptno) d ON(e.sal=d.max_sal AND e.deptno=d.deptno);#10.子查询2：查询每个部门平均工资所在的等级SELECT deptno,avg_sal,grade FROM salgrade s JOIN (SELECT AVG(sal)avg_sal,deptno FROM emp GROUP BY deptno)d ON(d.avg_sal BETWEEN s.losal AND s.hisal);SELECT deptno,avg_sal,grade FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) d JOIN salgrade s ON(d.avg_sal BETWEEN s.losal AND s.hisal); #11.子查询3：查询每个部门内平均的薪水等级SELECT deptno,AVG(d.grade)avg_grade FROM (SELECT deptno,grade FROM emp e JOIN salgrade s ON(e.sal BETWEEN s.losal AND s.hisal)) d  GROUP BY deptno;#12.子查询4：查询雇员中那些人是经理人 (DISTINCT 重复的只显示一次)SELECT ename FROM emp WHERE empno IN(SELECT DISTINCT mgr FROM emp);SELECT ename FROM emp JOIN (SELECT DISTINCT mgr FROM emp) t ON(emp.empno=t.mgr);#不可行【SELECT ename FROM emp on(empno IN(SELECT DISTINCT mgr FROM emp));】#13.子查询5：不准用库函数，求雇员表中薪水的最高者#求出额表中所有比中表最高工资低的工资表SELECT DISTINCT e1.sal FROM emp e1 JOIN emp e2 ON(e1.sal<e2.sal);#除去比最高工资低的工资表，剩下的就是最高工资SELECT sal FROM emp WHERE sal NOT IN (SELECT DISTINCT e1.sal FROM emp e1 JOIN emp e2 ON(e1.sal<e2.sal));#扩展：求最低工资SELECT sal FROM emp WHERE sal NOT IN (SELECT e1.sal FROM emp e1 JOIN emp e2 ON(e1.sal>e2.sal));#14.子查询6：平均薪水最高的部门的部门编号SELECT deptno,avg_sal FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) a WHERE avg_sal=(SELECT MAX(avg_sal) FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) b);#求部门的最高工资SELECT deptno,MAX(sal) FROM emp GROUP BY deptno;#15.子查询7：求平均薪水最高的部门的部门名称SELECT dname FROM dept WHERE deptno=(SELECT deptno FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) a WHERE avg_sal=(SELECT MAX(avg_sal) FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) b));#15.子查询8：查询平均薪水的等级最低的部门名称SELECT dname FROM dept WHERE deptno IN(SELECT deptno FROM (SELECT deptno,avg_sal,grade FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) d JOIN salgrade g ON(d.avg_sal BETWEEN g.losal AND g.hisal)) a WHERE grade=(SELECT MIN(grade) FROM (SELECT deptno,avg_sal,grade FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) d JOIN salgrade g ON(d.avg_sal BETWEEN g.losal AND g.hisal)) a));#平均薪水SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno;#求平均薪水的等级SELECT deptno,avg_sal,grade FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) d JOIN salgrade g ON(d.avg_sal BETWEEN g.losal AND g.hisal);#求平均薪水等级最低的SELECT MIN(grade) FROM (SELECT deptno,avg_sal,grade FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) d JOIN salgrade g ON(d.avg_sal BETWEEN g.losal AND g.hisal)) a;#平均薪水等级最低的部门编号SELECT deptno FROM (SELECT deptno,avg_sal,grade FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) d JOIN salgrade g ON(d.avg_sal BETWEEN g.losal AND g.hisal)) a WHERE grade=(SELECT MIN(grade) FROM (SELECT deptno,avg_sal,grade FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) d JOIN salgrade g ON(d.avg_sal BETWEEN g.losal AND g.hisal)) a);#select deptno from (SELECT deptno,avg_sal,grade FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) d JOIN salgrade g ON(d.avg_sal BETWEEN g.losal AND g.hisal)) c1 where grade=(SELECT MIN(grade) FROM (SELECT deptno,avg_sal,grade FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) d JOIN salgrade g ON(d.avg_sal BETWEEN g.losal AND g.hisal)) a3);#select deptno from (SELECT deptno,avg_sal,grade FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) d JOIN salgrade g ON(d.avg_sal BETWEEN g.losal AND g.hisal)) c1 where grade=(SELECT MIN(grade) FROM (SELECT deptno,avg_sal,grade FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) d JOIN salgrade g ON(d.avg_sal BETWEEN g.losal AND g.hisal)) a3);#select deptno from (select avg_sal,deptno,grade from (select avg(sal)avg_sal,deptno from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal)) t2 where grade=(select min(grade) from (select avg_sal,deptno,grade from (select avg(sal)avg_sal,deptno from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal)) t3);#select dname,t2.deptno,avg_sal,grade from (select avg_sal,deptno,grade from (select avg(sal)avg_sal,deptno from emp group by deptno) t join salgrade s on(t.avg_sal between s.losal and s.hisal)) t2 join dept on(t2.deptno=dept.deptno) where t2.grade= (select min(grade) from (select avg_sal,deptno,grade from (select avg(sal)avg_sal,deptno from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and hisal)) t3);#平均薪水等级最低的部门名称SELECT dname FROM dept WHERE deptno IN(SELECT deptno FROM (SELECT deptno,avg_sal,grade FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) d JOIN salgrade g ON(d.avg_sal BETWEEN g.losal AND g.hisal)) a WHERE grade=(SELECT MIN(grade) FROM (SELECT deptno,avg_sal,grade FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) d JOIN salgrade g ON(d.avg_sal BETWEEN g.losal AND g.hisal)) a));#简化操作CREATE VIEW v1 AS SELECT deptno,avg_sal,grade FROM (SELECT deptno,AVG(sal)avg_sal FROM emp GROUP BY deptno) d JOIN salgrade g ON(d.avg_sal BETWEEN g.losal AND g.hisal);CREATE VIEW v_dept_avg_sal_info AS SELECT avg_sal,deptno,grade FROM (SELECT AVG(sal)avg_sal,deptno FROM emp GROUP BY deptno) t JOIN salgrade s ON(t.avg_sal BETWEEN s.losal AND hisal);