Oracle中创建和管理表详解
以下是对Oracle中的创建和管理表进行了详细的分析介绍，需要的朋友可以过来参考下 SQL /* SQL 对于表的操作: 创建表，修改表(添加新的列，改变当前某些列，删除列),删除表 SQL 创建表: create table(需要create table的权限) SQL 修改表: alter table tabl
以下是对Oracle中的创建和管理表进行了详细的分析介绍，需要的朋友可以过来参考下
SQL> /*
SQL> 对于表的操作: 创建表，修改表(添加新的列，改变当前某些列，删除列),删除表
SQL> 创建表: create table(需要create table的权限)
SQL> 修改表: alter table tablename add/modify/drop
SQL> 删除表：drop table tablename
SQL> */
SQL> show user;
USER 为 "SCOTT"
SQL> --访问hr用户下的表
SQL> select * from hr.employees;
select * from hr.employees
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> --测试defaul值
SQL> create table test1
2 (tid number,
3 tname varchar(20),
4 hiredate date default sysdate);
表已创建。
SQL> insert into test1(tid,tname) values(1,'Mary');
已创建 1 行。
SQL> select * from test1;
TID TNAME HIREDATE
---------- -------------------- --------------
1 Mary 12-6月 -11
SQL> --rowid rownum都是伪列
SQL> select rowid,rownum,empno from emp;
ROWID ROWNUM EMPNO
------------------ ---------- ----------
AAANA2AAEAAAAAsAAT 1 1122
AAANA2AAEAAAAAsAAO 2 1234
AAANA2AAEAAAAAsAAP 3 1235
AAANA2AAEAAAAAsAAQ 4 2222
AAANA2AAEAAAAAsAAR 5 2345
AAANA2AAEAAAAAsAAS 6 2346
AAANA2AAEAAAAAsAAA 7 7369
AAANA2AAEAAAAAsAAB 8 7499
AAANA2AAEAAAAAsAAC 9 7521
AAANA2AAEAAAAAsAAD 10 7566
AAANA2AAEAAAAAsAAE 11 7654
ROWID ROWNUM EMPNO
------------------ ---------- ----------
AAANA2AAEAAAAAsAAF 12 7698
AAANA2AAEAAAAAsAAG 13 7782
AAANA2AAEAAAAAsAAH 14 7788
AAANA2AAEAAAAAsAAI 15 7839
AAANA2AAEAAAAAsAAJ 16 7844
AAANA2AAEAAAAAsAAK 17 7876
AAANA2AAEAAAAAsAAL 18 7900
AAANA2AAEAAAAAsAAM 19 7902
AAANA2AAEAAAAAsAAN 20 7934
已选择20行。
SQL> --rowid:oracle维护一个地址，该地址指向了该行在硬盘上实际存储的位置
SQL> --关于varchar2和char
SQL> create table testchar
2 ( c char(5),
3 v varchar(5));
表已创建。
SQL> insert into testchar values('a','b');
已创建 1 行。
SQL> select * from testchar;
C V
----- -----
a b
SQL> select concat(c,'#'),concat(v,'#') from testchar;
CONCAT CONCAT
------ ------
a # b#
SQL> --添加新列
SQL> alter table testchar
2 add hiredate date;
表已更改。
SQL> desc testchar;
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------------------------------------
C CHAR(5)
V VARCHAR2(5)
HIREDATE DATE
SQL> --修改表
SQL> alter table testchar
2 modify c char(10);
表已更改。
SQL> desc testchar;
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------------------------------------
C CHAR(10)
V VARCHAR2(5)
HIREDATE DATE
SQL> --删除列
SQL> alter table testchar
2 drop hiredate;
drop hiredate
*
第 2 行出现错误:
ORA-00905: 缺失关键字
SQL> ed
已写入 file afiedt.buf
1 alter table testchar
2* drop column hiredate
SQL> /
表已更改。
SQL> desc testchar;
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------------------------------------
C CHAR(10)
V VARCHAR2(5)
SQL> host cls
SQL> --删除表
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EMP10 TABLE
EMP101 TABLE
TEST1 TABLE
BIN$gNM24ey8RKW0vjhtZ7ZFsA==$0 TABLE
TESTDELETE TABLE
TESTCHAR TABLE
已选择10行。
SQL> drop table testdelete;
表已删除。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EMP10 TABLE
EMP101 TABLE
TEST1 TABLE
BIN$gNM24ey8RKW0vjhtZ7ZFsA==$0 TABLE
TESTCHAR TABLE
BIN$aJrS9iffT4O1GcD0H3fepg==$0 TABLE
已选择10行。
SQL> --使用purge参数彻底删除表
SQL> drop table test1 purge;
表已删除。
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EMP10 TABLE
EMP101 TABLE
BIN$gNM24ey8RKW0vjhtZ7ZFsA==$0 TABLE
TESTCHAR TABLE
BIN$aJrS9iffT4O1GcD0H3fepg==$0 TABLE
已选择9行。
SQL> --oracle的回收站
SQL> --查看回收站
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TESTDELETE BIN$aJrS9iffT4O1GcD0H3fepg==$0 TABLE 2011-06-12:15:43:34
TESTDELETE BIN$gNM24ey8RKW0vjhtZ7ZFsA==$0 TABLE 2011-06-12:14:51:43
SQL> --清空回收站
SQL> purge recyclebin;
回收站已清空。
SQL> show recyclebin;
SQL> --关于约束:
SQL> --创建一个表，包含所有约束
SQL> create table myuser
2 ( userID number constraint pk primary key,
3 username varchar2(20) constraint c_name not null,
4 gender varchar2(2) constraint c_gender check (gender in ('男','女')),
5 email varchar2(20) constraint c_email1 not null
6 constraint c_email2 unique
7 deptno number constraint fk refereneces dept(deptno)
8 );
deptno number constraint fk refereneces dept(deptno)
*
第 7 行出现错误:
ORA-00907: 缺失右括号
SQL> create table myuser
2 ( userID number constraint pk primary key,
3 username varchar2(20) constraint c_name not null,
4 gender varchar2(2) constraint c_gender check (gender in ('男','女')),
5 email varchar2(20) constraint c_email1 not null
6 constraint c_email2 unique,
7 deptno number constraint fk refereneces dept(deptno)
8 );
deptno number constraint fk refereneces dept(deptno)
*
第 7 行出现错误:
ORA-02253: 此处不允许约束条件说明
SQL> ed
已写入 file afiedt.buf
1 create table myuser
2 ( userID number constraint pk primary key,
3 username varchar2(20) constraint c_name not null,
4 gender varchar2(2) constraint c_gender check (gender in ('男','女')),
5 email varchar2(20) constraint c_email1 not null
6 constraint c_email2 unique,
7 deptno number constraint fk references dept(deptno)
8* )
SQL> /
表已创建。
SQL> desc myuser;
名称 是否为空? 类型
----------------------------------------------------------------- -------- --------------------------------------------
USERID NOT NULL NUMBER
USERNAME NOT NULL VARCHAR2(20)
GENDER VARCHAR2(2)
EMAIL NOT NULL VARCHAR2(20)
DEPTNO NUMBER
SQL> insert into myuser values(1,'Tom','男','ddd@126.com',10);
已创建 1 行。
SQL> insert into myuser values(1,'Tom','男','ddd@126.com',10);
insert into myuser values(1,'Tom','男','ddd@126.com',10)
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (SCOTT.PK)
SQL> insert into myuser values(2,'Tom','啊','ddd@126.coddm',10);
insert into myuser values(2,'Tom','啊','ddd@126.coddm',10)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.C_GENDER)
SQL> --触发器也可以检查数据的正确与否
SQL> spool off
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1379
52
How to check tablespace size of oracle
Apr 11, 2025 pm 08:15 PM
To query the Oracle tablespace size, follow the following steps: Determine the tablespace name by running the query: SELECT tablespace_name FROM dba_tablespaces; Query the tablespace size by running the query: SELECT sum(bytes) AS total_size, sum(bytes_free) AS available_space, sum(bytes) - sum(bytes_free) AS used_space FROM dba_data_files WHERE tablespace_
How to encrypt oracle view
Apr 11, 2025 pm 08:30 PM
Oracle View Encryption allows you to encrypt data in the view, thereby enhancing the security of sensitive information. The steps include: 1) creating the master encryption key (MEk); 2) creating an encrypted view, specifying the view and MEk to be encrypted; 3) authorizing users to access the encrypted view. How encrypted views work: When a user querys for an encrypted view, Oracle uses MEk to decrypt data, ensuring that only authorized users can access readable data.
How to view instance name of oracle
Apr 11, 2025 pm 08:18 PM
There are three ways to view instance names in Oracle: use the "sqlplus" and "select instance_name from v$instance;" commands on the command line. Use the "show instance_name;" command in SQL*Plus. Check environment variables (ORACLE_SID on Linux) through the operating system's Task Manager, Oracle Enterprise Manager, or through the operating system.
How to uninstall Oracle installation failed
Apr 11, 2025 pm 08:24 PM
Uninstall method for Oracle installation failure: Close Oracle service, delete Oracle program files and registry keys, uninstall Oracle environment variables, and restart the computer. If the uninstall fails, you can uninstall manually using the Oracle Universal Uninstall Tool.
How to get time in oracle
Apr 11, 2025 pm 08:09 PM
There are the following methods to get time in Oracle: CURRENT_TIMESTAMP: Returns the current system time, accurate to seconds. SYSTIMESTAMP: More accurate than CURRENT_TIMESTAMP, to nanoseconds. SYSDATE: Returns the current system date, excluding the time part. TO_CHAR(SYSDATE, 'YYY-MM-DD HH24:MI:SS'): Converts the current system date and time to a specific format. EXTRACT: Extracts a specific part from a time value, such as a year, month, or hour.
How to import oracle database
Apr 11, 2025 pm 08:06 PM
Data import method: 1. Use the SQLLoader utility: prepare data files, create control files, and run SQLLoader; 2. Use the IMP/EXP tool: export data, import data. Tip: 1. Recommended SQL*Loader for big data sets; 2. The target table should exist and the column definition matches; 3. After importing, data integrity needs to be verified.
How to create cursors in oracle loop
Apr 12, 2025 am 06:18 AM
In Oracle, the FOR LOOP loop can create cursors dynamically. The steps are: 1. Define the cursor type; 2. Create the loop; 3. Create the cursor dynamically; 4. Execute the cursor; 5. Close the cursor. Example: A cursor can be created cycle-by-circuit to display the names and salaries of the top 10 employees.
How to set up users of oracle
Apr 11, 2025 pm 08:21 PM
To create a user in Oracle, follow these steps: Create a new user using the CREATE USER statement. Grant the necessary permissions using the GRANT statement. Optional: Use the RESOURCE statement to set the quota. Configure other options such as default roles and temporary tablespaces.
