MySQL index

What is an index

  • In database tables, indexing fields can greatly speed up queries. By making good use of these indexes, you can make MySQL queries and run more efficiently.
  • If properly designed and indexed MySQL is a Lamborghini, then MySQL without design and indexed MySQL is a human tricycle. Take the Chinese dictionary’s catalogue page (index) as an example, we can quickly find the word we need according to the catalogue (index) sorted by pinyin, strokes, radicals, etc

Common index categories

The index name instructions
Primary key index A primary key is a unique index that can have only one primary key per table and is used to identify each record in the data table
Unique index A unique index means that all values of an indexed column can occur only once and must be unique
Normal index The most common type of index is to speed up access to data
  • MySql stores all indexes of a table in the same index file. If you add, delete, or change data in the table, MySql automatically updates the index.

PRIMARY KEY index

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

1) Create db4 database

CREATE DATABASE db4 CHARACTER SET utf8;
Copy the code

2) Create table DEMO01

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

3) Syntax format

  • Add primary key index when creating table (most commonly used)
CREATE TABLEThe name of the table (-- Add a primary key (a unique index, not null, not duplicate)Field name type PRIMARYKEY,);Copy the code
  • Modify table structure to add primary key index
ALTER TABLEThe name of the tableADD PRIMARY KEY(column name)Copy the code

4) Add primary key index to demo1 table

ALTER TABLE demo01 ADD PRIMARY KEY (did);
Copy the code

UNIQUE index

  • Features: All values in an indexed column can occur only once and must be unique.
  • A unique index ensures the uniqueness of data records. In fact, in many cases, unique indexes are created not to speed up access, but simply to avoid duplication of data.

1) Syntax format

  • Add primary key indexes directly when creating a table
CREATE TABLETable name (column name type (length),Add unique index
    UNIQUE[index name] (column name));Copy the code
  • Create using the CREATE statement: Create an index on an existing table
create unique indexIndex nameonTable name (column name (length))Copy the code

Modify the table structure to add indexes

ALTER TABLEThe name of the tableADD UNIQUE(column name)Copy the code

2) Add a unique index to the Hobby field

create unique index ind_hobby on demo01(hobby)
Copy the code

3) Insert data into table

INSERT INTO demo01 VALUES(1.'Joe'.'DBJ');

Duplicate entry 'DBJ' for key 'hobby'
Unique index ensures data uniqueness and improves index efficiency
INSERT INTO demo01 VALUES(2.'bill'.'DBJ');
Copy the code

Normal INDEX

  • The only job of a plain INDEX (an INDEX defined by the KEY or INDEX keyword) is to speed up access to data. Therefore, indexes should be created only for those data columns that appear most frequently in the query condition (WHERE column=) or the sort condition (ORDERBY column).

1) Create index on existing table with create index statement

create indexIndex nameonTable name (column name [length])Copy the code

Modify the table structure to add indexes

ALTER TABLEThe name of the tableADD INDEXIndex name (column name)Copy the code

2) add index to dname

Add index to dname
alter table demo01 add index dname_indx(dname);
Copy the code

Remove the index

  • Indexes occupy a certain amount of disk space. Therefore, you should delete indexes that are no longer used in a timely manner to avoid affecting database performance

1) Syntax format

ALTER TABLE table_name DROP INDEX index_name;
Copy the code

Alter table DEMO01 drop index dname_indx;

ALTER TABLE demo01 DROP INDEX dname_indx;
Copy the code

A summary of the pros and cons of indexes

  • To add an index, you should first consider indexing the columns involved in WHERE and Order BY.
  • Advantages of indexes
    1. Greatly improve query speed
    2. Can significantly reduce grouping and sorting time in queries.
  • Disadvantages of indexes
    1. Creating and maintaining indexes takes time, and the larger the volume of data, the longer it takes
    2. When the data in the table is added, modified, or deleted, the index must be maintained at the same time, which reduces the data maintenance speed

MySQL view

What is a view

  • A view is a virtual table.
  • A view builds on existing tables, which are called base tables.
  • The statements that provide the data content to the view are SELECT statements, and the view can be thought of as a stored SELECT statement.
  • Views provide the user with another representation of the base table data

The role of views

  • This parameter can be used for permission control
    • For example, you can run user queries for certain columns and not for other columns. You can open a view to query specific columns for permission control
  • Simplify complex multi-table queries
    • The view itself is a query SQL, we can build a complex query into a view, users can query the view to get the information they want (no need to write complex SQL)
    • The main purpose of views is to simplify multi-table queries

Use of views

Create a view

1) Syntax format

create viewThe view name [column_list]as selectStatements; Column_list: This parameter is optional. Column_list: indicates the list of attributes and specifies the names of each attribute in the viewSELECTThe attributes in the query are the sameas: Indicates the action to be performed by the viewselectStatement: Provide data content to the viewCopy the code

2) Create a view

#1. Write a query statement first
# query all commodities and their corresponding classification information
SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`;

#2. Create a view based on the query above
CREATE VIEW products_category_view
AS SELECT * FROM products p LEFT JOIN category c ON p.`category_id` = c.`cid`;
Copy the code

3) Query the view as if it were a read-only table

SELECT * FROM products_category_view;
Copy the code

Query information through views

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

Select * from multiple tables
SELECT
    cname 'Category name'.AVG(p.price) 'Average price'
FROM products p LEFT JOIN category c ON p.category_id = c.cid
GROUP BY c.cname;

SQL > select * from view
SELECT 
    cname 'Category name'
    AVG(price) 'Average price'
FROM products_category_view GROUP BY cname;
Copy the code
  1. Need: Query all information about the most expensive item in the footwear category
Select * from table_name
#1. First, find the highest commodity price under the category of shoes and clothing
SELECT
    MAX(price) maxPrice
FROM
products p LEFT JOIN category c ON p.category_id = c.id
WHERE c.cname = 'shoes clothes'

#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 clothes' AND p.price = 
(SELECT
    MAX(price) maxPrice
FROM
products p LEFT JOIN category c ON p.category_id = c.cid
WHERE c.cname = 'shoes clothes'	);

Query by view
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 views and tables

  • A view is based on a table, which stores data in a database, and a view is just a presentation of the data
  • The data in the table cannot be changed by using a view (in general, the data in the view is the result of the calculation of the columns in the table and cannot be updated).
  • Delete a view and the table is unaffected. Delete a table and the view is no longer in effect

MySQL stored procedure

What is a stored procedure

  • MySQL version 5.0 is starting to support stored procedures.
  • 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 designed to perform a specific function. It is created by compilation and stored in a database. The user can invoke the execution of the stored procedure by specifying its name and giving parameters (if necessary).
  • Simple: A stored procedure is simply a collection of SQL statements. Some logic is added in the middle.

Advantages and disadvantages of stored procedures

  • Advantages:
    • 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 the database interaction is faster (application server, and the database server are not in the same region)
  • Disadvantages:
    • In the Internet industry, MySQL is widely used. Compared with Oracle, MySQL’s stored procedure is relatively weak, so it is seldom used. Besides, the demand of the Internet industry changes rapidly
    • Try to use in simple logic, stored procedure migration is very difficult, database cluster environment, to ensure the consistency of stored procedure changes between libraries is also very difficult.
    • Ali’s code specification also forbids the use of stored procedures, which are really troublesome to maintain;

How stored procedures are created

Method 1

1) Data preparation

Create goods tables and order tables

Table # goods
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 items of data to the item list
INSERT INTO goods VALUES(1.'tea'.20); 
INSERT INTO goods VALUES(2.'green'.100); 
INSERT INTO goods VALUES(3.'tea'.25);
Copy the code

2) Create simple stored procedures

Syntax format

DELIMITER $$ -- Declaration statement terminator, can be customized general use $$
CREATE PROCEDUREProcedure Name ()Declare stored procedures
BEGIN Start writing stored procedures
    -- The operation to be performed
END $$ -- The stored procedure ends
Copy the code

Requirement: Write stored procedures to query all commodity data

DELIMITER $$
CREATE PROCEDURE goods_proc()
BEGIN
    SELECT * FROM goods;
END $$	
Copy the code

3) Call the stored procedure

Syntax format

callStored procedure nameCopy the code
Call stored procedure to query goods table for all data
call goods_proc;
Copy the code

Way 2

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

CREATE PROCEDUREStored procedure name (INParameter Name Parameter Type)Copy the code

2) Create a stored procedure that receives parameters

Requirement: Receive an item ID and delete data according to the ID

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

3) Call the stored procedure to pass parameters

Select * from product where id = 2
CALL goods_proc02(2);
Copy the code

Methods 3

1) Variable assignment

SET@ variable name = valueCopy the code

2) OUT Output parameter: indicates that the stored procedure passes a value to the caller

OUT Variable name data typeCopy the code

3) Create the stored procedure

Requirement: Insert a piece of data into the order table, return 1, indicating successful insert

Create a stored procedure that receives the parameter insert data and returns the number of rows affected
DELIMITER $$
CREATE PROCEDURE orders_proc(IN o_oid INT , IN o_gid INT ,IN o_price INT.OUT out_num INT)
BEGIN
    -- Perform the insert operation
    INSERT INTO orders VALUES(o_oid,o_gid,o_price);
    Set num to 1
    SET @out_num = 1;
    -- Returns the value of out_num
    SELECT @out_num;
END $$    
Copy the code

4) Call the stored procedure

Call the stored procedure to insert the data and get the return value
CALL orders_proc(1.2.30,@out_num);
Copy the code

MySQL trigger

What is a trigger

  • Trigger is a method provided by MySQL to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by the program or manually started, but triggered by events, such as when an operation (insert, delete, Update) activates its execution.
  • 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

  1. Monitoring Site (Table)
  2. Monitor events (INSERT /update/delete)
  3. Trigger time (before/after)
  4. Trigger events (INSERT /update/delete)

Create trigger

1) Syntax format

delimiter $ Mysql > alter table Mysql > alter table Mysql Change to $to avoid execution errors
CREATE TRIGGER Trigger_Name -- Trigger name, which is unique in a database
before/after(insert/update/delete)-- Triggering timing and monitoring events
on table_Name -- The table where the trigger resides
for each row The fixed term is called a row trigger. For each row affected, the triggering event is executed
begin
    -- Write triggering events between begin and end
end
$ -- Closing tag
Copy the code

2) Add a piece of data to the product

Add a piece of data to the item
INSERT INTO goods VALUES(1.'book'.40);
Copy the code

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

Write trigger

-- 1. Modify the end identifier
DELIMITER $
-- 2. Create trigger
CREATE TRIGGER t1
-- 3. Specify when to trigger and which table to listen on
AFTER INSERT ON orders
-- 4. Line trigger fixed writing
FOR EACH ROW
-- 5. The event to be executed after the event is triggered
BEGIN
    -- Order +1 Inventory -1
    UPDATE goods SET num = num - 1 WHERE gid = 1;
END$    
Copy the code

4) Query data in goods table

5) Add a data entry to the order table

INSERT INTO orders VALUES(1.1.25);
Copy the code

6) The data in the goods table is then -1

DCL(Data Control Language)

  • MySql default user root, super administrator, has all permissions. In addition to the root user, we can also define some users with low privileges through the DCL language, and assign different permissions to manage and maintain the database.

Create a user

Syntax format

CREATE USER 'Username'@'Host name' IDENTIFIED BY 'password';
Copy the code
parameter instructions
The user name Create a new user, login name
The host name Specify the host on which the user can log in. Localhost is used for local users

If you want the user to log in from any remote host, you can use the wildcard %
password The login password

1) create admin1 user and log in to mysql server only at localhost with password 123456

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

The admin2 user can log in to the mysql server from any computer with the password 123456

CREATE USER 'admin2'@The '%' IDENTIFIED BY '123456';
Copy the code
  • % indicates that users can log in to the mysql server from any computer

The user authorization

  • The created user needs to be authorized

Syntax format

GRANTpermissions1And permissions2..ONDatabase name. Table nameTO 'Username'@'Host name';
Copy the code
parameter instructions
permissions Permissions granted to users, such as CREATE, ALTER, SELECT, INSERT, UPDATE, and so on.

Use ALL if you want to grant ALL permissions
ON Used to specify which libraries and tables the permissions are for
TO Indicates that the permission is granted to a user

Select * from db4; select * from db4

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

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

GRANT ALL ON*. *TO 'admin2'@The '%';
Copy the code
  1. Log in to the database as admin1 user to test permissions

Select * from db4; select * from products

5) Perform query operations

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

6) Insert operation is not allowed

Insert data into products table
-- Execution is not allowed
INSERT INTO products VALUES('p010'.Bird Vodka.1000.1.NULL);
Copy the code

Check the permissions

Syntax format

SHOW GRANTS FOR 'Username'@'Host name';
Copy the code

1) Check the permissions of the root user

-- Check the permission of user root
SHOW GRANTS FOR 'root'@'localhost';
Copy the code
  • GRANT ALL PRIVILEGES indicates ALL PRIVILEGES

Delete user

Syntax format

DROP USER 'Username'@'Host name';
Copy the code

1) Delete admin1 user

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

Query the user

Select * from mysql; select * from mysql

Select * from user;
SELECT * FROM USER;
Copy the code

Database backup & Restore

  • Backup application scenarios When data is transmitted, stored, and exchanged on servers, data faults may occur. Such as unexpected downtime or storage media damage. At this time, if the means and measures of data backup and data recovery are not taken, it will lead to the loss of data, and the loss caused is irreparable and immeasurable.