26 MySQL indexes, stored procedures, and triggers

1 MySQL index

In a database table, creating indexes for fields can greatly improve the query speed. Common indexes in MySQL can be divided into the following types:

MySql stores all indexes in a table in the same index file. If you add, delete, or change the database, MySql automatically updates the index.

The primary key index

A table can have no primary key, but only one primary key, and cannot gradually be null. The syntax for adding a primary key index to a table is:

Add primary key indexes directly when creating a table

create tableTable name (field name Field typeprimary key,
);
Copy the code

Add a primary key index when modifying the table structure

alter table add primaryKey (column name)Copy the code

The only index

A unique index ensures the uniqueness of data records. In real development, unique indexes are created not only to speed up queries, but also to avoid data duplication.

Syntactic format for a unique index

The first way is to add a unique index when creating a table
create table(Column name type (length),unique[index name] (column name));The second way is to add a unique index to the created table
create uniqueThe index index nameonTable name (column name (length));The third way to modify the table structure is to add a unique index
alter tableThe name of the tableadd unique(column name)Copy the code

Normal index

The only purpose of a normal index (one defined by the key and index keywords) is to speed up data access. Therefore, columns of data that appear frequently in query or sort conditions should be added to a normal index. The syntax for a normal index is:

First, create an index on an existing table
createThe index index nameonTable name (column name (length));Alter table structure add index
alter tableThe name of the tableaddIndex Index name (column name)Copy the code

Remove the index

Because indexes occupy extra storage space, to avoid affecting database performance, you should delete indexes that are no longer used. The syntax for deleting indexes is:

alter tableThe name of the tabledropIndex Indicates the index name.Copy the code

Advantages and disadvantages of indexes:

• Advantages: improved query speed; Significantly reduced query grouping and sorting time.
• Disadvantages: Creating and maintaining indexes takes time, proportional to the size of the data; When adding, deleting, or modifying data in a table, indexes need to be maintained, which slows down data maintenance.

2 the MySQL view

A view is a virtual table that is built on the basis of existing tables. The tables that a view relies on are called base tables. The statements that provide data content to a view are SELECT statements.

The functions of views are as follows: For permission control, users can only view specific columns of data, and users cannot view other columns of data. Simplify complex multi-table queries. Since the view itself is an SQL query statement, we can build a complex query into a view, so that users can query views to get the corresponding data without writing complex query statements.

Create a view

Syntax format:

create viewView name [Optional]as selectStatements;Copy the code

Create view to query all commodities and their corresponding classification information, specific code:

create view products_category_view as select * from products p left join category c on c.cid = p.category_id;
Query views as if they were a table
select * from products_category_view;
Copy the code

Query all information about the most expensive items in the footwear category

select * from products_category_view pcv where pcv.cname='shoes clothes'and pcv.price=(select max(price) from products_category_view where cname='shoes clothes');
Copy the code

The difference between a view and a table

• A view is based on a table, which stores data in a database, and a view is just a presentation of the data

• You can’t change the data in a table through a view

• If you delete a view, the table is unaffected. If you delete a table, the view is unaffected

3 MySQL stored procedures

A stored procedure is a database object that stores complex programs in a database for external programs to call. A stored procedure is a set of SQL statements that accomplish a specific function. It is created by compilation and stored in a database. Users can invoke and execute the stored procedure by specifying its name and parameters. In short, a stored procedure is a collection of SQL statements, with some logic in between.

Advantages and disadvantages of using stored procedures:

• Advantages: Once debugged, stored procedures can run stably, which requires business requirements to be stable; The stored procedure reduces the interaction between the business system and the database, reduces the coupling, and makes the database interaction faster.
• Disadvantages: Business requirements change frequently during actual development, so stored procedures are not recommended; Stored procedure portability is poor; Maintenance headaches for stored procedures

How to create a stored procedure with no parameters:

delimiter $$ -- Declaration statement terminator, can be customized, usually use $$
create procedureProcess of ()Declare stored procedures
begin      Start writing stored procedures
           -- The operation to be performed
end $$     -- The stored procedure ends
Call the stored procedure
callStored procedure nameCopy the code

Write a stored procedure, query all commodity data, and call the stored procedure

delimiter $$
create procedure good_pro()
begin
    select * from products;

end $$;
call good_pro;
Copy the code

Write a stored procedure with parameters

create procedureStored procedure name (inParameter name Parameter type);Copy the code

Write a stored procedure that takes parameters, accepts the item ID, and deletes data based on the ID

delimiter $$
create procedure good_delete(in id varchar(32))
begin
    delete from products where pid=id;
end $$;
call good_delete('p009');
Copy the code

Write a stored procedure that takes parameters and returns values

Variable assignment and OUT output parameters, indicating that the stored procedure passes values to the caller

set@ the variable name=value-- Variable assignment
outVariable name data typeThe stored procedure passes values to the caller
Copy the code

Write a stored procedure with arguments and a return value, returning 1 to indicate successful insertion

delimiter $$
create procedure pro_in(in id varchar(32),in name varchar(50),in price int.in flag varchar(2),in cate varchar(32),out res int)
begin
    insert into products values (id,name,price,flag,cate);
    set @res=1;
    select @res;

end $$;
call pro_in('p009'.Apple Computer.10000.'1'.'c001'.@res);
Copy the code

MySQL > alter database

Triggers are special stored procedures related to table events. Their execution is triggered by events rather than program calls or manual calls. When we execute an SQL statement, the execution of this SQL statement automatically triggers the execution of other SQL statements.

Four elements of trigger creation:

• Monitoring Site (Table)
• Monitor events (INSERT /update/delete)
• Trigger time (before/after)
• Trigger events (INSERT /update/delete)

Create the syntactic format of the trigger

delimiter $$
create triggerTrigger name before/after(insert/update/delete)
onThe name of the tablefor each row A fixed term, called a row trigger, is executed for each row affected
begin
-- Write the trigger event here
end $$;
Copy the code

Write trigger: when the next order is placed, the inventory of the corresponding goods should be reduced accordingly, and the inventory of the goods should be reduced after the sale

delimiter $$
create trigger  t1 after insert on orders for each row
    begin
        update goods set num=num- 1 where gid=1;

    end $$;
insert into orders values (1.1.20);

Copy the code

DCL data control language

By using the DCL language, you can define some users with small permissions and assign different permissions to manage and maintain the database.

Create a user with the following syntax:

create user 'Username'@'Host name' identified by 'password'
Copy the code

The host name specifies the host on which the user can log in to the database. Local users write localhost, and wildcard % when they want to log in from any host

The instance

create user 'admin'@'localhost' identified by '123456';
Copy the code

The user authorization

After creating a user, you can authorize the user

grantpermissions1And permissions2..onDatabase name. Table nameto 'Username'@'Host name' -- Permissions include: CREATE ALTER SELECT INSERT UPDATE
-- on specifies the database and table to which the permission is assigned
Copy the code

Assign admin the query operation permission on the PRODUCTS table in db2

grant select on db2.products to admin@'localhost';
Copy the code

Check the permissions

View user permissions in the following syntax:

show grants for 'Username'@'Host name';
Copy the code

View the permission of user root

show grants for 'root'@'localhost';
Copy the code

Delete user

Delete the user.

drop user 'Username'@'Host name'
Copy the code

Deleting the admin user

drop user 'admin'@'localhost';
Copy the code

Querying the database

Select * from mysql; select * from mysql

select * from user;
Copy the code