SQL statements in the Orcal

1. Insert statement:
①insert into Dept (Deptno,dname,loc) VALUES (50, ' Sales department ', ' Beijing ');
② Call system Time (Sysdate):
INSERT into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (7196, ' Hill ', ' department manager ', 7782,Sysdate,2000,null,10);
View results: SELECT * from EMP where empno=7196;
③ Insert the specified time value:
INSERT into EMP values (2296, ' Xiaoshan ', ' Mananger ', 7782,to_date (' June 3,97 ', ' MON dd,yy '),300,null,10)); INSERT into EMP (empno,ename,hiredate) VALUES (9000, ' Zhangqs ',to_date (' 2008-8-30 ', ' yyyy-mm-dd '));
View results: SELECT * from EMP where empno=9000;
④ Setting display format:
Set Linesize 500
Set PageSize
SELECT * from EMP;
⑤run command or "/" to re-execute the most recently executed SQL statement;
⑥set the time display format：alter session set nls_date_format= ' Yyyy-mm-dd ';
⑦To use a binding variable:
INSERT INTO Dept (DEPNO,DNAME,LOC) values(&department_id, ' &department_name ', ' &department_location '))
INSERT into EMP (empno,ename,sal) values(&employee_no, ' &employee_name ', &employee_salary);
⑧ copying data from other tables:
INSERT into managers (id,name,salary,hiredate)
Select Empno,ename,sal,hiredate from emp where job= ' MANAGER ';

2. Modify the records in the table:
Update EMP
Set deptno=20
where empno=9001;

3. Delete the data in the table:
Delete from dept
where dname= ' development ';

4. Database transactions : A transaction is a set of logical operations of a database; Multiple DML statements can form transactions, and a single DDL statement can form a transaction; a single DCL can constitute a transaction;
Use the commit and rollback commands;

5. Transaction savepoint: a transaction savepoint can be established where needed;
savepoint Update_emp; --Create a savepoint;
rollback to Update_emp; --Will fall back to the designated savepoint;

SQL statements in the Orcal