Oracle merge query transaction SQL function knowledge learning. For more information, see
Oracle merge query transaction SQL function knowledge learning. For more information, see
Table query:
Merge query: Use the union keyword to remove duplicate rows that meet the condition.
The Code is as follows:
Select ename, sal, job from emp where sal> 2500 union select ename, sal, job from emp where job = 'manager ';
Union all is similar to union, but duplicate rows are not canceled.
Intersect is used to obtain the intersection of two results.
Minus is used to obtain the difference set of two results.
Make employee scott's position, salary, and subsidy the same as SMITH's. (Use subquery to modify data)
The Code is as follows:
Update emp set (job, sal, comm) = (select job, sal, comm from emp where ename = 'Smith ') where ename = 'Scott ';
Transaction:
Set save point
Savepoint
Cancel some transactions
Roll back to
Cancel all transactions
Rollback
Set as read-only transaction, used to count the information before a certain moment, and in the statistical process, there may be access, impact statistics, so before the statistics, set as read-only transactions, in this way, the results before the current moment will be saved, and subsequent modifications will not be displayed. The statement for setting as a read-only transaction is:
The Code is as follows:
Set transaction read only;
The transaction processing set is displayed.
SQL functions:
Display the display content in lower case. Use the lower function, for example
The Code is as follows:
Select lower (ename), sal from emp;
The display content is displayed in uppercase and lowercase. The upper function is used as well as the length function and substr function.
The Code is as follows:
Select * from emp where length (ename) = 5;
Select substr (ename, 1, 3) from emp;
Substr indicates that three values are obtained from the first value.
The names of all employees are displayed in uppercase.
Capital employee name
The Code is as follows:
Select upper (sub (ename, 1, 1) from emp;
Lowercase letters after the first letter
The Code is as follows:
select lower(substr(ename,2,length(ename)-1)) from emp;
Then merge the two results to get the content to be displayed:
select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) as name from emp;
Replace Function
Select replace (ename, 'A', 'I') from emp;
