mysql的必知必会_Mysql必知必会-CSDN博客

本文链接：https://blog.csdn.net/weixin_32537985/article/details/113230285

本文详细介绍了SQL的基本操作，包括数据检索、排序、过滤等，并演示了如何使用子查询、联结表来获取复杂数据，以及如何进行数据的增删改操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.数据库相关概念

数据库 :保存有组织的数据的容器(一个文件或一组文件),即一个以某种有组织的方式存储的数据集合.

数据库管理系统:数据库软件(DBMS).

表:结构化文件,可以用来存储某种特定类型的数据.

列:表是由列组成,列是表中的一个字段,所有表都是由一个或多个列组成的.列中存储着表中某部分的信息.

数据类型:所容许的数据的类型.

行:表中的一个记录.水平行,垂直列.

模式:可以用来描述数据库中特定的表以及整个数据库,关于数据库和表的布局及特性的信息.

SQL:结构化查询语言,一种专门用来与数据库通信的语言.

MYSQL:一种DBMS,是一种数据库软件.

2. 排序检索数据

排序检索数据：select语句的order by子句

//order by位置，位于from、where之后，limit之前select * from proucts order by prod_price DESC limit 1

3. 过滤数据

过滤数据：使用where子句-操作符：检查单个值、不匹配检查、范围值检查、空值检查

条件操作符：

#检查单个值、不匹配检查、范围值检查、空值检查select * from proucts where pro_name='fuses';select * from proucts where pro_price < 10;select * from proucts where vend_id <> 1003;select * from proucts where pro_price between 5 and 10; #范围值检查select * from proucts where pro_price is null; #空值检查，返回没有价格的所有产品

4. 数据过滤

数据过滤：组合where子句(and、or、in、not)、计算次数

#数据过滤：组合where子句(and、or、in、not)、计算次序select * from proucts where vend_id =1002 and pro_price =10;select * from proucts where vend_id =1002 or vend_id =1003;select * from proucts where (vend_id =1002 or vend_id =1003) and pro_price >= 10;#计算次序，and优先级高，需要用分组(括号)进行计算次序select * from proucts where pro_price in (5,10);select * from proucts where pro_price not in (5,10);

5. 用通配符进行过滤

通配符:用来匹配值的一部分的特殊字符。比如百分号(%)、下划线(_)

搜索模式：由字面量、通配符或两者组合构成的搜索条件。

#%表示任何字符出现任意次数[0、1、无数]，区分大小写select * from proucts where pro_price like 'jet%';

#_表示匹配一个字符select * from proucts where pro_price like '_ ton anvil';

6. 用正则表达式进行搜索

正则表达式作用：将一个模式(正则表达式)与一个文本串进行比较。

#REGEXP匹配列id包含文本'测试'的所有行select * from message_station_info WHERE type REGEXP '测试';

#LIKE匹配整个列id,除非用通配符%和_select * from message_station_info WHERE type LIKE '测试';

#.匹配一个任意字符,关键字BINARY，区分大小写select * from message_station_info WHERE type REGEXP BINARY '.测试';

#进行OR匹配：|，将匹配测试消息订阅关系或测试123包含的行select * from message_station_info WHERE type REGEXP '测试消息订阅关系|测试123';

#[]匹配特定的字符，[123] ton 为[1|2|3]ton的缩写select * from message_station_info WHERE type REGEXP '[123] ton';select * from message_station_info WHERE type REGEXP '[1|2|3] ton';

#匹配范围[0-9]、[a-z]

select * from message_station_info WHERE type REGEXP '[0-2] ton';

#匹配特殊字符\\_;\\.;\\|;\\[];\\()select * from message_station_info WHERE type REGEXP '\\.';select * from message_station_info WHERE type REGEXP '\\.[0-9]{3} ton?' ;

执行结果如下：

7. 创建计算字段

从数据库中检索出转换、计算或格式化过的数据。

#将字段type、title拼接成一个字段进行显示,去掉字段两边的空格select CONCAT(TRIM(type),'(',TRIM(title),')') AS type_title from message_station_info WHERE title='test';

#+、-、*、/等MYSQL算术操作符select on_del*status as del_status from message_station_info WHERE title='test';

8. 数据处理函数

文本处理函数：用于处理文本串(如删除或填充值，转换值为大写或小写)的文本函数。

日期和时间处理函数：用于处理日期和时间值并从这些值中提取特定成本(例如，返回两个日期之差，检查日期有效性等)的日期和时间函数。

数值处理函数：用于在数值数据上进行算术操作的数值函数。

#文本处理函数,upper()函数，列type字段全部转换为大写select UPPER(type) frommessage_station_info;

#文本处理函数，left()函数，列type长度左边开始3个字符select LEFT(type,3) frommessage_station_info;

#日期和时间处理函数，检索创建时间为2020-5-13到2020-5-14的数据select * from message_station_info where DATE(create_time)='2020-5-14';select * from message_station_info where DATE(create_time) BETWEEN '2020-5-13' and '2020-5-14';select * from message_station_info where Year(create_time)='2020' AND MONTH(create_time)='6';

9.汇总数据[聚集函数]

聚集函数：运行在行组上，计算和返回单个值的函数。

#AVG()函数，求平均值,用来返回所有列的平均值，也可以用来返回特定列或行的平均值.AVG()函数忽略列值为NULL的行。select AVG(define_id) as avg_id frommessage_station_info;

#COUNT()函数，计数，可利用COUNT()确定表中行的数目或符合特定条件的行的数目。

#COUNT(*)不管是空值(NULL)还是非空值;COUNT(id)对特定列中具有值的行进行计数，忽略NULL值。select COUNT(*) as num_id frommessage_station_info;select COUNT(id) as num_id frommessage_station_info;

#MAX()函数，返回指定列中的最大值。忽略列值为NULL的行。select MAX(id) as max_id frommessage_station_info;

#MIN()函数，返回指定列中的最小值。忽略列值为NULL的行。select MIN(id) as min_id frommessage_station_info;

#SUM()函数，用来返回指定列值的和(总计)。select SUM(id*num) as sum_id frommessage_station_info;

#DISTINCT()函数，用来返回指定列值的和(总计)。select AVG(DISTINCT prod_price) as avg_price from products where id=1003;

10. 分组数据

#GROUP BY子句和HAVING 子句,WITHROLLUP可以得到每个分组以及每个分组汇总级别的值

#WHERE过滤指定的是行；HAVING过滤指定的是分组select type,id,title,COUNT(*) as num from message_station_info group by type HAVING COUNT(*) >='11';

11 使用子查询、组合查询、全文本搜索

查询任何SQL语句都是查询，SQL还允许创建子查询(subquery),即嵌套在其他查询中的查询，子查询总是从内向外处理。

列必须匹配，在where子句中使用子查询，应该保证select语句具有where子句中相同数目的列。

#MYSQL执行3条select语句，从最里边的子查询返回订单号列表，用于其外面的子查询的where子句，外面的子查询返回客户ID列表，用于最外层查询的where子句，最外层查询确实返回所需的数据

#利用子查询进行过滤SELECT cust_name,cust_contact FROM customers WHERE cust_id IN(SELECT cust_id FROM orders where order_num in(SELECT order_num FROM orderitems where prod_id='TNT2'));

#作为计算字段使用子查询SELECTcust_name,cust_state,

(SELECT COUNT(*) FROM orders where orders.cust_id = customers.cust_id) ASordersFROMcustomersORDER BY cust_name;

组合查询：Mysql也允许执行多个查询(多条SELECT语句)，并将结果作为单个查询结果集返回，这些组合查询通过称为并(union)或复合查询(compound query)。使用组合查询的情况：

在单个查询中从不同的表返回类似结构的数据；

对单个表执行多个查询，按单个查询返回数据；

#UNION组合查询[UNION默认取消重复的行，全匹配需要UNION ALL]

selectid,type,on_delfromapp_infoWHERE on_del='1';UNION

selectid,type,on_delfromapp_infoWHERE id < '20';

#UNION全匹配需要加个UNIONALL

selectid,type,on_delfromapp_infoWHERE on_del='1';UNION ALL

selectid,type,on_delfromapp_infoWHERE id < '20';

#UNION与where可以互相调换selectid,type,on_delfromapp_infoWHERE on_del='1' or id < '20';

#UNION排序selectid,type,on_delfromapp_infoWHERE on_del='1';UNION

selectid,type,on_delfromapp_infoWHERE id < '20';ORDER BY id;

UNION规则：

必须由两条及两条以上的SELECT语句组成，语句之间用关键字UNION分隔。

UNION中每个查询必须包含相同的列、表达式、聚集函数

列数据类型必须兼容：类型不必完全相同，但是DBMS可以隐含地转换的类型。

全文本搜索：并非所有的引擎都支持全文本搜索，MyISAM支持，InnoDB不支持，我们平时主要是用的InnoDB.

全文本搜索前提条件：必须索引(FULLTEXT)被搜索的列，索引后可与Match()和Against()一起使用。

12 联结表[join]、创建高级联结

联结：数据检索查询的执行中联结(join)表。联结是一种机制，用来在一条SELECT语句中关联表。

外键：为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

可伸缩性：能够适应不断增加的工作量而不失败。

内部联结[等值联结]：基于两个表之间的相等测试。

#内部联结[等值联结]：基于两个表之间的相等测试。select *

from app_station_cls INNER JOINapp_infoon app_station_cls.from_id =app_info.id;

#联结多个表：select b.*

from sys_user as a,app_info as b,sys_user_app_relation ascwhere c.app_id = b.id and c.user_id = a.id and a.account='upm_test';

表别名好处：缩短SQL语句；允许在单条select语句中多次使用相同的表。表别名的用处：可用与where子句、select列表、order by子句、语句的其他部分。

#自联结selectA1.id,A1.typefrom app_info as A1,app_info asA2WHERE A1.id=A2.id and A2.type='1'#自然联结:排除多次出现，使每个列只返回一次。内部联结：返回所有数据。select A.*,B.idfrom app_station_cls AS A,app_info ASBWHERE A.from_id =B.id;

#联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结：左联结、右联结。[LEFT|RIGHT OUTER JOIN]

13 插入数据、更新和删除数据、创建和操纵表