Introduction and usage Examples of SELECT statements in MySQL

The data tables have been created, we can use the way we like to retrieve and display the information in the data table, the following for you to explain the application of the SELECT statement in MySQL, interested in touch can learn the

datasheet has been created, assuming 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, we can display the contents of the whole datasheet as follows:


SELECT * from President;


can also select only one data column in a single 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 to meet the conditions

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 it in detail





1, using various operators to set the search conditions


If you want the SELECT statement to retrieve only records that meet a specific condition, you must add a WHERE clause to set 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 for any type of value, for example, to search for values


select * FROM score where score>95; Displays all points above 95 points of information


can also be found for string values


Select Last_name,first_name from President where Last_name= ' Tom '; Find out all Tom's President
.

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 ');


//Find President
, who was born in VA or BA state 1950 years ago

visible WHERE clauses can be used for arithmetic operators (+-*/%), Comparison operators (<>=), and logical operators, we should be proficient in understanding the meanings of these operators (both simple)




special treatment of
2, 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 allowed to handle null values, and if a relative null value is found, use is null or is not NULL to determine, for example, as follows:


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


can also set ascending descending order of arrangement


Select Last_name,first_name from President


ORDER by state Desc,last_name ASC;


///first 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 simple, as long as the limit clause can be, look at 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)


tips: Randomly find a president from the President table to play:


Select Last_name,first_name,birth,state from President


ORDER by rand () limit 1; This is the expression evaluation method, where all works





5, evaluate and name output columns


in order to improve efficiency, MySQL can also take the expression of the calculation as 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


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 merging, 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, and date-related issues


first remember: In MySQL, the year is put to the front! We usually do the following for dates:


Sorted by Date


Find a date or date range


extracts the year in the date, the yuan, the day each part


calculates the interval of two dates


to 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?


three functions 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 )

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


can see how many days these guys have been alive! You should change it to a year.

The subtraction of the
date value also helps us figure out how long it will take to get to a specific date, which is the way we use to find members who need to pay their dues in the near future:


Select Last_name,first_name,expiration from member


where (to_days (expiration)-to_days (Curdate ()) <60;//Some people have 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. There are two wildcard characters "_" (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, setting up and using SQL variables


MYSQL 3.23.6 more than the version can use the query results to set the variable, 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:


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


simply relying 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 this subject, so as to make it easier for everyone to understand, 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 uses the count () function to count the number of related records, notice how to use it: 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 is to


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 row of commands is done


Select Sex,count (*) f rom student group by sex;


we can see that 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 similar queries over and over again, mainly in the following ways:


do not have to know how many different values are in the data columns being counted before starting to count


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 have 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;


Association Three tables, a query is done.








Example 3: Inquire about the name of the absent student, the 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 conditions


GROUP BY student.student_id;