Knowledge summary for Mysql interview (with examples)

This article brings you a knowledge summary about Mysql interviews (with examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you. .

In this article, we will introduce some major points about mysql that are important but rarely known by us programmers during development (self-righteous views).

Three paradigms of database design:

First paradigm: Each column of the database table is an indivisible atomic data item, that is, the columns cannot be split. Second normal form: Based on the first normal form, each instance or record in the database table must be uniquely identifiable, that is, uniquely identified. Third normal form: Based on the second normal form, any non-primary attribute does not depend on other non-primary attributes, that is, it refers to the primary key. (Related recommendations: MySQL Tutorial)

View

A view is a virtual table and does not store data. It only contains the dynamic data of the statement when it is defined.

create view view_name as sql查询语句

Stored procedure

A collection of one or more sql statements, its advantages are (condensed: simple/safe/high performance):
1. Stored procedure Can achieve faster execution speed
2. Stored procedures allow standard components to be programmed.
3. Stored procedures can be written using flow control statements, which have strong flexibility and can complete complex judgments and more complex operations.
4. Stored procedures can be fully utilized as a security mechanism.
5. Stored procedures can reduce network traffic

delimiter 分隔符
create procedure|proc proc_name()
begin
    sql语句
end 分隔符
delimiter ；    --还原分隔符，为了不影响后面的语句的使用
默认的分隔符是；但是为了能在整个存储过程中重用，因此一般需要自定义分隔符（除\外）

What is the difference between stored procedures and functions?

Same points: stored procedures and functions are both designed to reproducibly execute SQL statements that operate the database collection.
1) Stored procedures and functions are compiled once and will be cached. The next time you use them, you will directly hit the compiled sql statement and do not need to reuse it. Reduce network interaction and reduce network access traffic.
Differences: The identifiers are different. The identifier of the function is function, and the stored procedure is procedure.
1) There is a return value in the function, and there must be a return value, while the process does not have a return value, but you can implement multiple parameters or return values ​​by setting the parameter type (in, out).
2) Stored functions need to be called using select, and stored procedures need to be called using call.
3) Select statements can be called in stored procedures, but select statements other than select..into cannot be used in functions.
4) Through in out parameters, process-related functions are more flexible and can return multiple results.

Triggers

perform specific operations when table data is changed. There are six types, including before and after operations of addition, deletion and modification.

create trigger trigger_name 
ALTER|BEFORE select|update|delete
on 表
for each row
trigger_stmt

Key points:

Only tables support triggers. Neither views nor temporary tables support triggers. Updates and overwrites are not supported. Modifications must be deleted first and then

logs are created.

Mysql mainly has four log files:

Error log: records problems when starting, running or stopping mysql;
Query log: records all msyql activities
Binary Log: Record all statements that have updated data
Slow query log: Record any query that is slow

##

The above is the detailed content of Knowledge summary for Mysql interview (with examples). For more information, please follow other related articles on the PHP Chinese website!