Collect MySQL's face questions to share with everyone

1. How to log into MySQL database

Mysql-u username-p

2. How to turn on/off MySQL service

Service MySQL Start/stop

3. Check the status of MySQL

Service MySQL Status

4, how to display a number of all databases

Show databases

5. How to get the names and types of all field objects in a table

Describe table_name;

6. Does MySQL support business?

In the default mode, MySQL is in autocommit mode. All database update operations are committed immediately. So, by default, MySQL does not support transactions.
But assuming that your MySQL table type is using InnoDB Tables or BDB Tables, your MySQL will be able to use transaction processing, and using set autocommit=0 will enable MySQL to agree in non-autocommit mode, In non-autocommit mode, you must use commit to commit your changes. or roll your changes back and forth with rollback.
The scale is as follows:
START TRANSACTION;
SELECT @a:=sum (Salary) from table1 WHERE type=1;
UPDATE table2 SET [email protected] WHERE type=1;
COMMIT;

7. What are the characteristics of MySQL compared to other databases?

MySQL is a small relational database management system developed by the Swedish MySQL AB company. Now acquired by Sun, support for FreeBSD, Linux, MAC, Windows and many other operating systems and other large databases such as Oracle, DB2, SQL Server and other functions slightly weaker than
1. Ability to handle large data with thousands records
2. Support Common SQL statement specification
3, portable row height, easy installation and small
4, good operating efficiency, rich information network support
5, debugging, management, optimization simple (relative to other large databases)

8, the difference between varchar and char

Char is a fixed-length type, and varchar is a variable-length type

9. What kinds of database things are there?

Isolation, durability, consistency, atomicity

10. Please briefly describe the four types of transaction isolation level names supported by InnoDB in MySQL. And the difference between the ranks?

The four isolation levels defined by the SQL standard are:
Read uncommited: Reading uncommitted content
Read Committed: Reading submissions
REPEATABLE READ: Can be reread
Serializable: Serializable
For example, the following:
READ UNCOMMITTED (Read UNCOMMITTED content)
At this isolation level, all transactions can see the results of the operation of other uncommitted transactions. This isolation level is rarely used in real-world applications, because it has no better performance than other levels. Reading uncommitted data is also known as Dirty reading (Dirty read).

Read Committed (read submit content)
This is the default isolation level for most database systems (but not MySQL default). It satisfies the simple definition of isolation: A transaction can only see changes that have been submitted to the firm. This isolation level also supports so-called non-repeatable reads (nonrepeatable read), because other instances of the same transaction may have a new commit during the instance processing, so the same select may return different results.

Repeatable Read (can be reread)
This is the default transaction isolation level for MySQL, which ensures that multiple instances of the same transaction will see the same rows of data while concurrently reading the data. Just theoretically, this leads to a tricky problem: Phantom Reading (Phantom read). To put it simply, Phantom reading refers to when a user reads a range of data rows, another transaction inserts a new row within that range, when the user reads the data row for that range again. A new phantom line will be found. The InnoDB and Falcon storage engines are concurrency controlled by the multiple version number (MVCC. Multiversion Concurrency Control Gap Lock) mechanism overcomes this problem.

Note: The fact that the multi-version number is only to solve non-repeatable reading problems. And the gap lock, which is what it calls concurrency control, overcomes the illusion of reading.

Serializable (Serializable)
This is the highest isolation level, and it is not possible to conflict with each other by forcing transactions to sort. So as to solve the phantom reading problem. Simply. It is a shared lock on every data row that is read. At this level. May result in a large number of timeouts and lock competitions.
For different transactions, different isolation levels are used to separate the results. Different isolation levels have different phenomena. There are 3 main types of these:
1. Dirty reads (dirty read): One transaction can read a change data that has not yet committed a transaction.
2, non-repeated read (Nonrepeatable Read): In the same transaction, the same query reads a row at T1 time. When the T2 time is read again, the data for this row has changed and may have been updated (update). May also have been deleted (delete).

3, Phantom Read (Phantom Read): In the same transaction, the same query is repeated multiple times, due to the other insert operation (insert) of the transaction commits, resulting in a different result set each return.
Different isolation levels have different phenomena, and there are different locking/concurrency mechanisms, the higher the isolation level, the worse the concurrency of the database, the 4 types of transaction isolation are manifested in the following tables, for example:

11. mysql Database engine MyISAM and InnoDB differences

12. What are the MySQL tables for permissions?

MySQLServer controls user access to the database through a permission table, which is stored in the MySQL database and initialized by the mysql_install_db script. These permission tables are respectively user. Db,table_priv,columns_priv and host.

The structure and contents of these tables are described below:
User Permission table: records the Subscriber account information that is agreed to connect to the server. The permissions inside are the global level.
DB Permission table: Records the operation permissions of each account on each database.
Table_priv Permissions table: Records operation permissions at the data table level.
Columns_priv Permissions table: Records the operation permissions at the data column level.
Host permission table: The database-level operation permissions on a given host are more carefully controlled with the DB permission table. This permission table is not affected by the grant and REVOKE statements.

13. What is the MySQL storage engine? How do I change the MySQL storage engine?

MyISAM Indexed sequential access method (indexed sequential interview methods)
MyISAM has most of the tools for checking and repairing tables. Tables can be compressed. and support full text collection
is not a transactional security and does not support foreign keys.

Memory is also used (HEAP) the type table is stored in RAM. The index of the table is hash-distributed.
Merge These tables use the MyISAM table collection as a single table for querying purposes, so you can avoid the maximum file size limit in some operating systems.
Archive such types of tables are supported only, insert, select does not support Delete,update,replace, and does not use indexes.
CSV These tables are saved in a single file in the server. It includes a comma-separated data.

Tables such as InnoDB are transaction-safe. Provides a commit (commit) rollback (substantive rollback) support for foreign keys. Slower than MyISAM.

Change MySQL storage engine ALTER TABLE tablename type = InnoDB;

14. MYSQL data table repair and data recovery surface questions

1. Under what circumstances is the MySQL data table easy to corrupt?
   A sudden outage of the server caused data file corruption.
   Force shutdown. No MySQL service is turned off first.

2. What is the main phenomenon after the data table is damaged?
   When the data is selected from the table. Get an example of the following error: Incorrect key file for table: ' ... '. Try to repair it
   A query cannot find rows in a table or return incomplete data.
   Error:table ' P ' is marked as crashed and should be repaired.
   Failed to open table: Can ' t open file: ' xxx. MYI ' (errno:145).

3. What are the ways to fix data table corruption?
   Use Myisamchk to fix, detailed steps:
   1) Stop the MySQL service before repairing.
   2) Open the command line mode and then go to the MySQL/bin folder.
   3) Run the Myisamchk–recover database with the path/*. MYI
   Use the REPAIR table or the OPTIMIZE table command to repair, REPAIR table table_name fix tables OPTIMIZE table table_name -Optimized tables REPAIR The table is used to repair the corrupted tables.
   OPTIMIZE table is used to reclaim unused database space, and when the data rows on the table are deleted, the occupied disk space is not immediately reclaimed, the space is reclaimed after the OPTIMIZE Table command is used, and the data rows on the disk are re-ordered (note: on disk, Rather than a database)

15. What are the methods commands for MySQL database server performance analysis?

Show status
Some variable values worth monitoring:
Bytes_received and Bytes_sent
Traffic to and from the server.
Com_*server the command that is running.

Created_* temporary tables and files that are created between query run time.
handler_* Storage engine operation.

select_* different types of join run plans.
Sort_* several sorts of information.
Show session status like ' Select ';
Show Profiles
SET profiling=1;
Show Profiles\g
Show profile;

16, MySQL record currency with what field type good

The numeric and decimal types are implemented by MySQL as the same type, which is agreed in the SQL92 standard. They are used to hold values, and the exact accuracy of the value is an extremely important value, such as money-related data.

When declaring a class is one of these types, the precision and scale can be (and generally is) specified, for example:
Salary DECIMAL (9,2)
In this example, 9 (precision) represents the total number of decimal places that will be used to store the value, while a 2 (scale) represents the number of digits that will be used to store the decimal point. So. In such a case. The range of values that can be stored in the salary column is from 9999999.99 to 9999999.99. In Ansi/iso SQL92, syntactic decimal (p) is equivalent to decimal (p,0). The same syntax decimal is equivalent to decimal (p,0). Here the realization is agreed to the decision value p. MySQL does not currently support any of these variant forms of the Decimal/numeric data type. This is generally not a serious problem, since the main benefits of these types are derived from the ability to control precision and scale clearly.

The decimal and numeric values are stored as strings. Instead of as a binary floating-point number, the decimal precision of those values is saved.

One character is used for each digit of the value, the decimal point (assuming scale>0), and the "-" symbol (for negative values). Assume that scale is 0. Decimal and numeric values do not include decimal points or fractional parts.
Decimal and numeric are worth the largest range as a double, but for a given decimal or numeric column, the actual range can be limited by the precision or scale of the given column.

When such a column is assigned the value of a bit after the decimal point that is greater than the specified scale, the value is rounded up according to magnitude. When a decimal or numeric column is assigned a value whose size exceeds the implied range of the specified (or default) precision and scale, the MySQL store represents the corresponding endpoint value for that range.

Collect MySQL's face questions to share with everyone