MySql---集中练习MySQL的CRUD-->select查询

这里主要记录查询步骤：核心内容自己体会。（注意：测试插入的数据中，总经理damen不属于任何部门，部门编号为0）；
1.根据查询要求，分析哪些信息需要通过哪些表查询而得。
2.根据表与表之间的联系，确定表之间的关联条件。
3.根据分析的结果一步一步的查询。

#创建数据库CREATE DATABASE IF NOT EXISTS testdb1 DEFAULT CHARACTER SET utf8;#创建表CREATE TABLE emp(    eid INT AUTO_INCREMENT PRIMARY KEY,    ename VARCHAR(50),    sal DOUBLE,    mgrid INT,    sex VARCHAR(20),    did INT);CREATE TABLE dept(    did INT AUTO_INCREMENT PRIMARY KEY,    dname VARCHAR(50),    loc VARCHAR(50),    hisal DOUBLE,    losal DOUBLE);CREATE TABLE role(    rid INT AUTO_INCREMENT PRIMARY KEY,    rname VARCHAR(50));CREATE TABLE ACTION(    aid INT AUTO_INCREMENT PRIMARY KEY,    aname VARCHAR(50));CREATE TABLE emp_role(    eid INT,    rid INT);CREATE TABLE role_action(    rid INT,    aid INT);#插入数据INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("张三1",4000.0,13,"男",1);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("李四1",3000.0,13,"女",1);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("王二1",2400.0,13,"男",1);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("麻子1",4600.0,13,"女",1);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("张三2",4000.0,14,"男",2);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("李四2",3000.0,14,"女",2);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("王二2",2400.0,14,"男",2);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("麻子2",4600.0,14,"女",2);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("张三3",1000.0,15,"男",3);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("李四3",300.0,15,"女",3);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("王二3",200.0,15,"男",3);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("麻子3",8600.0,15,"女",3);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("Joy",4600.0,16,"男",1);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("Tom",3200.0,16,"女",2);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("nili",1400.0,16,"男",3);INSERT INTO emp (ename,sal,mgrid,sex,did) VALUE ("damen",80000.0,NULL,"女",0);SELECT * FROM emp;INSERT INTO dept(dname,loc,hisal,losal) VALUE ("研发部","501",2000,3000);INSERT INTO dept(dname,loc,hisal,losal) VALUE ("财务部","502",1000,2000);INSERT INTO dept(dname,loc,hisal,losal) VALUE ("开发部","503",3000,4000);SELECT * FROM dept;INSERT INTO role(rname) VALUE ("普通员工");INSERT INTO role(rname) VALUE ("部门经理");INSERT INTO role(rname) VALUE ("总经理");SELECT * FROM role;INSERT INTO ACTION(aname) VALUE ("查询");INSERT INTO ACTION(aname) VALUE ("增加");INSERT INTO ACTION(aname) VALUE ("修改");INSERT INTO ACTION(aname) VALUE ("删除");SELECT * FROM ACTION;INSERT INTO emp_role(eid,rid) VALUE (1,1);INSERT INTO emp_role(eid,rid) VALUE (2,1);INSERT INTO emp_role(eid,rid) VALUE (3,1);INSERT INTO emp_role(eid,rid) VALUE (4,1);INSERT INTO emp_role(eid,rid) VALUE (5,1);INSERT INTO emp_role(eid,rid) VALUE (6,1);INSERT INTO emp_role(eid,rid) VALUE (7,1);INSERT INTO emp_role(eid,rid) VALUE (8,1);INSERT INTO emp_role(eid,rid) VALUE (9,1);INSERT INTO emp_role(eid,rid) VALUE (10,1);INSERT INTO emp_role(eid,rid) VALUE (11,1);INSERT INTO emp_role(eid,rid) VALUE (12,1);INSERT INTO emp_role(eid,rid) VALUE (13,2);INSERT INTO emp_role(eid,rid) VALUE (14,2);INSERT INTO emp_role(eid,rid) VALUE (15,2);INSERT INTO emp_role(eid,rid) VALUE (16,3);SELECT * FROM emp_role;INSERT INTO role_action(rid,aid) VALUE (1,3);INSERT INTO role_action(rid,aid) VALUE (2,3);INSERT INTO role_action(rid,aid) VALUE (3,3);INSERT INTO role_action(rid,aid) VALUE (4,3);INSERT INTO role_action(rid,aid) VALUE (5,3);INSERT INTO role_action(rid,aid) VALUE (6,3);INSERT INTO role_action(rid,aid) VALUE (7,3);INSERT INTO role_action(rid,aid) VALUE (8,3);INSERT INTO role_action(rid,aid) VALUE (9,3);INSERT INTO role_action(rid,aid) VALUE (10,3);INSERT INTO role_action(rid,aid) VALUE (11,3);INSERT INTO role_action(rid,aid) VALUE (12,3);INSERT INTO role_action(rid,aid) VALUE (13,1);INSERT INTO role_action(rid,aid) VALUE (14,1);INSERT INTO role_action(rid,aid) VALUE (15,1);INSERT INTO role_action(rid,aid) VALUE (13,2);INSERT INTO role_action(rid,aid) VALUE (14,2);INSERT INTO role_action(rid,aid) VALUE (15,2);INSERT INTO role_action(rid,aid) VALUE (16,1);INSERT INTO role_action(rid,aid) VALUE (16,2);INSERT INTO role_action(rid,aid) VALUE (16,3);INSERT INTO role_action(rid,aid) VALUE (16,4);SELECT * FROM role_action;#1.查询张三1的基本信息，部门信息,领导信息，角色信息，权限信息#所用表#emp表:基本信息#dept:部门信息#emp表:领导信息#role表:部门信息#action:权限信息#关联条件#e.did=d.did#e.mgrid=em.eid#e.eid=er.eid#er.rid=r.rid#r.rid=ra.rid#ra.aid=a.aid#查询基本信息与部门信息SELECT e.eid,e.ename,e.sal,d.dname,d.loc FROM emp e,dept d WHERE e.did=d.did AND e.ename="张三1";#查询角色ridSELECT er.rid FROM emp e,emp_role er WHERE e.eid=er.eid AND e.ename="张三1"; #查询角色名称SELECT r.rname FROM role r WHERE r.rid IN (SELECT er.rid FROM emp e,emp_role er WHERE e.eid=er.eid AND e.ename="张三1");#根据rid查询权限aidSELECT ra.aid FROM role_action ra WHERE ra.rid IN (SELECT er.rid FROM emp e,emp_role er WHERE e.eid=er.eid AND e.ename="张三1");#查询权限名称SELECT a.aname FROM ACTION a WHERE a.aid IN (SELECT ra.aid FROM role_action ra WHERE ra.rid IN (SELECT er.rid FROM emp e,emp_role er WHERE e.eid=er.eid AND e.ename="张三1")); #所有信息已经查询完毕，将需要的信息进行整合 (将所有select，from，与where分别整合到相应的select，from，与where后面)SELECT e.eid,e.ename,e.sal,d.dname,em.ename,d.loc,r.rname,a.aname  FROM emp e,dept d,emp em,role r,ACTION a WHERE e.did=d.did AND e.mgrid=em.eid AND e.ename="张三1" AND r.rid IN (SELECT er.rid FROM emp e,emp_role er WHERE e.eid=er.eid AND e.ename="张三1") AND a.aid IN (SELECT ra.aid FROM role_action ra WHERE ra.rid IN (SELECT er.rid FROM emp e,emp_role er WHERE e.eid=er.eid AND e.ename="张三1"));#2.查询多有部门比王二2工资高的人数#emp表:统计人数#dept表:部门信息#关联条件#e.did=d.did#查询所有工资比他高的SELECT e.did FROM emp e WHERE e.sal > (SELECT sal FROM emp WHERE ename="王二2");SELECT e.did FROM emp e WHERE e.sal >ALL (SELECT sal FROM emp WHERE ename="王二2");#对查询结果进行统计，有统计就有分组条件SELECT COUNT(e.did),e.did FROM emp e WHERE e.sal >ALL (SELECT sal FROM emp WHERE ename="王二2") AND e.did<>0 GROUP BY e.did;#根据关联条件查询出部门名称SELECT COUNT(e.did),e.did,d.dname FROM emp e,dept d WHERE e.sal >ALL (SELECT sal FROM emp WHERE ename="王二2") AND e.did<>0  AND e.did=d.did GROUP BY e.did;#3.查询高于本部门平均工资的员工信息#emp表:平均工资，#emp表:员工信息#dept表:部门名称#关联条件#e.did=s.did#e.did=d.did#查询平均工资SELECT AVG(e.sal),e.did FROM emp e WHERE e.did<>0 GROUP BY e.did;#查询高于本部门平均工资的员工SELECT  e.*,s.avg FROM emp e,(SELECT AVG(e.sal) AVG,e.did sid FROM emp e WHERE e.did<>0 GROUP BY e.did) s WHERE s.sid=e.did AND e.sal>s.avg;#查询相应的部门名称SELECT  e.*,s.avg ,d.dname FROM emp e,dept d,(SELECT AVG(e.sal) AVG,e.did sid FROM emp e WHERE e.did<>0 GROUP BY e.did) s WHERE s.sid=e.did AND e.sal>s.avg AND e.did=d.did;