Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about the specific knowledge of MySQL database, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Database technology is a necessary artifact of salary increase, whether it is operation and maintenance, development, testing, or architect, so what do you want to learn about learning database and MySQL all the time?

1. How to master MySQL quickly?

Cultivate interest

Interest is the best teacher. No matter what knowledge you learn, interest can greatly improve your learning efficiency. Of course, learning MySQL 5.6 is no exception.

Tamp the foundation

The technology in the computer field places great emphasis on the foundation, which may not be realized at the beginning of the study. with the deepening of the application of technology, only with a solid foundation can we go faster and further on the road of technology. For the learning of MySQL, SQL statement is the most basic part, and many operations are realized through SQL statement. Therefore, in the process of learning, readers should write more SQL statements, for the same function, use different implementation statements to complete, so as to deeply understand the differences.

Learn new knowledge in time

With the correct and effective use of search engines, you can find a lot of knowledge about MySQL 5.6. At the same time, with reference to the ideas of others to solve problems, we can also learn from the experience of others and obtain the latest technical materials in a timely manner.

Multi-practice operation

The database system is highly operable and needs more hands-on operation. In the process of actual operation, we can find the problems and think about the methods and ideas to solve the problems. Only in this way can we improve the operational ability of the actual combat.

2. How to choose the type of server?

The meanings of the parameters in the MySQL server configuration window are as follows.

[Server Configuration Type] this option sets the type of server. Click the down button to the right of the option, and you can see that it includes 3 options.

The specific meanings of the three options are as follows:

Development Machine (development machine): this option represents a typical personal desktop workstation. Suppose there are multiple desktops running on the machine

Applications. Configure the MySQL server to use the least system resources.

Server Machine (server): this option represents the server, and the MySQL server can run with other applications, such as FTP, email, and web servers. The MySQL server is configured to use the appropriate proportion of system resources.

DedicatedMySQL Server Machine (dedicated MySQL server): this option represents a server that runs only the MySQL service. Assume that no other applications are running. The MySQL server is configured to use all available system resources. As a beginner, it is recommended to choose the [DevelopmentMachine] (developer machine) option, which takes up less system resources.

3. How to choose a storage engine

Different storage engines have their own characteristics to meet different needs, as shown in the following table. To make a choice:

The first thing to consider is what different functions each storage engine provides. InnoDB is a good choice if you want to provide transaction security (ACID compatibility) with commit, rollback and crash resilience, and require concurrent control. If the data table is mainly used for inserting and querying records, the MyISAM engine can provide higher processing efficiency; if the data is stored temporarily, the amount of data is small, and high data security is not needed, you can choose the Memory engine that saves the data in memory, which is used as a temporary table in MySQL to store the intermediate results of the query. If there are only INSERT and SELECT operations, you can choose the Archive engine, which supports highly concurrent insert operations, but is not transaction-safe by itself. The Archive storage engine is very suitable for storing archived data. For example, log messages can be stored using the Archive engine.

To choose which engine to use flexibly according to needs, multiple tables in a database can use different engines to meet a variety of performance and practical needs.

Using the appropriate storage engine will improve the performance of the entire database.

4. How to view the default storage engine?

Use the SHOW ENGINES statement to view all storage engines in the system, including the default storage engine. You can see that there are five storage engines in the current database system, and the default is MyISAM. You can also use a direct way to view the default storage engine. The execution result directly shows that the current default storage engine is MyISAM.

5. Table deletion should be done with caution

The table delete operation deletes the definition of the table along with the data in the table, and MySQL does not have any confirmation message when performing the delete operation, so you should be careful when performing the delete operation. Before deleting the table, it is best to back up the data in the table, so that when the operation is wrong, the data can be restored to avoid irreparable consequences.

Similarly, when using ALTER TABLE for basic table modification operations, you should also ensure that the data is fully backed up before performing the operation, because the changes to the database are irrevocable, and if you add an unwanted field, you can delete it; similarly, if you delete a desired column, all data below that column will be lost.

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.

7. Can each table choose a storage engine at will?

Foreign key constraints (FOREIGN KEY) cannot be used across engines. MySQL supports a variety of storage engines, and each table can specify a different storage engine, but it should be noted that foreign key constraints are used to ensure the referential integrity of data. If you need to associate foreign keys between tables, but specify different storage engines, foreign key constraints cannot be created between these tables. Therefore, the choice of storage engine is not completely random.

8. Does the field value with AUTO_INCREMENT constraint start at 1?

By default, in MySQL, the initial value of AUTO_INCREMENT is 1, and the field value is automatically incremented by 1 for each new record. When you set the self-increment attribute (AUTO_INCREMENT), you can also specify the value of the self-increment field of the first inserted record, so that the self-increment field value of the newly inserted record increases from the initial value. For example, if you insert the first record in tb_emp8 and specify an id value of 5, the id value of later inserted records will increase from 6. When adding unique primary key constraints, you often need to set fields to automatically add properties.

9. The difference between TIMESTAMP and DATATIME

TIMESTAMP and DATETIME in addition to storage bytes and the range of support, there is a biggest difference is that: DATETIME in the storage of date data, according to the actual input format, that is, what is entered is stored, regardless of the time zone; while the timestamp value is stored in UTC (World Standard time) format, the storage time to the current time zone conversion, retrieval and then converted back to the current time zone. That is, when querying, the time value displayed is different according to the current time zone.

10. What are the methods and techniques for selecting data types?

MySQL provides a large number of data types, and in order to optimize storage and improve database performance, the most accurate type should be used in all cases. That is, of all the types that can represent the values of the column, this type uses the least storage.

Integer and floating point number

If you do not need a decimal part, use an integer to save the data; if you need to represent a decimal part, use a floating point class. For floating-point data columns, the stored value rounds the decimal places defined by the column. For example, if the value of a column has a norm of 1-999999, MEDIUMINT UNSIGNED is the best type if you use integers; if you need to store decimals, use the FLOAT type. Floating-point types include FLOAT and DOUBLE types. The DOUBLE type is more precise than the FLOAT type, so if high storage precision is required, the DOUBLE type should be selected.

Floating point number and fixed point number

The advantage of floating-point FLOAT,DOUBLE over fixed-point DECIMAL is that floating-point numbers can represent a larger range of data when the length is fixed. However, because floating-point numbers are prone to errors, it is recommended to use DECIMAL to store them when they require high accuracy. DECIMAL is stored as a string in MySQL and is used to define data that requires high accuracy, such as currency. In data migration, float (MForce D) is a non-standard SQL definition, and database migration may cause problems, so it is best not to use it in this way. The other two floating-point numbers are also prone to problems when performing subtraction and comparison operations, so you must be careful when calculating. For numeric comparisons, it is best to use the DECIMAL type.

Date and time type

MySQL has many data types for different kinds of dates and times, such as YEAR and TIME. If you only need to record the year, use the YEAR type; if you only record the time, you only need to use the TIME type. If you need to record both the date and time, you can use the TIMESTAMP or DATETIME type. Because the value range of the TIMESTAMP column is smaller than that of DATETIME, it is best to use DATETIME for dates with a large storage range. TIMESTAMP also has an attribute that DATETIME does not have. By default, when a record is inserted but the TIMESTAMP column value is not specified, MySQL sets the TIMESTAMP column to the current time. Therefore, when you need to insert a record and insert the current time, it is convenient to use TIMESTAMP, and TIMESTAMP is more efficient than DATETIME in space.

Characteristics and Choice between CHAR and VARCHAR

The difference between CHAR and VARCHAR:

CHAR is a fixed-length character and 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.

ENUM and SET

ENUM can only take a single value, and its data list is an enumerated collection. Its list of legal values allows a maximum of 65,535 members. Therefore, you can use ENUM when you need to choose one of the multiple values. For example, the gender field can be defined as an ENUM type, and only one value can be taken from 'male' or 'female' at a time. SET is preferable to multiple values. Its list of legal values allows a maximum of 64 members.

The empty string is also a legal set value. When you need to take multiple values, it is suitable to use the SET type, for example: to store a person's interests, it is best to use the SET type. The values of ENUM and SET appear as strings, but internally, MySQL stores them as numeric values.

BLOB and TEXT

BLOB is a binary string and TEXT is a non-binary string, both of which can store large amounts of information. BLOB mainly stores pictures, audio information, etc., while TEXT can only store plain text files. The use of the two should be distinguished. Click here to summarize 55 MYSQL interview questions for BAT interview.

11. How to use special characters in MySQL?

Symbols such as single quotation marks ('), double quotation marks ("), backslash (), etc., cannot be entered and used directly in MySQL, otherwise they will produce unexpected results. In MySQL, these special characters are called escape characters, and they need to be entered with a backslash symbol ('\'), so you should enter (\') or (\ ") when using single and double quotes, and enter (\) when entering a backslash. Other special characters include carriage return (\ r), newline character (\ n), tab character (\ tab), backspace character (\ b), etc. When inserting these special characters into the database, be sure to escape.

12. Can files be stored in MySQL?

The BLOB and TEXT field types in MySQL can store files with large amounts of data, and you can use these data types to store images, sounds, or large-volume text content, such as web pages or documents. Although you can use BLOB or TEXT to store large amounts of data, the processing of these fields can degrade database performance. If it is not necessary, you can choose to save only the path to the file.

13. How to perform case-sensitive string comparison in MySQL?

On the Windows platform, MySQL is not case-sensitive, so the string comparison function is not case-sensitive either. If you want to perform a case-sensitive comparison, you can add the BINARY keyword before the string. For example, by default, the result returned by'a 'is equal to 1, and if the BINARY keyword is used, the BINARY'a'='A' result is zero. In the case of case sensitivity,'a 'and' A 'are not the same.

14. How to get some date or time values such as year, month, day and so on from the date-time value?

In MySQL, the date-time value is stored in the data table as a string, so you can use the string function to intercept different parts of the date-time value. For example, a field named dt has a value of "2010-10-01 12:00:30". If you only need to get the annual value, you can enter LEFT (dt, 4), so you get a substring with a length of 4 on the left side of the string, that is, the value of the YEAR part. If you want to get the month value, you can enter MID (dt,6,2), which starts with the sixth character of the string, and the substring of length 2 is exactly the month value in dt. In the same way, the reader can calculate and get the corresponding values based on the location of other dates and times.

15. How to change the default character set?

The CONVERT () function changes the default character set of the specified string. In the first chapter, you will be introduced to the installation and configuration of MySQL using the GUI graphical installation and configuration tool. One of the steps is to select the default character set of MySQL. However, if you only change the character set, there is no need to redo the configuration process, and here, an easy way is to modify the configuration file. In Windows, the name of the MySQL configuration file is my.ini, which is under the installation directory of MySQL. Modify the default-character-set and character-set-server parameter values in the configuration file and change them to the desired character set name, such as gbk, gb2312, latinl, etc., and restart the MySQL service after modification to take effect. Readers can use the SHOW VARIABLES LIKE 'character_set_ °%'; command when modifying the character set to view the current character set for comparison.

16. 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.

17. Can ORDER BY be mixed with LIMIT?

When using the ORDER BY clause, you should ensure that it is after the FROM clause, if you use LIMIT, you must be after ORDER BY, and if the clauses are not in the correct order, MySQL will generate an error message.

When do I use quotation marks?

When you query, you will see that conditions are used in the WHERE clause, with some values in single quotation marks and others without. Single quotation marks are used to qualify strings, which are required if values are compared to string type columns, while quotation marks are not required for comparisons with numeric rows.

19. Must parentheses be used in the WHERE clause?

Whenever you use a WHERE clause with AND and OR operators, you should use parentheses to specify the order of operations. If there are many conditions, even if the evaluation order can be determined, the default evaluation order may make the SQL statement difficult to understand, so it is a good habit to use parentheses to clarify the order of operators.

Is it necessary to specify the WHERE clause when updating or deleting a table?

As you can see in the previous section, all UPDATE and DELETE statements specify items in the WHERE clause. If you omit the WHERE clause, UPDATE or DELETE will be applied to all rows in the table.

Therefore, unless you really intend to update or delete all records, be careful to use UPDATE or DELETE statements without WHERE clauses. It is recommended that you use the select statement to confirm the records that need to be deleted before updating and deleting the table, so as not to produce irreparable results. Click here to summarize 55 MYSQL interview questions for BAT interview.

21. Index is so important to database performance, how should it be used?

Choosing the correct index for the database is a complex task. If there are fewer index columns, less disk space and maintenance overhead are required. If multiple combined indexes are created on a large table, the index file will also swell quickly.

On the other hand, more indexes can cover more queries. You may need to experiment with several different designs to find the most effective index. Indexes can be added, modified, and deleted without affecting the database schema or application design. Therefore, you should try multiple different indexes to establish the optimal index.

22. 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.

23. What's 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.

24. Can the code in stored procedures be changed?

Currently, MySQL does not provide changes to existing stored procedure code, if you must modify the stored procedure, you must use the drop statement to delete it, then rewrite the code, or create a new stored procedure.

25. Can other stored procedures be called in stored procedures?

Stored procedures contain a collection of user-defined SQL statements, and you can use call statements to call stored procedures. Of course, you can also use call statements to call other stored procedures, but you cannot use drop statements to delete other stored procedures.

The parameters of the stored procedure should not be the same as the field names in the data table.

When defining the stored procedure parameter list, you should be careful to distinguish the parameter name from the field name in the database table, otherwise it will produce unexpected results.

27. Can the parameters of the stored procedure be in Chinese?

In general, Chinese parameters may be passed in a stored procedure, for example, a stored procedure looks up the user's information based on the user's name, and the passed parameter value may be in Chinese. When defining a stored procedure, you need to add character set gbk at the end, otherwise calling the stored procedure using Chinese parameters will cause errors, such as defining a userInfo stored procedure, as shown below:

CREATE PROCEDURE useInfo (IN u_name VARCHAR (50) character set gbk, OUT u_age INT)

28. What are the differences and relationships between views and tables in MySQL?

The difference between the two:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

A view is a compiled SQL statement, a visual table based on the result set of the SQL statement, but not a table.

The view has no actual physical record, while the basic table does.

Tables are contents and views are windows.

The table occupies physical space and the view does not take up physical space, the view is only the existence of logical concepts, the table can be modified in time, but the view can only be modified with the statements created.

A view is a way to view a data table, which can query the data made up of certain fields in the data table, just a collection of SQL statements. From a security point of view, the view prevents users from touching the data table, so the user does not know the table structure.

The table belongs to the global schema and is the real table, while the view belongs to the local schema and is the virtual table.

The creation and deletion of the view only affects the view itself, not the corresponding basic table.

The connection between the two:

A view is a table built on top of the base table, whose structure (that is, the defined columns) and contents (that is, all records) are derived from the base table, and it exists according to the existence of the base table. A view can correspond to a basic table, also

Can correspond to multiple basic tables. Views are abstractions of basic tables and new relationships established in a logical sense.

29. 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. Click here to summarize 55 MYSQL interview questions for BAT interview.

30. Delete triggers that are no longer needed in time.

After the trigger is defined, each time the trigger event is executed, the trigger is activated and the statement in the trigger is executed. If the requirements change and the trigger does not change or delete accordingly, the trigger will still execute the old statement, thus affecting the integrity of the new data. Therefore, triggers that are no longer in use should be deleted in time.

Which method should be used to create users?

There are several ways to create a user: GRANT statements, CREATE USER statements, and direct manipulation of the user table. In general, it is best to use GRANT or CREATE USER statements rather than inserting user information directly into the user table, because global-level permissions and other account information are stored in the user table, which may have a great impact on the MySQL server if the records in the user table are accidentally broken.

32. Can files backed up by mysqldump only be used in MySQL?

The text file backed up by mysqldump is actually a copy of the database, which can not only be used to restore the database in MySQL, but also can be used to restore the database in other data libraries such as SQL Server or Sybase by simply modifying the file. This realizes the migration between databases to some extent.

33. How to choose a backup tool?

Copying data files directly is the most direct and fast backup method, but the disadvantage is that incremental backup can not be realized basically. You must make sure that these tables are not used when preparing. If a table is being modified by the server while it is being copied, replication is invalid. When backing up files, it is best to shut down the server and then restart the server. To ensure data consistency, you need to execute the following SQL statement before backing up the file:

CREATE PROCEDURE useInfo (IN u_name VARCHAR (50) character set gbk, OUT u_age INT)

Just go under the catalog. Mysqlhotcopy is a PERL program that uses LOCK TABLES, FLUSH TABLES, and cp or scp to quickly back up databases. It is the fastest way to back up a database or a single table, but it can only run on the same machine as the database file, and mysqlhotcopy can only be used to back up MyISAM tables.

Mysqlhotcopy is suitable for the backup of small databases with a small amount of data. You can use the mysqlhotcopy program to make a full backup once a day. Mysqldump exports datasheets into SQL script files, which is relatively appropriate when upgrading between different MySQL versions, which is the most commonly used backup method. Mysqldump is slower than copying directly. That is, all the data in memory is flushed to disk and the data table is locked to ensure that no new data is written during the replication process. The data backed up by this method can be easily recovered and copied back to the original database directly.

Which logs should be opened at ordinary times?

Logging not only affects the performance of MySQL, but also takes up a lot of disk space. Therefore, if not necessary, log should be opened as little as possible. Depending on the environment in which you use it, you can consider opening different logs. For example, if you optimize inefficient statements in the development environment, you can enable the slow query log; if you need to record all the query operations of the user, you can open the general query log; if you need to record changes in the data, you can open the binary log; error log is enabled by default.

35. How to use binary logs?

Binary logs are mainly used to record data changes. If you need to record changes in the database, you can turn on binary logging. Based on the characteristics of binary logs, it can be used not only for data recovery, but also for data replication.

In the case of regular database backups, if data loss occurs, you can first use the backup to restore most of the data, and then use binary logs to restore the data that has changed since the most recent backup. In the case of a dual hot standby, you can use MySQL's binary log to record changes to the data, and then copy the changes to the backup server.

36. How to use the slow query log?

Slow query logs are mainly used to record logs with long query time. In the development environment, you can open the slow query log to record the query statements with long query time, and then optimize these statements. By matching the value of long_query_time, you can flexibly master different degrees of slow query statements.

37. Is it better to build as many indexes as possible?

Reasonable indexes can improve the speed of queries, but not as many indexes as possible. When the insert statement is executed, MySQL indexes the newly inserted record. So too many indexes will cause the insert operation to slow down. In principle, only the fields used for the query are indexed.

After reading the above, do you have any further understanding of the little knowledge of MySQL database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.