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