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

This article mainly introduces the MySQL database commonly used skills have what the relevant knowledge, the content is detailed and easy to understand, the operation is simple and fast, has a certain reference value, I believe you read this MySQL database common skills what articles will have a harvest, let's take a look at it.

1. How to select 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. Assume that there are multiple desktop applications running on the machine. 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.

2. 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 start with a backslash symbol (''), so enter (') or (") when using single and double quotes, enter () when entering a backslash, and other special characters include carriage return (), newline (), tab (ab), backspace (), and so on. When inserting these special characters into the database, be sure to escape.

3. How does MySQL perform case-sensitive string comparisons?

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.

Optimization skills of MySQL sentences

The optimization of MySQL database performance is the only way for the development of MySQL database, and the optimization of MySQL database performance is also a witness to the progress of MySQL database. Here are some tips on MySQL statement optimization:

1. Try to avoid using the! = or operator in the where clause, otherwise the engine will give up using the index and do a full table scan.

2. In order to optimize the query, we should avoid full table scanning as far as possible, and first consider establishing indexes on the columns involved in where and order by.

3. Try to avoid judging the null value of the field in the where clause, otherwise it will cause the engine to give up using the index and perform a full table scan, such as:

Select id from t where num is null

You can set the default value of 0 on num to ensure that there is no null value for the num column in the table, and then query it like this:

Select id from t where num=0

4. Try to avoid using or to join conditions in the where clause, otherwise it will cause the engine to abandon the use of indexes and perform full table scans, such as:

Select id from t where num=10 or num=20

You can query it like this:

Select id from t where num=10

Union all

Select id from t where num=20

5. The following query will also cause a full table scan: (no leading percent sign)

Select id from t where name like'C%'

To improve efficiency, consider full-text retrieval.

6. In and not in should also be used with caution, otherwise it will cause full table scanning, such as:

Select id from t where num in (1, 2, 3)

For consecutive values, use between instead of in:

Select id from t where num between 1 and 3

7. If you use parameters in the where clause, it will also cause a full table scan. Because SQL parses local variables only at run time, the optimizer cannot defer the choice of an access plan until run time; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and cannot be used as an input to the index selection. A full table scan will be performed as follows:

Select id from t where num=@num

You can force the query to use the index instead:

Select id from t with (index (index name)) where num=@num

8. Try to avoid expression operations on fields in the where clause, which will cause the engine to give up using the index and do a full table scan. Such as:

Select id from t where num/2=100

It should be changed to:

Select id from t where num=100*2

9. Try to avoid functional operations on fields in the where clause, which will cause the engine to give up using the index and do a full table scan. Such as:

Select id from t where substring (name,1,3) = 'abc'-name id that begins with abc

Select id from t where datediff (day,createdate,'2005-11-30') = 0 id generated by Murray 2005-11-30'

It should be changed to:

Select id from t where name like 'abc%'

Select id from t where createdate > = '2005-11-30' and createdate

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.