Tables are divided into system tables and user tables.
Database objects
Tables (basic storage objects, composed of rows and columns), views (logical subset data obtained from one or more tables), sequences (unique serial numbers automatically generated), index (improves query performance), synonym (simplified access to data objects) standard naming rules
Table Name and column Name:
The first symbol must be a letter.
1-30 characters
Can only contain A-Z, A-Z, 0-9, _ $ #
The same user cannot have duplicate table names.
Oracle reserved words cannot be used for table and column name non-standard naming can use special characters, caused by "", such as create table "A-1" (int_id INT); unique to the data object, object owner. object
Select * From Informix. users; query the user _ of the data dictionary user. The DBA _ data type can be viewed by the system administrator.
Varchar2, Char, number, date, long, clob, raw, long raw, blob, bfile, and rowid
As select * from users; add a column to modify the table structure
Modify a column (data type, size, default value)
Delete a column
Define the default value for the new column
Alter table users
Add (job varchar2 (10) default 'unknow'; alter table users
Modify (name varchar2 (20); alter table users
Drop column job; Delete columns in a set, set to unused first
Alter table Test
Set unused job; alter table Test
Drop unused columns; delete a table
Delete table structures and records
Drop table test; rename object test to T1; clear table
No logs, DDL statements, only records are cleared, and the table structure still exists.
Truncate table T1; Comment table comment on table users is 'ses are user's table'; view comment information all_col_comments through data dictionary
User_col_comments
All_tab_comments
User_tab_comments Constraints
Maintain data integrity for rows, columns, and reference Columns
Not null
Line
Unique
Primary Key
Reference
Foreign key
Checkcreate table test1
(ID int not null,
Name varchar2 (20 ),
Age number,
Constraint uk_test unique (ID, name) where not null is the system name, And uk_test is the user name primary key, which can define a unique record
Alter table Test
Add constraint pk_test primary key (ID); Create Table Test2
(RID int,
Name varchar2 (10 ),
Constraint fk_test foreign key (RID) References test (ID); check Constraint
Value range of a column: alter table Test
Add constraint ck_test check (name like 'l % '); alter table Test
Drop constraint ck_test; alter table Test
Drop constraint pk_test cascade; temporarily disable the Constraint
Alter table Test
Disable constraint pk_test; alter table Test
Enable constraint pk_test; alter table Test
Drop column ID cascade constraints; get constraint information
Select constraint_name, constraint_type from user_constraints;
Select * From user_cons_columns; creating a view is a logical object that does not contain any data. It is a SELECT statement named "create view testview ".
As
Select ID, name, age from users; view usage limits Data Access
Simplify complex queries
Data independence
Different users can observe that the data AAA table does not exist from different perspectives, and force the creation of views
Create Force view testview2
As
Select * from AAA view cannot be directly modified using alter. You must use create or replace to modify the data in the base table through view update.
Update testview set name = 'abc'; create or replace view testview2
As select * from users where age> 20; update testview2 set age = 18 where id = 212; after data is updated, data with ID 212 is not in view testview2. To prevent modification, you can use with check Option
Create or replace view testview2
As select * from users where age> 20 with check option; read-only with read only
Create or replace view testview2
As select * from users where age> 20
With read only; Delete
Drop view testview2; TOP-N analysis select * from (select ID, age from users); select age, rownum from (select * from users order by age DESC) Where rownum <5; other data object Sequences
Index
Synonym creation Sequence
Create sequence testse1
Increment by 1
Start with 1; after a sequence is created, the sequence number cannot be modified. Other values can be changed to the current value and the next value, currval and nextval.
Select testse1.nextval from dual; you can fill in the table with sequence values.
Create Table testtable1
(Id int); insert into testtable1
Values (testse1.currval) deletes the sequence drop sequence testse1; obtains the sequence information
Select * From user_sequences; index some pointers and paths, pointing to the data in the table.
Indexes can be created automatically when primary keys and unique constraints are created. Other indexes can be created manually.
Create index testindex1
On users (name); no need to create an index
The table is small.
The where condition is not commonly used, and the table data is often changed to delete indexes.
Drop index testindex1; the synonym is not a table under my name. You can create a synonym to simplify access.
Create public synonym T1 for user1.test; select * From T2; equal
Select * From user1.test; drop public synonym T1; ------------------------------------ exercise -----------------------------------------/* Create and delete synonyms */
Select * from EMP;
Create synonym emp_ny for EMP;
Create public synonym pub_emp_ny for employees; -- create a public Synonym
Drop synonym emp_ny; -- delete a synonym
Drop public synonym pub_emp_ny; -- delete a public Synonym
/* Create and delete a sequence */
Create sequence seqid
Increment by 1
Start with 1001
Max value 1200
Minvalue 1
Cycle
Nocache;
Drop sequence seqid; -- delete a sequence
/* Create and delete a view */
Create view emp_ve as select * from EMP;
Create or replace view emp_ve as select * from EMP where address like '% USA %' -- conditional view Creation
Create Force view employee_ve as select * from ABC; -- create a view with errors, meaning to create a view even if the table does not exist.
Drop view emp_ve; -- delete a view
/* Create or delete an Index */
Create index employees_empid on employees (dateofjoining); -- Standard Index
Create index employees_empname on employees (ename, job); -- Composite Index
Create index emp_ename on EMP (ename) reverse; -- reverse key index
Create bitmap index emp_bit_address on EMP (Address); -- bitmap Index
Create index abc_name on ABC (lower (aname ));
Drop index employees_empid; -- delete an index
/* Create an index organization table */
Create Table place
(
Place_id number (5) primary key,
Pname varchar2 (20 ),
Address varchar2 (20 ),
Station varchar2 (10)
) Organization index;
