MySQL common statements - Moment For Technology

Database operations

Connecting to a Database

C:\Users\21952>mysql -u root -p Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement.Copy the code

Creating a database

CREATE DATABASEDatabase name;Copy the code

Deleting a Database

DROP DATABASEDatabase name;Copy the code

Select database

useDatabase name;Copy the code

Viewing all databases

mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | blog | | information_schema | | mydb | | mysql | | notes | | notesblog | | performance_schema | | sys | | test | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 9 rows in the set (0.00 SEC)Copy the code

Data table operation

Create table

CREATE TABLEtable_name( column_name column_type, ... ) ;Copy the code

The user table will be created in the following example

CREATE TABLE IF NOT EXISTS `user`(
    `uid` INT UNSIGNED AUTO_INCREMENT,
    `uname` VARCHAR(20) NOT NULL.`age` INT.`sex` VARCHAR(2),
    PRIMARY KEY(`uid`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

Resolution:

IF NOT EXISTS indicates that the user table is created only when it does NOT exist
An UNSIGNED number in UNSIGNED storage
NOT NULL indicates that the field cannot be empty; otherwise, an error will be reported
AUTO_INCREMENT Defines an attribute that is incremented as a primary key. The value is automatically incremented by 1.
The PRIMARY KEY keyword is used to define the PRIMARY KEY of a column. You can define primary keys with multiple columns separated by commas.
ENGINE sets the storage ENGINE, and CHARSET sets the encoding.

Delete table

DROP TABLE [IF EXISTS] table_name;
Copy the code

Alter table structure

Modify the name of the table

There are two ways to change a table name:

ALTER TABLE old_table_name RENAME new_table_name;
Copy the code

RENAME TABLE old_table_name TO new_table_name;
Copy the code

The following is an example:

mysql> ALTER TABLE user RENAME user1;
Query OK, 0 rows affected (0.28 sec)
Copy the code

mysql> RENAME TABLE user1 TO user;
Query OK, 0 rows affected (0.51 sec)
Copy the code

Modify the column name and column structure of a column in a table

ALTER TABLE table_name CHANGE old_column_name new_column_name new_ype;
Copy the code

Alter table user uname = name and VARCHAR(20) = VARCHAR(21)

ALTER TABLE user CHANGE uname name VARCHAR(21) NOT NULL;
Copy the code

Modify column structure

ALTER TABLE table_name MODIFY column_name new_type;
Copy the code

Example:

mysql> ALTER TABLE user MODIFY uname VARCHAR(20);
Query OK, 3 rows affected (1.28 sec)
Records: 3  Duplicates: 0  Warnings: 0
Copy the code

Increase the column

mysql> ALTER TABLE user ADD address VARCHAR(20);
Query OK, 0 rows affected (0.63 sec)
Records: 0  Duplicates: 0  Warnings: 0
Copy the code

Delete the column

mysql> ALTER TABLE user DROP address;
Query OK, 0 rows affected (0.93 sec)
Records: 0  Duplicates: 0  Warnings: 0
Copy the code

View table structure

DESC table_name;
Copy the code

mysql> DESC user; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | uid | int unsigned | NO | PRI | NULL | auto_increment  | | uname | varchar(21) | NO | | NULL | | | age | int | YES | | NULL | | | sex | varchar(2) | YES | | NULL | | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 4 rows in the set (0.00 SEC)Copy the code

View all tables in the current database

mysql> SHOW TABLES; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Tables_in_test | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | stu | | user | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 2 rows in the set (0.00 SEC)Copy the code

Data manipulation

Insert data

INSERT INTOtable_name (field1, field2,... fieldN)VALUES(value1, value2,... valueN);Copy the code

We can specify fields to partially insert before VALUES

When we insert all fields we can omit (field1, field2… fieldN)

Insert all fields :(to specify primary key)

Mysql > INSERT INTO user VALUES (1, 'XGC ', 21,' male '); Query OK, 1 row affected (0.08 SEC)Copy the code

Alter table AUTO_INCREMENT increment increment increment increment increment increment increment increment increment increment

mysql> INSERT INTO user (uname, age) VALUES ('zhangsan', 22);
Query OK, 1 row affected (0.08 sec)
Copy the code

Query data

SELECT column_name [AS result_name],column_name FROM table_name [WHRER Clause] [LIMIT N] [OFFSET M]
Copy the code

[] is optional

AS result_name Specifies a specific name for the column name corresponding to the query result
The WHERE clause is followed by the selection criteria
LIMIT to set the number of records to return
OFFSET Specifies the OFFSET of the query data. The default value is 0

When we want to query all fields, we can use * instead of column names

mysql> SELECT * FROM user; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + uid | | uname | age | sex | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- + | 1 | XGC 21 male | | | | | 6 Zhangsan 22 | NULL | | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + 2 rows in the set (0.00 SEC)Copy the code

Query partial fields

mysql> SELECT uname AS name,age FROM user;
+----------+------+
| name     | age  |
+----------+------+
| xgc      |   21 |
| zhangsan |   22 |
+----------+------+
2 rows in set (0.00 sec)
Copy the code

The WHERE clause

mysql> SELECT * FROM user WHERE uid=6; +-----+----------+------+------+ | uid | uname | age | sex | +-----+----------+------+------+ | 6 | zhangsan | 22 | NULL | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

LIMIT clause

LIMIT can be followed by one or two parameters

If there is only one parameter, it indicates the number of records to be queried

mysql> SELECT * FROM user LIMIT 1; + + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + uid | | uname | age | sex | + -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- + | 1 | XGC 21 | | | + + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

There are two parameters, the first indicating the number of records to skip and the second indicating the number of records to query

Mysql > SELECT * FROM user LIMIT 1,2; +-----+----------+------+------+ | uid | uname | age | sex | +-----+----------+------+------+ | 6 | zhangsan | 22 | NULL | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

OFFSET clause

The OFFSET clause cannot be used alone, only if the LIMIT clause exists

To facilitate testing, add another piece of data

Mysql > INSERT INTO user (uname, age, sex) VALUES ('xiaoli', 22, 'male '); Query OK, 1 row affected (0.35 SEC)Copy the code

At this point, all data are as follows:

mysql> SELECT * FROM user; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + uid | | uname | age | sex | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- + | 1 | XGC 21 male | | | | | 6 Zhangsan 22 | NULL | | | | 7 xiaoli 22 male | | | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + 3 rows in the set (0.00 SEC)Copy the code

Test the OFFSET clause

mysql> SELECT * FROM user WHERE age=22 LIMIT 1 OFFSET 1; + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + uid | | uname | age | sex | + -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- + | | 7 xiaoli 22 male | | | + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

The UNION operator

The MySQL UNION operator is used to join the results of more than two SELECT statements into a result set.

SELECT column_name, column_name FROM table_name [WHERE Clause]
UNION [ALL | DISTINCT]
SELECT column_name, column_name FROM table_name [WHERE Clause];
Copy the code

Parameters:

DISTINCT: Deletes duplicate data from the result set. This option is optional. The default.
ALL: ALL result sets, including duplicate data, are returned.

Note:

The number of columns in the query result must be the same.
The UNION operator does not check whether the column names are the same and simply combines the second SELECT statement into the result set of the first SELECT statement. That is, the column name of the result set is the column name of the first SELECT statement.

Example:

SELECT * FROM user WHERE uname = 'xgc'
UNION
SELECT * FROM user WHERE age = 22;
Copy the code

The ORDER BY ORDER

We can use the ORDER BY clause to sort the data set and return the result

SELECT column_name, column_name FROM table_name ORDER BY column_name [ASC | DESC], [column_name [ASC | DESC]]
Copy the code

The column name after ORDER BY indicates that the ORDER is sorted BY the column name
ASC indicates ascending order, and DESC indicates descending order. Sort by default in ascending order
We can sort by multiple column names, first by the first column name, and then by the next column name when the column name is the same.

Example:

mysql> SELECT * FROM user ORDER BY age DESC; +-----+----------+------+------+ | uid | uname | age | sex | +-----+----------+------+------+ | 6 | zhangsan | 22 | NULL 7 | | | xiaoli 22 male | | | | 1 | XGC | | | male 21 + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + 3 rows in the set (0.00 SEC)Copy the code

GROUP BY GROUP

The GROUP BY statement groups result sets BY one or more columns. Group columns with the same value.

On grouped columns we can use COUNT, SUM, AVG, etc.

SELECT column_name function(column_name) FROM table_name [WITH ROLLUP]
GROUP BY column_name;
Copy the code

WITH ROLLUP enables the same statistics to be performed on top of grouped statistics

Example: Divide the age 22 in the user table into a group and count the corresponding number

mysql> select age, count(age) AS num from user group by age;
+------+------------+
| age  | num        |
+------+------------+
|   21 |          1 |
|   22 |          2 |
+------+------------+
2 rows in set (0.00 sec)
Copy the code

Here, we can use WITH ROLLUP to recount the total number of queries

mysql> select age, count(age) AS num from user group by age WITH ROLLUP; + -- -- -- -- -- - + -- -- -- -- -- + | age | num | + -- -- -- -- -- - + -- -- -- -- -- + | | 1 | 21 22 | 2 | | | NULL | 3 | + -- -- -- -- -- - + -- -- -- -- -- + 3 rows in the set (0.00 SEC)Copy the code

We can see that the age column is NULL for the data calculated WITH ROLLUP. We can use coalesce to set values instead of NULL.

Let’s look at the syntax

coalesce(a,b,c)
Copy the code

Note: If a==null, select B; If b==null, select c; If a! =null, select a. If a, B, and c are both null, null is returned.

We set it to the total

Mysql > select coalesce(age, 'total ') AS age, count(age) AS num from user group by age WITH ROLLUP; + + -- -- -- -- -- -- -- -- -- -- -- -- -- + | age | num | + -- -- -- -- -- -- -- - + -- -- -- -- -- + | | 1 | 21 22 | 2 | | | | 3 | total + + -- -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set, 1 warning (0.00 SEC)Copy the code

Modify the data

UPDATE table_name SET column_name = new_value, column_name = new_value [WHERE Clause]
Copy the code

Delete the data

DELETE FROM table_name [WHERE Clause]
Copy the code

The WHERE clause

SELECT column_name, column_name FROM table_name [WHERE condition1 [AND|OR] condition2 .. ]Copy the code

WHERE is followed by the query criteria
Multiple query criteria can be joined using AND AND OR
The WHERE clause can be applied to SQL SELECT, DELETE, or UPDATE commands.
The string comparison of MySQL’s WHERE clause is case insensitive. You can use the BINARY keyword to specify that string comparisons in the WHERE clause are case sensitive.
```
SELECT * FROM user WHERE BINARY name=`xgc`;
Copy the code
```

The operators are as follows:

The operator	describe	The instance
=	Equal sign, checks if two values are equal, and returns true if they are	Return false
The < > and! =	Not equal: Checks if two values are equal, and returns true if they are not	(A ! = B) returns true
>	Greater than checks if the value on the left is greater than the value on the right, and returns true if the value on the left is greater than the value on the right	A > B returns false
<	The less than sign checks whether the value on the left is less than the value on the right, and returns true if the value on the left is less than the value on the right	(A < B) returns true
> =	The greater-than or equal sign checks whether the value on the left is greater than or equal to the value on the right, and returns true if the value on the left is greater than or equal to the value on the right	(A >= B) returns false
< =	The less than or equal sign checks whether the value on the left is less than or equal to the value on the right, and returns true if the value on the left is less than or equal to the value on the right	(A <= B) returns true

The LIKE clause

We can use the LIKE clause in the WHERE clause

The LIKE clause can replace the equal sign =. The difference is that the LIKE clause supports fuzzy matching.

MySQL provides two matching methods

%: Matches any zero or more characters.
_: Matches a single arbitrary character

mysql> SELECT * FROM user WHERE uname LIKE 'zh%'; +-----+----------+------+------+ | uid | uname | age | sex | +-----+----------+------+------+ | 6 | zhangsan | 22 | NULL | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

REGEXP

We know that MySQL can do fuzzy matching with LIKE.

The REGEXP operator is used in MySQL for regular expression matching.

SELECT * FROM user WHERE uname REGEXP '^xiao'
Copy the code

Select * from uname; select * from xiao;

model	describe
^	Matches the start of the input string.
$	Matches the end of the input string.
.	matches`\n`Any single character other than. Matches include`\n`Inside any character used`[.\n]`
[…].	Collection of characters. Matches any of the contained characters. For example,`[abc]`Can match`plain`In the`a`.
[^…].	A collection of negative characters. Matches any character that is not contained. For example,`[^abc]`Can match`plain`In the`p`.
p1\|p2\|p3	Matches P1 or P2 or P3. For example,`z\|flood`Can match`z` 或 `food`.`(z\|f)ood`The match`zood` 或 `food`.
*	Matches the preceding subexpression zero or more times. For example,`zo`Can match`z`As well as`zoo`.``Is equivalent to`, {0}`.
+	Matches the previous subexpression one or more times. For example,`zo+`Can match`zo`As well as`zoo`, but cannot match`z`.`+`Is equivalent to`{1,}`.
{n}	`n`Is a non-negative integer. Matched determined`n`Times. For example,`o{2}`Can’t match`Bob`In the`o`But it matches the two O’s in “food”.
{n,m}	Both m and n are non-negative integers, where n <= m. At least n times and at most m times are matched.

A NULL value processing

When we need to query column values for null or non-null yes, = and! = doesn’t work.

We should use IS NULL and IS NOT NULL

SELECT * FROM user WHERE sex IS NULL;
Copy the code

SELECT * FROM user WHERE sex IS NOT NULL;
Copy the code

JOIN the connection

Real applications often need to read data from multiple tables.

We can use MySQL’s JOIN to query data in two or more tables.

We can use Mysql’s JOIN in SELECT, UPDATE, and DELETE statements for federated multi-table queries.

According to functions, JOIN can be roughly divided into the following three categories:

INNER JOIN: Gets a record of the field matching relationship between two tables.
**LEFT JOIN: ** retrieves all records from the LEFT table, even if there is no matching record from the right table.
RIGHT JOIN: In contrast to LEFT JOIN, it is used to get all the records in the RIGHT table, even if there are no matching records in the LEFT table.

Before we learn about JOIN, let’s know what cartesian product is:

Cartesian product phenomenon occurs when we perform multi-table joint query

Now, we have two sets A and B

A = {0,1} B = {2,3,4}

The result sets of A×B and B×A can be expressed in the following form respectively:

A×B = {(0,2), (1,2), (0,3), (1,3), (0,4), (1,4)};

B×A = {(2,0), (2,1), (3,0), (3,1), (4,0), (4,1)}

So this A by B and B by A is called the Cartesian product of two sets.

From the above we can draw two conclusions:

The exchange rate is not satisfied when two sets are multiplied, so A cross B does not equal B cross A
When you multiply A by B, it covers all the possibilities of combining A member of A with A member of B. So the number of elements of the new set multiplied by two sets is going to be the number of elements of A times the number of elements of B

The algorithm followed when matching rows in a database table is the Cartesian product mentioned above

Before we use it, let’s look at two tables that join multi-table queries

mysql> SELECT * FROM stu; +-----+----------+------+------+---------+ | sid | sname | age | sex | address | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | 1 | zhangsan 21 male | | | NULL | | 2 | lisi | | NULL | NULL | 21 + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

mysql> SELECT * FROM user; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + uid | | uname | age | sex | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- + | 1 | XGC 21 male | | | | | 6 Zhangsan 22 | NULL | | | | 7 xiaoli 22 male | | | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + 3 rows in the set (0.00 SEC)Copy the code

INNER JOIN:

Select * from stu where age is equal to user where age is equal

mysql> SELECT * FROM user u INNER JOIN stu s ON u.age = s.age; +-----+-------+------+------+-----+----------+------+------+---------+ | uid | uname | age | sex | sid | sname | age | Sex | address | + + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | 1 | XGC | | | | 1 male 21 zhangsan | | | NULL male 21 | | 1 | XGC | | | | 2 male 21 lisi | | NULL | NULL | 21 + + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

Let me first explain the meaning of the above SQL statement

An INNER JOIN
The u and S after the table names user and stu are their aliases, respectively, fromu.age = s.ageAs you can see, it has the function of distinguishing two tables with the same column name
Similar to WHERE, ON represents the records that meet the criteria after a query

We see that the inner join matches:

I’m going to start with cartesian productsNumber of records in table A x number of records in table BA record
Then use ON to filter out the records that match

LEFT to JOIN:

mysql> SELECT * FROM user u LEFT JOIN stu s ON u.age = s.age; +-----+----------+------+------+------+----------+------+------+---------+ | uid | uname | age | sex | sid | sname | age | sex | address | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | 1 | XGC | | | | 1 male 21 Zhangsan 21 male | | | NULL | | 1 | XGC | | | | 2 male 21 lisi | | NULL 21 | NULL | | | 6 zhangsan 22 | NULL | NULL | NULL | | NULL | NULL | NULL | | | 7 xiaoli 22 male | | | NULL | NULL | NULL | NULL | NULL | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- - + 4 rows in the set (0.00 SEC)Copy the code

RIGHT JOIN:

mysql> SELECT * FROM user u RIGHT JOIN stu s ON u.age = s.age; +------+-------+------+------+-----+----------+------+------+---------+ | uid | uname | age | sex | sid | sname | age | Sex | address | + -- -- -- -- -- - + -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | 1 | XGC | | | | 1 male 21 Zhangsan | | | NULL male 21 | | 1 | XGC | | | | 2 male 21 lisi | | NULL | NULL | 21 + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC)Copy the code

MySQL transaction

A transaction is a set of SQL statements that are either all or none executed.

Transactions are only supported in MySQL for databases or tables that use the Innodb database engine.
Transactions manage INSERT, UPDATE, and DELETE statements

By default on the MySQL command line, transactions are committed automatically, that is, the COMMIT operation is performed immediately after the SQL statement is executed.

Therefore, to explicitly START a TRANSACTION, use the commands BEGIN or START TRANSACTION, or execute the command SET AUTOCOMMIT=0, which disables automatic commit using the current session.

Change the MySQL automatic commit mode

In MySQL, transactions are committed automatically by default. You can manually turn off or on automatic commit transactions

SET AUTOCOMMIT=0 Disables automatic commit
SET AUTOCOMMIT=1 Enables automatic commit

Open the transaction

We can manually start a transaction so that the operation to the database is permanent only after it is committed

BEGIN or START TRANSACTION To explicitly START a TRANSACTION;

Transaction rollback

When we were executing a transaction, we found an error in one of the statements and wanted to undo any uncommitted changes that were in progress. Then we can use transaction rollback.

ROLLBACK or ROLLBACK WORK ROLLBACK of the transaction

Transaction commit

When the transaction executes, none of the statements fail. We intend to commit all changes to make changes to the database persistent. We are going to commit the transaction

COMMIT or COMMIT WORK commits transactions and makes permanent any changes that have been made to the database;

Sample transaction processing (open, rollback, commit)

mysql> SELECT * FROM user; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + uid | | uname | age | sex | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- + | 1 | XGC 21 male | | | | | 6 Zhangsan 22 | NULL | | | | 7 xiaoli 22 male | | | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + 3 rows in the set (0.00 SEC) # open transaction mysql > BEGIN; Query OK, 0 rows affected (0.00 SEC) mysql> INSERT INTO user (uname, age) VALUES('lisi', 18); Query OK, 1 row affected (0.00 SEC) # mysql> COMMIT; Query OK, 0 rows affected (0.06 SEC) # mysql> BEGIN; Query OK, 0 rows affected (0.00 SEC) mysql> INSERT INTO user (uname, age) VALUES('xiaohong', 18); Query OK, 0 rows affected (0.00 SEC) mysql> INSERT INTO user (uname, age) VALUES('xiaohong', 18); Query OK, 1 row affected (0.00 SEC) # ROLLBACK; SQL > SELECT * FROM user; SQL > SELECT * FROM user; SQL > SELECT * FROM user; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + uid | | uname | age | sex | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- + | 1 | XGC 21 male | | | | | 6 Zhangsan 22 | NULL | | | | 7 xiaoli 22 male | | | | | 8 lisi | | NULL | + 18 + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- - + 4 rows in the set (0.00 SEC)Copy the code

The savepoint

SAVEPOINT is a way to implement subtransactions, also known as nested transactions, in database transactions. Transactions can be rolled back to SAVEPOINT without affecting the changes made before SAVEPOINT was created, without abandoning the entire transaction.

Allows you to create a SAVEPOINT in a transaction. You can have multiple savepoints in a transaction

Declare a savepoint
```
SAVEPOINT savapoint_name;
Copy the code
```

Roll back to the savepoint

ROLLBACK TO savepoint_name;
Copy the code

Delete savepoints and release them automatically after the transaction is complete (perform a ROLLBACK or COMMIT)

Since MySQL5, you can use:
```
RELEASE SAVEPOINT savepoint_name; # delete the specified savepointCopy the code
```

Sets the transaction isolation level

The isolation level of a transaction is used to solve concurrency problems such as dirty reads, phantom reads, and unrepeatable reads

The InnoDB storage engine provides transaction isolation levels

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Sets the transaction isolation level

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 
READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE
Copy the code

View the transaction isolation level

To view global and current session transaction isolation levels:

mysql> select @@global.transaction_isolation, @@transaction_isolation; +--------------------------------+-------------------------+ | @@global.transaction_isolation | @@transaction_isolation | +--------------------------------+-------------------------+ | REPEATABLE-READ | REPEATABLE-READ | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

MySQL index

The establishment of MySQL index is very important for the efficient operation of MySQL, index can greatly improve the speed of MySQL retrieval.

To create an index, you need to ensure that the index is a condition applied to the SQL query condition (typically as a condition in the WHERE clause).

In fact, an index is also a table. This table holds primary key and index fields and points to records in entity tables.

While indexes can speed up queries, they should not be abused. Therefore, when updating (UPDATE, DELETE, INSERT) tables, MySQL will not only save the data, but also save the index file, which will slow down the UPDATE of the table.

The index classification

In MySQL, indexes are divided into normal indexes, unique indexes, primary key indexes, and full-text indexes. Each index can be divided into single column index and combined index according to the different columns.

Therefore, we can also say that indexes are divided into single-column indexes and composite indexes.

Single-column index, that is, an index contains only a single column. A table can have multiple single-column indexes.
Composite indexes, in which an index contains multiple columns.

Composite indexes refer to indexes created on multiple fields. An index is used only if the first field that created the index is used in the query condition. Follow the leftmost prefix matching principle when using composite indexes.

Left-most prefix matching rules:

Mysql will keep matching to the right until it encounters range queries (>, <, between, like) and then stops matching.

If a = 1 and b = 2 and c > 3 and d = 4, if a = 1 and b = 2 and c > 3 and d = 4,

If the index (a,b,d,c) can be used, the order of a,b,d can be arbitrarily adjusted.

Here, we look at the differences between normal, unique, primary and full-text indexes

Plain index: This is the most basic index, it has no restrictions.
Unique index: It is similar to a normal index except that the value of the index column must be unique, but empty values are allowed.

If it is a composite index, the combination of column values must be unique.
Primary key index: A special unique index in which a table can have only one primary key and no null values are allowed. Typically, the primary key index is created at the same time as the table is being built.
Full-text indexes: Mainly used to find keywords in text, not to compare directly with values in the index.

The FULLTEXT index is very different from other indexes in that it is more like a search engine than a simple where statement parameter matching.

The FULLTEXT index is used with the match against operation, rather than the usual WHERE statement plus like.

Create indexes

There are three ways to create a normal index, a unique index, and a full-text index:

After the table is created, create it directly

CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name(column_name[, column_name]);
Copy the code

Modify table structure (add index)

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT] INDEX index_name(column_name[, column_name]);
Copy the code

When creating the table

CREATA TABLE table_name(
	column_name column_type,
    column_name column_type,
    [INDEX | FULLTEXT | UNIQUE] [index_name] (column_name[, column_name])
);
Copy the code

There are two ways to create a primary key index:

Modify table structure (primary key index)

ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY(column_name[, column_name]);
Copy the code

Create the table at the same time

CREATE TABLE table_name(
	column_name column_type,
    column_name column_type,
    PRIMARY KEY(column_name[, column_name])
);
Copy the code

Remove the index

All of the above indexes can be dropped using the following statement

DROP INDEX index_name ON table_name;
Copy the code

ALTER TABLE table_name DROP INDEX index_name;
Copy the code

If it is a primary key, we can also omit the index name

ALTER TABLE table_name DROP PRIMARY KEY;
Copy the code

Display index information

SHOW INDEX FROM table_name;
Copy the code

Example:

mysql> SHOW INDEX FROM user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name |  Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+---------+------------+ | user | 0 | PRIMARY | 1 | uid | A | 0 | NULL | NULL | | BTREE  | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

A temporary table

MySQL temporary tables are very useful when we need to save some temporary data. Temporary tables are only visible in the current connection. When a connection is closed, Mysql automatically drops the table and frees all space.

Temporary tables were added in MySQL 3.23. If your MySQL version is earlier than 3.23, you cannot use temporary tables in MySQL 3.23.

Create temporary table

CREATE TEMPORARY TABLE table_name(
	column_name column_type,
    column_name column_type
);
Copy the code

When using the SHOW TABLES command to display the table list, we cannot see temporary TABLES.

Delete temporary table

By default, temporary tables are destroyed automatically when you disconnect from the database.

You can also use the DROP TABLE command to manually DROP temporary tables in the current MySQL session.

DROP TABLE table_name;
Copy the code

The replication table

Sometimes, we need to complete a copy of MySQL table, including table structure, index, etc. This cannot be done by simply using the CREATE TABLE new_table_name SELECT command.

We may also need to copy MySQL table data to another table.

Replicate table structure

Use the SHOW CREATE TABLE command to get the CREATE TABLE clause, which contains the structure of the original TABLE, indexes, and so on.
```
SHOW CREATE TABLE table_name;
Copy the code
```
Copy the database table clause to modify the table name and execute

Example:

mysql> SHOW CREATE TABLE user; +-------+--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ----------------------------------------+ | user | CREATE TABLE `user` ( `uid` int unsigned NOT NULL AUTO_INCREMENT, `uname` varchar(20) DEFAULT NULL, `age` int DEFAULT NULL, `sex` varchar(2) DEFAULT NULL, PRIMARY KEY (`uid`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+--------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Replicate table data

INSERT INTO table_name SELECT column_name FROM table_name;
Copy the code

metadata

The command	describe
SELECT VERSION()	Server Version information
SELECT DATABASE()	Current database name (or return null)
SELECT USER()	Current user name
SHOW STATUS	Server status
SHOW VARIABLES	Server configuration variable

MySQL sequence

Only one field in a table can be an auto-increment primary key. If we want to add other fields automatically, we can use MySQL sequences.

Use the AUTO_INCREMENT

The easiest way to use sequences in MySQL is to use AUTO_INCREMENT to define columns.

mysql> CREATE TABLE insect -> ( -> id INT UNSIGNED NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, # type of insect -> date DATE NOT NULL, # date collected -> origin VARCHAR(30) NOT NULL # where collected ); Query OK, 0 rows affected (0.02sec) mysql> INSERT INTO insect (id,name,date,origin) VALUES -> (NULL,'housefly','2001-09-10','kitchen'), -> (NULL,'millipede','2001-09-10','driveway'), -> (NULL,'grasshopper','2001-09-10','front yard'); Query OK, 3 rows affected (0.02sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM insect ORDER BY id; +----+-------------+------------+------------+ | id | name | date | origin | +----+-------------+------------+------------+ | 1 | housefly | 2001-09-10 | kitchen | | 2 | millipede | 2001-09-10 | driveway | | 3 | grasshopper | 2001-09-10 | front yard | +----+-------------+------------+------------+ 3 rows in set (0.00 SEC)Copy the code

Get AUTO_INCREMENT value

In MySQL client we can use SQL function 1686434 to get the value of the increment column in the last insert table.

SELECT LAST_INSERT_ID(a);Copy the code

The reset sequence

If we delete multiple records in a table and want to rearrange the AUTO_INCREMENT column of the remaining data. We can do this by removing the increment column and then adding it again.

Do this with care, however; if a new record is added at the same time as the deletion, data may be corrupted.

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);
Copy the code

Sets the start value of the sequence

Normally the sequence starts with a value of 1, but if you need to specify a value of 100, you can do this by:

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, 
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;
Copy the code

Or you can create a table with the following statement:

mysql> ALTER TABLE t AUTO_INCREMENT = 100;
Copy the code

Handling duplicate data

In some MySQL tables, duplicate records may exist. In some cases, we allow duplicate data to exist, but sometimes we need to delete duplicate data.

Prevent duplicate data in the table

You can specify the PRIMARY KEY or UNIQUE index in the MySQL table to ensure that the data is UNIQUE.

Let’s try an example: The following table has no indexes or primary keys, so it allows multiple duplicate records.

CREATE TABLE person_tbl
(
    first_name CHAR(20),
    last_name CHAR(20),
    sex CHAR(10));Copy the code

Select last_name, last__name, last_name, last_name, last_name, last_name, last_name, last_name, last_name, last_name, last_name, last_name, last_name, last_name, last_name, last_name, last_name, last_name, last_name; As follows:

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);
Copy the code

Another way to set the uniqueness of the data is to add a UNIQUE index like this:

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   UNIQUE (last_name, first_name)
);
Copy the code

INSERT IGNORE INTO will IGNORE data already in the database, if there is no data, INSERT new data, if there is data skipped data. In this way, the existing data in the database can be preserved to achieve the purpose of inserting data in the gap.

The following example uses INSERT IGNORE INTO without error and does not INSERT duplicate data INTO the table:

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES( 'Jay', 'Thomas'); Query OK, 1 row affected (0.00 SEC) mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES('Jay', 'Thomas'); Query OK, 0 rows affected (0.00 SEC)Copy the code

If we set a unique index, the SQL statement will fail to execute and throw errors when using INSERT INTO to INSERT duplicate data. Suppose we use INSERT IGNORE INTO. If duplicate data is inserted, no error is returned, only a warning.

Statistical duplication

Select last_name from last_first_name and last_name from last_name;

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;
Copy the code

Filtering duplicate data

If you need to read unique data, use the DISTINCT keyword in the SELECT statement to filter duplicate data.

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl;
Copy the code

You can also use GROUP BY to read non-repeating data from a table:

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);
Copy the code

Deleting Duplicate Data

If you want to delete duplicate data from a table, you can use the following SQL statement:

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;
Copy the code

You can also delete duplicate entries from a table by simply adding INDEX and PRIMAY KEY to the table. The method is as follows:

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);
Copy the code

Export data

In MySQL you can use SELECT… INTO OUTFILE statement to simply export data to a text file.

Use the SELECT… The INTO OUTFILE statement exports data

/ TMP /runoob.txt export runoob_tbl to/TMP /runoob.txt

mysql> SELECT * FROM runoob_tbl 
    -> INTO OUTFILE '/tmp/runoob.txt';
Copy the code

Derived tables

Mysqldump is a utility used by mysql to convert databases. It mainly produces an SQL script that contains commands such as CREATE TABLE INSERT necessary to recreate the database from scratch.

To export data using mysqldump, use the — TAB option to specify the directory to export the file to, which must be writable.

The following example exports the data table runoob_tbl to the/TMP directory:

$ mysqldump -u root -p --no-create-info \
            --tab=/tmp RUNOOB runoob_tbl
password ******
Copy the code

Export data in SQL format

Export data in SQL format to the specified file, as shown below:

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******
Copy the code

The file content created by using the preceding command is as follows:

8.23 - MySQL dump
--
-- Host: localhost Database: RUNOOB
---------------------------------------------------------
- Server version 3.23.58

--
-- Table structure for table `runoob_tbl`
--

CREATE TABLE runoob_tbl (
  runoob_id int(11) NOT NULL auto_increment,
  runoob_title varchar(100) NOT NULL default ' ',
  runoob_author varchar(40) NOT NULL default ' ',
  submission_date date default NULL,
  PRIMARY KEY  (runoob_id),
  UNIQUE KEY AUTHOR_INDEX (runoob_author)
) TYPE=MyISAM;

--
-- Dumping data for table `runoob_tbl`
--

INSERT INTO runoob_tbl 
       VALUES (1.'Learn PHP'.'John Poul'.'2007-05-24');
INSERT INTO runoob_tbl 
       VALUES (2.'Learn MySQL'.'Abdul S'.'2007-05-24');
INSERT INTO runoob_tbl 
       VALUES (3.'JAVA Tutorial'.'Sanjay'.'2007-05-06');
Copy the code

If you need to export the entire database, you can use the following command:

$ mysqldump -u root -p RUNOOB > database_dump.txt
password ******
Copy the code

If you want to back up all databases, you can use the following command:

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******
Copy the code

— All-databases is added to MySQL 3.23.12 and later.

This method can be used to implement database backup strategy.

Copy data tables and databases to other hosts

If you need to copy data to another MySQL server, you can specify the database name and table in mysqldump.

Run the following command on the source host to back up data to the dump. TXT file:

$ mysqldump -u root -p database_name table_name > dump.txt
password *****
Copy the code

If the database is fully backed up, there is no need to use a specific table name.

If you need to import the backup database to the MySQL server, you can use the following command to confirm that the database has been created:

$ mysql -u root -p database_name < dump.txt
password *****
Copy the code

You can also use the following command to import the exported data directly to a remote server, but make sure the two servers are connected and accessible to each other:

$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name
Copy the code

Pipes are used in the command above to import the exported data to the specified remote host.

Import data

Mysql command import

Mysql -u user_name -p < runoob.sql >Copy the code

Source Command import

The source SQL fileCopy the code

LOAD DATA Imports DATA

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE table_name;
Copy the code

If the LOCAL keyword is specified, the file is read from the client host by path. If not specified, the file reads the file by path on the server.

Import data using mysqlimport

The mysqlimport client provides a command line interface to LOAD DATA INFILEQL statements. Most of the options for mysqlimport correspond directly to the LOAD DATA INFILE clause.

To import data from the dump. TXT file into the mytbL table, use the following command:

$ mysqlimport -u root -p --local mytbl dump.txt
password *****
Copy the code