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;

转载于:https://www.cnblogs.com/saroro/p/5721838.html