DB Note (Oracle)

DB Notes (Oracle)

A Oracle installation, service startup, database setup, user establishment (skip).

Two SELECT statement:

(i), single-line function:

1. Expressions and null calculations, the result is null, that is, no display (that is, the data is missing!). ）。 So the general function nvl (column name, replacement value), such as NVL (sal,0).

2. String concatenation: | | Example: Select Id| | ' ---' | | Uname from EMP;

3. Sorting and filtering (order by and where):

A) select* from emp order by Sal Asc,comm Desc; This expression is ranked by salary in ascending order, with the same salary in reverse.

b) select* from emp Order by (Sal + NVL (comm,0)) desc; This expression is: Sort by the sum of salary and bonus, note: If you do not use NVL directly with Comm, you will lose some values .

c) select* from emp where uname > ' CBA '; The filter of the string, this specific still do not understand .

d) select* from EMP where Sal between and 200;

4. Condition selection: And,or,in (A1,A2,A3). Note: Where in (a1,a2,a3) means selecting one of the a1,a2,a3 three instead of selecting one of the three ranges.

5. Not keyword: not in (A,B,C); is not null; Wait a minute.

6. Like keyword: There are two wildcard characters, as follows

A)%: Any character appears any number of times.

b) _: Any character appears once.

Note: The query wildcard needs to be escaped as follows: SELECT * from emp where ename like '%\%% ' escape ' \ '; SELECT * from emp where ename like ' [Email protected]_% ' escape ' @ ';

7. Event handler function: Upper (), Lower (), substr (ename,1,4), Length (), Trim (), LTrim (), RTrim ().

8. Time Format function: To_char (sysdate, ' yyyy-mm-dd '); To_date (String, ' yyyy-mm-dd ').

Days of two days Select Floor (sysdate–to_date (' 2014/12/27 ', ' yyyy/mm/dd '));

9. RowNum: line number, the table of the original data sorting, can only be used for < or <= operators, not with the > or >= operator, if you want to take a few rows of data, a where subquery will first be arranged in flashbacks.

10. Regular Expressions: SQL statements support regular expressions, as in the following example:

select* from EMP where regexp_like (column name, ' regular expression ');

(b), group functions:

1. AVG (), Min (), Max (), sum () skip over.

2. Count (): Count function. Returns the number of rows of query results, often with distinct, to remove duplicate rows.

Example: SELECT COUNT (Eno) from EMP whereeno>200; or select COUNT (distinct ENO) from EMP;

3. GROUP BY: grouping functions.

a). The query unit is no longer in the behavior unit, and all operations are in groups.

b). What is a group? The value is the same as a group.

c). Having a filter grouping,

c). The GROUP BY clause must precede the WHERE clause before the ORDER BY clause.

d). Select Eno from EMP Group Byeno; ----ENO the same group, querying each group of ENO

Select Depno,sal (SAL) from Dept Group by Depno; ----GROUP BY department number, query.

Select MAX (avg (SAL)) from Dept Group by Deptno; ----First GROUP by department number to find out the maximum value for each set of averages.

SELECT * FROM dept GROUP by Sal,comm; ----wages and bonuses are grouped in the same group, query *.

Three The Insert,update,delete statement (skipped).

Four Other operations:

1 Index: You can add index to a column to increase the efficiency of its select. The creation method is super simple: CREATE INDEX index_name on tablename (cols); Then, without a tube, Oracle will automatically call index to retrieve it when we index it.  The deletion method is also super simple: Drop index index_name; Note: Index disadvantage is also many,index does not fit: Data unique bad column + DELETE and change the column frequently . So it is not necessary to establish .

2. Views view: Using the same method and table, benefits: SQL Reuse + security

Create View V$_vname as (select clause); Drop View v$_vname;

3. Sequence sequence: Usage: Create, delete, reference. Skip over.

Five DB Design paradigm: (important)

1. There are currently six types of DB paradigms, 1NF,2NF,3NF,BCNF,4NF,5NF (perfect Paradigm). The more optimized in turn.

2. Enterprise-level development, the minimum to meet 3NF.

3. The higher the normal paradigm, the lower the DB redundancy and the more optimized the DB.

4. Detailed:

A) 1NF: Each column of the table is atomic.

Error demonstration--

b) 2NF: The entity attribute is completely dependent on the primary key. That is, you cannot rely on only part of the primary key.

Error demonstration à (skip)

Common error à Union primary key table. Some properties depend on a part of the primary key.

Solution à split the primary key split table, foreign Key Association.

c) 3NF:

Error demonstration--

A common error in the table is that property a relies on b,b for C. Large amounts of data are redundant.

Solution à split table, FOREIGN Key Association.

d) BCNF: A part of the primary key that is allowed to appear is determined by the other part of the primary key or other part. That is, the federated primary key depends on each other.

Error demonstration à warehouse table storehouse (warehouse ID, Administrator ID, item ID, quantity), one warehouse for one administrator, one warehouse for multiple items, then the dependencies are as follows:

The warehouse ID and administrator ID appear interdependent, 3NF compliant but not compliant with BCNF.

Solution--Split.

5. Relational relationships and solutions for tables:

A) One-to: the foreign key can be placed in any table.

b) One-to-many: foreign keys are placed in multiple parties. For example: Class table (a)-student table (many), student table plus a foreign key.

c) Many to 1:1 pairs more in turn, (skip).

D) Many-to-many: split an intermediate table to correlate, the many-to-many programming two one-to-many, this structure is also called: Entity Table-Mapping Table association structure.

e) Note: The two foreign keys in the mapping table need to form a federated primary key.

Note:

1. Aliases can be in Chinese, but double quotation marks are added;

2. Some Oracle clients, such as plsql, have SQL window and Command window Windows to run different statements, such as the Desc User; Instruction can only run in command window;

3. SQL statement Optimization: Select * from EMP; * is a wildcard character, performance is low, try not to appear in the program *, the best can be specific to the column name.

4. SQL is case-insensitive, but the script executes in a uniform conversion to uppercase. Therefore: Uppercase execution is highly efficient but not conducive to reading, lowercase script execution is highly efficient but not conducive to reading, so the General keyword capitalization, in addition to note: The proposed program keyword also capitalized, conducive to increased efficiency .

5. SQL statement optimization: Fewer results and more results, the results of multiple and search results less. Because SQL statements are executed sequentially.

6. SQL statement Optimization: SELECT * from emp where eno=100 or eno=200 oreno=300; This statement and select * from the EMP where Eno in (100,200,300); equivalence, but with in syntax clear + efficient, because the in function is optimized inside.

7.SQL Statement optimization: Retrieving data with the LIKE keyword and wildcard characters is a last resort, and it's slow to operate.

8. Group functions are nested up to two layers, such as Max (SAL), etc., and cannot appear max (avg (SAL)).

9. Oracle internal execution order: where row-level filtering Àgroup by group àhaving Group-level filtering àorder by sort. Therefore: The GROUP BY clause must precede the WHERE clause, before the ORDER BY clause, i.e. Where...group by...order by ...

Transaction transaction mechanism essentials: manipulate only insert,update,delete row-level statements. If the system shuts down gracefully, the transaction is automatically commit, and if the table-level operations such as Create,drop are performed, the transaction is automatically commit, and if the shutdown is not normal, the transaction is automatically rollback.

DB Note (Oracle)