《oracle語句》

標籤：scott   ror   alter   distinct   mil   use   where   表示   編輯功能   

select 查詢語句

查看使用者名稱下的表，解鎖hr使用者：

用sys身份進入

方法一：[[email protected] ~]$ sqlplus / as sysdba

方法二：SQL> conn / as sysdba

查看身份

SQL> show user

USER is "SYS"

SQL> select table_name from dba_tables where owner=‘HR‘;

SQL> select * from hr.employees;

SQL> alter user hr account unlock identified by hr;  解鎖使用者

切換使用者hr

$ sqlplus hr/hr或者SQL> conn hr/hr

SQL> show user

SQL> select * from tab;

SQL> desc employees     查看employees表結構

 

所要查詢資訊顯示的表的格式

Select 列             *表示所有列             多條列：列1，+空格列2

From  表

where行                

 

練習：

查看scott使用者名稱下的表，解鎖scott使用者：

Scott 使用者/tiger口令

1. 使用sqlplus的全屏編輯功能：

$ echo $EDITOR

SQL> select * from hr.employees;

SQL> ed

SQL> / 執行

 

 

 

 

 

 

 

1. 基礎select語句：

SQL> select LAST_NAME,SALARY*(1+COMMISSION_PCT)*12  from employees;

【可以給SALARY*(1+COMMISSION_PCT)*12起個別名，格式：原名+空格+新別名】

LAST_NAME   SALARY*(1+COMMISSION_PCT)*12

------------------------- ----------------------------

OConnell

Grant

Whalen

Hartstein

Fay

Mavris

Baer

Higgins

Gietz

King

Kochhar

 

SQL> select * from employees;

SQL> desc employees

SQL> select LAST_NAME, SALARY, COMMISSION_PCT from employees;

SQL> desc departments

SQL> select department_id, department_name from departments;

SQL> select distinct DEPARTMENT_ID from employees;

SQL> select last_name, salary*12*(1+commission_pct) total_salary, department_id from employees;                                                                            原名       別名                                         

SQL> select first_name||‘, ‘||last_name from employees;

SQL> select first_name||‘, ‘||last_name fullname from employees;

 

練習：

輸出下列員工資訊：

Eleni(first_name) Zlotkey(last_name) employeeid is ... at department .. total salary is …

答：

SQL> select first_name, last_name,‘employeeid is:‘||employee_id||

‘,at department:‘||DEPARTMENT_ID||‘,total salary:‘||salary*12 from employees;

Alana,Walsh

employeeid is:196,at department:50,total salary:37200

 

Kevin,Feeney

employeeid is:197,at department:50,total salary:36000

 

1. 使用連字號構造語句：

SQL> select table_name from user_tables;

SQL> select ‘grant select on hr.‘||table_name||‘ to scott;‘ from user_tables;

 

SQL> spool /home/oracle/grant.sql

SQL> select ‘grant select on hr.‘||table_name||‘ to scott;‘ from user_tables;

SQL> spool off

$ vi /home/oracle/grant.sql     去除沒用的——行

SQL> @/home/oracle/grant.sql

 

1. 單引號的處理：

SQL> select ‘I‘m teaher‘ from dual;

ERROR:

ORA-01756: quoted string not properly terminated

 

SQL> select ‘I‘‘m teaher‘ from dual;

SQL> select q‘{I‘m teaher}‘ from dual; {},[],<>,()都可以

 

除去重複值：distinct

SQL>select  deoatments_id from employees;

SQL>select distinct deoatments_id from employees;

 

where 和 order by

數字條件：

SQL> select salary from employees where employee_id=100;

字串大小寫敏感：

SQL> select last_name, salary from employees where last_name=‘King‘;

SQL> select last_name, salary from employees where lower(last_name)=‘king‘;

SQL> select table_name, tablespace_name from user_tables where table_name=‘EMPLOYEES‘;

日期是格式敏感： alter session :只是針對當前會話

SQL> alter sysdate from dual;

SQL> alter session set nls_date_format=‘RR-Mon-dd‘;

SQL> alter sysdate from dual;

SQL> select last_name from employees where hire_date=‘2006-05-23‘;  報錯

SQL> select last_name from employees where hire_date=‘23-MAY-06‘;  

SQL> select last_name from employees where hire_date=to_date(‘2006-05-23‘, ‘yyyy-mm-dd‘);

解釋一下日期格式，方便統一

區間查詢：

SQL> select last_name from employees where salary>=3400 and salary<=4000;

SQL> select last_name from employees where salary between 3400 and 4000;

SQL> select last_name from employees where salary between 3000 and 5000 and department_id=50;

in：

SQL> select last_name from employees where department_id=30 or department_id=40 or department_id=50;

SQL> select last_name from employees where department_id in (30, 40, 50);

萬用字元：

SQL> select last_name, job_id from employees where job_id like ‘%\_MAN‘ escape ‘\‘;

【\:轉義符，屏蔽\後面 的符號的特殊含義，讓其符號只是表示普通的含義】

null作為條件：

SQL> select last_name from employees where commission_pct is null;

SQL> select last_name from employees where commission_pct is not null;

and/or/not：

SQL> select last_name, job_id, department_id, salary from employees where job_id=‘SA_REP‘ or department_id=50 and salary>=8000;

SQL> select last_name, job_id, department_id, salary from employees where (job_id=‘SA_REP‘ or department_id=50) and salary>=8000;

排序：

SQL> select last_name, salary from employees order by salary;

SQL> select last_name, salary from employees order by salary desc;

SQL> select last_name, salary from employees order by last_name;

SQL> select last_name, hire_date from employees order by hire_date;

SQL> select last_name, salary, commission_pct from employees order by salary desc, commission_pct desc;

SQL> select last_name, salary*12*(1+commission_pct) from employees order by 2;

SQL> select last_name, salary*12*(1+commission_pct) total_salary from employees order by total_salary;

 

Or 和 and同時出現，優先and，在or

練習：

選擇部門30中的僱員

SQL> select last_name, department_id from employees where department_id=‘30‘;

列出所有職員(CLERK)的姓名、編號和部門

SQL> select last_name, employee_id, department_id from employees;

找出薪金大於5000的僱員

SQL> select last_name from employees where salary>5000;

找出獎金高於0.1的僱員

SQL> select last_name from employees where COMMISSION_PCT>0.1;

找出部門50中的所有員工和部門30中的經理的詳細資料

SQL> select * from employees where department_id=‘50‘ or department_id=‘30‘ and job_id like ‘man%‘;

找出收取獎金的僱員的不同工作職位 每種職位顯示一次

SQL> select distinct job_id from employees where commission_pct>0;

找出不收取獎金或收取的工資低於5000的僱員

SQL> select last_name from employees where commission_pct is null or salary<5000;

顯示last_name不帶有‘R‘的僱員姓名

SQL> select last_name from employees where last_name not like‘R%‘;

 

《oracle語句》