SQL语句学习备忘
编写基本的SQL SELECT 语句
检索所有的行和列
Select * from departments;
检索指定的列
select department_id,location_id from departments;
使用数学运算符
Select last_name,salary,salary+300 from employees;
对列运算
使用列的别名
1.select last_name as name,commission_pct comm from employees;
2.select last_name "name", salary*12 "annual salary"
From employees;
联接运算符
Select last_name || job_idas "employees" from employees;
使用文本字符串
Select last_name || ' is a ' || job_id as "employee Details"
From employees;
删除distinct冗余行
Select department_id from employees;
显示表结构
Describe employee
限制和分类数据
使用where子句
Select employee_id,last_name,job_id,department_id
From employees
Where department_id = 90 ;
字符串和日期
Select last_name,job_id,department_id
From employees
Where last_name = 'whalen' ;
使用比较条件
Select last_name,salary
From employees
Where salary <= 3000 ;
使用between条件
Select last_name,salary
From employees
Where salary between 2500 and 3500 ;
使用in条件
Select employee_id,last_name,salary,manager_id
From employees
Where manager_id in (100,101,201);
使用like条件
Select first_name
From employees
Where first_name like 's%'; (% 代替多个或零个字符)
Select last_name
From employees
Where last_name like '_o%' ;(_ 代表一个字符)
#可以使用escape关键字查找包含符号 % 和 _ 的字符串
使用空值
用 is null 操作指明空值元祖
Select last_name,manager_id
From employees
Where manager_id is null ;
使用 and 运算符
Select employee_id,last_name,job_id,salary
From employees
Where salary >= 10000
And job_id like '%man%' ;
使用OR运算符
Select employee_id,last_name,job_id,salary
From employees
Where salary >= 10000
Or job_id like '%man%' ;
使用 not 运算符
Select last_name, job_id
From employees
Where job_id
not in ('it_prog','st_clerk','sa_rep') ;
Order by 字句
Asc : 升序
Desc : 降序
Order by 子句放在select语句的最后
Select last_name,job_id,department_id,hire_date
From employees
Order by hire_date ;
使用降序排列
Select last_name,job_id,department_id,hire_date
From employees
Order by hire_date desc ;
使用列的别名排列
Select employee_id,last_name,salary*12 annsal
From employees
Order b annsal;
按多个列来排序
Select last_name,department_id,salary
From employees
Order by department_id,salary desc ;
单行函数
使用大小写转换函数
Select employee_id,last_name,department_id
From employees
Where lower(last_name) = 'higgins' ;
使用字符处理函数
Select employee_id,concat(first_name,last_name) name ,
job_id,length(last_name),
instr(last_name,'a') "contains 'a'?"
From employees
Where substr(job_id,4) = 'rep' ;
数值函数
使用ROUND函数
Select round(45.923,2),round(45,923,0),round(45.923,-1)
From dual;
(dual 是一个虚幻的表)
使用trunc函数
Select trunc(45.923,2),trunc(45.923),trunc(45.923,-2)
From dual;
使用mod函数
Select last_name,salary,mod(salary,5000)
From employees
Where job_id = 'sa_rep';
对日期使用算术运算
Select last_name , (sysdate-hire-date)/7 as weeks
From employees
Where department_id = 90 ;
转化函数
隐式数据类型转化
对日期使用to_char函数
Select last_name,
to_char(hire_date,'fmDD Month YYYY')
as hiredate
From employees;
对数字使用to_char函数
Select to_char(salary,'$99,999.00') salary
From employees
Where last_name = 'ernst' ;
嵌套函数
Select last_name,
nvl(to_char(manger_id),'no manager')
From employees
Where manager_id is null;
(nvl函数,把空值转换成一个实际的值)
使用nvl函数
Select last_name,salary,nvl(commission_pct,0),
(salary*12)+(salary*12*nvl(commission_pct,0)) an_sai
From employees;
使用nvl2函数
Select last_name,salary,commission_pct,
nvl2(commission_pct,'sal+comn','sal') income
From employees where dpartment_id in (50,80) ;
使用nullif函数
Select first_name,length(first_name) "expr1",
last_name,length(last_name) "expr2",
Nullif(length(first_name),length(last_name)) result
From employees;
使用coalesce函数
Select last_name,
coalesce(commission_pct,salary,10) comm
From employees
Order by commission_pct ;
条件表达式
case表达式
Select last_name,job_id,salary,
case job_id when 'it_prog' then 1.10*salary
when 'st_clerk' then 1.15*salary
when 'sa_rep' then 1.20*salary
else salary end "revised_salary"
From employees ;
decode函数
Select last_name,job_id,salary,
decode(job_id,'it_prog',1.10*salary,
'st_clerk',1.15*salary,
'sa_rep',1.20*salary,
salary)
revised_salary
From employees;
Select last_name,salary,
decode(trunc(salary/2000,0),
0,0.00,
1,0.09,
2,0.20,
3,0.30,
4,0.40,
5,0.42,
6,0.44,
0.45)tax_rate
From employees
Where department_id = 80;