Mysql的练习单表查询，多表查询。分组函数查询，等级查询

第一步：查看数据库        show databases;

第二部：进入数据库         use db0113;

第三步：创建表  emp      命令如下

          create table emp (
         ->  empno INT(4) NOT NULL COMMENT '员工编号',
         ->  ename VARCHAR(10) COMMENT '员工名字',
         ->  job VARCHAR(10) COMMENT '职位',
         ->  mgr INT(4) COMMENT '上司',
         ->  hiredate DATE COMMENT '入职时间',
         ->  sal INT(7) COMMENT '基本工资',
         ->  comm INT(7) COMMENT '补贴',
         ->  deptno INT(2) COMMENT '所属部门编号'
         -> );

第四步：插入信息      命令如下

INSERT INTO `emp` VALUES('7369','张倩','办事员','7902','2002-12-17','820',NULL,'20');

INSERT INTO `emp` VALUES('7499','刘博','售货员','7698','1992-02-20','1900','300','30');

INSERT INTO `emp` VALUES('752 1','李兴','售货员','7698','1995-07-22','1250','500','30');

INSERT INTO `emp` VALUES('7566','李雷','人事部长','7839','1991-04-02','975',NULL,'20');

INSERT INTO `emp` VALUES('7654','刘浩','售货员','7698','1991-09-28','1250','1400','30');

INSERT INTO `emp` VALUES('7698','刘涛','销售部长','7839','1997-05-01','2850',NULL,'30');

INSERT INTO `emp` VALUES('7782','华仔','人事部长','7839','1995-06-09','2450',NULL,'10');

INSERT INTO `emp` VALUES('7788','张飞','人事专员','7566','1998-04-19','3000',NULL,'20');

INSERT INTO `emp` VALUES('7839','马晓云','董事长',NULL,'1991-11-17','5000',NULL,'10');

INSERT INTO `emp` VALUES('7844','马琪','售货员','7698','1996-09-08','1500','0','30');

INSERT INTO `emp` VALUES('7876','李涵','办事员','7788','1997-05-23','1100',NULL,'20');

INSERT INTO `emp` VALUES('7900','李小涵','销售员','7698','1993-2-13','950',NULL,'30');

INSERT INTO `emp` VALUES('7902','张三','人事组长','7566','1992-10-08','3000',NULL,'20');

INSERT INTO `emp` VALUES('7934','张三丰','人事长','7782','1997-06-23','1300',NULL,'10');

第五步：进行单表查询

5.1：选择部门30中的所有员工。          命令如下

select * from emp where deptno = 30;

5.2：列出所有办事员的姓名，编号和部门编号

select ename,empno,deptno from emp where job='办事员';

5.3:找出佣金高于薪金的员工

select ename,sal,comm from emp where comm>sal;

5.4:找出没有佣金的员工

select ename,sal,comm from emp where comm is null or comm = 0;

5.5:找出佣金高于薪金的60%的员工

select * from emp where comm >sal*0.6;

5.6:找出部门10中所有人事部长和部门20中所有办事员的详细资料.

select * from emp where (deptno = 10 and job = '人事部长') or (deptno = 20 AND job='办事员')

5.7:找出收取佣金的员工

select * from emp where comm>0;

5.8:找出不收取佣金或收取的佣金低于100的员工.

select * from emp where comm<=0 or comm<100;

5.9:找出姓张的员工的信息.

select * from emp where ename like '张%';

5.10:显示员工的姓名和受雇日期,新的员工排在最前面.

select ename,hiredate from emp order by hiredate DESC;

5.11:按工作的降序排序,若工作相同则按薪金排序

select ename,hiredate from emp order by job,sal;

5.12:查出不姓刘的员工的信息

select * from emp where ename not like '刘%';

5.13:取出姓李的员工

select ename from emp where substr(ename,1,1)='李';

第六步：创建其他的表，并添加数据。

6.1：创建表salgrade。

          CREATE TABLE salgrade(
          grade INT (10) COMMENT '工资等级',
          losal INT (10) COMMENT '最低限额',
          hisal INT (10) COMMENT '最高限额'
          );

6.2：插入salgrade表信息。

insert into salgrade (grade, losal, hisal)VALUES (1, 700,  1200);

insert into salgrade (grade, losal, hisal)VALUES (2, 1201, 1400);

insert into salgrade (grade, losal, hisal)VALUES (3, 1401, 2000);

insert into salgrade (grade, losal, hisal)VALUES (4, 2001, 3000);

insert into salgrade (grade, losal, hisal)VALUES (5, 3001, 9999);

6.3：创建表dept。

    create table dept(
    ->  deptno INT(2) NOT NULL COMMENT '部门编号',
    ->  dname VARCHAR (15) COMMENT '部门名称',
    ->  loc VARCHAR (20) COMMENT '地理位置'
    -> );

6.4：插入dept表信息。

insert into dept (deptno,dname,loc)VALUES (10,'财务部','高新四路');

insert into dept (deptno,dname,loc)VALUES (20,'人事部','科技二路');

insert into dept (deptno,dname,loc)VALUES (30,'销售部','长安区');

insert into dept (deptno,dname,loc)VALUES (40,'运输部','雁塔区');

6.5：添加主键。

ALTER TABLE dept ADD PRIMARY KEY (deptno);

ALTER TABLE emp ADD PRIMARY KEY (empno);

6.6：添加外键约束。

ALTER TABLE emp ADD CONSTRAINT f_ed_key

FOREIGN KEY (deptno)

REFERENCES dept(deptno);

第七步：多表查询

7.1：查询出每一位雇员的编号、姓名、职位、部门名称、位置。

select e.empno,e.ename,e.job,d.dname,d.loc

from emp e,dept d

where d.deptno = e.deptno;

7.2：要求查询出每一位雇员的姓名、职位、领导的姓名。

select e.ename,e.job,m.ename

from emp e,emp m

where e.mgr = m.empno;

7.3：查询出每个雇员的编号、姓名、基本工资、职位、领导的姓名、部门名称及位置。

select e.empno,e.ename,e.sal,e.job,e.mgr,d.dname,d.loc

FROM emp e,emp m,dept d

where e.mgr = m.empno AND e.deptno = d.deptno;

7.4：列出在部门"销售部"工作的员工的姓名，假定不知道销售部的部门编号。

select e.ename FROMfrom  emp e,dept d where e.deptno=d.deptno and d.dname='销售部';

7.5：列出与"李兴"从事相同工作的所有员工。

select e.* FROM emp e WHERE e.job=(SELECT el.job FROM emp el WHERE el.ename='李兴');

7.6：列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

SELECT e.ename,e.sal FROM emp e WHERE e.sal >ALL(SELECT el.sal FROM emp el WHERE deptno=30);

7.7：列出受雇日期早于其直接上级的所有员工。

select e.* FROM emp e WHERE ((SELECT el.hiredate FROM emp el WHERE e.mgr=el.empno)-e.hiredate)>0;

第八步：进行分组函数查询

8.1：按照部门编号分组，求出每个部的人数，平均工资。

select deptno,COUNT(empno), avg(sal)

from emp

group by deptno;

8.2：按照职位分组，求出每个职位的最高和最低工资。

select job,max(sal),min(sal)

from emp

group by  job;

8.3：计算出每个各职位的平均工资。

select job,AVG(sal)

from emp

group by job;

8.4：查询出每个部门的名称、部门的人数、平均工资。

select d.dname,count(e.empno),avg(e.sal)

from dept d,emp e

where e.deptno = d.deptno

group by d.dname;

8.5：要求统计出每个部门的详细信息，并且要求这些部门的平均工资高于2000。

select d.dname,avg(e.sal)a

from dept d left outer join emp e

on d.deptno = e.deptno

group by d.dname HAVING a>2000;

8.6：要求查询出工资比华仔还要高的全部雇员信息。

select * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='华仔');

8.7：要求查询出高于公司平均工资的全部雇员信息。

select * from emp where sal>(select avg (sal) from emp);

8.8：查询出每个部门的编号、名称、位置、部门人数、平均工资。

select d.dname, count(e.deptno) c,avg (e.sal)

from dept d left outer join emp e

on d.deptno = e.deptno

Group by d.dname order by c;

8.9：列出至少有一个员工的所有部门。

select d.deptno, d.dname FROM dept d WHERE

(select COUNT(e.empno) FROM emp e WHERE e.deptno=d.deptno)>1;

第九步：进行等级查询

9.1：查询每个工资等级各有多少员工。

select s.grade,COUNT(*)

FROM emp e JOIN salgrade s

ON e.sal BETWEEN s.losal AND hisal

GROUP BY s.grade;

9.2：查询部门中（所有人）的平均工资等级。

select e.deptno,AVG(s.grade) avggrade

FROM emp e JOIN salgrade s

ON e.sal BETWEEN s.losal AND hisal

GROUP BY e.deptno

ORDER BY e.deptno;

注释：以上所有内容仅供参考，可能出现错误的解释。