MySQL Select statement

In mysql, if we want to query records, we will use the select statement. Next I will summarize the various operations on the mysql select statement. If you need to learn more, please do not go to the reference.

The most common database operations are "insert, query, delete, modify ". In the previous log, I introduced MySQL insert statements, update statements, delete statements, and truncate statements. Today, we will introduce the only data retrieval statement in MySQL: select statement.

The select statement is the most commonly used Statement by SQL developers and the most powerful weapon. Fortunately, it is not difficult to learn this statement.

Select statement Definition:

Everything must follow the rules. So like the previous tutorial, let's take a look at the syntax structure of the select statement mentioned above in the manual:

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 | expr | position} [ASC | DESC],... [with rollup] [HAVING where_definition] [Order by {col_name | expr | position} [ASC | DESC],...] [LIMIT {[offset,] row_count | row_count OFFSET}] [PROCEDURE procedure_name (argument_list)] [For update | lock in share mode]

Oh, you see it? The syntax is quite long, but one thing you need to know is that what is inside the brackets can be omitted. Sometimes these things cannot be used. Let's get a little bit out of this syntax. In fact, the select statement is still very simple. The reason why the syntax is so complex is that the select statement is very powerful.

Select performs mathematical operations:

We can use the select statement to execute mathematical operations. Of course, if the formula is too complex, forget it. Otherwise, the mathematical scientist will not die? Haha. Let's give you a few examples to illustrate the problem. As for addition and subtraction of less than one hundred, We will calculate it ourselves, so we won't bother MySQL. Haha.

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

Select to retrieve the specified rows and columns:

The select statement supports * wildcard, indicating that all fields are obtained. You can also specify the field name to return the specified column. For example, if the following statement returns all data columns of the previously used twitter plug-in, you can use the * wildcard.

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.bKjia. c0m Protected: 1 Followers_count: 1 1 row in set (0.00 sec) */

If you only want to view all the information in the url and name fields, you can use the following statement to specify the field name of the column to be displayed in the returned result set:

 

The Code is as follows:	Copy code
Select name, url from wp_threadtwitter_users; /* + ---------- + --------------------------- + | Name | url | + ---------- + --------------------------- + | Simaopig | http://www.bKjia. c0m | + ---------- + --------------------------- + 1 row in set (0.00 sec) */

Select adds constraints to the query results:

We can use the where clause to specify the constraints to be queried for the select statement. Only the results of the where clause are displayed in the result set. For example, the following statement, to return the url of a user whose name is simaopig from wp_threadtwitter_users, 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.bKjia. c0m | + --------------------------- + 1 row in set (0.00 sec) */

The where clause can also specify a more complex range. For example, if you want to query the logs I posted, the number of comments is greater than 30, the following clause can be specified for the Document id and title smaller than 50:

The Code is as follows:

Copy code

Select id, post_title, comment_count from wp_posts where comment_count> 30 and comment_count <50 limit 2; /* + ----- + ------------------------------------------------------- + --------------- + | Id | post_title | comment_count | + ----- + ------------------------------------------------------- + --------------- + | 488 | shipping female | 46 | | 501 | emails sent by Mail to Commenter are treated as spam | 36 | + ----- + ------------------------------------------------------- + --------------- + 2 rows in set (0.01 sec) */

Select uses built-in functions:

Long ago, I have introduced the functions of MySQL. MySQL provides a wide range of built-in functions to meet your daily needs. I will give you two examples below, use the MAX, MIN, and COUNT functions to return the articles with the most comments I have posted, the articles with the least comments, and the total number of logs I posted:

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, we can see that I still have the sofa to date. If you are interested, you can flip it over and grab the gray sofa. Oh.

The Code is as follows:	Copy code
Select count (*) from wp_posts; /* + ---------- + | Count (*) | + ---------- + | 1, 340 | + ---------- + 1 row in set (0.00 sec) */

From the results, we can see that I saved the draft. Since the establishment of this site, I have already written 340 logs, although the draft account for a large proportion. Haha. However, it is always typed by one word. Haha.

Select:

Sometimes, the names of the tables we want to query are too long. It is very tiring to type these names, especially for lazy people like me. MySQL provides us with a table alias mechanism, this allows us to create an alias for the table during query. You can think of it as an external number ", so when I call "Xiao Zi", someone knows that I am calling him. Haha.

Alias for a table. You can use the as clause to specify the name of the table to be queried by using the select statement. For example, if I use wp_threadtwitter_users, the alias is users, and in subsequent queries, I use aliases to specify fields, which is very convenient in subqueries.

The Code is as follows:	Copy code
Select * from wp_threadtwitter_users as users where screen_name like '% mg %' and users. followers_count> 200G

Select limits the number of query results:

Sometimes the number of results queried is too large, so that a screen cannot be displayed. What should we do with this result? Don't worry, MySQL provides us with a limit clause, which can be used together with the select statement to limit the number of query results. The limit clause is followed by the number of rows in the desired result set, the usage is as follows:

 

The Code is as follows:	Copy code
Select id from wp_posts where id> 3 and comment_count> 30 limit 2; /* + ----- + | Id | + ----- + | 86 | | 1, 488 | + ----- + */

You can also specify an offset. For example, in the following example, two records are returned starting from 4th rows (Note that the number starts from 0:

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 query results:

MySQL provides order by and group by. Combined with select statements, we can sort and group the returned results. You can add the asc and desc keywords to the order by clause to customize the sorting method. If the order by clause is not specified, MySQL sorts the result set in ascending order by default. Values of corresponding fields are sorted in ascending or descending order. Group by is followed by the field name, which means that the query result is grouped by this field:

Take the post id of the blog log posted by the author, take 3, sort by id in reverse order:

The Code is as follows:	Copy code
Select id from wp_posts order by id desc limit 3; /* + ------ + | Id | + ------ + | 1, 1451 | | 1, 1450 | | 1, 1449 | + ------ + 3 rows in set (0.00 sec) */

For example, in the following example, the wordpress links are grouped by link_rel to retrieve 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 2G /* * *************************** 1. row *************************** Link_rel: Link_name: LAONB * *************************** 2. row *************************** Link_rel: acquaintance Link_name: host of Tiantian Xiaozhang 2 rows in set (0.02 sec) */

Select uses variables and subqueries:

This section is full because this log is not explained. In the following logs, the hacker will write a log about MySQL subquery to explain it. As for the use of variables, it is generally not used, so we will not be blind to the vernacular, so that we will not be guilty of mistakes.

Select action control:

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

• The DISTINCT keyword deletes records containing duplicate values in the result.
• The SQL _CALC_FOUND_ROWS keyword tells MySQL to calculate the total number of rows that match the query (regardless of any LIMIT that may be set. You can call the FOUND_ROWS () function to obtain the total number of rows.
• The SQL _CACHE and SQL _NO_CACHE keywords tell MySQL whether high-speed cache is required for query results
• SQL _BUFFER_RESULT keyword forces MySQL to store the query results to a temporary table. This allows the buffer to eliminate the locking of the table used for the query and the result is sent to the customer. Therefore, the buffer can be used by other processes temporarily.
• The SQL _BIG_RESULT and SQL _SMALL_RESULT keywords can specify the expected size of the result set, so you can find the best way to sort and store the returned records (based on temporary tables in disk or memory)
• The SQL _HIGH_PRIORITY keyword improves the priority of queries that compete with UPDATE, INSERT, and DELETE statements, so that queries can be executed quickly on busy database servers.

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

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

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 can I query it? The simplest method is

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

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

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 with 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 "relationship, this operation is called "Association" or "Combination". We can see that select needs to provide the information (not repeated) in multiple data tables; 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.

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

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

The 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 // join Condition Group by student. student_id;