MySQL 的查询

创建三张表

雇员表：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> create table emp(empmo numeric(4) not null,ename varchar(10),job varchar(9),mgr numeric(4),hiredate datetime,sal numeric(7, 2),comm numeric(7, 2),deptno numeric(2));
Query OK, 0 rows affected (0.05 sec)
 
mysql> insert into emp values(7369,"SMITH","CLERK",7902,'1980-12-17',800, null, 20);
Query OK, 1 row affected (0.02 sec)
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, '1982-12-09', 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, '1983-01-12', 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);

　　

部门表：--部门编号，部门名称，部门所在位置

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> create table dept(deptno numeric(2),dname varchar(14),loc varchar(13));
Query OK, 0 rows affected (0.03 sec)
 
mysql> insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into dept values (20, 'RESEARCH', 'DALLAS');
Query OK, 1 row affected (0.05 sec)
 
mysql> insert into dept values (30, 'SALES', 'CHICAGO');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into dept values (40, 'OPERATIONS', 'BOSTON');
Query OK, 1 row affected (0.01 sec)

　　

工资等级表：--等级，这个等级里面的最低工资，这个等级里面的最高工资

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> create table salgrade (grade numeric,losal numeric,hisal numeric);
Query OK, 0 rows affected (0.08 sec)
 
mysql> insert into salgrade values (1,700,1200);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into salgrade values (2,1201,1400);
Query OK, 1 row affected (0.04 sec)
 
mysql> insert into salgrade values (3,1401, 2000);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into salgrade values (4,2001, 3000);
Query OK, 1 row affected (0.06 sec)
 
mysql> insert into salgrade values (5,3001, 9999);
Query OK, 1 row affected (0.01 sec)

select * from 表名;  --查询出该表名下的所有数据

* 代表所有字段

select [字段列表(可以是多个)，表达式，函数] from 表名;

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select * from emp;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empmo | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 00:00:00 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

　 查询某些字段数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select empmo,ename,sal from emp;
+-------+--------+---------+
| empmo | ename  | sal     |
+-------+--------+---------+
|  7369 | SMITH  |  800.00 |
|  7499 | ALLEN  | 1600.00 |
|  7521 | WARD   | 1250.00 |
|  7566 | JONES  | 2975.00 |
|  7654 | MARTIN | 1250.00 |
|  7698 | BLAKE  | 2850.00 |
|  7782 | CLARK  | 2450.00 |
|  7788 | SCOTT  | 3000.00 |
|  7839 | KING   | 5000.00 |
|  7844 | TURNER | 1500.00 |
|  7876 | ADAMS  | 1100.00 |
|  7900 | JAMES  |  950.00 |
|  7902 | FORD   | 3000.00 |
|  7934 | MILLER | 1300.00 |
+-------+--------+---------+
14 rows in set (0.00 sec)

select 表达式[算术表达式] from 表名;  查询每个人的年新；可以使用：加减乘除(+、-、*、\)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select ename,sal*12 from emp;
+--------+----------+
| ename  | sal*12   |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.01 sec)

取余[%]：什么是取余？求余数能整除的余数为0，不能整除的余数为剩下的余就是整除剩下的余数。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select ename,sal%100 from emp;
+--------+---------+
| ename  | sal%100 |
+--------+---------+
| SMITH  |    0.00 |
| ALLEN  |    0.00 |
| WARD   |   50.00 |
| JONES  |   75.00 |
| MARTIN |   50.00 |
| BLAKE  |   50.00 |
| CLARK  |   50.00 |
| SCOTT  |    0.00 |
| KING   |    0.00 |
| TURNER |    0.00 |
| ADAMS  |    0.00 |
| JAMES  |   50.00 |
| FORD   |    0.00 |
| MILLER |    0.00 |
+--------+---------+
14 rows in set (0.00 sec)

去重[distinct]：

?
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
mysql> select deptno from emp;
+--------+
| deptno |
+--------+
|     20 |
|     30 |
|     30 |
|     20 |
|     30 |
|     30 |
|     10 |
|     20 |
|     10 |
|     30 |
|     20 |
|     30 |
|     20 |
|     10 |
+--------+
14 rows in set (0.00 sec)
 
mysql> select distinct deptno from emp;
+--------+
| deptno |
+--------+
|     20 |
|     30 |
|     10 |
+--------+
3 rows in set (0.00 sec)

　　条件查询：where条件表达式等值比较 大于 小于 大于等于 小于等于 不等于

等值比较：比较数值的时候直接比较

?
1
2
3
4
5
6
7
8
9
mysql> select * from emp where deptno = 10;
+-------+--------+-----------+------+---------------------+---------+------+--------+
| empmo | ename  | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+--------+-----------+------+---------------------+---------+------+--------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL |     10 |
+-------+--------+-----------+------+---------------------+---------+------+--------+
3 rows in set (0.00 sec)

比较字符串的时候必须有对应的引号

?
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
34
mysql> select ename from emp where ename = "CLARK";
+-------+
| ename |
+-------+
| CLARK |
+-------+
1 row in set (0.00 sec)
 
mysql> select * from emp where ename = "CLARK";
+-------+-------+---------+------+---------------------+---------+------+--------+
| empmo | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+-------+-------+---------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)
 
mysql> select * from emp where hiredate = '1981';
Empty set, 2 warnings (0.00 sec)
 
mysql> select * from emp where hiredate = '1981-06-09';
+-------+-------+---------+------+---------------------+---------+------+--------+
| empmo | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+-------+-------+---------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)
 
mysql> select * from emp where hiredate = '1981/06/09';
+-------+-------+---------+------+---------------------+---------+------+--------+
| empmo | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+-------+-------+---------+------+---------------------+---------+------+--------+
1 row in set (0.01 sec)

　　大于比较：

?
1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * from emp where sal > '2000';
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empmo | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
6 rows in set (0.00 sec)

　　大于等于比较：

?
1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * from emp where sal >= '2450';
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empmo | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
6 rows in set (0.00 sec)

　　不等于比较：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select * from emp where sal <> '2450';
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empmo | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 00:00:00 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
13 rows in set (0.03 sec)

　多条件查询：并且and查询大于1600并且小于等于3000的

?
1
2
3
4
5
6
7
8
9
10
11
mysql> select * from emp where sal > 1600 and sal <= 3000;
+-------+-------+---------+------+---------------------+---------+------+--------+
| empmo | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
|  7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
|  7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL |     20 |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+---------------------+---------+------+--------+
5 rows in set (0.00 sec)

　查询大于1600并且小于等于3000的数据并且是10号部门的

?
1
2
3
4
5
6
7
mysql> select * from emp where sal >1600 and sal <= 3000 and deptno = 10;
+-------+-------+---------+------+---------------------+---------+------+--------+
| empmo | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+-------+-------+---------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)

　或者 or （or也可以写多个）大于3000小于1000

?
1
2
3
4
5
6
7
8
9
mysql> select * from emp where sal > 3000 or sal < 1000;
+-------+-------+-----------+------+---------------------+---------+------+--------+
| empmo | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+-----------+------+---------------------+---------+------+--------+
|  7369 | SMITH | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
|  7900 | JAMES | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 | NULL |     30 |
+-------+-------+-----------+------+---------------------+---------+------+--------+
3 rows in set (0.00 sec)

in关键字：代表在这个取值中只要有一个匹配就是符合条件

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select * from emp where sal = 800 or sal = 950  or sal = 1600;
+-------+-------+----------+------+---------------------+---------+--------+--------+
| empmo | ename | job      | mgr  | hiredate            | sal     | comm   | deptno |
+-------+-------+----------+------+---------------------+---------+--------+--------+
|  7369 | SMITH | CLERK    | 7902 | 1980-12-17 00:00:00 |  800.00 |   NULL |     20 |
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 |
|  7900 | JAMES | CLERK    | 7698 | 1981-12-03 00:00:00 |  950.00 |   NULL |     30 |
+-------+-------+----------+------+---------------------+---------+--------+--------+
3 rows in set (0.00 sec)
 
mysql> select * from emp where sal in (800,950,1600);
+-------+-------+----------+------+---------------------+---------+--------+--------+
| empmo | ename | job      | mgr  | hiredate            | sal     | comm   | deptno |
+-------+-------+----------+------+---------------------+---------+--------+--------+
|  7369 | SMITH | CLERK    | 7902 | 1980-12-17 00:00:00 |  800.00 |   NULL |     20 |
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 |
|  7900 | JAMES | CLERK    | 7698 | 1981-12-03 00:00:00 |  950.00 |   NULL |     30 |
+-------+-------+----------+------+---------------------+---------+--------+--------+
3 rows in set (0.00 sec)

in与and 

?
1
2
3
4
5
6
7
mysql> select * from emp where sal in (800,950,1600) and deptno <> 30;
+-------+-------+-------+------+---------------------+--------+------+--------+
| empmo | ename | job   | mgr  | hiredate            | sal    | comm | deptno |
+-------+-------+-------+------+---------------------+--------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL |     20 |
+-------+-------+-------+------+---------------------+--------+------+--------+
1 row in set (0.00 sec)

not in关键字不在这个范围区间之内的

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> select * from emp where sal not in (800,950,1600);
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empmo | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 00:00:00 | 1100.00 |    NULL |     20 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
11 rows in set (0.03 sec)

between关键字相当于大于等于，小于等于。

?
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
mysql> select * from emp where sal >= 1600 and sal <=3000;
+-------+-------+----------+------+---------------------+---------+--------+--------+
| empmo | ename | job      | mgr  | hiredate            | sal     | comm   | deptno |
+-------+-------+----------+------+---------------------+---------+--------+--------+
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 |
|  7566 | JONES | MANAGER  | 7839 | 1981-04-02 00:00:00 | 2975.00 |   NULL |     20 |
|  7698 | BLAKE | MANAGER  | 7839 | 1981-05-01 00:00:00 | 2850.00 |   NULL |     30 |
|  7782 | CLARK | MANAGER  | 7839 | 1981-06-09 00:00:00 | 2450.00 |   NULL |     10 |
|  7788 | SCOTT | ANALYST  | 7566 | 1982-12-09 00:00:00 | 3000.00 |   NULL |     20 |
|  7902 | FORD  | ANALYST  | 7566 | 1981-12-03 00:00:00 | 3000.00 |   NULL |     20 |
+-------+-------+----------+------+---------------------+---------+--------+--------+
6 rows in set (0.00 sec)
 
mysql> select * from emp where sal between 1600 and 3000;
+-------+-------+----------+------+---------------------+---------+--------+--------+
| empmo | ename | job      | mgr  | hiredate            | sal     | comm   | deptno |
+-------+-------+----------+------+---------------------+---------+--------+--------+
|  7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 |
|  7566 | JONES | MANAGER  | 7839 | 1981-04-02 00:00:00 | 2975.00 |   NULL |     20 |
|  7698 | BLAKE | MANAGER  | 7839 | 1981-05-01 00:00:00 | 2850.00 |   NULL |     30 |
|  7782 | CLARK | MANAGER  | 7839 | 1981-06-09 00:00:00 | 2450.00 |   NULL |     10 |
|  7788 | SCOTT | ANALYST  | 7566 | 1982-12-09 00:00:00 | 3000.00 |   NULL |     20 |
|  7902 | FORD  | ANALYST  | 7566 | 1981-12-03 00:00:00 | 3000.00 |   NULL |     20 |
+-------+-------+----------+------+---------------------+---------+--------+--------+
6 rows in set (0.00 sec)

空永远不等于空null 和 null 做等值判断时永远是假;判断一个字段数值是否是空需要用到关键字 is 

?
1
2
3
4
5
6
7
8
9
10
mysql> select * from emp where comm is not null;--判断一个字段的数值不为null需要用到关键
+-------+--------+----------+------+---------------------+---------+---------+--------+
| empmo | ename  | job      | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+----------+------+---------------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
+-------+--------+----------+------+---------------------+---------+---------+--------+
4 rows in set (0.00 sec)

　显示查询询列为空的数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select * from emp where comm is null;
+-------+--------+-----------+------+---------------------+---------+------+--------+
| empmo | ename  | job       | mgr  | hiredate            | sal     | comm | deptno |
+-------+--------+-----------+------+---------------------+---------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL |     10 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL |     10 |
+-------+--------+-----------+------+---------------------+---------+------+--------+
10 rows in set (0.02 sec)

　　

posted @   烟雨楼台，行云流水  阅读(250)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· 葡萄城 AI 搜索升级：DeepSeek 加持，客户体验更智能
· 什么是nginx的强缓存和协商缓存
· 一文读懂知识蒸馏
历史上的今天：
2019-04-08 docker 镜像仓库的安装与使用
点击右上角即可分享
微信分享提示