MySQL database (primary key, index, foreign key, trigger ...) ）

Primary key:

A property or property group that uniquely identifies a row in a table. A table can have only one primary key, but there may be multiple candidate indexes. Primary keys often form referential integrity constraints with foreign keys, preventing data inconsistencies. The primary key guarantees that the record is unique and the primary key domain is not empty, and the database management system automatically generates a unique index for the primary key, so the primary key is also a special index .

Index:

is used to quickly look for records that have a specific value. Mainly for the convenience of retrieval, is to speed up access, according to certain rules created, generally play the role of sorting.

Uniqueness Index: This index is basically the same as the previous "normal index", but with one difference: all the values of an indexed column can only occur once, that is, they must be unique.

　　Note: When your application makes SQL queries very slowly, you should think about whether you can build an index.

In a database table, indexing a field can greatly improve query speed. Suppose we create a mytable table:

CREATE TABLE mytable (ID INT NOT NULL, username VARCHAR (+) not NULL); We randomly inserted 10,000 records, including one: 5555, admin.

In the Find username= "admin" record select * from MyTable WHERE username= ' admin '; If an index has been established on username, MySQL does not need any scanning, that is, the record can be found exactly. Instead, MySQL scans all records, that is, to query 10,000 records.

Index sub- column indexes and composite Indexes . A single-column index, that is, an index contains only single columns, and a table can have multiple single-row indexes, but this is not a composite index. A composite index, that is, a cable that contains multiple columns.

• Normal index: This is the most basic index, it does not have any restrictions
• Unique index: It is similar to the previous normal index, except that the value of the indexed column must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique.
• Primary KEY index: It is a special unique index and is not allowed to have null values. The primary key index is typically created at the same time as the table.
• Composite index: CREATE TABLE mytable (ID int NOT NULL, username varchar (+) NOT NULL, City VARCHAR (+) NOT NULL, age INT not  NULL); To further extract the efficiency of MySQL, it is necessary to consider building a composite index. is to build name, city, and age into an index.

Selection and the appropriate data type:

• Typically, smaller data types tend to be better, and smaller data types typically require less space in disk, memory, and CPU caches for faster processing.
• Simple data types are better, integer data is less expensive to handle than characters, because string comparisons are more complex. In MySQL, you should use a built-in date and time data type instead of a string to store the time, and an integer data type to store the IP address.
• Try to avoid null: The column should be specified as NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query optimization because they complicate indexing, index statistics, and comparison operations. You should use 0, a special value, or an empty string instead of a null value.

Time to build the index:

In general, the columns that appear in the where and join need to be indexed, but not entirely, because MySQL uses the index only for <,<=,=,>,>=,between,in, and sometimes like.

FOREIGN key:

is one or more columns that are used to establish and strengthen links between two table data. FOREIGN key constraints are primarily used to maintain data consistency between two tables. In short, the foreign key of the table is the primary key of the other table, and the foreign key ties the two tables together. In general, to delete a primary key in a table you must first make sure that no other table has the same foreign key (that is, the primary key in the table does not have a foreign key associated with it).

Create a foreign key in MySQL:

On Delete, when updated:

• RESTRICT: restriction, which means that the value of a parent table is not allowed to be deleted directly if the Word table refers to the value of a field in the parent table.
• No ACTION: There is no referential integrity relationship, and it does not take effect.
• CASCADE: Cascade, deletes a record of the parent table, and records that reference the value in the Word table are deleted.
• Set NULL: When a record is update/delete on the parent table, the column of the matching record on the child table is set to NULL.

Trigger:

A trigger is a database object associated with a table operation that invokes the execution of a trigger on a table when a specified event occurs on the table on which the trigger is located.

Take navicat for MySQL as an example to illustrate the use of triggers:

　　

• Name: User-definable name of a trigger
• Trigger: There are two options (before&after) that indicate whether to execute the statement before or after
• Insert, UPDATE, delete

There are 6 types of triggers that can be created: before insert, before UPDATE, before DELETE, after INSERT, after UPDATE, and after DELETE.

Another limitation is that you cannot create 2 triggers of the same type on a table at the same time, so there are up to 6 triggers on a table.

Insert trigger: Activates a trigger when inserting a row, which may be triggered by an insert, LOAD DATA, and REPLACE statement;
UPDATE trigger: Activates a trigger when a row is changed and may be triggered by an UPDATE statement;
Delete trigger: Activates a trigger when a row is deleted and may be triggered by a delete, REPLACE statement.

BEGIN ... END detailed
In MySQL, BEGIN ... The syntax for the END statement is:

BEGIN
[Statement_list]
END
Where statement_list represents a list of one or more statements, each statement in the list must end with a semicolon (;).
In MySQL, the semicolon is the end-of-statement identifier, and a semicolon indicates that the segment statement has ended and MySQL can start executing. Therefore, the interpreter encounters a semicolon in the statement_list and then begins execution, and then reports an error because no END is found that matches the BEGIN.

This will use the DELIMITER command (DELIMITER is the delimiter, the meaning of the delimiter), it is a command, do not need a statement to end the identity, the syntax is:
DELIMITER New_delemiter
New_delemiter can be set to 1 or more length symbols, the default is a semicolon (;), we can modify it to other symbols, such as $:
DELIMITER $
After that, the statement ends with a semicolon, and the interpreter does not react, only if it encounters $, it is considered to be the end of the statement. Note that after use, we should also remember to change it back.

An example of a complete database trigger is shown below:

Suppose there are two tables in the system:
Class table Class (class number ClassID, number of students in the class Stucount)
Student Table Student (School number Stuid, class number ClassID)
To create a trigger that automatically updates the number of students in the class table as the student adds, the code is as follows:

DELIMITER $
Create trigger Tri_stuinsert after insert
On student for each row
Begin
declare c int;
Set c = (select Stucount from class where classid=new.classid);
Update class Set Stucount = C + 1 where ClassID = New.classid;
end$
DELIMITER;

Variable explanation
MySQL uses DECLARE to define a local variable that can be used only at BEGIN ... END compound statement, and should be defined at the beginning of the compound statement,

That is, before the other statements, the syntax is as follows:

DECLARE var_name[,...] Type [DEFAULT value]
which
Var_name is the variable name, as with the SQL statement, the variable name is not case-sensitive; Type is any data type supported by MySQL; You can define multiple variables of the same type at the same time, separated by commas, the initial value of the variable is NULL, and if necessary, you can use the default clause to provide the defaults. The value can be specified as an expression.

A SET statement is used to assign values to variables, with the following syntax:

SET var_name = expr [, var_name = expr] ...

NEW and Old detailed

The new keyword is used in the above example, similar to INSERTED and DELETED in MS SQL Server, where new and old are defined in MySQL to represent

The row of data that triggers the trigger in the same table as the trigger.
In particular:
In an INSERT trigger, new is used to represent the before or already (after) inserted data;
In an UPDATE trigger, old is used to represent the original data that will or has been modified, and new is used to represent the data that will or has been modified;
In a delete type trigger, old is used to denote the original data that will or has been deleted;
How to use: New.columnname (ColumnName is a column name for the corresponding data table)
In addition, old is read-only, and NEW can use SET assignment in a trigger so that the trigger is not triggered again, causing a circular call (such as adding "2013" to the student's number before each insert).

View triggers

and view the database (show databases;) to view the table (show tables;), the syntax for viewing the trigger is as follows:

SHOW TRIGGERS [from schema_name];
Where Schema_name is the name of the schema, in MySQL, the schema and database are the same, that is, you can specify the name of the databases, so that

Without first "use database_name;" The

Delete Trigger

As with deleting a database, deleting a table, the syntax for deleting a trigger is as follows:

DROP TRIGGER [IF EXISTS] [schema_name.] Trigger_name

Execution order of triggers

The database we build is generally a InnoDB database, and the tables created on it are transactional tables, which are transaction-safe. At this point, if the SQL statement or trigger execution fails, MySQL rolls back the transaction, with:

① if the before trigger execution fails, SQL does not execute correctly.
The after type trigger does not fire when the ②sql execution fails.
The ③after type of trigger execution fails and SQL is rolled back.

MySQL database (primary key, index, foreign key, trigger ...) ）