Interview must ask! 10 classic MySQL interview questions

1. The difference between drop, delete and truncate

Same point:

Truncate, delete without where clause, and drop will delete the data in the table

difference:

• truncate will clear the table data and reset the id starting from 1, delete only deletes the record, drop can be used to delete the table or database and release all the space occupied by the table
• Truncate and delete only delete data but not the structure of the table. The drop statement will delete the constraints, triggers, and stored procedures/functions that depend on the table structure, but will remain in the invalid state.
• Generally speaking in terms of speed: drop> truncate> delete
• In use, delete some rows of data if you want to delete them, drop if you want to delete the table, and delete all data if you want to keep the table. If it is not related to the transaction, just use truncate. If it is related to a transaction, or if you want to trigger a trigger, still use delete.
• delete is a DML statement and will not be automatically submitted. Drop/truncate are all DDL statements, which will be automatically submitted after execution.

2. What field type is good for recording currency in mysql

The NUMERIC and DECIMAL types are MySQL , which is allowed in the SQL92 standard. They are used to store values whose accuracy is extremely important, such as data related to money.

DECIMAL and NUMERIC values are stored as strings, not as binary floating-point numbers in order to preserve the decimal precision of those values.

3. The difference between myisam and innodb in MySQL

• InnoDB supports transactions, but MyISAM does not support transactions
• InnoDB supports row-level locks, while MyISAM supports table-level locks
• InnoDB supports MVCC, but MyISAM does not
• InnoDB supports foreign keys, but MyISAM does not
• InnoDB does not support full-text indexing, while MyISAM does.
• InnoDB cannot copy tables to another machine by directly copying table files, myisam supports
• InnoDB tables support multiple row formats, myisam does not support
• InnoDB is an index-organized table, myisam is a heap table

4. Four characteristics and meanings of affairs

Four basic elements for the correct execution of database transaction transanction: ACID, Atomicity, Correspondence, Isolation, and Durability.

Atomicity: All operations in the entire transaction are either completed or not completed. It is impossible to stop at a certain link in the middle. If an error occurs during the execution of the transaction, it will be rolled back (Rollback) to the state before the transaction started, as if the transaction had never been executed.

Consistency: Before the start of the transaction and after the end of the transaction, the integrity constraints of the database have not been destroyed.

Isolation: Perform transactions in an isolated state, making them seem to be the only operation performed by the system in a given time. If there are two transactions that run at the same time and perform the same function, the isolation of the transaction will ensure that each transaction in the system thinks that only the transaction is using the system. This property is sometimes called serialization. In order to prevent confusion between transaction operations, requests must be serialized or serialized so that only one request is used for the same data at a time.

Persistence: After the transaction is completed, the changes made by the transaction to the database are persisted in the database and will not be rolled back.

5. What are the characteristics of MYSQL compared to other databases?

MySQL is a small relational database management system, development of the Swedish MySQL AB company, now has been acquired by Sun, support FreeBSD, Linux, MAC, Windows and other operating systems and other large databases such as Oracle, Compared with DB2 and SQL Server, the functions are slightly weaker. Its characteristics are:

• Can process large data with tens of millions of records;
• Support the common SQL statement specification;
• Portable row height, simple and compact installation;
• Good operating efficiency, network support with rich information;
• Simple debugging, management and optimization (compared to other large databases).

6. The role of the view, can the view be changed?

Views are virtual tables, which are not the same as tables that contain data. Views only contain queries that dynamically retrieve data when used; they do not contain any columns or data. Using views can simplify complex SQL operations, hide specific details, and protect data; after views are created, they can be used in the same way as tables.

The view cannot be indexed, nor can it have associated triggers or default values. If there is an order by in the view itself, the order by of the view will be overwritten again.

7. The difference between stored procedures and triggers

Triggers are very similar to stored procedures. Triggers are also a set of SQL statements. The only difference between the two is that the trigger cannot be called with the EXECUTE statement, but is automatically triggered (activated) when the user executes the Transact-SQL statement.

A trigger is a stored procedure that is executed when the data in a specified table is modified. Usually by creating triggers to enforce the referential integrity and consistency of logically related data in different tables. Since users cannot bypass triggers, they can be used to enforce complex business rules to ensure data integrity.

Triggers are different from stored procedures. Triggers are mainly executed by event execution triggers, while stored procedures can be directly called by the name of the stored procedure. When operations such as UPDATE, INSERT, and DELETE are performed on a table, SQLSERVER will automatically execute the SQL statements defined by the trigger to ensure that the data processing must comply with the rules defined by these SQL statements.

8. The role of the index? And what are its advantages and disadvantages?

An index is a special query table, which can be used by the search engine of the database to accelerate the retrieval of data. It is very similar to a catalog of books in real life, and you can find the data you want without querying the entire book.

Indexes can be unique, creating an index allows you to specify a single column or multiple columns. The disadvantage is that it slows down the speed of data entry and also increases the size of the database.

9. The difference between union and union all

Union will filter out the duplicate records after the table join, so after the table join, the result set will be sorted, the duplicate records will be deleted and the result will be returned. Union all will display duplicate results, just simply merge the two results and return. So the efficiency is higher than union, and union all is used when there is no duplicate data.

10. Which parts of the SQL language include? What are the operational keywords in each part?

SQL language includes four parts: data definition (DDL), data manipulation (DML), data control (DCL) and data query (DQL).

• Data definition: Create Table, Alter Table, Drop Table, Craete/Drop Index, etc.
• Data manipulation: Select, insert, update, delete,
• Data control: grant, revoke
• Data query: select

Source: blog.csdn.net/Black1499/article/details/84945702