MySQL Finishing (ii)

　　I. Constraints of the MySQL operation table

MySQL provides a set of mechanisms to check if the data in a database table meets the specified conditions to ensure the accuracy and consistency of the data in the database tables, which is constrained.

(1) Set non-null constraint (NOT NULL), UNIQUE constraint (unique), field default

Create Table table name (    intunique, (Uniqueness constraint,    UK)varchar(5 not NULL , (non-null constraint, NK    ) varchar (3default'n'(set field defaults));

(2) PRIMARY KEY constraint (equivalent to a non-null constraint plus a unique constraint)

Create Table table name (    intprimarykey, (primary KEY constraint)    varchar(5  not NULL ,     varchar (3default'n');

(3) Multi-field primary key settings use the constraint keyword

 create  table      table name (ID  int   Name  varchar  (5 ) not  null   varchar  (3 ) default   " 12  "   constraint  pk_id_name primary  key   (id,name));  

(4) The field value automatically increases the CONSTRAINT keyword auto_increment

Create Table table name (    intprimarykey  auto_increment,    varchar(  5not null,    varchar(3default'  ");

(5) setting of the FOREIGN KEY constraint constraint,foreign key,references (when setting the FK constraint, the field that sets the FK must depend on the primary key of the parent table that already exists in the database, while the foreign key can be null)

Create Tabletable name (IDint, namevarchar(5) not NULL, Agevarchar(3)default ' A',    constraintfk_idForeign Key(ID)ReferencesThe table (ID) where the primary key is located;　　　　

　　Second, the operation of the index

(1) Why the index is used:

The database object index is similar to the book's directory, mainly in order to provide the speed of retrieving data from the table, the index can be composed of one or more fields generated by the table, can be found by MySQL to effectively find the field associated with the key, similar to directly from the directory to find what we need, rather than go to full-text search.

Index creation has pros and cons, creating indexes can improve retrieval speed, but too many indexes increase the pressure on the read disk.

According to the MySQL query document found, the index is divided into: Ordinary index, unique index, full-text index, single-column index, multi-columns index and spatial index.

(2) The scene created by the index:

Fields that are frequently queried, such as fields followed by where or on statements

Fields that require a grouped query, such as fields followed by a group by

A union query between a parent table and a Word table that has dependencies, such as primary key and foreign key

Set a field for a unique integrity constraint

Note: Fields that are seldom used in a query, or fields with many duplicate values on a field, are not suitable for adding an index

　　　　(3) Create a normal index: Index name (field name)　　　　

Create Table Fuck (    int,    varchar(5),    varchar( 3 default '  A ' ,     Index index_id (ID));

　　　　(4) View Index method:

Show create TABLE table name \g

　　　　

　　　　Explain select * from table name where Index field name =1\g

　　　　

　　　　(5) Create an index on a table that already exists:

　　　　Create index index name on table name (index field name);

　　　　CREATE INDEX index_name on fuck (name);

　　　　ALTER TABLE name add index index name ( index field name );

ALTER TABLE fuck Add index index_age (age);

　　　　(6) Create unique index (unique), full-text index (fulltext) is the same as normal index syntax, just precede the index keyword with a unique or fulltext

(7) Create multi-column index:ALTER TABLE name add index name ( index field name 1, index field name 2, ...);

　　　　 Note: Multi-column indexes, when an index is created, are associated with more than one field, although you can query through the associated field, but the multicolumn index will only work if the first field in the associated field is used in the query criteria.

　　　　(8) Delete index: Drop indexed index name on table name

　　Third, the operation of the view

(1) Why use a view?

To improve the reusability of complex SQL and the security of table operations, MySQL provides a view feature, which is actually a virtual table whose content is similar to a real table, that does not exist in the database with stored data values, and that rows and columns of data come from the base tables they reference.

Using views allows programmers to only care about the fields they need, and can block sensitive fields such as payroll.

The features of the view are as follows:

A) view is a virtual table generated by a base table

b) The columns of the view can come from different tables

c) The creation and deletion of views does not affect the base table

d) updates to the contents of the view directly affect the base table

e) Adding and deleting data is not allowed when the view is from more than one base table

(2) syntax for creating views

　　　　CREATE VIEW name as Select ID, age from table name : CREATE VIEW Fuck_view as select ID, age from Fuck;

(3) Delete a view

　　　　Drop View Name

　　　　(4) Modify the View

　　　　Mode one (created after first deletion): Drop view name; CREATE view name as SELECT ...

Mode two (direct modification): Create or Replace view name as SELECT ...

Mode three (ALTER statement): ALTER view name as SELECT ...

Note: The change and deletion of view is consistent with the basic table.

　　 Iv. introduction of Triggers

(1) Why use a trigger?

A trigger (TRIGGER) is one of the objects of MySQL that needs to be declared and executed, but the execution of the trigger is triggered by the event, that is, when the table changes, some processing occurs automatically.

MySQL provides a set of actions that are automatically executed when the Delete,update,insert statement is triggered. Triggers can act as interceptors.

Use Example: Single conditional trigger

Create a trigger with the name Tri_employee, which triggers an event each time you add data to the fuck table, which adds a piece of data to the employee table.

Create Trigger Insert  on  for Each row      Insert  into Values (haha',688,NULL,4);

Use example: Multi-Conditional trigger

Unlike triggers for a single condition, multiple statements that trigger execution are nested in the middle of begin,end, separated by semicolons, and MySQL is typically a semicolon as the closing symbol, but separated by semicolons when creating a trigger, and in order to solve the problem, you can use the keyword delimiter statement. To set the end symbol, such as the following statement, to set the end symbol to $$

delimiter $$Create TriggerTri_employee beforeInsert  onFuck forEach rowbegin    Insert  intoEmployee (EMPNO,EMPNAME,SALARY,HIREDATE,DEPTNO)Values( -,'haha',688,NULL,4); Insert  intoEmployee (EMPNO,EMPNAME,SALARY,HIREDATE,DEPTNO)Values( -,'hehe',886,NULL,5); End$ $delimiter;

To view a trigger:

(1) show triggers;

(2) Enter the system table to view the triggers stored in the system tables: Use Information_schema;select * from triggers \g

(3) View specified trigger in system table: SELECT * from triggers where trigger_name= ' xxx ' \g

Delete Trigger: The name of the drop trigger trigger;

MySQL Finishing (ii)