Some basic knowledge and considerations for Oracle

I. Oracle Foundation 1.1 DDL (data definition Language) Definition language Create DROP,DESC (note that this operation can only be performed in PL/SQL Developer command Windows and cannot be performed in the Windows window) table tablename1.2 DML (data manipulation Language) operation language Insert, Delete,update,... 1.3 TCL (Transaction control Language) transaction control language begin Transaction Commit rollback1.4 DQL (data query Language) querying language Select1. 5 DCL (Data Control Language) revoke2.oracle Some internal query statement 2.1 Understanding of Oracle's character encoding Select Userenv (' Language ') from dual;3 . About VARCHAR2 's Chinese question create table foo (C1 varchar2 (2)); INSERT into foo values (' Hello '); SQL Error: ORA-12899: Column "SCOTT". " FOO "." C1 "value is too large (actual value: 4, Maximum: 2) The solution to this problem is to use the NVARCHAR24. String Connection Select FName | | '. ' | | LName from Foo_6 can also be connected with the Concat function: Concat (fname,lname) 5. A set of commonly used functions trim to go to the left space, LTrim to RTrim to the right space lpad the left padding character to the specified length Lpad (str, padding to the length, [filled characters, default space]) Rpad the right padding character to a specified length lower into lowercase upper into uppercase Initcap first uppercase length take string length substr substring instr find string last_day (date) Last day of month Months_between (Date1, Date2) Date1-date2 number of months least (date1, date2) Date1 and Date2 the earlier one greastest (Date1,date2) Date1 and date2 closer to that one round(date) rounds the trunc (date) to the time and seconds to remove select To_char (trunc (sysdate), ' Yyyy-mm-dd Hh24:mi:ss ') from dual; Extract extract (year from date value) Extract (month from date value) Extract (Day from date value) extract (hour from date value) extract (minute from date Value) Extract (second from date value) Select Extract (year from sysdate) from dual;6. Null function (Oracle-specific function) NVL (ARG1,ARG2) If arg1 is null, Returns ARG2 if ARG1 is not NULL, returns Arg1select NVL (c1, ' hahaha ') from GOO_1;NVL2 (ARG1,ARG2,ARG3) If arg1 is null, returns ARG3 if ARG1 is not NULL, Return Arg2select NVL2 (C1, ' hehehe ', ' hahaha ') from goo_1;7. Primary key, uniqueness of rows in a data table 1) Create primary key primary key is also column (Multi-column-union primary key), generally no business meaning (cannot change), uniquely identifies a row in the data table must have a primary key type preferably NUMBERCONSTRAINT constraint name primary KEY (primary key column) CREATE TABLE Stu (stu_id number one), Stu_no number (8), Stu_name varchar2 (2), constraint STU_PK primary key (STU_ID)), primary key approx. Shing cannot repeat insert into Stu values (100,2013001, ' abc ') for null primary key; INSERT into Stu values (null,2013002, ' BCD '); INSERT into Stu VALUES (100,2013003, ' CDE '); 8. Drop truncate deletedrop table Stu; Delete table, free space TRUNCATE TABLE Stu; Preserve the structure of the table, delete the data, free up space, not recover, fast delete from Stu deleted data, can be restored,9 slower. Create a new table from the other tables creating table Emp_1 as Select ID, last_name, first_name, salary from s_emp; Note: Tables created can replicate structures and data but do not copy constraint 10. Decode function (Oracle-specific function) decode (value IF1 then1 if2 then2...else) If VALUE=IF1 returns THEN1 otherwise if Value=if2 returns THEN2 ... The default return value is else other specific functions include To_char, to_date, to_number, trim, length, substr (subscript starting from 1), and 11. Use Union,union all, intersect (intersection), Minus (difference set) Note: The difference between Union all and union all in the database, the Union and UNION ALL keywords combine two result sets into one, but both are different in terms of usage and efficiency. The Union will filter out duplicate records after the table link is made, so the resulting set of results will be sorted after the table is connected, the duplicate records are deleted and the results returned. In fact, most applications do not produce duplicate records, most commonly, the process table and the history table union, such as SELECT * FROM pro_table Union SELECT * FROM his_table This SQL takes out the results of two tables first at run time. The sort space is then used to sort the deleted duplicate records, and finally the result set is returned, which can cause the disk to be sorted if the amount of data in the table is large. The union all simply merges two results and returns, so that if there are duplicate data in the two result sets returned, the returned result set will contain duplicate data. In terms of efficiency, union All is much faster than union, so if you can confirm that the combined two result sets do not contain duplicate data, then use UNION ALL. Summary: The Union de-weight and sort union all contain a weight that is not sorted by 12.2. Connect by and start with (Oralce SQL) traversal tree, level equivalent to tree rank, provides a dedicated pseudo-column levelselect levels, empno, Ename,mgrfrom emp start with ename = ' KING ' Connect by prior Empno=mgr, where the Connect by PRIOr empno=mgr indicates that the empno of the previous record in this article is the mgr13 of this record. Advanced Grouping function Rollup function multiple lines "Subtotal" for grouped columns null for the result of the aggregate function for "Subtotal" select Job, sum (SAL) from EMP GROUP BY Job Select Job, sum (SAL), round (avg. Sal), Count (Empno), Max (SAL) from EMP Group by rollup (job); Select Dname,j Ob,sum (SAL) from the EMP INNER JOIN dept using (DEPTNO) Group by Cube (dname, job) Order by dname, job for a specific dname also ask for the aggregate function of "Subtotal" GR ouping function grouping Set function Select Grouping (dname), grouping (Job), dname, Job, sum (SAL) from EMP INNER JOIN dept using (DEPTNO) GROUP BY rollup (dname, job) Order by dname, job; query Subtotal Select dname, Job, sum (SAL) from EMP INNER JOIN dept using (DEPTNO) G Roup by grouping sets (dname, job) Order by dname, job; If you want the query to have only subtotals, use grouping sets because it is more efficient than cube and ROLLUP14. Ranking function select Rank () over (order by Sal Desc) r,ename, Sal from Empselect Dense_rank () over (order by Sal Desc) R,ename, Sal From EMP15. Calculate line number Select Row_number () over (order BY Sal Desc) num, ename from EMP; ii. database design 1. Tri-paradigm 1) column values are unique and cannot have duplicate column values (must obey) 2) The property is completely dependent on the primary key (must be adhered to) a. The first paradigm B must be met. Must have primary key C. The other columns mustFully dependent on the primary key 3) The property does not depend on other non-primary attributes (in particular cases, for efficiency reasons, there can be redundant designs-violations of the third paradigm) a. Second paradigm B must be observed. Remove the pass-dependent 2. E-r Relationship (E-R) 1) carefully read the requirements specification of the system, study the business requirements 2) design and draw the E-r diagram 3. Database design document three. Oracle other objects 1. Sequence Oracle can generate a primary key by sequence CREATE table Foo (foo_id number one), Foo_value varchar2 (a), constraint FOO_PK primary key (FOO_ID)); s Elect Max (foo_id) from Foo; XXX create sequence ddlcreate sequence sequence name [start with value] [Incremet by value] Delete sequence Ddldrop sequence sequence name example: Create sequence Seq_foo start W ith increment by 2 Gets the next value from the Pseudo-column (the sequence value is incremented) Nextval gets the current value (the sequence value is not incremented) Currvalselect Seq_foo.nextval from Dualselect seq_ Foo.currval from dual using sequence values as primary key insert into Foo (foo_id,foo_value) VALUES (Seq_foo.nextval,?) 2. Index INDEX1. Always query according to a column; 2. The selected column does not exceed the total number of 10% in order to improve query efficiency, you can create an index. Advantages: High query efficiency based on this column disadvantage: space occupancy, low efficiency when inserting the primary key creates an index by default for example: Create INDEX i_account_real_name on account (real _NAME) Note: Indexes and functions select Id,real_name from Account where upper (real_name) =? Function-based index CREATE INDEX i_account_real_name on Account (Upper (Real_name))--can be used to view all index information SELECT * from user_indexes--can be used to view all tables select * from User_tables; Causes of Oracle Index failureMany: 1. Implicit conversions cause an index to fail. This should be taken seriously. It is also a common mistake in development. Because the table's field TU_MDN is defined as VARCHAR2 (20), the field is passed to Oracle as the number type in the query, which causes the index to be invalidated. Example of error: SELECT * from Test where tu_mdn=13333333333; Right Example: SELECT * from Test where tu_mdn= ' 13333333333 '; 2. Operations on an indexed column cause an index to fail, and I refer to an example of an operation including (+,-,*,/,!, etc.) errors for an indexed column: SELECT * from Test where id-1=9; The correct example: SELECT * from Test where id=10; 3. Using an Oracle intrinsic function causes the index to fail. For this scenario, you should create a function-based index. Example of error: SELECT * from Test where round (id) = 10; Description, at this point the index of the ID has no effect on the correct example: first establish the function index, CREATE index TEST_ID_FBI_IDX on test (round (ID)); Then select * FROM Test where round (id) = 10; At this time the function index functioned 1,<> 2, a separate >,< (sometimes used, sometimes not) 3. View 1) Ease of access 2) simplify complex query authorization grant CREATE VIEW to Scott creates viewcreate view v_emp_1 as select Empno, ename, job from EMP; (simple) Create View v_emp_sal as select Empno,ename,sal fromemp where sal>1000; (simple)//You can check whether the data can be inserted through the view (whether it conforms to the query criteria for that view) CREATE view V_emp_sal_1 as select Empno,ename,sal fromemp where sal>1000 with CH ECK option constraint Check_v_emp_sal_1; (simple)//read-only view, only dqlcreate view V_emp_Sal_2 as Select Empno,ename,sal fromemp where sal>1000 with Read only constraint check_v_emp_sal_2; (simple) Create View v_emp_dept as select Empno,ename,dname from emp INNER JOIN dept using (DEPTNO); (complex) CREATE View V_emp_num as Select Deptno, Count (empno) emp_num from EMP where deptno are NOT null GROUP by DEPTNO (complex) to VI EW Operation DQL (OK) select * from V_EMP_1DML (for simple view OK) is actually the Dmlinsert into v_emp_1 values (8888, ' hahaha ', ' clerk ') of base Table 4. Constraint 1) FOREIGN KEY constraint create table emp_1 (-------A department can have more than one employee, foreign key in a multi-party ID number (one), name VARCHAR2 () not NULL, SAL number (12,2) is not Nu ll, DeptID number (4), Constraint Pk_emp primary key (ID), Constraint fk_emp_dept foreign key (DeptID) references dept_1 (ID) CREATE TABLE Dept_1 (ID number (4), name VARCHAR2 (+) NOT NULL, constraint PK_DEPT1 primary key (ID)) ALTER TABLE SERVICE a DD Constraint Fk_service_account foreign KEY (account_id) references account (ID); ALTER TABLE SERVICE DROP constraint fk_service_account5. stored procedure 1. Run a program that operates within the database for data (PL/SQL language in Oracle) 1). PL/SQL block--helloworldset Serveroutput on declare--the declaration of a variable a number (5): = 100; b number (5): = 200; C Number (5); Begin-Program C: = a + b; Dbms_output.put_line (' c= ' | | c); End /2) IFSet serveroutput on;declarea1 Number (5): = 100;a2 Number (5): = 200;a3 number (5); beginif A1>a2 then a3:=1;elsif A1 100;end loop;dbms_output.put_line (' v_sum= ' | | v_sum); end;/4) Forset serveroutput on;declarev_sum Number (5): = 0;begin--I can not declare for i in 1..100 loop v_sum: = V_sum + i; End Loop;dbms_output.put_line (' v_sum= ' | | v_sum); end;/5) cursor cursor set serveroutput on;declare--declaration variable is the type of the data table column V_empno Emp.empno%type; --%type The type of empno for the EMP table V_ename emp.ename%type;--declares a cursor corresponding to a query cursor v_emp_cursor is select Empno,ename from emp ORDER BY ename;begin--fetching data from a cursor open v_emp_cursor;loop--fetches a row from the cursor into the variable--after it is removed, the cursor moves down the fetch v_emp_cursor into V_empno, v_ename;-- Exit condition exit when v_emp_cursor%notfound;--print output dbms_output. Put_Line (v_empno| | ', ' | | V_ename); end Loop;close V_EMP_CURSOR;END;/6) rowtypeset serveroutput on;declare v_dept dept%rowtype; --so you can access--V_dept.deptno, V_dept.dname, v_dept.loc cursor v_dept_cursor is select Deptno,dname,loc from Dept;begin open V_dept_curso R Loop fetch v_dept_cursor into v_dept; Exit when V_dept_cursor%notfound; Dbms_output. Put_Line (V_dept.deptno | | ', ' | | v_dept.dname| | ', ' | | V_DEPT.LOC); End Loop; Close V_DEPT_CURSOR;END;/7) Simple stored procedure procedurecreate or Replace procedure HelloWorld (A1 in number, A2 in number, sum out Number, sub out number) as begin sum: = A1+A2; Sub: = a1-a2;end;/

Some basic knowledge and considerations about Oracle