What should we know about learning MySQL?

Essay: Small series because years ago has been looking for work, and after years to find work and have been busy work, so also very little time to write something to everyone, in general, review a few times before the interview experience, also a small sum of their shortcomings, found that although they have been in the original company (outsourcing) Pinsipinhuo, Also gradually learned a lot of things, in the mind of the original thought oneself also is small has become, but with that several interviews, or gradually found some of their own shortcomings, such as SQL this fast. Probably is the relationship with spring Data JPA has been used in addition to the usual do not how to exercise the sake of it, I found that although every day and database dealings, but also did not really good research, resulting in several interviews have more or less exposed their lack of this aspect ( Also because the honest SQL is not ripe 2000 of the Wages (#￣~￣#)), so small made up to make up his mind to go back to do a bad supplement, but also to share a bit.

Benefits at the outset:

　　

1. How to quickly master MySQL?

⑴ Interest in culture

Interest is the best teacher, regardless of learning what knowledge, interest can greatly improve the efficiency of learning. Of course, learning MySQL 5.6 is no exception.

⑵ Tamping Foundation

The computer field of technology very emphasis on the foundation, the beginning of learning may also not recognize this, with the deep application of technology, only have a solid foundation, can be in the technology of the road to go faster and farther. For

The SQL statement is the most basic part of MySQL learning, and many operations are implemented through SQL statements. So in the process of learning, the reader to write more SQL statements, for the same function, the use of not

The same implementation statement to understand the differences in depth.

(3) Learn new knowledge in time

Using search engines correctly and efficiently, you can find a lot of knowledge about MySQL 5.6. At the same time, refer to other people to solve the problem of ideas, can also learn from other people's experience, timely access to the latest technical information.

⑷ Multi-practice operation

The database system is highly operational and requires more hands-on operation. In the actual operation of the process can find the problem, and think of ways and ideas to solve the problem, only in this way can improve the operational ability of the actual combat.

2. How do I select the type of server?

The meaning of each parameter in the MySQL Server Configuration window is as follows.

The "Server Configuration type" option is used to set the type of server. Click the Down button to the right of the option to see that there are 3 options.

The specific meanings of the 3 options are as follows.

(1) Development machine (development machines): This option represents a typical personal desktop workstation. Assume that there are multiple desktop applications running on the machine. Configure the MySQL server to use minimal system resources.

(2) Server machine: 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 scale of system resources.

(3) Dedicatedmysql server machine (dedicated MySQL server): This option represents a server that only runs the MySQL service. Assume that the run is not running other applications. The MySQL server is configured to use all available system resources. As a beginner, it is recommended to select the "Developmentmachine" (Developer machine) option so that less resources are used to occupy the system.

3. How to select the storage engine

Different storage engines have their own characteristics to suit different needs, as shown in the following table. To make a choice, you first need to consider what different capabilities each storage engine provides. InnoDB is a good choice if you want to provide transactional security (acid-compatible) capabilities for commit, rollback, and crash resiliency, and require concurrency control. If the data table is mainly used to insert and query records, then the MyISAM engine can provide high processing efficiency, if only temporary storage of data, the amount of data is small, and does not require high data security, you can choose to save the data in memory of the storage engine, MySQL using the engine as a temporary table, Stores the intermediate results of the query. If you have only insert and select operations, you can select the archive engine, and the archive storage engine supports high concurrency inserts, but it is not transaction-safe by itself. The archive storage engine is ideal for storing archived data, such as logging information that can be used with the archive engine. Which engine to use is flexible to choose from, and multiple tables in a database can use different engines to meet a variety of performance and real-world requirements. Using the appropriate 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 the storage engines in the system, including the default storage engine

As you can see, there are five kinds of storage engines in the current database system, the default is MyISAM. You can also use a direct way to view the default storage engine

The execution results directly show that the current default storage engine is MyISAM.

5. The table delete operation must be cautious.

The table delete operation will delete the table definition and the data in the table, and MySQL will not have any confirmation message when performing the delete operation, so it should be cautious when performing the removal exercise. Before deleting a table, it is best to back up the data in the table so that when the operation is wrong, the data can be recovered to avoid irreversible consequences. Similarly, when you use ALTER TABLE to perform basic modifications to a table, you should also ensure that the data is fully backed up before performing the operation, because the changes to the database cannot be undone, and if you add an unwanted field, you can delete it; the same, if you delete a required column, All the data below the column will be lost.

6. Do you want to have a primary key in each table?

Not every table requires a primary key, and a primary key is typically used when connecting 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 a primary key.

7. Can I select any storage engine for each table?

FOREIGN KEY constraints (FOREIGN key) cannot be used across engines. MySQL supports a variety of storage engines, each of which can specify a different storage engine, but note that foreign key constraints are used to ensure referential integrity of the data, and if a foreign key is required between tables, a different storage engine is specified, and the foreign KEY constraint cannot be created between the tables. So the choice of the storage engine is not entirely arbitrary.

8. Is the field value with the auto_increment constraint starting from 1?

By default, in MySQL, the initial value of Auto_increment is 1, and the field value is automatically added 1 for each new record. When you set the auto-increment property (auto_increment), you can also specify the value of the increment field of the first inserted record, so that the value of the self-increment field of the newly inserted record is incremented from the initial value, such as inserting the first record in TB_EMP8, and specifying an ID value of 5. The ID value of the record that is inserted later increases from 6 onwards. When you add a PRIMARY KEY constraint that is unique, you often need to set the field to automatically increment the property.

9. Is the field value with the auto_increment constraint starting from 1?

Timestamp and datetime In addition to the storage byte and the range of support, there is one of the biggest difference is: DateTime store date data, in the actual format of the input stored, that is, the input of what is stored, regardless of the time zone, and the timestamp value is stored in UTC Saved in the (World standard Time) format, converted to the current time zone when stored, and then converted back to the current time zone when retrieved. When querying, the time values displayed are different depending on the current time zone.

10. What are the methods and techniques for choosing a data type?

MySQL provides a large number of data types, in order to optimize storage, improve database performance, in any case should use the most accurate type. That is, in all types that can represent the value of the column, the type uses the least amount of storage.

1. Integers and floating-point numbers

If the fractional part is not needed, the integer is used to hold the data, and if the fractional part is required, the floating-point number type is used. For floating-point data columns, the values that are deposited are rounded up for the decimal digits defined by the column. For example, if the value of a column has a range of 1?99999, Mediumint unsigned is the best type if you use integers, and float is used if you need to store decimals. Floating-point types include float and double types. The double type has a higher precision than the float type, so you should choose a double type if you require high storage precision.

2. Floating point and fixed point number

The advantage of floating point float,double relative to fixed-point decimal is that floating-point numbers can represent a larger range of data in the case of certain lengths. However, because floating-point numbers are prone to errors, it is recommended to use decimal to store them when the accuracy requirement is high. Decimal is stored as a string in MySQL, and is used to define data with higher accuracy requirements such as currency. In data migration, float (m,d) is a non-standard SQL definition, and database migration may be problematic, and it is best not to use it. The other two floating-point numbers are also prone to problems when they are subtracted and compared, so be careful when doing calculations. If you make a numeric comparison, it is best to use the decimal type.

3. 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 years, use the year type, or only the time type if you want to record only the times. If you also need to record the date and time, you can use the timestamp or datetime type. Because the value range of the timestamp column is less than the value range of the DateTime, it is best to use DateTime for a date with a large storage range. Timestamp also has a property that DateTime does not have. By default, when a record is inserted but the column value is not specified timestamp, MySQL sets the timestamp column to the current time. Therefore, it is convenient to use timestamp when inserting a record while inserting the current time, and the other timestamp is more efficient in space than DateTime.

The characteristics and choice between 4.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 the inserted data, and varchar does not delete trailing spaces.

Char is a fixed length, so it is faster to process than varchar, but its disadvantage is to waste storage space. Therefore, the storage is not small, but the speed of the requirements can use the char type, and vice versa can be implemented using the varchar type.

Impact of the storage engine on the selection of char and varchar:

For MyISAM storage engines: It is best to use fixed-length data columns instead of variable-length data columns. This makes the entire table static, making the data retrieval faster and using space to change time.

For INNODB storage engines: Use variable-length data columns because the INNODB data table has a storage format that is not fixed-length and variable-length, so using char is not necessarily better than using varchar, but because varchar is stored in the actual length, it is more space-saving, so the disk I /O and the total data storage is better.

5.ENUM and SET

An enum can only take a single value, and its data list is an enumeration collection. Its legal list of values allows a maximum of 65 535 members. Therefore, you can use an enum when you need to select one from multiple values. For example: the Gender field is suitable for definition as an enum type and can only be taken from ' male ' or ' female ' at a time. Set is preferable to multiple values. Its legal list of values allows a maximum of 64 members. An empty string is also a valid set value. When you need to take multiple values, it is appropriate to use set types, such as: to store a person's interests, it is best to use set type. The values of the enum and set are in the form of a string, but internally, MySQL stores them as numeric values.

6.BLOB and TEXT

BLOBs are binary strings, text is non-binary strings, and both can hold large volumes of information. BLOBs primarily store pictures, audio information, and so on, while text only stores plain text files. The use of the two should be distinguished.

How do I use special characters in 11.MySQL?

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

Can I store files in 12.MySQL?

The Blob and text field types in MySQL can store files with large amounts of data, which can be used to store images, sounds, or large volumes of text content, such as Web pages or documents. Although you can store large volumes of data using BLOBs or text, processing these fields can degrade the performance of your database. If it is not necessary, you can choose to save only the path to the file.

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

MySQL is not case-sensitive under the Windows platform, so the string comparison function is also not case-sensitive. 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 a result of 1, and if the binary keyword is used, binary ' a ' = ' a ' result is 0, in case of case, ' a ' is not the same as ' a '.

14. How do I get a partial date or time value from a datetime value for the year, month, day, etc.?

In MySQL, datetime values are stored in a data table as strings, so you can use String functions to intercept different parts of a datetime value, such as a field with a name of DT having a value of "2010-10-01 12:00:30", and if you only need to get the year value, you can enter left (DT, 4 ), so that the substring to the left of the string with a length of 4 is obtained, the value of the year part, and if you want to get the month value, you can enter the mid

(dt,6,2), starting with the 6th character of the string, a substring of length 2 is exactly the month value in dt. Similarly, the reader can calculate and obtain the corresponding value based on the location of other dates and times.

15. How do I change the default character set?

The CONVERT () function changes the default character set for the specified string, and in the beginning of the chapter, introduces the reader to the installation and configuration of MySQL using the GUI graphical installation Configuration tool, where one of the steps is to choose the default character set for MySQL. However, if you only change the character set, there is no need to re-execute the configuration process again, here, a simple way is to modify the configuration file. In Windows, the MySQL configuration file name is called 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, change it to the desired character set name, such as GBK, gb2312, Latinl, and so on, and then restart the MySQL service after modifying it to take effect. Readers can use show VARIABLES like ' character_set_°% ' when modifying character sets, and commands to view the current character set for comparison.

Can 16.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 list the columns that precede them in descending order. For example, to sort multiple columns in descending order, you must follow the column names of each column

Add desc keyword. Unlike distinct, distinct cannot be partially used. In other words, the DISTINCT keyword is applied to all columns, not just the first specified column after it. For example, if you query 3 fields S_id,f_name,f_price, if the combined values of the 3 fields of the different records are different, all the records will be queried.

17.ORDER by can I mix it with limit?

When using the ORDER BY clause, ensure that it is after the FROM clause, and if limit is used, it must be after order by, and MySQL will produce an error message if the clauses are in an incorrect order.

18. When do I use quotation marks?

At the time of the query, you will see that the conditions are used in the WHERE clause, some values are enclosed in single quotes, and some values are not added. Single quotation marks are used to qualify strings, and if you compare values to String type columns, you need to limit the quotation marks, and you do not need to use quotation marks when comparing them to numeric values.

19. Must you use parentheses in the WHERE clause?

Any time you use a WHERE clause with and and OR operators, you should use parentheses to explicitly manipulate the order. If there are many conditions, even if you can determine the order of calculation, the default calculation order may make the SQL statement difficult to understand, so it is a good practice to use parentheses to clarify the order of the operators.

20. Must you specify a WHERE clause when updating or deleting a table?

As you can see in the previous section, all the update and DELETE statements have the condition specified in the WHERE clause. If you omit the WHERE clause, the update or delete is applied to all rows in the table. Therefore, unless you do intend to update or delete all records, you should pay attention to using an UPDATE or DELETE statement without a WHERE clause. It is recommended that you use the SELECT statement to confirm the records that need to be deleted before updating and deleting the table to avoid irreversible results.

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

Choosing the correct index for a database is a complex task. If you have fewer index columns, you need less disk space and maintenance overhead. If you create multiple combinations of indexes on a large table, the index file expands quickly. On the other hand, more indexes can overwrite more queries. You may need to experiment with several different designs to find the most efficient index. You can add, modify, and delete indexes without affecting the database schema or application design. Therefore, you should try several different indexes to establish an optimal index.

22. Use the short index as much as possible.

Indexes a field of type string, if possible a prefix length should be specified. For example, if you have a column of CHAR (255), and if the majority value is unique within the first 10 or 30 characters, you do not need to index the entire column. Short indexes 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?

In essence, they are stored programs. A function can only return a single value or Table object through a return statement, while a stored procedure does not allow a return, but multiple values can be returned through an out parameter. The function limit is more than

Temporary tables can be used, only table variables are used, and some functions are not available, and stored procedures are relatively less restrictive. Functions can be embedded in SQL statements and can be called in a SELECT statement as part of a query statement, whereas stored procedures are typically executed as a separate part.

24. Can the code in the stored procedure be changed?

Currently, MySQL does not provide modifications to existing stored procedure Code, and if you have to modify the stored procedure, you must use the DROP statement to remove it, rewrite the code, or create a new stored procedure.

25. Can I call other stored procedures in a stored procedure?

A stored procedure contains a collection of user-defined SQL statements that can be invoked using the call statement, while other stored procedures can also be invoked using the call statement in a stored procedure, but you cannot use the drop statement to delete other stored procedures.

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

When defining a stored procedure parameter list, be careful to distinguish the parameter name from the field name in the database table, or you will see unexpected results.

27. Can I use the parameters of the stored procedure in Chinese?

In general, there may be cases in which Chinese parameters are passed in the stored procedure, such as when a stored procedure looks up the user's information according to the user's name, and the passed parameter value may be in Chinese. In this case, you need to define the stored procedure, and then add character set GBK, otherwise the call stored procedure using Chinese parameters will be an error, such as the definition of userinfo stored procedures, the code is as follows:

CREATE PROCEDURE useinfo (in U_name VARCHAR () Character set GBK, out U_age INT)

1. Difference between the two:what is the difference between a view and a table in 28.MySQL and what is a contact?

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

(2) The view has no actual physical record, and the base table has.

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

(4) The table occupies the physical space and the view does not occupy the physical space, the view is only the existence of the logical concept, the table can be modified in time, but the view can only be modified with the created statement.

(5) A view is a way of looking at a data table, querying data from some fields in a data table, just a collection of some SQL statements. From a security standpoint, the view prevents users from touching the data table, so the user does not know the table structure.

(6) Table is a table in the global schema, is a real table, the view belongs to the local schema of the table, is a virtual table.

(7) The creation and deletion of views affects only the view itself, and does not affect the corresponding base table.

2. The relationship between the two:

A view is a table built on top of a base table whose structure (that is, defined columns) and content (that is, all records) come from the base table, which exists based on the existence of the base table. A view can correspond to a basic table, or

You can correspond to more than one base table. A view is an abstraction of a basic table and a new relationship that is established in a logical sense.

29. Special care should be taken when using triggers.

When using triggers, it is important to note that for the same table, only one trigger can be created for the same event, such as creating a before insert trigger on the table account, if you create a before insert trigger on the table account again, MySQL will have an error, and you can only create a after insert or before update type trigger on the table account. The flexible use of triggers will save a lot of trouble for the operation.

30. Delete the triggers that are no longer needed.

After a trigger is defined, each time a trigger event is executed, the trigger is activated and the statement in the trigger executes. If the requirement changes and the trigger is not changed or deleted, the trigger will still execute the old statement, which will affect the integrity of the new data. Therefore, the triggers that are no longer used are deleted in a timely manner.

31. Which method should I use to create a user?

There are several ways to create users: The GRANT statement, the CREATE USER statement, and the direct manipulation of the user table. In general, it is best to use the grant or create USER statement instead of inserting user information directly into the users table, because global-level permissions and other account information are stored in the user table, and if the records in the user table are accidentally destroyed, you might The MySQL server has a big impact.

32.mysqldump backup files can only be used in MySQL?

The text file backed up by mysqldump is actually a copy of the database, which not only restores the database in MySQL, but also uses it to recover the database in other databases, such as SQL Server or Sybase, through simple modifications to the file. This has somehow implemented a migration between databases.

33. How do I select a backup tool?

Copying data files Directly is the most straightforward and fast way to backup, but the downside is that incremental backups are largely not possible. You must ensure that you do not use these tables when you back up. If the server is modifying a table while it is being copied, the replication is not valid. 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 () Character set GBK, out U_age INT)

directory. Mysqlhotcopy is a PERL program that uses the LOCK TABLES, FLUSH TABLES, and CP or SCP to quickly back up a database. It is the quickest 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 the MyISAM table. Mysqlhotcopy is suitable for small database backup, the amount of data is small, you can use the Mysqlhotcopy program to make a full backup every day. mysqldump data tables into SQL script files, which are relatively appropriate when upgrading between different MySQL versions, are the most commonly used backup methods. Mysqldump is slower than copying directly. that is, the data in memory is flushed to disk, and the data table is locked to ensure that no new data is written during the copy process. This method backs up the data recovery is also very simple, directly copied back to the original database

34. What logs should I open in peacetime?

Logs can affect both MySQL performance and disk space. Therefore, if it is not necessary, you should open the log as little as possible. Depending on your usage environment, you may consider opening different logs. For example, in the development environment to optimize query inefficient statement, you can turn on the slow query log, if you need to record all the user's query operations, you can open the General query log, if you need to record data changes, you can open the binary log, error log is turned on by default.

35. How do I use the binary log?

Binary logs are primarily used to record changes in data. If you need to log changes to the database, you can turn on the binary log. Binary log-based features can be used not only for data recovery, but also for data replication. In the case of a regular database backup, if data loss occurs, you can restore most of the data with a backup, and then use the binary log to recover the data that was changed after the most recent backup. In a dual-machine hot standby scenario, you can use MySQL's binary logging to record changes to the data, and then copy the changes to the backup server.

36. How do I use the slow query log?

The slow query log is primarily used to record logs that have a long query time. In the development environment, you can turn on the slow query log to record query statements that have a long query time and then optimize those statements. With the value of long_query_time, it is possible to grasp the slow query statements of different degrees flexibly.

37. Is it better to have the index built as much as possible?

A reasonable index can improve the speed of the query, but not the more the better the index. When executing the INSERT statement, MySQL will index the newly inserted record. Therefore, too many indexes can cause the insert operation to become slower. In principle, only fields that are queried are indexed.

38. How do I use the query buffer?

Query buffers can increase the speed of queries, but this approach is only appropriate if the query statement is more, and the UPDATE statement is less. By default, the size of the query buffer is 0, which is not available. You can modify the Queiy_cache_size to resize the query buffer, and modify the Query_cache_type to adjust the type of the query buffer. The values for modifying Query_cache_size and Query_cache_type in My.ini are as follows:

[Mysqld]query_cache_size= 512M query_cache_type= 1query_cache_type=1

　　

Indicates that the query buffer is turned on. The query buffer is not used until the query statement contains the Sql_no_cache keyword. You can use the Flush query cache statement to flush the buffer and clean up the fragments in the query buffer.

What should we know about learning MySQL?