The data table has been created. Assuming that we have inserted a lot of data, we can retrieve and display the information in the data table in our favorite way. For example: you can put the entire data table as follows The content is displayed
select * from president;
You can also select only a certain data column in a certain 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 satisfies the condition
The
select statement has several clauses, and their various combinations can help you find the information you are most interested in. These clauses can be very simple or complex, see how the author explained
1. Use various operators to set search conditions
If you want the select statement to retrieve only the records that meet certain conditions, you must add a where clause to it to set the retrieval conditions of the data row. Only in this way can we selectively select those data rows whose values meet the specific requirements. You can search for any type of value, for example, search for values
select * from score where score>95; //Display all information with score above 95
You can also search for string values
select last_name,first_name from president where last_name='Tom'; //find out all the presidents whose last name is tom
You can also combine different types of values
select last_name,first_name,birth,state from president
where birth<'1950-1-1' and (state='VA' or state='BA');
//Find the president who was born in VA or BA before 1950
It can be seen that the arithmetic operators (+-*/%), comparison operators (<>=) and logical operators can be used in the where clause.
2. Special handling of NULL values
This is a value that does not belong to any type. It is usually used to indicate various meanings such as "no data", "data unknown", "data missing", "data beyond the value range", "not related to this data column", "different from other values in this data column" and so on. In many cases, NULL values are very useful.
Our various operators cannot handle NULL values. If you are searching for relative NULL values, use is null or is not null to judge. Examples are as follows:
select last_name,first_name,birth,state from president
where death is null; //find out all the undead presidents
In some cases, NULL values are very useful types, and everyone will understand them slowly.
3. Sort the query results
Generally speaking, 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 the order in which they were inserted. This of course fits our thinking habits. But this is just a "take it for granted" assumption. In fact, when records are deleted, some empty areas will be generated in the database. MYSQL will fill these areas with new records. That is to say, this assumption is Not correct. Therefore, we must remember that there is no guarantee of the order of the rows returned from the server! 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; //Let the names of the presidents be arranged in alphabetical order
You can also set the ascending order and descending order
select last_name,first_name from president
order by state DESC,last_name ASC;
//First sort in descending order of birthplace, and sort in the same birthplace in ascending order of last name
Note: If the results contain NULL values, by default they always appear at the beginning of the query results.
4. Limit the number of data rows in the query results
This is simple, just use the limit clause, see two examples:
select last_name,first_name,birth,state from president
order by birth limit 5; //only want to see the first 5
order by birth limit 10,5; //Return 5 records starting from the 11th record (10 skipped)
Tip: Find a president randomly from the president table to play:
select last_name,first_name,birth,state from president
order by rand() limit 1; //This is an expression evaluation method, it works everywhere
5. Evaluate and name the output columns
In order to improve efficiency, MYSQL can also use 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 column corresponds to a very simple expression (a constant), and the latter output column corresponds to a complex expression using multiple arithmetic operators and two function calls formula.
Select 17,format(sqrt(3*3+4*4),0))
Output: 17 5
Look at this command again: merge the two output columns into one
select concat(first_namem,' ‘,last_name),concat(city,',',state) from president;
If the title of the output column is too long after the merge, you can give it an alias, such as:
select concat(first_namem,' ‘,last_name) as name,
concat(city,',',state) as birth place
from president; This is more beautiful.
6. Issues related to dates
First remember: in MYSQL, the year is placed first! We usually perform the following operations on dates:
Sort by date
Find a date or date range
Extract the parts of the year, yuan, and day from the date
Calculate the interval between two dates
Use one date to find another date
See example:
select * from event where date='2002-10-01' //Look at the exam information on this day?
select last_name,first_name,birth,state from president
where death>'1900-01-01' and death<'2000-01-01'; //Look at how many died in the last century?
The three functions year, month, and dayofmonth can separate the year, month, and day of the date.
select last_name,first_name,birth from president
where month(birth)=3; //Who was born in March?
where month(birth)=7 and dayofmonth(birth) =6; //Who was born on July 6th? (Tom Cruise?)
The function to_days can convert dates to days.
select last_name,first_name,birth to_days(death)-to_days(birth) as age from president
You can see how many days these guys have lived! Change it to year yourself.
The subtraction of the date value can also help us calculate how long it is before a specific date. This is exactly the method we use to find the members who need to pay the membership fee in the near future:
select last_name,first_name,expiration from member
where (to_days(expiration)-to_days(curdate())<60; //Some people need to spend money within 60 days!
7. Pattern matching
In some cases, fuzzy query is necessary, we use like and not like plus a wildcard string. There are two wildcards "_" (single character) and "&" (multiple characters)
select concat(first_namem,' ‘,last_name) as name,
where last_name like ‘W%'; //find people who start with W or w
where last_name like ‘%W%’; //find the person whose name starts with W or w
8. Set and use SQL variables
Versions above MYSQL 3.23.6 can use query results to set variables, and we can save some results for other uses. The naming specification of the variable is: @name, the assignment syntax is @name:=value (pascal?) It is also simple to use:
select @birth:=birth from president
where last_name ='adsltiger'; //After the execution is completed, we will have a @birth variable available
Try it with:
select concat(first_namem,' ‘,last_name) as name from president
where birth<@birth order by birth; //Look at those people who are older than me!
9. Generate statistical information
Relying solely on manual generation of statistical information is an arduous, time-consuming and error-prone task. If we can master the skill of generating various statistical information with a database, he will become a powerful information processing tool. The author uses a lot of space here to talk about this topic.
10. Extract information from multiple tables
Our current examples are to extract information from a table, but the real power of the database is to use "relationship" to synthesize the records in multiple data tables. This operation is called "association" or "combination". We can see To, select needs to give information in multiple data tables (not repeatable); from needs to know from which tables to do things; where to make a detailed description of the related information between several tables.
