MySQL database-MySql index & view & stored procedure

MySQL database-MySql index & view & stored procedure

MySQL index

What is an index

  • In the database table, indexing the fields can greatly improve the query speed. By making good use of these indexes, MySQL queries and operations can be made more efficient.
  • If the MySQL with a reasonable design and use of indexes is a Lamborghini, then the MySQL without the design and use of indexes is a human tricycle. Take the directory page (index) of a Chinese dictionary for example, we can quickly find the word we need in the directory (index) sorted by pinyin, strokes, radicals, etc.

Common index classification

Index nameDescription
Primary keyThe primary key is a unique index, each table can only have one primary key, used to identify each record in the data table
Unique index (unique)Unique index means that all values of the index column can only appear once and must be unique
Ordinary index (index)The most common index, the role is to accelerate the speed of data access
  • MySql stores the indexes of a table in the same index file. If you add, delete, or modify the data in the table, MySql will automatically update the index.

Primary key index (PRIMARY KEY)

  • Features: The primary key is a unique index. Each table can only have one primary key, which is used to identify a record in the data table.
  • A table can have no primary key, but there can only be one primary key at most, and the primary key value cannot contain NULL.

1) Create a db4 database


2) demo01

CREATE TABLE demo01( did INT, dname VARCHAR(20), hobby VARCHAR(30) );


  • ( )
CREATE TABLE table name ( -- ( , null, ,) PRIMARY KEY, ); Copy code
  • Modify the table structure to add a primary key index
ALTER TABLE table name ADD PRIMARY KEY (column name)

4) Add a primary key index to the demo1 table


Unique index (UNIQUE)

  • The unique index can guarantee the uniqueness of data records. In fact, in many occasions, people often create unique indexes not to improve access speed, but to avoid data duplication.

1) Syntax

  • Add the primary key index directly when creating the table
CREATE TABLE ( Column name type (length), - Add unique index UNIQUE [ ] ( ) ); Copy code
  • Use the create statement to create: create an index on an existing table
Create UNIQUE index index name on table name (column name (length)) copying the code

Modify the table structure to add an index

The ALTER TABLE table name the ADD UNIQUE (column name) Copy the code

2) Add a unique index to the hobby field

create unique index ind_hobby on demo01(hobby) Copy code

3) Insert data into the table

INSERT INTO demo01 VALUES ( 1 , 'Zhang San' , 'DBJ' ); Given entry # Duplicate 'on DBJ' for Key 'Hobby' # unique index to ensure the uniqueness of the efficiency, the index data also raised the INSERT the INTO demo01 the VALUES ( 2 , 'John Doe' , 'on DBJ' ); duplicated code

Ordinary index (INDEX)

  • The only task of an ordinary index (an index defined by the keyword KEY or INDEX) is to speed up data access. Therefore, you should only create indexes for the data columns that appear most frequently in query conditions (WHERE column=) or sort conditions (ORDERBY column).

1) The syntax format uses the create index statement to create: create an index on an existing table

Create index index name on table names (column names [length]) Copy the code

Modify the table structure to add an index

The ALTER TABLE table name the ADD INDEX index name (column name) Copy the code

2) Add an index to the dname field

# Dname field to add index ALTER Table demo01 the Add index dname_indx (dname); duplicated code

Delete index

  • Since the index will take up a certain amount of disk space, in order to avoid affecting the performance of the database, the indexes that are no longer used should be deleted in time

1) Syntax

The ALTER TABLE table_name the DROP the INDEX index_name; duplicated code

2) Delete the ordinary index named dname_indx in the demo01 table.

The ALTER TABLE demo01 the DROP the INDEX dname_indx; duplicated code

Summary of the advantages and disadvantages of indexes

  • Adding an index should first consider establishing an index on the columns involved in where and order by.
  • Advantages of indexing
    1. Greatly improve query speed
    2. Can significantly reduce the time of grouping and sorting in the query.
  • Disadvantages of indexing
    1. It takes time to create and maintain indexes, and the larger the amount of data, the longer the time.
    2. When the data in the table is added, modified, or deleted, the index should also be maintained at the same time, which reduces the speed of data maintenance

MySQL view

What is a view

  • The view is a kind of virtual table.
  • The view is built on the basis of existing tables, and the tables on which the view is built are called base tables.
  • The statement that provides data content to the view is a SELECT statement. The view can be understood as a stored SELECT statement.
  • View provides users with another form of base table data

The role of the view

  • Can be used for permission control
    • For example, user queries can be run on certain columns, but other columns are not allowed. You can open the view to query specific columns to play a role of permission control.
  • Simplify complex multi-table queries
    • The view itself is a query SQL, we can construct a complex query into a view, and the user can obtain the information they want by querying the view (no need to write complex SQL)
    • The view is mainly to simplify the query of multiple tables

Use of views

Create view

1) Syntax

create view view name [column_list] as select statement; view: represents the view column_list: optional parameter represents an attribute list, specify the name of each attribute of the view, by default, the SELECT same attribute query statement AS : view indicates the operation to be performed select to provide data content to view: Statement copy the code

2) Create a view

#1. First write a query statement # Query the corresponding classification information of all products and products SELECT * FROM products p LEFT JOIN category c ON p. `category_id` = c. `cid` ; #2. Based on the above query statement, create a view CREATE VIEW products_category_view AS SELECT * FROM products p LEFT JOIN category c ON p. `category_id` = c. `cid` ; Copy code

3) Query view, as a read-only table operation can be

The SELECT * the FROM products_category_view; Copy the code

Query by view

1) Demand: query the average price of goods under each category

# Multi-table queries by the SELECT CNAME 'category name' , AVG (p.price) 'average price' the FROM Products's the p- LEFT JOIN category c ON p.category_id = c.cid the GROUP BY c.cname; # View through a query table may be omitted even operate the SELECT CNAME 'Category Name' the AVG (. Price) 'average price' the FROM products_category_view the GROUP BY CNAME; duplicated code
  1. Requirement: Query all the information of the most expensive products under the category of shoes and clothing
#Through table query #1. First find the highest product price under the category of shoes and clothing SELECT MAX (price) maxPrice FROM products p LEFT JOIN category c ON p.category_id = WHERE c.cname = ' Shoes and clothing ' #2. Use the above query as a condition SELECT * FROM products p LEFT JOIN category c ON p.category_id = c.cid WHERE c.cname = 'shoes and clothing ' AND p.price = ( SELECT MAX (price) maxPrice FROM products p LEFT JOIN category c ON p.category_id = c.cid WHERE c.cname = 'shoes and clothing ' ); #Query by view SELECT * FROM products_category_view pcv WHERE pcv.cname = 'shoes and clothing ' AND pcv.price = ( SELECT MAX (price) FROM products_category_view WHERE cname = 'shoes and clothing' ); copy code

The difference between a view and a table

  • The view is built on the basis of the table, the table stores the data in the database, and the view is just a display of data
  • The data in the table cannot be changed through the view (in general, the data in the view is the result of the calculation of the columns in the table, and it is not allowed to update)
  • Delete the view, the table is not affected, and delete the table, the view no longer works

MySQL stored procedure

What is a stored procedure

  • MySQL 5.0 version began to support stored procedures.
  • Stored Procedure (Stored Procedure) is a database object that stores complex programs in the database so that external programs can call them. A stored procedure is a set of SQL statements to complete a specific function. It is compiled and saved in the database. The user can call and execute it by specifying the name of the stored procedure and given parameters (when needed).
  • Simple understanding: a stored procedure is actually a combination of a bunch of SQL statements. Some logic controls were added in the middle.

Advantages and disadvantages of stored procedures

  • advantage:
    • Once the stored procedure is debugged, it can run stably (provided that the business requirements are relatively stable and unchanged)
    • The stored procedure reduces the interaction between the business system and the database, reduces the coupling, and makes the database interaction faster (the application server is not in the same area as the database server)
  • Disadvantages:
    • In the Internet industry, MySQL is widely used. MySQL's stored procedures are weaker than Oracle's, so it is less used, and the demand for the Internet industry is changing rapidly.
    • Try to use it in simple logic. It is very difficult to transplant stored procedures. In a database cluster environment, it is also very difficult to ensure consistent changes in stored procedures between libraries.
    • Ali s code specification also proposes to prohibit the use of stored procedures, and it is indeed troublesome to maintain stored procedures;

How to create a stored procedure

Way 1

1) Data preparation

Create product table and order table

# Commodity table CREATE TABLE goods( gid INT , name VARCHAR ( 20 ), num INT - inventory ); #Order table CREATE TABLE orders( oid INT , gid INT , price INT - order price ); # Add 3 pieces of data to the product table INSERT INTO goods VALUES ( 1 , 'milk tea' , 20 ); INSERT INTO goods VALUES ( 2 , ' ' , 100 ); INSERT INTO goods VALUES ( 3 , ' ' , 25 ) ; Copy code

2) Create a simple stored procedure

Grammatical format

$$ DELIMITER - declaration statement terminator, you can customize the general use of $$ the CREATE PROCEDURE procedure name () - statement stored procedure BEGIN - start writing stored procedures - operations to be performed END $$ - storage process is complete copy Code

Requirements: write stored procedures, query all product data

$$ DELIMITER the CREATE PROCEDURE goods_proc () the BEGIN the SELECT * the FROM Goods; the END $$ copy the code

3) Call a stored procedure

Grammatical format

call a stored procedure name Copy the code
- Call the stored procedure to query all the data in the goods table call goods_proc; copy the code

Way 2

1) IN input parameter: indicates that the caller passes in a value to the stored procedure

The CREATE PROCEDURE stored procedure name ( the IN parameter name parameter type) copying the code

2) Create a stored procedure that receives parameters

Requirement: Receive a product id, delete data according to the id

DELIMITER $$ CREATE PROCEDURE goods_proc02(IN goods_id INT) BEGIN DELETE FROM goods WHERE gid = goods_id ; END $$


# id 2 CALL goods_proc02(2);



SET @ =

2) OUT




# , DELIMITER $$ CREATE PROCEDURE orders_proc( IN o_oid INT , IN o_gid INT , IN o_price INT , OUT out_num INT ) BEGIN - execute insert operation INSERT INTO orders VALUES (o_oid,o_gid,o_price); - set the value of num to 1 SET @out_num = 1 ; -- out_num SELECT @out_num; END $$


# Call the stored procedure to insert data and get the return value CALL orders_proc( 1 ,2,30,@out_num);


What is a trigger

  • Trigger (trigger) is a way MySQL provides programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by a program or started manually. Rather, it is triggered by events, such as when an operation (insert, delete, update) is performed on a table.
  • Simple understanding: when we execute a sql statement, the execution of this sql statement will automatically trigger the execution of other sql statements.

The four elements of trigger creation

  1. Monitoring location (table)
  2. Monitoring events (insert/update/delete)
  3. Trigger time (before/after)
  4. Trigger event (insert/update/delete)

Create trigger

1) Syntax

delimiter $ - Change the end symbol of Mysql from; to $ to avoid execution errors. CREATE TRIGGER Trigger_Name - Trigger name. Trigger name is the only one in a database before/after ( insert/update/delete ) - Trigger timing and monitored event on table_Name - the table where the trigger is located for each row - fixed writing is called row trigger, each row is affected, the trigger event is executed begin - write trigger event end $ between begin and end - End tag copy code

2) Add a piece of data to the product

# Add a piece of data to the product INSERT INTO goods VALUES ( 1 , 'book' , 40 ); copy code

3) Demand: When placing an order, the inventory of the corresponding product should be reduced accordingly, and the inventory should be reduced after the product is sold.

Write trigger

- 1. Modify the end flag DELIMITER $ - 2. Create the trigger CREATE TRIGGER t1 - 3. Specify the timing of the trigger, and the table to be monitored AFTER INSERT ON orders - 4. The fixed writing of the row trigger FOR EACH ROW - 5. The specific execution after the trigger event BEGIN - stock orders +1 -1 UPDATE Goods the SET NUM = NUM -1 the WHERE gid = 1 ; END $ copy the code

4) Query the data in the goods table

5) Add a piece of data to the order table

INSERT INTO orders VALUES ( 1 , 1 , 25 ); copy code

6) The data in the goods table followed by -1

DCL (Data Control Language)

  • MySql uses root users by default, super administrators, who have all permissions. In addition to the root user, we can also define some users with less authority through the DCL language, and assign different authority to manage and maintain the database.

Create user

Grammatical format

The CREATE the USER 'username' @ 'hostname' the IDENTIFIED BY 'password' ; duplicated code
usernameNew user created, login name
CPU nameSpecify the host on which the user can log in. Local users can use localhost.
If you want the user to log in from any remote host, you can use the wildcard%
passwordlogin password

1) Create user admin1, log in to the mysql server only on localhost, with a password of 123456

The CREATE the USER 'admin1' @ 'localhost' the IDENTIFIED BY '123456' ; duplicated code
  • The created user is in the user table in the database named mysql

2) Create the admin2 user to log in to the mysql server on any computer, the password is 123456

The CREATE the USER 'admins2' @ '%' the IDENTIFIED BY '123456' ; duplicated code
  • % Means that the user can log in to the mysql server on any computer

User authorization

  • Created users need to be authorized

Grammatical format

GRANT permission 1 , permission 2. .. ON database name. table name TO'user name' @ 'host name' ; copy code
AuthorityThe permissions granted to the user, such as CREATE, ALTER, SELECT, INSERT, UPDATE, etc.
If you want to grant all permissions, use ALL
ONUsed to specify the permissions for which libraries and tables
TOMeans to give permission to a user

1) Assign the operation authority to the products table in the db4 database to the admin1 user: query

The GRANT the SELECT the ON db4.products the TO 'admin1' @ 'localhost' ; duplicated code

2) Assign all permissions to the admin2 user, for all tables in all databases

The GRANT ALL the ON . * * The TO 'admins2' @ '%' ; duplicated code
  1. Use the admin1 user to log in to the database to test permissions

4) Found that there is only db4 in the database list, and only products in the table

5) Perform query operations

- Query the account table SELECT * FROM products; copy the code

6) Perform the insert operation and found that it is not allowed to be executed, and there is no permission

- to insert data into the products table - not allowed to perform the INSERT the INTO products the VALUES ( 'P010' , 'bird vodka' , 1000 , . 1 , NULL ); duplicated code

View permissions

Grammatical format

SHOW GRANTS the FOR 'username' @ 'hostname' ; duplicated code

1) View root user permissions

- Check the root user privileges SHOW GRANTS the FOR 'root' @ 'localhost' ; Copy the code
  • GRANT ALL PRIVILEGES means all permissions

delete users

Grammatical format

DROP USER'user name' @ 'host name' ; copy code

1) Delete user admin1

- delete user admin1 DROP the USER 'admin1' @ 'localhost' ; Copy the code

Query user

Select the database named mysql and query the user table directly

- Query user SELECT * FROM USER ; copy code

Database backup & restore

  • The application scenario of backup data transmission, data storage and data exchange on the server may cause data failure. Such as unexpected shutdowns or damage to storage media. At this time, if data backup and data recovery methods and measures are not taken, data loss will result, and the resulting loss cannot be compensated and estimated.