Mysql database advanced usage of views, transactions, indexes, self-connections, user management example analysis

This article uses examples to describe advanced usage of MySQL database: views, transactions, indexes, self-connections, and user management. Share with you for your reference, the details are as follows:

view

A view is a reference to several basic tables, a virtual table that only queries the field types and constraints of the statement execution results, and does not store specific data (if the basic table data changes, the view will also change accordingly). It facilitates operations, especially query operations, reduces complex SQL statements, and enhances readability.

1.————Create a view:

create view view name (usually starts with v_) as query statement;

2.————View View:

select * from view name;

3.————Delete view:

drop view view name;

Transactions

1. Features: Four major features ACID

(1) Atomicity: A transaction must be considered as an indivisible minimum unit of work. All operations in the entire transaction must either be committed successfully or fail and be rolled back. It is impossible for a transaction to execute only part of the operations.

(2) Consistency: The database always transitions from one consistent state to another consistent state.

(3) Isolation: Changes made by a transaction are not visible to other transactions until they are finally committed.

(4) Durability: Once a transaction is committed, the changes it makes will be permanently saved to the database.

2. Use:

(1) Open a transaction: After opening a transaction and executing a modification command, the changes will be saved in the local cache instead of in the physical table.
begin; or start transaction;

(2) Rollback transaction: Abandon the changed data in the cache, indicating that the transaction execution failed, and return to the state at the beginning of the transaction.
rollback;

(3) Commit transaction: Maintain data changes in the cache to the physical table.
commit;

index

An index is a special file (the index on an InnoDB table is a component of the table space). It contains the location information of all records in the table. In more general terms, a database index is like the table of contents at the front of a book, which can speed up database queries.

1. Use:

(1)————View index:

show index from table name;

(2)————Create index:

create index index name on table name (field name (length));

(3)————Delete index:

drop index index name on table name;

2. Verify performance:

(1) Enable runtime detection:

set profiling=1;

(2) Find the 100,000th piece of data:

select * from test_index where title='ha-99999';

(3) Create an index for the table:

create index title_index on test_index(title(10));

(4) Find the 100,000th piece of data:

select * from test_index where title='ha-99999';

(5) Check the execution time:

show profiles;

Summary: Indexes can greatly improve query speed, but the time to create an index is much slower than the query speed. Therefore, it is not suitable to use indexes when the data table is frequently updated. It is recommended to use indexes when the data table is large and has not been updated for a long time. It is not recommended in other situations.

User Management

1.————View users:

select host,user,authentication_string from user;

————View user permissions:

show grants for username@host;

2.————Create users and grant permissions:'

grant permission name on database name.* to 'user name'@'host' identified by 'password';

3.————Modify permissions:

grant permission name on database name.* to 'user name'@'host' where grant option;

Refresh permissions:

flush privileges;

4.————Change password:

update user set authentication_string=password('new password') where user='user name';

5.————Delete user:
uninstall:

drop drop user 'username'@'host';

Manual Removal:

delete from user where user='username';

Refresh permissions:

flush privileges;

Self-join

Aggregate data from multiple similar tables into one table and connect them to themselves for querying.

1.————Usage:

select * from table name as alias 1 inner join table name as alias 2 on condition;

2.————Case:

aid is the number, pid is the number to which it belongs, query all cities in Shanxi Province:

select city.* from areas as cityinner join areas as province 
on city.pid=province.aidwhere province.atitle='Shanxi Province'; 

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.