课程视频：
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