- 1. How to log in to 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 the 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 autocommit mode, all database update operations are committed immediately, so by default, MySQL does not support transactions.
- But if your MySQL table type is using InnoDB Tables or BDB Tables, your MySQL can use transaction processing, using Set autocommit=0 can allow MySQL to be allowed in non-autocommit mode, In non-autocommit mode, you must use commit to commit your changes, or roll back your changes with rollback.
- Examples are 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, the developer for the Swedish MySQL AB company, has now been acquired by Sun, support FreeBSD, Linux, MAC, Windows and other large databases such as Oracle, DB2, SQL Server is slightly weaker than the function
- 1. Can 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 levels.
- 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
- Detailed explanations are 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 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 new commits 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. In theory, however, this can lead to another tricky problem: Phantom Reading (Phantom read). To put it simply, Phantom reads when a user reads a range of data rows, another transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found. The InnoDB and Falcon storage engines address this issue through a multi-version concurrency control (mvcc,multiversion Concurrency control gap Lock) mechanism. Note: In fact, multi-version only solves the non-repeatable reading problem, and the gap lock (which is what it calls concurrency control) solves the Phantom reading problem.
- Serializable (Serializable)
- This is the highest isolation level, which solves the Phantom reading problem by forcing transactions to sort, making it impossible to conflict with one another. In short, it is a shared lock on every data row read. At this level, a large number of timeouts and lock competitions can result.
- For different transactions, different isolation levels are used to separate the results. Different isolation levels have different phenomena. There are 3 main types now:
- 1. Dirty reads (dirty read): One transaction can read another modified data that has not yet committed a transaction.
- 2, non-repeating read (nonrepeatable read): In the same transaction, the same query reads a row at T1 time, the data in this row is modified, it may be updated (update), or deleted (delete) when the line is re-read at T2 time.
- 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 is returned each time.
- 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 kinds of transaction isolation levels are shown in the following table:
- 12. What are the MySQL tables for permissions?
- The MySQL server 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 User,db,table_priv,columns_priv and host, respectively. The structure and contents of these tables are described below:
- User Permission table: records the users account information that is allowed to connect to the server, and the permissions are 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 controlled more carefully 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 modify the MySQL storage engine?
- MyISAM Indexed sequential access method (indexed sequential access methods)
- MyISAM has most of the tools for checking and repairing tables. Tables can be compressed and supported for full-text collection
- is not a transactional security and does not support foreign keys.
- Memory is also the previous (HEAP) the type table is stored in RAM and 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 This type of table is supported only, insert, select does not support Delete,update,replace, and does not use indexes.
- CSV These tables are stored in a single file on the server, which contains data separated by commas.
- InnoDB This table is transaction-safe. Provides a commit (commit) rollback (substantive rollback) support for foreign keys, slower than MyISAM.
- Modify the MySQL storage engine ALTER TABLE tablename type = InnoDB;
- 14. MYSQL data table repair and data recovery surface questions
- 1. What is the condition of MySQL data sheet easily damaged?
- A sudden power outage in the server caused data file corruption.
- Force shutdown, without first shutting down the MySQL service and so on.
- 2. What is the main phenomenon after the data sheet is damaged?
- When you select data from a table, you get 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 repair a damaged data sheet?
- Use Myisamchk to fix the steps:
- 1) Stop the MySQL service before repairing.
- 2) Open the command line and go to the MySQL/bin directory.
- 3) Execute the path of the Myisamchk–recover database/*. MYI
- Use the REPAIR table or the OPTIMIZE table command to repair, REPAIR table table_name repair tables OPTIMIZE A table table_name optimize tables for the repair of corrupted tables.
- OPTIMIZE table is used to reclaim unused database space, and when the data rows on the table are deleted, the disk space occupied 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 the performance analysis of MySQL database server?
- Show status
- Some variable values worth monitoring:
- Bytes_received and Bytes_sent
- Traffic to and from the server.
- Com_* the command that the server is executing.
- Created_* temporary tables and files that are created between query execution periods.
- handler_* Storage engine operation.
- select_* different types of join execution 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 allowed 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 a moment of these types, the accuracy and scale of the energy can be (and is usually) 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. Therefore, in this case, the values that can be stored in the Salary column range from 9999999.99 to 9999999.99. In Ansi/iso SQL92, syntactic decimal (p) is equivalent to decimal (p,0). Similarly, the syntactic decimal is equivalent to decimal (p,0), where the implementation is allowed to determine the 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, not as binary floating-point numbers, so that the decimal precision of those values is saved. One character is used for each digit of the value, the decimal point (if scale>0), and the "-" symbol (for negative values). If scale is 0,decimal and the numeric value does not contain a decimal point or fractional part.
- 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 to a bit that is later than the decimal point, the value is rounded up according to scale. 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.
Common interview questions for MySQL
