Mysql Data Object

Mysql Data Object
 Objective:

 

• Understanding common database objects
• Learn how to create specific data objects

What are common mysql Data Objects:

• DataBase/Schema
• Table
• Index
• View/Trigger/Function/Procedure

Multi-Database usage:

• Business isolation
• Resource isolation

Common Data Objects in a table:

• Index
• Constraints
• View, trigger, function, stored procedure

Index What is database index: Index is the directory of data in the database: Two object indexes are used to improve the query efficiency of the database. Data changes in the database also need to be synchronized to index data changes, because the index is arranged based on B + TREE, once the data changes, the corresponding location of the data also needs to change, so that the index can be quickly indexed, the change location 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 (User table ID, mobile phone number, UNIQUE information) SPATIAL

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, both INDEX and KEY represent indexes; Constraints CONSTRAINT: unique foreign key CONSTRAINT: CONSTRAINT creates a unique CONSTRAINT (a unique index can create a unique CONSTRAINT ):

• The unique constraint is a special index.
• The unique constraint can be one or more fields.
• The constraint can be created when the table is created or added later.
• Primary keys are also a unique constraint.

 

What are the indexes:

• 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
• Combined unique index userid + orderid

Add a unique constraint to add a primary key index:

Mysql> alter table order add primary key (id) # actually adds an index to the primary key id, which is unique, so this index becomes a unique constraint.

Add a unique index:

mysql>alter table order add unique key idx_uk_orderid(id)

Foreign key constraints are used to associate the data of two tables by certain conditions. Example: shopping items, order tables, and user information tables. If there is no foreign key constraint, the order can be placed successfully even if there is no foreign key constraint; if the foreign key constraint is used, users who do not exist at the database level cannot place orders successfully. Create a foreign key constraint: associate a user table with an order table using a foreign key:

mysql > alter table order add CONSTRAINT constarint_uid FOREIGN KEY (userid) REFERENCES user(userid)

When creating a foreign key constraint, you can specify the operations performed on the child table (the table associated with the foreign key) When deleting and updating the parent table (the referenced table, including restrict, cascade, set null, and no action. The restrict and no actions are the same, which means that the parent table cannot be updated when the sub-table has associated records. cascade indicates that when the parent table is updated or deleted, update or delete the records corresponding to the sub-Table. set null indicates that the corresponding field of the sub-table is set null when the parent table is updated or deleted. Exercise caution when selecting the last two methods, which may lead to data loss due to incorrect operations. To speed up the load data and alter table operations, you can temporarily disable the foreign key constraint: set foreign key_checks = 0; and then enable it after the operation is complete: 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 update cascade example:

mysql> select * from c_A;+----+------+| id | age  |+----+------+|  1 |   22 ||  2 |    3 ||  3 |    4 |+----+------+3 rows in set (0.00 sec) mysql> select * from c_B;+----+------+| id | age  |+----+------+|  1 |    2 ||  2 |   33 ||  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 |   33 ||  3 |    4 || 11 |    2 |+----+------+3 rows in set (0.00 sec) mysql> select * from c_A;+----+------+| id | age  |+----+------+|  2 |    3 ||  3 |    4 || 11 |   22 |+----+------+3 rows in set (0.00 sec)

Note: The foreign key constraint changes only the bound field; on delete cascade

On delete cascade	Insert	Update	Delete
Parent	Yes	Only values not available in the constraint fields in the subtable can be changed.	Yes
Child	Only values of the constraint fields in the parent table can be inserted;	Only values not available in the constraint fields in the parent table can be changed.	Yes

On update cascade

On update cascade	Insert	Update	Delete
Parent	Yes	Yes	Only values that are not included in the restricted fields in the subtable can be deleted;
Child	Only values of the constraint fields in the parent table can be inserted;	Only values not available in the constraint fields in the parent table can be updated.	Yes

You can see that child can only be deleted, and the rest are restricted to the constraint fields of the remaining parent table. parent can be inserted, but one of them is restricted by the constraint field of the child table. This adds a constraint to the order table, (CONSTRAINT, the CONSTRAINT keyword) the CONSTRAINT name constarint_uid is a foreign key CONSTRAINT and the userid in the order table is a foreign key. The userid in the order table is associated with the userid in the user table; note:

• It must be an innodb table. Other engines do not support foreign keys.
• The types of mutually constrained fields must be the same
• The constraint fields of the primary table must be indexed (in the preceding example, the user table is the primary table, so the userid must be indexed in the user table)
• The constraint name must be unique, even if it is not on a table (constarint_uid is unique throughout the database)

Delete a constraint:

mysql> alter table order drop FOREIGN KEY constarint_uid;

 

View View function:

• View refers to the result set composed of a set of query statements. It is a virtual structure and is not the actual data.
• The view can simplify database access and structure multiple query statements into a virtual structure.
• The view can hide the database's back-end table structure to improve database security.
• A view is also a type of permission management that only provides partial data to users.

Create a view of completed orders:

mysql > create view order_view as select * from order where status = 1

View Example:

mysql> CREATE TABLE t (qty INT, price INT);mysql> INSERT INTO t VALUES(3, 50);mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;mysql> SELECT * FROM v;+------+-------+-------+| qty  | price | value |+------+-------+-------+|    3 |    50 |   150 |+------+-------+-------+

 

Trigger What is a trigger: A trigger is a special program added to a table. It is triggered when a specific event (insert/update/delete/alter table) appears on the table. In mysql, only one trigger can be defined for one event of a table:

• Data correction;
• Migration table;
• Implement specific business logic;

The trigger has two users:

• Performer
• Caller

Trigger-basic syntax

CREATE [DEFINER = {user | CURRENT_USER}] -- defines the execution permission TRIGGER trigger_name trigger_timetrigger_event ON tbl_nameFOR each row -- trigger_bodytrigger_body t trigger_time: {BEFORE | AFTER} trigger_event: {INSERT | UPDATE | DELETE}

Set the delimiter Terminator. The default delimiter is a semicolon. However, when the trigger should execute a set of SQL statements, a semicolon will appear, so set the end character;

 

Use NEW and OLD to reference the changed records in the trigger. trigger sequence: before trigger, row operation, and after trigger; if an error occurs in any of the steps, the remaining operations will not continue. If it is an operation on the transaction table, it will be rolled back as a transaction, but if it is an operation on a non-transaction table, the updated records cannot be rolled back, which is also a problem to be aware of when designing a trigger. Trigger features:

• Trigger may cause performance loss and should be used with caution
• The same type of event can only be created once in a table.
• For the transaction table, if the trigger fails to be executed, the entire statement is rolled back.
• Row-format master-slave replication, trigger not executed on slave Database
• Use triggers to prevent Recursive Execution

It can help applications in the database to ensure data integrity. Stored Procedure Definition: a stored procedure is a set of SQL statements stored in the database. You can use the stored procedure name and the program module that has been called multiple times by passing parameters. Features:

• Flexible use, you can use flow control statements, custom variables, and other complex business logic
• Improves data security, shields applications from performing table operations directly, and facilitates audit.
• Reduce Network Transmission
• It increases the complexity of code maintenance. In actual use, it is necessary to assess whether the scenario is suitable.

Stored Procedure-basic syntax

CREATE [DEFINER = {user | CURRENT_USER}] -- Define the execution permission PROCEDURE sp_name ([proc_parameter [,...]) [characteristic ..] routine_body proc_parameter: [IN | OUT | INOUT] param_name type: Any valid MySQL data type

 

IN: The input parameter indicates that the parameter value has been specified during the call stored procedure. modifying this parameter during the call stored procedure does not return the OUT: output parameter, which can be changed within the stored procedure, can Return INOUT: input and output parameters; characteristic: COMMENT 'string' -- COMMENT | [NOT] DETERMINISTIC -- whether a definite value routine_body: Valid SQL routine statement is returned -- similar to trigger

 

Important:

• Definition of intermediate variables: DECLARE
• Flow Control statement
• Parameter input

Query: database Stored Procedures

mysql> show PROCEDURE STATUS ;

View: details of a trigger

mysql> SHOW TRIGGER STATUS ;

 

Usage of stored procedures: 1. Set the parameter value:

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

Delete a stored procedure or function:

DROP {PROCEDURE | FUNCTION}  [IF  EXISTS]  sp_name

View stored procedures or function SHOW {procedure | function} status like 'file _ in_stock 'view stored procedures or function definitions show create {procedure | function} sp_name stored procedure-Flow Control Language

 

Custom Functions

• User-Defined Functions are similar to stored procedures, but must return values.
• The usage of custom functions such as sum (), max (), and other mysql native functions is similar to: select func (val); select * from tbl where col = func (val)
• Since user-defined functions may be used in data traversal, pay attention to performance loss.

Custom functions-the basic syntax is finally called through RETURN;

mysql> select func_test1(4);

Confirm permissions: to create a stored procedure or function, you need to: create routine to modify or delete a stored procedure. You need to: alter routine to execute a stored procedure. execute can be nested; Summary  

• Triggers and stored procedures are not conducive to horizontal scaling and are mostly used for statistics and O & M operations;
• In addition, code management and maintenance costs are relatively high;
• However:
  • Simplify application development, reduce data transmission, and improve processing efficiency;

Summary

• Create and view Indexes
• Constraints:
  • Unique constraint,
  • Foreign key constraints:
    • Alter table order add CONSTRAINT constarint_uid foreign key (userid) REFERENCES user (userid );
    • Foreign key constraints: restrict, no action, set null, cascade;
• VIEW
• TIGGER:
  • One event of a table can only define one trigger.
  • Delimiter //
  • NEW. age OLD. age
• PROCEDURE:
  • DECLARE,
  • Parameter input return
  • Stream Control Language
  • Step 3
    • Initialization parameters
    • Call Stored Procedure
    • View Return Value
• Function:
  • Must have a return value
  • Use select func_test1 (14 );
• SUBSTRING (goods_name,): extract goods_name5 characters from position 1
• A ristmjoin B on...: B displays the null field in B;