Whether it is operation and maintenance, development, testing, or architect, database technology is a necessary salary magic tool, so, has been said to learn database, learn MySQL, in the end is to learn what it? This article reveals you, you are worth reading! Don’t forget to like it and retweet it.

1. How to quickly master MySQL?

⑴ Cultivate interest


Interest is the best teacher, no matter what knowledge to learn, interest can greatly improve learning efficiency. Learning MySQL 5.6 is no exception.



(2) Strengthen the foundation




The technology in the computer field emphasizes the foundation very much, which may not be realized at the beginning of learning. With the deep application of technology, only with a solid foundation can we go faster and farther on the road of technology. For MySQL learning, SQL statements are the most basic part, many operations are achieved through SQL statements. So in the process of learning, readers should write MORE SQL statements, for the same function, the use of different implementation statements to complete, so as to deeply understand its differences.



(3) Learn new knowledge in time




Using search engines correctly and effectively, you can search for a lot of information about MySQL 5.6. At the same time, reference other people to solve the problem ideas, but also can draw on the experience of others, timely access to the latest technical information.



Practice more




Database system has a strong operability, need more hands-on operation on the machine. Only in the process of actual operation can we find the problem and think about the methods and ideas to solve the problem. Only in this way can we improve the operational ability of actual combat.

2. How do I select a server type?

The following table describes the parameters in the MySQL server configuration window. [Server Configuration Type] Set the Server Type. Click the down button to the right of this option to see that there are three options included.

The meanings of the three options are as follows.

  • (1) Development Machine: This option represents a typical desktop workstation for personal use. Assume you have multiple desktop applications running on the machine. Configure the MySQL server to use minimal system resources.

  • (2) Server Machine: This option represents a Server. MySQL Server can run with other applications, such as FTP, email, and Web servers. The MySQL server is configured to use an appropriate proportion of system resources.

  • DedicatedMySQL Server Machine (3) DedicatedMySQL Server Machine: This option represents a Server that runs only MySQL services. Assume that no other application is running. The MySQL server is configured to use all available system resources. For starters, it is recommended to use the DevelopmentMachine option as it consumes less resources on the system.

3. How to select a storage engine

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

  • 1. First consider what different capabilities each storage engine provides. InnoDB is a good choice if you want to provide transaction security (ACID compatibility) with commit, rollback and crash recovery capabilities and require concurrency control. If the data table is mainly used to insert and query records, the MyISAM engine can provide high processing efficiency; If the data is only stored temporarily, the amount of data is not large and high data security is not required. You can choose to store the data in the Memory engine. MySQL uses the Memory engine as a temporary table to store intermediate query results. If you only have INSERT and SELECT operations, you can use the Archive engine, which supports highly concurrent inserts but is not transaction-safe. The Archive storage engine is ideal for storing archived data, such as logging messages.

  • 2. Choose which engine to use flexibly according to your needs. Multiple tables in a database can use different engines to meet various performance and practical requirements.

  • 3. Using the right storage engine will improve the performance of the entire database.

4. How do I view the default storage engine?

Use the SHOW ENGINES statement to view all storage ENGINES in your system, including the default storage engine. You can see that there are five storage engines in the current database system. The default is MyISAM. There is also a straightforward way to view the default storage engine. The default storage engine is MyISAM.

5. Exercise caution when deleting a table.

When deleting a table, the definition of the table and the data in the table will be deleted together. In addition, MySQL does not prompt any confirmation message when deleting a table. Therefore, exercise caution when deleting a table. Before deleting a table, back up data in the table so that data can be restored to avoid irretrievable consequences. Similarly, when making basic changes to a TABLE using ALTER TABLE, you should make sure that a full backup of the data is taken before performing the operation, because database changes cannot be undone. If an unwanted field is added, it can be removed. Similarly, if a required column is deleted, all data below that column will be lost.

6. Should every table have a primary key?

A primary key is not required in every table, but is typically used when joining multiple tables. Therefore, there is no need to create a primary key for every table, and in some cases it is best not to use primary keys.

7. Can each table choose any storage engine?

FOREIGN KEY constraints cannot be used across engines. MySQL supports multiple storage engines. Each table can specify a different storage engine, but note that foreign key constraints are used to ensure data referential integrity. If you specify different storage engines to associate foreign keys with tables, you cannot create foreign key constraints between these tables. So the choice of storage engine is not entirely arbitrary.

8. Do columns with an AUTO_INCREMENT constraint start at 1?

By default, in MySQL, the initial value of AUTO_INCREMENT is 1, and each new record is automatically incremented by 1. When setting the AUTO_INCREMENT attribute (TB_EMP8), you can also specify the value of the increment field in the first insert record. Then, the value of the increment field in the first insert record is incremented from the initial value. If you also specify an ID value of 5, the id value of future inserted records will increase from 6. When adding unique primary key constraints, you often need to set the field to automatically add attributes.

9. What is the difference between TIMESTAMP and DATETIME?

TIMESTAMP and DATETIME in addition to the different storage bytes and support range, there is another biggest difference is: DATETIME in the storage of date data, according to the actual input format, that is, the input of what is stored, regardless of the time zone; The TIMESTAMP value is stored in UTC(Universal Standard Time) format. The current time zone is converted when stored and then converted back to the current time zone when retrieved. That is, the time displayed varies 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 value of this column, this type uses the least amount of storage.

1. Integers and floating point numbers


If decimals are not needed, integers are used to store data; If you need to represent a fractional part, use the floating-point class. For floating-point data columns, the value stored is rounded to the decimal place defined for the column. For example, if the column value ranges from 1 to 99999 and is an integer, MEDIUMINT UNSIGNED is the best type. If you need to store decimals, use the FLOAT type. Floating point types include FLOAT and DOUBLE. DOUBLE is more precise than FLOAT, so it should be used when high storage accuracy is required.



2. Floating point and fixed-point numbers




The floating-point number FLOAT, DOUBLE has the advantage over fixed-point DECIMAL that floating-point numbers can represent a larger range of data for a given length. However, since floating-point numbers are prone to error, it is recommended to use DECIMAL for high accuracy. DECIMAL is stored as a string in MySQL and is used to define data such as currency that requires a high degree of precision. Float (M,D) is a non-standard SQL definition in data migration, which can cause problems in database migration and is best avoided. The other two floating-point numbers are also prone to problems with subtraction and comparison, so be careful when calculating them. For numerical comparisons, it is best to use the DECIMAL type.



3. Date and time types




MySQL has many data types for different types 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, just use the TIME type. If you need to record both the date and time, you can use TIMESTAMP or DATETIME. Since the value range of the TIMESTAMP column is smaller than the value range of DATETIME, DATETIME is the best way to store dates with a larger range. TIMESTAMP also has a property that DATETIME does not. By default, MySQL sets the TIMESTAMP column to the current time when a record is inserted without the specified TIMESTAMP value. So it’s convenient to use TIMESTAMP when you need to insert a record while inserting the current time, and TIMESTAMP is more spatially efficient than DATETIME.

4. Features and choices between CHAR and VARCHAR

CHAR and VARCHAR:

  • CHAR is a fixed length character and VARCHAR is a variable length character. CHAR automatically removes trailing whitespace from inserted data, VARCHAR does not.

  • CHAR is fixed length, so it is faster than VARCHAR, but it has the disadvantage of wasting storage space. So you can use CHAR if you don’t have a lot of memory but need speed, and VARCHAR if you don’t.

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

  • For 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 columns. Since InnoDB tables are stored in both fixed and variable lengths, using CHAR is not necessarily better than using VARCHAR. However, since VARCHAR is stored according to the actual length, it saves space, so it is better for disk I/O and total data storage.

5. ENUM and SET


ENUM can only take a single value, and its data list is a collection of enumerations. Its list of legal values is allowed to have up to 65 535 members. Therefore, you can use ENUM when you need to select one of 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 takes multiple values. Its list of legal values is allowed to have up to 64 members. An empty string is also a valid SET value. The SET type is best used when multiple values are required, for example, to store a person’s interests. ENUM and SET values appear as strings, but internally, MySQL stores them as numeric values.



6. A BLOB and TEXT




BLOB is a binary string, TEXT is a non-binary string, and both can hold large amounts of information. BLOB stores images, audio information, and so on, while TEXT stores only plain TEXT files. The purpose of both should be distinguished.

How to use special characters in MySQL?

Symbols such as single quotes (‘), double quotes (“), backslashes (), etc. These symbols cannot be entered directly in MySQL, otherwise they will produce unexpected results. In MySQL, these special characters are called escape characters and must start with a backslash (‘ \ ‘). Therefore, you should enter either (\ ‘) or (\”) for single or double quotation marks, and enter (\) for backslashes. Other special characters include carriage return (\r), newline (\n), and TAB (\ TAB). Backspace (\b), etc. Be sure to escape these special characters when inserting them into the database.

12. Can files be stored in MySQL?

The BLOB and TEXT field types in MySQL can store files with large amounts of data. You can use these data types to store images, sounds, or large amounts of TEXT content, such as web pages or documents. Although BLOB or TEXT can be used to store large volumes of data, processing of these fields can degrade database performance. If you don’t have to, you can choose a file-only path.

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

On Windows, MySQL is size insensitive, so the string comparison function is case insensitive. If you want to perform a case-sensitive comparison, you can add the BINARY keyword before the string. For example, by default, ‘a’ = ‘a’ returns 1. If you use the BINARY keyword, the BINARY ‘a’ = ‘a’ returns 0. ‘a’ is not the same as’ a ‘in case sensitive cases.

14. How to obtain partial date or time values such as year, month, day from date and time values?

In MySQL, the date and time values are stored as strings in the data table, so you can use string functions to intercept different parts of the date and time values. For example, a field named DT has the value “2010-10-01 12:00:30”. If you only want to obtain the year value, you can enter LEFT(dt, 4). This gives you the value of the YEAR part of the string that starts at length 4 on the left side; To get the month value, enter MID(dt,6,2), starting at the sixth character of the string, and the substring of length 2 is exactly the month value in dt. Similarly, the reader can calculate and retrieve 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 for the specified string. In the beginning section, the reader is introduced to installing and configuring MySQL using the GUI graphical installation and configuration tool. One of the steps is to select the default character set for MySQL. However, if you only change the character set, there is no need to redo the configuration process. In this case, a simple way is to modify the configuration file. On Windows, the MySQL configuration file name is my.ini, which is located under the MySQL installation directory. Change the values of default-character-set and character-set-server in the configuration file to the desired character set names, such as GBK, GB2312, or latinl. After the modification, restart the MySQL service to take effect. SHOW VARIABLES LIKE ‘character_set_°%’; Command to view the current character set for comparison.

16. Can DISTINCT be applied to all columns?

If you want to sort the columns in descending order, use DESC. This keyword can only sort the columns before it 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. DISTINCT, on the other hand, cannot be used partially. In other words, the DISTINCT keyword applies to all columns, not just the first specified column after it. For example, if the three fields s_id, f_name, and f_price are queried, all records will be queried if the combined values of the three fields are different.

17. Can YOU mix ORDER BY and LIMIT?

The ORDER BY clause must be placed after the FROM clause. If LIMIT is used, the ORDER BY clause must be placed after the ORDER BY clause. If the ORDER is not correct, MySQL will generate an error message.

18. When to use quotation marks?

When you query, you’ll see the use of conditions in the WHERE clause, with some values enclosed in single quotes and some without. Single quotes are used to qualify strings, and if values are compared with string type columns, you need to qualify quotes; Line comparisons with numeric values do not require quotation marks.

19. Must parentheses be used in WHERE clauses?

Any time 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, the default order of evaluation can make SQL statements difficult to understand, even if you can determine the order of evaluation, so it is a good practice to specify the order of the operators with parentheses.

20. Must THE WHERE clause be specified when updating or deleting a table?

As you saw in the previous section, all UPDATE and DELETE statements specify items in the WHERE clause. If the WHERE clause is omitted, UPDATE or DELETE is 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 a WHERE clause. You are advised to use the SELECT statement to confirm the records to be deleted before updating or deleting a table to avoid irreparable results.

21. How should an index be used when it is so important to database performance?

Choosing the right index for a database is a complex task. Fewer indexed columns require less disk space and less maintenance overhead. If multiple composite indexes are created on a large table, index files can also swell quickly. More indexes, on the other hand, cover more queries. You may need to experiment with several different designs to find the most efficient index. Indexes can be added, modified, and deleted without affecting database architecture or application design. Therefore, multiple different indexes should be tried to build the optimal index.

22. Use short indexes whenever possible.

Index fields of type string, specifying a prefix length if possible. For example, if you have a CHAR(255) column, you do not need to index the entire column if the multivalue is unique within the first 10 or 30 characters. Short indexes can not only improve query speed, but also save disk space and reduce I/O operations.

What is the difference between MySQL stored procedures and functions?

They are all stored programs in nature. A function can only return a single value or table object via a return statement; Stored procedures are not allowed to return, but can return multiple values via the OUT argument. There are a lot of restrictions on functions, you can’t use temporary tables, you can only use table variables, and some functions are not available, etc.; Stored procedures are less restrictive. Functions can be embedded in SQL statements and called as part of a query statement in a SELECT statement. Stored procedures are typically executed as a separate part.

24. Can code in a stored procedure be changed?

Currently, MySQL does not provide modification to existing stored procedure code. If you need to modify a stored procedure, you must use the DROP statement to delete the stored procedure and then rewrite the code or create a new stored procedure.

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

A stored procedure contains a set of user-defined SQL statements. You can use the CALL statement to CALL a stored procedure. You can also use the CALL statement to CALL other stored procedures in a stored procedure, but you cannot use the DROP statement to DROP other stored procedures.

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

When defining the stored procedure parameter list, care should be taken to distinguish the parameter names from the field names in the database table, otherwise unexpected results will occur.

27. Can stored procedure parameters be in Chinese?

Generally, Chinese parameters may be passed in a stored procedure. For example, a stored procedure searches for information about a user based on the user name, and the parameter values may be Chinese. In this case, you need to add character set GBK to the end of the stored procedure definition. Otherwise, it will fail to call the stored procedure using Chinese parameters. For example, to define the userInfo stored procedure, the code is as follows:

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

What is the difference between a view and a table in MySQL?

  • (1) A view is a visual table based on the result set of a compiled SQL statement, while a table is not.

  • (2) The view has no actual physical record, whereas the base table does.

  • (3) The table is the content and the view is the window.

  • (4) The table occupies physical space and the view does not occupy physical space. The view is only the existence of logical concepts. The table can modify it in time, but the view can only be modified by creating statements.

  • (5) View is a way to view a data table. It can query data composed of certain fields in the data table. It is just a collection of SQL statements. From a security point of view, the view prevents the user from touching the table so that the user does not know the structure of the table.

  • (6) The table belongs to the global schema table, is a real table; A view is a virtual table that belongs to a local schema.

  • (7) The creation and deletion of a view only affects the view itself, not the corresponding basic table.

2. The connection between the two:

  • A view is a table built on top of the base table, whose structure (defined columns) and content (all records) are derived from the base table. It exists as if the base table exists. A view can correspond to a base table, too

  • Multiple base tables can be mapped. A view is an abstraction of the base table and a new relationship established in a logical sense.

29. Special care must be taken when using triggers.

MySQL > create BEFORE INSERT trigger BEFORE INSERT trigger MySQL > create BEFORE INSERT trigger BEFORE INSERT trigger MySQL > create BEFORE INSERT trigger BEFORE INSERT trigger Triggers of type AFTER INSERT or BEFORE UPDATE can only be created on the table account. Flexible use of triggers will save a lot of trouble.

30. Remove triggers that are no longer needed in a timely manner.

After a trigger is defined, each time a trigger event is executed, the trigger is activated and the statement within 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, affecting the integrity of the new data. Therefore, remove triggers that are no longer used in a timely manner.

31. Which method should be used to create users?

There are several ways to CREATE a USER: the GRANT statement, the CREATE USER statement, and directly manipulating the USER table. In general, it is better to use GRANT or CREATE USER statements than to insert USER information directly into the USER table, because the USER table stores global-level permissions and other account information. If records in the USER table are accidentally broken, the MySQL server may have a serious impact.

Mysqldump backup files can only be used in MySQL.

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

33. How do I select a backup tool?

Copying data files directly is the most direct and fast backup method, but the disadvantage is that incremental backup is almost impossible. You must ensure that these tables are not used when preparing a copy. Replication is invalid if a table is copied while the server is modifying it. When backing up files, it is best to shut down the server and then restart it. To ensure data consistency, run the following SQL statements before backing up files:

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

In the directory. Mysqlhotcopy is a PERL program that uses LOCK TABLES, FLUSH TABLES, and cp or SCP to quickly back up the database. It is the fastest way to back up a database or a single table, but it can only run on the machine where the database file resides, and mysqlHotCopy can only be used to back up MyISAM tables. Mysqlhotcopy is suitable for backing up small databases with a small amount of data. You can use mysqlHotCopy to perform a full backup once a day. Mysqldump converts tables into SQL script files, which is relatively suitable for upgrading between different MySQL versions and is the most common backup method. Mysqldump is slower than direct replication. That is, all 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. This method backup out of the data recovery is very simple, directly copy back to the original database

34. Which logs should be opened at ordinary times?

Logs affect MySQL performance and occupy a large amount of disk space. Therefore, log as little as possible if you don’t have to. You can enable different logs based on different operating environments. For example, you can enable slow query logs to optimize statements with low query efficiency in the development environment. To record all user query operations, enable the general query log function. To record data changes, enable binary logs. Error logging is enabled by default.

35. How do I use binary logs?

Binary logs are used to record data changes. To record database changes, enable binary logging. The binary log-based feature can be used not only for data recovery but also for data replication. When the database is backed up periodically, if data is lost, you can use the backup to restore most of the data first, and then use binary logs to restore the data changed after the latest backup. In dual-system hot backup, you can use MySQL binary logs to record data changes and copy the changes to the backup server.

36. How do I use slow Query logs?

Slow query logs are used to record logs that are queried within a long time. In a development environment, you can enable slow query logging to record query statements that take longer to query, and then optimize those statements. By setting the value of long_query_time, you can flexibly master different degrees of slow query statements.

37. Is it better to build more indexes?

Reasonable indexes can speed up queries, but not the more indexes the better. When an insert statement is executed, MySQL creates an index for the newly inserted record. So too many indexes can make the insert operation slow. In principle, only query fields are indexed.

38. How do I use the query buffer?

The query buffer can speed up the query, but only when there are many query statements and few update statements. By default, the size of the query buffer is ○, which means it is not available. You can modify queiy_cache_size to adjust the query buffer size; Modify query_cache_type to adjust the type of query buffer. Modify the query_cache_size and query_cache_type values in my.ini as follows:

[mysqld]
query_cache_size= 512M query_cache_type= 1
query_cache_type=1Copy the code

Indicates that the query buffer is enabled. The query buffer is not used only if the query statement contains the SQL_NO_CACHE keyword. The FLUSH QUERY CACHE statement can be used to FLUSH the buffer and clean up debris in the QUERY CACHE.