Oracle processes data (DML + DDL + DCL) + transactions

SQL> /*
SQL> SQL statements
SQL> 1. DML Statement (Data Manipulation Language): insert update delete select
SQL> 2. DDL Statement (Data Definition Language): create/alter/drop/truncate table
SQL> create/drop view, create/drop index (sequence, synonym)
SQL> 3. DCL Statement (Data Control Language): commit rollback
SQL> */
SQL> -- insert
SQL> -- Insert new employee
SQL> insert into emp (empno, ename, sal, deptno)
2 values (1001, 'Tom );
SQL> -- implicit insertion of null values and explicit insertion of null values
SQL> -- Address operator &
SQL> insert into emp (empno, ename, sal, deptno) values (& empno, & ename, & sal, & deptno );
Input empno value: 1003
Enter the value of ename: 'Mike'
Input sal value: 3000
Enter the value of deptno: 20
Original Value 1: insert into emp (empno, ename, sal, deptno) values (& empno, & ename, & sal, & deptno)
New Value: 1: insert into emp (empno, ename, sal, deptno) values (1003, 'Mike ', 20)

One row has been created.
SQL> insert into emp (empno, ename, sal, deptno) values (& empno, '& ename', & sal, & deptno)
2;
Input empno value: 1005
Input ename value: fakfj
Input sal value: 3000
Input deptno value: 10
Original Value 1: insert into emp (empno, ename, sal, deptno) values (& empno, '& ename', & sal, & deptno)
New Value: 1: insert into emp (empno, ename, sal, deptno) values (1005, 'fakfj ', 10)

One row has been created.
SQL> -- & is equivalent to prepareStatemment, and ''needs to be added to the pre-compilation period to avoid errors.
SQL> rollback;

SQL> -- use address characters in DML statements
SQL> select empno, ename, &
2 from emp;
Input a value: sal
SQL> select * from &;
Enter the value of a: dept
SQL> -- insert (copy) a batch of data at a time
SQL> create table emp20 as select * from emp where 1 = 2;

The table has been created.

SQL> desc emp20
Is the name empty? Type
Certificate ---------------------------------------------------------------------------------------------------------------------------------------------------
Empno number (4)
ENAME VARCHAR2 (10)
JOB VARCHAR2 (9)
Mgr number (4)
HIREDATE DATE
Sal number (7,2)
Comm number (7, 2)
Deptno number (2)

SQL> select * from emp20;

Unselected row

SQL> -- insert all employees in department 20 of emp to emp20 at a time
SQL> insert into emp20
2 select * from emp where deptno = 20;

Five rows have been created.
SQL> truncate table emp20;

The table is truncated.

SQL> select * from emp20;

Unselected row

SQL> -- equivalent to: delete from emp20;
SQL> /*
SQL> difference between delete and truncate table
SQL> 1. delete is DML and truncate is DDL (DML can be rollback, but DDL cannot)
SQL> 2. delete one by one; truncate is destroyed first, and then rebuilt
SQL> 3. delet will produce fragments; truncate will not
SQL> 4. delete will not release space; truncate will
SQL> 5. But a lot of optimizations have been made in Oracle delete, so the delete performance is generally good.
SQL> */
SQL> set feedback off
SQL> -- how many lines are affected when the system prompts to close

Transactions

SQL> /*
SQL> Oracle transactions
SQL> 1. Start mark of the transaction: DML statement
SQL> 2. Transaction end flag: commit explicitly
SQL> implicitly submit DDL statements and exit normally
SQL> rollback: explicit rollback
SQL> implicitly exits abnormally, powers down, and goes down
SQL> */
SQL> -- save point
SQL> create table testsavepoint
2 (tid number, tname varchar2 (20 ));
SQL> set feedback on
SQL> insert into testsavepoint values (1, 'Tom ');

One row has been created.

SQL> insert into testsavepoint values (2, 'Mary ');

One row has been created.

SQL> -- Define a save point
SQL> savepoint;

The Save point has been created.

SQL> insert into testsavepoint values (3, 'moke ');

One row has been created.

SQL> rollback to savepoint;

Rollback completed.

SQL> select * from testsavepoint;

TID TNAME
------------------------------
1 Tom
2 Mary

2 rows have been selected.

SQL> commit;

Submitted.

Transaction isolation level

MySQL supports ONLY two read commited and Serializable data in four Oracle databases of SQL99. It also supports another read only specification that does not belong to SQL99.