Shulou(Shulou.com)06/01 Report--

In this issue, the editor will bring you the methods of realizing single-table query in mysql database. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

Database single table query methods: 1, select query operation, filter columns; 2, where statements, filter rows; 3, group by grouping; 4, having filtering conditions, is for the group to do filtering conditions, is put after the implementation of group by; 5, order by sorting, sort the data from the query.

The method of database single table query:

1. Priority of keyword execution (emphasis)

Key points: keyword execution priority 1:from # find the table 2:where # use the conditions specified by where, go to the table to take out a record 3:group by # to group the extracted data, if not specified Then as a group of 4:having # filter the grouped results according to the conditions specified by having # specify select query 6:distinct # remove duplicates 7:order by # sort the query results according to the fields specified by order by # limit the number of results displayed

2. Select query operation (filter columns)

1:#select query operation''pay attention to the method of closing parentheses after all select queries, for example, select user () is the built-in method of Mysql, select itself is the query operation, from just indicates where to look it up,' 'select * from table name; # all the data in the query table * represents all select field names and from table names # query the data select field name of a field in the query table, field name, field name from table name; # query the data of multiple fields in the query table select database (); # query the database select user () of the current operation; # query the select now of the currently logged in user # query return current time = 2:#distinct deduplicate operation''when using select to query certain field data of a table, multiple duplicates may appear in this field, so you can use distinct to deduplicate the queried data. Distinct can also combine the data of multiple fields to remove duplicates' select dictinct field name from table name. # deduplicating select distinct field name, field name from table name; # Joint deduplication of data queried by multiple fields = = 3 from # four operations''when querying data in a field using select, four operations can be performed on field data of numeric type, including operations such as addition, subtraction, multiplication and division,' 'select field name * 12 from table name. # multiply the queried data by 12, return the result, > > Field name if numeric type = 4:#concat string concatenation and concat_ws specified separator concatenation (custom display format)''concat built-in function can concatenate the queried field data by string' 'select concat (' name:', name) from table name # the data of the queried name field is preceded by a string name, which can friendly display the meaning of the queried data. In fact, it means string concatenation of select concat (name,':',salary) as info from table name # concat can also concatenate the data of two fields to produce a new field data to be displayed. As is a field to rename the concat_ws () function is also just a string concatenation, but concat_ws is similar to python join splicing, that is, splicing data of multiple fields with an element''select concat_ws (' _', 'name:', name,' gender:' Sex) as info from table name The function of the 5:#case end statement to process and display each query data is to further process the query statement when querying the data in the table, and show that the case end statement is a bit like the if statement in python, but the case statement of sql needs to indicate the beginning and end, case represents the beginning and end, end represents the end. When represents the if condition, else is the other When there are multiple when, the meaning of elif is similar to that of the second when. It is good to know the name of select (case when name=' Tian Shaogang concat (name,' tease) when name=' Tian Cai 'concat (name,' goddess') else concat (name,' while playing) as now_name from. = the relevant summary of 6:#select; you can check one, multiple, * all calling functions: now (), database (), concat (), concat_ws () can be four operations can be duplicated distinct can use case end conditional judgment statement

3. Where filter row statement

1Rank # range query #

> = > Note that there is one more select age from table name where age in (10Jing 20 in 30); # select age from table names can be extracted from both age=10 and age=20 where age=10 or age=20;2:# fuzzy query # like like is expressed as: the'% a 'query ends with a, the' a% 'query starts with a, and the'% a% 'query contains the select name from table name where name like' field%'of a. > >

4. Group by (grouping)

Group by can group the values of a field, and the field can be divided into as many groups as there are. Another feature of group by is de-duplication. Once you use group by to group data, you cannot operate on a certain piece of data. It is always this group of data 'group_concat () function (used only for final display, not intermediate data operation) that can display all the information of the current group. Put together to display select post,group_concat (name), count (id) from employee group by post having count (id)

The semantics of set global sql_mode='STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY';ONLY_FULL_GROUP_BY is to make sure that the values of all columns in select target list are explicit semantics. To put it simply, in ONLY_FULL_GROUP_BY mode, the values in target list are either from the result of the aggregate function or from the values of expressions in group by list. (as long as you know it)

5. Aggregate function

1:#count count 2:#max for maximum 3:#avg for average 4:#min for minimum 5:#sum summation

6. Having filtering conditions

Having is a filter for a group, which is executed after group by. It means the same as where. The order of execution of where group and having is: where > group by > having, so having is generally used with having''select post,group_concat (name), count (id) from employee group by post having count (id) < 2

7. Order by sorting

Sort the queried data, either ascending or descending (desc), use multiple conditions to sort together, and execute the sort from left to right.''SELECT * FROM employee ORDER BY salary; # SELECT * FROM employee ORDER BY salary ASC; # ascending order SELECT * FROM employee ORDER BY salary DESC; # descending order by default

8limit paging

Can be used with order by, sort first and then take a few pages, and also specify the scope for deletion. Limit mjournal n means to take n pieces of data from msquire 1, which is not as good as limit 0d6, that is, to take six pieces of data from 1, but when limit pagination, it is only suitable for paging small data. When the amount of data is too large, the efficiency will be very slow. Limit also has a syntax: limit n offset m, which also means starting from mpen1. If you take n entries, you only need to know the syntax. The default initial position is 0, and three SELECT * FROM employee ORDER BY salary DESC LIMIT 3 entries are taken out sequentially from the first one. # starting from 0, first query the first article, and then include this article to check 5 SELECT * FROM employee ORDER BY salary DESC LIMIT 0Mague 5; # # starting from 5, first query Article 6, and then include this article to check 5 SELECT * FROM employee ORDER BY salary DESC LIMIT 5L5 The above is the method of realizing single-table query in the mysql database shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.