Introduction and example of select statements in MySQL

Data Tables have been created. If we have inserted a lot of data, we can search and display the information in the data table in our favorite way, for example: the content in the entire data table can be displayed 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 previous output column corresponds to a very simple expression (a constant ), the next output column corresponds to a complex expression that uses multiple Arithmetic Operators and two function calls.
Select 17, format (sqrt (3*3 + 4*4), 0 ))
Output: 17 5
Let's look at this command again: merge two output columns into one
Select concat (first_namem, '', last_name), concat (city, ',', state) from president;
If the title of the output column after merging is too long, you can give it an alias, for example:
Select concat (first_namem, '', last_name) as name,
Concat (city, ',', state) as birth place
From president.

6. Date-related issues
First remember: in MYSQL, the year is placed at the top! We usually perform the following operations on the date:
Sort by date
Search for a date or date range
Extract the year, Yuan, and day of a date.
Calculate the interval between two dates
Use a date to find another date
Example:
Select * from event where date = '2017-10-01 '// check the exam information on this day?
Select last_name, first_name, birth, state from president
Where death> '2017-01-01 'and death <'2017-01-01'; // How many deaths have occurred in the last century?
The year, month, and dayofmonth functions can be used to separate the year, month, and day of the date.
Select last_name, first_name, birth from president
Where month (birth) = 3; // who was born on July 15, March?
Where month (birth) = 7 and dayofmonth (birth) = 6; // who was born on January 1, July 6? (Tom Cruise ?)
The to_days function can convert a date to a number of 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 a year.
The subtraction operation of the date value can also help us calculate the time remaining from a specific date. This is the method we use to find members who need to pay membership dues 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 search is necessary. We can use like and not like to add a string with a wildcard character. There are two wildcards "_" (single character) and "&" (multiple characters)
Select concat (first_namem, '', last_name) as name,
Where last_name like 'W % '; // find the person starting 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
MYSQL 3.23.6 and later versions can use the query results to set variables, so that we can save some results for other users. The variable name type is: @ name. The assignment syntax is @ name: = value (pascal ?) Easy to use:
Select @ birth: = birth from president
Where last_name = 'adsltiger '; // after the execution is complete, a @ birth variable is available.
Try again:
Select concat (first_namem, '', last_name) as name from president
Where birth <@ birth order by birth; // Let's see if those people are older than me!

* 9. Generate Statistics
Relying solely on manual generation of statistical information is a hard, time-consuming, and error-prone task. If we can master the skills of using a database to generate various statistical information, it will become a very powerful information processing tool. I have used a lot of space to talk about this topic here. To make it easier for everyone to understand, I will discuss it separately:


9.1 finding out how many different values in a group of data is a common statistical task, and the keyword distinct can clear duplicate data in the query results. For example
Select distinct state from president // check which states the presidents of the United States come from? (Repeated)


9.2 use the count () function to count the number of related records. Note the usage method: count (*) calculates all records, and NULL also needs; count (data column name) NULL values are not included.
Select count (*) from president;


9.3 if we want to know the number of boys and girls in the class? How can I query it? The simplest method is
Select count (*) from student where sex = 'F ';
Select count (*) from student where sex ='m
However, if the count function is used in combination with the group by keyword, a single line of command is done.
Select sex, count (*) f rom student group by sex;
We can see that, compared with repeatedly using similar queries to calculate the number of occurrences of different values in a data column, count (*) the combination of group by statements has many advantages, mainly manifested in:
Before starting statistics, you do not need to know the different values in the columns to be calculated.
Because only one query command is used, we can sort the output.
Select state, count (*) as count from president
Group by state order by count desc limt4; // check which of the first four States have the largest number of Presidents?


9.4 In addition to count (), we also use other statistical functions, such as finding the min () of the minimum, finding the max () of the maximum, and sum () of the sum (), avg () for averaging. In actual work, these functions are often used!

* 10. extract information from multiple tables
Our current example is to extract information from a table, but the real power of the database is to combine the records in multiple data tables with the "relationship, this operation is called "join" or "join". We can see that select needs to provide information in multiple data tables (which cannot be repeated); from needs to know from which tables to work in; where describes the association information between several tables in detail.
First, we need to learn the most reliable data column reference method: data table name. Data column name. In this way, the query will not confuse the table in which the data column is located.


Example 1: query the scores of students in a day and list them with student IDs.
Select scroe. student_id, event_date, score. score. event. type
From event, score
Where event. date = '2017-09-12'
And event. event_id = score. event_id
First, use the event data table to map the date to a test event number. Then, use this number to find the matching test scores in the score table. Join two tables and perform one query.


Example 2: query the scores of students in a day, and list them by name.
Select student. name event. name, score. score, event. type
Form event, score, student
Where event. date = '2017-09-12'
And event. event_id = score. event_id
And scroe. student_id = student. student_id;
Associate the three tables and perform one query.


Example 3: query the name, student ID, and number of absent students
Select student. student_id, student_name
Count (absence. date) as absences
From student, absence
Where student. student_id = absence. student_id // join Condition
Group by student. student_id;