SQL数据查询2
USE h CREATE TABLE zy1( empno INT, ename VARCHAR(10), job VARCHAR(10), mgr INT, hiredate DATE, sal DOUBLE, COOM DOUBLE, deptno INT ) DROP TABLE zy1 DESC zy1; SELECT * FROM zy1 INSERT INTO zy1(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1001,'甘宁','文员',1013,'2000-12-17',8000,20); INSERT INTO zy1 VALUES(1002,'黛绮丝','销售员',1006,'2001-02-20',16000,3000,20); UPDATE zy1 SET sal=16000,deptno=30 WHERE empno=1002; INSERT INTO zy1 VALUES(1003,'殷天正','销售员',1006,'2001-02-22',12500,5000,20); INSERT INTO zy1(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1004,'刘备','经理',1009,'2001-04-02',29750,20); INSERT INTO zy1 VALUES(1005,'谢逊','销售员',1006,'2001-09-28',12500,14000.00,30); INSERT INTO zy1(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1006,'关羽','经理',1009,'2001-05-01',28500.00,30); INSERT INTO zy1(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1007,'张飞','经理',1009,'2001-09-01',24500.00,10); INSERT INTO zy1(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1008,'诸葛亮','分析师',1004,'2007-04-19',30000.00,20); INSERT INTO zy1(empno,ename,job,hiredate,sal,deptno) VALUES(1009,'曾阿牛','董事长','2001-11-17',50000.00,10); INSERT INTO zy1 VALUES(1010,'韦一笑','销售员',1006,'2001-09-08',15000.00,0.00,30); INSERT INTO zy1(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1011,'周泰','文员',1008,'2007-05-23',11000.00,20); INSERT INTO zy1(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1012,'程普','文员',1006,'2001-12-03',9500.00,30); INSERT INTO zy1(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1012,'庞统','分析师',1004,'2001-12-03',30000.00,20); INSERT INTO zy1(empno,ename,job,mgr,hiredate,sal,deptno) VALUES(1014,'黄盖','文员',1007,'2002-01-03',13000.00,10); INSERT INTO zy1 VALUES(1015,'张三','保洁员',1001,'2013-05-01',80000,50000,50); /*查询出部门编号为30的所有员工*/ SELECT * FROM zy1 WHERE deptno=30; /*所有销售员的姓名、编号和部门编号*/ SELECT ename,empno,deptno FROM zy1 WHERE job='销售员'; /*找出奖金高于工资的员工*/ SELECT * FROM zy1 WHERE COOM>sal; /*找出奖金高于工资60%的员工。*/ SELECT * FROM zy1 WHERE COOM>(sal*1.6); /*找出部门编号为10中所有经理,和部门编号为20中所有销售员的详细资料*/ SELECT * FROM zy1 WHERE deptno=10 AND job='经理' OR deptno=20 AND job='销售员'; /* 找出部门编号为10中所有经理,和部门编号为20中所有销售员的详细资料*/ SELECT * FROM zy1 WHERE deptno=10 AND job='经理' OR deptno=20 AND job='销售员' OR deptno<>'经理' AND deptno<>'销售员' AND sal>=20000; /*无奖金或奖金低于1000的员工。*/ SELECT * FROM zy1 WHERE COOM IS NULL OR COOM=0 OR COOM<1000; /*查询名字由三个字组成的员工*/ SELECT * FROM zy1 WHERE ename LIKE '___'; /*查询2000年入职的员工*/ SELECT * FROM zy1 WHERE hiredate BETWEEN 20000101 AND 20010101; /*查询所有员工详细信息,用编号升序排序*/ SELECT * FROM zy1 ORDER BY empno ASC; /*查询所有员工详细信息,用工资降序排序,如果工资相同使用入职日期升序排序*/ SELECT * FROM zy1 ORDER BY sal DESC,hiredate ASC; /*查询姓周的两个名字的员工*/ SELECT * FROM zy1 WHERE ename LIKE '周_'; /*查询所有姓张的员工*/ SELECT * FROM zy1 WHERE ename LIKE '张%'; /*14. 查询该部门有多少个岗位,每个岗位有多少人*/ SELECT job AS '岗位',COUNT(*) AS '人数' FROM zy1 GROUP BY job; /*查询哪个岗位中人数大于3*/ SELECT job AS '岗位',COUNT(*) AS '人数' FROM zy1 GROUP BY job HAVING COUNT(*)>3;