您好,登錄后才能下訂單哦!
開(kāi)發(fā)課 做練習(xí) 學(xué)生指南051 les01 ppt
oracle經(jīng)常兩表連接,叫第三范式,如帶有ID性質(zhì)的東西
以下是sql語(yǔ)句的命令
select * from departments; 部門(mén)表
select * from employees; 員工表
select employee_id,rowid,rownum from employees
where employee_id>=200; 64進(jìn)制內(nèi)部運(yùn)算 rownum本質(zhì)查詢(xún)第幾行
select employee_id,rowid,rownum from employees
where rownum<=5; 查看前5行
select * from employees e where e.department_id=80; 這部門(mén)里的人都有提成
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; 關(guān)于空的處理
select last_name,12*salary*(1+nvl(commission_pct,0)) as anaualsal from employees;進(jìn)行列別名處理
select last_name,12*salary*(1+nvl(commission_pct,0)) "Anaualsal" from employees;如果要區(qū)分大小寫(xiě)要加雙引號(hào)
select last_name ||'xxxx'|| job_id from employees; 連起來(lái)顯示
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號(hào)員工沒(méi)部門(mén))
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'; 查看用戶(hù)范圍內(nèi)的所有表的所有列
方法2:sqlplus命令
sqlplus /nolog
conn hr/oracle_4U
describe locations 查看表結(jié)構(gòu)
小提示:選中SQL 按F5看執(zhí)行計(jì)劃
les02 ppt
oracle變量有6種+1形參,兩種宿主
select * from employees where rownum=1;
alter session set nls_date_format='YYYY-MM-DD';修改當(dāng)前會(huì)話(huà)默認(rèn)格式
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);查看多少人是老板管過(guò)人
select * from employees e where e.employee_id not in (select manager_id from employees);這個(gè)例子是錯(cuò)誤的。不能not in 因?yàn)槔镱^有空值取反還是空
select * from employees e where e.employee_id not in (select manager_id from employees where manager_id is not null);這樣才有顯示89個(gè)人
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 '\'; 查看%號(hào)
select * from t05102_a where a like '\_%' escape '\';查看下劃線(xiàn)打頭的
insert into t05102_a values('''');插入單引號(hào)
insert into t05102_a values(chr(39)||1); man ascii查看得來(lái)的
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世界里無(wú)所謂是“X”還是a都可以,但是正常語(yǔ)句的sql語(yǔ)句的別名不行
替換變量
select employee_id,salary from employees where employee_id=100;
select employee_id,salary from employees where employee_id=&S_1; 可以進(jìn)出彈窗,自己選擇ID號(hào)
select last_name,salary from employees where last_name like '&S_1%';可以進(jìn)出彈窗
sqlplus /nolog
select salary from employees where employee_id=&&s_2;問(wèn)兩次后永遠(yuǎn)都是這個(gè)
define 默認(rèn)在這了
undefine s_2 取消
select &&s2,salary from employees where employee_id=&s_2;
set verify off 配置這個(gè)后就不會(huì)有舊的新的,環(huán)境變量
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。