課程視訊：
Oracle資料庫開發必備利器之SQL基礎
文章目錄
- 登入資料庫
- 使用者操作
-
- 檢視登入使用者
- 啟用scott使用者
- 表空間
-
- 查找表空間的位置
- 修改資料檔案
- 資料類型
-
- 數值型
- 日期型
- 其他類型
- 建立表
- 修改表
-
- 字段操作
-
- 添加字段
- 更改字段資料類型
- 删除字段
- 修改字段名
- 修改表名
- 删除表
- 操作表資料
-
- 添加資料
-
- 向表中所有字段添加值
- 向表中指定字段添加值
- 向表中添加預設值
-
- 建表時添加預設值
- 建表後添加預設值
- 複制表資料
-
- 建表時複制
-
- *全部列複制
- *部分列複制
- 在添加時複制
-
- *全部複制
- *部分複制
- 修改資料
-
- 無條件更新
- 有條件更新
- 删除資料
-
- 全部删除
- 部分删除
登入資料庫
F:\>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 11月 6 17:15:33 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
請輸入使用者名: system
輸入密碼:
連接配接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> connect sys
輸入密碼:
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
警告: 您不再連接配接到 ORACLE。
使用者操作
檢視登入使用者
SQL> show user
USER 為 "SYSTEM"
SQL> --使用dba_user字典
SQL> desc dba_users;
名稱 是否為空? 類型
----------------------------------------- -------- -------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(8)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
SQL> select username from dba_users;
USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
OUTLN
FLOWS_FILES
MDSYS
ORDSYS
EXFSYS
USERNAME
------------------------------
WMSYS
APPQOSSYS
APEX_030200
OWBSYS_AUDIT
ORDDATA
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
OWBSYS
SI_INFORMTN_SCHEMA
USERNAME
------------------------------
OLAPSYS
ORACLE_OCM
XS$NULL
BI
PM
MDDATA
IX
SH
DIP
OE
APEX_PUBLIC_USER
USERNAME
------------------------------
HR
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
已選擇36行。
啟用scott使用者
SQL> alter user scott account unlock;
使用者已更改。
SQL> --使用scott使用者登入SQL Plus
SQL> --預設密碼tiger
SQL> connect scott/tiger
已連接配接。
SQL> show user
USER 為 "SCOTT"
表空間
SQL> --檢視使用者的表空間
SQL> desc dba_tablespaces
ERROR:
ORA-04043: 對象 "SYS"."DBA_TABLESPACES" 不存在
SQL> conn system
輸入密碼:
已連接配接。
SQL> desc dba_tablespaces
名稱 是否為空? 類型
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
PREDICATE_EVALUATION VARCHAR2(7)
ENCRYPTED VARCHAR2(3)
COMPRESS_FOR VARCHAR2(12)
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TEMPTEST1_TABLESPACE
已選擇7行。
SQL> desc user_tablespaces
名稱 是否為空? 類型
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
PREDICATE_EVALUATION VARCHAR2(7)
ENCRYPTED VARCHAR2(3)
COMPRESS_FOR VARCHAR2(12)
SQL> select tablespace_name from user_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TEMPTEST1_TABLESPACE
已選擇7行。
SQL> conn scott
輸入密碼:
已連接配接。
SQL> select tablespace_name from dba_tablespaces;
select tablespace_name from dba_tablespaces
*
第 1 行出現錯誤:
ORA-00942: 表或視圖不存在
SQL> select tablespace_name from user_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TEMPTEST1_TABLESPACE
已選擇7行。
SQL> conn system
輸入密碼:
已連接配接。
SQL> desc dba_users;
名稱 是否為空? 類型
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(8)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
SQL> --檢視system使用者預設表空間和臨時表空間
SQL> select default_tablespace,temporary_tablespace from dba_users where usernam
e='SYSTEM';
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYSTEM TEMP
SQL> alter user system
2 default tablespace system;
使用者已更改。
SQL> select default_tablespace,temporary_tablespace from dba_users where usernam
e='SYSTEM';
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYSTEM TEMP
SQL> create tablespace test1_tablespace
2 datafile 'test1file.dbf' size 10m;
表空間已建立。
SQL> create temporary tablespace temptest1_tablespace
2 tempfile 'tempfile1.dbf' size 10m;
表空間已建立。
查找表空間的位置
SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPAC
E';
FILE_NAME
--------------------------------------------------------------------------------
--------------------
F:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF
SQL> select file_name from dba_temp_files where tablespace_name='TEMPTEST1_TABLE
SPACE';
FILE_NAME
--------------------------------------------------------------------------------
--------------------
F:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEMPFILE1.DBF
SQL> --修改表空間的狀态
SQL> alter tablespace test1_tablespace
2 offline;
表空間已更改。
SQL> desc dba_tablespaces
名稱 是否為空? 類型
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
PCT_INCREASE NUMBER
MIN_EXTLEN NUMBER
STATUS VARCHAR2(9)
CONTENTS VARCHAR2(9)
LOGGING VARCHAR2(9)
FORCE_LOGGING VARCHAR2(3)
EXTENT_MANAGEMENT VARCHAR2(10)
ALLOCATION_TYPE VARCHAR2(9)
PLUGGED_IN VARCHAR2(3)
SEGMENT_SPACE_MANAGEMENT VARCHAR2(6)
DEF_TAB_COMPRESSION VARCHAR2(8)
RETENTION VARCHAR2(11)
BIGFILE VARCHAR2(3)
PREDICATE_EVALUATION VARCHAR2(7)
ENCRYPTED VARCHAR2(3)
COMPRESS_FOR VARCHAR2(12)
SQL> select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE'
;
STATUS
---------
OFFLINE
SQL> alter tablespace test1_tablespace
2 online;
表空間已更改。
SQL> select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE'
;
STATUS
---------
ONLINE
SQL> alter tablespace test1_tablespace
2 read only;
表空間已更改。
SQL> select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE'
;
STATUS
---------
READ ONLY
SQL> alter tablespace test1_tablespace
2 read write;
表空間已更改。
SQL> select status from dba_tablespaces where tablespace_name='TEST1_TABLESPACE'
;
STATUS
---------
ONLINE
SQL>
修改資料檔案
`
SQL> alter tablespace test1_tablespace
2 add datafile 'test2_file.dbf' size 10m;
表空間已更改。
SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPAC
E';
FILE_NAME
--------------------------------------------------------------------------------
F:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF
F:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST2_FILE.DBF
SQL> alter tablespace test1_tablespace
2 drop datafile 'test2_file.dbf';
表空間已更改。
SQL> select file_name from dba_data_files where tablespace_name='TEST1_TABLESPAC
E';
FILE_NAME
--------------------------------------------------------------------------------
F:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\TEST1FILE.DBF
SQL> drop tablespace test1_tablespace including contents;
表空間已删除。
資料類型
字元型、數值型、日期型、其他類型（存放大對象資料）
字元型：
以上類型，位數沒有填滿需要在後面補充空格。
varchar(n)/nvarchar(n)：位數沒有填滿不用補充空格，varchar裡的n最大支援4000，nvarchar最大支援2000。
數值型
float（n）:用于二進制資料。
日期型
timestamp:比date更精确，精确到秒
其他類型
blob
clob
建立表
修改表
字段操作
添加字段
SQL> alter table userinfo
2 add remarksd varchar2(500);
表已更改。
SQL> desc userinfo
名稱 是否為空? 類型
----------------------------------------- -------- -------------------------
ID NUMBER(6)
USERNAME VARCHAR2(20)
USERPWD VARCHAR2(20)
EMAIL VARCHAR2(30)
REGDATE DATE
REMARKSD VARCHAR2(500)
更改字段資料類型
注意：如果表中資料已有資料，則表不能修改了。
SQL> alter table userinfo
2 modify remarksd varchar2(400);
表已更改。
SQL> desc userinfo
名稱 是否為空? 類型
----------------------------------------- -------- -------------------------
ID NUMBER(6)
USERNAME VARCHAR2(20)
USERPWD VARCHAR2(20)
EMAIL VARCHAR2(30)
REGDATE DATE
REMARKSD VARCHAR2(400)
删除字段
SQL> alter table userinfo
2 drop column remarksd;
表已更改。
SQL> desc userinfo
名稱 是否為空? 類型
----------------------------------------- -------- -------------------------
ID NUMBER(6)
USERNAME VARCHAR2(20)
USERPWD VARCHAR2(20)
EMAIL VARCHAR2(30)
REGDATE DATE
修改字段名
SQL> alter table userinfo
2 rename column email to new_emal;
表已更改。
SQL> desc userinfo
名稱 是否為空? 類型
----------------------------------------- -------- -------------------------
ID NUMBER(6)
USERNAME VARCHAR2(20)
USERPWD VARCHAR2(20)
NEW_EMAL VARCHAR2(30)
REGDATE DATE
修改表名
SQL> rename userinfo to new_userinfo;
表已重命名。
SQL> desc new_userinfo
名稱 是否為空? 類型
----------------------------------------- -------- ----------------------------
ID NUMBER(6)
USERNAME VARCHAR2(20)
USERPWD VARCHAR2(20)
NEW_EMAIL VARCHAR2(30)
REGDATE DATE
删除表
Truncate Table table_name:删除表中資料，很快。
SQL> truncate table new_userinfo;
表被截斷。
SQL> desc new_userinfo
名稱 是否為空? 類型
----------------------------------------- -------- ----------------------------
ID NUMBER(6)
USERNAME VARCHAR2(20)
USERPWD VARCHAR2(20)
NEW_EMAIL VARCHAR2(30)
REGDATE DATE
Drop Table table_name:删除整張表，較慢。
SQL> drop table new_userinfo;
表已删除。
SQL> desc new_userinfo
ERROR:
ORA-04043: 對象 new_userinfo 不存在
操作表資料
添加資料
向表中所有字段添加值
SQL> insert into userinfo
2 values(1,'xxx','123','[email protected]',sysdate);
已建立 1 行。
SQL> select * from userinfo;
ID USERNAME USERPWD
---------- -------------------- --------------------
EMAIL REGDATE
------------------------------ --------------
1 xxx 123
[email protected] 06-11月-19
向表中指定字段添加值
SQL> insert into userinfo(id,username,userpwd) values(2,'yyy','123');
已建立 1 行。
SQL> select username,userpwd from userinfo;
USERNAME USERPWD
-------------------- --------------------
xxx 123
yyy 123
向表中添加預設值
有兩種方式：
1.建表時添加預設值 2.建表後添加預設值
建表時添加預設值
SQL> create table userinfo1
2 ( id number(6,0),
3 regdate date default sysdate);--建表時添加預設值
表已建立。
SQL> insert into userinfo1(id)
2 values(1);
已建立 1 行。
SQL> select * from userinfo1;
ID REGDATE
---------- --------------
1 06-11月-19
建表後添加預設值
SQL> alter table userinfo
2 modify email default '無';--建表後添加預設值
表已更改。
SQL> insert into userinfo(id)
2 values(3);
已建立 1 行。
SQL> select id,email from userinfo;
ID EMAIL
---------- ------------------------------
1 [email protected]
2
3 無
SQL> insert into userinfo(id,email)
2 values(4,'aaa');
已建立 1 行。
SQL> select id,email from userinfo;
ID EMAIL
---------- ------------------------------
1 [email protected]
2
3 無
4 aaa
複制表資料
1.建表時複制 2.添加時複制
建表時複制
*全部列複制
SQL> create table userinfo_new
2 as
3 select * from userinfo;
表已建立。
*部分列複制
SQL> create table userinfo_new1
2 as
3 select id,username from userinfo;
表已建立。
SQL> select * from userinfo_new1;
ID USERNAME
---------- --------------------
1 xxx
2 yyy
3
4
在添加時複制
*全部複制
SQL> insert into userinfo_new
2 select * from userinfo;
已建立4行。
SQL> select id from userinfo_new;
ID
----------
1
2
3
4
1
2
3
4
已選擇8行。
*部分複制
SQL> insert into userinfo_new(id,
2 select id,username from user
已建立4行。
SQL> select id,username from
2 userinfo_new;
ID USERNAME
---------- --------------------
1 xxx
2 yyy
3
4
1 xxx
2 yyy
3
4
1 xxx
2 yyy
3
ID USERNAME
---------- --------------------
4
已選擇12行。
修改資料
注修改的資料需要與源資料類型一緻。
無條件更新
SQL> update userinfo
2 set userpwd='111111';
已更新4行。
SQL> select userpwd from userinfo;
USERPWD
--------------------
111111
111111
111111
111111
SQL> update userinfo
2 set userpwd='111',email='[email protected]';
已更新4行。
SQL> select userpwd,email from userinfo;
USERPWD EMAIL
-------------------- ------------------------------
111 [email protected]
111 [email protected]
111 [email protected]
111 [email protected]
有條件更新
SQL> update userinfo
2 set userpwd='123456'
3 where username='xxx';
已更新 1 行。
SQL> select username,userpwd from userinfo;
USERNAME USERPWD
-------------------- --------------------
xxx 123456
yyy 111
111
111
删除資料
chuncate table比delete高效。
全部删除
SQL> create table testdel
2 as
3 select * from userinfo;
表已建立。
SQL> delete from testdel;
已删除4行。
SQL> select * from testdel;
未標明行
部分删除
SQL> select username from userinfo;
USERNAME
--------------------
xxx
yyy
SQL> delete from userinfo
2 where username='yyy';
已删除 1 行。
SQL> select username from userinfo;
USERNAME
--------------------
xxx