PLSQL高級查詢
4.授權(quán)
Grant 角色名 to 用戶名;
Create table school(id varchar2(5),name varchar2(40),studentCount number(3));
查詢表
desc表名;
添加字段
alter table 表名 add(字段名 字段類型(字段長度));
修改字段
alter table 表名 modify(字段名 字段類型(字段長度));
刪除字段
Alter table 表名 drop column 字段名;
刪除表 Drop table 表名
6.約束
增加主鍵約束
Alter table 表名 add constraint約束名primary key(字段名);
查看主鍵約束
Select constraint_name from user_constraints where table_name = '表名(大寫)’;
增加唯一約束
Alter table 表名 add constraint約束名unique(字段名);
增加檢查約束
Alter table 表名 add constraint約束名check(字段名>0);
刪除約束
Alter table 表名 drop constraint約束名;
7.表數(shù)據(jù)操作
創(chuàng)建記錄
Insert into 表名 (字段名,字段名,字段名,createdate) values(字段值,字段值,字段
值,to_date('2012-03-19’,’yyyy-mm-dd’));
創(chuàng)建備份表
Create table school_bk(字段名 字段類型(字段長度), 字段名 字段類型(字段長度))
備份數(shù)據(jù)
Insert into school_bk select t.*,sysdate from school t;
提交數(shù)據(jù)
commit
更新表數(shù)據(jù)
Update 表名 set 字段名 = 字段值, 字段名 = 字段值 where id = '00002’;
刪除表數(shù)據(jù)
Delete from 表名 where id = '00002’,
恢復備份表
Insert into 表名 select id,name,studentCount from school_bk
截斷表，不需要提交
Truncate table 表名;
8.查詢字段
部分查詢
Select 字段名,字段名 from 表名;
條件查詢
Select * from 表名 where 字段名 between 100 and 200;
升序排序查詢
Select * from school order by 字段名;
降序排序查詢
Select * from school order by 字段名 desc;
別名查詢
Select id as “編號”,name as “學?！?from 表名
Sys賬戶連接數(shù)據(jù)庫
Conn sys/123@school as sysdba;
創(chuàng)建用戶
Create user test1 identified by sys
Default tablespace 表空間名
Temporary tablespace 臨時表空間名
Quota 5m on 表空間名;
用戶授權(quán)
連接表權(quán)限
Grant create session to 用戶;
創(chuàng)建表權(quán)限
Grant create table to用戶;
撤銷權(quán)限
Revoke create session,create table from 用戶;
創(chuàng)建角色
Create role 角色名;
角色授權(quán)
Grant create session,create table to 角色名;
為用戶授予角色
Grant 角色名 to 用戶名;
10.字符函數(shù)
查詢字段長度
Select length('asvasdad’) from dual; Select name,length(name) from
school;
左截取
Select ltrim('oracle’,’ora’) from dual;
右截取
Select rtrim('oracle’,’cle’) from dual;
兩端截取
Select trim('a’from’Oracle’) from dual;
轉(zhuǎn)換小寫
Select lower('Oracle’)from dual;
轉(zhuǎn)換大寫
Select upper('Oracle’)from dual;
字符串替換
Select replace('Hello DataBase’,’Database’,’Oracle’)from dual;
查找
select instr('Hello Oracle’,’l’,’1’,’1’)from dual;
截取字符串
select substr('Hello Oracle’,’l’,5’)from dual;
字符串連接
select concat(’ Hello, ’ Oracle’)from dual;
首字母大寫
Select initcap(’ Hello Oracle’)from dual;
11.日期函數(shù)
系統(tǒng)當前日期
Select sysdate from dual;
增加月份
Select sysdate,add_months(sysdate,1)from dual;
返回月份差
Select months_between(sysdate,add_months(sysdate,1))from dual;
當月最后一天
select sysdate,last_day(sysdate)from dual;
返回下一個周幾
select sysdate,next_day(sysdate,2)from dual;
獲取當期日期年份、月份
Select sysdate,extract(year from sysdate)from dual;
12.數(shù)值函數(shù)
絕對值
select abs(-100)from dual;
最小整數(shù)
select ceil(8.3),ceil(-8.3) from dual;
最大整數(shù)
select floor(8.3),floor(-8.3) from dual;
取余
select mod(24,5)from dual;
四舍五入
select round(23.45,1), round(23.45), round(23.45,-1)from dual;
截取數(shù)字
select trunc(23.45,1),trunc(23.45,-1)from dual;
13.轉(zhuǎn)換函數(shù)
轉(zhuǎn)換字符串
select to_char(sysdate,’yyyy-mm-dd’)from dual;
轉(zhuǎn)換數(shù)值
select to_number(’22.2’)from dual;
轉(zhuǎn)換日期
select to_date('2012-03-19’,’yyyy-mm-dd’)from dual;
14.聚合函數(shù)
平均值
Select avg(score) from 表名 where subject=’數(shù)學’;
最大值
select max(score) from 表名 where subject=’數(shù)學’;
最小值
select min(score) from 表名 where subject=’數(shù)學’;
總成績
select sum(score) from 表名 where name=’張三’;
統(tǒng)計個數(shù)
select count(*) from 表名;
分組求總成績
select name,sum(score) from score group by name;
有條件分組
select name,sum(score) from score group by name having sum(score)>150;
dbms_output.put_line(name);
end;
16.邏輯控制結(jié)構(gòu)
declare
birthyear number;
begin
birthyear :=1990;
if birthyear between 1980 and 1989 then
dbms_output.put_line('80后');
elsif birthyear between 1990 and 1999 then
dbms_output.put_line('90后');
else
dbms_output.put_line('00后');
end if;
end;
17.多分支條件結(jié)構(gòu)(查詢語句中使用)
select (case
when atk between 2000 and 2999 then
'高級怪獸'
when atk between 3000 and 3999 then
'超級怪獸'
when atk between 4000 and 4999 then
'神之卡'
else
'沒有找到匹配項'
end)lv from card;
多分支條件結(jié)構(gòu)2
declare
atk number;
begin
atk := 4000;
case
when atk between 2000 and 2999 then
dbms_output.put_line('高級卡');
when atk between 3000 and 3999 then
dbms_output.put_line('超級卡');
when atk between 4000 and 4999 then
dbms_output.put_line('神之卡');
else
dbms_output.put_line('沒有找到匹配項');
end case;
end;
18.LOOP循環(huán)
declare
i number := 0;
begin
dbms_output.put_line('循環(huán)之前i=' || i);
loop
if i > 4 then
exit;
end if;
dbms_output.put_line('循環(huán)第' || (i + 1) || '次');
i := i + 1;
end loop;
dbms_output.put_line('循環(huán)結(jié)束');
end;
19.FOR循環(huán)
begin
for i in 1..100 loop
if(mod(i,3)=0)then
dbms_output.put_line(i);
end if;
end loop;
end;
20.捕獲異常
declare
salary number := 0 ;
salary_exception exception ;
begin
salary := &salary;
if salary < 0 then
raise salary_exception;
end if;
dbms_output.put_line('輸入的工資是'||salary);
exception
when salary_exception then
dbms_output.put_line('輸入的工資數(shù)據(jù)不合法!');
end;
左外連接查詢方式
select c.name,o.name from card c left join owner o on c.owner=o.id;
右外連接查詢方式
select c.name,o.name from card c right join owner o on c.owner=o.id;
全外連接查詢方式
select c.name,o.name from card c full join owner o on c.owner=o.id;
組合查詢
select id,name from owner union all select id,name from owner2;
簡單子查詢
select name,owner from card where owner = (select id from owner where name
= '游戲');
相關(guān)子查詢
select name,owner from card where owner in (select id from owner where
name = '游戲' or name='城之內(nèi)');
內(nèi)嵌視圖子查詢
select name,owner from card e where exists (select o.id from owner o where
(o.name = '游戲'or o.name='城之內(nèi)')and e.owner = o.id);
使用序列
begin
insert into location (id,name) values (seq_location_pk.nextval,'北京');
insert into location (id,name) values (seq_location_pk.nextval,'上海');
insert into location (id,name) values (seq_location_pk.nextval,'廣州');
commit;
end;
修改序列
alter sequence seq_location_pk
刪除序列
drop sequence seq_location_pk;
23.分頁查詢
分頁查詢
select cd.rn,cd.* from (select rownum as rn,card.* from card)cd where rn
between (2*(2-1)+1) and (2*2);
