以下所有的操作示例都是基于EMP表:
[bjpowernode]>select * from EMP;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
1、简单的查询语句(DQL)
- select 字段名1,字段名2,···from 表明;
提示:
- 任何语句以;结尾
- sql语句不区分大小写
- sql语句字符串用单引号括起来
- as关键字可以省略
[bjpowernode]>select ENAME,sal*12 as '年薪' from EMP;
+--------+----------+
| ENAME | 年薪 |
+--------+----------+
| 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.00 sec)
2、条件查询:
语法格式:
- select 字段1,字段2··· from 表名 where 条件;
#查询工资等于5000的员工姓名
[bjpowernode]>select ename from EMP where sal = 5000;
+-------+
| ename |
+-------+
| KING |
+-------+
1 row in set (0.00 sec)
#查询SMITH的工资
[bjpowernode]>select sal from EMP where ename = 'SMITH';
+--------+
| sal |
+--------+
| 800.00 |
+--------+
1 row in set (0.00 sec)
#找出工资高于3000的员工
[bjpowernode]>select ename,sal from EMP where sal > 3000;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
+-------+---------+
1 row in set (0.00 sec)
#找出工资不等于3000的
[bjpowernode]>select ename,sal from EMP where sal != 3000;
或者[bjpowernode]>select ename,sal from EMP where sal <> 3000;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| MILLER | 1300.00 |
+--------+---------+
12 rows in set (0.00 sec)
#找出工资在1100到3000之间的员工
[bjpowernode]>select ename,sal from EMP where sal > 1100 and sal < 3000; #开区间,不包括1100,3000.
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+
8 rows in set (0.00 sec)
[bjpowernode]>select ename,sal from EMP where sal between 1100 and 3000; #闭区间,包括1100,3000.
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
11 rows in set (0.00 sec)
between...and:闭区间且左小右大,
#找出那些人津贴为null
空不是一个值,不能用等号衡量,必须使用is null或者is not null
[bjpowernode]>select ename,sal,comm from EMP where comm is null;
+--------+---------+------+
| ename | sal | comm |
+--------+---------+------+
| SMITH | 800.00 | NULL |
| JONES | 2975.00 | NULL |
| BLAKE | 2850.00 | NULL |
| CLARK | 2450.00 | NULL |
| SCOTT | 3000.00 | NULL |
| KING | 5000.00 | NULL |
| ADAMS | 1100.00 | NULL |
| JAMES | 950.00 | NULL |
| FORD | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
+--------+---------+------+
10 rows in set (0.00 sec)
========================================================================
错误写法
[bjpowernode]>select ename,sal,comm from EMP where comm = null;
Empty set (0.00 sec)
========================================================================
#找出那些人没有津贴
[bjpowernode]>select ename,sal,comm from EMP where comm is null or comm = 0;
+--------+---------+------+
| ename | sal | comm |
+--------+---------+------+
| SMITH | 800.00 | NULL |
| JONES | 2975.00 | NULL |
| BLAKE | 2850.00 | NULL |
| CLARK | 2450.00 | NULL |
| SCOTT | 3000.00 | NULL |
| KING | 5000.00 | NULL |
| TURNER | 1500.00 | 0.00 |
| ADAMS | 1100.00 | NULL |
| JAMES | 950.00 | NULL |
| FORD | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
+--------+---------+------+
11 rows in set (0.00 sec)
#找出工作岗位是MANAGER和SALESMAN的员工
[bjpowernode]>select ename,job from EMP where job = 'MANAGER' or job = 'SALESMAN';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
#and和or联合使用:找出薪资大于1000并且部门编号是20或30部门的员工
错误示例:
[bjpowernode]>select ename,sal,deptno from EMP where sal > 1000 and deptno = 20 or deptno = 30;
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| SCOTT | 3000.00 | 20 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| JAMES | 950.00 | 30 | #此条不应该出现,所以and和or一起使用时优先级and高
| FORD | 3000.00 | 20 |
+--------+---------+--------+
10 rows in set (0.00 sec)
#当优先级不确定的时候加小括号
正确示例:
[bjpowernode]>select ename,sal,deptno from EMP where sal > 1000 and (deptno = 20 or deptno = 30);
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| SCOTT | 3000.00 | 20 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| FORD | 3000.00 | 20 |
+--------+---------+--------+
9 rows in set (0.00 sec)
#in等同于or:找出工作岗位是MANAGER和SALESMAN的员工
[bjpowernode]>select ename,job from EMP where job = 'SALESMAN' or job = 'MANAGER';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
[bjpowernode]>select ename,job from EMP where job in('SALESMAN','MANAGER'); #in后面的不是区间,是具体的值。
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)
3、模糊查询like
两个特殊的符号:
- %:代表任意多个字符
- _:任意一个字符
#找出名字中含有O的
[bjpowernode]>select ename from EMP where ename like '%O%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+
3 rows in set (0.00 sec)
#找出名字中第二个是A的
[bjpowernode]>select ename from EMP where ename like '_A%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+
3 rows in set (0.00 sec)
#找出名字中有_的
\:转义字符
[bjpowernode]>select ename from EMP where ename like '%\_%;
#找出名字中最后一个字母是T的
[bjpowernode]>select ename from EMP where ename like '%T';
+-------+
| ename |
+-------+
| SCOTT |
+-------+
1 row in set (0.00 sec)