Introduction to SQL

1.1SQL language

The SQL language is divided into two categories:

1. Data definition Language: DDL (data definition Language): Most commonly used create, alter,drop, etc.

2. Data Manipulation Language: DML (Manipulation langugage): select,insert,delete,update

1.2 Relational database management system

Several professional terms:

• All the information is expressed in relation, we also regard the relationship as a two-dimensional table

• A table or relationship is a database object

• Each relationship consists of a relationship name and a collection of columns or attributes

• The data in the table appears as a collection of rows or tuples

• The total number of attributes in a relationship is called the degree of relationship

• The total number of rows is called the cardinality of the relationship

• Domain: A collection of values for a specific column

• main code (PK) : Used to determine the uniqueness of a tuple or row, the attribute value that makes up the main code cannot be null (NULL), an additional restriction, called Integrity Constraints .

• External Code (FK) : Because columns with the same base domain can be used in relational tables in a database, foreign keys (FK) maintain rows of two relationships or row consistency across the same relationship. Can be defined as follows:

Assuming that there are relationships between R1 and R2 in the same database, the FK attribute group for R1 can be called the outer code of R1 If the following two conditions are met.

1. The attributes in FK have the same base domain as the PK attribute group defined in the relationship R2 as R2.

2. The value of FK is either null, or the PK value of a tuple in the relationship R2, and both must reside.

The Outer code guarantees: the tuple of relation R1 if reference relation R2 tuple, the tuple of relation R2 must already exist, this external code constraint is called Referential integrity constraints .

• tables that contain external codes are called child table , the table that contains the reference property is called Parent Table

1.5 Relational operators

Relational Operators : Operators that manipulate tables are closed (that is, new relationships are generated based on relational operations).

Common relational operators: selection, projection and equivalent connections .

1.5.1 selection operator

Definition: Suppose R is a relationship, A is an attribute of R, a is an element of domain (a), a selection on attribute A, a subset of T-tuples that produce R, and T (A) =a.

Note: The Select operator is a single-mesh operator that can operate on only one relationship at a time.

1.5.2 projection operator

The projection operator is also a single-mesh operator

Select operator in select Relationship subset of Rows .

projection operator Selection a subset of the columns.

Definition: The relationship to the attribute group x projection operation, resulting in a new relationship, first exclude the relationship R is not specified in the X column, and then exclude any repeating tuple to get.

15.3 equivalent Join operator

The equivalent join operator is a two-mesh operator that combines two relationships.

Definition: The relationship r contains the attribute group R, the relationship s contains the attribute group S, and R and s have some common properties (not the same as the name, to determine the common attributes according to the same domain). A common set of properties for R and S creates a new relationship between the X,r and S connections, whose attributes are the elements of the RUS (R and S), and, in addition, each tuple T in the new relationship, to satisfy:

Split the tuple t into two tuples, you can find the corresponding tuple in R and S respectively.

1.6 Attribute Domains

In Rdms, any given domain is implemented with a data type.

Introduction to SQL