SQL plus³£ÓÃÃüÁî

1.µÇ½ϵͳÓû§
sqlplus È»ºóÊäÈëϵͳÓû§ÃûºÍÃÜÂë
µÇ½±ðµÄÓû§
conn Óû§Ãû/ÃÜÂë;
2.´´½¨±í¿Õ¼ä
create tablespace ¿Õ¼äÃû
datafile 'c:"¿Õ¼äÃû' size 15M --±í¿Õ¼äµÄ´æ·Å·¾¶,³õʼֵΪ15M
autoExtend on next 10M --¿Õ¼äµÄ×Ô¶¯Ôö³¤µÄÖµÊÇ10M
permanent online; --ÓÀ¾ÃʹÓÃ
3.´´½¨Óû§
create user shi --´´½¨Óû§ÃûΪshi
identified by scj --´´½¨ÃÜÂëΪscj
default tablespace ±í¿Õ¼äÃû --ĬÈϱí¿Õ¼äÃû
temporary tablespace temp --ÁÙʱ±í¿Õ¼äΪtemp
profile default --ÊÜprofileÎļþµÄÏÞÖÆ
quota unlimited on ±í¿Õ¼äÃû; --ÔÚ±í¿Õ¼äÏÂÃ潨±í²»ÊÜÏÞÖÆ
4.´´½¨½ÇÉ«
create role ½ÇÉ«Ãû identified by ÃÜÂë;
5.¸ø½ÇÉ«ÊÚȨ
grant create session to ½ÇÉ«Ãû£»--¸ø½ÇÉ«ÊÚÓè´´½¨»á»°µÄȨÏÞ
grant ½ÇÉ«Ãû to Óû§Ãû£» --°Ñ½ÇÉ«ÊÚÓèÓû§
6.¸øÓû§ÊÚÓèȨÏÞ
grant create session,resource to shi;--¸øshiÓû§ÊÚÓèËùÓÐȨÏÞ
grant create table to shi; --¸øshiÓû§ÊÚÓè´´½¨±íµÄȨÏÞ
7.select table_name from user_tables; ²ì¿´µ±Ç°Óû§ÏµÄËùÓбí
8.select tablespace_name from user_tablespaces; ²ì¿´µ±Ç°Óû§Ï嵀 ±í¿Õ¼ä
9.select username from dba_users;²ì¿´ËùÓÐÓû§Ãû³ÆÃüÁî ±ØÐëÓÃsys as sysdbaµÇ½
10.´´½¨±í
create table ±íÃû
(
id int not null,
name varchar2(20) not null
)tablespace ±í¿Õ¼äÃû --ËùÊôµÄ±í¿Õ¼ä
storage
(
initial 64K --±íµÄ³õʼֵ
minextents 1 --×îСÀ©Õ¹Öµ
maxextents unlimited --×î´óÀ©Õ¹Öµ
);
11.--Ϊusrs±íÌí¼ÓÖ÷¼üºÍË÷Òý
alter table users
add constraint pk primary key (ID);
12.ΪÒѾ­´´½¨users±íÌí¼ÓÍâ¼ü
alter table users
add constraint fk_roleid foreign key (roleid)
references role(role_id) on delete cascad; --ϱßдÖ÷±íµÄÁÐ
on delete cascadÊÇ´´½¨¼¶Áª
13.°ÑÁ½¸öÁÐÁ¬½ÓÆðÀ´
select concat(name,id) from ±íÃû£» --°ÑnameºÍidÁ¬½ÓÆðÀ´
14.½ØÈ¡×Ö·û´®
select column(name,'Àî') from ±íÃû£» --°ÑnameÖеđÀî’È¥µô
15.ÔËÐÐÊÂÎñ֮ǰ±ØÐëд
set serveroutput on; --´ò¿ªÊäÈëÊä³ö£¨²»Ð´µÄ»°,´òÓ¡²»³öÐÅÏ¢£©
16.whileµÄÓ¦ÓÃ
declare --ÉùÃ÷²¿·Ö
ccc number:=1; --¸´Ö°
a number:=0;
begin --ÊÂÎñµÄ¿ªÊ¼
while ccc<=100 loop --Ñ­»·
if((ccc mod 3)=0) then --Ì