Written in front:
The series of articles are based on the principle of changing from simple to complex, and continue to be updated. Partners with good interview questions can also privately write to bloggers. Let's work together! Of course, please correct me if there is anything inappropriate in the article!
(The answer is later, it is recommended to look at the question first, then the answer!!!)
- A table with an ID auto-incrementing primary key. After inserting 17 records, delete the 15, 16, 17 records, restart Mysql, and insert a record. Is the ID of this record 18 or 15?
- What is the Heap table?
- Distinguish between CHAR_LENGTH and LENGTH?
- Please briefly describe the names of the four transaction isolation levels supported by InnoDB in Mysql and the differences between each level?
- What is the usage of ENUM in MySQL?
- How to define REGEXP?
- What is the string type of the column?
- What storage engine is used in MySQL?
- What is the MySQL driver?
- What does TIMESTAMP do on the UPDATE CURRENT_TIMESTAMP data type?
- What is the difference between primary key and candidate key?
- How to log in to MySQL using unix shell
- What is myisamchk used for?
- What are the method commands for MySQL database server performance analysis?
- How to control the size of the heap table?
- What is the difference between LIKE and REGEXP operations?
- What is the difference between MyISAM static and MyISAM Dynamic?
- What is a federated table?
- If a table has a column defined as TIMESTAMP, what will happen?
- When the column is set to AUTO INCREMENT, what happens if it reaches the maximum value in the table?
- How can I find which auto increment was allocated during the last insertion?
- What is the difference between BLOG and TEXT?
- What is the difference between mysql_fetch_array and mysql_fetch_object?
- How do we run batch mode in mysql?
- Where will the MyISAM table be stored and also provide its storage format?
- What tables are there in MySQL?
- MySQL data optimization
- Keywords for MySQL
- Storage engine
- What is ISAM?
- How does Mysql optimize DISTINCT?
- How to input characters as hexadecimal numbers?
- How many columns can be used to create an index?
- What is the difference between NOW() and CURRENT_DATE()?
- What kind of objects can be created using the CREATE statement?
- How many TRIGGERS are allowed in Mysql table?
- What is a non-standard string type?
- What are common SQL functions?
- Explain the access control list
- Does MYSQL support transactions?
- What field type is good for recording currency in mysql
- What are the mysql-related authority tables?
- What kinds of locks are there in Mysql?
- database backup
A table with an ID auto-incrementing primary key. After inserting 17 records, delete the 15, 16, 17 records, restart Mysql, and insert a record. Is the ID of this record 18 or 15?
(1) If the type of the table is MyISAM , then it is 18
Because the MyISAM table will record the maximum ID of the auto-incrementing primary key in the data file, the maximum ID of the auto-incrementing primary key will not be lost after restarting MySQL ;
( 2 ) If the type of the table is InnoDB , then it is 15
InnoDB tables only record the maximum ID of the self-incrementing primary key in memory, so restarting the database or OPTIMIZE the table will cause the maximum ID to be lost
What is the Heap table?
Heap table is a table that uses the MEMORY storage engine. The data of this table is stored in memory. Due to hardware problems or power failure, the data is easy to lose, so data can only be read from other data tables as temporary tables or read-only caches To use.
scenes to be used:
Operations involving transient, non-critical data, such as session management or caching. When the MySQL server is stopped or restarted, the data in the MEMORY table will be lost. Data that can be accessed quickly and with low latency is stored in the memory. The amount of data can be completely contained in memory without causing the operating system to swap out virtual memory pages.
Read-only or read-only data access mode.
BLOB or TEXT fields are not allowed.
Only comparison operators =, <, >, =>, = <can be used.
The HEAP table does not support AUTO_INCREMENT.
The index cannot be NULL.
Distinguish between CHAR_LENGTH and LENGTH
CHAR_LENGTH is the number of characters, and LENGTH is the number of bytes. The two data of Latin characters are the same, but the
They are different from Unicode and other encodings.
Please briefly describe the names of the four transaction isolation levels supported by InnoDB in Mysql and the differences between each level?
The four isolation levels defined by the SQL standard are:
read uncommited : read uncommitted data
read committed : dirty read, non-repeatable read
repeatable read : repeatable read
serializable : serial things
What is the usage of ENUM in MySQL?
ENUM is a string object, used to specify a set of predefined values, and can be used when creating a table.
Create table size(name ENUM('Smail,'Medium','Large');
How to define REGEXP?
REGEXP is pattern matching, where the matching pattern is anywhere in the search value.
What is the string type of the column?
The string types are:
What storage engine is used in MySQL?
The storage engine is called a table type, and data is stored in files using various technologies.
Capabilities and functions.
What is the MySQL driver?
The following are the drivers available in Mysql :
What does TIMESTAMP do on the UPDATE CURRENT_TIMESTAMP data type?
The TIMESTAMP column is updated with Zero when the table is created . As long as other fields in the table change, UPDATE
The CURRENT_TIMESTAMP modifier updates the timestamp field to the current time.
What is the difference between primary key and candidate key?
Each row of the table is uniquely identified by the primary key , and a table has only one primary key.
The primary key is also a candidate key. By convention, candidate keys can be designated as primary keys and can be used for any foreign key references.
How to log in to MySQL using unix shell
We can log in with the following command:
[mysql dir]/bin/mysql -h hostname -u
What is myisamchk used for?
It is used to compress MyISAM tables, which reduces disk or memory usage.
What are the method commands for MySQL database server performance analysis?
Show status, some variable values worth monitoring:
The traffic between Bytes_received and Bytes_sent and the server.
Com_* The command being executed by the server.
Created_* Temporary tables and files created during the query execution period.
Handler_* storage engine operation.
Select_* Different types of connection execution plans.
Sort_*Several sorting information.
Show profiles are used by MySql to analyze the resource consumption of the current session SQL statement execution
How to control the size of the heap table?
The size of the Heal table can be controlled by the Mysql configuration variable called max_heap_table_size .
What is the difference between LIKE and REGEXP operations?
The LIKE and REGEXP operators are used to represent ^ and %.
SELECT * FROM <tablename> WHERE * REGEXP "^b" ;
SELECT * FROM <tablename> WHERE * LIKE "%b" ;
What is the difference between MyISAM static and MyISAM Dynamic?
All fields on MyISAM Static have a fixed width.
The dynamic MyISAM table will have fields like TEXT, BLOB, etc. to accommodate data types of different lengths.
MyISAM Static is easier to recover in case of damage.
What is a federated table?
Federated tables allow access to tables located on other server databases.
If a table has a column defined as TIMESTAMP, what will happen?
Whenever the row is changed, the timestamp field will get the current timestamp.
When the column is set to AUTO INCREMENT, what happens if it reaches the maximum value in the table?
It will stop incrementing, and any further insertion will generate an error because the key has already been used.
How can I find which auto increment was allocated during the last insertion?
LAST_INSERT_ID will return the last value assigned by Auto_increment , and there is no need to specify the table name.
What is the difference between BLOG and TEXT?
BLOB is a binary object that can hold a variable amount of data. There are four types of BLOB −
They can only differ in the maximum length of value they can hold.
TEXT is a case-insensitive BLOB . Four TEXT types
They correspond to the four BLOB types and have the same maximum length and storage requirements. The only difference between BLOB and TEXT types is that BLOB values are case-sensitive when sorting and comparing, and TEXT values are not case-sensitive.
What is the difference between mysql_fetch_array and mysql_fetch_object?
mysql_fetch_array () - Return 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.
How do we run batch mode in mysql?
The following commands are used to run in batch mode:
Where will the MyISAM table be stored and also provide its storage format?
Each MyISAM table is stored on disk in three formats:
·" .Frm " file storage table definition
·Data files have " .MYD " ( MYData ) extension
Index files have " .MYI " ( MYIndex ) extension
What tables are there in MySQL?
There are 5 types of forms:
MySQL data optimization
Optimize data type
Avoid using NULL, NULL needs special treatment, most of the time you should use NOT NULL, or use a special value, such as 0, -1 as the default value.
Possible to use only a smaller field, MySQL after reading data from the disk is stored in memory , and then use cpu cycles and disk I / O read it, which means that the smaller the class data smaller type of space occupied.
Be careful with character set conversion
The character set used by the client or application may be different from the character set of the table itself. This requires MySQL to implicitly convert it during operation. In addition, it is necessary to determine whether the character set such as UTF- 8 supports multi-byte characters. They require more storage space.
Optimize count(my col) and count( ) *
The face of the sub-query, MySQL query optimization engine is not always the most effective, it is to what is often the cause of sub-query into a join query optimizer has been able to correctly handle join queries, and of course, the point to note is , To ensure that the connection column of the connection table (the second table) is indexed. On the first table, MySQL usually performs a full table scan relative to the query subset of the second table. This is a nested loop algorithm Part.
- Using UNION across multiple different databases is an interesting optimization method. UNION returns data from two unrelated tables, which means that there will be no duplicate rows, and the data must also be sorted. We Sort know it is very resource-intensive, especially not a sort of large tables.
- UNION ALL can greatly accelerate the speed, if you already know that your data will not include re- re-line, or you do not care whether there will be duplicate rows, in both cases UNION ALL is more suitable. In addition, some methods may be employed to avoid the application logic for Free duplicate rows, and so UNION ALL UNION returned result is the same , but not sorted UNION ALL.
Keywords for MySQL
alter table tableName add index (index field)
Primary key: primary key
Multiple columns: index index_name
Page level: Engine BDB. Lock an adjacent set of records at a time.
Table level: The engine MyISAM, understood as locking the entire table, can read and write at the same time. Row level: Engine
INNODB, a single row of records is locked, and the specified record is locked, so that other processes can still operate on other records in the same table. Table-level locking is fast, but there are many conflicts, and row-level conflicts are few, but the speed is slow
Storage engine that white is how to store data, how to index data stored and how to update, query the number of implementation technologies such data.
MyISAM: This engine is the earliest provided by mysql. This kind of engine can be divided into static MyISAM, dynamic MyISAM and compressed MyISAM three kinds:
Static MyISAM: If the length of each data column in the data table is fixed in advance, the server will automatically select this table type. Because the space occupied by each record in the data table is the same, the efficiency of this table access and update is very high. When data is corrupted, restore the complex work is relatively easy to do.
Dynamic MyISAM: If a varchar, text or BLOB field appears in the data table , the server will automatically select this table type. With respect to the static MyISAM, such a table memory storage space is relatively small, but due to the varying lengths of each record, so many changes data, the number of the data in the table data may be stored in a discrete memory, leading to efficiency decline. The same time, the memory may be a lot of debris. Therefore, this type of table should often be defragmented with the optimize table command or optimization tool.
Compress MyISAM: The two types of tables mentioned above can be compressed with the myisamchk tool . This type of table further reduce memory consumption, but not after such compression table longer be modified. In addition, because the data is compressed, this kind of table must be decompressed in advance when it is read .
However, no matter what kind of MyISAM table, it currently does not support transactions, row-level locks and foreign key constraints.
MyISAM Merge engine: This type is a variant of the MyISAM type. The merge table is to merge several identical MyISAM tables into a virtual table. Often used in logs and data warehouses.
InnoDB: InnoDB table types can be seen as further product updates for MyISAM, which mention for the transactions, row-level locking mechanism and a foreign key constraint functions.
memory(heap): This type of data table only exists in memory. It uses hash index, so the data access speed is very fast. Because it exists in memory, this type is often used in temporary tables .
archive: This type only supports select and insert statements, and does not support indexes.
Desc[ribe] tablename: View the structure of the data table.
show engines: command to display the storage engines supported by the current database
What is ISAM?
ISAM is referred to as index sequential access method for short. It was developed by IBM to store and retrieve data on secondary storage systems such as magnetic tape .
How does Mysql optimize DISTINCT?
DISTINCT is converted to GROUP BY on all columns and used in conjunction with the ORDER BY clause.
How to input characters as hexadecimal numbers?
If you want to enter the characters as hexadecimal numbers, you can enter the hexadecimal numbers with single quotes and the prefix ( X ), or
Use only ( Ox ) prefix to enter hexadecimal numbers.
If the expression context is a string, the hexadecimal number string will be automatically converted to a string.
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 is used to display the current year, month, date, hour, minute, and second.
CURRENT_DATE () only displays the current year, month and date.
What kind of objects can be created using the CREATE statement?
The following objects are created using the CREATE statement:
How many TRIGGERS are allowed in Mysql table?
Six triggers are allowed in the Mysql table, as follows:
What is a non-standard string type?
The following are non-standard string types:
What are common SQL functions?
Abs (num) to find the absolute value
floor (num) rounded down
ceil (num) rounded up
insert (s1,index,length,s2) replacement function
S1 represents the string to be replaced
s2 represents the string to be replaced
Index indicates the position to be replaced, starting from 1
Lebgth represents the length to be replaced
upper (str), ucase (str) changes the letter to uppercase
lower (str), lcase (str) change the letter to lowercase
left(str, length) returns the first length characters of the str string
right (str, length) returns the last length characters of the str string
substring(str, index, length) returns the str string with length characters starting from the index bit (index starting from 1)
reverse (str) output the str string in reverse order
curdate(), current_date() get the current date
curtime(), current_time() get the current date
now () to get the current date and time
datediff (d1, d2) The difference in days between d1 and d2
adddate (date, num) returns the date starting from date and num days afterwards
subdate (date, num) returns the date starting from date, num days before the date
Count (field) counts the total number of records according to a field (how many pieces of data are saved in the current database)
sum (field) calculates the sum of values in a field
avg (field) calculates the average value of a field
Max (field), min (field) to find the maximum or minimum value of a field
Explain the access control list
ACL (Access Control List) is a list of permissions associated with an object. This list is Mysql based server security model basis, it helps to eliminate the problem that users can not connect.
Mysql the ACL (also known as the authorization table) cached in memory. When a user attempts authentication or run command, Mysql will press Check ACL permissions and authentication information of a predetermined sequence of pictures.
Does MYSQL support transactions?
In the default mode, MYSQL is autocommit mode, all database updates will be immediately submitted, the order by default, mysql does not support transactions.
But if your MYSQL table type uses InnoDB Tables or BDB tables , your MYSQL can use transaction processing. Use SET AUTOCOMMIT=0 to allow MYSQL to be in non-autocommit mode.
In autocommit mode, you must use COMMIT to commit your changes, or use ROLLBACK to roll back your changes.
Examples are as follows:
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET [email protected] WHERE type=1;
What field type is good for recording currency in mysql
The 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 are these
When one of the types, the precision and scale can be ( and usually are ) specified;
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.
In ANSI/ISO SQL92 , the syntax DECIMAL(p) is equivalent to DECIMAL(p,0) .
Similarly, the syntax DECIMAL is equivalent to DECIMAL(p,0) , where the implementation is allowed to determine the value p . Mysql currently does not support any of these variants of DECIMAL/NUMERIC data types. This is generally not a serious problem, because the main benefit of these types comes from the ability to clearly control accuracy and scale. DECIMAL and NUMERIC values are stored as strings rather than as binary floating-point numbers in order to preserve the decimal precision of those values.
One character is used for each digit of the value, the decimal point ( if scale>0) and the " - " sign ( for negative values ) . If scale is 0 , DECIMAL and NUMERIC values do not include a decimal point or fractional part.
The maximum range of DECIMAL and NUMERIC values is the same as 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 of the decimal point exceeds a specified scale values of the bits allowed, according to the value of scale rounded fifty-four into.
When a DECIMAL or NUMERIC column is assigned a value whose size exceeds the range implied by the specified ( or default) precision and scale , Mysql stores the corresponding endpoint value representing that range.
What are the mysql-related authority tables?
Mysql server controls the user's 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 privilege tables are user, db, table_priv, columns_priv and host.
What kinds of locks are there in Mysql?
MyISAM supports table locks, InnoDB supports table locks and row locks, the default is row locks
Table-level locks: low overhead, fast locking, and no deadlocks. Large locking granularity, the highest probability of lock conflicts, and the amount of concurrency
Row-level locks: high overhead, slow locking, and deadlocks. The lock strength is small, the probability of lock conflicts is small, and the concurrency is the highest
Must be logged in
Export the entire database:
mysqldump -u username -p database name > exported file name
Export a table:
mysqldump -u username -p database name table name > exported file name
Export a database structure:
mysqldump -u dbuser -p -d --add-drop-table
-d No data --add-drop-table adds a drop table before each create statement Regarding the MySQL interview questions, I reprinted a more classic one, which is worth recommending! Very basic and comprehensive summary: MySQL Interview Classic 100 Questions (Collection Edition, with answers)