Database Database SQL statement Five

Tag:sel    Execution time    sql statement     atomicity     data     values    start     operation    insert   

  set operation  Union (two sets if there is a repeating part, Then only one repeating part is shown) union All unions (two sets if there is a repeating part, then the repeating portion is displayed two times) intersect intersection minus difference set -- The enquiry department number is employee information for numbers 10 and 20th  select  * from  EMP where  deptno=10  union  select  * from  EMP where  deptno=20   collection operations Considerations:  1   2 . The header of the first collection as the table header for the result set 

 DML Data manipulation Insert statement syntax inserts data into a table by using the INSERT statement (column name 1, column Name 2, ...) VALUES (value1,value2,...)-- Insert a full piece of data (no need to add a column name) insert INTOEMP values (7288,'JACk','Clerk',7902,'1 July-December on -80', the,NULL, -)--Insert an incomplete piece of data (need to add a column name) insert INTO EMP (EMPNO, Ename,job,mgr,sal,deptno) VALUES (7289,'JHON','Clerk',7902,1780, -)--two ways to insert null values1implicit insertion (incomplete insertion is implicit insertion)2. Explicit insertion (NULL is an explicit insertion)-- Insert the specified value of the date insert into EMP values (7293,'Blackh','Clerk',7902, To_date ('1990-2-4 9:30:28','YYYY-MM-DD Hh12:mi:ss'),3400,NULL, -)--& in the Insertoperator (requires user input EMPNO and DEPTNO values) insert INTO EMP (EMPNO,ENAME,JOB,MGR,SAL,DEPTNO) VALUES (&empno,'JHON','Clerk',7902,1780,&deptno)--Fallback Transaction SQL> rollback;--Oracle Open Transaction by default--Create a new table (copy the table structure of the EMP) CREATE TABLE T1 as     Select* fromEmpwhere 1=2--from the EMP table, insert employee data from department 10th into the T1 table insert into T1 (Select* fromEmpwheredeptno=Ten );--subqueries are not only used in select, but can also be used in the INSERT clause--do not write the values clause--the list of values in the subquery should correspond to the column names in the INSERT clause

 update statement syntax -- update data -- Multiple Data update table names can be updated at one time  set  Colnum1=value1,colnum2=value2,...  where   conditioneg:sql  > update t1 set  ename=< Span style= "COLOR: #800000" > " joke   , Sal=3000  where  empno=7782 ; 

DELETE statement -- from table name wherefromwhere empno= 7782;

 truncate statement truncate: Empty table truncate tables T1; --Delete all data in the table

the difference between delete and truncate 1 . Delete Delete truncate first destroy and rebuild 2 The . Delete language is a DML language truncate is a DDL language that can be flashed back, done wrong and committed, and can be undone by a flashback undo Operation DDL Language can not be flashed back 3 . Delete is deleted by one article, which will produce fragments and truncate will not produce fragmentation 4 . Delete will not free up space, truncate will 5 . Delete can be rolled back, and truncate cannot be 6. Oracle Delete fast MySQL truncate fast

SQL statement Optimization ⑤:oracle Delete table data, delete fast, MySQL delete table data, truncate fast.

Common Oracle Commands --set timing off;-- turn off SQL execution time display --set feedback off;-- turn off data feedback [ Email protected]:\sql.sql-- Import SQL file from C drive --set timing on;--Open SQL execution time display

database Transaction concept: One or more DML statement features: either success or failure characteristics: atomicity Consistency Isolation persistence transaction isolation: When multiple clients operate the database at the same time, their operations are isolated,              Because Oracle instances do not commit operations directly to the database, operations between multiple transactions do not affect each other

start and end of a transaction 1 . Flag for transaction start: DHL language (Oracle default transaction is open ) 2 the flag for the end of the transaction    commits: explicit commit commit          implicit commit (1) Executes the DDL language, the DDL language can implicitly submit the data            eg:create table Language            (  2) normal exit    rollback: Explicit: rollback          an implicit power outage, abnormal exit, system error

Rollback to save point 1 . Use the SavePoint statement to create a savepoint in the current transaction Eg:savepoint al; -- Create savepoint A12. Use the rollback to savepoint statement to roll back to the created SavePoint Eg:rollback to savepoint A1; Roll back to A1 save Point

Database Database SQL statement Five