Oracle Database Self-study notes (ongoing updates ...) ）

The previous projects were developed using the MySQL database, and are now in the new company, beginning to reach the Oracle database, and have not been contacted before, to squeeze time to learn.

I. The concept of a relational database

The relational data theory was proposed by Dr. E.f.codd in 1970;

Codd set up a database product become a relational database necessary a series of relevant standards;

It is the basis of RDBMS (relational database management system);

A relational database needs to contain the following three aspects:

1. A collection of objects or relationships;

2. The set of operations based on the relationship;

3. Completeness of data resulting from data accuracy and consistency;

A relational database is a collection of relationships or a collection of two-dimensional tables;

Each table is composed of rows and columns;

The data in the table can be manipulated by structured Query Language (SQL);

Each row of data in the table can be uniquely identified by the primary key, i.e., primary key (PK);

The table and table can be correlated by a foreign key i.e. foreign key (FK);

Objects in a relational database:

1. Table: The basic unit of storage, consisting of rows and columns;

2. View: A logical representation of the data from one or more tables

3. Sequence: Used to generate the primary key;

4. Index: Improve the speed of queries;

5. Synonyms: An alias for an object;

6. Programming Unit: Includes procedures, functions, or packages, and declarations in PL/SQL.

Two. Introduction to Oracle

Oracle is one of the largest database vendor-supplied relational database products

Oracle's RDBMS is also known as Oracle server

1. A place where data is stored;

2. Quick access to data;

3. Database security;

4. Distributed database;

5. Configuration of client and server;

sql--Structured Query language, through which it can access and manipulate data of relational database species in a standardized way.

sql*plus--is the simplest database development tool. It allows the user to interactively enter SQL statements and PL-SQL statement blocks from the cue line. These statements are sent directly to the database, and the results are returned to the screen. Typically, Sql*plus is integrated with Oracle servers and is part of a standard Oracle installation. Sql/plus is a character-mode environment, and the window version includes the need for GUI operations, primarily for connecting databases and setting Sql*plus options.

pl/sql--is a more complex programming language for accessing Oracle databases from a variety of environments. It is integrated with the database server and is part of the standard database installation.

SQL command:

1. Value statement:

SELECT

2. Data manipulation Statements Manipulation language (DML):

INSERT uppdate DELETE

3. Data definition Statement language (DDL):

CREATE ALTER DROP RENAME TRUNCATE

4. Transaction control Statements Transaction controls:

Commmit ROLLBACK Savepiont

5. Data Control Language (DCL):

GRANT REVOKE

Three. SELECT statement

Syntax: SELECT [DISTINCT] {*,column [alias],......} from table;

Description

1.SELECT can be followed by the above name, column aliases, wildcard characters;

Follow the 2.FROM clause followed by the name of the table you want to find;

3.DISTINCT find non-repeating records

4.SELECT statements can be written one or more lines, not case-sensitive;

5. Wildcard * Displays all the columns in a table

Select *  from Department;

6. When querying a specified column, the column and column in the SELECT statement are separated by commas

Select  from Department;

7. The value or date of the query can be subtraction operation, if an item is null, then the result of the operation is also null

Select Salary* from department;

You can use parentheses to change precedence, such as:

Select Salary*+  from department; and Select  *(Salary+) from department ;

8. Use the NVL function to convert null to a value, such as NVL (column name, ' string ') or NVL (column name, value)

9. Column aliases are optional, and a column alias contains spaces, special characters, or case sensitivity, then enclose it in double quotation marks

Select  from Department;

Four. Sorting and conditional queries

ORDER BY clause:

1.order by clause at the end of the entire SQL statement;

The 2.ORDER by can be followed by a column name, column alias, an expression, and an ordinal that lists the current select keyword.

Select  from Order  by salary the same as: Select  from Order  by 2;

3.ORDER by can be followed by multiple columns, sorted by first column, if the first column is the same, then the second column, and so on ...

4.ASC means ascending, is the default, can be omitted, desc is descending order;

5.NULL is always the biggest.

WHERE clause:

1. Immediately after the FROM clause, filter the returned data

2. A condition consists of one or more expressions, comparators, or literals

3. All strings and dates are wrapped in single quotation marks, and numbers do not need

4. Date in Oracle database has a specific form, ' dd-mon-yy ' (depending on the date display format), otherwise as a string

5.WHERE clause operator: = equals, > greater than, >= greater than equals, < less than, <= less than equals,! = Not equal, <> not equal, between ... And ... Include start and end values, in (list) qualified content, like fuzzy query, is null empty (cannot use =null), not between does not exist ... Between, not in no longer in the collection, not like does not contain, is isn't null not empty, not, and, or;

　　　　Today write tired, first write here, the next update, add the function of Oracle ... Tomorrow starts 51 room, wishes everybody to have fun ...

Oracle Database Self-study notes (ongoing updates ...) ）