Introduction to the SELECT statement in Mysql and its use example _mysql

Data tables have been created, assuming that we have inserted a lot of data, we can use the way we like to retrieve and display the information in the datasheet, for example: You can display the entire datasheet as follows
SELECT * from President;
You can also select only one data column in one row of data
Select birth from President where Last_name= ' Eisenhower ';
The general form of the SELECT statement is as follows:
Select the information you want
From datasheet (one or more)
where the condition satisfies
The SELECT statement has several clauses that help you find the information that interests you the most, and these clauses can be simple or complex to see how the author explains them in detail.

1, using various operators to set the search conditions
For a SELECT statement to retrieve only records that meet a specific condition, you must add a WHERE clause to the search criteria for the data row. Only in this way can you selectively select the data rows that meet the specific requirements of the data column's values. You can look up any type of value, for example, search for a number
SELECT * FROM score where score>95; Displays all points above 95 points of information
You can also find a string value
Select Last_name,first_name from President where Last_name= ' Tom '; Find out all the presidents with Tom's name.
You can also combine different types of values to find
Select Last_name,first_name,birth,state from President
where birth< ' 1950-1-1 ' and (state= ' VA ' or state= ' BA ');
To find the president who was born 1950 years ago in VA or BA states.
We should be proficient in understanding the meaning of these operators (simple) by showing that there are arithmetic operators (+-*/%), Comparison operators (<>=), and logical operators that can be used in the WHERE clause.

2, the special treatment of NULL value
This is a value that does not belong to any type. It is commonly used to denote the "no data" "Data Unknown" "Data Missing" "data out of range" "" and this data column is not related to the other values of this data column, and other meanings. In many cases, NULL values are useful.
Our various operators are not able to handle null values, and if a relative null value is found, use is NULL or is NULL to determine, 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 slowly.

3, the query results are sorted
Generally, if you create a data table and insert some records into it, when a select * from name command is issued, the order in which the data is recorded in the query results is usually the same as the order in which they were inserted. This is of course in line with our thinking habits. But this is only a "take for granted" assumption, in fact, but when the record is deleted, the database will produce some empty areas, MySQL will use new records to fill these areas, that is, this assumption is not correct. So we have to remember that the sequence of rows returned from the server is not guaranteed! If you want to do so in a certain order, you must use the ORDER BY clause to set this sequence.
Select Last_name,first_name,birth,state from President
Order by Last_Name; Put the presidents ' names in alphabetical order.
You can also set ascending descending order of arrangement
Select Last_name,first_name from President
ORDER by state Desc,last_name ASC;
Arranged in descending order of place of birth, in ascending order of last name in the same place of birth
Note: If the results contain 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, just use the limit clause to see two examples:
Select Last_name,first_name,birth,state from President
Order by birth limit 5; I just want to see the first 5.
Order BY birth limit 10, 5; Returns 5 records starting with 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 a method of expression evaluation, which works everywhere.

5, the output column for evaluation and naming
In order to improve efficiency, MySQL can also take the evaluation of the expression as the output column value. An expression 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), and then an 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
And look at this command: combine two output columns into one
Select Concat (First_namem, ', last_name), concat (city, ', ', state) from President;
If the header 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 of all remember: in MySQL, the year is put to the front! We usually do the following for dates:
Sort by date
Find a date or date range
Extracts the year in the date, the yuan, each part of the day
Calculate a two-date interval
Find another date with a date
See Example:
SELECT * from event where date= ' 2002-10-01 '//See what exam information is available this day?
Select Last_name,first_name,birth,state from President
where death> ' 1900-01-01 ' and death< ' 2000-01-01 '; How many died in the last century?
The three function year,month,dayofmonth can be separated from the date of the month.
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 6? Tom )
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 been alive! You should change it to a year.
The subtraction of date values also helps us figure out how long it will take to get to a specific date, which is what we use to find members who need to pay their dues in the near future:
Select Last_name,first_name,expiration from
Where (to_days (expiration)-to_days (Curdate ()) <60;//Some people need to spend 60 days!

7, pattern matching
In some cases, fuzzy queries are necessary, and we can use like and not to add a string with wildcard characters. A total of two wildcard "_" (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 is W or W

8. Set up and use SQL variables
MYSQL 3.23.6 version can use the query results to set variables, we will be able to save some of the results for his use. The naming specification for variables is: @name, the assignment syntax is @name: =value (Pascal) is also simple to use:
Select @birth: =birth from President
where last_name = ' adsltiger '; Once the execution is complete, 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 older than me!

*9, generating statistical information
Relying solely on hand to generate statistics is a hard and time-consuming and error-prone task, and if we are proficient in using databases to generate statistical information, he will become a powerful information processing tool. The author used a lot of space here to talk about the subject, and for the sake of understanding, I break it down:


9.1 It is a common statistic to find out how many different values are in a set of data, and the keyword distinct can erase the duplicate data from the query results. Such as
Select DISTINCT state from President//See the Presidents of the United States are from those States? (Repeat not counted)


9.2 Use the count () function to count the number of related records, notice how they are used: count (*) to calculate all, null, and COUNT (data column name) null value is not counted.
Select COUNT (*) from President;


9.3 If we want to know the number of boys and girls in the class? How do I inquire about this? The easiest way to do this is
Select COUNT (*) from student where sex= ' F ';
Select COUNT (*) from student where sex= ' m
But if you use the Count function to combine the group by keyword, a line of commands is done
Select Sex,count (*) f rom student group by sex;
As we can see, there are many advantages to using the combination of count (*) and group by words to statistically compare the number of occurrences of a data column by using a query that is similar to each other, mainly in the following ways:
Before you start counting, you don't have to know how many different values are in the data column being counted.
Because only one query command is used, we can sort the output to handle
Select State,count (*) as Count from President
GROUP BY state ORDER by count Desc LIMT4; What are the top four states that were born president?


9.4 In addition to count (), we also use some other statistical functions, such as the Min () to find the minimum value of Max (), Sum sum (), the average AVG (), in the actual work, these functions often used!

*10, extracting information from multiple tables
Our current example is to extract information from a table, but the real power of the database is to use "relationships" to synthesize the records in multiple datasheets, an operation called "association" or "binding." We can see that select needs to give the information in multiple datasheets (not repeatable) From needs to know which tables to work from, where the information about the correlation between several tables is described in detail.
First we need to learn the most reliable data column reference way: Data table name. data column name. So in the query will not confuse the data column in the end of which table.


Example 1: Check the students ' test results in one day, and list them with the school number.
Select Scroe.student_id,event_date,score.score.event.type
From Event,score
where event.date= ' 2003-09-12 '
and event.event_id=score.event_id
First, use the event data table to map the date to an exam event number, and use this number to find the test scores that match the score table. Association two tables, a query is done.


Example 2: Check the students ' test results in one day and list them by name.
Select Student.name Event.name,score.score,event.type
Form Event,score,student
where event.date= ' 2003-09-12 '
and event.event_id= score.event_id
and scroe.student_id=student.student_id;
Associate three tables, one query is done.


Example 3: Inquire about the name of the absent student, school number, number of absences
Select Student.student_id,student_name
Count (absence.date) as absences
From Student,absence
where student.student_id=absence.student_id//associated condition
Group BY STUDENT.STUDENT_ID;