Quickly grasp the SELECT statement in MySQL database

This paper is a fast and precise grasp of the SELECT statement in MySQL database.

The basic syntax for a SELECT statement in MySQL is:

The following is a reference fragment:
SELECT [Straight_join] [Sql_small_result]
[Sql_big_result] [High_priority]
[distinct| distinctrow| ALL]
Select_list
[Into {outfile| DumpFile} ' file_name ' export_options]
[From Table_references [WHERE where_definition]
[GROUP by Col_name,...] [Having where_definition]
[Order BY {Unsighed_integer|col_name|formura} [asc| DESC],...]
[LIMIT [Offset,] rows] [PROCEDURE Procedure_name]]

As you can see from this basic syntax, the simplest SELECT statement is the select select_list, and you can actually use the simplest SELECT statement to do many of the things you expect, and you can use it for any operation that MySQL supports, for example: SELECT 1+1, it will return 2; second, you can also use it to assign values to variables, and in PHP, using the function of the SELECT statement, you are free to use MySQL's functions to perform various operations on the PHP program and assign values to variables. In many cases, you will find that MySQL has many functions that are more powerful than PHP.

Straight_join, Sql_small_result, Sql_big_result, high_priority are MySQL extensions to the ANSI SQL92. If the optimizer joins tables in a non optimal order, using Straight_join can speed up the query.

Sql_small_result and Sql_big_result are a set of relative keywords. They must be used in conjunction with group BY, distinct, or distinctrow. Sql_small_result told the optimizer that the results would be small, requiring MySQL to use temporary tables to store the final tables instead of using the sort; instead, Sql_big_result told the optimizer that the results would be small, requiring MySQL to use sort instead of temporary tables.

High_priority will give the select a higher priority than a statement that updates the table, making it possible to make a quick query first.

The use of the above four keywords is indeed more obscure. Fortunately, in most cases, we can choose not to use these four keywords in mysql.

DISTINCT, Distinctrow provides a basic but useful filter for the result set returned by the query. That is, the result set contains only distinct rows. Note here that for the keyword distinct, distinctrow, null values are equal, no matter how many null values, select only one. And all the use of the superfluous suspicion. It has no effect on the production of the result set.

into {outfile| DumpFile} ' file_name ' export_options to write a result set to a file. The file is created on the server host and cannot be already present. The syntax for the export_options part of the statement is the same as in the fields and lines clauses used in the LOAD datainfile statement, which we will discuss in detail in the MySQL Advanced _load data section. The difference between outfile and DumpFile is that you write only one line to the file, and no columns or rows end.

Select list: This can contain one or more of the following:

1, "*", representing all columns arranged in the order of the CREATE table.

2. List of column names in the order of the users.

3, you can use aliases to replace the column name, in the form of: column name as column_heading.

4, expression (column name, constant, function, or any combination of column names, constants, and functions that are connected by arithmetic or bitwise operators).

5, internal function or aggregate function.

6. Any combination of the above items.

From: Determines which tables are used in the Select command. This is generally required unless the column name is not included in the select_list (for example, only constants, arithmetic expressions, and so on). If there are multiple tables in the table entry, separate them with commas. The order of the tables after the keyword from does not affect the result.

The table name can be given a related alias so that the expression is clear. The syntax here is tbl_name [as] alias_name. For example:

Select T1.name,t2.salary from employee as t1,info as T2 where t1.name=t2.name with select T1.name,t2.salary from employee t1,in The FO t2 where t1.name=t2.name is completely equivalent.

All other references to the table, such as in the WHERE clause and the HAVING clause, are aliased, and the alias cannot begin with a number.

The WHERE clause sets the search condition, and its application in the Insert,update,delete statement is exactly the same as the method applied in the SELECT statement. The search condition is immediately following the keyword where. If a user wants to use more than one search condition in a statement, it can be connected with and OR OR. The basic syntax for search conditions is [not] expression Comparison_operator expression; [NOT] expression (not) like "match_string"; [NOT] expression are [not] null; [NOT] expression [not] between expression and expression; [NOT] column_name join_operator column_name; [NOT] boolean_expression.

And: To join two conditions and return the result when all two conditions are true. When multiple logical operators are used in the same statement, the AND operator is always the highest priority, unless the user changes the order of operations with parentheses.

Or: is used to join two conditions and return the result when any of the two conditions are true. When more than one logical operator is used in the same statement, the operator or is usually performed after the operator and. Of course, users can use parentheses to change the order of operations.

Between: A keyword used to identify the lower bound of a range, followed by a value with a range cap. The range where @val between x and y contain the end value. If the first value specified after between is greater than the second value, the query does not return any rows.

COLUMN_NAME: The name of the column used in the comparison. When ambiguity arises, be sure to indicate the name of the table in which the column is located.

Comparison_operator: comparison operator. See table below:

The following is a reference fragment:
Symbolic meaning
= equals
> Greater than
< less than
>= is greater than or equal to
<= less than or equal
!= is not equal to
<> Not equal to

When comparing Char,varchar data, "<" means closer to the head of the alphabet, and ">" represents closer to the end of the alphabet. In general, lowercase letters are larger than uppercase letters, uppercase letters are larger than numbers, but this may depend on the comparison order of the operating systems on the server.

At the time of comparison, the trailing spaces are ignored. For example, "Dirk" equals "Dirk".

When comparing a date, "<" is earlier than, ">" indicates later than.

When using comparison operators to compare character and datetime data, enclose all data in quotes.

Expression: May be a column name, constant, function, or any combination of column names or constants, as well as functions connected by arithmetic or bitwise operators. The arithmetic operators are shown in the following table:

The following is a reference fragment:
Symbolic meaning
+ Plus
-Minus sign
* Multiplication
/Division

Is null: used when searching for a null value.

Like: keywords, for char, varchar and datetime (excluding seconds and milliseconds) can use like, in MySQL like also can be used in numeric expressions.

When a user searches for datetime data, it is best to use the keyword like, because the full datetime record contains a variety of date components. For example, the user adds a value of "9:20" to the column arrival_time, and the clause where arrival_time= "9:20" does not find it, because MySQL converts the input data into a "1,1900 9:20am". However, the clause where arrival_time like "%9:20%" can be found.

Boolean_expression: An expression that returns a value of "true" or "false."

Match_string: A string consisting of characters and wildcards, enclosed in single or double quotes, is a matching pattern. The wildcard characters are shown in the following table:

The following is a reference fragment:
Symbolic meaning
String of% 0 or more characters
_ Any single character
Not: Negation of any logical expression, or keyword,
such as Like,null,between and so on.
The GROUP BY and having clauses are used in the SELECT statement,
You can divide a table into groups and return a group that matches the conditions of a HAVING clause.
Syntax: Beginning of SELECT statement
GROUP BY [all] aggregate_free_expression [, aggregate_free_expression]*
[Having search_conditions]

End of SELECT statement

GROUP BY: Specifies the groups that the table will divide, and if you include aggregate functions in a Select table entry, calculate a total for each group. The results of these total values are displayed in a new column, not a new row. In the HAVING clause, the user can refer to these new total columns. You can use the collection of AVG, COUNT, Max, Min, and sum in the select_list before group by

Would you like to issue an award-winning or other message by randomly taking out some eligible emails or mobile phone number users for an activity in your job? This article explains how to extract random numbers in a number of ways by example.

If so, you can use ORACLE to generate random numbers of Pl/sql, directory file name in:/oracle_home/rdbms/admin/dbmsrand.sql.

To compile with the SYS user first: Sql>@/oracle_home/rdbms/admin/dbmsrand.sql.

It actually generates a DBMS_RANDOM package under the SYS user, generates a public synonym, and authorizes all database users to have permission to execute.

Using the Dbms_random package, the method of taking random data out:

1. First create a unique growth serial number tmp_id:

The following is a reference fragment:
Create sequence tmp_id increment by 1-start with 1 maxvalue 9999999 nocycle;

2. Then create a temporary table tmp_1 to take out all records that meet the conditions of this activity:

The following is a reference fragment:
CREATE TABLE Tmp_1 as select Tmp_id.nextval as ID, email,mobileno from table name where condition;

Maximum ID number found: SELECT MAX (ID) from tmp_1;.

3. Set a seed to generate random numbers:

The following is a reference fragment:
Execute Dbms_random.seed (12345678); or Execute Dbms_random.seed (to_char (sysdate, ' mm-dd-yyyy HH24:MI:SS '));

4. Call random number generation function Dbms_random.value generate temporary table tmp_2 (assuming 200 randomly):

The following is a reference fragment:
CREATE TABLE tmp_2 as Select Trunc (Dbms_random.value (1,5000)) as ID from tmp_1 where rownum<201;

[Description: Dbms_random.value (1,5000) is a random number between 1 and 5000, with decimals,

The trunc function takes a random number rounding to correspond to the integer ID field of a temporary table.

Note: If the tmp_1 record is more (100,000 or more), you can also find a table of about 200 rows (if it is tmp_3) to generate tmp_2

The following is a reference fragment:
CREATE TABLE tmp_2 as Select Trunc (Dbms_random.value (1,5000)) as ID from Tmp_3 where rownum<201; ]

5. Tmp_1 and tmp_2 associated with the acquisition of eligible 200 users

The following is a reference fragment:
Select T1.mobileno,t1.email from Tmp_1 T1,
Tmp_2 T2 where t1.id=t2.id;

[Note: If the tmp_1 record is more than 100,000, you need to index the ID field.] ]

You can also output to a text file:

The following is a reference fragment:
Set pagesize 300;
Spool/tmp/200.txt;
Select T1.mobileno,t1.email from Tmp_1 T1,
Tmp_2 T2 where t1.id=t2.id order by T1.mobileno;
Spool off;

6. After use, delete temporary table tmp_1, tmp_2 and serial number tmp_id.

This article is organized in the Internet, welcome to the original author letter signed copyright zujizhe@chinaz.com