Shulou(Shulou.com)06/01 Report--

Editor to share with you what is the basic operation and syntax of oracle database. I hope you will gain a lot after reading this article. Let's discuss it together.

Basic statement of oracle database

1. Oracle database operation

1. Create a database

Create database databasename

2. Delete the database

Drop database dbname

3. Back up the database

Full backup

Exp demo/demo@orcl buffer=1024 file=d:\ back.dmp full=y

Demo: user name, password

Buffer: cache siz

File: specific backup file address

Full: whether to export all files

Ignore: ignore the error, or overwrite if the table already exists

Export tables for system and sys users in the database

Exp demo/demo@orcl file=d:\ backup\ 1.dmp owner= (system,sys)

Export the specified table

Exp demo/demo@orcl file=d:\ backup2.dmp tables= (teachers,students)

By filter criteria, export

Exp demo/demo@orcl file=d:\ back.dmp tables= (table1) query=\ "where filed1 like 'fg%'\"

You can compress the export; add compress=y after the command; if you need a log, then: log=d:\ log.txt

Back up the database of the remote server

Exp username / password @ remote IP: Port / instance file= location:\ file name. Dmp full=y

4. Database restore

Open cmd and execute the following command directly without having to log in to sqlplus.

Complete reduction

Imp demo/demo@orcl file=d:\ back.dmp full=y ignore=y log=D:\ implog.txt

It is important to specify the log so that errors can be analyzed and remedied.

Import the specified table

Imp demo/demo@orcl file=d:\ backup2.dmp tables= (teachers,students)

Restore to a remote server

Imp username / password @ remote IP: Port / instance file= location:\ file name. Dmp full=y

II. Oracle table operation

1. Create a table

Create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],..)

Create a new table based on an existing table:

A:select * into table_new from table_old (create a new table using the old table) B:create table tab_new as select col1,col2 … From tab_old definition only

2. Delete the table

Drop table tabname

3. Rename the table

Alter table table name rename to new table name

4. Add a field

Alter table table name add (whether the default value of field name field type is empty); for example: alter table tablename add (ID int)

5. Modify the field

Alter table table name modify (whether the field name field type default value is empty)

6. Duplicate name field

Alter table table name rename column column name to new column name (where: column is the keyword)

7. Delete a field

Description:

Alter table table name drop column field name

8. Add primary key

Alter table tabname add primary key (col)

9. Delete the primary key

Alter table tabname drop primary key (col)

10. Create an index

Create [unique] index idxname on tabname (col … (.)

11. Delete the index

Drop index idxname

Note: the index is immutable and must be deleted and rebuilt if you want to change it.

12. Create a view

Create view viewname as select statement

13. Delete view

Drop view viewname

3. Oracle operation data

1. Data query

Select from [where] [order by [asc or desc]]

2. Insert data

Insert into table name values (values of all columns); insert into test values (1 recordzhangsanzhanjinghe 20); insert into table name (column) values (corresponding value); insert into test (id,name) values (2)

3. Update data

Update table set column = new value [where condition]-- > update eligible records update test set name='zhangsan2' where name='zhangsan'update table set column = new value-> update all data update test set age = 20

4. Delete data

Delete from table name where condition-- > delete records that meet the criteria delete from test where id = 1 world delete from test-- > delete all commit;-- > submit data rollback -- > rollback data delete mode can restore deleted data, but there is no way to submit it. When delete deletes, log will be recorded-- > delete will be slow and slow. Truncate table table name deletes all data, does not affect table structure, does not log, data cannot be recovered-- > delete very soon drop table table name deletes all data, including table structure, and will not log. Data cannot be recovered-- > deleted quickly

5. Data replication

Table data replication

Insert into table1 (select * from table2)

Copy table structure

Create table table1 select * from table2 where 1 > 1

Copy table structure and data

Create table table1 select * from table2

Copy the specified field

Create table table1 as select id, name from table2 where 1 > 1

Database replication command

Different database syntax is different (SQL Server and Oracle are examples), and replication includes situations where the target table already exists and the target table does not exist. Answer respectively:

In SQL Server, if the target table exists:

Insert into target table select*from original table

In SQL Server, if the target table does not exist:

Select*into target table from original table; (copy table structure and data) select*from target table from original table where1=0 (copy table structure only)

In Oracle, if the target table exists:

Insert into target table select*from original table; commit

In Oracle, if the target table does not exist:

Create table target table as select * from original table where 1: 0 (only copy table structure) create table target table as select * from original table; copy table structure and data after reading this article, I believe you have a certain understanding of the basic operation and syntax of oracle database, want to know more related knowledge, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.