1. Similarities between DROP, DELETE and TRUNCate are as follows: TRUNCate, DELETE without a WHERE clause, and DROP all delete data in a table

Differences: TRUNCate deletes table data and resets ids. Starting from 1, DELETE deletes only records. Drop can be used to delete a table or database and release all space occupied by the table. A drop statement removes the constrains and triggers from the table, and stored procedures/functions that depend on the table remain but are in the invalid state. In terms of speed, drop> TRUNCate > DELETE: Delete is used to delete some rows, drop is used to delete tables, and all data is deleted to keep tables. If there is no transaction, truncate is used. If it is related to a transaction or if you want to trigger, use delete. Delete is a DML statement and is not automatically committed. Drop and TRUNCATE are DDL statements and are automatically submitted after execution.

The NUMERIC and DECIMAL types are implemented as the same type by mysql, 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.

DECIMAL and NUMERIC values are stored as strings rather than as binary floating-point numbers to preserve the DECIMAL precision of those values.

Innodb supports row-level locking while MyISam supports table-level locking innoDB supports MVCC while MyISAM does not support innoDB supports foreign keys. InnoDB does not support full-text indexing, while MyISAM does. InnoDB cannot copy tables to another machine by copying table files directly. Myisam supports multiple row formats for InnoDB tables. Myisam does not support InnoDB as indexed organized tables and MyISAM as heaped tables

4. Transaction Features and Meaning Four basic elements for proper execution of database transaction transanction: ACID, Atomicity, Consistency, Isolation, persistence.

Atomicity: All operations in the entire transaction are either complete or not complete, and cannot be stopped at some intermediate stage. If a transaction fails during execution, it will be rolled back to the state before the transaction began, as if the transaction had never been executed.

Consistency: Database integrity constraints are not broken before and after a transaction.

Isolation: The isolation state performs transactions as if they were the only operation performed by the system at a given time. If you have two transactions, running at the same time and performing the same function, the isolation of the transaction ensures that each transaction is considered by the system to be the only one using the system. This property is sometimes called serialization. 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 a transaction completes, changes made to the database by that transaction persist in the database and are not rolled back.

5. What are the features of MYSQL compared with other databases? MySQL is a small relational database management system developed by MySQL AB, a Swedish company that has been acquired by Sun. Supports various operating systems such as FreeBSD, Linux, MAC, and Windows. Compared with other large databases such as Oracle, DB2, and SQL Server, it has weaker functions. Its characteristics are:

  • It can process large data with tens of millions of records;
  • Support common SQL statement specifications;
  • Portable line height, simple and compact installation;
  • Good operation efficiency, with rich information network support;
  • Debugging, management, optimization is simple (compared with other large databases).

6. What does a view do? Can a view be changed? Views are virtual tables that, unlike tables that contain data, contain only queries that dynamically retrieve data when used; Does not contain any columns or data. Views simplify complex SQL operations, hide details, and protect data. Once views are created, they can be exploited in the same way as tables.

Views cannot be indexed, have associated triggers or default values, and order BY is overwritten if the view itself has an order BY.

A trigger is very similar to a stored procedure in that it is also a set of SQL statements. The only difference is that triggers cannot be called with an EXECUTE statement. Instead, they trigger (activate) execution automatically when the user executes a Transact-SQL statement.

A trigger is a stored procedure that is executed when data in a specified table is modified. Triggers are typically created to enforce 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 differ from stored procedures in that they are executed primarily through event execution, whereas stored procedures can be invoked directly through stored procedure names. When operations such as UPDATE, INSERT, or DELETE are performed on a table, SQL Server automatically executes SQL statements defined by triggers to ensure that data is processed in accordance with the rules defined by these SQL statements.

8. What does an index do? And what are its advantages and disadvantages? An index is a special query table that a database search engine can use to speed up the retrieval of data. It is very similar to the real life book catalog, you do not need to search the entire book to find the desired data.

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

9. Difference between Union and Union all Union will filter out duplicate records after table join. Therefore, it will sort the result set generated after table join, delete duplicate records and return the result. Union all displays duplicate results, simply combining the two results and returning them. Therefore, the efficiency is higher than that of union, and union all is used under the condition that no duplicate data is guaranteed.

10. What are the parts of SQL? What are the operation keys for each section? SQL language includes data definition (DDL), data manipulation (DML), data control (DCL) and data query (DQL).

  • Data definitions: Create Table,Alter Table,Drop Table, Craete/Drop Index, etc
  • Data manipulation: Select, INSERT,update,delete,
  • Data control: Grant, REVOKE
  • Data query: select