SQL

SQL用来和数据库打交道，完成和数据库的通信，SQL是一套标准

数据库（DataBase）：

通常是一个或一组文件，保存了一些符合特定规格的数据

表（table）:

命名规则：小写，单词之间用"_"分割开来

是一种结构化的文件，可以用来存储特定类型的数据

表中的概念：列，行，主键

列叫做字段（Column），行叫做表中的记录，每一个字段都有：

字段名称/字段数据类型/字段约束/字段长度

学生信息表：

学号（主键）	姓名	性别	年龄
00001	张三	男	20
00002	李四	女	20

主键是不能重复的

在mysql中语句是不区分大小写的

数据查询语言（DQL-Data Query Language）

代表关键字：select

数据操作语言（DML-Data Manipulation Language）

代表关键字：insert，delete，update

数据定义语言（DDL-Data Definition Language）

代表关键字：create，drop，alter

事务控制语言（TCL-Transaction Control Language）

代表关键字：commit，rollback

数据控制语言（DCL-Data Control Language）

代表关键字：grant，revoke

创建数据库

create database 数据库名称


1
1
create database os467test;

use 数据库名称


1
1
use os467test;

在数据库中建立表，因此创建表的时候必须要先选择数据库

可以查询当前使用的数据库


1
1
select database();

查询数据库版本也可以使用


1
1
select version();

终止一条语句,输入ctrl + c

退出mysql

可以使用\q，QUIT或EXIT

显示表


1
1
show tables;

基本流程:

1.安装mysql

2.启动mysql


1
1
net start mysql

3.登录mysql


1
1
mysql -u -p

4.创建数据库


1
1
create database 数据库名称;

5.使用数据库


xxxxxxxxxx
1
1
use 数据库名称;

6.创建测试表，添加测试数据


x
1
DROP TABLE IF EXISTS EMP;
2
DROP TABLE IF EXISTS DEPT;
3
DROP TABLE IF EXISTS SALGRADE;
4

5
CREATE TABLE DEPT
6
       (DEPTNO int(2) not null ,
7
    DNAME VARCHAR(14) ,
8
    LOC VARCHAR(13),
9
    primary key (DEPTNO)
10
    );
11
CREATE TABLE EMP
12
       (EMPNO int(4)  not null ,
13
    ENAME VARCHAR(10),
14
    JOB VARCHAR(9),
15
    MGR INT(4),
16
    HIREDATE DATE  DEFAULT NULL,
17
    SAL DOUBLE(7,2),
18
    COMM DOUBLE(7,2),
19
    primary key (EMPNO),
20
    DEPTNO INT(2) 
21
    )
22
    ;
23

24
CREATE TABLE SALGRADE
25
      ( GRADE INT,
26
    LOSAL INT,
27
    HISAL INT );
28

29

30

31

32
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
33
10, 'ACCOUNTING', 'NEW YORK'); 
34
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
35
20, 'RESEARCH', 'DALLAS'); 
36
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
37
30, 'SALES', 'CHICAGO'); 
38
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
39
40, 'OPERATIONS', 'BOSTON'); 
40
commit;
41
 
42
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
43
DEPTNO ) VALUES ( 
44
7369, 'SMITH', 'CLERK', 7902,  '1980-12-17'
45
, 800, NULL, 20); 
46
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
47
DEPTNO ) VALUES ( 
48
7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20'
49
, 1600, 300, 30); 
50
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
51
DEPTNO ) VALUES ( 
52
7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22'
53
, 1250, 500, 30); 
54
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
55
DEPTNO ) VALUES ( 
56
7566, 'JONES', 'MANAGER', 7839,  '1981-04-02'
57
, 2975, NULL, 20); 
58
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
59
DEPTNO ) VALUES ( 
60
7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28'
61
, 1250, 1400, 30); 
62
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
63
DEPTNO ) VALUES ( 
64
7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01'
65
, 2850, NULL, 30); 
66
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
67
DEPTNO ) VALUES ( 
68
7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09'
69
, 2450, NULL, 10); 
70
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
71
DEPTNO ) VALUES ( 
72
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
73
, 3000, NULL, 20); 
74
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
75
DEPTNO ) VALUES ( 
76
7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17'
77
, 5000, NULL, 10); 
78
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
79
DEPTNO ) VALUES ( 
80
7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08'
81
, 1500, 0, 30); 
82
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
83
DEPTNO ) VALUES ( 
84
7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23'
85
, 1100, NULL, 20); 
86
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
87
DEPTNO ) VALUES ( 
88
7900, 'JAMES', 'CLERK', 7698,  '1981-12-03'
89
, 950, NULL, 30); 
90
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
91
DEPTNO ) VALUES ( 
92
7902, 'FORD', 'ANALYST', 7566,  '1981-12-03'
93
, 3000, NULL, 20); 
94
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
95
DEPTNO ) VALUES ( 
96
7934, 'MILLER', 'CLERK', 7782,  '1982-01-23'
97
, 1300, NULL, 10); 
98
commit;
99
 
100
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
101
1, 700, 1200); 
102
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
103
2, 1201, 1400); 
104
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
105
3, 1401, 2000); 
106
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
107
4, 2001, 3000); 
108
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES ( 
109
5, 3001, 9999); 
110
commit;

7.关于表的结构

emp员工表

dept部门表

SALGRADE薪水等级表

员工表和部门表的关系是多对一的关系，因为多个员工会对应一个部门

一个部门会对应多个员工；然后现在我们要找到员工表和部门表之间对应的关系

dept 主键 deptno（唯一标识）

emp 主键 empno 外键（通过主键id建立多表关系的字段，一般就是另外一张表的主键id）deptno

列出当前可用数据库：


xxxxxxxxxx
1
1
show databases;

写SQL语句：

1.DQL语句（select）

单表查询

//查询员工表emp所有的数据，*通配符，代表所有的数据 from 属于


xxxxxxxxxx
1
1
select * from emp;

查询结果：


xxxxxxxxxx
18
1
+-------+--------+-----------+------+------------+---------+---------+--------+
2
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
3
+-------+--------+-----------+------+------------+---------+---------+--------+
4
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
5
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
6
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
7
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
8
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
9
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
10
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
11
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
12
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
13
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
14
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
15
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
16
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
17
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
18
+-------+--------+-----------+------+------------+---------+---------+--------+

2.DML语句（增删改 insert（添加） delete（删除） update（修改））

//a：在emp员工表中添加一条员工数据一般在添加数据的时候，主键在有自动递增的情况下是不用添加的，因为主键我们在设计表的时候给了它自动递增的约束

//into关键字可加可不加


xxxxxxxxxx
2
1
insert into emp(EMPNO,ename,job,mgr,hiredate,sal,comm,deptno)
2
values(7935,"Tom","CLERK",7698,"1980-12-17",1600.00,1400.00,10);

//删除员工表中姓名为"Tom"的数据

//条件关键字where(在一条sql中只能出现一次)


xxxxxxxxxx
1
1
delete from emp where ename = "Tom";

//修改员工表中SMITH的薪水为850；


xxxxxxxxxx
1
1
update emp set SAL = 850 where ename = "SMITH";

3.带条件查询

//查询一个字段

//查询多个字段


xxxxxxxxxx
38
1
select ename from emp;
2
+--------+                         
3
| ename  |
4
+--------+
5
| SMITH  |
6
| ALLEN  |
7
| WARD   |
8
| JONES  |
9
| MARTIN |
10
| BLAKE  |
11
| CLARK  |
12
| SCOTT  |
13
| KING   |
14
| TURNER |
15
| ADAMS  |
16
| JAMES  |
17
| FORD   |
18
| MILLER |
19
+--------+                        
20
select ename,job from emp;
21
+--------+-----------+
22
| ename  | job       |
23
+--------+-----------+
24
| SMITH  | CLERK     |
25
| ALLEN  | SALESMAN  |
26
| WARD   | SALESMAN  |
27
| JONES  | MANAGER   |
28
| MARTIN | SALESMAN  |
29
| BLAKE  | MANAGER   |
30
| CLARK  | MANAGER   |
31
| SCOTT  | ANALYST   |
32
| KING   | PRESIDENT |
33
| TURNER | SALESMAN  |
34
| ADAMS  | CLERK     |
35
| JAMES  | CLERK     |
36
| FORD   | ANALYST   |
37
| MILLER | CLERK     |
38
+--------+-----------+

//计算员工的年薪（月薪*12）


xxxxxxxxxx
39
1
select ename,SAL*12 from emp;
2
+--------+----------+
3
| ename  | SAL*12   |
4
+--------+----------+
5
| SMITH  | 10200.00 |
6
| ALLEN  | 19200.00 |
7
| WARD   | 15000.00 |
8
| JONES  | 35700.00 |
9
| MARTIN | 15000.00 |
10
| BLAKE  | 34200.00 |
11
| CLARK  | 29400.00 |
12
| SCOTT  | 36000.00 |
13
| KING   | 60000.00 |
14
| TURNER | 18000.00 |
15
| ADAMS  | 13200.00 |
16
| JAMES  | 11400.00 |
17
| FORD   | 36000.00 |
18
| MILLER | 15600.00 |
19
+--------+----------+
20
select ename,SAL*12 as '年薪' from emp;
21
//或者 select ename,SAL*12 '年薪' from emp;
22
+--------+----------+
23
| ename  | 年薪     |
24
+--------+----------+
25
| SMITH  | 10200.00 |
26
| ALLEN  | 19200.00 |
27
| WARD   | 15000.00 |
28
| JONES  | 35700.00 |
29
| MARTIN | 15000.00 |
30
| BLAKE  | 34200.00 |
31
| CLARK  | 29400.00 |
32
| SCOTT  | 36000.00 |
33
| KING   | 60000.00 |
34
| TURNER | 18000.00 |
35
| ADAMS  | 13200.00 |
36
| JAMES  | 11400.00 |
37
| FORD   | 36000.00 |
38
| MILLER | 15600.00 |
39
+--------+----------+

//查询出薪资大于2000的员工


xxxxxxxxxx
11
1
select ename,SAL from emp where SAL > 2000;
2
+-------+---------+
3
| ename | SAL     |
4
+-------+---------+
5
| JONES | 2975.00 |
6
| BLAKE | 2850.00 |
7
| CLARK | 2450.00 |
8
| SCOTT | 3000.00 |
9
| KING  | 5000.00 |
10
| FORD  | 3000.00 |
11
+-------+---------+

Sql语句条件运算符（where后面的）

运算符	说明
=	等于
<>或!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between … and ….	两个值之间,等同于 >= and <=
is null	为null（is not null 不为空）
and	并且
or	或者
in	包含，相当于多个or（not in不在这个范围中）
not	not可以取非，主要用在is 或in中
like	like称为模糊查询，支持%或下划线匹配 %匹配任意个字符下划线，一个下划线只匹配一个字符,"_"代表占位符

//部门编号不等于20的员工


xxxxxxxxxx
14
1
select * from emp where deptno != 20;
2
+-------+--------+-----------+------+------------+---------+---------+--------+
3
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
4
+-------+--------+-----------+------+------------+---------+---------+--------+
5
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
6
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
7
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
8
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
9
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
10
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
11
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
12
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
13
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
14
+-------+--------+-----------+------+------------+---------+---------+--------+

//查找薪资大于500小于1000的员工


xxxxxxxxxx
8
1
select * from emp where SAL > 500 and SAL < 1000;
2
//或 select * from emp where sal between 500 and 1000;
3
+-------+-------+-------+------+------------+--------+------+--------+
4
| EMPNO | ENAME | JOB   | MGR  | HIREDATE   | SAL    | COMM | DEPTNO |
5
+-------+-------+-------+------+------------+--------+------+--------+
6
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 850.00 | NULL |     20 |
7
|  7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL |     30 |
8
+-------+-------+-------+------+------------+--------+------+--------+

//查询出COMM字段不为空的数据


xxxxxxxxxx
10
1
select * from emp where comm is not null;
2
//查询为空的: select * from emp where comm is null;
3
+-------+--------+----------+------+------------+---------+---------+--------+
4
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
5
+-------+--------+----------+------+------------+---------+---------+--------+
6
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
7
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
8
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
9
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
10
+-------+--------+----------+------+------------+---------+---------+--------+

//查询出薪资小于1000或者薪资大于2000的员工


xxxxxxxxxx
13
1
select * from emp where sal < 1000 or sal > 2000;
2
+-------+-------+-----------+------+------------+---------+------+--------+
3
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
4
+-------+-------+-----------+------+------------+---------+------+--------+
5
|  7369 | SMITH | CLERK     | 7902 | 1980-12-17 |  850.00 | NULL |     20 |
6
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
7
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
8
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
9
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
10
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
11
|  7900 | JAMES | CLERK     | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
12
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
13
+-------+-------+-----------+------+------------+---------+------+--------+

//in（包含的数据），查询出员工编号是7369、7839、7788的员工 in()

//不包含：not in not in()


xxxxxxxxxx
8
1
select * from emp where empno in (7369,7839,7788);
2
+-------+-------+-----------+------+------------+---------+------+--------+
3
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
4
+-------+-------+-----------+------+------------+---------+------+--------+
5
|  7369 | SMITH | CLERK     | 7902 | 1980-12-17 |  850.00 | NULL |     20 |
6
|  7788 | SCOTT | ANALYST   | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
7
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
8
+-------+-------+-----------+------+------------+---------+------+--------+

Like可以实现模糊查询，like支持%和下划线匹配
查询姓名以M开头所有的员工


xxxxxxxxxx
25
1
select ename as '员工姓名' from emp where ename like "M%";
2
+----------+
3
| 员工姓名 |
4
+----------+
5
| MARTIN   |
6
| MILLER   |
7
+----------+
8
//查询以S结尾的员工姓名
9
select ename as '员工姓名' from emp where ename like "%S";
10
+----------+
11
| 员工姓名 |
12
+----------+
13
| JONES    |
14
| ADAMS    |
15
| JAMES    |
16
+----------+
17
//查询员工姓名中包含O的员工姓名
18
select ename as '员工姓名' from emp where ename like "%O%";
19
+----------+
20
| 员工姓名 |
21
+----------+
22
| JONES    |
23
| SCOTT    |
24
| FORD     |
25
+----------+

4.排序数据

排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面

//按照薪水由小到大排序 order by 排序关键字（默认是升序）

注意：order by后面的排序依据字段不加引号


xxxxxxxxxx
21
1
select ename '员工姓名',SAL '薪水' from emp order by SAL asc;//asc为正序关键字可以省略
2
+----------+---------+
3
| 员工姓名 | 薪水    |
4
+----------+---------+
5
| SMITH    |  850.00 |
6
| JAMES    |  950.00 |
7
| ADAMS    | 1100.00 |
8
| WARD     | 1250.00 |
9
| MARTIN   | 1250.00 |
10
| MILLER   | 1300.00 |
11
| TURNER   | 1500.00 |
12
| ALLEN    | 1600.00 |
13
| CLARK    | 2450.00 |
14
| BLAKE    | 2850.00 |
15
| JONES    | 2975.00 |
16
| SCOTT    | 3000.00 |
17
| FORD     | 3000.00 |
18
| KING     | 5000.00 |
19
+----------+---------+
20
select ename '员工姓名',SAL '薪水' from emp order by SAL desc;
21
//此为倒序排序 使用desc关键字

//查询出薪资大于1000的员工，然后按照薪水降序排列


xxxxxxxxxx
17
1
select ename '员工姓名',SAL '薪水' from emp where SAL > 1000 order by SAL desc;
2
+----------+---------+
3
| 员工姓名 | 薪水    |
4
+----------+---------+
5
| KING     | 5000.00 |
6
| SCOTT    | 3000.00 |
7
| FORD     | 3000.00 |
8
| JONES    | 2975.00 |
9
| BLAKE    | 2850.00 |
10
| CLARK    | 2450.00 |
11
| ALLEN    | 1600.00 |
12
| TURNER   | 1500.00 |
13
| MILLER   | 1300.00 |
14
| WARD     | 1250.00 |
15
| MARTIN   | 1250.00 |
16
| ADAMS    | 1100.00 |
17
+----------+---------+

//按照job 和 sal 倒序（满足前面的条件，再满足后面的条件，排序具有优先级）


xxxxxxxxxx
19
1
select * from emp order by job desc,sal desc;
2
+-------+--------+-----------+------+------------+---------+---------+--------+
3
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
4
+-------+--------+-----------+------+------------+---------+---------+--------+
5
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
6
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
7
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
8
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
9
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
10
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
11
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
12
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
13
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
14
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
15
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
16
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  850.00 |    NULL |     20 |
17
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
18
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
19
+-------+--------+-----------+------+------------+---------+---------+--------+

5.分组函数/聚合函数/多行处理函数

函数	作用
count	取得记录数
sum	求和
avg	取平均
max	取最大的数
min	取最小的数

//统计emp这张表中有多少个员工 count(*) 求一张表中的总记录条数


xxxxxxxxxx
19
1
select count(1) '总记录条数' from emp; //作用一样，但是执行效率高
2
+------------+
3
| 总记录条数 |
4
+------------+
5
|         14 |
6
+------------+
7
mysql> select count(comm) from emp;//统计comm有记录的条数
8
+-------------+
9
| count(comm) |
10
+-------------+
11
|           4 |
12
+-------------+
13
select count(distinct job) '岗位个数' from emp;
14
//统计岗位个数，distinct关键字起到去重作用
15
+----------+
16
| 岗位个数 |
17
+----------+
18
|        5 |
19
+----------+

//取得薪水和津贴的合计,ifnull函数表示将空值用后面的数字替换


xxxxxxxxxx
6
1
select sum(sal+IFNULL(comm,0)) '薪水和津贴的合计' from emp;
2
+------------------+
3
| 薪水和津贴的合计 |
4
+------------------+
5
|         31275.00 |
6
+------------------+

//求出员工薪资大于2000的薪资总和


xxxxxxxxxx
6
1
select sum(SAL) '薪资总和' from emp where SAL > 2000;
2
+----------+
3
| 薪资总和 |
4
+----------+
5
| 19275.00 |
6
+----------+

//求出薪资小于2000的薪资平均值


xxxxxxxxxx
6
1
select avg(SAL) '薪资平均值' from emp where SAL < 2000;
2
+-------------+
3
| 薪资平均值  |
4
+-------------+
5
| 1225.000000 |
6
+-------------+

//求出薪资的最大值,最小值


xxxxxxxxxx
6
1
select max(SAL) '薪资最大值',min(SAL) '薪资最小值' from emp;
2
+------------+------------+
3
| 薪资最大值 | 薪资最小值 |
4
+------------+------------+
5
|    5000.00 |     850.00 |
6
+------------+------------+

//组合聚合函数


xxxxxxxxxx
6
1
select count(1),sum(sal),avg(sal),max(sal),min(sal) from emp;
2
+----------+----------+-------------+----------+----------+
3
| count(1) | sum(sal) | avg(sal)    | max(sal) | min(sal) |
4
+----------+----------+-------------+----------+----------+
5
|       14 | 29075.00 | 2076.785714 |  5000.00 |   850.00 |
6
+----------+----------+-------------+----------+----------+

6.分组查询

group by 关键字

//取得每个工作岗位的工资和，要求显示岗位名称和工资总和，按照薪水的倒序进行排序


xxxxxxxxxx
10
1
select job '岗位',sum(SAL) '薪资总和' from emp group by job order by 薪资总和 desc;
2
+-----------+----------+
3
| 岗位      | 薪资总和 |
4
+-----------+----------+
5
| MANAGER   |  8275.00 |
6
| ANALYST   |  6000.00 |
7
| SALESMAN  |  5600.00 |
8
| PRESIDENT |  5000.00 |
9
| CLERK     |  4200.00 |
10
+-----------+----------+

//按照工作岗位和部门编码分组，取得的工资合计


xxxxxxxxxx
14
1
select job '工作岗位',deptNO '部门编码',sum(SAL) from emp group by job,deptNO;
2
+-----------+----------+----------+
3
| 工作岗位  | 部门编码 | sum(SAL) |
4
+-----------+----------+----------+
5
| ANALYST   |       20 |  6000.00 |
6
| CLERK     |       10 |  1300.00 |
7
| CLERK     |       20 |  1950.00 |
8
| CLERK     |       30 |   950.00 |
9
| MANAGER   |       10 |  2450.00 |
10
| MANAGER   |       20 |  2975.00 |
11
| MANAGER   |       30 |  2850.00 |
12
| PRESIDENT |       10 |  5000.00 |
13
| SALESMAN  |       30 |  5600.00 |
14
+-----------+----------+----------+

如果有group by 语句，那么在select语句后面只能跟分组函数+参与分组的字段

having关键字(分组之后的数据再过滤)

如果想对分组数据再进行过滤需要使用having子句

取得每个岗位的平均工资大于2000


xxxxxxxxxx
8
1
select job, avg(sal) from emp group by job having avg(sal) >2000;
2
+-----------+-------------+
3
| job       | avg(sal)    |
4
+-----------+-------------+
5
| ANALYST   | 3000.000000 |
6
| MANAGER   | 2758.333333 |
7
| PRESIDENT | 5000.000000 |
8
+-----------+-------------+

select语句总结

一个完整的select语句格式如下

select 字段
from 表名
where …….
group by ……..
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ……..

以上语句的执行顺序

首先执行where语句过滤原始数据
执行group by进行分组
执行having对分组数据进行操作
执行select选出数据
执行order by排序

原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。

多表查询：

在项目中，业务是很复杂的，那表与表之间的关系也很复杂，而且以后在公司中我们接触到的表会有几百张或者几千张，在进行多表查询的时候，我们要捋清楚表与表之间的关系

多表查询的时候一定要带条件进行查询，不然会出现笛卡尔积现象！

多表查询语法：

sql92：

emp 主表找到从表的外键deptno

dept 从表


xxxxxxxxxx
20
1
select * from emp,dept where emp.deptno = dept.deptno;
2
//映射关系：主表的外键 = 从表的主键
3
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
4
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | DEPTNO | DNAME      | LOC      |
5
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+
6
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  850.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
7
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |     30 | SALES      | CHICAGO  |
8
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |     30 | SALES      | CHICAGO  |
9
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
10
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |     30 | SALES      | CHICAGO  |
11
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
12
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
13
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
14
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
15
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |     30 | SALES      | CHICAGO  |
16
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
17
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |     30 | SALES      | CHICAGO  |
18
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |     20 | RESEARCH   | DALLAS   |
19
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |     10 | ACCOUNTING | NEW YORK |
20
+-------+--------+-----------+------+------------+---------+---------+--------+--------+------------+----------+

//查询每个员工所对应的部门


xxxxxxxxxx
19
1
select e.ename '员工姓名',d.dname '部门名称' from emp e,dept d where e.deptno = d.deptno;
2
+----------+------------+
3
| 员工姓名 | 部门名称   |
4
+----------+------------+
5
| SMITH    | RESEARCH   |
6
| ALLEN    | SALES      |
7
| WARD     | SALES      |
8
| JONES    | RESEARCH   |
9
| MARTIN   | SALES      |
10
| BLAKE    | SALES      |
11
| CLARK    | ACCOUNTING |
12
| SCOTT    | RESEARCH   |
13
| KING     | ACCOUNTING |
14
| TURNER   | SALES      |
15
| ADAMS    | RESEARCH   |
16
| JAMES    | SALES      |
17
| FORD     | RESEARCH   |
18
| MILLER   | ACCOUNTING |
19
+----------+------------+

//显示出薪资大于2000的员工,并显示出每个员工对应的部门(连接查询)


xxxxxxxxxx
11
1
select e.ename '员工姓名',e.SAL '薪水',d.dname '部门' from emp e,dept d where e.deptno = d.deptno and e.sal > 2000;
2
+----------+---------+------------+
3
| 员工姓名 | 薪水    | 部门       |
4
+----------+---------+------------+
5
| JONES    | 2975.00 | RESEARCH   |
6
| BLAKE    | 2850.00 | SALES      |
7
| CLARK    | 2450.00 | ACCOUNTING |
8
| SCOTT    | 3000.00 | RESEARCH   |
9
| KING     | 5000.00 | ACCOUNTING |
10
| FORD     | 3000.00 | RESEARCH   |
11
+----------+---------+------------+

sql99：

内连接

//显示出薪资大于2000的员工,并显示出每个员工对应的部门 inner join 等值连接（效率高 inner可以不写）


xxxxxxxxxx
15
1
select e.ename '员工姓名' , e.sal '薪水',d.dname '部门' 
2
from emp e 
3
inner join dept d 
4
on e.deptno = d.deptno 
5
where e.sal > 2000;
6
+----------+---------+------------+
7
| 员工姓名 | 薪水    | 部门       |
8
+----------+---------+------------+
9
| JONES    | 2975.00 | RESEARCH   |
10
| BLAKE    | 2850.00 | SALES      |
11
| CLARK    | 2450.00 | ACCOUNTING |
12
| SCOTT    | 3000.00 | RESEARCH   |
13
| KING     | 5000.00 | ACCOUNTING |
14
| FORD     | 3000.00 | RESEARCH   |
15
+----------+---------+------------+

外连接

左外连接 left join


xxxxxxxxxx
1
1
select 查询的数据 from 表1(主表) left join 表2(从表) on 表1的条件 = 表2的条件;

右外连接 right join


xxxxxxxxxx
1
1
select 查询的数据 from 表1(从表) right join 表2(主表) on 表1的条件 = 表2的条件;

在外连接中，主表的数据要求全部显示出来，即使在从表中没用数据与之匹配，那该数据也必须显示出来，从从表中不能匹配的数据用null来代替

//显示员工信息，并显示所属的部门名称，如果某一个部门没有员工，那么该部门也必须显示出来


xxxxxxxxxx
20
1
select e.*,d.dname '部门' from emp e right join dept d on e.deptno = d.deptno;
2
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
3
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | 部门       |
4
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
5
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  850.00 |    NULL |     20 | RESEARCH   |
6
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | SALES      |
7
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | SALES      |
8
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 | RESEARCH   |
9
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 | SALES      |
10
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 | SALES      |
11
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | ACCOUNTING |
12
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 | RESEARCH   |
13
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 | ACCOUNTING |
14
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 | SALES      |
15
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | RESEARCH   |
16
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | SALES      |
17
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | RESEARCH   |
18
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 | ACCOUNTING |
19
|  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL | OPERATIONS |
20
+-------+--------+-----------+------+------------+---------+---------+--------+------------+

//查询出所有的员工的薪资以及员工的薪水等级显示每个员工所对应的部门


xxxxxxxxxx
24
1
select 
2
e.ename '员工姓名',e.sal '员工薪资',s.grade '薪水等级',d.dname '所属部门' 
3
from emp e left join dept d 
4
on e.deptno = d.deptno
5
left join salgrade s
6
on e.sal between s.losal and s.hisal;
7
+----------+----------+----------+------------+
8
| 员工姓名 | 员工薪资 | 薪水等级 | 所属部门   |
9
+----------+----------+----------+------------+
10
| SMITH    |   850.00 |        1 | RESEARCH   |
11
| ADAMS    |  1100.00 |        1 | RESEARCH   |
12
| JAMES    |   950.00 |        1 | SALES      |
13
| MILLER   |  1300.00 |        2 | ACCOUNTING |
14
| WARD     |  1250.00 |        2 | SALES      |
15
| MARTIN   |  1250.00 |        2 | SALES      |
16
| ALLEN    |  1600.00 |        3 | SALES      |
17
| TURNER   |  1500.00 |        3 | SALES      |
18
| CLARK    |  2450.00 |        4 | ACCOUNTING |
19
| JONES    |  2975.00 |        4 | RESEARCH   |
20
| SCOTT    |  3000.00 |        4 | RESEARCH   |
21
| FORD     |  3000.00 |        4 | RESEARCH   |
22
| BLAKE    |  2850.00 |        4 | SALES      |
23
| KING     |  5000.00 |        5 | ACCOUNTING |
24
+----------+----------+----------+------------+

通过子查询的方式查表

//查询出薪水大于平均薪水的员工的薪水


xxxxxxxxxx
12
1
select ename '员工姓名',sal '员工薪水' from emp
2
where sal > (select avg(e.sal) '平均薪水' from emp e);
3
+----------+----------+
4
| 员工姓名 | 员工薪水 |
5
+----------+----------+
6
| JONES    |  2975.00 |
7
| BLAKE    |  2850.00 |
8
| CLARK    |  2450.00 |
9
| SCOTT    |  3000.00 |
10
| KING     |  5000.00 |
11
| FORD     |  3000.00 |
12
+----------+----------+

//查询每个工作岗位对应的薪资总和并且降序排序


xxxxxxxxxx
10
1
select e.job '工作岗位',sum(e.sal) '工资合计' from emp e group by e.job order by 工资合计 desc;
2
+-----------+----------+
3
| 工作岗位  | 工资合计 |
4
+-----------+----------+
5
| MANAGER   |  8275.00 |
6
| ANALYST   |  6000.00 |
7
| SALESMAN  |  5600.00 |
8
| PRESIDENT |  5000.00 |
9
| CLERK     |  4200.00 |
10
+-----------+----------+

//查询每个员工所对应的领导姓名


xxxxxxxxxx
19
1
select e1.ename '员工姓名',e2.ename '领导姓名' from emp e1
2
inner join emp e2 on e1.MGR = e2.EMPNO;
3
+----------+----------+
4
| 员工姓名 | 领导姓名 |
5
+----------+----------+
6
| SMITH    | FORD     |
7
| ALLEN    | BLAKE    |
8
| WARD     | BLAKE    |
9
| JONES    | KING     |
10
| MARTIN   | BLAKE    |
11
| BLAKE    | KING     |
12
| CLARK    | KING     |
13
| SCOTT    | JONES    |
14
| TURNER   | BLAKE    |
15
| ADAMS    | SCOTT    |
16
| JAMES    | BLAKE    |
17
| FORD     | JONES    |
18
| MILLER   | CLARK    |
19
+----------+----------+

7.子查询

子查询就是嵌套的select语句，可以理解为子查询是一张表

在where语句中使用子查询，也就是在where语句中加入select语句

//查询每个部门平均薪水所对应的薪水等级


xxxxxxxxxx
17
1
select y.部门名称,s.grade '薪水等级' from 
2

3
(select d.dname '部门名称',avg(e.sal) '平均薪水' from
4

5
emp e left join dept d on e.deptno = d.deptno 
6

7
group by d.dname) y left join salgrade s on
8

9
y.平均薪水 between s.losal and hisal;
10

11
+------------+----------+
12
| 部门名称   | 薪水等级 |
13
+------------+----------+
14
| SALES      |        3 |
15
| ACCOUNTING |        4 |
16
| RESEARCH   |        4 |
17
+------------+----------+

分页:

limit关键字(在sql语句最后面)


xxxxxxxxxx
5
1
select * from emp limit 起始索引位置,分页单位;
2

3
1.select * from emp limit 0,3;
4
2.select * from emp limit 3,3;
5
3.select * from emp limit 6,3;

起始索引位置：从哪个位置开始查（小标索引是从0开始）

分页单位：查询多少条数据

起始索引位置跟当前页的关系(平均分页):

起 始 索 引 位 置 = （ 当 前 页 - 1 ） * 分 页 单 位

//根据分页来查询5-10条的数据


xxxxxxxxxx
1
1
select * from emp limit 4,6;

//查询出每个员工的所属部门，薪水，薪水等级，取到薪资最低的三个员工


xxxxxxxxxx
12
1
select e.ename '员工姓名',d.dname '部门名称',
2
e.sal '薪水',s.GRADE '薪水等级' FROM
3
emp e left join dept d on e.DEPTNO = d.DEPTNO
4
left join salgrade s on e.sal BETWEEN s.LOSAL and s.HISAL
5
ORDER BY e.SAL LIMIT 0,3;
6
+----------+----------+---------+----------+
7
| 员工姓名 | 部门名称 | 薪水    | 薪水等级 |
8
+----------+----------+---------+----------+
9
| SMITH    | RESEARCH |  850.00 |        1 |
10
| JAMES    | SALES    |  950.00 |        1 |
11
| ADAMS    | RESEARCH | 1100.00 |        1 |
12
+----------+----------+---------+----------+