本文共 4280 字,大约阅读时间需要 14 分钟。
使用到的表,ORACLE数据库HR用户
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225151839480.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
查看整张表
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225151919727.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
查看指定的列(指出列名就行)
SELECT department_id,department_name FROM departments;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225151946537.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
使用算术表达式
SELECT employee_id,first_name,salary,salary+100,salary+(salary*0.1)
from employees;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225152032852.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
定义列别名(重命名列标题)
SELECT last_name, last_name as xing, last_name shixing , last_name “hai shi XING”
from employees;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225152055253.png)
连接运算符| |链接列或字符
first_name||last_name “xingming”:把姓名连接到一块,显示成一列。修改列名
first_name||’ ‘||last_name “shiyongkongge”:把姓名连接到一块,中间有空格。显示成一列
SELECT first_name,last_name, first_name||last_name “xingming” ,
first_name||’ '||last_name “shiyongkongge” from employees;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225152428325.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225152346840.png)
把两列连接成一列,中间加上自定义的内容
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225152653683.png)
取消重复的值
有许多重复的值
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225155223419.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
不显示重复的值
SELECT distinct department_id from employees;
![在这里插入图片描述](https://img-blog.csdnimg.cn/2019122515531483.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
SELECT distinct department_id,job_id from employees;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225155457671.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
显示表的结构 desc
![在这里插入图片描述](https://img-blog.csdnimg.cn/201912251555165.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
空值是未分配的或不适用的值, 空不等于零或空格
SELECT last_name,job_id,salary,commission_pct from employees;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225155606746.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
包含空值进行计算结果为空
WHERE带条件的查询,用来限制选定的行
select * from employees
where department_id=90;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225155659518.png)
字符串和数据值用单引号括起来
字符值区分大小写,日期值区分大小写
select employee_id,first_name,last_name,job_id from employees
where first_name=‘Steven’;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225155743401.png)
没区分大小写,无法查询到
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225155800827.png)
不加引号,报错
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225155817902.png)
查询日期
select * from employees
where hire_date = ‘24-12月-05’;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225155843868.png)
使用比较运算符
select first_name,last_name,salary from employees
where salary>=15000;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225155904266.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
查询在指定范围内的数值BETWEEN AND
select first_name,last_name,salary from employees
where salary BETWEEN 13000 AND 20000;
![在这里插入图片描述](https://img-blog.csdnimg.cn/2019122515595214.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
不能返着写,不然没结果
select first_name,last_name,salary from employees
where salary BETWEEN 20000 AND 13000;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225160034603.png)
varcher列中使用运算符
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225160101915.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
in运算符:查找指定的值
select first_name,last_name,salary from employees
where salary in (10000,250000,17000);
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225160116224.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
like运算符
select first_name,last_name,salary from employees
where first_name like ‘S%’;
查询S开头的
![在这里插入图片描述](https://img-blog.csdnimg.cn/2019122516014864.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
select first_name,last_name,salary from employees
where first_name like ‘%s’;
查询S结尾的
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225160236592.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
select first_name,last_name,salary from employees
where first_name like ‘%am%’;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225160316138.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
select first_name,last_name,salary from employees
where first_name like ‘_d%’;
前面匹配任意一个字符,第二个字第是d
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225160338902.png)
前面匹配任意两个字符,第三个字第是d
select first_name,last_name,salary from employees
where first_name like ‘__s%’;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225160506378.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
查询为null
select first_name,last_name,commission_pct from employees
where commission_pct is null;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225160651898.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
查询非空
select first_name,last_name,commission_pct from employees
where commission_pct is not null;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225160753772.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
employee_id不是100和101的
select first_name,last_name,employee_id,commission_pct from employees
where employee_id not in (100,101);
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225160853715.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
只有它两不显示
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225160904393.png)
显示不是以S开头的名
select first_name,last_name,salary from employees
where first_name not like ‘S%’;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225160922111.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
使用逻辑运算符定义条件
And同时满足两个条件
select email,last_name,salary,department_id from employees
where salary >= 10000 and department_id=90;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225161020947.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
or:要求不高,只要满足一个条件就好
select email,last_name,salary,department_id from employees
where salary >= 10000 or department_id=90;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225161111997.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
AND的优先级大于OR
select last_name, job_id,salary from employees
where job_id = ‘SA_REP’ OR job_id=‘AD_PRES’
and salary > 15000;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225161158317.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
select last_name, job_id,salary from employees
where ( job_id = ‘SA_REP’ OR job_id=‘AD_PRES’ )
and salary > 15000;
ORDER BY排序
select last_name,first_name,hire_date from employees
ORDER BY hire_date;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225161312589.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
同上面一样
select last_name,first_name,hire_date from employees
ORDER BY hire_date asc;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225161334233.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
返着查询
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225161358284.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
查询后在排序
select last_name,first_name,department_id,employee_id from employees
where department_id=90 ORDER BY employee_id;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225161427411.png)
select last_name,first_name,department_id,employee_id from employees
where department_id=90 ORDER BY employee_id desc;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225161509428.png)
默认空值排在最后
select last_name,first_name,commission_pct from employees
ORDER BY commission_pct;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225161602106.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
让空值排在前面
select last_name,first_name,commission_pct from employees
ORDER BY commission_pct nulls FIRST;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225161653506.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
列别名进行排序
select last_name,first_name m ,commission_pct from employees
ORDER BY m;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225161734531.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
使用表达式排序
select last_name,first_name,salary,salary+100 from employees
ORDER BY salary+100;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225161856218.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
多个列排序(先排序ID列,在排序first_name列,在按字母排序)
select last_name,first_name,salary,department_id from employees
ORDER BY department_id,first_name;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225162109753.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
一个升序,一个降序(这两我不分)
select last_name,first_name,salary,department_id from employees
ORDER BY department_id asc ,first_name desc;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225162125120.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
在select中按列号排序
select last_name,first_name,salary,department_id from employees
ORDER BY 3;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225162150467.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
select last_name,first_name,salary,department_id from employees
ORDER BY 4.3;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225162207152.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
返回前几行
select employee_id,last_name,first_name from employees
ORDER BY employee_id
FETCH first 5 ROWS ONLY;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20191225162250516.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTU2ODA3Mw==,size_16,color_FFFFFF,t_70)
返回百分之n行
select employee_id,last_name,first_name from employees
ORDER BY employee_id
FETCH first 10 percent rows only;
转载地址:http://kpbzk.baihongyu.com/