MySQL数据库练习【一】 @TOC一、建库建表-数据准备create database company; use company; -- 创建部门表 DROP TABLE IF EXISTS dept; CREATE TABLE `dept` ( `deptno` int(11) PRIMARY KEY NOT NULL COMMENT '部门编号', `dname` varchar(14) NOT NULL COMMENT '部门名称', `loc` varchar(13) NOT NULL COMMENT '部门所在地' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表'; -- 向部门表插入数据 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'), (20,'RESEARCH','DALLAS'), (30,'SALES','CHICAGO'), (40,'OPERATIONS','BOSTON'); -- 创建员工表 DROP TABLE IF EXISTS emp; CREATE TABLE `emp` ( `empno` int(11) PRIMARY KEY NOT NULL COMMENT '员工编号', `ename` varchar(10) DEFAULT NULL COMMENT '员工姓名', `job` varchar(9) DEFAULT NULL COMMENT '工作岗位', `mgr` int(11) DEFAULT NULL COMMENT '直属领导', `hiredate` date DEFAULT NULL COMMENT '入职时间', `sal` double DEFAULT NULL COMMENT '工资', `comm` double DEFAULT NULL COMMENT '奖金', `deptno` int(11) DEFAULT NULL COMMENT '所属部门' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'; -- 向员工表插入数据 INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20), (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30), (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30), (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20), (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30), (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30), (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10), (7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20), (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10), (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30), (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20), (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30), (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20), (7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10); -- 创建工资等级表 DROP TABLE IF EXISTS salgrade; CREATE TABLE `salgrade` ( `grade` int(11) DEFAULT NULL COMMENT '等级', `losal` double DEFAULT NULL COMMENT '最低工资', `hisal` double DEFAULT NULL COMMENT '最高工资' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资等级表'; -- 向工资等级表插入数据 INSERT INTO salgrade VALUES (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);-- 为员工表表添加外键约束 -- ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY EMP(deptno) REFERENCES dept (deptno);员工表emp部门表dept工资等级表salgradeempno：员工编号deptno：部门编号grade：等级ename：员工姓名dname：部门名称losal：最低工资job：工作岗位loc：部门所在地hisal：最高工资mgr：直属领导 hiredate：入职时间 sal：工资 comm：奖金 deptno：所属部门 empnoenamejobmgrhiredatesalcommdeptno7369SMITHCLERK79021980-12-17800NULL207499ALLENSALESMAN76981981-02-201600300307521WARDSALESMAN76981981-02-221250500307566JONESMANAGER78391981-04-022975NULL207654MARTINSALESMAN76981981-09-2812501400307698BLAKEMANAGER78391981-05-012850NULL307782CLARKMANAGER78391981-06-092450NULL107788SCOTTANALYST75661987-07-033000NULL207839KINGPRESIDENTNULL1981-11-175000NULL107844TURNERSALESMAN76981981-09-0815000307876ADAMSCLERK77881987-07-131100NULL207900JAMESCLERK76981981-12-03950NULL307902FORDANALYST75661981-12-033000NULL207934MILLERCLERK77821981-01-231300NULL10deptnodnameloc10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONgradelosalhisal17001200212011400314012000420013000530019999二、习题2.1.查询部门编号为30的部门的员工详细信息 2.2.查询从事clerk工作的员工的编号、姓名以及其部门号 2.3.查询奖金多于基本工资的员工的信息、查询奖金小于基本工资的员工的信息 2.4.查询奖金多于基本工资60%的员工的信息 2.5.查询部门编号为10的部门经理 (job=manager) 2.6.查询部门编号为20的在部门中工作为CLERK的职员信息 2.7.查询部门编号为10的部门经理 (job=manager)或部门编号为20的部门工作为CLERK的职员信息 2.8.查询部门编号为10的部门经理或部门编号为20的部门工作为CLERK的职员信息或者既不是经理也不是CLERK但是工资高于2000的员工信息 2.9.查询获得奖金的员工的信息 2.10.查询奖金少于100或者没有获得奖金的员工的信息 2.11.MySQL LEFT函数【语法】 2.12.查询姓名以A、B、S开头的员工的信息【LEFT函数】 2.13.查询找到姓名长度为6个字符的员工的信息【length函数】 2.14.查询姓名中不包含R字符的员工信息【not like】 2.15.查询员工的详细信息并按姓名排序【asc、desc】 2.16.查询员工的信息并按工作岗位降序、工资升序排列 2.17.计算员工的日薪(按30天计)【ROUND】 2.18.查询姓名中包含字符A的员工的信息 2.19.查询拥有员工的部门的部门名称和部门编号【自连接 inner join】 2.20.查询工资多于smith的员工信息 2.21.查询员工和及其所属经理的姓名(同一个部门，job=manager) 2.22.DATEDIFF函数与TIMESTAMPDIFF函数【语法】 2.23.查询员工雇佣日期早于其经理雇佣日期的员工及其经理姓名 2.24.查询员工姓名及其所在的部门名称 2.25.查询工作为clerk的员工姓名及其所在部门名称 2.26.查询各部门编号及其部门中的最低工资【group by】 2.27.查询销售部sales的所有员工的姓名 2.28.子查询【语法】 2.29.列出薪金高于公司平均薪金的所有员工，要求使用子查询实现【子查询】 2.29.查询工资比编号为7566雇员工资高的雇员姓名 2.30.显示和雇员scott同部门的雇员姓名、工资和部门编号 2.31.显示和雇员SCOTT从事相同工作，并且工资大于JAMES的雇员姓名、工作岗位和工资 2.32.显示工资最低的雇员姓名、工作和工资 2.33.显示工资最高的雇员姓名、工作和工资 2.34.显示工资高于平均工资的雇员姓名、工作、工资和工资等级 2.35.查询部门内的最低工资比20号部门的最低工资要高的部门的编号及部门内最低工资【having】 2.36.查询和SMITH从事相同工作的雇员姓名和工作 2.37.查询有下属的雇员姓名、工作、工资、部门号 2.38.查询没有下属的雇员姓名、工作、工资和部门号 2.39.查询各部门中工资最低的员工姓名、工作、工资和部门号 2.40.查询部门中工资最高的员工姓名、工作、工资和部门号 2.41.查询与销售部门（SALES）工作岗位相同的其它部门雇员姓名、工作、工资和部门名称 2.42.使用any运算符的多行子查询【语法】 2.43.查询工资低于某个文员（CLERK）雇员工资，但不从事文员工作的员工编号、姓名、工作岗位和工资 2.44.查询工资高于某个文员（CLERK）雇员工资，但不从事文员工作的员工编号、姓名、工作岗位和工资 2.45.查询工资高于部门编号是30的部门内某个雇员工资，但不在该部门工作的员工姓名、工作岗位、工资和部门编号 2.46.查询工资低于部门名称是SALES的部门内某个雇员工资，但不在该部门工作的员工姓名、工作岗位、工资和部门编号和部门名称 2.47.使用all运算符的多行子查询【语法】 2.48.查询高于所有部门平均工资的雇员姓名、工作、工资和部门编号 2.49.查询低于所有部门平均工资的雇员姓名、工作、工资和部门编号 2.50.查询工资高于部门编号为30的部门内所有员工工资的雇员姓名、工作、工资和部门编号 2.51.查询工资等级为4的雇员姓名、工作、工资、部门编号和工资等级，同时满足该雇员工资高于部门编号为30的部门内所有员工工资 2.52.查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门号 2.53.查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门名称 2.54.查询工资与30部门中任意一个雇员的工资相等，同时奖金也与30部门中任意一个雇员奖金相等的雇员姓名、工资、奖金、部门编号，但该雇员不是来自30号部门 2.55.查询在纽约（NEW YORK）工作的雇员姓名、工作岗位、工资和奖金 2.56.显示工资高于部门平均工资的雇员姓名、工作、工资和部门号 2.57.查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门号 2.58.查询平均工资最低的工种名称及其平均工资 2.59.查询与部门编号为30的员工工资水平相同的员工信息 2.60.查询部门号、部门名、部门所在位置及其每个部门的员工总数 2.61.计算员工的年薪并且以年薪排序2.1. 查询部门编号为30的部门的员工详细信息mysql> select * from emp where deptno=30; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | +-------+--------+----------+------+------------+------+------+--------+2.2.查询从事clerk工作的员工的编号、姓名以及其部门号mysql> select empno,ename,deptno from emp where job='clerk'; +-------+--------+--------+ | empno | ename | deptno | +-------+--------+--------+ | 7369 | SMITH | 20 | | 7876 | ADAMS | 20 | | 7900 | JAMES | 30 | | 7934 | MILLER | 10 | +-------+--------+--------+ 4 rows in set (0.00 sec)2.3.查询奖金多于基本工资的员工的信息、查询奖金小于基本工资的员工的信息mysql> select * from emp where comm > sal; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | +-------+--------+----------+------+------------+------+------+--------+ 1 row in set (0.00 sec) mysql> select * from emp where comm is null or comm < sal; +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | +-------+--------+-----------+------+------------+------+------+--------+ 13 rows in set (0.00 sec)2.4.查询奖金多于基本工资60%的员工的信息mysql> select * from emp where comm > sal * 0.6; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | +-------+--------+----------+------+------------+------+------+--------+ 1 row in set (0.00 sec)2.5.查询部门编号为10的部门经理 (job=manager)mysql> select * from emp where deptno = 10 and job = 'manager'; +-------+-------+---------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+---------+------+------------+------+------+--------+ | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | +-------+-------+---------+------+------------+------+------+--------+ 1 row in set (0.00 sec)2.6.查询部门编号为20的在部门中工作为CLERK的职员信息mysql> select * from emp where deptno = 20 and job = 'clerk'; +-------+-------+-------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | +-------+-------+-------+------+------------+------+------+--------+ 2 rows in set (0.00 sec)2.7.查询部门编号为10的部门经理 (job=manager)或部门编号为20的部门工作为CLERK的职员信息mysql> select * from emp where (deptno = 10 and job = 'manager') or (deptno = 20 and job = 'clerk'); +-------+-------+---------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+---------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | +-------+-------+---------+------+------------+------+------+--------+ 3 rows in set (0.00 sec)2.8.查询部门编号为10的部门经理或部门编号为20的部门工作为CLERK的职员信息或者既不是经理也不是CLERK但是工资高于2000的员工信息mysql> select * from emp where (deptno = 10 and job = 'manager') -> or (deptno = 20 and job = 'clerk') -> or (job not in ('manager','clerk') AND sal > 2000); +-------+-------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-----------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | +-------+-------+-----------+------+------------+------+------+--------+ 6 rows in set (0.00 sec)2.9.查询获得奖金的员工的信息mysql> select * from emp where comm is not null and comm > 0; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | +-------+--------+----------+------+------------+------+------+--------+ 3 rows in set (0.00 sec)2.10.查询奖金少于100或者没有获得奖金的员工的信息mysql> select * from emp where comm is null or comm < 100; +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | +-------+--------+-----------+------+------------+------+------+--------+ 11 rows in set (0.00 sec)2.11.MySQL LEFT函数【语法】LEFT()函数是一个字符串函数，它返回具有指定长度的字符串的左边部分。LEFT()函数的语法LEFT(str,length);LEFT()函数接受两个参数：str是要提取子字符串的字符串。 length是一个正整数，指定将从左边返回的字符数。 LEFT()函数返回str字符串中最左边的长度字符。如果str或length参数为NULL，则返回NULL值。如果length为0或为负，则LEFT函数返回一个空字符串。如果length大于str字符串的长度，则LEFT函数返回整个str字符串。mysql> SELECT LEFT('MySQL LEFT', 5); +-----------------------+ | LEFT('MySQL LEFT', 5) | +-----------------------+ | MySQL | +-----------------------+ 1 row in set (0.00 sec)2.12.查询姓名以A、B、S开头的员工的信息【LEFT函数】LEFT(ename,1) 表示取名字的第一个字符 IN ('A','B','C') 取出姓名第一个字符与之相匹配的员工信息mysql> select * from emp where ename like 'A%' or ename like 'B%' or ename like 'C%'; +-------+-------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+----------+------+------------+------+------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | +-------+-------+----------+------+------------+------+------+--------+ 4 rows in set (0.01 sec) mysql> select * from emp WHERE LEFT(ename,1) IN ('A','B','C'); +-------+-------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+----------+------+------------+------+------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | +-------+-------+----------+------+------------+------+------+--------+ 4 rows in set (0.00 sec)2.13.查询找到姓名长度为6个字符的员工的信息【length函数】mysql> select * from emp where length(ename) = 6; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | +-------+--------+----------+------+------------+------+------+--------+ 3 rows in set (0.00 sec)2.14.查询姓名中不包含R字符的员工信息【not like】mysql> select * from emp where ename not like '%R%'; +-------+-------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-----------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | +-------+-------+-----------+------+------------+------+------+--------+ 8 rows in set (0.00 sec) mysql> select * from emp where ename like '%R%'; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | +-------+--------+----------+------+------------+------+------+--------+ 6 rows in set (0.00 sec)2.15.查询员工的详细信息并按姓名排序【asc、desc】mysql> select * from emp order by ename asc; +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | +-------+--------+-----------+------+------------+------+------+--------+ 14 rows in set (0.00 sec) mysql> select * from emp order by ename desc; +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | +-------+--------+-----------+------+------------+------+------+--------+ 14 rows in set (0.00 sec)2.16.查询员工的信息并按工作岗位降序、工资升序排列mysql> select * from emp order by job desc,sal asc; +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | +-------+--------+-----------+------+------------+------+------+--------+ 14 rows in set (0.00 sec)2.17.计算员工的日薪(按30天计)【ROUND】mysql round()函数的第二个参数表示需要保留的小数位数，如果不指定第二个参数，则默认保留零位小数。如果第二个参数为负数，则会将数字整数部分进行四舍五入。ROUND(sal/30,2)，2表示小数点后面两位后面的数进行四舍五入ROUND(sal/30,-1) ，-1表示小数点前面一位例如ROUND(316.15926,-1)，小数点的前面一位6前面的2保留，然后6进1，结果为320mysql> select ROUND(314.15926,2); +--------------------+ | ROUND(314.15926,2) | +--------------------+ | 314.16 | +--------------------+ 1 row in set (0.00 sec) mysql> select ROUND(314.15926,-1); +---------------------+ | ROUND(314.15926,-1) | +---------------------+ | 310 | +---------------------+ 1 row in set (0.00 sec) mysql> select ROUND(316.15926,-1); +---------------------+ | ROUND(316.15926,-1) | +---------------------+ | 320 | +---------------------+ 1 row in set (0.00 sec) mysql> select ename 员工姓名,sal 月薪,ROUND(sal/30,2) 日薪 from emp; +--------------+--------+--------+ | 员工姓名 | 月薪 | 日薪 | +--------------+--------+--------+ | SMITH | 800 | 26.67 | | ALLEN | 1600 | 53.33 | | WARD | 1250 | 41.67 | | JONES | 2975 | 99.17 | | MARTIN | 1250 | 41.67 | | BLAKE | 2850 | 95.00 | | CLARK | 2450 | 81.67 | | SCOTT | 3000 | 100.00 | | KING | 5000 | 166.67 | | TURNER | 1500 | 50.00 | | ADAMS | 1100 | 36.67 | | JAMES | 950 | 31.67 | | FORD | 3000 | 100.00 | | MILLER | 1300 | 43.33 | +--------------+--------+--------+ 14 rows in set (0.00 sec)2.18.查询姓名中包含字符A的员工的信息mysql> select * from emp where ename like '%A%'; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | +-------+--------+----------+------+------------+------+------+--------+ 7 rows in set (0.00 sec)2.19.查询拥有员工的部门的部门名称和部门编号【自连接 inner join】-- 查询拥有员工的部门的部门名称和部门编号【自连接 inner join】 -- INNER JOIN ... ON ... -- 表结构: emp,dept -- 连接条件: emp.deptno = dept.deptno select distinct dname 部门名称,dept.deptno 部门编号 from dept inner join emp on dept.deptno = emp.deptno;mysql> select distinct dname 部门名称,dept.deptno 部门编号 -> from dept -> inner join emp -> on dept.deptno = emp.deptno; +--------------+--------------+ | 部门名称 | 部门编号 | +--------------+--------------+ | RESEARCH | 20 | | SALES | 30 | | ACCOUNTING | 10 | +--------------+--------------+ 3 rows in set (0.00 sec)2.20.查询工资多于smith的员工信息mysql> select * from emp where ename = 'smith'; +-------+-------+-------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | +-------+-------+-------+------+------------+------+------+--------+ 1 row in set (0.00 sec) mysql> select * from emp where sal > (select sal from emp where ename = 'smith'); +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | +-------+--------+-----------+------+------------+------+------+--------+ 13 rows in set (0.00 sec)2.21.查询员工和及其所属经理的姓名(同一个部门，job=manager)-- 查询员工和及其所属经理的姓名(同一个部门，job=manager) -- INNER JOIN ... ON ... -- 表结构: emp e,emp t -- 连接条件: e.deptno = t.deptno -- where: e.job!='manager' and t.job='manager' select e.*,t.ename 经理姓名 from emp e inner join emp t on e.deptno = t.deptno where e.job!='manager' and t.job='manager' order by e.empno asc;mysql> select e.*,t.ename 经理姓名 -> from emp e -> inner join emp t -> on e.deptno = t.deptno -> where e.job!='manager' and t.job='manager' -> order by e.empno asc; +-------+--------+-----------+------+------------+------+------+--------+--------------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | 经理姓名 | +-------+--------+-----------+------+------------+------+------+--------+--------------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | JONES | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | BLAKE | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | BLAKE | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | BLAKE | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | JONES | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | CLARK | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | BLAKE | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | JONES | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | BLAKE | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | JONES | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | CLARK | +-------+--------+-----------+------+------------+------+------+--------+--------------+ 11 rows in set (0.00 sec)2.22.DATEDIFF函数与TIMESTAMPDIFF函数【语法】MySQL DATEDIFF()函数是MySQL中常见的日期函数之一，它主要用于计算两个日期之间的差值，单位可以是天、周、月、季度和年。DATEDIFF(date1, date2)其中，date1和date2是两个日期，可以是日期型、时间型或时间戳型数据。-- 1 SELECT DATEDIFF('2022-04-30','2022-04-29'); -- 0 SELECT DATEDIFF('2022-04-30','2022-04-30'); -- -1 SELECT DATEDIFF('2022-04-29','2022-04-30'); -- 0 SELECT DATEDIFF('2022-04-30','2022-04-30 14:00:00'); -- 1 SELECT DATEDIFF('2022-04-30 13:00:00','2022-04-29 14:00:00'); -- 10 SELECT DATEDIFF('2017-06-25 09:34:21', '2017-06-15'); -- -2 SELECT DATEDIFF('2022-04-28', '2022-04-30'); -- 2 SELECT TIMESTAMPDIFF(DAY,'2022-04-28', '2022-04-30');DATEDIFF函数与TIMESTAMPDIFF函数的区别DATEDIFF函数仅用于返回两个日期的天数，TIMESTAMPDIFF函数用于返回计算两个日期指定单位的时间差(指定单位可以是年，季度，月，星期，天数，小时，分钟，秒等等)对日期差值的计算方式相反DATEDIFF函数的语法格式: DATEDIFF(start,end)DATEDIFF函数返回start - end的计算结果TIMESTAMPDIFF函数的语法格式: TIMESTAMPDIFF(DAY,start,end)TIMESTAMPDIFF函数返回end - start的计算结果2.23.查询员工雇佣日期早于其经理雇佣日期的员工及其经理姓名-- 查询雇佣日期早于其经理雇佣日期的员工及其经理姓名 -- INNER JOIN ... ON ... -- 表结构: emp e,emp t -- 连接条件: e.deptno = t.deptno -- where条件: e.job!='manager' and t.job='manager' and datediff(e.hiredate,t.hiredate)<0 select e.ename 员工姓名,e.hiredate 员工雇佣日期,t.ename 经理姓名,t.hiredate 经理雇佣日期 from emp e inner join emp t on e.deptno = t.deptno where e.job!='manager' and t.job='manager' and datediff(e.hiredate,t.hiredate)<0;mysql> select e.ename 员工姓名,e.hiredate 员工雇佣日期,t.ename 经理姓名,t.hiredate 经理雇佣日期 -> from emp e -> inner join emp t -> on e.deptno = t.deptno -> where e.job!='manager' and t.job='manager' and datediff(e.hiredate,t.hiredate)<0; +--------------+--------------------+--------------+--------------------+ | 员工姓名 | 员工雇佣日期 | 经理姓名 | 经理雇佣日期 | +--------------+--------------------+--------------+--------------------+ | SMITH | 1980-12-17 | JONES | 1981-04-02 | | ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | | WARD | 1981-02-22 | BLAKE | 1981-05-01 | | MILLER | 1981-01-23 | CLARK | 1981-06-09 | +--------------+--------------------+--------------+--------------------+ 4 rows in set (0.00 sec)2.24.查询员工姓名及其所在的部门名称-- 查询员工姓名及其所在的部门名称 -- INNER JOIN ... ON ... -- 表结构: emp e,dept d -- 连接条件: e.deptno = d.deptno select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno; select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;mysql> select e.ename,d.dname -> from emp e -> inner join dept d -> on e.deptno = d.deptno; +--------+------------+ | ename | dname | +--------+------------+ | SMITH | RESEARCH | | ALLEN | SALES | | WARD | SALES | | JONES | RESEARCH | | MARTIN | SALES | | BLAKE | SALES | | CLARK | ACCOUNTING | | SCOTT | RESEARCH | | KING | ACCOUNTING | | TURNER | SALES | | ADAMS | RESEARCH | | JAMES | SALES | | FORD | RESEARCH | | MILLER | ACCOUNTING | +--------+------------+ 14 rows in set (0.00 sec)2.25.查询工作为clerk的员工姓名及其所在部门名称-- 查询工作为clerk的员工姓名及其所在部门名称 -- INNER JOIN ... ON ... -- 表结构: emp e,dept d -- 连接条件: e.deptno = d.deptno -- where条件: e.job = 'clerk' select e.ename 员工姓名,d.dname 部门名称 from emp e inner join dept d on e.deptno = d.deptno where e.job = 'clerk';mysql> select e.ename 员工姓名,d.dname 部门名称 -> from emp e -> inner join dept d -> on e.deptno = d.deptno -> where e.job = 'clerk'; +--------------+--------------+ | 员工姓名 | 部门名称 | +--------------+--------------+ | SMITH | RESEARCH | | ADAMS | RESEARCH | | JAMES | SALES | | MILLER | ACCOUNTING | +--------------+--------------+ 4 rows in set (0.00 sec)2.26.查询各部门编号及其部门中的最低工资【group by】mysql> select deptno,min(sal) from emp group by deptno; +--------+----------+ | deptno | min(sal) | +--------+----------+ | 10 | 1300 | | 20 | 800 | | 30 | 950 | +--------+----------+ 3 rows in set (0.01 sec)2.27.查询销售部sales的所有员工的姓名-- 查询销售部sales的所有员工的姓名 -- INNER JOIN ... ON ... -- 表结构: emp e,dept d -- 连接条件: e.deptno = d.deptno -- where条件: d.dname = 'sales' select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno where d.dname = 'sales';mysql> select e.ename,d.dname -> from emp e -> inner join dept d -> on e.deptno = d.deptno -> where d.dname = 'sales'; +--------+-------+ | ename | dname | +--------+-------+ | ALLEN | SALES | | WARD | SALES | | MARTIN | SALES | | BLAKE | SALES | | TURNER | SALES | | JAMES | SALES | +--------+-------+ 6 rows in set (0.00 sec)2.28.子查询【语法】子查询是嵌套在一个select语句中的另一个select语句。当需要从一个表中检索信息，检索条件值又是来自该表本身的内部数据时，子查询非常有用。子查询可以嵌入以下SQL子句中：where子句、having子句和from子句。where子句使用单行子查询返回结果要采用单行比较运算符（=、>、>=、<、<=、<>）说明：子查询要用括号括起来；将子查询放在比较运算符的右边；不要在子查询中使用order by子句，select语句中只能有一个order by子句，并且它只能是主select语句的最后一个子句。2.29.列出薪金高于公司平均薪金的所有员工，要求使用子查询实现【子查询】mysql> select * from emp where sal > (select avg(sal) from emp); +-------+-------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-----------+------+------------+------+------+--------+ | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | +-------+-------+-----------+------+------------+------+------+--------+ 6 rows in set (0.00 sec)2.29.查询工资比编号为7566雇员工资高的雇员姓名select ename from emp where sal > (select sal from emp where empno = 7566) order by ename;mysql> select ename -> from emp -> where sal > (select sal from emp where empno = 7566) -> order by ename; +-------+ | ename | +-------+ | FORD | | KING | | SCOTT | +-------+ 3 rows in set (0.00 sec)2.30.显示和雇员scott同部门的雇员姓名、工资和部门编号select ename,sal,deptno from emp where deptno = (select deptno from emp where ename = 'scott');mysql> select ename,sal,deptno -> from emp -> where deptno = (select deptno from emp where ename = 'scott'); +-------+------+--------+ | ename | sal | deptno | +-------+------+--------+ | SMITH | 800 | 20 | | JONES | 2975 | 20 | | SCOTT | 3000 | 20 | | ADAMS | 1100 | 20 | | FORD | 3000 | 20 | +-------+------+--------+ 5 rows in set (0.00 sec)2.31.显示和雇员SCOTT从事相同工作，并且工资大于JAMES的雇员姓名、工作岗位和工资select ename,job,sal from emp where job = (select job from emp where ename = 'scott') and sal > (select sal from emp where ename = 'james');mysql> select ename,job,sal -> from emp -> where job = (select job from emp where ename = 'scott') -> and sal > (select sal from emp where ename = 'james'); +-------+---------+------+ | ename | job | sal | +-------+---------+------+ | SCOTT | ANALYST | 3000 | | FORD | ANALYST | 3000 | +-------+---------+------+ 2 rows in set (0.00 sec)2.32.显示工资最低的雇员姓名、工作和工资select ename,job,sal from emp where sal = (select min(sal) from emp);mysql> select ename,job,sal -> from emp -> where sal = (select min(sal) from emp); +-------+-------+------+ | ename | job | sal | +-------+-------+------+ | SMITH | CLERK | 800 | +-------+-------+------+ 1 row in set (0.00 sec)2.33.显示工资最高的雇员姓名、工作和工资select ename,job,sal from emp where sal = (select max(sal) from emp);mysql> select ename,job,sal -> from emp -> where sal = (select max(sal) from emp); +-------+-----------+------+ | ename | job | sal | +-------+-----------+------+ | KING | PRESIDENT | 5000 | +-------+-----------+------+ 1 row in set (0.01 sec)2.34.显示工资高于平均工资的雇员姓名、工作、工资和工资等级select e.ename,e.job,e.sal,s.grade from emp e,salgrade s where e.sal > (select avg(sal) from emp) and e.sal between s.losal and s.hisal;mysql> select e.ename,e.job,e.sal,s.grade -> from emp e,salgrade s -> where e.sal > (select avg(sal) from emp) -> and e.sal between s.losal and s.hisal; +-------+-----------+------+-------+ | ename | job | sal | grade | +-------+-----------+------+-------+ | JONES | MANAGER | 2975 | 4 | | BLAKE | MANAGER | 2850 | 4 | | CLARK | MANAGER | 2450 | 4 | | SCOTT | ANALYST | 3000 | 4 | | FORD | ANALYST | 3000 | 4 | | KING | PRESIDENT | 5000 | 5 | +-------+-----------+------+-------+ 6 rows in set (0.00 sec)2.35.查询部门内的最低工资比20号部门的最低工资要高的部门的编号及部门内最低工资【having】-- 按部门显示部门编号、部门最低工资 select deptno 部门编号,min(sal) 最低工资 from emp group by deptno; select deptno 部门编号,min(sal) 最低工资 from emp group by deptno having min(sal)>(select min(sal) from emp where deptno = 20);mysql> select deptno 部门编号,min(sal) 最低工资 -> from emp -> group by deptno; +--------------+--------------+ | 部门编号 | 最低工资 | +--------------+--------------+ | 10 | 1300 | | 20 | 800 | | 30 | 950 | +--------------+--------------+ 3 rows in set (0.00 sec) mysql> select deptno 部门编号,min(sal) 最低工资 -> from emp -> group by deptno -> having min(sal)>(select min(sal) from emp where deptno = 20); +--------------+--------------+ | 部门编号 | 最低工资 | +--------------+--------------+ | 10 | 1300 | | 30 | 950 | +--------------+--------------+ 2 rows in set (0.00 sec)2.36.查询和SMITH从事相同工作的雇员姓名和工作select ename,job from emp where job = (select job from emp where ename = 'smith');mysql> select ename,job -> from emp -> where job = (select job from emp where ename = 'smith'); +--------+-------+ | ename | job | +--------+-------+ | SMITH | CLERK | | ADAMS | CLERK | | JAMES | CLERK | | MILLER | CLERK | +--------+-------+ 4 rows in set (0.00 sec)2.37.查询有下属的雇员姓名、工作、工资、部门号select ename,job,sal,deptno,mgr from emp where empno in (select mgr from emp where mgr is not NULL);mysql> select ename,job,sal,deptno,mgr -> from emp -> where empno in (select mgr from emp where mgr is not NULL); +-------+-----------+------+--------+------+ | ename | job | sal | deptno | mgr | +-------+-----------+------+--------+------+ | FORD | ANALYST | 3000 | 20 | 7566 | | BLAKE | MANAGER | 2850 | 30 | 7839 | | KING | PRESIDENT | 5000 | 10 | NULL | | JONES | MANAGER | 2975 | 20 | 7839 | | SCOTT | ANALYST | 3000 | 20 | 7566 | | CLARK | MANAGER | 2450 | 10 | 7839 | +-------+-----------+------+--------+------+ 6 rows in set (0.00 sec)2.38.查询没有下属的雇员姓名、工作、工资和部门号select ename,job,sal,deptno,mgr from emp where empno not in (select mgr from emp where mgr is not null);mysql> select ename,job,sal,deptno,mgr -> from emp -> where empno not in (select mgr from emp where mgr is not null); +--------+----------+------+--------+------+ | ename | job | sal | deptno | mgr | +--------+----------+------+--------+------+ | SMITH | CLERK | 800 | 20 | 7902 | | ALLEN | SALESMAN | 1600 | 30 | 7698 | | WARD | SALESMAN | 1250 | 30 | 7698 | | MARTIN | SALESMAN | 1250 | 30 | 7698 | | TURNER | SALESMAN | 1500 | 30 | 7698 | | ADAMS | CLERK | 1100 | 20 | 7788 | | JAMES | CLERK | 950 | 30 | 7698 | | MILLER | CLERK | 1300 | 10 | 7782 | +--------+----------+------+--------+------+2.39.查询各部门中工资最低的员工姓名、工作、工资和部门号select ename,job,sal,deptno from emp where sal in (select min(sal) from emp group by deptno);mysql> select ename,job,sal,deptno -> from emp -> where sal in (select min(sal) from emp group by deptno); +--------+-------+------+--------+ | ename | job | sal | deptno | +--------+-------+------+--------+ | SMITH | CLERK | 800 | 20 | | JAMES | CLERK | 950 | 30 | | MILLER | CLERK | 1300 | 10 | +--------+-------+------+--------+ 3 rows in set (0.01 sec)2.40.查询部门中工资最高的员工姓名、工作、工资和部门号select ename,job,sal,deptno from emp where sal in (select max(sal) from emp group by deptno);mysql> select ename,job,sal,deptno -> from emp -> where sal in (select max(sal) from emp group by deptno); +-------+-----------+------+--------+ | ename | job | sal | deptno | +-------+-----------+------+--------+ | BLAKE | MANAGER | 2850 | 30 | | SCOTT | ANALYST | 3000 | 20 | | KING | PRESIDENT | 5000 | 10 | | FORD | ANALYST | 3000 | 20 | +-------+-----------+------+--------+ 4 rows in set (0.00 sec)2.41.查询与销售部门（SALES）工作岗位相同的其它部门雇员姓名、工作、工资和部门名称select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno and d.dname<>'sales' and job in (select distinct e.job from emp e,dept d where e.deptno = d.deptno and d.dname='sales');mysql> select e.ename,e.job,e.sal,d.dname -> from emp e,dept d -> where e.deptno = d.deptno -> and d.dname<>'sales' -> and job in (select distinct e.job from emp e,dept d where e.deptno = d.deptno and d.dname='sales'); +--------+---------+------+------------+ | ename | job | sal | dname | +--------+---------+------+------------+ | SMITH | CLERK | 800 | RESEARCH | | JONES | MANAGER | 2975 | RESEARCH | | CLARK | MANAGER | 2450 | ACCOUNTING | | ADAMS | CLERK | 1100 | RESEARCH | | MILLER | CLERK | 1300 | ACCOUNTING | +--------+---------+------+------------+ 5 rows in set (0.00 sec)2.42.使用any运算符的多行子查询【语法】Any运算符将和内部查询返回的结果逐个比较，与单行操作符配合使用。<any：表示比子查询返回结果中的最大值小；=any：表示可以是子查询返回结果中的任意一个值；\>any：表示比子查询返回结果中的最小值大。2.43.查询工资低于某个文员（CLERK）雇员工资，但不从事文员工作的员工编号、姓名、工作岗位和工资select empno,ename,job,sal from emp where sal <any (select sal from emp where job = 'clerk') and job<>'clerk';mysql> select empno,ename,job,sal -> from emp -> where sal <any (select sal from emp where job = 'clerk') -> and job<>'clerk'; +-------+--------+----------+------+ | empno | ename | job | sal | +-------+--------+----------+------+ | 7521 | WARD | SALESMAN | 1250 | | 7654 | MARTIN | SALESMAN | 1250 | +-------+--------+----------+------+ 2 rows in set (0.00 sec)2.44.查询工资高于某个文员（CLERK）雇员工资，但不从事文员工作的员工编号、姓名、工作岗位和工资select empno,ename,job,sal from emp where sal >any (select sal from emp where job = 'clerk') and job<>'clerk';mysql> select empno,ename,job,sal -> from emp -> where sal >any (select sal from emp where job = 'clerk') -> and job<>'clerk'; +-------+--------+-----------+------+ | empno | ename | job | sal | +-------+--------+-----------+------+ | 7499 | ALLEN | SALESMAN | 1600 | | 7521 | WARD | SALESMAN | 1250 | | 7566 | JONES | MANAGER | 2975 | | 7654 | MARTIN | SALESMAN | 1250 | | 7698 | BLAKE | MANAGER | 2850 | | 7782 | CLARK | MANAGER | 2450 | | 7788 | SCOTT | ANALYST | 3000 | | 7839 | KING | PRESIDENT | 5000 | | 7844 | TURNER | SALESMAN | 1500 | | 7902 | FORD | ANALYST | 3000 | +-------+--------+-----------+------+ 10 rows in set (0.00 sec)2.45.查询工资高于部门编号是30的部门内某个雇员工资，但不在该部门工作的员工姓名、工作岗位、工资和部门编号select empno,ename,job,sal from emp where sal >any (select sal from emp where deptno = 30) and deptno<>30;mysql> select empno,ename,job,sal -> from emp -> where sal >any (select sal from emp where deptno = 30) -> and deptno<>30; +-------+--------+-----------+------+ | empno | ename | job | sal | +-------+--------+-----------+------+ | 7566 | JONES | MANAGER | 2975 | | 7782 | CLARK | MANAGER | 2450 | | 7788 | SCOTT | ANALYST | 3000 | | 7839 | KING | PRESIDENT | 5000 | | 7876 | ADAMS | CLERK | 1100 | | 7902 | FORD | ANALYST | 3000 | | 7934 | MILLER | CLERK | 1300 | +-------+--------+-----------+------+ 7 rows in set (0.00 sec)2.46.查询工资低于部门名称是SALES的部门内某个雇员工资，但不在该部门工作的员工姓名、工作岗位、工资和部门编号和部门名称select e.empno,e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno = d.deptno and d.dname<>'sales' and e.sal <any (select distinct e.sal from emp e,dept d where e.deptno = d.deptno and dname = 'sales');mysql> select e.empno,e.ename,e.job,e.sal,d.dname -> from emp e,dept d -> where e.deptno = d.deptno -> and d.dname<>'sales' -> and e.sal <any (select distinct e.sal from emp e,dept d where e.deptno = d.deptno and dname = 'sales'); +-------+--------+---------+------+------------+ | empno | ename | job | sal | dname | +-------+--------+---------+------+------------+ | 7369 | SMITH | CLERK | 800 | RESEARCH | | 7782 | CLARK | MANAGER | 2450 | ACCOUNTING | | 7876 | ADAMS | CLERK | 1100 | RESEARCH | | 7934 | MILLER | CLERK | 1300 | ACCOUNTING | +-------+--------+---------+------+------------+ 4 rows in set (0.00 sec)2.47.使用all运算符的多行子查询【语法】All运算符将和内部查询返回的每个结果比较。\>all：比最大的大；<all：比最小的小。2.48.查询高于所有部门平均工资的雇员姓名、工作、工资和部门编号select ename,job,sal,deptno from emp where sal >all (select avg(sal) from emp group by deptno);mysql> select ename,job,sal,deptno -> from emp -> where sal >all (select avg(sal) from emp group by deptno); +-------+-----------+------+--------+ | ename | job | sal | deptno | +-------+-----------+------+--------+ | JONES | MANAGER | 2975 | 20 | | SCOTT | ANALYST | 3000 | 20 | | KING | PRESIDENT | 5000 | 10 | | FORD | ANALYST | 3000 | 20 | +-------+-----------+------+--------+ 4 rows in set (0.00 sec)2.49.查询低于所有部门平均工资的雇员姓名、工作、工资和部门编号select ename,job,sal,deptno from emp where sal <all (select avg(sal) from emp group by deptno);mysql> select ename,job,sal,deptno -> from emp -> where sal <all (select avg(sal) from emp group by deptno); +--------+----------+------+--------+ | ename | job | sal | deptno | +--------+----------+------+--------+ | SMITH | CLERK | 800 | 20 | | WARD | SALESMAN | 1250 | 30 | | MARTIN | SALESMAN | 1250 | 30 | | TURNER | SALESMAN | 1500 | 30 | | ADAMS | CLERK | 1100 | 20 | | JAMES | CLERK | 950 | 30 | | MILLER | CLERK | 1300 | 10 | +--------+----------+------+--------+ 7 rows in set (0.00 sec)2.50.查询工资高于部门编号为30的部门内所有员工工资的雇员姓名、工作、工资和部门编号select ename,job,sal,deptno from emp where sal >all (select distinct sal from emp where deptno = 30);mysql> select ename,job,sal,deptno -> from emp -> where sal >all (select distinct sal from emp where deptno = 30); +-------+-----------+------+--------+ | ename | job | sal | deptno | +-------+-----------+------+--------+ | JONES | MANAGER | 2975 | 20 | | SCOTT | ANALYST | 3000 | 20 | | KING | PRESIDENT | 5000 | 10 | | FORD | ANALYST | 3000 | 20 | +-------+-----------+------+--------+ 4 rows in set (0.00 sec)2.51.查询工资等级为4的雇员姓名、工作、工资、部门编号和工资等级，同时满足该雇员工资高于部门编号为30的部门内所有员工工资select e.ename,e.job,e.sal,e.deptno,s.grade from emp e,salgrade s where s.grade = 4 and e.sal between s.losal and s.hisal and e.sal >all (select distinct sal from emp where deptno = 30);mysql> select e.ename,e.job,e.sal,e.deptno,s.grade -> from emp e,salgrade s -> where s.grade = 4 -> and e.sal between s.losal and s.hisal -> and e.sal >all (select distinct sal from emp where deptno = 30); +-------+---------+------+--------+-------+ | ename | job | sal | deptno | grade | +-------+---------+------+--------+-------+ | JONES | MANAGER | 2975 | 20 | 4 | | SCOTT | ANALYST | 3000 | 20 | 4 | | FORD | ANALYST | 3000 | 20 | 4 | +-------+---------+------+--------+-------+ 3 rows in set (0.00 sec)2.52.查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门号select max(s.grade),e.deptno from emp e,salgrade s where e.sal between s.losal and s.hisal group by e.deptno; select e.ename,e.job,e.sal,s.grade,e.deptno from emp e,salgrade s where e.sal between s.losal and s.hisal and (s.grade,e.deptno) in ( select max(s.grade),e.deptno from emp e,salgrade s where e.sal between s.losal and s.hisal group by e.deptno) order by e.deptno;mysql> select max(s.grade),e.deptno -> from emp e,salgrade s -> where e.sal between s.losal and s.hisal -> group by e.deptno; +--------------+--------+ | max(s.grade) | deptno | +--------------+--------+ | 5 | 10 | | 4 | 20 | | 4 | 30 | +--------------+--------+ 3 rows in set (0.00 sec) mysql> select e.ename,e.job,e.sal,s.grade,e.deptno -> from emp e,salgrade s -> where e.sal between s.losal and s.hisal -> and (s.grade,e.deptno) in ( -> select max(s.grade),e.deptno -> from emp e,salgrade s -> where e.sal between s.losal and s.hisal -> group by e.deptno) -> order by e.deptno; +-------+-----------+------+-------+--------+ | ename | job | sal | grade | deptno | +-------+-----------+------+-------+--------+ | KING | PRESIDENT | 5000 | 5 | 10 | | FORD | ANALYST | 3000 | 4 | 20 | | SCOTT | ANALYST | 3000 | 4 | 20 | | JONES | MANAGER | 2975 | 4 | 20 | | BLAKE | MANAGER | 2850 | 4 | 30 | +-------+-----------+------+-------+--------+ 5 rows in set (0.00 sec)2.53.查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门名称select max(s.grade),e.deptno from emp e,salgrade s where e.sal between s.losal and s.hisal group by e.deptno; select e.ename,e.job,e.sal,s.grade,d.dname from emp e,dept d,salgrade s where e.sal between s.losal and s.hisal and e.deptno = d.deptno and (s.grade,e.deptno) in ( select max(s.grade),e.deptno from emp e,salgrade s where e.sal between s.losal and s.hisal group by e.deptno) order by e.deptno;mysql> select e.ename,e.job,e.sal,s.grade,d.dname -> from emp e,dept d,salgrade s -> where e.sal between s.losal and s.hisal -> and e.deptno = d.deptno -> and (s.grade,e.deptno) in ( -> select max(s.grade),e.deptno -> from emp e,salgrade s -> where e.sal between s.losal and s.hisal -> group by e.deptno) -> order by e.deptno; +-------+-----------+------+-------+------------+ | ename | job | sal | grade | dname | +-------+-----------+------+-------+------------+ | KING | PRESIDENT | 5000 | 5 | ACCOUNTING | | JONES | MANAGER | 2975 | 4 | RESEARCH | | SCOTT | ANALYST | 3000 | 4 | RESEARCH | | FORD | ANALYST | 3000 | 4 | RESEARCH | | BLAKE | MANAGER | 2850 | 4 | SALES | +-------+-----------+------+-------+------------+ 5 rows in set (0.00 sec)2.54.查询工资与30部门中任意一个雇员的工资相等，同时奖金也与30部门中任意一个雇员奖金相等的雇员姓名、工资、奖金、部门编号，但该雇员不是来自30号部门select ename,sal,comm,deptno from emp where sal in (select sal from emp where deptno = 30) and comm in (select comm from emp where deptno = 30 and comm is not null) and comm is not null and comm > 0;mysql> select ename,sal,comm,deptno -> from emp -> where sal in (select sal from emp where deptno = 30) -> and comm in (select comm from emp where deptno = 30 and comm is not null) -> and comm is not null -> and comm > 0; +--------+------+------+--------+ | ename | sal | comm | deptno | +--------+------+------+--------+ | ALLEN | 1600 | 300 | 30 | | WARD | 1250 | 500 | 30 | | MARTIN | 1250 | 1400 | 30 | +--------+------+------+--------+ 3 rows in set (0.00 sec)2.55.查询在纽约（NEW YORK）工作的雇员姓名、工作岗位、工资和奖金select ename,job,sal,comm from emp where deptno = (select deptno from dept where loc = 'new york');mysql> select ename,job,sal,comm -> from emp -> where deptno = (select deptno from dept where loc = 'new york'); +--------+-----------+------+------+ | ename | job | sal | comm | +--------+-----------+------+------+ | CLARK | MANAGER | 2450 | NULL | | KING | PRESIDENT | 5000 | NULL | | MILLER | CLERK | 1300 | NULL | +--------+-----------+------+------+ 3 rows in set (0.00 sec)2.56.显示工资高于部门平均工资的雇员姓名、工作、工资和部门号-- 在from子句中使用子查询时，必须给子查询指定别名 select ename,job,sal,emp.deptno from emp,(select deptno,avg(sal) avgsal from emp group by deptno) t where emp.deptno = t.deptno and sal > t.avgsal;mysql> select ename,job,sal,emp.deptno -> from emp,(select deptno,avg(sal) avgsal from emp group by deptno) t -> where emp.deptno = t.deptno -> and sal > t.avgsal; +-------+-----------+------+--------+ | ename | job | sal | deptno | +-------+-----------+------+--------+ | ALLEN | SALESMAN | 1600 | 30 | | JONES | MANAGER | 2975 | 20 | | BLAKE | MANAGER | 2850 | 30 | | SCOTT | ANALYST | 3000 | 20 | | KING | PRESIDENT | 5000 | 10 | | FORD | ANALYST | 3000 | 20 | +-------+-----------+------+--------+ 6 rows in set (0.00 sec)2.57.查询各部门中工资等级最高的雇员姓名、工作、工资、工资等级和部门号select max(s.grade) maxgrade,e.deptno from emp e,salgrade s where e.sal between s.losal and s.hisal group by e.deptno; select e.ename,e.job,e.sal,s.grade,e.deptno from emp e,salgrade s,( select max(s.grade) maxgrade,e.deptno from emp e,salgrade s where e.sal between s.losal and s.hisal group by e.deptno) p where e.sal between s.losal and s.hisal and e.deptno = p.deptno and s.grade = p.maxgrade order by e.deptno;mysql> select e.ename,e.job,e.sal,s.grade,e.deptno -> from emp e,salgrade s,( -> select max(s.grade) maxgrade,e.deptno -> from emp e,salgrade s -> where e.sal between s.losal and s.hisal -> group by e.deptno) p -> where e.sal between s.losal and s.hisal -> and e.deptno = p.deptno -> and s.grade = p.maxgrade -> order by e.deptno; +-------+-----------+------+-------+--------+ | ename | job | sal | grade | deptno | +-------+-----------+------+-------+--------+ | KING | PRESIDENT | 5000 | 5 | 10 | | SCOTT | ANALYST | 3000 | 4 | 20 | | JONES | MANAGER | 2975 | 4 | 20 | | FORD | ANALYST | 3000 | 4 | 20 | | BLAKE | MANAGER | 2850 | 4 | 30 | +-------+-----------+------+-------+--------+ 5 rows in set (0.00 sec)2.58.查询平均工资最低的工种名称及其平均工资-- 1.按工种查询平均工资 select job,avg(sal) from emp group by job; -- 2.按工种查询最低平均工资 select min(avgsal) from (select avg(sal) avgsal from emp e group by e.job) as t; -- 3.使用having子句把（2）作为（1）子查询 select job,avg(sal) from emp group by job having avg(sal) = (select min(avgsal) from (select avg(sal) avgsal from emp e group by e.job) as t);mysql> select job,avg(sal) from emp group by job; +-----------+--------------------+ | job | avg(sal) | +-----------+--------------------+ | ANALYST | 3000 | | CLERK | 1037.5 | | MANAGER | 2758.3333333333335 | | PRESIDENT | 5000 | | SALESMAN | 1400 | +-----------+--------------------+ 5 rows in set (0.00 sec) mysql> select min(avgsal) from (select avg(sal) avgsal from emp e group by e.job) as t; +-------------+ | min(avgsal) | +-------------+ | 1037.5 | +-------------+ 1 row in set (0.00 sec) mysql> select job,avg(sal) -> from emp -> group by job -> having avg(sal) = (select min(avgsal) from (select avg(sal) avgsal from emp e group by e.job) as t); +-------+----------+ | job | avg(sal) | +-------+----------+ | CLERK | 1037.5 | +-------+----------+ 1 row in set (0.00 sec)2.59.查询与部门编号为30的员工工资水平相同的员工信息select avg(sal) from emp where deptno = 30; select grade from salgrade where (select avg(sal) from emp where deptno = 30) between losal and hisal; select * from emp inner join salgrade s on sal between s.losal and s.hisal where s.grade = (select grade from salgrade where (select avg(sal) from emp where deptno = 30) between losal and hisal);mysql> select avg(sal) from emp where deptno = 30; +--------------------+ | avg(sal) | +--------------------+ | 1566.6666666666667 | +--------------------+ 1 row in set (0.00 sec) mysql> select grade from salgrade where (select avg(sal) from emp where deptno = 30) between losal and hisal; +-------+ | grade | +-------+ | 3 | +-------+ 1 row in set (0.00 sec) mysql> select * -> from emp -> inner join salgrade s -> on sal between s.losal and s.hisal -> where s.grade = -> (select grade from salgrade where (select avg(sal) from emp where deptno = 30) between losal and hisal); +-------+--------+----------+------+------------+------+------+--------+-------+-------+-------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | grade | losal | hisal | +-------+--------+----------+------+------------+------+------+--------+-------+-------+-------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 3 | 1401 | 2000 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 3 | 1401 | 2000 | +-------+--------+----------+------+------------+------+------+--------+-------+-------+-------+ 2 rows in set (0.00 sec)2.60.查询部门号、部门名、部门所在位置及其每个部门的员工总数select d.deptno,d.dname,d.loc,count(e.deptno) from emp e inner join dept d on e.deptno = d.deptno group by d.deptno;mysql> select d.deptno,d.dname,d.loc,count(e.deptno) -> from emp e -> inner join dept d -> on e.deptno = d.deptno -> group by d.deptno; +--------+------------+----------+-----------------+ | deptno | dname | loc | count(e.deptno) | +--------+------------+----------+-----------------+ | 10 | ACCOUNTING | NEW YORK | 3 | | 20 | RESEARCH | DALLAS | 5 | | 30 | SALES | CHICAGO | 6 | +--------+------------+----------+-----------------+ 3 rows in set (0.00 sec)2.61.计算员工的年薪并且以年薪排序select (sal*12+(if(comm is null,0,comm))) from emp; select e.*,(select (sal*12+(if(comm is null,0,comm))) y_sal from emp where e.empno = emp.empno) y_sal from emp e order by y_sal asc;mysql> select e.*,(select (sal*12+(if(comm is null,0,comm))) y_sal from emp where e.empno = emp.empno) y_sal -> from emp e -> order by y_sal asc; +-------+--------+-----------+------+------------+------+------+--------+-------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | y_sal | +-------+--------+-----------+------+------------+------+------+--------+-------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | 9600 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 11400 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | 13200 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 15500 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | 15600 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 16400 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 18000 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 19500 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 29400 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 34200 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 35700 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | 36000 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 36000 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | 60000 | +-------+--------+-----------+------+------------+------+------+--------+-------+ 14 rows in set (0.00 sec)endl