SQL constraints, transactions, triggers, storage engines, multi-table queries

SQL constraints

There are several main SQL constraints:

Not NULL: 　 The content used to control the field must not be empty (null).

Unique: 　 control field content cannot be duplicated, and a table allows multiple Unique constraints.

PRIMARY KEY: also used for control field content cannot be duplicated, but it only allows one in a table.

FOREIGN key: the FOREIGN key constraint is used to prevent the action of breaking connections between tables, and the FOREIGN key constraint also prevents illegal data from being inserted into the foreign key column because it must be one of the values in the table it points to.

Check: 　 The range of values used to control the field.

Default: 　 used to set defaults for new records.

650) this.width=650; "src=" Http://img.baidu.com/hi/jx2/j_0012.gif "alt=" j_0012.gif "/> Usage Use cases:

1, notnull: the content used to control the field must not be empty (null). Usage:

CREATE TABLE MyTable (ID varchar (+) NOT NULL, NAME varchar (+) NOT NULL, ...);

2.Unique: control field content cannot be duplicated, one table allows multiple Unique constraints.

CREATE TABLE MyTABLE (ID varchar (+) NOT null UNIQUE, NAME varchar (+) NOT NULL, ...);

Add Unique syntax in SQL Server, My SQL support: You can add several fields at the same time as constraints such as: Unique (id,name)

CREATE TABLE MyTable (ID varchar (+) NOT NULL, NAME varchar (+) NOT NULL, UNIQUE (Id,name), ...);

3, Primary Key: also used for control field content cannot be duplicated, but it only allows one in a table.
Add primary key syntax in SQL Server, Orcale, MS Access support:

CREATE TABLE MyTable (ID varchar (+) NOT NULL, NAME varchar (+) NOT NULL, ... PRIMARY KEY (ID), ...)

4. Foreign Key: The FOREIGN key constraint is used to prevent the action of breaking connections between tables, and the FOREIGN key constraint also prevents illegal data from being inserted into the foreign key column because it must be one of the values in the table it points to.

CREATE TABLE MyTable1 (ID varchar (+) NOT NULL, NAME varchar (+) NOT NULL, ...    PRIMARY key (ID), FOREIGN key (ID) REFERENCES MyTable2 (ID); ...)

Notice that the "id" column in "MyTable1" points to the "id" column in the "MyTable1" table.

The "ID" column in the "MyTable2" table is the PRIMARY KEY in the "MyTable2" table.

The "Id" column in the "MyTable1" table is the FOREIGN KEY in the "MyTable1" table.

5. Check: the range of values used to control the field.

CREATE TABLE MyTable (id varchar () NOT NULL, NAME varchar (+) NOT NULL, UNIQUE (id,name), PRIMARY KEY (ID), CHECK (ID > 0), ...);

6 . Default: Used to set defaults for new records.

CREATE TABLE MyTable (ID varchar (+) NOT NULL, NAME varchar (+) NOT NULL, City VARCHAR (+) DEFAULT ' Beijing ', PRIMARY KEY (ID), ...);

SQL transaction

1. Concept

A transaction (Transaction) is a unit of concurrency control and is a user-defined sequence of operations. These operations are either done or not, and are an inseparable unit of work. With transactions, SQL Server can bind a logically related set of operations so that the server maintains the integrity of the data.

2. Transaction properties
Transaction 4 Large Properties:

• atomicity (atomicity): A transaction is a complete operation.

• Consistency (consistency) : The data must be in a consistent state when the transaction is complete.

• Isolation (Isolation): All concurrent transactions that modify data are isolated from each other.

• persistence (Durability): After a transaction completes, its effect on the system is permanent.
  

3. Create a transaction
Statements that manage transactions in T-sql:

1. Start transaction: BEGIN TRANSACTION

2. COMMIT TRANSACTION: Commit Transaction

3. ROLLBACK TRANSACTION: ROLLBACK TRANSACTION
   

SQL triggers

1. Concept and role

Triggers belong to a special stored procedure that can contain complex SQL statements. The difference between a trigger and a stored procedure is that the trigger is automatically executed and does not contain parameters. It is often possible to write an automated program within a trigger that guarantees the integrity of data operations, extending the integrity checks on default values, constraints, and rules. The trigger executes automatically when the table is populated with data, deletes data, and updates one or more of the operations in the data.

2. Key benefits of using triggers

1. Triggers are executed automatically, after an object has been defined in the database, or when the data in the table has been modified in some way, it is activated immediately;

2. Triggers can achieve more complex integrity requirements than constraints, such as the check constraint can not reference columns in other tables, and triggers may be referenced; A check constraint is simply a conditional expression connected by a logical symbol, and a complex logical judgment function cannot be completed.

3. The trigger can take corresponding measures according to the difference of the table data before and after the modification;

4. Triggers can prevent malicious or erroneous insert,update and delete operations.
   

3. Types OF Triggers

SQL Server 2008 provides three types of triggers: DML triggers, DDL triggers, DDL triggers, and logon triggers.

Do not do a detailed introduction

SQL Storage Engine

Data in MySQL is stored in files (or memory) in a variety of different technologies. Each of these technologies uses different storage mechanisms, indexing techniques, locking levels, and ultimately offers a wide range of different capabilities and capabilities. By selecting different technologies, you can gain additional speed or functionality to improve the overall functionality of your application.

The storage engine is how to store the data, how to index the stored data, and how to update and query the data. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, the type that stores and operates this table).

There is only one storage engine in a database such as Oracle and SQL Server, and all data storage management mechanisms are the same. The MySQL database provides a variety of storage engines. Users can choose different storage engines for the data table according to different requirements, and users can write their own storage engine according to their own needs.

MySQL has a variety of storage engines (not currently described in detail):

MyISAM,InnoDB,MERGE,MEMORY (HEAP),BDB (BerkeleyDB),EXAMPLE,Federated,ARCHIVE,CSV,blackhole;

Reference:

"Database principles and application of the third edition"----------------------------------------, Wang, etc.

This article from the "777 Fast" blog, declined reprint!

SQL constraints, transactions, triggers, storage engines, multi-table queries