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

This article will explain in detail what MySQL database skills are, the quality of the article content is high, so Xiaobian shared with you as a reference, I hope you have a certain understanding of related knowledge after reading this article.

10 MySQL Database Tips for You

01 How do I use special characters in MySQL?

Symbols such as single quotes ('), double quotes ("), backslashes (), etc., cannot be entered directly in MySQL, otherwise unexpected results will occur. In MySQL, these special characters are called escape characters and need to start with a backslash ('') when entering, so you should enter (') or (") when using single quotes and double quotes respectively, and you should enter () when entering backslash. Other special characters include carriage return ( ), newline ( ), tab (ab), backspace (), etc. When inserting these special characters into a database, be sure to escape them.

02 Can MySQL store files?

BLOB and TEXT field types in MySQL can store large files, and these data types can be used to store images, sounds, or large text content, such as web pages or documents. Although BLOB or TEXT can be used to store large amounts of data, processing these fields degrades database performance. If it is not necessary, you can choose a path where only files are stored.

03 MySQL How do I perform case-sensitive string comparisons?

MySQL is size insensitive on Windows, so string comparison functions are case insensitive. If you want to perform case-sensitive comparisons, you can precede the string with the BINARY keyword. For example,'a'='A' returns a result of 1 by default, BINARY 'a'='A' results in 0 if BINARY keyword is used, and 'a' is not the same as 'A' in case sensitivity.

04 How do I get partial date or time values from date time values?

MySQL, date and time values are stored in the form of strings in the data table, so you can use string functions to intercept different parts of the date and time value, for example, a field named dt has the value "2010-10-01 12:00:30", if you only need to get the year value, you can enter LEFT(dt, 4), so you get the left side of the string starting with a length of 4 sub-string, that is, the YEAR part of the value; if you want to get the month value, you can enter MID(dt,6,2), the string starts with the 6th character, the length of 2 sub-string is exactly the month value in dt. Similarly, readers can calculate and retrieve values based on the location of other dates and times.

05 How do I change the default character set?

The CONVERT() function changes the default character set for a specified string. In the opening chapter, the reader is introduced to MySQL installation and configuration using the GUI graphical installation configuration tool. One of the steps is to select the default character set for MySQL. However, if you change only the character set, there is no need to re-execute the configuration process; here, a simple way is to modify the configuration file.

On Windows, the MySQL configuration file is named my.ini and is located under MySQL's installation directory. Modify the default-character-set and character-set-server parameter values in the configuration file to the desired character set name, such as gbk, gb2312, latinl, etc. After modification, restart MySQL service to take effect. Readers can use the SHOW VARIABLES LIKE 'character_set_°%'; command to view the current character set for comparison when modifying a character set.

06 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 before it. For example, to sort multiple columns in descending order, you must add the DESC keyword to the column name of each column.

Unlike DISTINCT, DISTINCT cannot be partially used. In other words, the DISTINCT keyword applies to all columns and not just the *** specified columns that follow it. For example, query 3 fields s_id, f_name, f_price, if the combination values of these 3 fields are different for different records, all records will be queried.

Can ORDER BY be mixed with LIMIT?

When using an ORDER BY clause, make sure it follows the FROM clause, or LIMIT must follow ORDER BY, and MySQL generates an error message if the clauses are not in the correct order.

08 When to use quotation marks?

When querying, you will see conditions used in WHERE clauses, some values enclosed in single quotes, and some values without quotes. Single quotation marks are used to qualify strings, if values are compared to string type columns, they are required; quotation marks are not required to compare values.

09 Do I have to use parentheses in the WHERE clause?

Whenever you use a WHERE clause with AND and OR operators, you should use parentheses to clarify 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.

10 Do I have to specify a WHERE clause to update or delete a table?

As you can see earlier, all UPDATE and Delete statements specify conditions in the WHERE clause. If the WHERE clause is omitted, UPDATE or Delete is applied to all rows in the table.

Therefore, unless you do intend to update or delete all records, be careful to use UPDATE or Delete statements without a WHERE clause. It is recommended that you use SELECT statements to identify records that need to be deleted before updating and deleting tables to avoid irreparable results.

What are the MySQL database skills to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.