classic-Mysql语句整理

该本文列举了一些公认为比较经典的Mysql语句,对以后的数据库操作学习有很大的帮助
建议:自己本地打开Mysql新建数据库,边操作边理解

初始化数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
drop database if exists emps;
create database emps;
use emps;
create table EMP (
EMPNO INT, -- 员工号
ENAME VARCHAR(10), -- 员工姓名
JOB VARCHAR(9), -- 工作岗位
MGR int, -- 经理的员工号,外键
HIREDATE date, -- 入职时间
SAL double, -- 底薪
COMM double, -- 提成
DEPTNO int, -- 部门编号
primary key(EMPNO)
) ;
create table BONUS (
id int primary key auto_increment, -- 主键
ENAME VARCHAR(10), -- 员工性别
JOB VARCHAR(9), -- 工作岗位
SAL double, -- 底薪
COMM double -- 奖金
) ;
CREATE TABLE DEPT (
DEPTNO int, -- 部门编号
DNAME varchar(13), -- 部门名称
LOC VARCHAR(13), -- 部门地点
primary key(DEPTNO)
) ;
CREATE TABLE SALGRADE ( -- 工资等级表
id int primary key auto_increment,
GRADE int, -- 等级
LOSAL double, -- 最低工资
HISAL double -- 最高工资
) ;

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,'2003-12-17',800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,'2007-9-3',1600,300,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,'2005-3-8',1250,500,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,'2007-7-7',2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,'2005-5-6',1250,1400,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,'2005-5-6',2850,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,'2010-3-1',2450,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,'2005-5-6',3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,'2001-5-6',5000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,'2008-8-8',1500,0,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,'2005-5-6',1100,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,'2005-5-6',950,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,'2005-5-6',3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,'2005-5-6',1300,null,10);
Insert into DEPT (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into DEPT (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into DEPT (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into DEPT (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');
Insert into SALGRADE (GRADE,LOSAL,HISAL) values (1,700,1200);
Insert into SALGRADE (GRADE,LOSAL,HISAL) values (2,1201,1400);
Insert into SALGRADE (GRADE,LOSAL,HISAL) values (3,1401,2000);
Insert into SALGRADE (GRADE,LOSAL,HISAL) values (4,2001,3000);
Insert into SALGRADE (GRADE,LOSAL,HISAL) values (5,3001,9999);

Mysql Query 示例

列出至少有一个员工的所有部门。

循环嵌套,去重复

1
2
3
SELECT DISTINCT DNAME as 部门
FROM dept,emp
WHERE dept.DEPTNO = emp.DEPTNO AND (SELECT COUNT(DEPTNO) from emp )>0;

列出薪金比“SMITH”多或者相等的所有员工。

where语句

1
2
3
SELECT ENAME as 员工名
FROM emp
WHERE SAL > (SELECT SAL FROM emp WHERE ENAME = 'SMITH');

列出所有员工的姓名及其直接上级的姓名。

比较一个表上不同的两列的信息

1
2
SELECT a.ENAME as 员工名,(SELECT ENAME FROM emp b where b.empno=a.mgr) as 上级姓名 
FROM emp a;

列出受雇日期早于其直接上级的所有员工。

1
2
3
SELECT a.ENAME as 受雇日期早于其直接上级的所有员工 
FROM emp a
WHERE a.HIREDATE<(SELECT HIREDATE FROM emp b WHERE b.empno=a.mgr);

列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

1
2
SELECT a.DNAME as 部门,b.EMPNO as 员工编号, b.ENAME as 员工,b.job as 岗位, b.MGR as 经理编号, b.SAL as 薪水
From dept a LEFT JOIN emp b ON a.DEPTNO = b.DEPTNO;

列出所有“CLERK”(办事员)的姓名及其部门名称。

1
2
3
SELECT ENAME as 办事员员工,b.DNAME as 部门
From emp a,dept b
WHERE a.JOB = 'CLERK' AND a.DEPTNO = b.DEPTNO;

列出最低薪金大于1500的各种工作。

1
2
3
SELECT JOB as 大于1500薪水工作
FROM emp
GROUP BY JOB HAVING MIN(SAL)>1500;

列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。

1
2
3
SELECT ENAME as 销售部员工
FROM emp
WHERE DEPTNO = (SELECT DEPTNO FROM dept WHERE DNAME = 'SALES');

列出薪金高于公司平均薪金的所有员工。

1
2
3
SELECT ENAME as 薪金高于平均薪金员工 
FROM emp
WHERE SAL > (SELECT AVG(SAL) FROM emp);

列出与“SCOTT”从事相同工作的所有员工。

1
2
3
SELECT ENAME as 从事SCOTT工作的员工 
FROM emp
WHERE JOB = (SELECT JOB FROM emp WHERE ENAME = 'SCOTT') AND ENAME <> 'SCOTT' ;

列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

不等于符号

1
2
3
SELECT a.ENAME as 符合要求的员工,a.SAL as 薪金
FROM emp a
WHERE a.SAL in (SELECT b.SAL from emp b WHERE b.DEPTNO = 30) AND a.DEPTNO <> 30 ;

列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金

函数MAX

1
2
3
SELECT ENAME as 符合要求的员工,SAL as 薪金
FROM emp
WHERE SAL > (SELECT MAX(SAL) FROM emp WHERE DEPTNO = '30');

列出在每个部门工作的部门名称,员工数量、平均工资。

函数AVG

1
2
3
SELECT (SELECT a.DNAME FROM dept a WHERE a.DEPTNO = b.DEPTNO) as 部门,COUNT(b.DEPTNO) as 员工数量,AVG(SAL) as 平均工资
FROM emp b
GROUP BY b.DEPTNO;

列出所有员工的姓名、部门名称和工资(含奖金)。

1
2
SELECT a.ENAME as 员工姓名,(SELECT b.DNAME FROM dept b WHERE b.DEPTNO=a.DEPTNO) as 部门名称,SAL as 薪金 
FROM emp a ;

列出所有部门的详细信息和部门人数。

函数COUNT

1
2
SELECT a.DEPTNO as 部门编号,a.DNAME as 部门名称,a.LOC as 部门位置,(SELECT COUNT(DEPTNO) FROM emp b WHERE b.DEPTNO=a.DEPTNO GROUP BY b.DEPTNO) as 部门人数 
FROM dept a;

列出各种工作的最低工资。

1
2
SELECT JOB as 工作,MIN(SAL) as 薪金
FROM emp GROUP BY JOB;

列出各个MANAGER(经理)的最低薪金。

函数MIN

1
2
3
ELECT ENAME as 经理, MIN(SAL) as 经理最低薪金
FROM emp
WHERE JOB='MANAGER' GROUP BY DEPTNO;

列出所有员工的年工资,按年薪从低到高排序。

排序,列属性运算

1
2
3
SELECT ENAME as 员工, SAL*12 as 年工资
FROM emp
ORDER BY SAL;
打赏还是得开着的,万一有人打赏呢?