MySQL-作业

CREATE TABLE yg(
            empno INT,
            ename VARCHAR(20),
            job VARCHAR(6),
            mgr INT,
            hiredate DATE,
            sal DOUBLE,
            comm DOUBLE,
            deptno INT
);
DESC yg;
INSERT INTO yg VALUES(1002,'黛绮丝','销售员',1006,20010220,16000.00,3000.00,30);
INSERT INTO yg VALUES(1003,'殷天正','销售员',1006,20010222,12500.00,5000.00,30);
INSERT INTO yg VALUES(1005,'谢逊','销售员',1006,20010928,16000.00,14000.00,30);
INSERT INTO yg VALUES(1010,'韦一笑','销售员',1006,20010908,15000.00,0.00,30);
INSERT INTO yg VALUES(1002,'张三','保洁员',1001,20130501,80000.00,50000.00,50);
INSERT INTO yg(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1001,'甘宁','文员',1013,20001217,8000.00,20);
INSERT INTO yg(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1004,'刘备','经理',1009,20010402,29750.00,20);
INSERT INTO yg(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1006,'关羽','经理',1009,20000501,28500.00,30);
INSERT INTO yg(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1007,'张飞','经理',1009,20010901,24500.00,10);
INSERT INTO yg(empno,ename,job,hiredate,sal,deptno) VALUES(1009,'曾阿牛','董事长',20011117,50000.00,10);
INSERT INTO yg(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1011,'周泰','文员',1008,20070523,11000.00,20);
INSERT INTO yg(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1012,'程普','文员',1006,20011203,9500.00,30);
INSERT INTO yg(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1013,'庞统','分析师',1004,20011203,30000.00,20);
INSERT INTO yg(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1014,'黄盖','文员',1007,20020123,13000.00,10);
INSERT INTO yg(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1008,'诸葛亮','分析师',1004,20070419,30000.00,20);
UPDATE yg SET empno=1015 WHERE ename='张三';
UPDATE yg SET sal=12500 WHERE ename='谢逊';
SELECT * FROM yg;
/*查询部门编号为30的员工*/
SELECT * FROM yg WHERE deptno=30;
/*所有销售员的姓名、编号和部门编号*/
/*找出奖金高于工资的员工*/
SELECT * FROM yg WHERE comm>sal;
/*找出奖金高于工资60%的员工*/
SELECT * FROM yg WHERE comm>sal*0.6;
/*找出部门编号为10中的所有经理和部门编号为20的所有销售员的详细资料*/
SELECT * FROM yg WHERE (deptno=10 AND mgr=1009)OR(deptno=20 AND mgr=1006);
  
/*找出部门编号为10中的所有经理和部门编号为20的所有销售员的详细资料和既不是销售又不是经理但工资大于20000的所有员工的详细资料*/
SELECT * FROM yg WHERE (deptno=10 AND mgr=1009)OR(deptno=20 AND mgr=1006)OR(mgr<>1009 AND mgr<>1006 AND sal>=20000);
 /*无奖金或者奖金低于1000的员工*/
 SELECT * FROM yg WHERE comm IS NULL OR comm<1000;
 /*查询名字有三个字组成的员工*/
 SELECT * FROM yg WHERE ename LIKE'___';
 /*查询2000年后入职的员工*/
 SELECT * FROM yg WHERE hiredate>20000000;
 /*查询所有员工详细信息，用编号升序排序*/ 
 SELECT * FROM yg ORDER BY empno ASC;
 /*查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序*/
 SELECT * FROM yg ORDER BY sal DESC,hiredate ASC;
 /*查询姓周的两个名字的员工*/
 SELECT * FROM yg WHERE ename LIKE '周_';
 /*查询所有姓张的员工*/
 SELECT * FROM yg WHERE ename LIKE '张%';
 /*查询该部门有多少个岗位，每个岗位有多少人*/ 
 SELECT *,COUNT(*) FROM yg GROUP BY job;
 /*查询哪个岗位中人数大于3*/
 SELECT *,COUNT(*)FROM yg GROUP BY job
   HAVING COUNT(*)>3;