编程知识 cdmana.com

9. MySQL data query

9.MySQL Data query SQL

Grammar format :

select  Field list |* from  Table name

[where  search criteria ]

[group by  Grouping field  [having  Grouping conditions ]]

[order by  Sort field   Sort rule ]

[limit  Paging parameters ]

Basic query

#  Query all columns in the table   All the data

select * from users;

#  Specify the field list to query

select id,name,phone from users;

Where  Conditions of the query

Can be in where Clause to specify any condition

have access to  and  perhaps  or  Specify one or more conditions

where Conditions can also be applied to update and delete After statement

where Clause is similar to if Conditions , according to mysql The field values in the table are used to filter the data

Example :

--  Inquire about users In the table  age > 22 The data of

select * from users where age > 22;

--  Inquire about  users  In the table  name= A certain conditional value   The data of

select * from users where name = ' Wang Wu ';

--  Inquire about  users  In the table   Age 22 To 25 Data between

select * from users where age >= 22 and age <= 25;

select * from users where age between 22 and 25;

--  Inquire about  users  In the table   Age not in 22 To 25 Data between

select * from users where age < 22 or age > 25;

select * from users where age not between 22 and 25;

--  Inquire about  users  In the table   Age 22 To 25 Between the girls

select * from users where age >= 22 and age <= 25 and sex = ' Woman ';

and and or  Pay attention to

Assume that demand   Inquire about  users  In the table   Age is 22 perhaps 25  Of girls

select * from users where age=22 or age = 25 and sex = ' Woman ';

Think about whether the above statement can return qualified data ?

The actual query results do not meet the requirements ?

select * from users where age=22 or age = 25 and sex = ' Woman ';

+------+--------+------+-------+-------+------+------+

| id | name | age | phone | email | sex | mm |

+------+--------+------+-------+-------+------+------+

| 1 |  Chapter three  | 22 | | NULL |  male  | 0 |

| 1002 | cc | 25 | 123 | NULL |  Woman  | NULL |

+------+--------+------+-------+-------+------+------+

2 rows in set (0.00 sec)

--  The above query results do not match   Requirements for query conditions .

--  The problem is  sql  In the order of calculation ,sql Will give priority to and Conditions , So the top sql The sentence becomes

--  The query becomes for age 22 Regardless of gender , Or age is  25 The girl of

--  How to transform sql Meet our query criteria ?

--  Use parentheses to associate the same conditions

select * from users where (age=22 or age = 25) and sex = ' Woman ';

+------+------+------+-------+-------+------+------+

| id | name | age | phone | email | sex | mm |

+------+------+------+-------+-------+------+------+

| 1002 | cc | 25 | 123 | NULL |  Woman  | NULL |

+------+------+------+-------+-------+------+------+

1 row in set (0.00 sec)

Like  Clause

We can do it in where Use in condition =,<,>  And so on , But how to filter when you want to query whether a field contains ?

have access to like Statement to perform a fuzzy search of a field ,

for example :  Inquire about  name The field contains five data

-- like  sentence  like A certain value   and .where name = ' Wang Wu '  It's the same

select * from users where name like ' Wang Wu ';

+----+--------+------+-------+-----------+------+------+

| id | name | age | phone | email | sex | mm |

+----+--------+------+-------+-----------+------+------+

| 5 |  Wang Wu  | 24 | 10011 | [email protected] |  male  | 0 |

+----+--------+------+-------+-----------+------+------+

1 row in set (0.00 sec)

--  Use  %  Fuzzy search .% Represents any arbitrary character

--  Inquire about name Field that contains five of

select * from users where name like '% 5、 ... and %';

--  Inquire about name The last character in the field   by   Five

select * from users where name like '% 5、 ... and ';

--  Inquire about name The first character in the field   by   king   Of

select * from users where name like ' king %';

--  Use  _  Single underline . Represents an arbitrary character , Use and % similar

--  In the query table  name  The field is two character data

select * from users where name like '__';

--  Inquire about  name  The field ends with five , Two character data for

select * from users where name like '_ 5、 ... and ';

Be careful :where In Clause like In the use of % perhaps _ When doing a fuzzy search , The efficiency is not high , Pay attention to :

Try not to use % perhaps _

If needed , Also try not to put wildcards at the beginning

Mysql The statistical function in ( Aggregate functions )

max(),min(),count(),sum(),avg()

#  Calculation  users  In the table   Maximum age , Minimum age , Age and average age

select max(age),min(age),sum(age),avg(age) from users;

+----------+----------+----------+----------+

| max(age) | min(age) | sum(age) | avg(age) |

+----------+----------+----------+----------+

| 28 | 20 | 202 | 22.4444 |

+----------+----------+----------+----------+

--  The columns in the above data are the function names used in the query , Not easy to read and later call , You can use aliases   beautify

select max(age) as max_age,

min(age) min_age,sum(age) as sum_age,

avg(age) as avg_age

from users;

+---------+---------+---------+---------+

| max_age | min_age | sum_age | avg_age |

+---------+---------+---------+---------+

| 28 | 20 | 202 | 22.4444 |

+---------+---------+---------+---------+

--  Statistics  users  The amount of data in the table

select count(*) from users;

+----------+

| count(*) |

+----------+

| 9 |

+----------+

select count(id) from users;

+-----------+

| count(id) |

+-----------+

| 9 |

+-----------+

--  The two statistics above , Used separately  count(*)  and  count(id), The results are the same at the moment , What's the difference? ?

-- count(*)  Is in accordance with the  users All columns in the table are used for data statistics , As long as there's data on one of the columns , You can calculate

-- count(id)  According to the designation  id  Field for Statistics , You can also use other fields for Statistics ,

--  But notice , If... Appears on the specified column NULL value , So for NULL This data will not be counted

--  Suppose there is a table like this that needs Statistics

+------+-----------+------+--------+-----------+------+------+

| id | name | age | phone | email | sex | mm |

+------+-----------+------+--------+-----------+------+------+

| 1 |  Chapter three  | 22 | | NULL |  male  | 0 |

| 2 |  Li Si  | 20 | | NULL |  Woman  | 0 |

| 5 |  Wang Wu  | 24 | 10011 | [email protected] |  male  | 0 |

| 1000 | aa | 20 | 123 | NULL |  Woman  | NULL |

| 1001 | bb | 20 | 123456 | NULL |  Woman  | NULL |

| 1002 | cc | 25 | 123 | NULL |  Woman  | NULL |

| 1003 | dd | 20 | 456 | NULL |  Woman  | NULL |

| 1004 | ff | 28 | 789 | NULL |  male  | NULL |

| 1005 |  Wang Wuliu  | 23 | 890 | NULL | NULL | NULL |

+------+-----------+------+--------+-----------+------+------+

9 rows in set (0.00 sec)

--  If according to sex This column makes statistics , The result is 8 A rather than 9 individual , because sex There is... In this column NULL The value is

mysql> select count(sex) from users;

+------------+

| count(sex) |

+------------+

| 8 |

+------------+

In addition to the above simple use of aggregate functions , In general, it is to cooperate with grouping for data statistics and calculation

Group BY  grouping

group by  Statement to group the result set according to one or more columns

In general , Statistics or calculations with data , Use with aggregate functions

--  Statistics  users  In the table   The number of boys and girls

--  Obviously according to the above requirements , You can write two sentences for statistics

select count(*) from users where sex = ' Woman ';

select count(*) from users where sex = ' male ';

--  You can use groups for Statistics , More convenient

select sex,count(*) from users group by sex;

+------+----------+

| sex | count(*) |

+------+----------+

|  male  | 4 |

|  Woman  | 5 |

+------+----------+

--  Statistics 1 Class and 2 The number of people in the class

select classid,count(*) from users group by classid;

+---------+----------+

| classid | count(*) |

+---------+----------+

| 1 | 5 |

| 2 | 4 |

+---------+----------+

--  Count the number of boys and girls in each class

select classid,sex,count(*) as num from users group by classid,sex;

+---------+------+-----+

| classid | sex | num |

+---------+------+-----+

| 1 |  male  | 2 |

| 1 |  Woman  | 3 |

| 2 |  male  | 2 |

| 2 |  Woman  | 2 |

+---------+------+-----+

#  Be careful , In the use of .group by In groups , In addition to aggregate functions , Others in select The following fields and columns need to appear in grouop by  Back

Having  Clause

having After group aggregation calculation , Filter the results again , Be similar to where,

where The filtered data is row data ,having Filtering is grouping data

--  Count the class

select classid,count(*) from users group by classid;

--  Count the class , And the number of people should reach 5 People and above

select classid,count(*) as num from users group by classid having num >=5;

Order by  Sort

We are mysql Use in select The results of the query are sorted according to the structure of the data in the underlying file ,

First, don't rely on the default sort , In addition, you need to use... When you need to sort orderby Sort the returned results

Asc  Ascending , Default

desc Descending

--  Sort the results by age , From big to small

select * from users order by age desc;

--  Sort from small to large  asc  The default is . Don't write

select * from users order by age;

--  You can also sort by multiple fields

select * from users order by age,id; #  First according to age Sort ,age In the same case , according to id Sort

select * from users order by age,id desc;

Limit  Data paging

limit n  extract n Data ,

limit m,n  skip m Jump data , extract n Data

--  Inquire about users Table data , as long as 3 strip

select * from users limit 3;

--  Skip the former 4 Data , Retake 3 Data

select * from users limit 4,3;

-- limit Generally used in data paging

--  For example, each page shows 10 Data , Page three  limit How to write ?  reflection

first page  limit 0,10

The second page  limit 10,10

The third page  limit 20,10

Page four  limit 30,10

--  extract  user In the table   Three oldest user data   How to query ?

select * from users order by age desc limit 3;

版权声明
本文为[Don't eat tomatoes]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/09/20210909121459265d.html

Scroll to Top