preface
As one of the most popular relational database management systems, the importance of MySQL does not need me to say more, and it is almost a must to ask in the interview, and the words of larger companies will ask quite deep.
So now you are ready or not ready but want to enter the big factory friends, can take a look at me to organize these classic interview questions about MySQL, you can answer it?
PS: Complete layout questions PDF and MySQL study notes
MySQL > select * from user where MySQL > select * from user where MySQL >
1, table lock: low overhead, fast lock; No deadlocks occur; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low.
2, row-level lock: high overhead, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest.
3. Page lock: the overhead and locking time are between table lock and row lock; Deadlocks occur; The locking granularity is between table locks and row locks, and the concurrency is average.
2, What are the different tables in MySQL?
There are five types of tables:
1, MyISAM
2, Heap
3, the Merge
4, INNODB
5, ISAM
3. Briefly describe the difference between MyISAM and InnoDB in MySQL database
MyISAM:
Transactions are not supported, but each query is atomic;
Supports table-level locking, that is, each operation locks the entire table.
The total number of rows stored in the table;
A MYISAM table has three files: index file, table structure file, data file;
The data domain of the index file stores Pointers to the data file. Secondary indexes are basically the same as primary indexes, but secondary indexes are not guaranteed to be unique.
InnoDb:
Acid-supported transactions, which support four isolation levels of transactions;
Row-level locking and foreign key constraints are supported, so write concurrency is supported.
Total number of rows not stored:
An InnoDb engine is stored in one file space (shared table space, table size is not controlled by the operating system, a table may be distributed in multiple files), may be multiple (set to independent table empty, table size is limited by the operating system file size, generally 2G), by the operating system file size limit.
A primary key index uses a clustered index (the index’s data domain stores the data file itself), and a secondary index’s data domain stores the value of the primary key.
Therefore, to search data from the secondary index, you need to find the primary key through the secondary index and then access the secondary index.
It is best to use auto-increment primary keys to prevent large file adjustments when inserting data to maintain the B+ tree structure.
4, MySQL InnoDB supports four transaction isolation levels, and the difference between each level?
The four isolation levels defined by the SQL standard are:
1. Read uncommited: Uncommitted data is read
2. Read COMMITTED: Indicates dirty reads and cannot be committed
Repeatable read: repeatable read
4, serializable: serial things
5, What is the difference between CHAR and VARCHAR?
1. The CHAR and VARCHAR types differ in storage and retrieval
When CHAR values are stored, they are filled with Spaces to a specified length. Trailing Spaces are removed when retrieving CHAR values.
What is the difference between a primary key and a candidate key?
Each row of a table is uniquely identified by a primary key, and a table has only one primary key.
Primary keys are also candidates. By convention, candidate keys can be specified as primary keys and can be used for any foreign key
References.
7. What is Myisamchk used for?
It is used to compress MyISAM tables, which reduces disk or memory usage.
What is the difference between MyISAM Static and MyISAM Dynamic?
All fields on MyISAM Static have fixed widths. Dynamic MyISAM tables will have fields like TEXT, BLOB, and so on to accommodate data types of different lengths.
MyISAM Static is easier to recover from damage.
8. What happens if a table has a column defined as TIMESTAMP?
The timestamp field gets the current timestamp whenever the row is changed.
What happens if the maximum value in the table is reached when the column is set to AUTO INCREMENT?
It stops incrementing, and any further inserts will generate an error because the key has already been used.
How do I find out which automatic increment was allocated on the last insert?
LAST_INSERT_ID returns the last value assigned by Auto_increment and does not need to refer to
Specify the table name.
9. How do you see all the indexes defined for the table?
Indexes are defined for tables in the following way:
SHOW INDEX FROM ;
10, What do % and _ in LIKE declarations mean?
% corresponds to zero or more characters, and _ is just one character in a LIKE statement.
How to convert between Unix and MySQL timestamps?
UNIX_TIMESTAMP is a command that converts MySQL timestamp to Unix timestamp
FROM_UNIXTIME is the command to convert a Unix timestamp to a MySQL timestamp
What is the column comparison operator?
Use the =, <>, <=, <, > =, >, <<, >, <=>, AND, OR, OR LIKE operators in column comparisons in SELECT statements.
What’s the difference between a BLOB and a TEXT?
A BLOB is a binary object that can hold a variable amount of data. TEXT is a case insensitive BLOB.
The only difference between BLOB and TEXT types is that BLOB values are case-sensitive when sorting and comparing, while TEXT values are case-insensitive.
What is the difference between MySQL_fetch_array and MySQL_fetch_object
MySQL_fetch_array differs from MySQL_fetch_object:
MySQL_fetch_array () – Returns the result row as an associative array or as a regular array from the database.
MySQL_fetch_object – Returns the result row as an object from the database.
14. Where will MyISAM tables be stored and their storage format provided?
Each MyISAM table is stored on disk in three formats:
·. FRM file storage table definition
· Data files have the extension “.myd “(MYData)
Index files have the.myi (MYIndex) extension
MySQL: How to optimize DISTINCT?
DISTINCT is converted to GROUP BY on all columns and is used in combination with the ORDER BY clause.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
16. How to display the first 50 lines?
In MySQL, query to display the first 50 lines using the following code:
SELECT*FROM
LIMIT 0, 50;
17, How many columns can be used to create an index?
Up to 16 index columns can be created for any standard table.
What’s the difference between NOW () and CURRENT_DATE ()?
The NOW () command is used to display the current year, month, date, hour, minute, and second.
CURRENT_DATE () displays only the current year, month, and date.
What is a nonstandard string type?
1, TINYTEXT
2, the TEXT
3, MEDIUMTEXT
4, LONGTEXT
What are generic SQL functions?
CONCAT(A, B) – Concatenate two string values to create A single string output. Usually used to combine two or more fields into a single field.
2, FORMAT(X, D)- FORMAT the valid numbers from X to D.
3. CURRDATE(), CURRTIME()- Returns the current date or time.
4. NOW () – Returns the current date and time as a value.
MONTH (), DAY (), YEAR (), WEEK (), WEEKDAY () – Extracts the given data from the date value.
6. HOUR (), MINUTE (), SECOND () – Extract the given data from the time value.
7. DATEDIFF (A, B) — Determine the difference between two dates, usually used to calculate age
8. SUBTIMES (A, B) — Determine the difference between two times.
FROMDAYS (INT) – Converts integer days to date values.
Does MySQL support transactions?
By default, MySQL is in autoCOMMIT mode. All database updates are committed immediately, so MySQL does not support transactions by default.
SETAUTOCOMMIT=0 allows MySQL to use non-autoCOMMIT mode. In non-autocommit mode, you must COMMIT your changes using COMMIT, or ROLLBACK your changes with ROLLBACK.
22, what is the best field type to record currency in MySQL
The NUMERIC and DECIMAL types are implemented by MySQL as the same type, which is allowed by the SQL92 standard.
They are used to hold values whose precise accuracy is extremely important, such as data relating to money.
Precision and size can be (and usually are) specified when declaring a class to be one of these types.
Such as:
Salary is a DECIMAL (9, 2)
In this example, 9(precision) represents the total number of decimal places that will be used to store values, and 2(scale) represents the number of decimal places that will be used to store values.
So, in this case, the range of values that can be stored in the SALARY column is from -9999999.99 to 9999999.99.
MySQL > select * from ‘MySQL’;
The MySQL server controls user access to the database through the 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.
24. What can be the string type of a column?
The string type is:
1, SET
2, a BLOB
3, ENUM
4, CHAR
5, the TEXT
MySQL database as the storage of the release system, more than 50,000 increments a day, is expected to operate and maintain for three years, how to optimize?
1. Well-designed database structure allows partial data redundancy, avoids join query as far as possible, and improves efficiency.
2. Select the appropriate table field data type and storage engine, and add indexes appropriately.
MySQL database master separated from read and write.
4, find the regular table, reduce the amount of data in the single table to improve the query speed.
5. Add caching mechanisms such as memcached, APC, etc.
6, do not often change the page, generate static pages.
7. Write efficient SQL. SELECT * FROM TABEL SELECT field_1
field_2, field_3 FROM TABLE.
Space is limited, this article will first write this, there are 25 classic interview questions behind, mention a mouth, brush questions remember not to memorize, a lot of questions can be combined to understand, learn to draw inferitions, in order to make you get twice the result with half the effort, I wish brothers find their own satisfactory work as soon as possible. sky~