Oracle Database Fundamentals 1

I. Preparation TOOLS

1.Oracle database: Full name Oracle Datebase is a relational database management system of Oracle Corporation;

2.pl/sql:pl/sql Developer is an integrated development environment specifically for the development of Oracle database applications, and PL/SQL is also a programming language called the procedural SQL language.

Two. Create a user

Open the PLSQ software, right-click in the Users folder to create the user, set the user name and password, create two roles in the Role Permissions option, and then turn off the software to reopen the login after applying.

View services: Press Win+r to open the pop-up window, Enter Services.msc to open the Computer Services menu to see whether the Oracleservicexe (XE: Refers to the DB instance name) and Oraclexetnslistener (listener) services are started to ensure proper operation of the database.

Three. Table Space

1.Oracle database management of data is based on the concept of table space, and the various data and storage data optimization is actually achieved by optimizing the table space.

2. Table Space Classification:

• Permanent table space: the space for objects that need to be persisted, such as data, views, stored procedures, or functions, for storing tables.
• Temporary tablespace: The space used to hold temporary data such as query results.
• Undo tablespace: A table space for objects such as rollback data, deleted data, and so on.

3. Classification of users:

• SYS: Super administrator, maximum permissions.
• System: Secondary Super Administrator.
• Scott: Regular users.

Four. Oracle base data type

1. Character type: contains string

char (maximum length 2000): The length of the data type in parentheses, is fixed length, immutable. The disadvantage is that extra space is not wasted.

NCHAR (maximum length 1000): Same as char (), Unicode supported.

VARCHAR2 (maximum length 4000): variable length, generally with this type.

NVARCHAR2 (maximum length 2000): With VARCHAR2 (); Unicode supported.

2. Number types

Number (including positive and negative integers and decimals, significant digits, number of decimal places);

Number (5): The valid digits are written in parentheses, where the maximum range is 99999, and the minimum range is-99999.

Number (5,2): The comma is followed by the significant digits of the decimal point, where the maximum range is 999.99, and the minimum range is-999.99.

Integer: Refers to a smaller integer.

3. Date Type: Time

Date: Value range: January 1, 4712 A.D.---A.D. December 31, 9999, can be directly accurate to the second.

Timestamp: Timestamp: More accurate data can be accurate to milliseconds.

4. Other types

CLOB: Represents large text with a maximum capacity of 4g, stored as a string.

Blob: Stores files, slices, videos, sounds, etc. in binary form, with a maximum capacity of 4g.

There are three types of 5.boolean: True;false;null/unknown (empty/unknown);

Five. Oracle operators and expressions

The expression is similar to Java, can be a constant, can be a variable, or it can be a field in a table.

1. Assignment operator (: =)

2. Arithmetic operator (+-*/Note that there is no remainder operator in this place, use mod function for remainder)

3. Comparison operators (> >= < <= =[Note = = =] <>/!=)

4. Logical operators (and or not)

5. String Connector (| |)

Six. Create a table

Create a new table in the tables file, enter the table name in the general options, such as create a new user table can be written as: T_users, in the row option to add the table of contents of the classification, line-sensitive, such as the user table has a user name and user password, can be named: uname and PSM, and choose the data type of the data, click on the application, such a basic form is called to create a good;

Edit table: Right-click on the table file to edit the table by clicking Edit Options.

Edit data: Right mouse button Select Table file Click Edit Table option to edit the completed form, click on the Checkmark icon when finished, and click the Submit icon button in the toolbar to save the modified data.

Query data: The first is directly right mouse button table file Click Query data, the second is a new SQL window, enter code query data.

Select and from are common keywords in SQL, which are represented in the SQL language--(two minus signs).

Seven. Primary key and foreign key of table

Primary keys and foreign keys are defined primarily to maintain the integrity of the relational database.

1. A primary key is a unique identifier that determines a record, for example, a record that includes an identity plus, name, and age. The ID number is the only person who can identify you, others may have duplicates, so the ID number is the primary key.

2. A foreign key is used to associate with another table. is a field that determines the record of another table, which is used to maintain data consistency. For example, a field in a table is the primary key of table B, so he can be a foreign key to table A.

Eight. Oracle database constraints

Constraints actually define a rule that defines what value is to be entered, or what the value of the range is, and its purpose is to ensure the integrity and accuracy of the data.

1. Non-null constraints

When a message is logged, if the user name and password are not recorded, then this record is meaningless, and when the table is created, it can be empty by default. You can remove an empty check in a column in an edit table.
code example: ALTER table t_users MODIFY uname NOT NULL means that the uname field in the T_users table is set to non-empty.

2. PRIMARY KEY constraints

To ensure uniqueness of the data in a table, there can be only one primary key in a table, but the primary key may consist of multiple fields (federated primary key, composite primary key), but this is not recommended.

You can set the key in the key option when you are building a table: primary key; For example, setting the name as the primary key can be written like this: Pk_t_users_uname (Oracle is case insensitive and automatically converted to uppercase after saving)

code example: ALTER table name ADD CONSTRAINT PRIMARY Key name PRIMARY key (uname)

3. FOREIGN KEY constraints

The only one that involves a field constraint in two tables, when inserting data, is the field of the associated relationship from the table, either the association of a primary table primary key or a null value.

code example: ALTER table name ADD CONSTRAINT foreign key name FOREIGN key (foreign key field name) REFERENCES primary table name

4. Unique constraints

Function: Guarantees the uniqueness of the field values, the difference from the primary key: The unique constraint can be NULL, the unique constraint can have more than one, the use of a statement to create a unique constraint can write more than one statement, similar to insert into;

code example: ALTER table name ADD CONSTRAINT uk_appuser UNIQUE (specified column name)

5. Check constraints

Effect: Make the values in the table meaningful; For example, define an age range;

code example: ALTER TABLE appuser ADD CONSTRAINT c_age CHECK (age > 0 and Age < 150)

Oracle Database Fundamentals 1