(五)练习Query查询语句

落日映苍穹つ 2022-05-27 00:05 243阅读 0赞

\#\#1.创建数据库  
\#\#2.在Query中输入如下:

/*
    Navicat MySQL Data Transfer
    
    Source Server         : forCourse
    Source Server Version : 50162
    Source Host           : localhost:3306
    Source Database       : forselect
    
    Target Server Type    : MYSQL
    Target Server Version : 50162
    File Encoding         : 65001
    
    Date: 2015-07-11 21:58:10
    */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for `bonus`
    -- ----------------------------
    DROP TABLE IF EXISTS `bonus`;
    CREATE TABLE `bonus` (
      `ename` varchar(10) DEFAULT NULL,
      `job` varchar(9) DEFAULT NULL,
      `sal` decimal(7,2) DEFAULT NULL,
      `comm` decimal(7,2) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
    
    -- ----------------------------
    -- Records of bonus
    -- ----------------------------
    
    -- ----------------------------
    -- Table structure for `dept`
    -- ----------------------------
    DROP TABLE IF EXISTS `dept`;
    CREATE TABLE `dept` (
      `deptno` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `dname` varchar(15) DEFAULT NULL,
      `loc` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`deptno`)
    ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=gbk;
    
    -- ----------------------------
    -- Records of dept
    -- ----------------------------
    INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'NEW YORK');
    INSERT INTO `dept` VALUES ('20', 'RESEARCH', 'DALLAS');
    INSERT INTO `dept` VALUES ('30', 'SALES', 'CHICAGO');
    INSERT INTO `dept` VALUES ('40', 'OPERATIONS', 'BOSTON');
    
    -- ----------------------------
    -- Table structure for `emp`
    -- ----------------------------
    DROP TABLE IF EXISTS `emp`;
    CREATE TABLE `emp` (
      `empno` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `ename` varchar(15) DEFAULT NULL,
      `job` varchar(10) DEFAULT NULL,
      `mgr` int(10) unsigned DEFAULT NULL,
      `hiredate` date DEFAULT NULL,
      `sal` decimal(7,2) DEFAULT NULL,
      `comm` decimal(7,2) DEFAULT NULL,
      `deptno` int(10) unsigned DEFAULT NULL,
      PRIMARY KEY (`empno`),
      KEY `deptno` (`deptno`),
      CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
    ) ENGINE=InnoDB AUTO_INCREMENT=7935 DEFAULT CHARSET=gbk;
    
    -- ----------------------------
    -- Records of emp
    -- ----------------------------
    INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', null, '20');
    INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30');
    INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30');
    INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975.00', null, '20');
    INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30');
    INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850.00', null, '30');
    INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450.00', null, '10');
    INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-07-13', '3000.00', null, '20');
    INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '10');
    INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', '0.00', '30');
    INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-07-13', '1100.00', null, '20');
    INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', null, '30');
    INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20');
    INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', null, '10');
    
    -- ----------------------------
    -- Table structure for `salgrade`
    -- ----------------------------
    DROP TABLE IF EXISTS `salgrade`;
    CREATE TABLE `salgrade` (
      `grade` int(10) unsigned DEFAULT NULL,
      `losal` int(10) unsigned DEFAULT NULL,
      `hisal` int(10) unsigned DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
    
    -- ----------------------------
    -- Records of salgrade
    -- ----------------------------
    INSERT INTO `salgrade` VALUES ('1', '700', '1200');
    INSERT INTO `salgrade` VALUES ('2', '1201', '1400');
    INSERT INTO `salgrade` VALUES ('3', '1401', '2000');
    INSERT INTO `salgrade` VALUES ('4', '2001', '3000');
    INSERT INTO `salgrade` VALUES ('5', '3001', '9999');

\#\#3.练习查询语句  
– 01.查询员工表所有数据，并说明使用\*的缺点  
SELECT \* from emp;

– 02.查询职位（JOB）为’PRESIDENT’的员工的工资  
SELECT sal from emp WHERE job=‘PRESIDENT’;

– 03.查询佣金（COMM）为0或为NULL的员工信息  
SELECT \* from emp where comm=0 or comm is null;

– 04.查询入职日期在1982-5-1到1981-12-31之间的所有员工信息  
SELECT \* from emp WHERE TO\_DAYS(hiredate) <=TO\_DAYS(‘1982-05-01’) and TO\_DAYS(hiredate) >=TO\_DAYS(‘1981-12-31’);

– 05.查询所有名字长度为4的员工的员工编号，姓名  
SELECT \* from emp WHERE LENGTH(ename)=4;

– 06.显示10号部门的所有经理（‘MANAGER’）和20号部门的所有职员（‘CLERK’）的详细信息  
SELECT \* FROM emp where (deptno=10 and job=‘MANAGER’) or (deptno=20 and job=‘CLERK’);

– 07.显示姓名中没有’L’字的员工的详细信息或含有’SM’字的员工信息  
select \* from emp where ename not like ‘%L%’ and ename NOT like ‘%L’ and ename not like ‘L%’ or ename like ‘%SM%’;

– 08.显示各个部门经理（‘MANAGER’）的工资  
SELECT ename ,sal from emp WHERE job =‘MANAGER’;

– 09.显示佣金（COMM）收入比工资（SAL）高的员工的详细信息  
SELECT \* from emp WHERE comm>sal;

– 10.把hiredate列看做是员工的生日，求本月过生日的员工（考察知识点：单行函数）  
SELECT ename ,hiredate from emp where MONTH(hiredate)=MONTH(NOW());

– 11.把hiredate列看做是员工的生日，求下月过生日的员工（考察知识点：单行函数）  
SELECT ename ,hiredate from emp where MONTH(hiredate)=IF(MONTH(NOW())=12,0,12)+1;

– 12.求1982年入职的员工（考察知识点：单行函数）  
SELECT \* from emp WHERE YEAR(hiredate)=1982;

– 13.求1981年下半年入职的员工（考察知识点：单行函数）  
SELECT \* from emp WHERE YEAR(hiredate)=1981 and MONTH(hiredate)>6;

– 14.求1981年各个月入职的员工个数（考察知识点：组函数）  
SELECT mon ,COUNT(mon) sum from (SELECT MONTH(hiredate) mon from emp WHERE YEAR(hiredate)=1981) c GROUP BY mon;

– 15.查询各个部门的平均工资  
SELECT deptno,avg(sal) from emp GROUP BY deptno;

– 16.显示各种职位的最低工资  
SELECT job,MIN(sal) from emp GROUP BY job;

– 17.按照入职日期由新到旧排列员工信息  
SELECT \* from emp ORDER BY hiredate desc;

– 18.查询员工的基本信息，附加其上级的姓名  
SELECT a.\*,b.ename from emp a LEFT JOIN emp b on a.mgr=b.empno;

– 19.显示工资比’ALLEN’高的所有员工的姓名和工资  
SELECT ename,sal from emp WHERE sal>(SELECT sal from emp WHERE ename=‘ALLEN’);

– 20.显示与’SCOTT’从事相同工作的员工的详细信息  
SELECT \* from emp WHERE job=(SELECT job from emp WHERE ename=‘SCOTT’);

– 21.显示销售部（‘SALESMAN’）员工的姓名  
SELECT ename from emp WHERE job=‘SALESMAN’;

– 22.显示与30号部门’MARTIN’员工工资相同的员工的姓名和工资  
SELECT ename,sal from emp WHERE sal=(SELECT sal from emp WHERE deptno=30 AND job=‘MARTIN’);

– 23.查询所有工资高于平均工资的销售人员（‘SALESMAN’）  
SELECT \* from emp WHERE job=‘SALESMAN’ and sal>(SELECT avg(sal) from emp);

– 24.显示所有职员的姓名及其所在部门的名称和工资  
SELECT a.ename ,b.dname ,a.sal from emp a LEFT JOIN dept b on a.deptno = b.deptno;

– 25查询在研发部（‘RESEARCH’）工作员工的编号，姓名，工作部门，工作所在地  
SELECT a.empno,a.ename,b.dname,b.loc from emp a LEFT JOIN dept b on a.deptno=b.deptno WHERE b.dname=‘RESEARCH’;

– 26.查询各个部门的名称和员工人数  
SELECT b.dname,COUNT(\*) from emp a RIGHT JOIN dept b on a.deptno=b.deptno GROUP BY a.deptno;

– 27.查询各个职位员工工资大于平均工资（平均工资包括所有员工）的人数和员工职位  
SELECT job,IFNULL(COUNT(\*),0) sum from emp WHERE sal>(SELECT avg(sal) from emp) GROUP BY job;

– 28.查询工资相同的员工的工资和姓名  
SELECT a.sal, a.ename from emp a ,emp b WHERE a.sal=b.sal and a.ename!=b.ename;

– 29.查询工资最高的3名员工信息  
SELECT \* from emp ORDER BY sal DESC LIMIT 0,3;

– 30.按工资进行排名，排名从1开始，工资相同排名相同（如果两人并列第1则没有第2名，从第3名继续排）  
SELECT a.ename,COUNT(*) from emp a, emp b WHERE a.sal<b.sal GROUP BY a.ename ORDER BY COUNT(*);  
– 31.求入职日期相同的（年月日相同）的员工  
SELECT a.hiredate,b.ename from emp a,emp b WHERE a.hiredate=b.hiredate and a.ename!=b.ename;

– 32.查询每个部门的最高工资  
SELECT a.deptno,b.dname,IFNULL(max(a.sal),0) from emp a RIGHT JOIN dept b on a.deptno=b.deptno ORDER BY ;

– 33.查询每个部门，每种职位的最高工资  
SELECT b.dname,a.job,max(a.sal) FROM emp a RIGHT JOIN dept b on a.deptno = b.deptno GROUP BY b.dname,a.job;

– 34.查询每个部门有多少人  
SELECT b.dname,COUNT(ename) from emp a RIGHT JOIN dept b on a.deptno = b.deptno GROUP BY dname;

– 35.查询出king所在部门的部门号\\部门名称\\部门人数  
select a.deptno,dname,count(\*) from emp a ,dept b where a.deptno=b.deptno  
and a.deptno=(  
select deptno from emp where  
ename =‘KING’  
)  
group by a.deptno

– 36.查询出king所在部门的工作年限最大的员工名字  
select ename,hiredate from emp where deptno =  
(  
select deptno from emp where ename=‘KING’  
) order by hiredate limit 0,1

– 37.查询出管理员工人数最多的人的名字和他管理的人的名字  
select count(\*) num ,ms\_name from (  
select a.ename , b.ename ms\_name from emp a ,emp b where a.mgr=b.empno  
) a group by ms\_name order by num desc limit 0,1

– 38.查询出工资成本最高的部门的部门号和部门名称  
select deptno ,dname from dept where deptno = (select deptno from emp group by deptno order by sum(sal) desc limit 0,1)

– 39.查询出工资不超过2500的人数最多的部门名称  
select deptno ,dname from dept where deptno =(select deptno from emp where sal<=2500 group by deptno order by count(1) limit 0,1)

– 40.查询出没有下属员工的人的名字和他的职位  
select ename ,job from emp where empno not in (select distinct mgr empno from emp where mgr is not null )  
select ename ,job from emp a where not exists (select empno from emp b where a.empno=b.mgr)  
–41.现在公司要给员工增加工龄工资,规则是:30\*工作年限,请按以下格式显示下面结果  
部门名称 员工姓名 原工资 增加额度 新工资  
select ename,sal, sal+timestampdiff(year,hiredate,now())\*30 newSal,  
timestampdiff(year,hiredate,now())\*30 addNUm from emp  
– 42.针对dept和emp表,查询出下面格式的结果.(要求使用外联接,没有员工的部门名也要显示示,员工姓名如果是空值,要求用"不存在代替;如果工资是空值,要求用0代替")  
部门名称 员工姓名 工资  
select dname,ifnull(ename,‘not exists’)ename ,ifnull(sal,‘not exists’)sal from emp a right join dept b on a.deptno=b.deptno  
– 43.查询出工资高于全体平均工资人数最多的部门编号,部门名称和员工姓名,工资.  
select a.deptno,dname,ename,sal from emp a left join dept b on  
a.deptno=b.deptno  
where a.deptno=(  
select deptno from emp where sal>(select avg(sal) from emp) group by deptno order by count(1) desc limit 0,1  
)