9. MySQL data query

Don't eat tomatoes 2021-09-15 06:06:55
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://javamana.com/2021/09/20210909121459265d.html

  1. Open source: Suzhou tourism strategy based on pyecharts visual analysis
  2. 從底層開始帶你了解並發編程,五步搞定Java開發環境部署,
  3. 以商品超卖为例讲解Redis分布式锁,一招彻底帮你搞定HashMap源码,
  4. 从青铜到王者的路线,2021Java者未来的出路在哪里?
  5. JavaScript Advanced Programming (3rd Edition) Reading note 6
  6. 從青銅到王者的路線,2021Java者未來的出路在哪裏?
  7. Quelle est la voie à suivre pour les 2021 Java du bronze au roi?
  8. Prenez l'exemple de la surproduction de marchandises pour expliquer redis Distributed Lock, un tour complet pour vous aider à résoudre le code source de hashtap,
  9. 以商品超賣為例講解Redis分布式鎖,一招徹底幫你搞定HashMap源碼,
  10. Win10系统 java环境配置
  11. Non-ASCII character ‘\xe5‘ in file kf1.py on line 4, but no encoding declared; see http://python.or
  12. 手把手教你搭建微信小程序服务器(HTTPS)
  13. JavaScript Review sketch - 1
  14. sqli-labs-less-18 http头user agent+报错注入
  15. Git下载、安装、配置、配合Intellij Idea实现代码版本控制
  16. NHibernate inheritance
  17. Summary of basic knowledge points of JavaScript language (mind map)
  18. GIT télécharge, installe, configure et implémente le contrôle de version de code avec intellij idea
  19. Sqli Labs - less - 18 http header user agent + Error Reporting Injection
  20. Vous apprendrez à construire un serveur d'applet Wechat (https) à la main
  21. Non - ASCII character 'xe5' in file kf1.py on Line 4, but no Encoding declared;Voirhttp://python.or
  22. 作为一名程序员我不忘初心,Java最新实习面试经验总结,
  23. 作为一名Java面试者你应该知道的,2021最新Java常用开源库总结,
  24. 作为一个程序员,你觉得最大的悲哀是什么,2021年大厂Java岗面试必问,
  25. Configuration de l'environnement Java du système win10
  26. 作為一個程序員,你覺得最大的悲哀是什麼,2021年大廠Java崗面試必問,
  27. En tant que programmeur, quelle est la plus grande tristesse que vous ressentez? L'entrevue d'emploi Java de 2021 dans une grande usine vous demandera:
  28. Comme vous devriez le savoir en tant qu'intervieweur Java, 2021 dernier résumé des bibliothèques open source couramment utilisées pour Java,
  29. En tant que programmeur, je n'oublie pas le dernier résumé de mon expérience d'entrevue de stage en Java.
  30. 作為一名Java面試者你應該知道的,2021最新Java常用開源庫總結,
  31. New feature of Java 8. Stream (). Map (general programming method: collect. Groupingby)
  32. Computer graduation project java + SSM hospital registration system
  33. 作為一名程序員我不忘初心,Java最新實習面試經驗總結,
  34. 使用Docker部署Spring-Boot项目,论程序员成长的正确姿势,
  35. Conseils pour améliorer l'efficacité du Code Java mille fois
  36. 全网首发,我在华为做Java外包的真实经历!
  37. 全套Java视频百度云,终于找到一个看得懂的JVM内存模型了,
  38. 入职3个月的Java程序员面临转正,字节跳动 京东 360 网易面试题整理,
  39. Docker tutorial series (I) introduction to docker tutorial spring cloud mybatis distributed microservice Cloud Architecture
  40. 全網首發,我在華為做Java外包的真實經曆!
  41. Lancement de l'ensemble du réseau, je fais l'expérience réelle de l'externalisation Java à Huawei!
  42. Run around with money? Li Weijia fell into the storm of endorsement! In the face of collective hot discussion, personal attitude has become the focus of attention
  43. 全套Java視頻百度雲,終於找到一個看得懂的JVM內存模型了,
  44. Un ensemble complet de vidéos Java Baidu Cloud a finalement trouvé un modèle de mémoire JVM compréhensible.
  45. Déployez le projet Spring Boot avec docker, et parlez de la bonne posture pour que les programmeurs grandissent.
  46. 关于网络优化你必须要知道的重点,GC 堆排 Tomcat 算法题,
  47. 关于电商秒杀系统中防超卖处理方案简述,Java开发热门前沿知识,
  48. Les programmeurs Java qui sont entrés dans l'entreprise pendant trois mois ont dû faire face à une correction d'échelle, et les octets ont sauté dans le traitement des questions d'entrevue de JD 360 Netease.
  49. What is the new syntax of XX ≠ null in Java?
  50. Spring scheduled task cron expression (@ scheduled)
  51. Une brève description du plan de traitement anti - surproduction dans le système d'arrêt du commerce électronique et les connaissances de pointe du développement Java.
  52. Ce que vous devez savoir sur l'optimisation du réseau, c'est que le problème de l'algorithme Tomcat de gerbage GC,
  53. 凭借这份Java面试题集,成体系化的神级Java进阶笔记,
  54. 凭借这份Java面试题集,BAT大厂面试基础题集合,
  55. Docker Knowledge point collation
  56. Redis sur la réalisation élégante des tâches retardées
  57. 憑借這份Java面試題集,BAT大廠面試基礎題集合,
  58. Avec cet ensemble de questions d'entrevue Java, l'ensemble de questions de base d'entrevue de bat,
  59. Avec cet ensemble de questions d'entrevue Java, les notes avancées Java de niveau divin sont systématisées,
  60. Android Architect path 21 Responsive Programming RX Java thread transformation Principles