Interview Summary (vi)--oracle Database Basics Summary

Oracle Database Basic Knowledge Summary

1. One instance corresponds to a database

2. Mode

3. Table Space (Segment > Area > Block) Improve management efficiency

From the user's point of view, there are several patterns in a library (the pattern uniquely corresponds to the user of the same name), and a schema has database objects such as tables, views, and stored procedures.

From a storage perspective, a library has several table spaces that are responsible for storing various objects in the database.

Note: Tables in one mode can be stored in different table spaces.

Tables stored in the same table space can be in different modes.

Use schema concepts for database access, and use tablespace concepts for data storage management.

Table Space

Tablespace: A logical structure consisting of multiple data files,

Physically, a table exists in a data file.

Logically, a table exists in a table space

/**

(The logical structure of an Oracle database consists of tablespaces, segments, extents, and blocks, which are physically composed of data files, and one table space contains one or more data files.) )

The table space is used to logically organize the data of the database, which can control the disk space occupied by the database, while the DBA can deploy different data types to different locations, which helps to improve I/O performance while facilitating operations such as backup and recovery.

**/

Creating a tablespace is typically performed by a privileged user or DBA role, requiring permission

4. Authorize the use of grant, reclaim authority using revoke (Unlock account-use profile to manage users)

Grant Connect to Xiaoming (with admin option)

Revoke select on EMP from Xiaoming;
Alter user tea account unlock;

Overview: Profile is a command set of password limits, resource constraints, and Oracle automatically creates a profile named default when a database is established. When the build user does not specify profile options, Oracle assigns default to the user.

5. Implementation steps:

A. Creating a user

Create user user1 identified by u123 default tablespace AA; (the password must begin with a letter)

B. Assigning connect permissions to Users

Grant connect to User1;

C. Assigning table space to use permissions

Grant unlimited tablespace to User1;

===============================================================

Modify Password:

If you change your password, you can use it directly.
Password User name
If you change the password for someone else, you need DBA authority, or you have system permissions on alter user
Sql> alter user username identified by new password

SYS user is superuser, the default password for this user is Change_on_install

The system user is a management operator and the default password is Manager

The biggest difference between the SYS and system two users is that there is no permission to create database

================================================

* How to cancel duplicate rows distinct

* Use the alias of the column: ...........

* How to handle null values, using the NVL function to handle

* How to connect strings (| | ）

* How to use the LIKE operator
%: 0 To more characters _: represents any single character

* Data grouping--max,min, AVG, SUM, Count

* If you want to delete the user, has created a table, then you need to delete the time with a parameter cascade;

6.insert update delete/truncate drop

7. Functions: Date, Math, string function, System function decode

(1) Date function

By default, the date format is Dd-mon month-yy, December-July-78
Sysdate: This function returns the system time
Add_months (D,n)
Last_day (d): Returns the last day of the month of the specified date
(2) Mathematical functions

The data type of the input and return values of the mathematical function is numeric. Mathematical functions include Cos,cosh,exp,ln, Log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round,

Commonly used are:

Round (n,[m]) This function is used to perform rounding, and if M is omitted, rounded to an integer, and if M is a positive number, rounded to the decimal point's M bit. If M is a negative number, it is rounded to the M-bit before the decimal point.
Trunc (N,[m]) This function is used to intercept numbers. If M is omitted, the decimal part is truncated, and if M is a positive number, it is intercepted to the M bit of the decimal point, and if M is a negative number, it is intercepted to the first m bit of the decimal point.
MoD (m,n)
Floor (n) returns the largest integer less than or equal to n
Ceil (n) returns the smallest integer greater than or equal to n

(3) System function

Sys_context
1) Terminal: identifier of the terminal for the current session client
2) Lanuage: Language
3) db_name: Current database name
4) Nls_date_format: Date format for current session client
5) Session_user: Database user name for current session client
6) Current_schema: The default scheme name for the current session client
7) Host: Returns the name of the host where the database resides
With this function, you can query for some important information, such as which database you are using.
Select Sys_context (' USERENV ', ' db_name ') from dual;
Note: USERENV is fixed and cannot be changed, db_name can be replaced with other, such as Select Sys_context (' USERENV ', ' Lanuage ') from dual, or select Sys_context (' USERENV ', ' Current_schema ') from dual;

 

8. Group function min Max Count ...

9. Query group Check Connection lookup subquery

If you include group by at the same time in the SELECT statement, having, the order by then they are ordered by the group by

Sub-query:
A subquery is a SELECT statement embedded in another SQL statement, also called a nested query.
There are three ways in which Oracle is paginated:

(1) According to ROWID (execution time 0.03 seconds)
(2) divided by analytic function (execution time 1.01 seconds)

(3) According to RowNum (execution time 0.1 seconds)

SELECT * FROM (select S.*,rownum rn from

(SELECT * from Stuinfo where stu_id= ' 1001 ') s

where rownum<=10)

Where rn>=6

10. Result set

Difference set, minus displays the data in the first collection, but not in the second collection.

Intersection, Intersect uses this operator to get the intersection of two result sets.

and set union when this operator is used, the result set is automatically stripped of the repeated rows of union all it does not cancel duplicate rows and does not sort

PL variable (several data types, cursor system cursors) conditional loops

data types supported by Oracle:

(1) character class

Char: fixed length, maximum 2000 characters.

varchar2: variable length, maximum 4,000 characters.

CLOB (character large object) character large object Max 4G
Char query is very fast, wasting space, query more data.
varchar Save space

(2) Digital type

Number Range-10 of 38 to 10 of the 38, can represent integers, can also represent decimal

Number (5,2) number (5)

(3) Date type

Date contains date and time

Timestamp can be accurate to milliseconds

(4) Picture

Blob binary data can be stored in picture/sound 4G

definition cursor : CURSOR cursor name is SELECT query statement

Open cursor: Open cursor name

Extract data: Fetch cursor name into variable name

Closing cursor: Close cursor name

Cursors: The mechanism by which a result set is retrieved from a table, each of which points to a record for interaction. Operations in a relational database are performed on a complete set of rows.

What is the effect of cursors.

① Specifies the location of a specific row in the result set.

② retrieves a row or successive rows based on the current result set location.

③ modifies the data in the row at the current position of the result set.

④ defines different sensitivity levels for data changes made by other users.

⑤ can access the database programmatically.

Low efficiency (more actual data) try to avoid further table joins in the cursor loop

12. Stored procedure function package trigger sequence transaction (atomicity, consistency, isolation, persistence)

Stored procedures: A set of SQL statements to complete a specific function stored in the database, after the first compilation of the call again does not need to compile, the user by specifying the name of the stored procedure and give the parameters to execute it.

Triggers: Triggers are special types of stored procedures that are executed primarily by triggering events, and stored procedures can be called directly by stored procedure names

Trigger Action: Hardening constraint Tracking Change cascade run a stored procedure invocation trigger can call one or more stored procedures

Functions: Functions are used to return specific data. You need to find a variable to receive the return value of the function.

Package: So by using the package you can classify the management process and function package into two parts, package specification and package body

sequence: a Database object provided by Oacle to produce a series of unique numbers

Primarily used to provide a primary key value automatically provide unique values to improve access efficiency

transactions: The task of a transaction is to transform a database from one state to another State commit rollback

A procedure (Procedure) is a collection of P l/s Q L statements that are stored in a database. It can contain input and output parameters, an executable collection of code, and can be invoked by other applications.

function is also a collection of P l/s Q L statements stored in the database. The main difference with a procedure is that the function must have a return value.

A package (Package) is a collection of processes and functions bundled together according to the characteristics of the same function. All internal processes and functions are recorded as a single storage package in the data dictionary, which facilitates the organization and management of the program.

The Database trigger (Trigger) is also the stored executable p l/s Q L statement collection. Triggers are executed primarily by triggering events, and database triggers run before or after the insert, change, or DELETE statement.

13. Indexed view

An index is a database structure for a server to quickly find a row in a table

(1) Fast access to data.

(2) Can improve the performance of the database, but also can guarantee the uniqueness of the column value.

(3) To realize referential integrity between table and table

(4) In the use of the ORDER BY, GROUPBY clause for data retrieval, the use of the index can reduce the sorting and grouping time.

View:

1. To centralize data for users, simplify user's data query and processing.

2. Shielding the complexity of the database, users do not have to understand the complexity of the database.

3. Simplify the administration of user rights and grant users permission to use the view only.

4. To facilitate data sharing, multiple users do not have to define the required data.

5. Data can be organized to be associated with other applications.

14. Optimize

Oracle SQL Performance Optimization:

The order of joins in the WHERE clause. The connection between the tables must be written before the other where conditions, and the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

Avoid the use of ' * ' in the SELECT clause:

Use commit as much as possible

Replace Delete with truncate

Reduce queries against tables

Using the alias of a table

Simple integration, no associated database access

 

three paradigms

(1) The atomic nature of the field (the column cannot be divided/field is not ambiguous);

(2) A non-primary attribute must rely on the main attribute (a table describing one thing);

(3) A non-primary attribute must rely directly on the primary property (no delivery dependencies exist).

The first paradigm is a column without duplicates.

The second paradigm is that non-primary attributes are dependent on the primary key

The third paradigm is that attributes are not dependent on other non-primary attributes.

Digital Dictionaries

The

  Data dictionary records system information for a database, which is a collection of read-only tables and views, and the owner of the data dictionary is the SYS user. &NBSP
   users can only perform query operations (SELECT statements) on the data dictionary, and their maintenance and modification is done automatically by the system.
  Data Dictionary composition: The data dictionary includes the data Dictionary base table and the data dictionary view, in which the base table stores the basic information of the database, the ordinary user cannot directly access the base table of the data dictionary. The Data dictionary view is a view based on the base table of the data dictionary, and ordinary users can obtain system information by querying the data dictionary view. The data dictionary view mainly includes three types of user_xxx,all_xxx,dba_xxx. &NBSP