Shulou(Shulou.com)06/01 Report--

This article mainly introduces "what problems to think about by learning MySQL". In daily operation, I believe that many people have doubts about what problems to think about by learning MySQL. The editor has consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what questions to learn MySQL to think about"! Next, please follow the editor to study!

Should there be a primary key in each table?

Not every table requires a primary key. In general, a primary key is required if a join operation is performed between multiple tables. Therefore, there is no need to establish a primary key for each table, and in some cases it is best not to use the primary key.

Characteristics and Choice between CHAR and VARCHAR

The difference between CHAR and VARCHAR: CHAR is a fixed-length character, VARCHAR is a variable-length character, CHAR automatically removes trailing spaces from inserted data, and VARCHAR does not delete trailing spaces.

CHAR is of fixed length, so its processing speed is faster than that of VARCHAR, but its disadvantage is a waste of storage space. So CHAR type can be used for those with small storage but speed requirements, whereas VARCHAR type can be used to achieve it.

The impact of the storage engine on the selection of CHAR and VARCHAR:

For the MyISAM storage engine: it is best to use fixed-length data columns instead of variable-length data columns. This makes the entire table static, making data retrieval faster and trading space for time.

For InnoDB storage engine: use variable length data columns, because the storage format of InnoDB data table is not divided into fixed length and variable length, so using CHAR is not necessarily better than using VARCHAR, but because VARCHAR is stored according to the actual length, it saves more space, so it is better for disk Imax O and the total amount of data storage.

Can DISTINCT be applied to all columns?

In the query results, if you need to sort the columns in descending order, you can use DESC, which can only sort the columns in front of them in descending order. For example, to sort multiple columns in descending order, you must add the DESC keyword after the column name of each column. Unlike DISTINCT, DISTINCT cannot be partially used. In other words, the DISTINCT keyword applies to all columns, not just the first specified column following it. For example, if you query three fields, which are different from each other, all records will be queried.

Try to use short indexes

Index fields of string type and specify a prefix length if possible. For example, if you have a column of CHAR, and if most of the values are unique within the first 10 or 30 characters, you don't need to index the entire column. The short index can not only improve the query speed, but also save disk space and reduce the Icano operation.

What is the difference between MySQL stored procedures and functions?

In essence, they are stored programs. Functions can only return a single value or table object through a return statement; stored procedures are not allowed to execute return, but can return multiple values through the out parameter. Function restrictions are more, can not use temporary tables, can only use table variables, there are some functions are not available, and so on; while stored procedures have relatively few restrictions. Functions can be embedded in SQL statements and can be called as part of a query statement in SELECT statements, while stored procedures are generally executed as a separate part.

Can other stored procedures be called in a stored procedure?

Special attention should be paid when using triggers

When using triggers, it should be noted that for the same table, only one trigger can be created for the same event. For example, if a BEFORE INSERT trigger is created for table account, then if another BEFORE INSERT trigger is created for table account, MySQL will report an error. In this case, only AFTER INSERT or BEFORE UPDATE type triggers can be created on table account. Flexible use of triggers will save a lot of trouble for operation.

At this point, the study of "what problems to think about by learning MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.