MySQL Select statement retrieval records using the detailed

The most common operation of database is "plug, check, delete, change". In the previous log, I introduced the MySQL inserts INSERT statement, also introduced the update UPDATE statement for everyone, as well as deletes the statement delete and the TRUNCATE statement. Let's introduce you today to the only data-retrieval statement in MySQL: SELECT statement.

The SELECT statement is the most commonly used statement of SQL developers, and the most powerful weapon, fortunately, learning this statement is not difficult.

Definition of SELECT statement:

Everything has to follow the rules, so like the previous tutorial, let's look at the syntax structure of the SELECT statement mentioned above:

The code is as follows

Copy Code

SELECT [All | DISTINCT | Distinctrow] [high_priority] [Straight_join] [Sql_small_result] [Sql_big_result] [Sql_buffer_ Result] [Sql_cache | Sql_no_cache] [sql_calc_found_rows] select_expr, ... [into outfile ' file_name ' export_options | Into DumpFile ' file_name ' [from table_references [WHERE where_definition] [GROUP by {col_name | exp R | Position} [ASC | DESC], ... [with ROLLUP]] [having where_definition] [ORDER by {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count offset offset}] [PROCEDURE procedure_name (argument_list)] [For UPDATE | LOCK in SHARE MODE]]

Oh, see? Its syntax is quite long, but one thing you should know is that inside of brackets is something that can be omitted. There are times when these things are not used. We come a little bit, don't be intimidated by this syntax, but the SELECT statement is very simple, and the syntax is so complex because the SELECT statement is very powerful.

Select performs mathematical operations:

We can use the SELECT statement to perform mathematical operations, of course, if the formula is too complex to forget, otherwise the math is not dead? Ha ha. Simple to give you a few examples, explain the problem on the line, as for the addition and subtraction within 100, we will not trouble MySQL ha. Oh.

The code is as follows	Copy Code
Select +, 50>= (2+3), 100/3; /* +-----------+-----------+---------+ | 100 + 100 | 50>= (2+3) | 100/3 | +-----------+-----------+---------+ | 200 | 1 | 33.3333 | +-----------+-----------+---------+ 1 row in Set (0.00 sec) */

Select retrieves the specified rows and columns:

The SELECT statement supports the * wildcard character, which means that all fields are obtained. At the same time, the user is also supported to specify a field name to return the specified column. For example, the following statement returns all the data columns of a previously used Twitter plug-in, and you can use the wildcard character.

The code is as follows	Copy Code
SELECT * from Wp_threadtwitter_users G; /* 1. Row *************************** id:16628009 Name:simaopig Screen_name:simaopig Location Description Profile_image_url:http://s3.amazonaws.com/twitter_production/profile_images/180700745/head_normal.png Url:http://www.111cn.net Protected:1 Followers_count:1 1 row in Set (0.00 sec) */

If I just want to look at the URL and all the information in the Name field, you can use the following statement to specify the field name of the column that you want to show the result set:

The code is as follows	Copy Code
Select Name,url from Wp_threadtwitter_users; /* +----------+---------------------------+ | name | URL | +----------+---------------------------+ | Simaopig | http://www.111cn.net | +----------+---------------------------+ 1 row in Set (0.00 sec) */

Select adds restrictions to query results:

We can use the WHERE clause to specify the constraints of the query for the SELECT statement, as long as the result of the WHERE clause is satisfied to be displayed in the result set, such as the following statement, I want to get from the Wp_threadtwitter_users To return only the URL of a user named Simaopig, you can use the following statement:

The code is as follows	Copy Code
Select URL from wp_threadtwitter_users where name = ' Simaopig '; /* +---------------------------+ | URL | +---------------------------+ | http://www.111cn.net | +---------------------------+ 1 row in Set (0.00 sec) */

The WHERE clause can also specify a more complex scope, for example, I would like to query, in my published log, the number of comments is greater than 30, and less than 50 of the article ID and title can specify the following clause:

The code is as follows

Copy Code

Select Id,post_title,comment_count from wp_posts where Comment_count > Comment_count < limit 2; /* +-----+-------------------------------------------------------+---------------+ | ID | Post_title | Comment_count | +-----+-------------------------------------------------------+---------------+ | 488 | Sea girl, another unlucky egg | 46 | | 501 | Resolve mail to commenter messages sent as garbage | 36 | +-----+-------------------------------------------------------+---------------+ 2 rows in Set (0.01 sec) */

Select uses built-in functions:

Long ago, I introduced the functions of MySQL, MySQL for you to provide a very rich built-in functions to meet the daily needs of everyone, below I give you two examples, using MAX, MIN, COUNT these three functions, To return the most commented articles, the least commented articles, and the total number of posts I published in my Journal:

The code is as follows	Copy Code
Select Max (comment_count), Min (comment_count) from wp_posts; /* +--------------------+--------------------+ | Max (Comment_count) | Min (comment_count) | +--------------------+--------------------+ | 362 | 0 | +--------------------+--------------------+ 1 row in Set (0.01 sec) */

From the above results can be seen, I also have the article sofa preservation so far. Oh, who is interested can go over, rob this fell a lot of gray sofa. Oh.

The code is as follows	Copy Code
Select COUNT (*) from wp_posts; /* +----------+ | COUNT (*) | +----------+ | 340 | +----------+ 1 row in Set (0.00 sec) */

From the results can be seen, I saved the draft, the site from the establishment so far, Keoko has written 340 posts, although the draft accounted for a large proportion. Oh. However, I always knock one word at a time. Ha.

Select to alias the table:

Sometimes we have to query the name of the table is too long, often knocking these names is very tiring, especially my lazy people, MySQL provides us with the table alias mechanism, so that we can give the table in the query when the individual name, we can understand as a nickname, such as I tube "kitten" called "bastard", then when I shouted "bastard "When, of course, someone knew I was calling him. Ha.

Alias the table, you can specify it with the AS clause after specifying the table name of the query with the SELECT statement, for example, I alias wp_threadtwitter_users for users, and in the following query I specify the field with the alias, which is convenient in the subquery, OH

The code is as follows	Copy Code
SELECT * from Wp_threadtwitter_users as users where screen_name like '%mg% ' and Users.followers_count > G

Select limits the number of query results:

Sometimes the number of queries to the results of too many, so that a screen can not be displayed, so how do we see the results? Take it easy, MySQL. Provides us with a limit clause that, when used with a SELECT statement, restricts the number of query results, followed by the number of rows of the result set you want to get directly after the limit clause, using the following method:

The code is as follows	Copy Code
Select ID from wp_posts where ID > 3 and comment_count > limit 2; /* +-----+ | ID | +-----+ | 86 | | 488 | +-----+ */

You can also specify an offset, for example, the following example starts at line 4th (note, counting from 0) and returns 2 records:

The code is as follows	Copy Code
Select ID from wp_posts limit 3, 2; /* +----+ | ID | +----+ | 26 | | 28 | +----+ 2 rows in Set (0.00 sec) */

select groups and sorts the results of the query:

MySQL provides order by and group BY, combining SELECT statements to sort and group the results returned by the query. You can customize the sorting method by adding ASC and DESC keywords to two fields in the ORDER BY clause, and if you do not specify an ORDER BY clause, MySQL sorts the result set by default to ascending. The values of the corresponding fields are sorted in ascending or descending order. Group BY is followed by the field name, meaning to group the query results by that field:

Take Keoko published blog post ID, take 3, according to the ID to the reverse order:

The code is as follows	Copy Code
Select ID from wp_posts ORDER BY id desc limit 3; /* +------+ | ID | +------+ | 1451 | | 1450 | | 1449 | +------+ 3 Rows in Set (0.00 sec) */

For example, the following example, I put the wordpress friendship link, according to Link_rel Group, to take the first two records, you can use the following statement:

The code is as follows	Copy Code
Select Link_rel,link_name from Wp_links as links group by Link_rel limit 2 G /* 1. Row *************************** Link_rel: Link_name:laonb 2. Row *************************** Link_rel:acquaintance Link_name: Heng Tian Xiao Zhang Mainframe 2 rows in Set (0.02 sec) */

Select uses variables and subqueries:

This section is impostors, because this log does not explain it, in the following log, Keoko will write a book about MySQL subquery log to explain. As for the use of variables, generally not used, we will not blind vernacular, lest fraught on the sin.

To control the behavior of a select:

You can add many keywords to the SELECT statement to modify the behavior:

DISTINCT keyword Deletes records that contain duplicate values in the result letter
The sql_calc_found_rows keyword tells MySQL to calculate the total number of rows that match the query (without considering any limit that may be set). The total number of rows can be obtained by calling the Found_rows () function
Sql_cache and Sql_no_cache keywords tell mysql whether the query results require caching
The sql_buffer_result keyword forces MySQL to store the query results in a temporary table. This allows the buffer to eliminate the locking of the table used by the query, and the result is passed on to the customer and can be temporarily used by other processes
The sql_big_result and Sql_small_result keywords can specify the desired size of the result set, thus helping to find the best way to sort and store the returned records (disk-based or in-memory temporary tables)
The sql_high_priority keyword increases the priority of queries that compete with Update,insert and DELETE statements, so you can quickly execute queries on a busy database server

To generate 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

The code is as follows	Copy Code
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.

The code is as follows	Copy Code
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

The code is as follows	Copy Code
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

The code is as follows	Copy Code
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

The code is as follows	Copy Code
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.

The code is as follows	Copy Code
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.

The code is as follows	Copy Code
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

code is as follows	copy code
select Student.student_id,student_name Count (absence.date) as absences from Student,absence where student.student_id=absence.student_id //Association condition Group by student.student_id;