Detailed description of select in MySQL

the 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 entire data table content can be displayed as follows
select * from President;
You can select only one data column in a data row.
select birth from President where last_name = 'isenhower ';
the general form of the SELECT statement is as follows:
select the information you want
from data table (one or more)
where conditions are met
SELECT statement has several clauses, their various combinations can help you find the information you are most interested in. 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
the statement only retrieves 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; // display all information with 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 ');
// find the President born in Va or BA state before January 1, 1950
visible 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
value of 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 operators cannot process null values. If the relative null value is searched, use is null or is not null for determination. For example:
select last_name, first_name, birth, State from President
where death is null; // find all the dead presidents
in some cases, the null value is a useful type and you will understand it.

3, sort query results
generally, if a data table is created and some records are inserted into it, when a select * from name command is issued, 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 ascending and descending order
select last_name, first_name from President
order by State DESC, last_name ASC;
// sort data in descending order of place of birth, place of the same place in ascending order of the last name
Note: if the result contains a null value, by default, they always appear at the beginning of the query result.

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;// Only 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 "%" (any character, including 0)
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, we will have a @ birth variable 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 are presidents of the United States 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 conditions
Group by student. student_id;

Here is a simple association operation. In fact, we need to learn a lot about the associated knowledge. For example, we suspect that a data table does not contain data related to us, how do I deal with this table when associating the query? This involves many new concepts of inner join, Outer Join, left join, and right join. I don't know if you have the confidence to look down at my notes? In Chapter 4 of this book, the Association is discussed in great detail. It seems that "in SQL, the statement of" select "is not enough.

We understand so many usage of the SELECT command and feel its flexibility. The combination of many words can form a very "subtle" SQL statement, I have no need for beginners to study things highly skillful at the moment. "a certain degree of creativity is necessary, but it is too professional or skillful.CodeIt is the birthplace of various bugs and a prelude to several sleepless nights"