Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to use views, transactions and indexes in the Mysql database, which may not be well understood by many people. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

View is a reference to several basic tables, a virtual table, which only queries the field types and constraints of the execution results of the statement, and does not store specific data (when the basic table data has changed, the view will also change), it is convenient for operation, especially query operations, reduce complex SQL statements and enhance readability.

1. Murray-create a view:

Create view view name (usually starts with v _) as query statement

2.Murray-View the view:

Select * from View name

3. Murray: delete the view:

Drop view View name

Business

1. Properties: four characteristics ACID

(1) Atomicity: a transaction must be regarded as an indivisible minimum unit of work, and all operations in the whole transaction are either committed successfully or all failed to rollback. For a transaction, it is impossible to perform only part of the operations.

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

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

(4) Durability: once a transaction commits, its changes are permanently saved to the database.

two。 Use:

(1) start the transaction: execute the modify command after opening the transaction, and the change will be protected to the local cache, not in the physical table.

Begin; or start transaction

(2) Roll back the transaction: discard the data changed in the cache, indicating that the transaction failed to execute and return to the state where the transaction started.

Rollback

(3) commit transaction: maintain the data changes in the cache to the physical table.

Commit

Indexes

An index is a special file (the index on the InnoDB data table is a part of the table space), which contains the location information of all the records in the data table. More generally, the database index is like the directory in front of a book, which can speed up the query of the database.

1. Use:

(1)-View the index:

Show index from table name

(2)-create an index:

Create index index name on table name (field name (length))

(3)-delete the index:

Drop index index name on table name

two。 Verify performance:

(1) enable run time detection:

Set profiling=1

(2) search for the 100th item of data:

Select * from test_index where title='ha-99999'

(3) Index the table:

Create index title_index on test_index (title (10))

(4) search for the 100th item of data:

Select * from test_index where title='ha-99999'

(5) check the execution time:

Show profiles

Summary: the index can greatly improve the query speed, but the time to create the index is much slower than the query speed, so when the data table is updated frequently, it is not suitable to use the index. When the data table data is very large and is not updated for a long time, it is recommended to use the index, but not recommended in other cases.

User management

1. Murray-View users:

Select host,user,authentication_string from user

-View user permissions:

Show grants for user name @ host

2. Murray-create users and grant permissions:'

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

3. Murray-modify permissions:

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

Refresh permissions:

Flush privileges

4. Murray-change the password:

Update user set authentication_string=password ('new password') where user=' username'

5.Mutual Mutual-Delete users:

Uninstall:

Drop drop user 'username' @ 'host'

Manually delete:

Delete from user where user=' user name'

Refresh permissions:

Flush privileges

Self-connection

Aggregate the data from multiple similar tables into one table and connect yourself to yourself for query.

1. Mui-usage:

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

2. Murray-case study:

Aid is the number and pid is the number to which it belongs

Inquire about all cities in Shanxi Province:

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

After reading the above, do you have any further understanding of how to use views, transactions, and indexes in Mysql databases? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.