Basic Database knowledge Review

1. Data View and data model data abstraction have three layers: physical layer, logical layer, and view layer. There are two types of data models used in the logic layer: one is the conceptual data model, which is mainly used for database design. It can be understood by general users and is similar to the way people think. Such a model has an entity.

1. Data View and data model data abstraction have three layers: physical layer, logical layer, and view layer. There are two types of data models used in the logic layer: one is the conceptual data model, which is mainly used for database design. It can be understood by general users and is similar to the way people think. Such a model has an entity-link model (ERM). The other is

1. Data View and Data Model

Data abstraction has three layers: physical layer, logical layer, and view layer. There are two types of data models used in the logic layer: one is the conceptual data model, which is mainly used for database design. It can be understood by general users and is similar to the way people think. Such a model has an entity-link model (ERM). The other is a logical data model, which models data based on computer systems, making the data more suitable to be represented by computers. The model is mainly used for DBMS implementation, such as relational model, object-oriented model, hierarchical model, and mesh model. A designer's approach to building a database model is usually to first use a E-R model to model data at a high level, and then convert it into a relational model. The data model used at the physical layer is called the physical data model.

A Data Model consists of three parts: data structure, data operation, and integrity constraint.

2. database language

Based on different functions, the language provided by the database system can be divided into three types: 1) Data Definition Language (DDL), used to define the database mode; 2) Data Control Language (DML ), it is used to query and update databases. 3) Data Control Language (DCL) is used to manage data permissions.

3. Database Mode

Based on different abstract layers of data, the database has three modes: Physical Mode (internal mode) describes all the storage structures and access methods in the database at the physical layer, while logical mode (conceptual Mode) the logical layer describes the logical structure and features of all data in the database. The view layer can also be divided into several modes, called the sub-mode (external mode). It describes the logical structure and features of local data that can be viewed and used by database users. Generally, a database has only one physical mode and one logical mode, but there are several submodes.

4. E-R Diagram

A E-R diagram consists of the following elements: 1) a rectangle, representing a real shape; 2) an elliptic representing an attribute; 3) a rhombus representing a link; 4) a line segment that connects an attribute to an entity, you can also connect the real-size and contact information.

5. Link Model

A relational model consists of three parts: relational data structure, relational operation set, and Relational integrity constraint. In the relational model, the real-world entities and the relationships between entities are expressed by relations.

Commonly used relational operations in a relational model include selection, projection, join, division, union, intersection, and other query operations and addition, deletion, and modification. Relational operations are characterized by the set operation method, that is, the objects and results of operations are collections. Relational operations can be defined in two ways: algebra-based definitions are called relational algebra, and logic-based definitions are called relational calculus. Due to the use of different variables, relational calculus is divided into tuples relational calculus and domain relational calculus.

The relational model allows defining three types of integrity constraints: entity integrity, referential integrity, and user-defined integrity. Entity integrity and referential integrity are integrity constraints that must be met by the relational model. The entity integrity rule is: the primary code of the link cannot be null. The integrity rules are as follows: the external code must be the valid value of the master code in another table or be "null ".

6. Join Operation

The Join Operation selects tuples that meet certain conditions between attributes from the multiplication result of the two relationships to form a new relationship. There are two types of Join Operations: equijoin and natural join. The natural connection requires that the components for comparison in the two relationships must be the same attribute group, and duplicate attribute columns should be removed from the results.

7. SQL

The relational database system supports three-level schema. The basic objects in the schema (database), external schema, and internal schema include tables, views, and indexes. Therefore, the data definition function of SQL includes schema definition, table definition, view definition, and index definition. SQL generally does not support modifying schema definitions, modifying view definitions, and modifying index definitions. If you want to modify these objects, you can only delete them and then recreate them.

8. Basic table operations

1) create a table

Create table basic table name

(Column name type,

......

Integrity constraints,

......

)

Integrity constraints mainly include three clauses: primary key clause (primary key), foreign key clause (foreignkey), and check clause (check ).

2) modify a table

Alter table <基本表名> Add <列名> <类型>

Alter table <基本表名> Drop <列名> <类型> [Cascade | restrict] (cascade indicates that all views and constraints referenced to this column must be deleted together. restrict indicates that when no view or constraint references this attribute, this column can be deleted in this table. Otherwise, the column cannot be deleted .)

Alter table <基本表名> Modify <列名> <类型>

3) cancel a table

Drop table <基本表名> [Cascade | restrict]

9. SQL query

The where clause can use the following operators:

L Arithmetic Operators

L logical operators

L string matching operators, including like and not like

L set Member qualification operators, including in, not in

L predicates, including exists, all, some, unique

L Aggregate functions, including avg, min, max, sum, and count

L it can also be another select statement

Complete select statement Syntax:

Column name or column expression sequence of the select target table

From basic table name and (or) view Sequence

[Where row conditional expression]

[Sequence of group by column names]

[Having group conditional expression]

[Order by column name [asc | desc]

The entire statement execution process is as follows:

1) read data from base tables and views in the from clause and perform the Cartesian Product operation;

2) Select the tuples that meet the conditional expressions given in the where clause;

3) group by the value of the specified column in the group clause, and extract the groups that meet the group condition expressions in the having clause;

4) Calculate the output value based on the column name or column expression given in the select clause;

5) The order clause sorts the output target tables in ascending order or in descending order according to the attached instructions.

10. View

Create View:

Create view <视图名> [ <列名> <列名> …]

As <子查询>

[With check option]

With checkoption indicates that adding, deleting, and modifying a view must ensure that the row to be operated meets the predicate condition in the view definition (that is, the conditional expression in the subquery ).

A view is defined on top of a basic table. All operations on the view must be converted to operations on the basic table. Benefits of a view:

L view can simplify user operations

L view: users can view the same data from multiple perspectives

L view provides a certain degree of logical independence for the restructured Database

L view provides security protection for confidential data

11. Transactions

Transactions are a series of database operations, the basic unit of database applications, and a task that reflects the real world needs to be submitted in a complete unit. A transaction is a sequence of user-defined database operations.

Four features of transactions: atomicity, consistency, isolation, and persistence.

Transaction processing includes database recovery and concurrency control. Database recovery has two purposes: to ensure the atomicity of transactions and to restore the database to a correct state.

The principle of data recovery is summarized as redundancy. The most common technology for establishing redundant data is data dumping and login log files. Data dumping is a process in which DBA regularly copies the entire database to a disk or another disk.

12. triggers

A trigger is used to define a condition and the action to be executed when the condition is true. Generally, the trigger conditions are defined in the form of assertions. Actions are defined in the form of processes.

13. Index

See the data structure and algorithm principles behind MySQL indexes.