The MySQL Object

What are the common data objects for MySQL:

• Database/schema
• Table
• Index
• View/trigger/function/procedure

Multi-database uses:

• Segregation of business
• Isolation of resources

Common data objects on a table:

• Index
• Constraints
• views, triggers, functions, stored procedures

IndexWhat is a database index: An index is a directory of data in a database: indexes and data when two object indexes are mainly used to improve the query efficiency of the database data changes in the database also need to synchronize the change of index data, Because the index is in accordance with the B+tree, the position, once the data changes, then the corresponding location of the data will also change, so that after the search, to quickly index, and change the position is the index maintenance; View a database command:

mysql> help create IndexName: ' Create INDEX ' Description:Syntax:CREATE [online| OFFLINE] [unique| Fulltext| SPATIAL] INDEX index_name    [Index_type] on    tbl_name (index_col_name,...)    [Index_option].

Unique: Unique index (ID of User table, phone number, etc.) SPATIAL: Location Index (search for people around) two ways to create indexes and view indexes in a table

Mysql> SELECT * FROM vc;+------+------+| V | C |+------+------+| AB | AB |+------+------+1 row in Set (0.00 sec) mysql> CREATE index Idx_v on VC (v); Query OK, 0 rows affected (0.02 sec) records:0 duplicates:0 warnings:0 mysql> ALTER TABLE VC add KEY Idx_c (c); Query OK, 0 rows affected (0.02 sec) records:0 duplicates:0 warnings:0 mysql> Show index from vc;+-------+--------- ---+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+- --------+---------------+| Table | Non_unique | Key_name | Seq_in_index | column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+---------------+|          VC | 1 |            Idx_v | 1 | V |           A |     1 | NULL | NULL | YES |         BTREE |              | ||          VC | 1 |            Idx_c | 1 | C |           A |     1 | NULL | NULL | YES |         BTREE |               | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+-- ----+------------+---------+---------------+2 rows in Set (0.00 sec) mysql> Show create table vc;+-------+----------- --------------------------------------------------------------------------------------------------------------- --------------------------------------+| Table |                                                                                                                                                   Create Table |+-------+--------------------------------------------------------------------------- -------------------------------------------------------------------------------------+| VC | CREATE TABLE ' vc ' (' V ' varchar (5) default NULL, ' C ' char (5) default NULL, key ' Idx_v ' (' V '), key ' Idx_c ' (' C ')) Engine=innodb DEFAULT charset=latin1 |+-------+---------------------------------------------------------------- ------------------------------------------------------------------------------------------------+1 row in Set ( 0.00 sec)

In MySQL, index and key all represent indexes; ConstraintsConstraint: Unique constraint: Unique FOREIGN KEY constraint: CONSTRAINT create a UNIQUE constraint (a unique index can contribute to a unique constraint):

• A unique constraint is a special kind of index
• A unique constraint can be one or more fields
• The constraint can be built at the time of the construction of the table, or it can be mended later
• A primary key is also a unique constraint

What the index is:

• Primary KEY Index ID
• Single-Key index OrderID
• Single-Key index BookID
• Combined Index (userid + orderid)

What are the unique constraints:

• PRIMARY KEY constraint ID
• Single-Key unique index OrderID
• Combining unique Indexes Userid+orderid

Add a unique constraint to add a primary key index:

Mysql> ALTER TABLE Order add primary key (ID) #实际上是给主键id增加了一个索引, and this index is unique so the index becomes a unique constraint

To add a unique index:

Mysql>alter table Order Add unique key Idx_uk_orderid (ID)

A FOREIGN KEY constraint associates two tables of data with a certain condition: buy things, order forms, user information tables; If there is no foreign KEY constraint: There may be no user to order success, and if you use a FOREIGN key constraint, a user who does not exist at the database level cannot order successfully. Create a FOREIGN KEY constraint: Associate a user table and an order table with a foreign key:

mysql > ALTER TABLE order add CONSTRAINT constarint_uid FOREIGN KEY (userid) REFERENCES User (UserID)

When you create a foreign key constraint, you can specify the appropriate actions for the child table (the table that is associated with the foreign key) when the parent table (the referenced table) is deleted and updated, including: Restrict (limit), cascade (concatenation), set NULL, and no action. where restrict and no action are the same, the parent table cannot be updated when the child table is associated with a record, and Cascade indicates that the parent table updates or deletes the records corresponding to the child table when it is updated or deleted; Set NULL indicates that the corresponding field of the child table is set when the parent table is updated or deleted. Null.  Be cautious when selecting the latter two ways, which may result in loss of data due to incorrect operation. When performing load data and ALTER TABLE operations, the FOREIGN KEY constraint can be temporarily closed in order to speed up: Set foreign key_checks = 0, and then open after completion: Set foreign key_checks =1

mysql > ALTER TABLE order add CONSTRAINT constarint_uid FOREIGN KEY (userid) REFERENCES User (userid) on delete restrict ON UPDATE cascade;

On the update Cascade example:

Mysql> SELECT * FROM c_a;+----+------+| ID | Age  |+----+------+|  1 |   | |  2 |    3 | |  3 |    4 |+----+------+3 rows in Set (0.00 sec) mysql> SELECT * FROM c_b;+----+------+| ID | Age  |+----+------+|  1 |    2 | |  2 |   | |  3 |    4 |+----+------+3 rows in Set (0.00 sec) mysql> Update c_b set id=11 where age = 2; Query OK, 1 row Affected (0.00 sec) Rows matched:1  changed:1  warnings:0 mysql> select * FROM c_b;+----+---- --+| ID | Age  |+----+------+|  2 |   | |  3 |    4 | | One |    2 |+----+------+3 rows in Set (0.00 sec) mysql> SELECT * FROM c_a;+----+------+| ID | Age  |+----+------+|  2 |    3 | |  3 |    4 | | One |   |+----+------+3 rows in Set (0.00 sec)

Note: The FOREIGN KEY constraint changes only the constrained field; ON DELETE Cascade

ON DELETE Cascade	Insert	Update	Delete
Parent	Yes	You can only change values that are not in a constraint field in a child table	Yes
Child	Only the values of the constraint fields in the parent table can be inserted;	You can only change values that are not in the constraint field in the parent table	Yes

ON UPDATE cascade

ON UPDATE cascade	Insert	Update	Delete
Parent	Yes	Yes	Only the values that are not in the constraint field in the child table can be deleted;
Child	Only the values of the constraint fields in the parent table can be inserted;	Only values that are not in the constraint field in the parent table can be updated	Yes

It can be seen that child can only be deleted, the rest is constrained by the parent table, and parent can be inserted, but one of them is constrained by the Sub-table constraint field, and the order table is added a constraint, (CONSTRAINT, constraint keyword) constraint name constarint_uid; He is a foreign key constraint and the UserID in the order table is a foreign key, associating the UserID in the order table with the UserID of the user table; Considerations for using foreign keys:

• Must be a InnoDB table, other engines do not support foreign keys
• field types must be the same as each other
• The constraint field of the primary table requires an index (in the example above, the user table is the primary table, so the userid is required to be indexed in the user table)
• Constraint names must be unique, even if they are not on a single table (Constarint_uid is unique across the entire library)

To delete a constraint:

Mysql> ALTER TABLE order drop FOREIGN KEY constarint_uid;

views ViewView function:

• A view sets the result set of a set of query statements, which is a virtual structure, not the actual data
• Views simplify database access and enable multiple query statements to be structured as a single virtual structure
• View can hide the database backend table structure, improve the security of the database
• A view is also a privilege management that provides only partial data to the user

Create a view of a completed order:

MySQL > CREATE view order_view as SELECT * from order where status = 1

View Examples:

mysql> Create TABLE t (qty int, price int);mysql> INSERT into T VALUES (3,);mysql> create VIEW v as SELECT qty, Price, qty*price as value from t;mysql> SELECT * from v;+------+-------+-------+| Qty  | price | value |+------+-------+-------+|    3 |    |   |+------+-------+-------+

TriggerWhat triggers are: Triggers are special programs that are added to the table and trigger when a particular event (Insert/update/delete/alter table) appears on the table. One event on a table MySQL can define only one trigger to do:

• Data revisions;
• Migration table;
• Implementation of specific business logic;

The trigger has two users:

• Performer
• Called by

Triggers--Basic syntax

Create[definer = {User | Current_User}]     --Defines the permissions performed trigger Trigger_name trigger_timetrigger_event on tbl_namefor each ROW                                    -- Each line involved will execute trigger_bodytrigger_body  T Trigger_time:{before | After}trigger_event:{insert | UPDATE | DELETE}

The delimiter terminator is set by default, which is a semicolon. However, when the trigger should be executed for a set of SQL, a semicolon will appear, so the Terminator is set;

Use New,old to refer to the order in which the trigger is triggered by a change in the record content in the trigger: Before trigger, line action, after trigger, and any one of these steps does not continue to perform the remainder of the operation.  If it is an operation on a transaction table, it will be rolled back as a transaction (rollback), but if it is an operation on a non-transactional table, the updated record will not be rolled back, which is also a concern when designing the trigger. Features of the trigger:

• The trigger has a loss of performance and should be used with caution
• The same class of events can only be created once in a table
• For a transaction table, the trigger execution fails and the entire statement is rolled back
• Row format master-slave replication, triggers do not execute on subordinate libraries
• You should prevent recursive execution when using triggers

can assist the application to ensure data integrity on the database side. Stored ProceduresDefinition: A stored procedure is a set of SQL statements stored on the database side that can be called multiple times by the user through stored procedure names and arguments. Characteristics:

• Use flexibility to complete complex business logic using flow control statements, custom variables, and more
• Improve data security, mask applications directly to the table, easy to audit
• Reduce network transmission
• Improve the complexity of code maintenance, in real-life to assess whether the scene is suitable for

Stored Procedures-Basic syntax

CREATE      [definer = {User | Current_User}]                 --Defines the permissions performed     PROCEDURE  sp_name ([proc_parameter[,...]])     [Characteristic ...]  Routine_body proc_parameter:     [In | Out | INOUT]  param_name  type                                   type: Any                                            valid MySQL data type

In: input parameter, indicating that the parameter value has been specified in the calling stored procedure, modifying the parameter in the call stored procedure will not return out: output parameter, can be changed inside the stored procedure, can return InOut: input output parameter; Characteristic:comment ' String '--comment | [NOT] Deterministic--will return a deterministic value routine_body:valid SQL routine statement-similar to trigger

Focus:

• Intermediate variable definition: DECLARE
• Flow control Statements
• Parameter passed in

Query: What stored procedures are in the database

Mysql> show PROCEDURE STATUS;

View: Details of a trigger

mysql> SHOW TRIGGER STATUS;

Use of stored procedures: 1. Set parameter values:

mysql> Set @total = 5;mysql> set @res = 0;

2. Call the stored procedure:

Mysql> Call Proc_test1 (@total, @res);

3. View the return value:

Mysql> Select @res

To delete a stored procedure or function:

DROP {PROCEDURE | FUNCTION}  [IF  EXISTS]  sp_name

View stored procedures or functions show {procedure | function} status like ' file_in_stock ' view the definition of a stored procedure or function show Create {procedure | function} Sp_name stored procedure-flow control language

Custom functions

• A custom function is similar to a stored procedure, but must have a return value
• The custom function is similar to the sum (), Max (), and other MySQL native functions using the same method: select Func (val); SELECT * from tbl where col = func (val)
• Since custom functions may be used in traversing data, pay attention to performance loss

Custom Function-The basic syntax eventually passes the return;

Mysql> Select Func_test1 (4);

Confirm permissions: Create a stored procedure or function requires: Creation routine modify or delete the stored procedure needs: Alter routine executes the stored procedure needs: Execute can be nested use; Summary

• Triggers and stored procedures are not conducive to horizontal expansion, and are used in statistical and operational operations;
• There is also a high cost of code management maintenance;
• But:
  • Simplify application development, reduce data transmission and improve processing efficiency;

Summary

• Index is created in view
• Constraints:
  • Unique constraint,
  • FOREIGN KEY constraints:
    • ALTER TABLE order add CONSTRAINT constarint_uid FOREIGN KEY (userid) REFERENCES user (UserID);
    • Four modes of FOREIGN KEY constraint: Restrict,no action,set null,cascade;
• VIEW
• Tigger:
  • One event of a table can define only one trigger
  • Delimiter//
  • New.age Old.age
• PROCEDURE:
  • DECLARE,
  • Parameter incoming return
  • Flow Control Language
  • Use three steps
    • Initialize parameters
    • Call a stored procedure
    • View put back value
• function
  • Must have a return value
  • Use Select Func_test1 (14);
• SUBSTRING (goods_name,1,5): intercepts goods_name5 characters from position 1
• A rigth Join B on..:b Displays the null field in B;

The MySQL Object