Proficient in SQL structured queries---Learn NOTE 1

1, the database architecture of the three-level model is: External mode, conceptual mode, internal mode.

Internal mode, also known as storage mode, is a description of the physical structure and storage of the data. The internal schema is defined by the data definition language provided by the database system.

The conceptual pattern, also known as the database schema, is a description of the logical structure of all the data in the database. Conceptual patterns are based on a data model and are defined in the schema definition language. The concept pattern can be regarded as the realization of the real-world entity in the concrete database system.

The external mode, also known as user mode or sub-mode, is usually derived from the conceptual schema and is a subset of the conceptual schema. Its main function is to define the data that allows the user to manipulate. You can understand the outside mode as a view of the data that the user sees.

2. A complete database system consists of 4 parts: database, software system, hardware system and database administrator .

650) this.width=650; "title=" 1.png "src=" Http://s3.51cto.com/wyfs02/M02/59/51/wKiom1TPd_qRWcAcAAK3PMH9DCY992.jpg " alt= "Wkiom1tpd_qrwcacaak3pmh9dcy992.jpg"/>

Database Work Flow:

650) this.width=650; "title=" 2.png "src=" Http://s3.51cto.com/wyfs02/M00/59/4E/wKioL1TPeSqha4DfAAKq9yYOc7s931.jpg " alt= "Wkiol1tpesqha4dfaakq9yyoc7s931.jpg"/>

The DBMS does not directly read the data in the database, but accesses the database through the operating system, which is based on the operating system.

3, the development of the database can be divided into three generations:

The first mesh model and the hierarchical model are all formatted models.

The second model of the relationship.

The third generation is characterized by a richer data model and more powerful data management capabilities, such as an object-oriented data model.

relational database

4.1. The relationship model sees the world as being composed of entities and connections. Entities in a relational model typically behave as tables, and each row of a table despises an instance of an entity, and each column of the table describes a feature or attribute of the entity.

4.2. Paradigm:

At present, the database has 6 paradigm, in any relational database, the first paradigm (1NF) is the basic requirement of the relational model, the database that does not satisfy the first paradigm is not a relational database; The first paradigm is simply a column with no duplicates.

The second paradigm (2NF) is built on the basis of the first paradigm, and the second paradigm in short is that each row or instance must be unique.

The third paradigm (3NF) must first satisfy the second paradigm, in short the third paradigm requires that a database table not contain non-primary key information already contained in other tables. For example, there is a departmental information table, where each department has information such as department number dept_id, department name, department profile, and so on. Then, after the department number dept_id is listed in the Employee Information table, it is no longer possible to add the department name, department profile and other related information to the employee information sheet. If there is no departmental information table, it should be built according to the third paradigm (3NF), otherwise there will be a lot of data redundancy.

The composition of the SQL language

5.1. Data definition Language (DDL) is primarily used to create, modify, and delete database objects, and the associated SQL keywords include create, alter, and drop.

5.2. Data Query Language (DQL) is mainly used for retrieving queries of data; the relevant SQL keyword is select.

5.3. Data manipulation Language (DML) is primarily used to add, modify, or delete data stored in database objects. The main SQL keywords associated with them include insert, update, and delete.

5.4, Data Control Language (DCL), you can control access to specific objects in the database users, but also control the user access to the database type, and its related main SQL keyword includes grant (grant user access), deny (Deny user access) and revoke (user access).

6. Transact-SQL

Transact-SQL is an enhanced version of ANSI-C language, provides standard SQL commands, and also provides a number of extensions to SQL commands, providing basic functions like Basid, Pascal, and third-generation languages such as variable description, program flow control language, function function, etc. Generated by a variety of applications, including the following.

General Office Production application program.

Use a graphical user interface (GUI) application that enables users to select the tables and columns that contain the data to be viewed.

Commercial applications that store their data in SQL Server databases, which can be applications from other vendors or internally written applications.

Applications created using database application interfaces (APIs), such as ADO, OLE DB, and ODBC, are developed by developing systems such as visualc++, Visual Basic, or Visual J + +.

Main components of Transact-SQL

Data definition language (DDL)

Data manipulation Language (DML)

Data Control Language (DCL)

system stored procedures (systems Storedprodedure)

Some additional language elements.

7. Oracle Database

7.1. Oracle Database Composition Structure

650) this.width=650; "title=" 3.png "src=" Http://s3.51cto.com/wyfs02/M01/59/4E/wKioL1TPecGRzbgyAAROTXo9zGY144.jpg " alt= "Wkiol1tpecgrzbgyaarotxo9zgy144.jpg"/>

Sql*plus is an interactive platform for running SQL statements. In this environment, the user can execute the SQL statement like a DOS command, and the result of the statement execution is displayed on the screen.

The Pro*c precompiled interface can preprocess the C language of a SQL statement, and turn it into a pure C language, which is handled by a generic C program.

The OCI subroutine invocation interface provides a library of libraries to access the database, and the C program implements access to the database by invoking them. These functions are legitimate syntax components for C programs and therefore do not require precompilation.

Developer2000 is a fast, graphical interface-based development tool provided by Oracle for typical business processes such as developing tables, menus, charts, and reports.

Sql*forms a set of tools based on DOS and character terminal products.

7.2, the Oracle system uses the client/server architecture.

7.3. In Oracle system, the storage space of a database is divided into one or more logical regions, called table spaces, each table space consists of one or more data files, and a data file can only be associated with one table space. Each Oracle database is associated with an Oracle instance. When the Oracle database is started on the server, the system allocates a memory area----the system global Zone (SGA) and initiates the associated Oracle process. An Oracle instance is comprised of the SGA and Oracle processes, and from a process standpoint, the user accesses the Oracle database using user processes and Oracle processes.

Proficient in SQL structured queries---Learn NOTE 1