How to operate MySQL 01/18 Update SLTechnology News&Howtos

How to operate MySQL

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

Editor to share with you how to operate MySQL, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Create a database luowei

Mysql > CREATE DATABASE luowei

Using the database

Mysql > use luowei

Create table study

Mysql > CREATE TABLE study (ID bigint (20) NOT NULL AUTO_INCREMENT UNIQUE, Name varchar (255) NOT NULL, Age int (10), Gender enum ('Fleming M') DEFAULT' M')

Query table study

Mysql > SELECT * FROM study

View the structure of the table:

Mysql > DESC study

+-+ +

+-+ +

| | ID | bigint (20) | NO | PRI | NULL | auto_increment |

| | Age | int (10) | YES | | NULL |

| | Gender | enum ('Flying Magazine M') | YES | | M | |

+-+ +

Insert data

Mysql > INSERT INTO study (Name) VALUE ('luowei'); / / single insert

Mysql > INSERT INTO study (Name) VALUE ('Qi'), (' Ro'), ('RQ'); / / bulk insert

Mysql > INSERT INTO study SET Name='Hua'; / / insert using SET

Mysql > SELECT * FROM study

+-- +

| | ID | Name | Age | Gender | |

+-- +

| | 1 | luowei | NULL | M | |

| | 2 | Qi | NULL | M | |

| | 3 | Ro | NULL | M | |

| | 4 | RQ | NULL | M | |

| | 5 | Hua | NULL | M | |

+-- +

5 rows in set (0.00 sec)

This is the content of a simple table after inserting data.

Modify the data of the table

Mysql > UPDATE study SET Gender='F' WHERE ID=2; / / change the Gender of ID=2 to F

Mysql > UPDATE study SET Gender='F' WHERE ID IN (4. 5); / / batch processing

Mysql > SELECT * FROM study

+-- +

| | ID | Name | Age | Gender | |

+-- +

| | 1 | luowei | NULL | M | |

| | 2 | Qi | NULL | F | |

| | 3 | Ro | NULL | M | |

| | 4 | RQ | NULL | F | |

| | 5 | Hua | NULL | F | |

+-- +

Delete data from a table

Mysql > DELETE FROM study WHERE ID=5;// deletes the specified row

Mysql > SELECT * FROM study

+-- +

| | ID | Name | Age | Gender | |

+-- +

| | 1 | luowei | NULL | M | |

| | 2 | Qi | NULL | F | |

| | 3 | Ro | NULL | M | |

| | 4 | RQ | NULL | F | |

+-- +

But at this time, if we insert it again, the ID number will be increased from the original deleted number.

Mysql > SELECT LAST_INSERT_ID (); / / shows which one was last inserted

+-+

| | LAST_INSERT_ID () |

+-+

| | 5 |

+-+

Mysql > INSERT INTO study (Name) VALUE ('aQ')

Mysql > SELECT * FROM study

+-- +

| | ID | Name | Age | Gender | |

+-- +

| | 1 | luowei | NULL | M | |

| | 2 | Qi | NULL | F | |

| | 3 | Ro | NULL | M | |

| | 4 | RQ | NULL | F | |

| | 6 | aQ | NULL | M | |

+-- +

See, it doesn't grow in turn, but there is an interval. Now we delete the row whose ID is 6, then insert it and set its ID to 5, and then insert it automatically.

Mysql > DELETE FROM study WHERE ID=6

Mysql > INSERT INTO study SET ID=5,Name='pk'

Mysql > INSERT INTO study (Name) VALUE ('jk')

Mysql > SELECT * FROM study

+-- +

| | ID | Name | Age | Gender | |

+-- +

| | 1 | luowei | NULL | M | |

| | 2 | Qi | NULL | F | |

| | 3 | Ro | NULL | M | |

| | 4 | RQ | NULL | F | |

| | 5 | Competition | NULL | M | |

| | 7 | jk | NULL | M | |

+-- +

We can see that it will not continue to grow automatically as we want. At this time, we can set it up again and let it grow automatically according to the starting point set by us.

Mysql > DELETE FROM study WHERE ID=7

Mysql > ALTER TABLE study AUTO_INCREMENT=5;// sets the starting point for automatic growth

Mysql > INSERT INTO study (Name) VALUE ('jk')

Mysql > INSERT INTO study (Name) VALUE ('OL')

Mysql > SELECT * FROM study

+-- +

| | ID | Name | Age | Gender | |

+-- +

| | 1 | luowei | NULL | M | |

| | 2 | Qi | NULL | F | |

| | 3 | Ro | NULL | M | |

| | 4 | RQ | NULL | F | |

| | 5 | Competition | NULL | M | |

| | 6 | jk | NULL | M | |

| | 7 | OL | NULL | M | |

+-- +

OK again, I guess QQ in setting the QQ number is also in this way, to retain less than 5 digits of the QQ number, all of you are using more than 7 digits (beside the point, hehe).

Modify table structure

If you want to insert a new field, you can use ALTER to modify it

Mysql > DESC study;// structure of the original table

+-+ +

+-+ +

| | ID | bigint (20) | NO | PRI | NULL | auto_increment |

| | Age | int (10) | YES | | NULL |

| | Gender | enum ('Flying Magazine M') | YES | | M | |

+-+ +

Mysql > ALTER TABLE study ADD Work INT

Mysql > DESC study

+-+ +

+-+ +

| | ID | bigint (20) | NO | PRI | NULL | auto_increment |

| | Age | int (10) | YES | | NULL |

| | Gender | enum ('Flying Magazine M') | YES | | M | |

| | Work | int (11) | YES | | NULL |

+-+ +

The last row is inserted by default. If you want to insert the specified row, you can use parameters such as AFTER,FIRST.

Mysql > ALTER TABLE study ADD Master VARCHAR (30) AFTER Gender

Mysql > DESC study

+-+ +

+-+ +

| | ID | bigint (20) | NO | PRI | NULL | auto_increment |

| | Age | int (10) | YES | | NULL |

| | Gender | enum ('Flying Magazine M') | YES | | M | |

| | Work | int (11) | YES | | NULL |

+-+ +

This puts the newly inserted field after the Gender

If we want to change the names of the fields in the original table, for example, I want to change Work to work

Mysql > ALTER TABLE study CHANGE Work work INT;// modify fields in the table

Mysql > DESC study

+-+ +

+-+ +

| | ID | bigint (20) | NO | PRI | NULL | auto_increment |

| | Age | int (10) | YES | | NULL |

| | Gender | enum ('Flying Magazine M') | YES | | M | |

| | work | int (11) | YES | | NULL |

+-+ +

Can achieve the effect.

You can also try to set the properties of the fields in the table by using MODIFY

Mysql > ALTER TABLE study MODIFY work VARCHAR (30) NOT NULL

Mysql > DESC study

+-+ +

+-+ +

| | ID | bigint (20) | NO | PRI | NULL | auto_increment |

| | Age | int (10) | YES | | NULL |

| | Gender | enum ('Flying Magazine M') | YES | | M | |

+-+ +

But if there are no fields in the original work in your table, there will be warning, so you can add data first and then modify it when you do it.

As it grew, I found that the following work field was useless and wanted to delete this property in the study table

Mysql > ALTER TABLE study DROP work

Mysql > DESC study

+-+ +

+-+ +

| | ID | bigint (20) | NO | PRI | NULL | auto_increment |

| | Age | int (10) | YES | | NULL |

| | Gender | enum ('Flying Magazine M') | YES | | M | |

+-+ +

Of course, if we have an index in our table (currently), we can also use DROP to delete the index.

Mysql > SHOW INDEX FROM study;// to view the index of the table

Mysql > ALTER TABLE study ADD INDEX index_name (Name); / / add index

You can also use KEY privately (mysql > ALTER TABLE study ADD KEY index_name (Name);)

Mysql > SHOW INDEX FROM study

+ -+

+ -+

| | study | 0 | ID | 1 | ID | A | 7 | NULL | NULL | | BTREE |

+ -+

Mysql > ALTER TABLE study DROP INDEX index_name;// delete index

Now I think it's a little long, and I want to change a short one to facilitate the following experiment. Of course, in the actual process, we should know the meaning of the name.

Mysql > ALTER TABLE study RENAME TO st;// change the table name from study to st

Mysql > SHOW TABLES

+-+

| | Tables_in_luowei |

+-+

| | st |

+-+

To view the current database, you can use the command

Mysql > SELECT DATABASE ()

Mysql > SHOW TABLES;// displays tables in the current database

Mysql > SELECT * FROM st

+-- +

+-- +

| | 2 | Qi | NULL | F | NULL | |

| | 3 | Ro | NULL | M | NULL | |

| | 4 | RQ | NULL | F | NULL | |

| | 6 | jk | NULL | M | NULL | |

| | 7 | OL | NULL | M | NULL | |

+-- +

I only have one table here. Now I'll modify the data in the table, change the type of Master to INT, and add some data to it.

Mysql > ALTER TABLE st CHANGE MODIFY Master INT

Mysql > UPDATE st SET Master=1 WHERE ID=1

Mysql > UPDATE st SET Master=2 WHERE ID=2

Mysql > UPDATE st SET Master=3 WHERE ID=3

Mysql > UPDATE st SET Master=4 WHERE ID=4

Mysql > UPDATE st SET Master=7 WHERE ID=5

Mysql > UPDATE st SET Master=8 WHERE ID=6

Mysql > UPDATE st SET Master=5 WHERE ID=7

Mysql > SELECT * FROM st

+-- +

+-- +

| | 1 | luowei | NULL | M | 1 | |

| | 2 | Qi | NULL | F | 2 | |

| | 3 | Ro | NULL | M | 3 | |

| | 4 | RQ | NULL | F | 4 | |

| | 5 | Competition | NULL | M | 7 | |

| | 6 | jk | NULL | M | 8 | |

| | 7 | OL | NULL | M | 5 | |

+-- +

The basic situation of the table is now shown above. Of course, in order to record Master, we use the INT tag, but Master is actually a name, so we set up a new table mt to record the corresponding relationship of Master.

Mysql > CREATE TABLE mt (

-> id INT NOT NULL UNIQUE AUTO_INCREMENT

-> name VARCHAR (30) NOT NULL)

Mysql > INSERT INTO mt (name) VALUE ('A')

Insert a few more lines yourself, and I'll omit it here.

Mysql > SELECT * FROM mt

+-+ +

| | id | name |

+-+ +

| | 1 | A |

| | 2 | B |

| | 3 | C |

| | 4 | D | |

| | 5 | E |

| | 6 | F |

+-+ +

All right, now that the two tables are ready, I'll do a query between multiple tables.

Queries between multiple tables can be divided into:

Cross query: Cartesian product, multiplication of two tables

Inner join: symmetrical join, which shows what is found in both tables. If there is no one in one table, it will not be displayed.

External connections: asymmetric connections

Left outer join: LEFT JOIN ON is shown in the left table, and none in the right table is shown as NULL

Right outer connection: everything in the right table of RIGHT JOIN ON is shown, and none in the left table is shown as NULL

Self-join: one column in a table is established in another column in the same table

UNION: join two tables

Next, we will conduct experiments on the above situation.

1. Cross query:

Mysql > SELECT * FROM st,mt

42 rows in set (0.00 sec) / / the data in the middle is omitted, and the result here is the result of multiplying the number of rows of the two tables.

two。 Internal connection:

Mysql > SELECT st.Name,mt.name FROM st,mt WHERE st.Master=mt.id

+-+ +

| | Name | name |

+-+ +

| | luowei | A |

| | Qi | B | |

| | Ro | C | |

| | RQ | D | |

| | OL | E | |

+-+ +

3. Left outer connection:

Mysql > SELECT st.Name,mt.name FROM st LEFT JOIN mt ON st.Master=mt.id

+-+ +

| | Name | name |

+-+ +

| | luowei | A |

| | Qi | B | |

| | Ro | C | |

| | RQ | D | |

| | Competition | NULL |

| | jk | NULL |

| | OL | E | |

+-+ +

At this time, we can see that everything in the left table is displayed, and what is not in the right table is shown as NULL.

4. Right outer connection:

Mysql > SELECT st.Name,mt.name FROM st RIGHT JOIN mt ON st.Master=mt.id

+-+ +

| | Name | name |

+-+ +

| | luowei | A |

| | Qi | B | |

| | Ro | C | |

| | RQ | D | |

| | OL | E | |

| | NULL | F | |

+-+ +

5. Self-connect:

Mysql > SELECT k1.namemore k2.Gender FROM st AS K1 st AS K2 WHERE k1.ID = k2.Master

+-+ +

| | Name | Gender |

+-+ +

| | luowei | M | |

| | Qi | F | |

| | Ro | M | |

| | RQ | F | |

| | OL | M | |

| | Competition | M |

+-+ +

6.UNION

Mysql > SELECT ID AS new_id,Name AS new_name FROM st UNION SELECT id AS new_id,name AS new_name FROM mt

+-+ +

| | new_id | new_name |

+-+ +

| | 1 | luowei |

| | 2 | Qi |

| | 3 | Ro |

| | 4 | RQ |

| | 5 | Competition |

| | 6 | jk |

| | 7 | OL |

| | 1 | A |

| | 2 | B |

| | 3 | C |

| | 4 | D | |

| | 5 | E |

| | 6 | F |

+-+ +

13 rows in set (0. 00 sec) this connects the two tables together.

This is the end of multi-table operation, and then we will talk about the knowledge of views, subqueries, transactions and synchronization in the database.

I'm based on the st table:

Mysql > SELECT * FROM st

+-- +

+-- +

| | 1 | luowei | NULL | M | 1 | |

| | 2 | Qi | NULL | F | 2 | |

| | 3 | Ro | NULL | M | 3 | |

| | 4 | RQ | NULL | F | 4 | |

| | 5 | Competition | NULL | M | 7 | |

| | 6 | jk | NULL | M | 8 | |

| | 7 | OL | NULL | M | 5 | |

+-- +

View: a series of virtual tables that are built to execute queries on a base table

Create a view:

Mysql > CREATE VIEW view AS SELECT ID,Name,Gender FROM st;// create a view that queries to display ID,Name,Gender

Mysql > SELECT * FROM view

+-- +

+-- +

| | 1 | luowei | M | |

| | 2 | Qi | F | |

| | 3 | Ro | M | |

| | 4 | RQ | F | |

| | 5 | Competition | M |

| | 6 | jk | M | |

| | 7 | OL | M | |

+-- +

You can delete a view using the

Mysql > DROP VIEW view

Subquery statement: (in fact, MySQL's subquery does not support very well)

Mysql > SELECT * FROM view WHERE ID > 0

I will borrow the newly established view to query, in fact, this is superfluous, ha ha, just to demonstrate the effect, when let in the work may encounter more problems.

Transaction: transact

A transaction means that several Sql statements are either executed or not executed, which prevents one side from executing and the other side does not, thus creating a virtual illusion. For example, A transfers money to B through the bank. If A has just been transferred out at this time, the power on the B side is cut off (assuming, in fact, the probability of such a thing happening is still quite small, hehe), so after An and B meet, it is not clear. So the transaction is that if both are not realized, that is, A sends successfully and B accepts success, it can be regarded as a real success, so unnecessary losses can be avoided.

Let's talk about the level of transaction isolation:

1. Read unsubmitted READ UNCOMMITED

two。 Read submit READ COMMITED

3. Rereadable REPEATABLE READ default

4. Serialization of SERIABLIZABLE

The isolation level increases successively from 1mer to > 4, and the concurrency decreases in turn.

Mysql > SHOW VARIABLES LIKE 'tx_isolation';// to view the default isolation level used by the system

+-+ +

| | Variable_name | Value |

+-+ +

| | tx_isolation | REPEATABLE-READ |

+-+ +

However, if our database uses the engine MyISAM, transactions are not supported. The engine commands used to view the database are as follows

Mysql > SHOW ENGINES; or use SHOW TABLE STATUS LIKE 'st'; or use SHOW TABLE STATUS; you can view

+-- +

+-- +

| | CSV | NO | CSV storage engine | |

+-- +

The engine shown as DEFAULT is the default, because the database does not support transactions at this time. In order to demonstrate the effect of transactions, I changed the engine of mysql to InnoDB, using the following command:

Mysql > SET SESSION storage_engine = InnoDB

And then build a table based on this engine.

Mysql > CREATE TABLE tt (

-> tid INT UNIQUE NOT NULL AUTO_INCREMENT

-> tname VARCHAR (30) NOT NULL

-> tage INT)

Insert the data as shown in the following figure. Of course, you can add the data in the table as you like.

Mysql > SELECT * FROM tt

+-+

+-+

| | 1 | ser | NULL |

| | 3 | php | NULL |

+-+

View the storage engine of the current table

Mysql > SHOW TABLE STATUS LIKE 'tt'

The result displayed is the engine of InnoDB

1. Set to the level of READ UNCOMMITTED (read uncommitted)

Mysql > SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;// change the isolation level to READ UNCONMMITED and display it as REPEATABLE-READ

Mysql > SHOW VARIABLES LIKE 'tx_isolation'

+-+ +

| | Variable_name | Value |

+-+ +

| | tx_isolation | READ-UNCOMMITTED |

+-+ +

At this level, if two transactions are carried out at the same time, the changes made by either side can be seen by the other. Because we set it in SESSION, session mode, both terminals have to set READ UNCOMMITTED.

So this setting has the best concurrency, but it has the greatest impact.

two。 Set to READ COMMITED (read submit) level

At this level, when any one of the transactions commits, the others in the transaction state can synchronize to the information. For example, the following figure is the one before the commit, which cannot be seen at this time.

If B's transaction commits, take a look at the result, as shown in the following figure:

Although A did not submit it, you can still see the information.

3. Set to REPEATABLE READ (rereadable) level

If it is set to this level, the two simultaneous transactions will not be affected by each other, and all changes can be seen only when both transactions are committed, otherwise, each can only see what they have completed.

I now open two terminals An and B and enter the transaction at the same time.

Mysql > START TRANSACTION;//An enters transaction state

Mysql > START TRANSACTION;//B enters transaction state

Then I insert a row of data into the table in the state of B transaction, and then look at it in both tables, and find that the data is different, as shown in the following figure:

If I commit B's transaction now and look at it on An and B, I find that only B has a newly inserted row, while A still doesn't, as shown in the figure:

If I submit A's now and look at it, the two will be synchronized, as shown in the figure:

This is the effect of the REPEATABLE READ level, that is, the two transactions do not interfere with each other, and the data will be synchronized only when both transactions are committed.

4. Set to SERIABLIZABLE level

At this time, only when the execution of the current transaction is completed and committed, the next transaction will start, and more stringent restrictions will be made, so such a level limit is generally not made.

Synchronization: (clock)

Solve the synchronization problem through the locking mechanism

Lock:

Shared lock (read lock):

Exclusive lock (write lock):

Write locks take precedence over read locks

Lock level:

Table lock, row lock, page lock (memory space)

Implementation of the lock:

At the server level; only table locks can be implemented

Storage engine level: row locks can be implemented

LOCK TABLES tab_name READ; lock

UNLOCK TABLES; releases locks

(note: if in InnoDB, the definition time does not take effect, only the real transaction begins

The defined lock will only take effect)

Reduce the granularity, can better support concurrency, so that the smaller the lock particles, similar to row locks, locks

The scope is smaller.

User management of MYSQL:

Create a user

CREATE USER

Delete user

DROP USER

Authorization

GRANT PRIV_LIST ON db.table TO [IDENTIFIED BY 'redhat']

Take back the authority

REVOKE priv_list ON db.table FROM

WITH GRANT OPTION also authorized him at the same time.

Set the resources it uses:

WITH MAX_QUERIES_PER_HOUT N; can only do N queries per hour

WITH MAX_UPDATES_PER_HOUT N can only update N times per hour

WITH MAX_CONNECTIONS_PER_HOUT N can only make N connections per hour

WITH MAX_USER_CONNECTIONS N maximum simultaneous connections per user

FLUSH USER_RESOURCES

GRANT SELECT (name) ON db.table TO; field-level permissions

Permissions to execute stored procedures

GRANT EXECUTE ON XXXX TO

Forget MySQL's password:

Method 1. Reinstall

Method 2.

Vim / etc/init.d/d

Stop the MySQL service first

# service mysqld stop

Find the location of the binary file of mysqld. I'm right here under / user/local/mysql/.

# / user/local/mysql/bin/mysqld_safe-- skip-grant-tables-- skip-networking & safe mode for background operation

Then connect to the database

# mysql

> use mysql

> UPDATE user SET Password=PASSWORD ('redhat') WHERE User='root' AND Host='localhost'

> UPDATE user SET Password=PASSWORD ('redhat') WHERE User='root' AND Host='127.0.0.1'

# killall mysqqld

# service mysqld start

# mysql-uroot-p login

The above is all the contents of this article "how to operate MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.