Sort out some MySQL interview questions

Sort out some MySQL interview questions

1. How to log on to the mysql database
Mysql-u username-p

2. How to enable/disable the mysql Service
Service mysql start/stop

3. view the mysql status
Service mysql status

4. How to display the number of all databases
Show databases

5. How to obtain the names and types of all field objects in the table
Describe table_name;

6. Does MYSQL support transactions?
In the default mode, MYSQL is in the autocommit mode, and all database update operations are submitted instantly. Therefore, mysql does not support transactions by default.
However, if your MYSQL table type is InnoDB Tables or BDB tables, you can use transaction processing in MYSQL. Using set autocommit = 0, MYSQL can be allowed in non-autocommit mode, in non-autocommit mode, you must use COMMIT to submit your changes, or use ROLLBACK to roll back your changes.
Example:
Start transaction;
SELECT @ A: = SUM (salary) FROM table1 WHERE type = 1;
UPDATE table2 SET summmary = @ a where type = 1;
COMMIT;

7. What are the features of MYSQL compared with other databases?
MySQL is a small relational database management system. The developer is MySQL AB in Sweden and has now been acquired by Sun, support for FreeBSD, Linux, MAC, Windows, and other operating systems is slightly weaker than other large databases such as Oracle, DB2, and SQL Server.
1. Large data with over 10 million records can be processed
2. Supports common SQL statement specifications
3. High portability, simple and small Installation
4. Good operation efficiency and rich information network support
5. debugging, management, and simple optimization (compared with other large databases)

8. Differences between varchar and char
Char is a fixed-length type, and varchar is a variable-length type.

9. What types of database transactions are there?
Isolation, continuity, consistency, atomicity

10. What are the four transaction isolation level names supported by InnoDB in MySQL and the differences between them?
The SQL standard defines four isolation levels:
Read uncommited: read uncommitted content
Read committed: read the submitted content
Repeatable read: repeable
Serializable: serializable
The detailed explanation is as follows:
Read Uncommitted (Read Uncommitted content)
At this isolation level, all transactions can see the execution results of other uncommitted transactions. This isolation level is rarely used in practical applications, because its performance is no better than other levels. Read uncommitted data, also known as Dirty Read ).
Read Committed (Read submitted content)
This is the default isolation level for most database systems (but not for MySQL ). It satisfies the simple definition of isolation: a transaction can only see changes made by committed transactions. This isolation level also supports the so-called Nonrepeatable Read, because other instances of the same transaction may have a new commit during the processing of this instance, so the same select may return different results.
Repeatable Read (repeable)
This is the default transaction isolation level of MySQL. It ensures that multiple instances of the same transaction will see the same data rows when reading data concurrently. However, theoretically, this will lead to another tricky problem: Phantom Read ). In short, phantom read refers to when a user reads data in a certain range, another transaction inserts a new row in this range. When the user reads data in this range, there will be a new Phantom line. InnoDB and Falcon storage engine solve this problem through multi-version Concurrency Control (MVCC, Multiversion Concurrency Control gap lock) mechanism. Note: In fact, multiple versions only solve the problem of non-repeatable read, while the gap lock (also known as Concurrency Control) solves the phantom read problem.
Serializable (Serializable)
This is the highest isolation level. It forces transaction sorting to make it impossible to conflict with each other, thus solving the phantom read problem. In short, it adds a shared lock to each read data row. At this level, there may be a lot of timeout and lock competition.
For different transactions, different isolation levels have different results. Different isolation levels have different phenomena. There are mainly three types of current:
1. dirty read: one transaction can read the modified data of another transaction that has not yet been committed.
2. nonrepeatable read: In the same transaction, when the same query reads a row at T1 and re-reads the row at T2, the data in this row has been modified and may have been updated or deleted ).
3. phantom read: when the same query is performed multiple times in the same transaction, different result sets are returned each time because other insert transactions are committed.
Different isolation levels have different phenomena and have different locking/concurrency mechanisms. The higher the isolation level, the worse the database concurrency, the following table lists the symptoms of the four transaction isolation levels:

For more details, please continue to read the highlights on the next page:

1 2 Next Page