1、DQL(数据查询语言)
在日常的使用中,数据库查询语言基本高达80%的使用率,人们经常使用查询语言查到自己需要的信息。在进行查询时主要使用select语句。具体语法:select 字段名1,字段名2,字段名3,… where 条件 group by 分组字段 having 筛选条件 order by asc/desc
执行顺序:
select 要查询的字段 5
from 要在那张表中查询 1
where 查询的条件是什么 2
group by 按着什么分组 3
having 对分组后的数据进行过滤 4
order by 对查询到的结果排序,默认是升序(asc) 但可以更改 desc------------降序 6
2、简单查询
简单查询时最基本也是最简单的查询,一般包括对一个字段查询、对多个字段查询、查询全部。
2.1 单个字段查询
对单个字段查询就是每次只查询一个字段。
----------->>>> 案例1: 查询EMP表中员工名字。
mysql> SELECT ENAME FROM EMP;
+--------+
| ENAME |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+--------+
14 rows in set
---------->>>>案例2: 查询EMP表中员工的工资大于2000的员工的名字。
mysql> SELECT ENAME FROM EMP WHERE SAL > 2000;
+-------+
| ENAME |
+-------+
| JONES |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| FORD |
+-------+
6 rows in set
2.2 多个字段查询
多个字段查询是指同时可以查询多个字段。查询多个字段,select中的字段采用逗号间隔即可,最后一个字段,也就是在from前面的字段不能使用逗号了。
----------->>>>案例1:查询EMP表中员工名字、工资以及部门。
mysql> select ename,sal,job from emp;
+--------+------+-----------+
| ename | sal | job |
+--------+------+-----------+
| SMITH | 800 | CLERK |
| ALLEN | 1600 | SALESMAN |
| WARD | 1250 | SALESMAN |
| JONES | 2975 | MANAGER |
| MARTIN | 1250 | SALESMAN |
| BLAKE | 2850 | MANAGER |
| CLARK | 2450 | MANAGER |
| SCOTT | 3000 | ANALYST |
| KING | 5000 | PRESIDENT |
| TURNER | 1500 | SALESMAN |
| ADAMS | 1100 | CLERK |
| JAMES | 950 | CLERK |
| FORD | 3000 | ANALYST |
| MILLER | 1300 | CLERK |
+--------+------+-----------+
14 rows in set
----------->>>>>案例2:查询EMP表中员工的工资小于1500的员工的名字、工资以及部门。
mysql> select ename,sal,job from emp where sal < 1500;
+--------+------+----------+
| ename | sal | job |
+--------+------+----------+
| SMITH | 800 | CLERK |
| WARD | 1250 | SALESMAN |
| MARTIN | 1250 | SALESMAN |
| ADAMS | 1100 | CLERK |
| JAMES | 950 | CLERK |
| MILLER | 1300 | CLERK |
+--------+------+----------+
6 rows in set
2.3 1. 查询所有
查询数据表中所有的数据,此时可以使用 星号(*)来代替,但是不建议使用,天威这个时候编写的SQL语句可读性不好。
----------->>>>>案例:查询EMP表中所有的员工。
mysql> select * from emp;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 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-04-19 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 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 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set
3、 起别名查询
这个操作比较简单,就是在查询的时候如果不想用原来的名字,便可以起别名。
----------->>>>>案例:查询EMP表中员工的工资小于1500的员工的名字、工资以及部门并且要求ename=员工姓名,sal=员工薪资,job=部门(岗位)。
mysql> select ename as '员工姓名',sal as '员工薪资',job as '部门'from emp where sal < 1500;
+----------+----------+----------+
| 员工姓名 | 员工薪资 | 部门 |
+----------+----------+----------+
| SMITH | 800 | CLERK |
| WARD | 1250 | SALESMAN |
| MARTIN | 1250 | SALESMAN |
| ADAMS | 1100 | CLERK |
| JAMES | 950 | CLERK |
| MILLER | 1300 | CLERK |
+----------+----------+----------+
6 rows in set
其中as也可以省略
mysql> select ename '员工姓名',sal '员工薪资',job '部门'from emp where sal < 1500;
+----------+----------+----------+
| 员工姓名 | 员工薪资 | 部门 |
+----------+----------+----------+
| SMITH | 800 | CLERK |
| WARD | 1250 | SALESMAN |
| MARTIN | 1250 | SALESMAN |
| ADAMS | 1100 | CLERK |
| JAMES | 950 | CLERK |
| MILLER | 1300 | CLERK |
+----------+----------+----------+
6 rows in set
既然字段可以起别名,那么表同样也可以去别名。
4、 条件查询
4.1 运算符
在查询的时候可以根据一定的条件进行查询,条件一般用在where子句后面,即where 后面的条件。常见的运算符大致可以分为以下几类;
运算符 | 描述 |
---|---|
= | 等于, |
<>或!= | 不等于 |
< | 小于 一般用在数值比较中 |
<= | 小于等于,一般用在数值比较中 |
> | 大于,一般用在数值比较中 |
>= | 大于等于,一般用在数值比较中 |
between … and …. | 两个值之间,等同于 >= and <= |
is null | 为null |
is not null | 不为空 |
in | 包含,相当于多个or |
not in | 不在这个范围中 |
and | 并且 |
or | 或者 |
not | not可以取非,主要用在is 或in中 |
like | like称为模糊查询,支持%或下划线匹配 %匹配任意个字符 下划线,一个下划线只匹配一个字符 |
4.2 运算符分析
等号运算符(=):
-------------->>>>>>案例1;查询EMP表中员工工资等于3000的员工的名字和部门编号。
mysql> select ename,deptno from emp where sal=3000;
+-------+--------+
| ename | deptno |
+-------+--------+
| SCOTT | 20 |
| FORD | 20 |
+-------+--------+
2 rows in set
数值因为可以用英文状态下的单引号引起来,但是不建议使用。
mysql> select ename,deptno from emp where sal='3000';
+-------+--------+
| ename | deptno |
+-------+--------+
| SCOTT | 20 |
| FORD | 20 |
+-------+--------+
2 rows in set
------------->>>>>>>案例2:查询EMP表中员工job为SALESMAN 的员工的名字和工资。
mysql> select ename,sal from emp where job='salesman';
+--------+------+
| ename | sal |
+--------+------+
| ALLEN | 1600 |
| WARD | 1250 |
| MARTIN | 1250 |
| TURNER | 1500 |
+--------+------+
4 rows in set
字符类型的数值一定要用英文状态下的单引号引起来,否则会报错。
mysql> select ename,sal from emp where job = salesman;
1054 - Unknown column 'salesman' in 'where clause'
不等号运算符:
------------->>>>>>> 案例3:查询EMP表中员工部门号不等于10的员工的名字和工资。
1.使用不等于运算符 !=
mysql> select ename,sal from emp where deptno != 10;
+--------+------+
| ename | sal |
+--------+------+
| SMITH | 800 |
| ALLEN | 1600 |
| WARD | 1250 |
| JONES | 2975 |
| MARTIN | 1250 |
| BLAKE | 2850 |
| SCOTT | 3000 |
| TURNER | 1500 |
| ADAMS | 1100 |
| JAMES | 950 |
| FORD | 3000 |
+--------+------+
11 rows in set
1.使用不等于运算符 <>
mysql> select ename,sal from emp where deptno <> 10;
+--------+------+
| ename | sal |
+--------+------+
| SMITH | 800 |
| ALLEN | 1600 |
| WARD | 1250 |
| JONES | 2975 |
| MARTIN | 1250 |
| BLAKE | 2850 |
| SCOTT | 3000 |
| TURNER | 1500 |
| ADAMS | 1100 |
| JAMES | 950 |
| FORD | 3000 |
+--------+------+
11 rows in set
------------->>>>>>> 案例4:查询EMP表中员工job不等于SALESMAN的员工的名字和工资。
mysql> select ename,sal from emp where job <> 'salesman';
+--------+------+
| ename | sal |
+--------+------+
| SMITH | 800 |
| JONES | 2975 |
| BLAKE | 2850 |
| CLARK | 2450 |
| SCOTT | 3000 |
| KING | 5000 |
| ADAMS | 1100 |
| JAMES | 950 |
| FORD | 3000 |
| MILLER | 1300 |
+--------+------+
10 rows in set
小于运算符:
------------->>>>>>> 案例5;查询EMP表中员工工资小于2000的员工的名字和部门。
mysql> select ename,deptno from emp where sal < 2000;
+--------+--------+
| ename | deptno |
+--------+--------+
| SMITH | 20 |
| ALLEN | 30 |
| WARD | 30 |
| MARTIN | 30 |
| TURNER | 30 |
| ADAMS | 20 |
| JAMES | 30 |
| MILLER | 10 |
+--------+--------+
8 rows in set
小于等于运算符:
------------->>>>>>> 案例6;查询EMP表中员工工资小于等于2000的员工的名字和部门。
mysql> select ename,deptno from emp where sal <= 2000;
+--------+--------+
| ename | deptno |
+--------+--------+
| SMITH | 20 |
| ALLEN | 30 |
| WARD | 30 |
| MARTIN | 30 |
| TURNER | 30 |
| ADAMS | 20 |
| JAMES | 30 |
| MILLER | 10 |
+--------+--------+
8 rows in set
大于运算符:
------------->>>>>>> 案例7;查询EMP表中员工工资大于2000的员工的名字和部门。
mysql> select ename,deptno from emp where sal > 2000;
+-------+--------+
| ename | deptno |
+-------+--------+
| JONES | 20 |
| BLAKE | 30 |
| CLARK | 10 |
| SCOTT | 20 |
| KING | 10 |
| FORD | 20 |
+-------+--------+
6 rows in set
大于等于运算符:
------------->>>>>>> 案例8;查询EMP表中员工工资大于等于3000的员工的名字和部门。
mysql> select ename,deptno from emp where sal >= 3000;
+-------+--------+
| ename | deptno |
+-------+--------+
| SCOTT | 20 |
| KING | 10 |
| FORD | 20 |
+-------+--------+
3 rows in set
**between…and…运算符:**使用这个运算符时要注意左闭右开的原则。它包括最大值最小值。表示一个区间
------------->>>>>>>案例9:查询EMP表中员工工资在2000到3000之间的员工信息。
mysql> select * from emp where sal between 2000 and 3000;
+-------+-------+---------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+------+------+--------+
| 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-04-19 | 3000 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+-------+---------+------+------------+------+------+--------+
5 rows in set
或者使用普通运算符的组合形式,但是这些sql语句写起来比较麻烦。
mysql> select * from emp where sal >= 2000 and sal <= 3000;
+-------+-------+---------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+------+------+--------+
| 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-04-19 | 3000 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+-------+---------+------+------------+------+------+--------+
5 rows in set
is null 运算符: Null不等于空串,他只是说明这个字段的值可以不填写,但不是空。即NULL != 空串
------------->>>>>>>案例10:查询EMP表中员工津贴为null的员工的信息。
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-04-19 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 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
使用等于号时,查询不出来,因为Null是一个特殊的操作符
mysql> select * from emp where comm = null;
Empty set
如果使用等于号时,把null用单引号引起来,此时会被转换成0,即comm = 'null'会转化成 comm = 0
mysql> select * from emp where comm = 'null';
+-------+--------+----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+------+------+--------+
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
+-------+--------+----------+------+------------+------+------+--------+
1 row in set
**is not null 运算符:**与 is null 相反,经常使用 **not null 作为字段的约束条件,即非空约束,**他说明这个字段的值必须填写。
------------->>>>>>> 案例11:查询EMP表中员工津贴不为null的员工的信息。
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
使用不等于号时,查询不出来,因为Null是一个特殊的操作符
mysql> select * from emp where comm != null;
Empty set
如果使用不等于号时,把null用单引号引起来,此时会被转换成0,即comm != 'null'会转化成 comm != 0
mysql> select * from emp where comm != '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 |
+-------+--------+----------+------+------------+------+------+--------+
3 rows in set
```
or运算符 :或者,只要满足条件即可,当有多个条件时,只要满足其中一个条件就可以查询出来。
--------------->>>>>>>案例12:查询EMP表中员工的部门号为20或者薪资大等于2000的员工的信息。
mysql> select * from emp where deptno = 20 or sal >= 2000;
+-------+-------+-----------+------+------------+------+------+--------+
| 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-04-19 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+-------+-----------+------+------------+------+------+--------+
8 rows in set
and运算符: 并且,需要满足所有条件,当有多个条件时,要满足所有条件就可以查询出来。
--------------->>>>>>> 案例13:查询EMP表中员工的部门号为20并且薪资大等于2000的员工的信息。
mysql> select * from emp where deptno = 20 and sal >= 2000;
+-------+-------+---------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+-------+---------+------+------------+------+------+--------+
3 rows in set
in运算符 :包含,相当于在多个or,只是采用in书写起来比较简单。语法:in(A,B)包含A或者B 表示几个具体的值
--------------->>>>>>> 案例14:查询EMP表中员工的薪资为1500和3000的员工的信息。
mysql> select * from emp where sal in(1500,3000);
+-------+--------+----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+------+------+--------+
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+--------+----------+------+------------+------+------+--------+
3 rows in set
想当于多个or
mysql> select * from emp where sal = 1500 or sal = 3000;
+-------+--------+----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+----------+------+------------+------+------+--------+
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+--------+----------+------+------------+------+------+--------+
3 rows in set
not in运算符 :不包含,相当于在多个and,只是采用not in书写起来比较简单与in的作用相反。:not in(A,B)不包含A或者B
-------------->>>>>>>>案例15:查询EMP表中员工的薪资不包含1500和薪资不包含3000的员工的信息。
mysql> select * from emp where sal not in(1500,3000);
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
11 rows in set
想当于多个and
mysql> select * from emp where sal != 1500 and sal != 3000;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
11 rows in set
like运算符: 模糊查询,支持%或下划线匹配 %匹配任意个字符 下划线,一个下划线只匹配一个字符
------------>>>>>>>>案例16:查询EMP表中员工的名字中有S的员工的信息。
mysql> select * from emp where ename like '%S%';
+-------+-------+---------+------+------------+------+------+--------+
| 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 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
+-------+-------+---------+------+------------+------+------+--------+
5 rows in set
------------>>>>>>>>案例17:查询EMP表中员工的名字中第二个字为O的员工的信息。
mysql> select * from emp where ename like '_o%';
+-------+-------+---------+------+------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
+-------+-------+---------+------+------------+------+------+--------+
2 rows in set
---------------------------------------------------------------END---------------------------------------------------
MySQL数据库之DML(数据操作语言):--------------------------->>>>>>加载完成<<<<<<<----------------------------------