DB notes (Oracle), db notes oracle

DB notes (Oracle), db notes oracle

DB notes (Oracle)

1. Oracle Installation, Service Startup, database creation, and User Creation (omitted ).

Ii. Select statement:

(1) Single Row functions:

1. Expression and null calculation, the result is null, that is, not displayed (that is, the data is missing !). Therefore, the NVL function (column name, replace value) is generally used, such as NVL (sal, 0 ).

2. String concatenation: | For example: select ID | '---' | uname from emp;

3. sort and filter (order by and where ):

A) select * from emp order by sal asc, comm desc; this expression is in ascending order of salary, and in reverse order of bonus with the same salary.

B) select * from emp order by (sal + NVL (comm, 0) desc; this expression is: sort by the sum of salary and bonus. Note:If comm is directly used without NVL, some values will be lost..

C) select * from emp where uname> 'CBA'; filter strings,I have not understood this details yet..

D) select * from emp where sal between 100 and 200;

4. Select and, or, in (a1, a2, a3) as the condition ).Note: in (a1, a2, a3) indicates selecting one of a1, a2, and a3, instead of selecting one of the three ranges.

5. Not Keyword: not in (a, B, c); is not null; and so on.

6. Like Keyword: There are two wildcard characters, as shown below

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

B) _: any character appears once.

Note: To query wildcards, escape the following: select * from emp where ename like '% \ % 'escape '\'; select * from emp where ename like '_ H @ _ %' escape '@';

7. event processing functions: upper (), Lower (), substr (ename,), length (), trim (), ltrim (), rtrim ().

8. Time Format function: to_char (sysdate, 'yyyy-mm-dd'); to_date (string, 'yyyy-mm-dd ').

Select floor (sysdate-to_date ('2014/1/27', 'yyyy/mm/dd '));

9. rownum: the row number. The original data sorting of the table can only be used for the <or <= Operator, not the> or> = Operator. If you want to obtain the last few rows of data, A where subquery is required to be sorted in reverse order.

10. Regular Expression: SQL statements support regular expressions, for example:

Select * from emp where regexp_like (column name, 'regular expression ');

(2) group functions:

1. avg (), min (), max (), sum () skipped.

2. count (): count function. Returns the number of rows in the query result. This parameter is often used with distinct to remove duplicate rows.

For example, select count (eno) from emp whereeno> 200; or select count (distinct eno) from emp;

3. group by: group function.

A) The unit of query is no longer the unit of action, and all operations are in groups.

B) What is a group? If the value is the same, it is a group.

C). having is a filtering group,

C) the group by clause must be after the where clause and before the order by clause.

D). select eno from emp group byeno; ---- the same eno is divided into one group, and the eno of each group is queried.

Select depno, sal (sal) from dept group by depno; ---- group by Department number and query.

Select max (avg (sal) from dept group by deptno; ---- group by department ID to find the maximum value of the average value of each group.

Select * from dept group by sal, comm; ---- the same salary and bonus are divided into one group, query *.

 

3. Insert, update, and delete statements (skipped ).

4. Other operations:

1. index: You can add an index to a column to improve its select efficiency. The creation method is super simple: create index index_name on tablename (cols); then you don't have to worry about it. oracle will automatically call index to retrieve the index during indexing. The deletion method is also very simple: drop index index_name; Note: index has many disadvantages,Index is not suitable: Columns with poor data uniqueness + columns with frequent addition, deletion, and modification. SoNot required..

2. view: the usage method is the same as that of the table. Benefits: SQL reuse + Security

Create view v $ _ vname as (select clause); drop view V $ _ vname;

3. sequence: Usage: Create, delete, and reference. Skipped.

V. DB design paradigm: (important)

1. There are currently six DB paradigms: 1NF, 2NF, 3NF, BCNF, 4NF, and 5NF (perfect paradigm ). The more optimized.

2. enterprise-level development, with a minimum of 3NF.

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

4. Details:

A) 1NF: each column in the table is atomic.

Error example -->

 

B) 2NF: the object attribute fully depends on the primary keyword. That is, you cannot only rely on a portion of the primary key.

Error demonstration (omitted)

Common error: a table with a primary key. Some attributes depend on a portion of the primary key.

Solution à split the primary key split table and associate the foreign key.

C) 3NF:

Error example -->

A. attribute A depends on B, and B depends on C. A large amount of data is redundant.

Solution à split the table and associate the foreign key.

D) BCNF: allows the presence of a part of a primary key to be determined by another part of the primary key or other parts. That is, the Federated primary keys are mutually dependent.

Error example à warehouse table storehouse (warehouse ID, administrator ID, item ID, quantity), one warehouse, one administrator, and one warehouse with multiple items, the dependency is as follows:

The repository ID and administrator ID are mutually dependent, Which is 3NF but not BCNF.

Solution --> split.

5. Table associations and solutions:

A) one-to-one: Foreign keys can be placed in any table.

B) one-to-many: Foreign keys are placed in multiple parties. For example, in the class table (1)-student table (multiple tables), a foreign key is added to the student table.

C) Multiple-to-one: one-to-multiple reverse (skipped ).

D) many-to-many: split an intermediate table and associate multiple-to-many programming. This structure is also called the object table- ing table association structure.

E) Note: The two Foreign keys in the ing table must form a joint primary key.

 

 

 

Note:

1. aliases can be in Chinese, but must be enclosed in double quotation marks;

2. Some oracle clients, such as PLsql, have SQL windows and command windows to run different statements, such as desc User. commands can only run in command windows;

3. SQL statement optimization: Select * from emp; * is a wildcard, with low performance. do not appear in the Program *. It is best to specify the column name.

4. SQL statements are case-insensitive, but will be converted to uppercase during script execution. Therefore, the upper-case execution efficiency is high, but it is not conducive to reading. The lower-case script execution efficiency is high, but not conducive to reading. Therefore, the general keywords should be capitalized. Note:We recommend that you use uppercase keywords in the program to increase efficiency..

5. Optimization of SQL statements: fewer and fewer retrieval results, and fewer retrieval results. Because SQL statements are executed sequentially.

6. SQL statement optimization: select * from emp where eno = 100 or eno = 200 oreno = 300; this statement is equivalent to select * from emp where eno in (100,200,300, however, the syntax of in is clear and efficient, because the in function is optimized internally.

7. SQL statement optimization: using the like keyword and wildcard to retrieve data is a last resort, and its operation is extremely slow.

8. A group function can be nested at most two layers, such as max (avg (sal). max (min (avg (sal) is not allowed ))).

9. Oracle internal execution sequence: where row-level filtering. Therefore, the group by clause must be placed after the where clause and before the order by clause, that is, where... Group... Order...

10. Key points of the Transaction mechanism: only insert, update, and delete row-level statements are operated. If the system is shut down normally, the transaction will automatically commit; if you execute create, drop, and other table-level operations, the transaction will automatically commit; if it is shut down abnormally, such as power failure, the transaction will automatically roll back.