Data tables have been created. Suppose we have inserted a lot of data, we can search and display the information in the data table in the way we like, and the data table has been created. Suppose we have inserted a lot of data, we can search and display the information in the data table as we like. for example, we can display the content in the entire data table as follows:
Select * from president;
You can also select only one data column in a data row.
Select birth from president where last_name = 'Eisenhower ';
The general form of the select statement is as follows:
Select the information you want
From data table (one or more)
Where conditions
The select statement has several clauses. Their combinations can help you find the most interesting information. these clauses can be very simple or complex, and you can see how the author explains them in detail.
1. use various operators to set search conditions
To allow the select statement to retrieve records that meet specific conditions, you must add the where clause to set the search conditions for data rows. Only in this way can we select the data rows whose values of the data column meet the specific requirements. You can search for any type of value. for example, you can search for a value.
Select * from score where score> 95; // displays information of all scores above 95.
You can also search for string values.
Select last_name, first_name from president where last_name = 'Tom '; // find all the presidents named Tom.
You can also search for different types of values in combination.
Select last_name, first_name, birth, state from president
Where birth <'2014-1-1 'and (state = 'Va' or state = 'ba ');
// Identify the president born in VA or BA state before January 1, 1950
It can be seen that the where clause can use arithmetic operators (+-*/%), comparison operators (<> =), and logical operators, we should be familiar with the meaning of these operators (both very simple)
2. special handling of NULL values
This is a value that does not belong to any type. It is usually used to indicate "no data", "unknown data", "missing data", "data out of value range", "irrelevant to this data column" and "different from other values in this data column", etc. meaning. In many cases, the NULL value is very useful.
Our various operators cannot process NULL values. if we look for NULL values, we use is null or is not null to judge them. for example:
Select last_name, first_name, birth, state from president
Where death is null; // finds all dead presidents
In some cases, the NULL value is a useful type and you will understand it later.
3. sort the query results
Generally, if you create a data table and insert some records into it, when you issue a select * from name command, the order of data records in the query results is usually the same as that when they are inserted. this is certainly in line with our habits of thinking. however, this is just a "take it for granted" assumption. In fact, when a record is deleted, some blank areas will be generated in the database, and MYSQL will fill these areas with new records, that is to say, this assumption is incorrect at this time. therefore, we must remember that the order of record rows returned from the server is not guaranteed! If you want to follow a certain order, you must use the order by clause to set this order.
Select last_name, first_name, birth, state from president
Order by last_name; // sort the presidents in alphabetical order.
You can also set the ascending and descending order of the arrangement.
Select last_name, first_name from president
Order by state DESC, last_name ASC;
// Sort by place of birth in descending order, and by last name in ascending order of place of birth
Note: If the result contains NULL values, they always appear at the beginning of the query result by default.
4. limit the number of data rows in the query results
This is simple. you only need to use the limit clause. let's look at two examples:
Select last_name, first_name, birth, state from president
Order by birth limit 5; // you only want to view the first five
Order by birth limit 11th; // returns 5 records starting from records (10 records are skipped)
Tip: randomly find a president from the president table:
Select last_name, first_name, birth, state from president
Order by rand () limit 1; // This is a method that uses an expression to evaluate the value. it works everywhere.
5. evaluate and name the output column
To improve efficiency, MYSQL can also regard the calculation result of the expression as the value of the output column. Expressions can be simple or complex. For example, the following query has two output columns, the first output
The above is the detailed description of the use of SELECT. for more related articles, please follow the PHP Chinese network (www.php1.cn )!