EMP+DEPT+SALGRADE 表的基本操作1

EMP+DEPT+SALGRADE 表的基本操作1

创建表

部门表

-- 部门表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');

员工表

-- 员工表CREATE TABLE EMP ( EMPNO INT PRIMARY KEY, -- 员工编号 ENAME VARCHAR(10), -- 员工名称 JOB VARCHAR(9), -- 工作 MGR DOUBLE, -- 直属领导编号 HIREDATE DATE, -- 入职时间 SAL DOUBLE, -- 工资 COMM DOUBLE, -- 奖金 DEPTNO INT, -- 部门号 FOREIGN KEY(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-13',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,'1982-01-23',1300,NULL,10);

工资等级表

-- 工资等级表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);

小练习

1、查询工资等于5000 的员工姓名？

mysql> select * from EMP where sal =1500;+-------+--------+----------+------+------------+------+------+--------+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |+-------+--------+----------+------+------------+------+------+--------+| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |+-------+--------+----------+------+------------+------+------+--------+1 row in set (0.00 sec)

2、查询SMITH的工资

mysql> select sal from EMP where ename="SMITH";+------+| sal |+------+| 800 |+------+1 row in set (0.00 sec)

3、找出工资高于3000的员工

mysql> select Sal from EMP where sal >=3000;+------+| Sal |+------+| 3000 || 5000 || 3000 |+------+3 rows in set (0.00 sec)

4、找2900到3000的工资

mysql> select sal from EMP where sal between 2900 and 3000;+------+| sal |+------+| 2975 || 3000 || 3000 |+------+3 rows in set (0.00 sec)

5、查看不为空和为空的字段

mysql> select * from EMP where comm is null;+-------+--------+-----------+------+------------+------+------+--------+| 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-13 | 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 || 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 || 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |+-------+--------+-----------+------+------------+------+------+--------+10 rows in set (0.00 sec)mysql> select * from EMP where comm is not null;+-------+--------+----------+------+------------+------+------+--------+| 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 || 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |+-------+--------+----------+------+------------+------+------+--------+4 rows in set (0.00 sec)

6、找出薪资大与3000 的并且部门编号是20 或者30部门的员工

mysql> select sal from EMP where sal >1000 and (deptno=20 or deptno=30);+------+| sal |+------+| 1600 || 1250 || 2975 || 1250 || 2850 || 3000 || 1500 || 1100 || 3000 |+------+9 rows in set (0.00 sec)

in的使用

范围 (起始范围 --> 结束范围)

mysql> select ename,job from EMP where sal in (800,5000);+-------+-----------+| ename | job |+-------+-----------+| SMITH | CLERK || KING | PRESIDENT |+-------+-----------+2 rows in set (0.00 sec)mysql> select ename,job from EMP where sal not in (800,5000);+--------+----------+| ename | job |+--------+----------+| ALLEN | SALESMAN || WARD | SALESMAN || JONES | MANAGER || MARTIN | SALESMAN || BLAKE | MANAGER || CLARK | MANAGER || SCOTT | ANALYST || TURNER | SALESMAN || ADAMS | CLERK || JAMES | CLERK || FORD | ANALYST || MILLER | CLERK |+--------+----------+12 rows in set (0.00 sec)

order by 升序降序的使用

mysql> select sal from EMP order by sal asc;+------+| sal |+------+| 800 || 950 || 1100 || 1250 || 1250 || 1300 || 1500 || 1600 || 2450 || 2850 || 2975 || 3000 || 3000 || 5000 |+------+14 rows in set (0.01 sec)mysql> select sal from EMP order by sal desc;+------+| sal |+------+| 5000 || 3000 || 3000 || 2975 || 2850 || 2450 || 1600 || 1500 || 1300 || 1250 || 1250 || 1100 || 950 || 800 |+------+14 rows in set (0.00 sec)

分组函数

count 计数Sum求和avg平均数max最大值mix最小值 count()和count(具体的某个字段),他们有什么区别? count():不是统计某个字段中数据到的个数,而是统计总记录条数.(和别的字段无关) count(comm):表示统计comm字段中不为null的数据总数量

mysql> select ename,(sal+comm)*12 as years from EMP;# 所有数据库都是这样规定的,只要有null参加运算结果一定是null

group by和having

group by:按照某个字段或者某些字段进行分组 having:having是对分组之后的数据进行再次过滤

1、找出每个工作岗位的最高薪资

Select max(sal) from emp group by job;

2、找出工资高于平均各自的员工

第一步：找出平均工资 select avg（sal）from emp； 第二步：找出高于平均工资的员工 select ename,sal from emp where sal >（平均工资）

mysql> select ename,sal from EMP where sal > (select avg(sal) from EMP);+-------+------+| ename | sal |+-------+------+| JONES | 2975 || BLAKE | 2850 || CLARK | 2450 || SCOTT | 3000 || KING | 5000 || FORD | 3000 |+-------+------+6 rows in set (0.00 sec)

3、查询每个工作岗位的平均薪资

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 |+-----------+--------------------+

4、找出每个工作岗位的最高薪资

mysql> select max(sal) from EMP group by job ;+----------+| max(sal) |+----------+| 3000 || 1300 || 2975 || 5000 || 1600 |+----------+

5、找出没个部门不同工作岗位的最高薪资

mysql> select max(sal),deptno from EMP group by deptno;+----------+--------+| max(sal) | deptno |+----------+--------+| 5000 | 10 || 3000 | 20 || 2850 | 30 |+----------+--------+3 rows in set (0.00 sec)

6、找出每个部门最高薪资，要求显示薪资大与2900的数据

mysql> select max(sal) from EMP where sal>2900 group by deptno;+----------+| max(sal) |+----------+| 5000 || 3000 |+----------+2 rows in set (0.00 sec)

总结一个完整的DQL语句怎么写

顺序重要

select ...from ...group by ...having ...order by ...

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。