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

1. Create a table

Sql code

Create table test (

Id varchar2 (10)

Age number

);

2. Back up the table

Sql code

Create table

As

Select * from test group by id

3. Delete the table

Sql code

Drop table test;-- deletes table structure and table data

4. Clear the table

Sql code

Truncate table test;-- empties the datasheet data and has no room to return

Delete from test;--- empties the data table data, there is room for return

5. Add a field to download

Sql code

Alter table test add (

Name varchar2 (10)

Interest varchar2 (20)

);

6. Delete a field

Sql code

Alter table test drop name

7. Update data

7.1 Update a piece of data

Sql code

Update test t

Set t. Idling

Where t.id is not null

Commit

7.2 updating multiple pieces of data from other tables

Sql code download

Update test t set t.name = (

Select tm.name

From test_common tm

Where t.id=tm.id

);

Commit

-- Note: when update data, it is best to create a separate table of sql in the update subquery to improve the update speed.

7.3 after querying the data in PL/SQL, directly enter the edit mode to change the data.

Sql code

Select * from test for update

-- Note: after the update operation is completed, lock the data table and perform the commit submit operation.

8. Query data

Sql code

Select * from test

9. Query the number of data tables

Sql code download

Select count (0) from test

-- Note: count (0) or other numbers are more efficient and efficient than count (*).

10. Insert data

10.1 insert multiple fields in a piece of data

Sql code

Insert into test (C1 ~ C2) values (1 ~ ~ 'Technical Department')

Sql code

Insert into test (C1 ~ C2) select C _ 1 ~ 2 from test_common

Commit

10.2 insert multiple pieces of data

Sql code

Insert into test (

Id

Name

Age

)

Select

Id

Name

Age

From test_common

Commit

-- remarks: 1. When inserting multiple pieces of data, there is no values after the insert into statement, so the data query statement is stitched directly; 2. After insert, update, delete and other operations on the data table in oracle, the commit submission should be performed, otherwise the operation of the database is prohibited in the system, because the database has been locked, which is a prevention mechanism for the database to prevent confusion caused by multiple people modifying the database data at the same time.

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.