创建表
部门表
-- 部门表
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
...