Oracle common functions and their usage

01, Getting Started Oracle Chapter Objective: Master Oracle installation, startup and shutdown basic management and common tools simple backup and recovery skilled use of SQL, master Oracle Common Objects master database design and optimization of basic methods

http://jingyan.baidu.com/article/5d6edee228308899eadeec3f.html

Oracle Database: Collection of a series of physical files

One instance is one database, but in 12c (c stands for cloud) RAC cluster, multiple instances can correspond to one database

03, SQL Programming 1.oracle between and is closed interval, MySQL is open interval 2. Modify table name statement: ALTER TABLE teacher Rename column Gandar to gender; Two single quotes in 3.oracle denote a quotation mark, for example: SELECT ' INSERT INTO dept values (' ' | | deptno| | "," | | dname| | "," | | loc| | '); The From Dept order by Deptno effect is as follows:

4. Write the criteria for filtering the most data at the end, which will compare to SQL 5. Oracle Functions: A single-line function, an aggregate function, an analytic function requirement: a function with one or more parameters, and returns a value   1) Single-line function: numbers, characters, dates, conversions, other query returns only one value per row 2) Aggregate function: Returns results based on a set of rowsAvg, Min, max, sum, counthaving words: A condition that specifies a GROUP BY clause to retrieve rows 3) Analysis function: Calculates aggregated values according to a set of rows, accumulates rank, returns multiple rows Row_number, rank, DENSE_RANK4) Set operators: Union, UNION All, intersect, minus combine the results of two queries into a single result select Tname,deptno,rank () over (partition by Deptno ORDER by Sal) Rank,dense_rank () over (partition by Deptno order by Sal), Row_nu Mber () over (partition by Deptno order by Sal), Row_number () over (order by Sal) Row_number from Teachersql Analysis: Rank () allows for a side rank, under A ranking continues from the end of the previous ranking; Dense_rank () allows for parallel rankings, the next ranking will skip the occupancy; Row_number () does not allow for side-by-side rankings. partition by what group; Order by is ranked by what. Query results such as:

6.oracle Paging QueryPseudo-columns: Like a table column, not stored in the table, pseudo-columns can be queried from the table, but cannot add or remove the common pseudo-columns: ROWID, Rownumrowid: The storage address of the table row, you can uniquely identify a row in the database, you can quickly locate a row in the table by ROWID. The peso is fast .RowNum: Is the ordinal of the row in the result set returned by the query, which can be used to limit the number of rows returned by the query (three-layer implementation: 1) with a constraint, a result set of 2) to filter the maximum range (<= maximum) 3) to the result set to filter out the smallest range (starting from the first) 04, Database design This chapter aims at: 1. Understanding the normalization of databases--three paradigms 2. Understanding the Design Database Step 3. Learn how to draw a e-r diagram of a database  1. The first paradigm of the three paradigms: the goal is to ensure the atomicity of each column, if each column is an irreducible minimum data unit, satisfies the first normal form: the relationship satisfies 1NF, and the other column other than the primary key is dependent on the primary key, then the third normal form of the second normal form is satisfied: if a relationship satisfies 2NF, and other columns except the primary key are passed, and the third normal form  2 is satisfied. Design Step 1) collect information 2) identify object 3) identify the relationship between an object 4) identify the relationships between objects note: The primary foreign key is not typically established in real development, but is manipulated using logical associations.  3. Drawing e-r diagram using PowerDesigner to draw e-r diagram  05, SQL Optimization and architecture This chapter aims to: 1. Learn the basic techniques of SQL Optimization 2. Learn how to use indexes to improve query efficiency 3. Understanding partitioning a Table 4. Understanding Common Database objects  1.sql Optimization tips 1) General optimization tips:      Do not use * Instead of all column names       Delete all data with truncate instead of delete      Use NOT exists instead of in       exists instead of in      exists instead of distinct note: Three o'clock is useful before 11g, 11g after itself has been optimized   5th example is as follows: The query out of the teacher table of the different department number  select distinct t.deptno from teacher T where T.deptno are not null; &N Bsp;select * FROM dept where exists (select 1 from teacher where Teacher.deptno = dept.deptno)  2) Table connection method     & nbsp. Selection of the driver table     Connection Order of the &NBSP;WHERE clause: Table join relationship is placed in front, the more filter records should be placed in the back  sql execution order is from right to left.   Driver table: From the back of the right side of the table is the driver table (two tables are not indexed); In the case of an index, the table that is not indexed is the driver table       Driver table to select a small table   3) index index is divided into logical classification and physical classification, &Nbsp;  

Oracle common functions and their usage