search
HomeDatabaseMysql Tutorial详解MySQL中的分组查询与连接查询语句_MySQL

详解MySQL中的分组查询与连接查询语句_MySQL

分组查询 group by
group by 属性名 [having 条件表达式][ with rollup]
“属性名 ”指按照该字段值进行分组;“having 条件表达式 ”用来限制分组后的显示,满足条件的结果将被显示;with rollup 将会在所有记录的最后加上一条记录,该记录是上面所有记录的总和。

1)单独使用
group by 单独使用,查询结果只显示一个分组的一条记录。
实例:

select * from employee group by sex;

将只显示男女两条记录。

2)与group_concat()函数一起使用
每个分组中指定字段值都显示出来
实例:

select sex,group_concat(name) from employee group by sex;

显示结果中“女”会显示所有sex为“女”的名字name

sex | group_concat(name)
女 | 小红,小兰
男 | 张三,王五,王六

3)与集合函数一起使用
实例:

select sex,count(sex) from employee group by sex;

结果:

sex | count(num)
女 | 1
男 | 3

count()为计算个数的方法。

4)与having一起使用
“having条件表达式”,可以限制输出结果。只有满足条件表达式的结果才显示。
实例:

select sex,count(sex) from employee group by sex having count(sex) >= 3;

结果:

sex | count(sex)
男 | 3

“having条件表达式”作用于分组后的记录。

5)按多字段进行分组

select * from employee group by d_id,sex;

查询结果先按d_id分组,再按sex进行分组

6) 与with rollup一起使用
使用with rollup将会在所有记录的最后加上一条记录,这条记录是上面所有记录的总和
实例:

select sex,count(sex) from employee group by sex with rollup;

结果:

sex | count(sex)
女 | 1
男 | 5
null | 6

如果是字符串的话,比如姓名就会生成“张三,李四,王五”这种类型的结果,即name总和。

连接查询
将两个及两个以上的表连接起来选取所需数据。

1)内连接查询:
当两个表中具有相同意义的字段值相等时,就查询出该条记录。
实例:

代码如下:

select num,name,employee.d_id,age,d_name from employee,department where employee.d_id = department.d_id

因字段名相同,所以取d_id字段值时最好指定哪张表的字段。

2)外连接查询
select 属性名列表 from 表名1 left|right join 表名2 on 表名1.属性名1=表名2.属性名2;
左连接查询:
进行左连接查询时,可以查出表名1中所指的表中所有记录。而表名2所指表中,只能查询出匹配的记录。
实例:

代码如下:

select num,name,employee.d_id,age,d_name from employee left join department on 
employee.d_id = department.d_id;

右连接查询:
与左连接相反,可以查询出表名2中的的所有记录,而表名1中所指的表中,只查询出匹配的记录。

PS:使用集合函数查询
集合函数包括count(),sum(),avg(),max()和min()。
1)count()函数
统计记录条数
实例:

select count(*) from employee;

与group by一起使用

select d_id,count(*) from employee group by d_id;

上述语句会先分组后统计

2) sum()函数
sum()函数是求和函数
实例:

select num,sum(score) from grade where num= 1001;

select num,sum(score) from grade group by num;

sum()只能计算数值类型字段。
3)avg()函数
avg()函数是求平均值函数。
实例:

select avg(age) from employee;

select course,avg(score) from group by course;

4)max(),min()函数
求最大值和最小值。
实例:

select max(age) from employee;
select num,course,max(score) from grade group by course;

对于字符串的最大值问题,max()函数是使用字符对应的ascii码进行计算的。

以上就是详解MySQL中的分组查询与连接查询语句_MySQL的内容,更多相关内容请关注PHP中文网(www.php.cn)!


Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How does MySQL's licensing compare to other database systems?How does MySQL's licensing compare to other database systems?Apr 25, 2025 am 12:26 AM

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

When would you choose InnoDB over MyISAM, and vice versa?When would you choose InnoDB over MyISAM, and vice versa?Apr 25, 2025 am 12:22 AM

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

Explain the purpose of foreign keys in MySQL.Explain the purpose of foreign keys in MySQL.Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

What are the different types of indexes in MySQL?What are the different types of indexes in MySQL?Apr 25, 2025 am 12:12 AM

There are four main index types in MySQL: B-Tree index, hash index, full-text index and spatial index. 1.B-Tree index is suitable for range query, sorting and grouping, and is suitable for creation on the name column of the employees table. 2. Hash index is suitable for equivalent queries and is suitable for creation on the id column of the hash_table table of the MEMORY storage engine. 3. Full text index is used for text search, suitable for creation on the content column of the articles table. 4. Spatial index is used for geospatial query, suitable for creation on geom columns of locations table.

How do you create an index in MySQL?How do you create an index in MySQL?Apr 25, 2025 am 12:06 AM

TocreateanindexinMySQL,usetheCREATEINDEXstatement.1)Forasinglecolumn,use"CREATEINDEXidx_lastnameONemployees(lastname);"2)Foracompositeindex,use"CREATEINDEXidx_nameONemployees(lastname,firstname);"3)Forauniqueindex,use"CREATEU

How does MySQL differ from SQLite?How does MySQL differ from SQLite?Apr 24, 2025 am 12:12 AM

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

What are indexes in MySQL, and how do they improve performance?What are indexes in MySQL, and how do they improve performance?Apr 24, 2025 am 12:09 AM

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Explain how to use transactions in MySQL to ensure data consistency.Explain how to use transactions in MySQL to ensure data consistency.Apr 24, 2025 am 12:09 AM

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.