Summarize and share 15 commonly used query clauses in MySQL (come and collect them)

How to query in MySQL? The following article summarizes and shares 15 commonly used query clauses in MySQL, which are worth collecting. I hope it will be helpful to everyone!

For data analysis, MySQL mostly uses queries, such as data sorting, grouping, deduplication, summary, string matching, etc. If the queried data involves multiple tables, you need to join these tables. This article will talk about the commonly used query clauses in MySQL, a total of 15. [Related recommendations: mysql video tutorial]

*1, *Sort: order by

Sort is by order Implemented, for example, to query data from the StuInfo table and sort it by age. The sql statement is:

select * from stuinfo order by age;

Query results:

As you can see, the query results are arranged in ascending order according to age. If you want to achieve descending order, just You need to add desc at the end. The sql statement is:

select * from stuinfo order by age desc;

Query results:

The query results at this time are arranged in descending order by age.

The above is for sorting numerical data. If it is a string, you can also use order by to sort. For example, to sort by name, the sql statement is:

select * from stuinfo order by stuname;

Query result:

As you can see from the above, string sorting is actually sorting in ascending order according to the first letter. Of course, you can also sort dates, please think about it yourself.

*2, *Group: group by

Grouping is implemented through the group by clause. For example, to group by gender, the sql statement is.

select gender,count(*) from stuinfo group by gender;

Query results:

The above SQL statements are grouped by gender and give the number of people in each group. Grouping by other fields is also similar.

*3, *Deduplication: distinct

Deduplication is a common operation in data processing, which is implemented through the distinct clause, such as query All cities where students are located need to be duplicated because some students are in the same city.

sql statement is:

select distinct city from stuinfo;

Query result:

As you can see, deduplication is to add distinct before the deduplication field That’s it.

*4, *Null value: is null/is not null

Null value is to determine whether a field is empty or not empty For example, to query the records whose city is empty, the sql statement is:

select * from stuinfo where city is null;

Query results:

You can see that the query is empty records In fact, it is the where condition followed by is null.

Conversely, if you query for records where the city is not empty, just follow is not null. The sql statement is:

select * from stuinfo where city is not null;

Query results:

*5, *Summary: counting, maximum value, sum, mean value

Summary is actually counting, finding the maximum/minimum value, Sum, average, etc.

How many records are in the most commonly used statistical table, implemented through count, the sql statement is:

select count(*) from stuinfo;

Query the maximum value of a field in the table/ The minimum value is achieved through max/min. For example, to query the maximum age, the sql statement is:

select max(age) from stuinfo;

Query results:

The next question: Query How to write the name of the oldest student in SQL statement?

This is a conditional query. The condition is that the age must be equal to the maximum age. According to this idea, write the sql statement:

select stuname,age from stuinfo where age=(select max(age) from stuinfo);

Query result:

As you can see from the above, the oldest age is actually queried as a condition, and then the corresponding name and age are queried.

Leave a question: How to query the name of the youngest student?

To sum, just use sum. It must be a sum of numerical data, similar to count. This will not be demonstrated.

The last is the mean value, use avg, for example, to query the average age of all students, the sql statement is:

select avg(age) from stuinfo;

Query results:

*6、 *别名：as

别名，就是as，意为用作，例如，查询出年龄的最大值、最小值和均值，sql语句为：

select max(age),min(age),avg(age) from stuinfo;

查询结果：

这样显示字段名不太好看，我们可以用as起一个别名，sql语句为：

select max(age) as age_max,min(age) as age_min,avg(age) as age_avg from stuinfo;

查询结果：

*7、 *表的连接

当要查询的记录涉及两个或者两个以上的表时，就需要表的连接。

表的连接有三种：内连接、左连接和右连接。

（1）内连接：inner join

内连接相当于两个表的交集，如下图所示。

例如，有两个表：学员信息表和成绩表，有一个共同的列：学号。

现在想查询出每个学员的姓名和成绩，查询结果如下图所示。

以上查询结果中的姓名需要从第一个表中提取，成绩需要从第二个表中提取，这叫表的连接。

因为学号是共同的列，所以根据学号连接，以上的这种连接方式是获取两个表中学号相同的记录，叫作内连接。

具体的sql语句为：

select sname,score fromtb1 inner join tb2 on tb1.sid=tb2.sid;

查询结果：

需要注意的是，连接条件用on。

（2）左连接：left join

左连接是以左表为基准，去匹配右表中的记录，如果右表中没有对应匹配记录则为空。

左连接用集合中的文氏图表示如下。

还是用以上提到的学员信息表和成绩表，左连接的示意图如下。

左连接的sql语句为：

select sname,score fromtb1 left join tb2 on tb1.sid=tb2.sid;

查询结果：

（3）右连接：right join

右连接是以右表为基准，去匹配左表中的记录，如果左表中没有对应匹配记录则为空。

右连接用集合中的文氏图表示如下。

还是用以上提到的学员信息表和成绩表，右连接的示意图如下。

右连接的sql语句为：

select sname,score fromtb1 right join tb2 on tb1.sid=tb2.sid;

查询结果：

*8、 *字符串匹配：like

有时候在字符串查找时，需要查找符合某个匹配模式的字符串。

例如，在表stuinfo中，查找城市中含有字符串‘an’的记录，sql语句为：

select * from stuinfo where city like '%an%';

注意：匹配模式中的%表示匹配任意长度的任意字符串。

*9、 *集合：in

查找属于某个集合的记录用in。

例如，查找城市为北京或者天津的记录，sql语句为：

select * from stuinfo where city in('Beijing','Tianjin');

查询结果：

*10、 *条件语句：having

这里说的条件语句是having，跟where类似，但是一般和统计函数连用。

比如，查找平均年龄小于25岁的城市，sql语句为：

select city from stuinfo group by city having avg(age)<25;

查询结果：

以上sql语句先按照城市分组，再跟条件语句having。

*11、 *区间查找：between and

between and用于查找符合某个区间（包含两个边界的值）的记录。

例如，查找年龄介于20到30岁之间的记录，sql语句为：

select * from stuinfo having age between 20 and 30;

查询结果：

*12、 *联结：union

联结，也叫联合，用于连接两个查询的结果，并且去重。

例如，两个表中都有学号，用sql语句分别从这两个表中查询出学号，然后联结。

select sid from tb1unionselect sid from tb2;

查询结果：

以上sql语句相当于将两个select语句的查询结果用union合并，并且是去重合并。

如果不想去重，用union all即可，sql如下。

select sid from tb1union allselect sid from tb2;

查询结果：

*13、 *日期格式化：date_format

在SQL中，对于日期时间的处理也是很常见的问题。

例如，按照年份、月份或者日期等对数据进行分组汇总，就需要从日期时间中提取年份、月份及日期等。

举个例子，获取每个学员的出生年份，sql语句为：

select date_format(birthdate,'%Y') as birth_year from stuinfo;

查询结果：

获取出生月份也是如此，只是参数用'%m'，sql语句为：

select date_format(birthdate,'%m') as birth_year from stuinfo;

查询结果：

获取到年份、月份后，就可以按照年份、月份进行分组查询统计了。

*14、 *条件分支：case语句

条件分支是数据统计中的一个很重要的应用，在MySQL中用case语句表达条件分支，case语句有以下两种用法。

用法一：

例如，根据学员所处的城市，将记录标记为一线城市及其他，sql语句为：

select distinct city,
  case city
    when 'Beijing' then '一线城市'
    when 'Shanghai' then '一线城市'
    else '其他' end as city_level
from stuinfo;

查询结果：

从上面看到，case后面跟的是要判断的字段，when后跟的是不同的情况，then则是不同情况对应的类别，最后的else表示其他情况。

整个case语句相当于给原来的表增加一个字段：city_level，然后我们可以根据这个字段进行分组查询。

用法二：

例如，根据学员的年龄进行分层，青年、中年及其他，sql语句为：

select stuname,age,
  case when age<30 then &#39;青年&#39;
      when age>=30 and age<35 then &#39;中年&#39;
      else &#39;其他&#39; end as age_level
from stuinfo;

查询结果：

从上面可以看到，case后面跟的是条件when，即年龄满足什么条件时，将其划分到对应的类别，最后的else表示其他情况。

整个case语句相当于给原来的表增加一个字段：age_level，然后我们可以根据这个字段进行分组查询。

*15、 *变量

最后，谈谈MySQL中的变量，当然，MySQL中的变量有好几种类型，这里只说用户变量，像@var这种。

例如，我们希望根据学员的年龄计算出每个人到35岁还有几年，这里可以定义一个变量并赋值为35，然后利用这个变量减去年龄就得到结果。

首先，定义变量var，sql语句为：

set @var:=35;

接着，使用这个变量，sql语句为：

select @var-age as age_gap from stuinfo;

查询结果：

当然，关于变量还有很多更复杂的用法，多用于计算连续登陆天数这种问题或者存储过程中。

更多编程相关知识，请访问：编程视频！！

The above is the detailed content of Summarize and share 15 commonly used query clauses in MySQL (come and collect them). For more information, please follow other related articles on the PHP Chinese website!