mysql每个岗位上的男女人数怎么算 mysql中nvl

昨天我们已经把这张表创建好了

那么今天就根据需求来继续了

1、查询20号部门的所有员工信息

SELECT * FROM emp WHERE deptno = 20;

2、查询所有工作为CLERK的员工的工号、名字和部门名

SELECT empno,ename,deptno FROM emp WHERE job LIKE 'clerk';

3、查询奖金高于工资的员工的信息

SELECT * FROM emp WHERE comm > sal;

4、查询奖金高于工资的20%的员工信息

SELECT * FROM emp WHERE comm>(sal*0.2);

5、查询10号部门中工作为MANAGER和20号部门中工作为CLERK的员工的信息

SELECT * FROM emp WHERE (deptno = 20 AND job LIKE 'manager') OR (deptno = 20 AND job LIKE 'clerk');

6、查询所有工作不是MANAGER和CLERK,并且工资大于或等于2000的员工的详细信息

SELECT * FROM emp WHERE job NOT IN ('manager' ,'clerk') AND sal >= 2000;

7、查询所有有奖金的员工的不同工作(这里有个去重的关键字 distinct)

SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;

8、查询所有员工工资和奖金的和

SELECT ename,(sal+IFNULL(COMM,0))salcomm FROM emp;

MYSQL里面没有nvl这个函数，有ifnull，注意这里的Oracle的写法是这样的：

select ename,(sal+nvl(comm,0)) salcomm from emp

9、查询没有奖金或奖金低于100的员工信息

SELECT * FROM emp WHERE (comm IS NULL OR comm < 100);

10、查询各月倒数第二天入职的员工信息

SELECT * FROM emp WHERE hiredate IN (SELECT (LAST_DAY(hiredate)-1) FROM emp  );

11、查询员工工龄大于或等于10年的员工信息

SELECT * FROM emp WHERE YEAR(CURDATE())-YEAR(hiredate)>=10;

这里的oracle写法是这样的

select * from emp where (sysdate - hiredate)/365 >= 10 ;

12、查询员工信息，要求以首字母大写的方式显示所有员工的姓名

SELECT CONCAT(UPPER(SUBSTRING(ename,1,1)) , LOWER(SUBSTRING(ename,2))),ename FROM emp;

oracle的方式为：

select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp;

13、查询员工名正好为6个字符的员工的信息

SELECT * FROM emp WHERE LENGTH(ename) = 6;

14、查询员工名字中不包含字母S的员工

这个有两种写法：

SELECT * FROM emp WHERE ename NOT LIKE '%S%';

SELECT * FROM emp WHERE ename NOT IN (SELECT ename FROM emp WHERE ename LIKE '%S%');

第一种写法更快写，虽然也是遍历了整个数据库，但是只要遍历一次即可

15、查询员工姓名的第二个字母为M的员工

SELECT * FROM emp WHERE ename LIKE '_M%';

16、欻下所有员工的前3个字符

SELECT SUBSTR(ename,1,3)FROM emp;

17、查询所有员工的姓名，如果包含S则用s替代

SELECT REPLACE(ename,'s','S')FROM emp;

18、查询员工的姓名和入职日期，并按入职日期从先到后进行排序

SELECT ename,hiredate FROM emp ORDER BY hiredate ASC;

19、显示所有的姓名、工作、工资和奖金，按工作降序排列，若工作相同则按工资升序排列

SELECT ename,job,sal,comm FROM emp ORDER BY job DESC,sal ASC;

20、显示所有员工的姓名、入职日期，以入职日期所在的月份排序，若月份相同则按入职的年份排序

SELECT ename,hiredate,EXTRACT(YEAR FROM hiredate) YEAR,EXTRACT(MONTH FROM hiredate) MONTH FROM emp ORDER BY YEAR ASC , MONTH ASC

oracle的写法是这样的

SELECT ename,to_char(hiredate,'yyyy')||'-'||to_char(hiredate,'mm') FROM emp ORDER BY to_char(hiredate,'mm'),to_char(hiredate,'yyyy');

21、查询在2月入职的员工信息

SELECT * FROM emp WHERE EXTRACT(MONTH FROM hiredate) = 2;

oracle的写法：

select * from emp where to_char(hiredate,'mm') = 2 ;

SELECT ename,DATE_FORMAT(hiredate,'%Y年%m月%d日')工作年限 FROM emp;

oracle的写法：

select ename,floor((sysdate-hiredate)/365)||'年'||floor(mod((sysdate-hiredate),365)/30)||'月'||cell(mod(mod((sysdate-hiredate),365),30))||'天' from emp ;

23、查询至少有一个员工的部门信息

SELECT d.dname,COUNT(empno)部门人数 FROM emp e RIGHT JOIN dept d ON d.deptno = e.deptno GROUP BY d.dname,e.deptno HAVING COUNT(empno)>=1;

oracle的写法：

select * from dept where deptno in (select distinct deptno from emp where mgr is not null)

24，查询工资比smith工资高的员工

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename LIKE 'SMITH');

25、查询部门中薪水最高的人

SELECT ename,sal,emp.deptno FROM emp JOIN (SELECT deptno,MAX(sal) max_sal FROM emp GROUP BY deptno) t ON (emp.deptno = t.deptno AND emp.sal = t.max_sal);

26、查询部门平均薪水

SELECT deptno, avg_sal, grade FROM (SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno) t JOIN salgrade ON (t.avg_sal BETWEEN salgrade.losal AND salgrade.hisal);

27、查询所有员工工资都在900～3000之间的部门的信息。

SELECT * FROM dept WHERE deptno IN( SELECT deptno FROM emp  GROUP BY deptno HAVING MIN(sal)>900 AND MAX(sal)<3000)

28、统计各个工种的员工人数与平均工资。

SELECT job 工种,COUNT(empno) 员工人数,AVG(sal) 平均工资 FROM emp GROUP BY job

29、查询工资高于本部门平均工资的员工的信息及其部门的平均工资。

SELECT *,(SELECT AVG(sal) FROM emp e2 WHERE e2.deptno=e1.deptno) 部门平均工资 FROM emp e1 WHERE sal>(SELECT AVG(sal) FROM emp e2 WHERE e2.deptno=e1.deptno)

30、查询在SALES部门工作的员工的姓名信息。

SELECT * FROM dept LEFT OUTER JOIN emp ON dept.deptno=emp.deptno WHERE dept.dname='SALES'

下面是所有的sql语句

CREATE TABLE IF NOT EXISTS `EMP`(   `EMPNO` INT UNSIGNED AUTO_INCREMENT,   `ENAME` VARCHAR(100) NOT NULL,   `JOB` VARCHAR(40) NOT NULL,   `MGR` INT(4) NOT NULL,   `HIREDATE` DATE,   `SAL` VARCHAR(40) NOT NULL,   `COMM` VARCHAR(40) ,   `DEPTNO` INT(4) NOT NULL,   PRIMARY KEY ( `EMPNO` ))ENGINE=INNODB DEFAULT CHARSET=utf8;CREATE TABLE IF NOT EXISTS `dept`(  `DEPTNO` INT UNSIGNED AUTO_INCREMENT,  `DNAME` VARBINARY(50) NOT NULL,  `LOC` VARBINARY(30) NOT NULL,  PRIMARY KEY(`DEPTNO`))ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)  VALUES (1001,'james','CLERK',2001,STR_TO_DATE('1980/12/17','%Y/%m/%d'),'800','100',20);INSERT INTO emp (ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES ('SMITH','CLERK',2002,STR_TO_DATE('1980/2/20','%Y/%m/%d'),'800','100',20);INSERT INTO emp(ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES ('ALLEN','SAKESNAN',2003,STR_TO_DATE('1981-2-22','%Y-%m-%d'),'1600','300',30);INSERT INTO emp (ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES ('WARD','SALESMAN',2004,STR_TO_DATE('1981-2-22','%Y-%m-%d'),'1250','500',30);INSERT INTO emp(ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES ('JONES','MANAGER',2005,STR_TO_DATE('1981-4-2','%Y-%m-%d'),'2975',20);INSERT INTO emp(ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES('MARTIN','SALESMAN',2006,STR_TO_DATE('1981-9-28','%Y-%m-%d'),'1250','1400',30);INSERT INTO emp(ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES('BLAKE','MANAGER',2007,STR_TO_DATE('1981-5-1','%Y-%m-%d'),'2850',30);INSERT INTO emp(ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES('CLARK','MANAGER',2008,STR_TO_DATE('1981-6-9','%Y-%m-%d'),'2450',10);INSERT INTO emp(ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES ('SCOTT','ANALYST',2009,STR_TO_DATE('1987-4-19','%Y-%m-%d'),'3000',20);INSERT INTO emp(ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES('KING','PRESIDENT',2010,STR_TO_DATE('1981-11-17','%Y-%m-%d'),'5000',10);INSERT INTO emp(ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)VALUES('TURNER','SALESMAN',2011,STR_TO_DATE('1981-9-8','%Y-%m-%d'),'1500','0.00',30);INSERT INTO emp(ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES('ADAMS','CLERK',2012,STR_TO_DATE('1987-5-23','%Y-%m-%d'),'1100',20);INSERT INTO emp(ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES('JAMES','CLERK',2013,STR_TO_DATE('1981-12-3','%Y-%m-%d'),'950',30);INSERT INTO emp(ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES('PORD','ANALYST',2014,STR_TO_DATE('1981-12-3','%Y-%m-%d'),'3000',20);INSERT INTO emp (ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO)VALUES('MILLER','CLERK',2015,STR_TO_DATE('1982-1-23','%Y-%m-%d'),'1300',10);INSERT INTO dept(DEPTNO,DNAME,LOC)VALUES(10,'ACCOUNTING','SHANGHAI');INSERT INTO dept(DEPTNO,DNAME,LOC)VALUES(20,'RESEARCH','BEIJING');INSERT INTO dept(DEPTNO,DNAME,LOC)VALUES (30,'SALES','ANHUI');INSERT INTO dept(DEPTNO,DNAME,LOC)VALUES (40,'OPERATIONS','NANJING');CREATE TABLE IF NOT EXISTS `salgrade`(  `GRADE` INT UNSIGNED AUTO_INCREMENT,  `LOSAL` VARBINARY(50) NOT NULL,  `HISAL` VARBINARY(30) NOT NULL,  PRIMARY KEY(`GRADE`)) ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT INTO salgrade(GRADE,LOSAL,HISAL)VALUES (1,'700','1200');INSERT INTO salgrade(LOSAL,HISAL)VALUES('1201','1400');INSERT INTO salgrade(LOSAL,HISAL)VALUES('1401','2000');INSERT INTO salgrade(LOSAL,HISAL)VALUES('2001','3000');INSERT INTO salgrade(LOSAL,HISAL)VALUES('3001','9999');SELECT * FROM emp WHERE deptno = 20;SELECT empno,ename,deptno FROM emp WHERE job LIKE 'clerk';SELECT * FROM emp WHERE comm > sal;SELECT * FROM emp WHERE comm>(sal*0.2);SELECT * FROM emp WHERE (deptno = 20 AND job LIKE 'manager') OR (deptno = 20 AND job LIKE 'clerk');SELECT * FROM emp WHERE job NOT IN ('manager' ,'clerk') AND sal >= 2000;SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;SELECT ename,(sal+IFNULL(COMM,0))salcomm FROM emp;SELECT * FROM empSELECT * FROM emp WHERE (comm IS NULL OR comm < 100);SELECT * FROM emp WHERE hiredate IN (SELECT (LAST_DAY(hiredate)-1) FROM emp  );SELECT * FROM emp WHERE YEAR(CURDATE())-YEAR(hiredate)>=10;SELECT CONCAT(UPPER(SUBSTRING(ename,1,1)) , LOWER(SUBSTRING(ename,2))),ename FROM emp;SELECT * FROM emp WHERE LENGTH(ename) = 6;SELECT * FROM emp WHERE ename NOT LIKE '%S%';SELECT * FROM emp WHERE ename NOT IN (SELECT ename FROM emp WHERE ename LIKE '%S%');SELECT * FROM emp WHERE ename LIKE '_M%';SELECT SUBSTR(ename,1,3)FROM emp;SELECT REPLACE(ename,'s','S')FROM emp;SELECT ename,hiredate FROM emp ORDER BY hiredate ASC;SELECT ename,job,sal,comm FROM emp ORDER BY job DESC,sal ASC;SELECT ename,to_char(hiredate,'yyyy')||'-'||to_char(hiredate,'mm') FROM emp ORDER BY to_char(hiredate,'mm'),to_char(hiredate,'yyyy');SELECT ename,hiredate,EXTRACT(YEAR FROM hiredate) YEAR,EXTRACT(MONTH FROM hiredate) MONTH FROM emp ORDER BY YEAR ASC , MONTH ASCSELECT * FROM emp WHERE EXTRACT(MONTH FROM hiredate) = 2;SELECT ename,DATE_FORMAT(hiredate,'%Y年%m月%d日')工作年限 FROM emp;SELECT d.dname,COUNT(empno)部门人数 FROM emp e RIGHT JOIN dept d ON d.deptno = e.deptno GROUP BY d.dname,e.deptno HAVING COUNT(empno)>=1;SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename LIKE 'SMITH');SELECT ename,sal,emp.deptno FROM emp JOIN (SELECT deptno,MAX(sal) max_sal FROM emp GROUP BY deptno) t ON (emp.deptno = t.deptno AND emp.sal = t.max_sal);SELECT deptno, avg_sal, grade FROM (SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno) t JOIN salgrade ON (t.avg_sal BETWEEN salgrade.losal AND salgrade.hisal);SELECT * FROM dept WHERE deptno IN( SELECT deptno FROM emp  GROUP BY deptno HAVING MIN(sal)>900 AND MAX(sal)<3000)SELECT job 工种,COUNT(empno) 员工人数,AVG(sal) 平均工资 FROM emp GROUP BY job SELECT *,(SELECT AVG(sal) FROM emp e2 WHERE e2.deptno=e1.deptno) 部门平均工资 FROM emp e1 WHERE sal>(SELECT AVG(sal) FROM emp e2 WHERE e2.deptno=e1.deptno)SELECT * FROM dept LEFT OUTER JOIN emp ON dept.deptno=emp.deptno WHERE dept.dname='SALES'

接下来打算是花一段时间啃Spring源码了，可能分享主要是在Spring上面了哈