"MySQL must know" notes-part

"MySQL must know" notes-part

Comments:

Start to organize notes, record their own reading process of doubt and focus, later found that the record does not have much meaning, it is better to go directly to the original book, so give up.
So the note is only part of it, but it's still shared.

1. Language classification:
Clients can be MySQL-provided tools, scripting languages (such as Perl), Web application development languages (such as ASP, ColdFusion, JSP, and PHP), programming languages such as C, C+kjava, and so on.

2. The reason why the table could not be found before:
Although SQL is case-insensitive, some identifiers, such as database names, table names, column names, may be different: in MySQL4.1 and previous versions, these identifiers are case-sensitive by default, and in the MySQL4.1.1 version, these identifiers are not case-sensitive by default.

3, develop a good habit:
Multiple SQL statements must be separated by semicolons (;). MySQL, like most dbms-, does not need to add semicolons after a single SQL statement. However, a particular DBMS may have to append a semicolon to a single SQL statement. Of course, you can always add semicolons if you want. In fact, even if not necessary, but with a semicolon certainly not bad. If you are using the MySQL command line, you must add a semicolon to end the SQL statement.

4, I always use, later remember so optimized:
Unless you do need each column in the table, it's best not to use the * wildcard. Although using wildcards may make your own
The required columns are explicitly listed, but retrieving the columns that are not needed usually reduces the performance of the search and the application.

5, for the understanding of distinct:

The DISTINCT keyword applies to all columns, not just the columns that are predecessor to it . If a SELECT DISTINCT vend_id is given,
Prod_price, unless the specified two columns are different, all rows will be retrieved.

Specific to see the tutorial, very detailed

6. Usage of LIMIT: I always misunderstand.

LIMIT 5: Indicates that MySQL returns no more than 5 rows.

LIMIT 5, 5: Indicates that MySQL returns 5 rows starting at line 5. The first number is the start position, and the second number is the number of rows to retrieve.

Note: The MySQL line count starts at 0. Therefore, LIMIT 1, 1 will retrieve the second row instead of the first row.

7. Clauses:

SQL statements are made up of clauses, some are required, and some are optional. A clause typically consists of a keyword and the provided data set
Yes. Examples of clauses are the FROM clause of the SELECT statement, where clause, etc...

8, multi-column sorting:

When sorting by more than one column, the sort is done exactly in the order specified.
The first column is sorted first, and then the second column is sorted based on the result of the first column. If all the values in the first column are unique, they are not sorted by the second column.

If you want to sort in descending order on more than one column, you must specify the DESC keyword for each column. (Not written: Default ascending)

9. When to use quotation marks:

Single quotation marks are used to qualify strings. If you compare a value to a column of type string, you need to limit the quotation marks. The value used to compare with the numeric column is not quoted.

10, Between:

When using between, you must specify two values-the low-end value and the high-end value of the desired range. These two values must be in the AND keyword
Separated. Between matches all values in the range, including the specified start value and end value.

11. Is the space and the empty string different?

NULL (no value), which differs from a field that contains 0, an empty string, or a space that contains only spaces.

12, NULL and mismatched

When you select rows that do not have a specific value by filtering, you may want to return a row with a NULL value. But, No.

Because the unknown has a special meaning, the database does not know if they match, so they are not returned when matching filtering or mismatched filtering.
Therefore, when filtering data, be sure to verify that the returned data does give a row with NULL in the filtered column.

13, and and or priority issues:

14. Why do I use the in operator? The advantages of the in operator:

• The In operator syntax is clearer and more intuitive when using a long list of legitimate options.
• When you use in, the order of calculations is easier to manage (because fewer operators are used).
• The in operator is generally faster than the OR operator manifest.
• The greatest advantage of in is that it can contain other SELECT statements, allowing more dynamic creation of WHERE clauses.

15. Wildcard characters and like:

Like indicates MySQL, followed by a search pattern that uses wildcard matching instead of direct equality matching for comparison.

Percent semicolon (%) wildcard: represents 0, one, or more characters in a given position in a search pattern.

The underscore (_) wildcard character: uses the same as%, but underscores match only a single character rather than multiple characters.

Note When using wildcard characters:

• Note trailing spaces: Trailing spaces may interfere with wildcard matching. For example, when you save the word anvil, if there is one or more spaces behind it, the clause whereprod_name like '%anvil ' will not match them because there are extra characters after the last L. An easy way to solve this problem is to append a% to the search pattern at the end. A better approach is to use the function (which will be introduced in the 11th chapter) to remove the trailing spaces.
• Note null: Although it seems that the% wildcard can match anything, there is one exception, that is, NULL. Even WHERE prod_name like '% ' does not match the line with the value NULL as the product name.

Tips for using wildcard characters:

• There is a cost to using wildcards: Wildcard searches generally take longer to process than other searches discussed earlier.
• Do not use wildcard characters excessively. If other operators can achieve the same purpose, other operators should be used.
• When you do need to use wildcards, do not use them at the beginning of the search pattern unless absolutely necessary. The wildcard character is placed at the beginning of the search pattern and is the slowest to search.
• Pay close attention to the position of the wildcard character. If misplaced, you may not return the data you want.

16. Predicate:

When is the operator not an operator? The answer is when it acts as a predicate (predi-cate). Technically, like is a predicate rather than an operator. Although the end result is the same, you should be aware of the term in order to avoid encountering this term in a SQL document.

17. Regular Expressions:

REGEXP: It tells the mysql:regexp what to do with the regular expression (a regular expression that matches the text body 1000).

• 1. The regular expression differs from the like:

• 1. Match is case-insensitive: regular expression matching in MySQL (since version 3.23.4) is not case-sensitive (that is, uppercase and lowercase are matched). For case sensitivity, you can use the BINARY keyword, such as where Prod_name REGEXP
     BINARY ' JetPack. 000 '.

• | is the OR operator of the regular expression.

• is another form of an OR statement: [123] Defines a set of characters, which means to match 1 or 2 or 3

• Use-to define a range

• Match Special characters (escape characters):

must be preceded by \. \-means find-, \. Represents a lookup.

• Match \: To match the backslash (\) character itself, you need to use \ \.

Note: \ or \? Most regular expression implementations use a single backslash to escape special characters so that the characters themselves can be used. But MySQL requires two backslashes (MySQL itself interprets one, and the regular expression library interprets the other).

• Match character class:

• Match multiple instances

• Locator characters

Attention:

• ^ 's dual use ^ There are two ways to use it. In the collection (defined by [and]), it is used to negate the collection, otherwise it is used to refer to the beginning of the string.
• Character sets can also be negated, that is, they will match anything except the specified character. To negate a character set, place a ^ at the beginning of the collection. So, although [123] matches characters 1, 2, or 3, [^123] matches anything except those characters.

18. Calculated fields:

Field: Basically the same as the column, which is often used interchangeably, but the database column is generally called a column, and the term field is usually used on the connection of the calculated field.

Client and Server format: Many of the transformations and formatting work that can be done within a SQL statement can be done directly within the client application. In general, however, it is much faster to complete these operations on the database server than in the client because the DBMS is designed to do this quickly and efficiently.

1. Splicing fields: The values are joined together to form a single value.

In MySQL, you can use the concat () function.

Concat () requires one or more specified strings, separated by commas between each string.

2. Delete extra spaces in the data:

You can use the MySQL RTrim () function.

RTrim () function: Removes all spaces to the right of the value.

LTrim () Function: Remove the blank space on the left side of the string.

Trim () function: Remove the left and right spaces on both sides of the string.

3. Using aliases

An alias (alias: Also known as an export column) is an alternate name for a field or value. Aliases are given with the AS keyword.

4. Perform arithmetic calculations:

MySQL supports basic arithmetic operators:

19. Function:

Considerations for using Functions:

function does not have a strong portability of SQL:
Code that can run on multiple systems is called portable (portable).

In contrast, most SQL statements are portable, and when there are differences between SQL implementations, these differences are often less difficult to handle.

But the portability of the function is not strong. Almost every major DBMS implementation supports functions that are not supported by other implementations, and sometimes the differences are large.

For code portability, many SQL programmers do not approve of the use of specially implemented features. While this is beneficial, it does not always benefit the performance of your application. If you do not use these functions, it can be difficult to write some application code. Other methods must be used to achieve the work that the DBMS has done very effectively. If you decide to use a function, you should be sure to comment on the code so that later you (or
Others) can know exactly what the SQL code is written to mean.

1. Text Processing function:

SOUNDEX is an algorithm that converts any text string into an alphanumeric pattern that describes its voice representation. SOUNDEX considered similar pronounced characters and syllables, making it possible to compare strings in pronunciation rather than letters.

2. Date and time processing functions:

The data type is DateTime: Stores the date and time values. The values in the sample table all have a time value of 00:00:00

Retrieve all orders from September 2005: (Two methods)

3. Numerical processing function

20. Aggregation function (aggregate functions): A function that runs on a row group and evaluates and returns a single value.

AVG () function: counts the number of rows in a table and calculates the sum of specific column values, and evaluates the average of the column.

• Can only be used to determine the average of a particular numeric column, and the column name must be given as a function parameter.
• To get the average of multiple columns, you must use more than one AVG () function.
• The AVG () function ignores rows where the column value is NULL.

Count () function: counts.

• Use COUNT (*) to count the number of rows in a table, regardless of whether the table column contains a null value (NULL) or a non-null value.
• Use Count (column) to count the rows that have values in a particular column, ignoring null values.

Max () function: Returns the maximum value in the specified column. MAX () requires a column name to be specified

• Using Max () for non-numeric data is typically used to find the largest numeric or date value, but MySQL allows it to return the maximum value in any column, including the maximum value in a text column.
• When used with text data, MAX () returns the last row if the data is sorted by the appropriate column.
• The MAX () function ignores rows where the column value is NULL.

Min () function: functions exactly as opposed to the MAX () function, which returns the minimum value of the specified column. Requires specifying a column name.

• Use min () for non-numeric data (): the Min () function is similar to the MAX () function, which MySQL allows to return the smallest value in any column, including the smallest value in the returned text column.
• When used with text data, MIN () returns the front row if the data is sorted by the appropriate column.
• The MIN () function ignores rows where the column value is NULL.

SUM () function: Used to return the sum (grand total) of the specified column value.

• SUM (item_price*quantity): With standard arithmetic operators, all aggregation functions can be used to perform computations on multiple columns.
• Null value: The SUM () function ignores rows where the column value is NULL.

Aggregation functions can be used as follows:

• Performs a calculation on all rows, specifying the all parameter or not giving the parameter (because all is the default behavior);
• Contains only a different value, specifying the DISTINCT parameter.

Note: If you specify a column name, DISTINCT can only be used for COUNT (). DISTINCT cannot be used with count (*), so count (DISTINCT) is not allowed, otherwise an error is generated. Similarly, DISTINCT must use a column name and cannot be used for calculations or expressions.

Follow-up does not tidy up, the use of small, if necessary, can directly read the original book. The original book is for the purpose of tool reference.

"MySQL must know" notes-part