Examples of Oracle SQL statements

Import and export of--oracle user objects exp devimage/[email protected]/testdb owner= ' devimage ' file=d:/devimage.dmp log=d:/ Devimage.logimp wxtest5star03/[email protected]/orcl fromuser= ' devimage ' touser= ' wxtest5star03 ' FILE=D:/ Devimage.dmp log=d:/wxtest5star03.log ignore=y --oracle Creating user Create users devtest10 identified by Dev10default Tablespace tbs_bcp_dattemporary tablespace user_temp;grant connect,resource,dba to Devtest10; --oracle Creating tablespace Create Tablespace  data_testkidswantloggingdatafile ' E:\app\administrator\oradata\orcl\ data_ testkidswant.dbf ' size 50mautoextend onnext 50m maxsize 20480mextent Management local; --oracle 11G when exporting with export, An empty table cannot be exported. A new feature in 11G that does not allocate segment when the table has no data to save space select ' ALTER TABLE ' | | table_name| | ' allocate extent; ' from User_tables where Num_rows=0 sql select statement complete execution order: 1, the FROM clause assembles data from different data sources ; 2. The WHERE clause filters the record rows based on the specified criteria, 3, the GROUP&NBSP;BY clause divides the data into multiple groupings, 4, uses the aggregation function for the calculation, 5, uses the HAVING clause to filter the grouping, 6, evaluates all expressions, 7, uses order  By to sort the result set. 8, select  set output.  --Conditional Branch Select USErid, LoginName, e-mail, case when e-mail is null and then ' null ' when email was not null and then ' NOT NULL ' end as Statusfrom T_ac_u ser;--get first 5 rows select * from t_ac_user  where rownum =1;--get random number Select Dbms_random.value () from dual;--get random string Select Dbms_random.string (' A ', 5) from dual;--gets any five elements select * FROM (SELECT * from T_ac_user ORDER by Dbms_random.value ()) where row Num <5;--converts null values to actual values select UserID, loginname, email, coalesce (email, ' 0 ') from t_ac_user;--replace the character with the specified character select translate ( Name, ' bl ', ' BX ') from UserInfo;   Replace B with B, replace L with x;--to remove all digits from the character select replace (translate (name, ' 0123456789 ', ' ########## '), ' # ', ') from userinfo;- -Null sorting Problem SELECT * from UserInfo order by age nulls last; or nulls first;--condition sorting, the commodity table in the current sale of the product price promotion when the promotion price, usually normal price, according to the current sales price to sort select goods_name, case when Is_sell = ' 1 ' Then Price when Is_sell = ' 0 ' then pricecx end as Nowprice from t_bd_goods order by Nowprice; or select Goods_name From T_bd_goods order by case when Is_sell = ' 1 ' then price
When the Is_sell = ' 0 ' then pricecxend;--oracle the intersection, the set, the difference is intersect,union All,minus, the same type of field is retrieved. Select Ename,job from emp
Minusselect ename,job from empv;--query No employee's department information using outer join select d.* from dept d,emp E where D.deptno=e.deptno (+) and E.deptno is null;--three tables joint query two tables in the connection and then and another outside the junction such as query all employee's name, department name, second occupation, some no second occupation, so with external connection select E.ename,d.dname,b.job from emp e,dept D , bonus B where e.deptno=d.deptno and e.ename = B.ename (+);-If there are no employees in some departments, and some employees do not have a department this situation to query all the information needs to use the full connection select D.deptno, E.ename from Dept D full join EMP E on D.deptno=e.deptno; Follow up with updates!

Examples of Oracle SQL statements