select department_id as dashabia from hr.departments;
//别名的使用,从hr用户下查询departments表,将department_id命名为dashabia并列出
SELECT last_name||' '||job_id AS "Employees" FROM employees;
//连接符 ||'@' || 中间用引号隔开,可以加字符,但是不可以加'
select ename || q'<'s salary is >'|| sal from scott.emp;
select ename || q'<'s job is >'|| job from scott.emp;
//引用操作符Alternative Quote (q) Operator
SELECT DISTINCT department_id FROM employees;
//DISTINCT,去除重复的行
select EMPLOYEE_ID , LAST_NAME , EMAIL ,PHONE_NUMBER , SALARY ,COMMISSION_PCT ,DEPARTMENT_ID from hr.employees where department_id=50;
//过滤条件where,一般写在from table 后面,where子句中不可以使用别名
select employee_id ,first_name ,salary from employees where salary <3000
//比较运算符,大于等于之类,太简单就不说了
SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500 ;
//between,区间值
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201) ;
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id =100 or manager_id = 101 or manager_id =201;
//in和 or 也很好理解
SELECT first_name FROM employees WHERE first_name LIKE 'S%' ; //模糊查询,%表示一个或者多个字符,理解起来就是从employees表中查询出最初名字开头为S的姓名,_表示一个字符
SELECT first_name FROM employees WHERE first_name LIKE 'S_e%' ;
//同上,区别在于 _表示一个字符
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 AND job_id LIKE '%MAN%' ;
//and ,须保证2边都为真
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 OR job_id LIKE '%MAN%' ; //一边为真即可
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = 'SA_REP'
OR job_id = 'AD_VP'
AND salary > 10000;
SELECT last_name, job_id, salary
FROM employees
WHERE (job_id = 'SA_REP'
OR job_id = 'AD_VP')
AND salary > 10000;
//优先级别问题,2者区别,由于在 job_id = 'SA_REP' OR job_id = 'AD_VP' AND salary > 10000 中,AND优先级大于OR,所以先算 job_id = 'AD_VP' AND salary > 10000 ,之后再进行( job_id = 'SA_REP' ) OR ( job_id
= 'AD_VP' AND salary > 10000) 具体看个人理解,其实道理和1+2*3=? 一样,所以也就可以使用符号(1+2)*3=? 类似一个道理,在此查到一个优先级别表:
运算符 |
级别 |
算术运算符(即‘+’,‘-’,‘*’,‘/’) |
1 |
连接运算符(即‘||’) |
2 |
比较运算符(即‘>’,‘>=’,‘<’,‘<=’,‘<>’) |
3 |
Is [not] null,[not] like,[not] in |
4 |
[not] between-and |
5 |
not |
6 |
and | |
or |
|
select department_id, last_name
from employees
order by department_id desc;
//结果排序,ASC:升序,默认 DESC:降序,在order by 列名 之后加desc