MySQL Face test summary

1. What does a trigger do?

A: A trigger is a special stored procedure that is executed primarily by triggering an event. It can enforce constraints to maintain the integrity and consistency of data, and can track operations within the database without permitting unauthorized updates and changes. Can be associated with a level operation. For example, a trigger on a table contains data operations on another table, and that action causes the table trigger to be triggered.

2. What is a stored procedure? Using what to invoke?

A: A stored procedure is a precompiled SQL statement, and the advantage is that it allows for a modular design, meaning that it is created once and can be called more than once in the program. If an operation needs to execute multiple times of SQL, using a stored procedure is faster than simply SQL statement execution. You can invoke a stored procedure with a command object.

3. The role of the index? And what are the pros and cons of it?

A: The index is a special query table, the database search engine can use it to speed up the retrieval of data. It is similar to the catalogue of real-life books, and you can find the data you want without having to query the entire contents of the book. 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 also increases the size of the database.

4. What is a memory leak?

A: Generally what we call a memory leak refers to a leak in heap memory. Heap memory is a program that is allocated to it from the heap, arbitrarily sized, and then shown to free memory after it is exhausted. When an application creates an object with the keyword new and so on, it allocates a piece of memory from the heap, the program calls free or the delete frees the memory after use, or the memory is not used, we say that the memory is compromised.

5. What is a transaction? What is a lock?

A: A transaction is a grouping of SQL statements that are bound together as a logical unit of work, and if any one statement fails then the entire operation fails, and the operation is rolled back to the pre-operation state, or there is a node on it. A transaction can be used to ensure that it is either executed or not executed. To consider a set of statements as transactions, you need to pass acid testing, that is, atomicity, consistency, isolation, and persistence.

Lock: In so the DBMS, the lock is the key to implement the transaction, the lock can guarantee the integrity and concurrency of the transaction. Like a real-life lock, it enables certain data owners to be unable to use certain data or structures for a certain period of time. Of course, locks are also divided into levels.

6. What does a view mean? What is a cursor?

A: A view is a virtual table that has the same functionality as a physical table. You can add, change, check, manipulate, and attempt a view that is usually a subset of rows or columns that have a table or multiple tables. Changes to the view do not affect the base table. It makes it easier for us to get data, compared to multiple table queries.

Cursor: is a query out of the result set as a unit to effectively handle. A cursor can be set to a specific row in that cell, retrieving one or more rows from the current row of the result set. You can make modifications to the current row of the result set. Cursors are generally not used, but cursors are important when you need to process the data individually.