Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to implement the query statement in sql database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

The complete syntax of the sql database query statement is "Select [select options] field list [field aliases] / * from data source [where clause] [group by clause] [having clause] [order by clause] [limit clause];".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

The database is mysql, and the database table name used is my_student.

The complete data information for the table is:

The full syntax is:

Select [select options] Field list [Field aliases] / * from data Source [where clause] [group by clause] [having clause] [order by clause] [limit clause]

① [select options]:

Select options include: ALL (all, default), distinct (de-duplicated). Where distinct is for the entire record of the query result.

Select DISTINCT (sex) from my_student

The results of selectDISTINCT (sex), name from my_student; and selectDISTINCT sex,name from my_student; are the same.

② [where sentence]: where is the only condition that starts to judge when the data is taken from the disk. Take a record from the disk and start the where judgment. If the result is valid, the result is saved to memory, otherwise it is discarded.

Select * from my_student where name = ' 1'

③ [group by clause]: grouping clause. The group by clause is mainly used for grouping to perform statistical operations, not for display (only the first record of the grouped record is displayed when displayed). When grouping, the functions count (), max (), min (), avg () and sum () are usually used in combination.

A, single sub-segment grouping:

Selectc_id,count (*), max (height), min (height), avg (height), sum (age) from my_studentgroup by c_id

The sql statement means that the my_ student table is grouped in c_id, and then displays the c_id name of each group, the total number of each group, the highest, lowest, average height and the sum of ages of each group.

B, multi-field grouping

Select min (height), min (height), height (height), sum (age) from my_student group by sexier count (*), from my_student group by (sex)

The meaning of the representation is that the entire table is grouped by c_id, then on the basis of this grouping, and then each group is grouped according to sex.

C, multi-field grouping (plus displaying all the data of a field in each group)

Selectc_id,sex,count (*), max (height), min (height), avg (height), sum (age), GROUP_CONCAT (name) from my_student group by clockwise

④ [having clause]: having acts like where, and having can do almost everything where can do, while where can't do many of the things having can do, mainly because

Where can only manipulate data when it is extracted from disk, while the results of group by grouping of data in memory can only be processed through having.

Selectc_id,count (*), max (height), min (height), avg (height), sum (age) from my_studentgroup by c_id having COUNT (*) > 3

⑤ [order by clause]: sort the data in ascending or descending order based on a field. (when sorting multiple fields, Pan Xu is first sorted according to a certain field, and then sorted according to a certain field inside the sorted field.)

A. sorting of individual fields:

Select * from my_student order by c_id

B, multi-field sorting

Select * from my_student order by clockwork sex

⑥ [limit clause]: limit the number of results. Number of Limit offset record entries

A, select * frommy_student limit 2

B, select * frommy_student limit 0pl 3

This is the end of this article on "how to implement sql database query statements". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.