preface
As one of the most popular relational database management systems, the importance of MySQL does not need me to say more. It is almost a must to ask questions in interviews, and even if you are in a larger company, you will ask them very deeply.
So now almost ready or not ready at all but want to enter Dachang friends, you can take a look at me to sort out these classic interview questions about MySQL, whether you can answer it?
PS: Full page test questions PDF and MySQL learning notes
1. What types of locks are available in MySQL?
1, table level lock: low overhead, lock fast; There are no deadlocks; With large lock granularity, the probability of lock conflict is the highest and the concurrency is the lowest.
2. Row level locking: high overhead, slow locking; There will be a deadlock; The lock granularity is the smallest, the probability of lock conflict is the lowest, and the degree of concurrency is the highest.
3, page lock: cost and lock time between table lock and row lock; There will be a deadlock; The locking granularity is between table lock and row lock, and the concurrency is moderate.
2. What are the different tables in MySQL?
There are 5 types of tables:
1, MyISAM
2, Heap
3, the Merge
4, INNODB
5, ISAM
3. Describe the difference between MyISAM and InnoDB in MySQL database
MyISAM:
Transactions are not supported, but each query is atomic;
Support table level locking, that is, each operation is to lock the entire table;
The total number of rows in the table;
A MyISAM table has three files: an index file, a table structure file, and a data file.
The data field of the index file stores the pointer to the data file. Secondary indexes are basically the same as primary indexes, but secondary indexes do not have to guarantee uniqueness.
InnoDb:
ACID supported transactions, which support four isolation levels of transactions;
Support for row-level locking and foreign key constraints: therefore write concurrency can be supported;
Do not store the total number of rows:
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 more than one file), or multiple (set to independent table empty, table size is limited by the operating system file size, generally 2G), and is limited by the operating system file size;
The primary key index uses a clustered index (the data field of the index stores the data file itself), and the data field of the secondary index stores the primary key value.
Therefore, to search data from the secondary index, it is necessary to first find the primary key value through the secondary index, and then access the secondary index;
It is best to use a self-incrementing primary key to prevent large adjustments to files when inserting data to maintain a B+ tree structure.
What are the four transaction isolation levels InnoDB supports in MySQL, and what are the differences between them?
The four isolation levels defined by the SQL standard are:
1, Read Uncommited: To read uncommitted data
2, Read committed. 2, Read committed
3. Repeatable read
4, Serializable
What is the difference between a CHAR and a VARCHAR?
1, The CHAR and VARCHAR types differ in storage and retrieval
When CHAR values are stored, they are padded with whitespace to a specific length. When retrieving CHAR values, the trailing whitespace is removed.
What is the difference between a primary key and a candidate key?
Each row of the table is uniquely identified by a primary key, and a table has only one primary key.
Primary keys are also candidate keys. By convention, a candidate key can be designated as a primary key 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.
What happens if a table has a column defined as TIMESTAMP?
Each time a row is changed, the timestamp field gets the current timestamp.
When the column is set to AUTO INCREMENT, what happens if the maximum value is reached in the table?
It will stop incrementing, and any further inserts will produce an error because the key has already been used.
How do I find out which autoincrement was assigned on the last insert?
LAST_INSERT_ID will return the last value allocated by Auto_increment and need not be referred 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 ways:
SHOW INDEX FROM <tablename>;
10, What is the meaning of % and _ in the LIKE statement?
% corresponds to zero or more characters, and _ is just one character in a LIKE statement.
How do I convert between UNIX and MySQL timestamps?
UNIX_TIMESTAMP is a command that converts a MySQL timestamp to a UNIX timestamp
FROM_UNIXTIME is the command to convert from UNIX timestamp to MySQL timestamp
11. What is the column comparison operator?
Use the =, <>, <=, <, > =, >, <<, >>, <=>, AND, OR OR LIKE operators in column comparisons of 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, and TEXT values are case insensitive.
What is the difference between MySQL_fetch_array and MySQL_fetch_object?
Here is the difference between MySQL_fetch_array and MySQL_fetch_object:
MySQL_fetch_array () – returns the result row as an associative array or a regular array from the database.
MySQL_fetch_object – Returns the result row from the database as an object.
14. Where will MyISAM tables be stored and in what format will they be stored?
Each MyISAM table is stored on disk in one of three formats:
· “.frm “file stores table definitions
· The data file has the “.myd “(MYData) extension
The index file has the “.myi “(MyIndex) extension
15, How to optimize DISTINCT MySQL?
DISTINCT is converted to GROUP BY on all columns and is used in conjunction with the ORDER BY clause.
SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
16. How do I display the first 50 lines?
In MySQL, the first 50 rows are displayed using the following code query:
SELECT*FROM
LIMIT 0, 50;
How many columns can be used to create an index?
Any standard table can create up to 16 index columns.
What is the difference between NOW () and CURRENT_DATE ()?
The NOW () command displays the current year, month, date, hour, minute, and second.
CURRENT_DATE () only displays the current year, month, and date.
What is a non-standard string type?
1, TINYTEXT
2, the TEXT
3, MEDIUMTEXT
4, LONGTEXT
20, What is a general purpose SQL function?
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)- Formats a number X to a significant number D.
CurrDate (), currTime ()- Returns the current date or time.
4, NOW () – Returns the current date and time as a value.
MONTH (), DAY (), YEAR (), WEEK (), WEEKDAY () — Extract the given data from the date value.
6, HOUR (), MINUTE (), SECOND () – Extract the given data from the time value.
7, Datediff (A, B) — Determining the difference between two dates, usually used to calculate age
8, SUBTIMES (A, B) — Determine the difference between the two times.
9, fromDays (INT) — Converts an integer number of days to a date value.
21. Does MySQL support transactions?
By default, MySQL is in AUTOCOMMIT mode, and all database updates are committed immediately, so by default, MySQL does not support transactions.
However, if your MySQL table type is using InnoDB Tables or BDB Tables, your MySQL table can use transactions. Using setAUTOCOMMIT =0 will enable MySQL to operate in non-AUTOCOMMIT mode. In non-AUTOCOMMIT mode, you must either COMMIT your changes using COMMIT or roll your changes back and forth using ROLLBACK.
22, What is the best field type for recording currency in MySQL
Numeric and Decimal types are implemented by MySQL as the same type, which is allowed in the SQL92 standard.
They are used to store values whose accuracy is extremely important, such as data related to money.
When declaring a class to be one of these types, the precision and size can be (and usually is) specified.
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 the value, and 2(scale) represents the number of decimal places that will be used to store the value.
Therefore, in this case, the range of values that can be stored in the SALARY column is from -9999999.99 to 9999999.99.
23, MySQL permissions on the table are what several?
The MySQL server controls user access to the database through the permission table. The permission table is stored in the MySQL database and initialized by the MySQL_install_db script. These access tables are user, db, table_priv, columns_priv, and host.
24. What can the string type of a column be?
The string type is:
1, SET
2, a BLOB
3, ENUM
4, CHAR
5, the TEXT
25, MySQL database for release system storage, a day of more than 50,000 incremental, estimated operation and maintenance for three years, how to optimize?
1. A well-designed database structure allows partial data redundancy and avoids join query as far as possible to improve efficiency.
2. Select the appropriate table field data type and storage engine, and add indexes appropriately.
3. MySQL library master is separated from read and write.
4. Find regular tables to reduce the amount of data in a single table and 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 first wrote this, there are 25 classic interview questions behind, mention a mouth, brush the questions to remember not rote, many questions can be combined to understand, learn to draw analogy, in order to let you get twice the result with half the effort, I wish brothers as soon as possible to find their own satisfactory job. sky~