Oracle study notes 9

I learned the operations related to query and create a management table from my previous notes. This time, I learned how to process data, including inserting data into the table and updating data) and DELETE data ).

To save the original data of emp, we create a new table, which only contains part of the data in the original table and perform related data operations based on the table.

SQL> create table emp1
2 as (select empno, ename, sal, hiredate from emp );
 
Table created
 
SQL> desc emp1;
Name Type Nullable Default Comments
-------------------------------------------
Empno number (4) Y
ENAME VARCHAR2 (10) Y
Sal number (7,2) Y
HIREDATE DATE Y
 
SQL> select * from emp1;
 
EMPNO ENAME SAL HIREDATE
-----------------------------------
7369 SMITH 800.00
7499 ALLEN 1600.00
7521 WARD 1250.00
7566 JONES 2975.00
7654 MARTIN 1250.00
7698 BLAKE 2850.00
7782 CLARK 2450.00
7788 SCOTT 3000.00
7839 KING 5000.00
7844 TURNER 1500.00
7876 ADAMS 1100.00
7900 JAMES 950.00
7902 FORD 3000.00
7934 MILLER 1300.00
 
14 rows selected

The preceding query results indicate that emp1 is successfully created. Next, perform table-related data operations.

Add data

Basic Syntax:

Insert into table_name (column1, column2,..., column n) values (value1, value2,..., value n );

This syntax can insert only one data entry at a time.

For example, to insert a data entry to emp1, empno is 7000, ename is zhangsan, sal is 8000, and date is;

SQL> insert into emp1 (empno, ename, sal, hiredate) values (7000, 'hangsan', 8000, sysdate );
 
1 row inserted
 
SQL> select * from emp1 where ename = 'hangsan ';
 
EMPNO ENAME SAL HIREDATE
-----------------------------------
7000 zhangsan 8000.00 1

If the data to be inserted contains all the columns in the table, you can omit the column names in parentheses, however, the values of the column names in values must be in the same order as those in the table, that is, insert into emp1 values (7000, 'hangsan ', 8000, sysdate ); the execution results are the same as those above. In addition, the column names in the brackets behind the table name do not have a sequential order, as long as the order in the values value is also the corresponding column name, it is OK. The date above uses the current date of the system. In fact, we can specify the date, that is, insert into emp1 (empno, ename, sal, hiredate) values (7000, 'zhangsan', 8000, to_date ('1970-4-17 ', 'yyyy-mm-dd '));.

Note: The characters and dates must be enclosed in single quotes.

For example, only empno, ename, and sal are provided.

SQL> insert into emp1 (empno, ename, sal) values (7001, 'lisi', 9000 );
 
1 row inserted
 
SQL> select * from emp1 where ename = 'lisi ';
 
EMPNO ENAME SAL HIREDATE
-----------------------------------
7001 lisi 9000.00

Only part of the data is inserted above. This is also possible. However, data must be provided for the primary key and not null column. Conditions to meet the constraints.

 

Modify data

Basic Syntax:

UPDATE table

SET column = value [, column = value,...]

[WHERE condition];

Multiple data records can be updated at a time. Use the where clause to specify the update conditions to determine which columns need to be updated. If the where condition is not specified, all columns in the table are updated.

SQL> update emp1 set sal = 8000 where sal = 950;
 
1 row updated
 
SQL> update emp1 set sal = 8000;
 
14 rows updated

When the update condition is not specified, 14 data items are updated, and one is updated after the condition is specified. Generally, the update statement must follow the update conditions.

For example, change king's salary to 13000;

SQL> update emp1 set sal = 13000 where lower (ename) = 'King ';
 
1 row updated
 
SQL> select * from emp1 where lower (ename) = 'King ';
 
EMPNO ENAME SAL HIREDATE
-----------------------------------
7839 KING 13000.00

Delete data

Basic syntax

Delete from table

[WHERE condition];

If it is followed by the where condition, only the statements that meet the condition are deleted. If it is not followed by the where condition, all data is deleted.

Example: delete an employee numbered 7839.

SQL & gt; delete from emp1 where empno = 7839;
 
1 row deleted
 
SQL & gt; select * from emp1 where empno = 7839;
 
EMPNO ENAME SAL HIREDATE
-----------------------------------

Delete from emp1; if no deletion condition is specified, all records will be deleted.

The preceding insert, delete, and update operations can also use subqueries, but they must be based on another table.

 

Database transactions

Transaction: A group of DML operations on data in one or more tables as a unit. These DML statements are executed through INSERT, UPDATE, and DELETE statements.

Commit transactions:

Commit; Use commit to submit a transaction. Its role is to end the current transaction and save all the modifications executed by the current transaction to the external database. When rollback is used, the changed data in the database will not change.

Set the save point:

Savepoint savepoint_name; this statement is used to set a transaction rollback point. You can use this statement to set multiple save points.

Cancel transaction:

The ROLLBACK statement is used to cancel a transaction. The statement is used to cancel the changes made in the current transaction, that is, the data operated by update, insert, and delete is used to return to the original state.

ROLLBACK [TO savepoint _ name];

You can also use rollback to SAVAEPOINT_NAME TO specify which storage point TO roll back.