Day40 python MySQL "Four" index "view" "trigger" "Stored Procedure" "function"

1. Index

The index is the equivalent of a book directory, which helps users quickly find what they need.

The database can greatly improve the query efficiency by using a variety of rapid localization techniques. In particular, when the amount of data is very large and the query involves more than one table, using an index can often speed up the query by tens of thousands of times.

Index Benefits: You can improve the efficiency of the query, and the larger the amount of data is more obvious effect.

Index disadvantage: Low efficiency in adding data and deleting data

Index Type:

A 1.hash:hash is a (key=>value) Form of a key-value pair that allows multiple keys to correspond to the same value, but does not allow a key to correspond to more than one value, and to establish a hash index for a column or column.   The value of this column or column will be used to calculate a hash value by a certain algorithm, corresponding to one row or several rows of data. The hash index can be positioned once, and does not need to be looked up by layers like a tree index, so it is highly efficient.

Suppose you create one of the following tables:
CREATE TABLE Testhash (
FName VARCHAR () not NULL,
LName VARCHAR () not NULL,
KEY USING HASH (fname)
) Engine=memory;
The following data is included:
　　　　

Suppose the index uses the hash function f (), as follows:

F (' Arjen ') = 2323 F (' Baron ') = 7437 F (' Peter ') = 8784 F (' Vadim ') = 2458

At this point, the structure of the index is probably as follows:

The slots are orderly, but the records are not orderly. When you perform
Mysql> SELECT lname from Testhash WHERE fname= ' Peter ';
MySQL calculates the ' Peter ' hash value and then queries the index's row pointer through it. Because F (' Peter ') = 8784,mysql finds 8784 in the index, it gets a pointer to record 3.
Because indexes only store very short values, the index is very compact. The hash value does not depend on the data type of the column, and the index of a tinyint column is as large as the index of a long string column.

2.BTREE: is a kind of index value according to a certain algorithm, into a tree-shaped data structure. Like a binary tree.

　　

　

Index classification:

1. General Index

2. Unique index

3. Primary KEY index

4. Combined Index

5. Fulltext: Full-Text Indexing

Currently only MyISAM engines are supported. And only CHAR, VARCHAR, text columns can be created on the text index.

Fulltext Index is indexed according to the principle of word segmentation. In Latin, most of the alphabet, Word segmentation can be easily separated by the space. However, it is obvious that Chinese cannot make participle in this way. And what about that? This introduces you to a MySQL Chinese word breaker plugin mysqlcft, with it, you can Chinese word segmentation, want to know the students please MYSQLCFT, of course, there are other word-breaker can be used.

1. Create a normal index

1 CREATE index AAA on Ren (p_name) 2 Add normal index 3 NOTE: index: Indicates an indexed AAA: An alias for an index, on: Indicates to which table to add the index  ren: Table name, (add indexed fields, multiple fields with "," interval)

2. Create a unique index

1 CREATE Unique index age on Ren (p_age) 2 Add a unique index 3 NOTE: Unique index: Represents an alias for the index, on: Indicates which table to add the index  ren: Table name, (add indexed fields, multiple Fields with "," interval)

3. Create a primary key index

1 ALTER TABLE name add PRIMARY key (ID), 2 add between index 3 note: The primary key index can only have one

4. Create a composite index

1 CREATE index Id_name on Ren (id,name) 2 Add composite index 3 Note: After creating a composite index, query: 4 ID and name--use index 5 ID-                -use index 6 name           --Do not use index

2. View

A view is a virtual table whose contents are defined by a query. As with a real table, a view contains a series of column and row data with names. However, the view does not exist in the database as a stored set of data values. Row and column data is derived from the table referenced by the query that defines the view, and is generated dynamically when the view is referenced.

View Benefits:

A, simplify the junction between tables (write the connection in select);

b, the appropriate use of the view can be more clearly expressed in the query

C. Filter unwanted data (select part) view to provide security for confidential data

D. Use the view to calculate field values, such as summarizing values.

1. Create a View

1 #create View view name as  SQL query Statement 2 CREATE VIEW Ren_view as select P_id,p_name,p_age,p_leader from Ren;

2. Working with views

1 SELECT * from view name;

3. Updating view data

1 Update ren_view set p_age =99 where p_id = ' p004 ';

4. Delete view data

1 DELETE from Ren_view where p_id = ' p010 ';

5. Delete a view

1 Drop View Ren_view;

3. Trigger-trigger

Trigger (Trigger): Monitors a situation and triggers an action.

Trigger creation Syntax four elements: 1. Watch location (table)

2. Monitoring Events (Insert/update/delete)

3. Trigger Time (After/before)

4. Trigger Event (Insert/update/delete)

1. Create TRIGGER syntax

1 Create trigger Triggername  After/before  insert/update/delete 2 on     table name for each row #这句话是固定的 3  4 begin 5
    6     #需要执行的sql语句 7  8 End 9 10 note 1:after/before: Only one can be selected, after represents a post-trigger, before indicates a front-trigger 11 note 2:insert/update/delete: Select only One

Create two tables

#商品表 CREATE TABLE Goods ( ID int primary KEY auto_increment, Name varchar (20), Num INT );

#订单表 CREATE TABLE Order_table ( OID int primary Key auto_increment, GID int, much int );

Add 3 Item data

1 INSERT into G (name,num) VALUES (' Commodities 1 ', 10), (' Goods 2 ', 10), (' Goods 3 ', 10);

If we're not using a trigger: Let's say we're selling 3 item 1, we need to do two things.

1. Insert a record into the order form

1 INSERT into O (Gid,much) values (1,3);

2. Update the remaining quantity of commodity list item 1

Update g set num=num-3 where id=1;

Now, let's create a trigger:

1 CREATE trigger TG1 after insert on  order_table for each row2 3 begin 4     5     

At this point we just execute:

1 insert INTO order_table (Gid,much) values (1,3);                

Will find that the number of items 1 has changed to 7, indicating that when we insert an order,

The trigger automatically helps us with the update operation.

But now there is a problem, because we trigger that NUM and ID are written dead, so no matter which product we buy, the final update is the number of goods 1. For example: We insert a record into the order form:

INSERT into O (Gid,much) values (2,3);

After execution, it will be found that the number of goods 1 has changed to 4, and the number of goods 2 has not changed, which is obviously not the result we want. We need to change the trigger we created earlier.

How do we refer to the value of the row in the trigger, that is, we want to get the GID or much value in our newly inserted order record.

For insert, the newly inserted row is represented by new, and the value of each column in the row is represented by the new. Column name.

So now we can change our triggers like this:

Create trigger TG2 Afert Insert on order_table for each Rowbegin   update goods Set num = Num-new.much  

The second trigger is created, we'll first delete the first trigger.

1 drop trigger TG1;

To test again, insert an order record:

1 insert INTO order_table (Gid,much) VALUES (2,3)

After the execution of the discovery item 2 the quantity becomes 7, now is right.

Now there are two situations:

1. When the user revokes an order, we delete an order directly, do we need to add the corresponding quantity of goods back?

For delete: Originally there was a row, and then was deleted, want to refer to the deleted line, old to represent, old. Column names can refer to the value of the row being deleted.

Then our trigger should be written like this:

Create trigger TG3 Afert Delete on order_table for each  row Bigen     

2. When the user modifies the quantity of an order, how do we write the trigger change?

Don't write, write yourself ....

4. Stored Procedures

The MySQL database started supporting stored procedures after version 5.0, so what is a stored procedure? How do I create, view, and delete stored procedures? What are the advantages of stored procedures?

1. Concept: What is a stored procedure: similar to a function (method), simply saying that a stored procedure is a collection of statements written to complete a particular function in a database, which includes SQL statements (additions and deletions to data), conditional statements, and loop statements.

2. View existing stored procedures

1 Show procedure status;

3. Delete a stored procedure

1 drop procedure stored procedure name;

4. Calling a stored procedure

1 Call stored procedure name (parameter 1 parameter  1 type, parameter 2  parameter 2 type);

5. Create a stored procedure

1 # Experience Package 2 CREATE PROCEDURE P1 () 3 begin 4    SELECT * from account;  5   6 End

1 2#sql Experience Parameter 2 CREATE PROCEDURE P2 (in M int) 3 BEGIN 4   SELECT * from account where money > M;5 end

1 #SQL Experience Control 2 CREATE PROCEDURE P3 (in X int,in C char (1)) 3  4 begin 5  6     if C = ' d ' then 7      8         SELECT * Fro M account where money >x; 9    Else one-          select * from account  where money <x;      End If;14 End

#体会循环: Calculates the sum of 1-100 cumulative and returns the calculated result.
CREATE PROCEDURE P4 (inout n int)    begin      DECLARE sum int default 0;--set sum variable and specify initial value 0      DECLARE i int;--DECLARE variable      set i = 0;    --Set the value of the variable by the set while i<=n do  --Begins the loop            set sum = Sum +i;            Set i = i+1;          End while; --End Loop

Select sum; --Provide                              the result set n = sum;--to provide the result of the calculation to the output variable N;      End;                  --Call:       Set @n = +       ; Call P4 (@n);       Select @n;

Stored Procedure Benefits :
1. The stored procedure enhances the SQL language flexibility. Stored procedures can be written using control statements, can complete complex judgments and more complex operations, has a strong flexibility;
2, reduce network traffic, reduce the network load. After a stored procedure is successfully created on the database server side, it is only necessary to invoke the stored procedure, and the traditional practice is to send a large number of SQL statements over the network to the database server side and then execute them.
3. Stored procedures are compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled, while the general SQL statements are compiled once per execution, so using stored procedures can improve database execution speed.
4, the system administrator by setting the permissions of a stored procedure to achieve access to the corresponding data restrictions, to avoid the unauthorized user access to data, to ensure the security of data.

　　

5. Functions

MySQL provides built-in functions:

View Code

More functions: Official bash here

1. Custom Functions

CREATE FUNCTION fun1 (i1 int,i2 int) RETURNS int//Set return type begin    DECLARE sum int default 0;    Set sum = I1+i2;    

2. Calling a custom function

#直接调用自定义函数select fun1 (1,5), #在sql语句中使用自定义函数select fun1 (parameter 1, parameter 2), name from table name

3. Delete a custom function

Drop FUNCTION Fun_name;

Day40 python MySQL "Four" index "view" "trigger" "Stored Procedure" "function"