Database Learning Notes (iii) SQL basics

The SQL language is divided into: data definition language (data-definition language,ddl)--sql DDL defines relationship patterns, deletes relationships, and modifies relational schema commands; Data manipulation language (Data-manipulation Language, DML)--additions and deletions; completeness (integrity)--sql DDL includes commands that define integrity constraints; View definition--sql DDL includes commands to define views; Transaction control (transaction Control)--sql includes commands that define the start and end of a transaction; Embedded SQL and dynamic SQL (Embedded SQL and dynamically SQL)--embedded and dynamic SQL define how SQL statements are embedded in common programming languages (such as C, C + +, etc.) ; Authorization (authorization): SQL DDL includes commands that define access rights to relationships and views.

3.1 Basic Mode definition
The collection of relationships in the database must be assigned to the system by the data definition language (DDL). SQL's DDL is not only able to define a set of relationships, but also to define the information for each relationship, including the pattern of each relationship, the value type of each attribute, the integrity constraint, the set of indexes each relationship maintains, the security and permissions information for each relationship, and the physical storage structure of each relationship on disk.

Create a relationship (that is, create a table):

CREATE TABLE R (A1 d1,a2 D2,..., an dn,< integrity constraint 1>,..., < integrity constraint k>);

The most commonly used integrity constraints are:

Primary KEY (Aj1,aj2,..., AJM): Property Aj1,aj2,... AJM The main code of the constituent relation--the main code attribute must be non-null and unique, no tuple in the relationship takes null value on the main code attribute, and no two tuples take the same value on all the main code attributes.

Foreign key (AK1,AK2,..., akn) references T: The value of any tuple on the attribute Ak1,ak2,..., akn must correspond to the value of a tuple on the main code attribute in the relationship T.

NOT NULL: null value is not allowed on this property

Delete a relationship (that is, delete a table):

drop table R

Modify a relationship (that is, modify the definition of a table):

ALTER TABLE R

3.2 Modification of database data

Delete a tuple (that is, delete the data):

Delete from r where p; if the WHERE clause is omitted, all tuples in R are deleted

Note that the Drop:delete only deletes the table data, and the drop deletes not only the table data, but also the schema of the table.

Insert a tuple (that is, insert data):

Insert INTO R (a1,a2,..., a) VALUES (P1,P2,..., pn)

Boost: INSERT INTO R1 select A1,a2,..., A from R2 where Ak=v1 and Aj>v2

Updating tuples (that is, updating data for individual property values)

Update r set a1=v1,..., AK=VK

Improve:

Database Learning Notes (iii) SQL basics