MySQL基础知识总结（三）：单表查询

谁践踏了优雅 2023-07-10 15:53 102阅读 0赞

# 单表查询 #

## 插入数据 ##

insert into table_name(field1,field2,....fieldn);

插入记录的一部分

insert into 表名（字段1，字段3，字段5）
    values（对应字段1，对应字段3，对应字段5）；

插入多条记录  
格式：

insert into table_name(field1,field2,...fieldn)
    values
    	(value1,value2,...valuen),(value1,value2,...valuen),(value1,value2,...valuen),....

## 更新数据 ##

更新特定数据

update table_name 
    set filed1=value1, filed2=value2 
    where condition

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70]  
更新所有数

update table_name set filed=value;

## 删除数据 ##

删除指定数据

delete from table_name where condition

删除所有数据

delete from tabe_name;

\*\*

## 查询数据 ##

\*\*  
**简单记录查询**

select field1,field2,...fieldn from table_name;
    select* from table_name;

**包含数学运算的查询**  
\+、-、*、/、%  
例如：select name,sal*12 as “年薪” from employee;

**格式化显示**  
concat()连接字符  
select concat(name,“的年薪为：”,sal\*12) “薪资” from employee;

**条件记录查询**  
**比较运算符：**>, <, =, !=(<>), >=, <=  
**逻辑运算符**：and（&&）、or（||）、Xor、NOT（！）  
**between and**  
**is null / is not null**  
**带in关键字的条件查询**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70 1]

**带like的关键字查询**  
like语句支持的通配符：  
\*\*“\_”\*\*表示匹配单个字符  
\*\*“%”\*\*表示可以匹配任意长度的字符串  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70 2]

**排序记录查询**  
**order by：**  
asc：升序（默认）  
desc：降序

**限制记录查询**  
**limit**  
语句格式：

select field1，field2，...fieldn 
    from table_name 
    where condition 
    limit offset_start(起始偏移量),row_count(行数)；

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70 3]

**统计函数**  
count()  
avgr()  
sum()  
max()  
min()  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70 4]

**分组记录查询**  
**group by**  
简单分组  
语句：

select function（） from table_name where condition group by field；

group\_concat：显示每个分组中的字段，显示每个分组中的指定字段  
语句：

select group_concat（field） from Table_name where condition group by field；

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70 5]  
**多个字段的分组查询**  
having组织分组后的条件  
1、where语句只能放在group by 之前！  
2、where是在列出现之前查询，having是在列出现之后再筛选

select column_name,aggregate_function(column_name) 
    from table_name
    where column_name operator value
    group by column_name
    having aggregate_function(column_name) operator value;

having和where的区别：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70 6]

**结果去重**  
避免重复查询：distinct 放在字段名前

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70 7]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200303183903580.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200303184310415.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200303184351544.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200303184518658.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200303185026267.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200303184643613.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/202003031848097.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200303184931807.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDM5MDQ2Mg==,size_16,color_FFFFFF,t_70