sql基本查询

使用到的表，ORACLE数据库HR用户

查看整张表

查看指定的列（指出列名就行）
SELECT department_id,department_name FROM departments;

使用算术表达式
SELECT employee_id,first_name,salary,salary+100,salary+(salary*0.1)
from employees;

定义列别名（重命名列标题）
SELECT last_name, last_name as xing, last_name shixing , last_name “hai shi XING”
from employees;

连接运算符| |链接列或字符
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;

把两列连接成一列，中间加上自定义的内容

取消重复的值
有许多重复的值

不显示重复的值
SELECT distinct department_id from employees;

SELECT distinct department_id,job_id from employees;

显示表的结构 desc


空值是未分配的或不适用的值, 空不等于零或空格
SELECT last_name,job_id,salary,commission_pct from employees;

包含空值进行计算结果为空

WHERE带条件的查询，用来限制选定的行
select * from employees
where department_id=90;

字符串和数据值用单引号括起来
字符值区分大小写，日期值区分大小写
select employee_id,first_name,last_name,job_id from employees
where first_name=‘Steven’;

没区分大小写，无法查询到

不加引号，报错

查询日期
select * from employees
where hire_date = ‘24-12月-05’;

使用比较运算符
select first_name,last_name,salary from employees
where salary>=15000;

查询在指定范围内的数值BETWEEN AND
select first_name,last_name,salary from employees
where salary BETWEEN 13000 AND 20000;

不能返着写，不然没结果
select first_name,last_name,salary from employees
where salary BETWEEN 20000 AND 13000;

varcher列中使用运算符

in运算符：查找指定的值
select first_name,last_name,salary from employees
where salary in (10000,250000,17000);

like运算符
select first_name,last_name,salary from employees
where first_name like ‘S%’;
查询S开头的

select first_name,last_name,salary from employees
where first_name like ‘%s’;
查询S结尾的

select first_name,last_name,salary from employees
where first_name like ‘%am%’;

select first_name,last_name,salary from employees
where first_name like ‘_d%’;
前面匹配任意一个字符，第二个字第是d

前面匹配任意两个字符，第三个字第是d
select first_name,last_name,salary from employees
where first_name like ‘__s%’;

查询为null
select first_name,last_name,commission_pct from employees
where commission_pct is null;

查询非空
select first_name,last_name,commission_pct from employees
where commission_pct is not null;

employee_id不是100和101的
select first_name,last_name,employee_id,commission_pct from employees
where employee_id not in (100,101);

只有它两不显示

显示不是以S开头的名
select first_name,last_name,salary from employees
where first_name not like ‘S%’;

使用逻辑运算符定义条件
And同时满足两个条件
select email,last_name,salary,department_id from employees
where salary >= 10000 and department_id=90;

or:要求不高，只要满足一个条件就好
select email,last_name,salary,department_id from employees
where salary >= 10000 or department_id=90;

AND的优先级大于OR
select last_name, job_id,salary from employees
where job_id = ‘SA_REP’ OR job_id=‘AD_PRES’
and salary > 15000;

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;

同上面一样
select last_name,first_name,hire_date from employees
ORDER BY hire_date asc;

返着查询

查询后在排序
select last_name,first_name,department_id,employee_id from employees
where department_id=90 ORDER BY employee_id;

select last_name,first_name,department_id,employee_id from employees
where department_id=90 ORDER BY employee_id desc;

默认空值排在最后
select last_name,first_name,commission_pct from employees
ORDER BY commission_pct;

让空值排在前面
select last_name,first_name,commission_pct from employees
ORDER BY commission_pct nulls FIRST;

列别名进行排序
select last_name,first_name m ,commission_pct from employees
ORDER BY m;

使用表达式排序
select last_name,first_name,salary,salary+100 from employees
ORDER BY salary+100;

多个列排序（先排序ID列，在排序first_name列，在按字母排序）
select last_name,first_name,salary,department_id from employees
ORDER BY department_id,first_name;

一个升序，一个降序（这两我不分）
select last_name,first_name,salary,department_id from employees
ORDER BY department_id asc ,first_name desc;

在select中按列号排序
select last_name,first_name,salary,department_id from employees
ORDER BY 3;

select last_name,first_name,salary,department_id from employees
ORDER BY 4.3;

返回前几行
select employee_id,last_name,first_name from employees
ORDER BY employee_id
FETCH first 5 ROWS ONLY;

返回百分之n行
select employee_id,last_name,first_name from employees
ORDER BY employee_id
FETCH first 10 percent rows only;