MySQL (Thu)

Group

• Group By field, indicating that the same data in this field is placed in a group
• After grouping, only the same data columns can be queried, and data columns that have differences cannot appear in the result set
• You can count the grouped data and do the aggregation operations.
• Grammar:

select 列1,列2,聚合... from 表名 group by 列1,列2,列3...

• Total number of male and female health enquiries

select gender as 性别,count(*)from studentsgroup by gender;

• Check the number of cities

select hometown as 家乡,count(*)from studentsgroup by hometown;

Data filtering after grouping

• Grammar:

select 列1,列2,聚合... from 表名group by 列1,列2,列3...having 列1,...聚合...

• The conditional operator behind the having the same as where
• Total number of male enquiries

方案一select count(*)from studentswhere gender=1;-----------------------------------方案二：select gender as 性别,count(*)from studentsgroup by genderhaving gender=1;

Compare where and have

• Where is the data filter for the table specified after from, which is the filter for the original data
• Having is filtering the results of GROUP by

Sort

• To make it easier to view data, you can sort the data
• Grammar:

select * from 表名order by 列1 asc|desc,列2 asc|desc,...

• The row data is sorted by column 1, sorted by column 2 if the values of some row 1 are the same, and so on
• Default by column values from small to large
• ASC arranges from small to large, ascending
• Desc from large to small sort, i.e. descending
• Query does not delete boys student information, by number descending

select * from studentswhere gender=1 and isdelete=0order by id desc;

• Query does not delete account information, ascending by name

select * from subjectwhere isdelete=0order by stitle;

Get Department Branch

• Viewing data on one page is a very troublesome thing when the amount of data is too large
• Grammar

select * from 表名limit start,count

• From start, get count bar data
• Start index starting from 0

Example: Paging

• Known: Show m data per page, currently showing page n
• Total Pages: This logic is later implemented in Python
  • Query total number of bars P1
  • Use P1 divided by M to get P2
  • P2 is the total page if divisible
  • P2+1 is the total number of pages if not divisible
• To find data on page n

select * from studentswhere isdelete=0limit (n-1)*m,m

Summarize

• The complete SELECT statement

select distinct *from 表名where ....group by ... having ...order by ...limit star,count

• The order of execution is:
  • From table name
  • where ....
  • GROUP BY ...
  • SELECT DISTINCT *
  • Having ...
  • ORDER BY ...
  • Limit Star,count
• In practice, it's just a combination of some parts of the statement, not all

MySQL (iv)