mysql sql练习
程序员文章站
2022-06-01 20:16:00
...
1. 准备数据工作
DROP TABLE DEPT;
--部门表
CREATE TABLE DEPT(
DEPTNO int PRIMARY KEY,
DNAME VARCHAR(14) , --部门名称
LOC VARCHAR(13) ---部门地址
) ;
CREATE TABLE DEPT(
DEPTNO int PRIMARY KEY,
DNAME VARCHAR(14) ,
LOC VARCHAR(13)
) ;
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');
DROP TABLE EMP;
--员工表
CREATE TABLE EMP(
EMPNO int PRIMARY KEY, --员工编号
ENAME VARCHAR(10), ---员工姓名
JOB VARCHAR(9), --员工工作
MGR int, ----员工直属领导编号
HIREDATE DATE, ----入职时间
SAL double, ---工资
COMM double, --奖金
DEPTNO int REFERENCES DEPT); --关联dept表
CREATE TABLE EMP(
EMPNO int PRIMARY KEY,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR int,
HIREDATE DATE,
SAL double,
COMM double,
DEPTNO int ,
foreign key EMP(DEPTNO) REFERENCES DEPT(DEPTNO)
);
INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10);
DROP TABLE SALGRADE;
//工资等级表
CREATE TABLE SALGRADE(
GRADE int,--等级
LOSAL double, --最低工资
HISAL double ); --最高工次
CREATE TABLE SALGRADE(
GRADE int,
LOSAL double,
HISAL double );
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);2. 单表操作
--2.1 查找部门30中员工的详细信息。
select * from emp where deptno = 30;
MariaDB [test]> 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 |
+-------+--------+----------+------+------------+------+------+--------+
6 rows in set (0.01 sec)
-- 2.2 找出从事clerk工作的员工的编号、姓名、部门号。
MariaDB [test]> select * from emp where 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 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 |
+-------+--------+-------+------+------------+------+------+--------+
4 rows in set (0.00 sec)
-- 2.3 检索出奖金多于基本工资的员工信息。
MariaDB [test]> 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.02 sec)
MariaDB [test]> select * from emp where ifnull(comm ,0)> sal;
+-------+--------+----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+------+------+--------+
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
+-------+--------+----------+------+------------+------+------+--------+
1 row in set (0.05 sec)
-- 2.4 检索出奖金多于基本工资60%的员工信息。
MariaDB [test]> 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.01 sec)
-- 2.5 找出10部门的经理(job=Manger)、20部门的职员(job=clerk) 的员工信息。
MariaDB [test]> select * from emp where (job='manager' and deptno = 10 ) 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.02 sec)
-- 2.6 找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。
MariaDB [test]> select * from emp where (deptno = 10 and job = 'manager') or (deptno = 20 and job = 'clerk') or (job no t 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.01 sec)
-- 2.7 找出获得奖金的员工的工作。
mysql> select * from emp where ifnull(comm,0)>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.8 找出奖金少于100或者没有获得奖金的员工的信息。
MariaDB [test]> select * from emp where ifnull(comm , 0) < 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.01 sec)
-- 2.9 找出姓名以A、B、S开始的员工信息。
MariaDB [test]> select * from emp where ename like 'A%' or ename like 'B%' or ename like 's%';
+-------+-------+----------+------+------------+------+------+--------+
| 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 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 |
+-------+-------+----------+------+------------+------+------+--------+
5 rows in set (0.00 sec)
-- 2.10 找到名字长度为7个字符的员工信息。
MariaDB [test]> select * from emp where ename like '_______';
Empty set (0.00 sec)
MariaDB [test]> select * from emp where length(ename)=7;
Empty set (0.05 sec)
--2.11 名字中不包含R字符的员工信息。
MariaDB [test]> 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)
-- 2.12 返回员工的详细信息并按姓名排序。
MariaDB [test]> select * from emp order by ename ;
+-------+--------+-----------+------+------------+------+------+--------+
| 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)
-- 2.13 返回员工的信息并按工作降序工资升序排列。
MariaDB [test]> select * from emp order by job desc , sal;
+-------+--------+-----------+------+------------+------+------+--------+
| 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 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)
-- 2.14 计算员工的年薪
MariaDB [test]> select ename , sal*12 + ifnull(comm,0) as yearsal from emp ;
+--------+---------+
| ename | yearsal |
+--------+---------+
| SMITH | 9600 |
| ALLEN | 19500 |
| WARD | 15500 |
| JONES | 35700 |
| MARTIN | 16400 |
| BLAKE | 34200 |
| CLARK | 29400 |
| SCOTT | 36000 |
| KING | 60000 |
| TURNER | 18000 |
| ADAMS | 13200 |
| JAMES | 11400 |
| FORD | 36000 |
| MILLER | 15600 |
+--------+---------+
14 rows in set (0.00 sec)
-- 返回部门号及其本部门的最低工资。
MariaDB [test]> select min(sal) ,deptno from emp group by deptno;
+----------+--------+
| min(sal) | deptno |
+----------+--------+
| 1300 | 10 |
| 800 | 20 |
| 950 | 30 |
+----------+--------+
3 rows in set (0.00 sec)
3. 多表联合查询
--1、返回拥有员工的部门名、部门号。
MariaDB [test]> select distinct(e.deptno) , d.dname from emp as e left join dept as d on e.deptno = d.deptno ;
+--------+------------+
| deptno | dname |
+--------+------------+
| 10 | accpunting |
| 20 | RESEARCH |
| 30 | SALES |
+--------+------------+
3 rows in set (0.01 sec)
--子查询
MariaDB [test]> select * from dept where deptno in (select distinct(deptno) from emp);
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | accpunting | new york |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
+--------+------------+----------+
3 rows in set (0.00 sec)
--2、工资水平多于smith的员工信息
-- 子查询
MariaDB [test]> 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.01 sec)
---多表联合查询
mysql> select e1.* from emp as e1 , emp as e2 where e2.ename = 'smith' and e1.sal > e2.sal;
+-------+--------+-----------+------+------------+------+------+--------+
| 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)
--3、返回员工和所属经理的姓名
MariaDB [test]> select e1.ename as ename , e2.ename as mname from emp as e1 left join emp as e2 on e1.mgr = e2.empno;
+--------+-------+
| ename | mname |
+--------+-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+
14 rows in set (0.00 sec)
--4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。
MariaDB [test]> select e1.ename as ename , e2.ename as mname from emp as e1 left join emp as e2 on e1.mgr=e2.empno where e1.hiredate > e2.hiredate;
+--------+-------+
| ename | mname |
+--------+-------+
| MARTIN | BLAKE |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
+--------+-------+
6 rows in set (0.01 sec)
--5、返回员工姓名及其所在的部门名称。
MariaDB [test]> select e.ename , d.dname from emp as e left join dept as d on e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | accpunting |
| SCOTT | RESEARCH |
| KING | accpunting |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | accpunting |
+--------+------------+
14 rows in set (0.00 sec)
--6、返回从事clerk工作的员工姓名和所在部门名称。
--- 多表联合查询
MariaDB [test]> select e.ename , d.dname from emp as e left join dept as d on e.deptno=d.deptno where e.job='clerk';
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ADAMS | RESEARCH |
| JAMES | SALES |
| MILLER | accpunting |
+--------+------------+
4 rows in set (0.00 sec)
--- 子查询
mysql> select * from (select * from emp where job = 'clerk') as c left join dept as d on c.deptno=d.deptno;
+-------+--------+-------+------+------------+------+------+--------+--------+------------+----------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | DEPTNO | DNAME | LOC |
+-------+--------+-------+------+------------+------+------+--------+--------+------------+----------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | 20 | RESEARCH | DALLAS |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | 30 | SALES | CHICAGO |
| 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
+-------+--------+-------+------+------------+------+------+--------+--------+------------+----------+
4 rows in set (0.00 sec)
--7、返回部门名称及其本部门的最低工资。
MariaDB [test]> select min(sal) , d.dname from emp as e left join dept as d on e.deptno = d.deptno group by e.deptno;
+----------+------------+
| min(sal) | dname |
+----------+------------+
| 1300 | accpunting |
| 800 | RESEARCH |
| 950 | SALES |
+----------+------------+
3 rows in set (0.01 sec)
-- 子查询
MariaDB [test]> select tmp.minsal , d.dname from (select min(sal) as minsal, deptno from emp group by deptno) as tmp left join dept as d on tmp.deptno = d.deptno;
+--------+------------+
| minsal | dname |
+--------+------------+
| 1300 | accpunting |
| 800 | RESEARCH |
| 950 | SALES |
+--------+------------+
3 rows in set (0.00 sec)
--8、返回销售部(sales)所有员工的姓名。
MariaDB [test]> select e.ename from emp as e left join dept as d on e.deptno = d.deptno where d.dname='sales';
+--------+
| ename |
+--------+
| ALLEN |
| WARD |
| MARTIN |
| BLAKE |
| TURNER |
| JAMES |
+--------+
6 rows in set (0.01 sec)
--9、返回工资水平多于平均工资的员工。
-- 子查询
MariaDB [test]> 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.01 sec)
--10、返回与SCOTT从事相同工作的员工。
--子查询
MariaDB [test]> select * from emp where job=(select job from emp where ename='scott') and ename<>'scott';
+-------+-------+---------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+------+------+--------+
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+-------+---------+------+------------+------+------+--------+
1 row in set (0.00 sec)
--12、返回工资高于30部门所有员工工资水平的员工信息。
MariaDB [test]> select * from emp where sal>=(select avg(sal) from emp where deptno = 30);
+-------+-------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+------+------+--------+
| 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 |
| 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 |
+-------+-------+-----------+------+------------+------+------+--------+
7 rows in set (0.01 sec)
--13、返回部门号、部门名、部门所在位置及其每个部门的员工总数。
MariaDB [test]> select count(e.ename) , d.deptno , d.dname ,d.loc from emp as e left join dept as d on e.deptno = d.deptno group by d.deptno;
+----------------+--------+------------+----------+
| count(e.ename) | deptno | dname | loc |
+----------------+--------+------------+----------+
| 3 | 10 | accpunting | new york |
| 5 | 20 | RESEARCH | DALLAS |
| 6 | 30 | SALES | CHICAGO |
+----------------+--------+------------+----------+
3 rows in set (0.01 sec)
--14、返回员工的姓名、所在部门名及其工资。
MariaDB [test]> select e.ename , d.dname ,e.sal from emp as e left join dept as d on e.deptno = d.deptno;
+--------+------------+------+
| ename | dname | sal |
+--------+------------+------+
| SMITH | RESEARCH | 800 |
| ALLEN | SALES | 1600 |
| WARD | SALES | 1250 |
| JONES | RESEARCH | 2975 |
| MARTIN | SALES | 1250 |
| BLAKE | SALES | 2850 |
| CLARK | accpunting | 2450 |
| SCOTT | RESEARCH | 3000 |
| KING | accpunting | 5000 |
| TURNER | SALES | 1500 |
| ADAMS | RESEARCH | 1100 |
| JAMES | SALES | 950 |
| FORD | RESEARCH | 3000 |
| MILLER | accpunting | 1300 |
+--------+------------+------+
14 rows in set (0.00 sec)
--15、返回员工的详细信息。(包括部门名)
--16、返回员工工作及其从事此工作的最低工资。
-- 子查询
MariaDB [test]> select e.ename , e.job , t.minsal from emp as e left join (select min(sal) as minsal, job from emp group by job) as t on e.job=t.job;
+--------+-----------+--------+
| ename | job | minsal |
+--------+-----------+--------+
| SMITH | CLERK | 800 |
| ALLEN | SALESMAN | 1250 |
| WARD | SALESMAN | 1250 |
| JONES | MANAGER | 2450 |
| MARTIN | SALESMAN | 1250 |
| BLAKE | MANAGER | 2450 |
| CLARK | MANAGER | 2450 |
| SCOTT | ANALYST | 3000 |
| KING | PRESIDENT | 5000 |
| TURNER | SALESMAN | 1250 |
| ADAMS | CLERK | 800 |
| JAMES | CLERK | 800 |
| FORD | ANALYST | 3000 |
| MILLER | CLERK | 800 |
+--------+-----------+--------+
14 rows in set (0.00 sec)
--17、返回不同部门经理的最低工资。
MariaDB [test]> select min(sal), deptno from emp where job='manager' group by deptno;
+----------+--------+
| min(sal) | deptno |
+----------+--------+
| 2450 | 10 |
| 2975 | 20 |
| 2850 | 30 |
+----------+--------+
3 rows in set (0.01 sec)
--18、计算出员工的年薪，并且以年薪排序
MariaDB [test]> select sal*12+ifnull(comm,0) as salcount , ename from emp order by salcount;
+----------+--------+
| salcount | ename |
+----------+--------+
| 9600 | SMITH |
| 11400 | JAMES |
| 13200 | ADAMS |
| 15500 | WARD |
| 15600 | MILLER |
| 16400 | MARTIN |
| 18000 | TURNER |
| 19500 | ALLEN |
| 29400 | CLARK |
| 34200 | BLAKE |
| 35700 | JONES |
| 36000 | FORD |
| 36000 | SCOTT |
| 60000 | KING |
+----------+--------+
14 rows in set (0.02 sec)
--19、返回工资处于第四级别的员工的姓名。
MariaDB [test]> select * from emp ,salgrade where sal between losal and hisal and salgrade.grade=4;
+-------+-------+---------+------+------------+------+------+--------+-------+-------+-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | GRADE | LOSAL | HISAL |
+-------+-------+---------+------+------------+------+------+--------+-------+-------+-------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 4 | 2001 | 3000 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 4 | 2001 | 3000 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 4 | 2001 | 3000 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | 4 | 2001 | 3000 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 4 | 2001 | 3000 |
+-------+-------+---------+------+------------+------+------+--------+-------+-------+-------+
5 rows in set (0.00 sec)
--20、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
--21、返回工资为二等级的职员名字、部门所在地、二等级员工工资的最低工资和最高工资
MariaDB [test]> select (select min(sal) from emp as e , salgrade as s where e.sal between s.losal and s .hisal and s . grade=2) as min ,t.ename , d.loc from (select * from emp as e , salgrade as s where e.sal between s.losal and s .hisal and s.grade=2) as t left join dept as d on t.deptno=d.deptno;
+------+--------+----------+
| min | ENAME | loc |
+------+--------+----------+
| 1250 | WARD | CHICAGO |
| 1250 | MARTIN | CHICAGO |
| 1250 | MILLER | new york |
+------+--------+----------+
3 rows in set (0.01 sec)
--22.工资等级多于smith的员工信息。
MariaDB [test]> select * from emp as e ,salgrade as s where sal between losal and hisal and s.grade > (select s.grade from emp as e , salgrade as s where sal between losal and hisal and e.ename = 'smith');
+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | GRADE | LOSAL | HISAL |
+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 3 | 1401 | 2000 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 2 | 1201 | 1400 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | 4 | 2001 | 3000 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 2 | 1201 | 1400 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | 4 | 2001 | 3000 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | 4 | 2001 | 3000 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | 4 | 2001 | 3000 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | 5 | 3001 | 9999 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 3 | 1401 | 2000 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | 4 | 2001 | 3000 |
| 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | 2 | 1201 | 1400 |
+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
11 rows in set (0.00 sec)4. 子查询
上一篇： 第七章子查询课后练习和作业