MySQL SELECT statement detailed

This article refers to the experimental building of the SELECT statement in detail with their own operating part of the written.

Note: in most systems, SQL statements are case-insensitive, but for rigorous and easy-to-distinguish reserved words and variable names, reserved words should be capitalized when written, and variable names should be lowercase. The so-called reserved words are: Words that have been defined in a high-level language, and users cannot use them as variable names and procedure names.

1) The basic format of the SELECT statement is: Thecolumn name of the select query from the table name where constraints ; In the previous blog create a simple score management system, use the SELECT * from employee representative to query all columns. For example, to view the name, age two columns in the Employee table, you can use: it is worth noting that there is no comma between the age and the from, it is easy to write easily, it is written up, only the column names need a comma.

2) mathematical notation conditions, where constraints are often used in SELECT statements to achieve more precise lookups. Where restrictions can have mathematical symbols (=, >, <, >=, <=), for example, we can find names of people older than 25 years in the employee table,

When there is more than one limit behind the where, you can use or (or) and and (and) to achieve the corresponding requirements, such as can be used or to achieve the age of less than 25, and the age of more than 30 of these two parts of the name; Accordingly, the age between 25 and 30 can be achieved using and,

Another way to find the names of people aged between 25 and 30 is:

3) keywords in and not are used to brush the results of "in" and "not" within a range, for example, to find the names, ages, and phone numbers of people in department 3 and department 4, you can use the following method:

4) wildcard characters. The keyword like is generally used with wildcards, and wildcards represent unknown characters. The wildcard characters in SQL are "_" and "%", which represent an unspecified character, and the latter represents an unspecified character. such as: Only know the first four digits of the phone, the last two bits unknown, want to get the relevant information, can be as follows: It is worth noting that the relative position of the keyword and wildcard .

Another example: only know the first character of the name of ' J ' want to query the relevant information, can be as follows:

5) in order to be more pleasing to the results of the query, we may need to sort the results by a column. The keyword is orderby, and by default, the result of an order by is ascending, using the keyword ASC and DESC to specify ascending and descending . In descending order of wages:

6) SQL built-in functions and calculations. SQL has 5 built-in functions, as follows:

Where the Count function can be used for any data type, SUM, AVG can only evaluate numeric class data types, and MAX, Min can be used for calculations of numbers, strings, and date-time types.

If you calculate the maximum and minimum value of salary, you can use:

Among them, mainly to the relationship of the two green ellipses, by keyword As,max (salary) renamed in order to Max_salary.

7) When working with multiple tables, subqueries are only useful when the results come from a table, and if you need to display data for two tables or multiple tables, use a JOIN operation. The basic idea of a connection is to manipulate two or more tables as a new table . Query the number of employees in the department, where the ID and name of the employee are from the employee table, people_num from the Department table. As follows:

Another, the format of the join statement is the join on syntax, just the statement is equivalent to:

MySQL SELECT statement detailed