Oracle學習筆記之第八節sql語句(開發課學生指南051)

標籤：sql語句

 開發課 做練習 學生指南051 les01 ppt

 oracle經常兩表串連，叫第三範式，如帶有ID性質的東西

 以下是sql語句的命令

 select * from departments; 部門表

select * from employees; 員工表

select employee_id,rowid,rownum from employees

where employee_id>=200;  64進位內部運算 rownum本質查詢第幾行

select employee_id,rowid,rownum from employees

where rownum<=5; 查看前5行

select * from employees e where e.department_id=80; 這部門裡的人都有提成 

select last_name,12*salary*(1+commission_pct) from employees; 查看年度營收

select last_name,12*salary*(1+nvl(commission_pct,0)) from employees; 查看年度營收,處理空行的顯示

select * from employees where manager_id is null; 關於空的處理

select last_name,12*salary*(1+nvl(commission_pct,0)) as anaualsal  from employees;進行列別名處理

select last_name,12*salary*(1+nvl(commission_pct,0)) "Anaualsal"  from employees;如果要區分大小寫要加雙引號

select last_name ||‘xxxx‘|| job_id from employees; 連起來顯示

select department_name || q‘[ department‘s manager id:]‘ || manager_id as "Department and Manager" from departments;    q‘#xxx# 也可以， 後面就是你想要的東西

select DISTINCT department_id from employees; 有除重必須排序(空有顯示，178號員工沒部門） 

create table t05101_distinct (a varchar2(10),b varchar2(10));

insert into t05101_distinct values (‘A‘,‘B‘);

select * from t05101_distinct;

select distinct a,b from t05101_distinct;

insert into t05101_distinct values (‘A‘,‘B1‘);  除重是除掉整行

select distinct a,b from t05101_distinct;

select * from user_tab_cols tc where tc.TABLE_NAME=‘LOCATIONS‘; 查看使用者範圍內的所有表的所有列

方法2：sqlplus命令

sqlplus /nolog

conn hr/oracle_4U

describe  locations  查看錶結構

小提示：選中SQL 按F5看執行計畫

 

les02 ppt

oracle變數有6種+1形參，兩種宿主

select * from employees where rownum=1;

alter session set nls_date_format=‘YYYY-MM-DD‘;修改當前會話預設格式

select last_name from employees where hire_date = to_date(‘17-JUN-07‘,‘DD-MON-RR‘);

select * from employees e where e.employee_id in (select manager_id from employees);查看多少人是老闆管過人

select * from employees e where e.employee_id not in (select manager_id from employees);這個例子是錯誤的。不能not in 因為裡頭有空值取反還是空

select * from employees e where e.employee_id not in (select manager_id from employees where manager_id is not null);這樣才有顯示89個人

create table t05102_a (a varchar2(10));

insert into t05102_a values (‘A‘);

insert into t05102_a values (‘A1‘);

insert into t05102_a values (‘%‘);

insert into t05102_a values (‘_‘);

insert into t05102_a values (‘_1‘);

select * from t05102_a;

select * from t05102_a where a like ‘A%‘; A打頭的東西

select * from t05102_a where a like ‘A_‘;查看A打頭的東西

select * from t05102_a where a like ‘\%%‘ escape ‘\‘; 查看%號

select * from t05102_a where a like ‘\_%‘ escape ‘\‘;查看底線打頭的

insert into t05102_a values(‘‘‘‘);插入單引號

insert into t05102_a values(chr(39)||1); man ascii查看得來的

create table t05102_b (a number,b number);

insert into t05102_b values(1,999);

insert into t05102_b values(1,0);

insert into t05102_b values(2,999);

insert into t05102_b values(2,0);

select * from t05102_b order by a,b;先按a排列在按照b排列

select * from t05102_b order by a desc ,b desc;   

select a "X", b "Y" from t05102_b order by "X" desc,"Y" desc;別名,在order by世界裡無所謂是“X”還是a都可以，但是正常語句的sql語句的別名不行

替換變數

select employee_id,salary from employees where employee_id=100;

select employee_id,salary from employees where employee_id=&S_1; 可以進出彈窗，自己選擇ID號

select last_name,salary from employees where last_name like ‘&S_1%‘;可以進出彈窗

sqlplus /nolog

select  salary from employees where employee_id=&&s_2;問兩次後永遠都是這個

define 預設在這了

undefine s_2 取消

select  &&s2,salary from employees where employee_id=&s_2;

set verify off 配置這個後就不會有舊的新的，環境變數

本文出自 “Oracle個人學習筆記” 部落格，請務必保留此出處http://wuchunqiang.blog.51cto.com/1022331/1852767

Oracle學習筆記之第八節sql語句(開發課學生指南051)