Oracle中sql相關的命令

標籤：

1.建立使用者

SQL> -- 例如建立一個使用者名稱為xiaoming,密碼為a123的使用者

SQL> create user xiaomingidentified by a123;

 

使用者已建立。

 

SQL> show user

USER 為 "SYS"

 

2.給使用者授予許可權

SQL> -- 給使用者xiaoming授予許可權

SQL> grant connect,resource,dba to xiaoming;

 

授權成功。

 

SQL> -- 登入xiaoming使用者

SQL> conn xiaoming/a123

已串連。

 

3.建立資料表空間

SQL> -- 建立資料表空間

SQL> create tablespace xiaoming_data logging datafile ‘E:\xiaoming_data.dbf‘ size 50M;

 

資料表空間已建立。

 

SQL> /*

SQL> 這個可以通過alter方法，重新定義預設資料表空間的：

SQL> sql：

SQL> alter user username default tablespace userspace;

SQL> 解釋：以上語句就是說給username使用者重新指定資料表空間為userspace；

SQL> 擴充：建立使用者的時候指定資料表空間。

SQL> sql：create user username identified by userpassword default tablespace userspace;

SQL> */

SQL> -- 給使用者guoming指定資料表空間guoming_data

SQL> alter user guoming default tablespace guoming_data;

 

使用者已更改。

 

 

4.建立表

SQL> -- 1.建立users表

SQL>create table users (

  2  userName char(18) primary key not null,

  3  password varchar(12) not null,

  4  nickName varchar(20),

  5  sex char(2),

  6  age number(3),

  7  address varchar(40),

  8  Email varchar(40) not null,

  9  phone char(11)

 10* )

SQL> /

 

4.1查看已經建立的表

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID                              

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

USERS                          TABLE                                           

ADMIN                          TABLE                                           

JOB                            TABLE                                           

ENLIST                         TABLE                                           

ID                             TABLE                                           

ACCEPTANCE                     TABLE                                           

MESSAGE                        TABLE                                           

DATA                           TABLE                                           

NEWS                           TABLE                                           

 

已選擇9行。

 

5.添加外鍵

SQL> /*

SQL> 1.建立外鍵格式：alter table 子表名 add constraint 外鍵名 foreign key(外鍵欄位) references 父表(欄位)

SQL> 2.刪除外鍵格式：alter table 表名 drop constraint 外鍵名

SQL> 如：刪除enlist的外鍵

SQL> alter table enlist drop constraint FK_ID;

SQL> */

SQL> --為enlist表設定ID外鍵

SQL> alter table enlist add constraint enlist_ID foreign key(ID) references ID(ID)

SQL> /

 

表已更改。

 

SQL> --為acceptance表設定ID外鍵

SQL> alter table acceptance add constraint acceptance_ID foreign key(ID) references ID(ID);

 

表已更改。

 

6.建立索引

SQL> /*

SQL> 建立索引 格式：create [unique] index 索引名 on 表(欄位);

SQL> 刪除索引 格式：drop index 索引名;

SQL> 尋找一個表的有幾個索引（索引名）格式：select index_name from user_indexes where table_name=upper(‘表名‘)

SQL> */

SQL> --為表enlist表和acceptance表的外鍵建立索引

SQL>create index enlist_index on enlist(ID);

 

索引已建立。

 

SQL> create index acceptance_index on acceptance(ID);

 

索引已建立。

 

7.觸發器（這裡是自增觸發器）

SQL> /* 建立觸發器

SQL>  對於自增案例：1.先建立自增序列 2.建立觸發器

SQL> */

SQL> --建立enlist的自增序列

SQL> create sequence s_enlist

  2  increment by 1

  3  start with 1

  4  nomaxvalue

  5  nocycle

  6  nocache;

 

序列已建立。

 

 

SQL> --建立enlist表的觸發器t_enlist

SQL> create or replace trigger t_enlist

  2  before insert on enlist

  3  for each row

  4  begin

  5  select s_enlist.nextval into :new.enlistId from dual;

  6  end;

  7  /

 

觸發器已建立

註：new.觸發欄位

 

8.建立視圖（這裡是整合外鍵內容，及整合兩張表）

SQL> /*

SQL> 建立視圖 ：格式 create or replace 視圖名 select ... from 表1，表2 where ..

SQL> 刪除視圖 : 格式 drop view 視圖名;

SQL> */

SQL> -- 建立整合enlist表和ID資訊的視圖

SQL> create or replace view v_enlist

  2  as

  3  select enlist.enlistId,enlist.ID,ID.name,ID.sex,ID.age,enlist.expenses,enlist.enTime from enlist,ID

  4  where enlist.ID=ID.ID;

 

視圖已建立。

 

SQL> -- 建立整合acceptance表和ID資訊的視圖

SQL> create or replace view v_acceptance

  2  as

  3  select acceptance.accId,acceptance.jobName,acceptance.ID,ID.name,ID.sex,ID.age,

  4  acceptance.dept,acceptance.wokeDate from acceptance,ID where acceptance.ID=ID.ID

 

視圖已建立。

 

 

9.預存程序（這裡是尋找使用者是否註冊）

SQL> /*

SQL> 建立一個預存程序 格式 ： create or replace procedure 函數名(變數1 類型，變數2 類型..) as ... begin select .. ;

SQL> exception

SQL> when 異常名 then

SQL> ...

SQL> end 函數名；

SQL> /

SQL> */

SQL> -- 建立一個查看使用者名稱是否已經註冊的預存程序find_users

SQL> create or replace procedure

  2  find_users(u_name number)

  3  as

  4  name number(18);

  5  begin

  6  select userName into name from users where u_name=users.userName;

  7  dbms_output.put_line(name||‘使用者已經被註冊了！‘);

  8  exception

  9  when no_data_found then

 10  dbms_output.put_line(‘該使用者名稱未被註冊！‘);

 11  end find_users;

SQL> /

 

過程已建立。

 

下面就可以插入資料了，大家好好複習！

 

10.修改表的欄位屬性

alter table 表名 modify 欄位名number(5,2);

 

 

11.刪除有戶名和資料表空間（徹底）

drop user 使用者名稱 cascade ;

drop tablespace 資料表空間名 including contents and datafiles cascade constraints ;

Oracle中sql相關的命令