首页 > 数据库 >MySQL常用语句总结

MySQL常用语句总结

时间：2024-06-01 18:00:51浏览次数：28  
标签：语句 总结 -- price sale MySQL prod id SELECT

本篇MySQL语句笔记录笔记所引用案例为B站未明学院SQL课程教学案例，个人觉得这个UP主的视频讲解特别仔细，感兴趣小伙伴可以去听听~

一、数据库操作（MySQL运行界面）

按Page up和Page down可以将之前出现过的命令正序或者倒序展示，避免重复输入命令。

--展示所有的数据库
SHOW DATABASES;
--创建一个新的数据库
CREATE DATABASE sqllearning;
--使用名为sqllearning的数据库
USE sqllearning;

二、单表查询（Navicat操作）

1.基础单表查询

--常规单表查询，字段，列
SELECT M.* FROM milk_tea AS M;
SELECT M.prod_name FROM milk_tea AS M;
SELECT M.prod_id , M.prod_name FROM milk_tea AS M;

2.结果修饰，设置别名，添加新列

--查询结果修饰，别名，四则运算，添加新列
SELECT M.net_w AS net_weight FROM milk_tea AS M;
SELECT M.sale_price * 0.9 AS discount_price FROM milk_tea AS M;
SELECT M.* ,0.9 AS discount FROM milk_tea AS M;

3.字符拼接

--字段的拼接
SELECT M.*,CONCAT(M.prod_name,'是',M.net_w) AS '描述' FROM milk_tea AS M;
SELECT M.*,CONCAT_WS('是',M.prod_name,M.net_w,M.sale_price) FROM milk_tea AS M;

4.内容去重，结果排序

--内容去重
SELECT DISTINCT M.net_w FROM milk_tea AS M;

--结果排序
SELECT M.* FROM milk_tea AS M ORDER BY M.pro_date DESC;
SELECT M.* FROM milk_tea AS M ORDER BY CONVERT(M.prod_name USING gbk);
SELECT M.* FROM milk_tea AS M ORDER BY M.net_w DESC,M.valid_month ASC;

5.结果过滤和模糊查询

--结果过滤
SELECT M.* FROM milk_tea AS M WHERE M.prod_name = '饼干';
SELECT M.* FROM milk_tea AS M WHERE M.sale_price * 0.9 > 10;
SELECT M.* FROM milk_tea AS M WHERE M.sale_price BETWEEN 10 AND 20;
--有空值的情况
SELECT M.* FROM milk_tea AS M WHERE IFNULL(M.sale_price,15) = 15;
SELECT M.* FROM milk_tea AS M WHERE M.sale_price IS NOT NULL;
SELECT M.* FROM milk_tea AS M WHERE M.sale_price IS NULL;
--模糊查询
SELECT M.* FROM milk_tea AS M WHERE M.prod_name LIKE '奶_';
SELECT M.* FROM milk_tea AS M WHERE M.prod_name LIKE '%糖';

6.聚合函数

--计数
SELECT COUNT(*) FROM milk_tea;
SELECT COUNT(1) FROM milk_tea;
SELECT COUNT(M.sale_price) FROM milk_tea AS M;
--对多列计数
SELECT COUNT(DISTINCT M.sale_price),COUNT(DISTINCT M.pro_date) FROM milk_tea AS M;
--sum函数
SELECT SUM(M.in_price) FROM milk_tea AS M;
SELECT SUM(M.in_price * 0.9) FROM milk_tea AS M;
SELECT SUM(M.in_price - M.sale_price) FROM milk_tea AS M;

--SUM函数遇见空值直接忽略，不会作为0处理
SELECT SUM(IFNULL(M.in_price,0) - IFNULL(M.sale_price,0)) FROM milk_tea AS M;

--AVG函数
SELECT AVG(M.sale_price) FROM milk_tea AS M;

--聚合函数与过滤语句的结合
SELECT AVG(M.sale_price) FROM milk_tea AS M WHERE M.sale_price > 15;

--最大值最小值
SELECT MIN(m.sale_price) FROM milk_tea AS M;

--可以出现多个聚合函数
SELECT COUNT(M.sale_price), SUM(M.sale_price), AVG(M.sale_price), MAX(M.sale_price), MIN(M.sale_price) FROM milk_tea AS M;

7.HAVING语句与分组

--数据分组
SELECT M.net_w, SUM(M.sale_price) FROM milk_tea AS M GROUP BY M.net_w;
--在上例子中聚合键就是M.net_w
SELECT M.net_w, SUM(M.sale_price) FROM milk_tea AS M  WHERE m.net_w IN ('100g','150g') GROUP BY M.net_w;

--对聚合之后的结果过滤
SELECT M.net_w, SUM(M.sale_price) FROM milk_tea AS M GROUP BY M.net_w HAVING SUM(M.sale_price) > 20;
SELECT M.net_w, SUM(M.sale_price) FROM milk_tea AS M GROUP BY M.net_w HAVING M.net_w IN ('150g','100g');
--以上这一条不推荐，这个是要先计算所有分组再过滤，计算量更大，应该在WHERE语句就进行过滤
--HAVING子句只能对聚合结果进行过滤，在过滤行的时候先在前面过滤

SELECT P.sale_price FROM prod_info AS P WHERE P.class = '日用品';
SELECT P.class, AVG(P.sale_price) FROM prod_info AS P GROUP BY P.class ;
SELECT P.class, AVG(P.sale_price) FROM prod_info AS P GROUP BY P.class HAVING AVG(P.sale_price) > 10 ;
SELECT COUNT(DISTINCT P.brand) FROM prod_info AS P
SELECT prod_name, AVG(sale_price-cost) AS '平均利润' FROM prod_info GROUP BY prod_name;

实操案例

--产品信息表中一共有多少种产品
SELECT COUNT(DISTINCT P.prod_name) FROM prod_info AS P;
--每个类别有多少种产品
SELECT P.class, COUNT(DISTINCT P.prod_name) FROM prod_info AS P GROUP BY P.class;
--一共有多少客户购买了商品
SELECT COUNT(DISTINCT O.cust_name) FROM order_list AS O;
--每个客户分别购买了多少商品
SELECT O.cust_name, COUNT(O.order_id) FROM order_list AS O GROUP BY O.cust_name;

三、多表查询

1.多表关联查询

--标量子查询
SELECT M.prod_name, M.sale_price FROM milk_tea AS M 
WHERE M.sale_price > (SELECT M.sale_price FROM milk_tea AS M WHERE M.prod_name = '奶茶') ;
--关联子查询
--查询售价为15的类别
SELECT M.prod_name FROM milk_tea AS M WHERE m.sale_price = 15;
SELECT M.* FROM milk_tea AS M WHERE M.prod_name IN (
	SELECT M.prod_name FROM milk_tea AS M WHERE m.sale_price = 15);
--子查询生成的值是三个不连续的文本
--将子查询输出的表作为主查询的输入
SELECT * FROM prod_info WHERE prod_name = '抽纸';
--将以上语句一整个作为一张表,给表取一个别名
SELECT B.sale_price FROM (SELECT * FROM prod_info WHERE prod_name = '抽纸') AS B;

2.表联结

--简单内部联结
SELECT * FROM prod_info AS P;
SELECT * FROM supplier_info AS S;
--多表联结查询(某个列),WHERE后面跟着的是联结条件
SELECT P.class, S.supplier_name FROM prod_info AS P, supplier_info AS S WHERE P.supplier_id = S.supplier_id;
--把两个表直接联结起来（把供应商的信息嫁接到产品信息表上）
SELECT P.*, S.* FROM prod_info AS P, supplier_info AS S WHERE P.supplier_id = S.supplier_id;

--内部联结（将INNER JOIN 替代表格之间的逗号，ON替代WHERE）
SELECT P.*,O.* FROM prod_info AS P INNER JOIN order_list AS O ON O.prod_id = P.prod_id AND O.order_id = '20190403001';

--外部联结
SELECT * FROM order_list AS O WHERE O.order_id LIKE '20190407%';
--左外联结，以左边那张表的主键为准展示后面的内容
SELECT C.*, O.* FROM cust_info AS C LEFT OUTER JOIN order_list AS O ON C.cust_id = O.cust_id;
--以上这条展示的就是不管这个客户有没有买东西都会被展示出来
--这种查询最终的总表行数应该是大于等于左边的表行数，这取决于关联表有没有重复的记录（比如一个客户一天有两条流水）
SELECT C.*, O.* FROM cust_info AS C LEFT OUTER JOIN order_list AS O ON C.cust_id = O.cust_id AND O.order_id LIKE '20190401%'; --增加了日期的过滤条件


--在联结中使用聚合函数,看每个客户再这一天买了多少商品
SELECT B.cust_name, COUNT(B.prodname) FROM (SELECT C.cust_name, O.prodname FROM cust_info AS C LEFT OUTER JOIN order_list AS O ON C.cust_id = O.cust_id AND O.order_id LIKE '20190401%') AS B GROUP BY B.cust_name;
--这里是将前一步生成的表作为这里查询的表

--也可以不用把表作为整体，而是直接在之前输出表的语句基础上进行聚合
SELECT C.cust_name, COUNT(O.prodname) FROM cust_info AS C LEFT OUTER JOIN order_list AS O ON C.cust_id = O.cust_id AND O.order_id LIKE '20190401%' GROUP BY C.cust_name; --这里结果与上一条语句是一样的


--UNION聚合
--将4月1号与4月7号的订单合并起来（来自同一张表）
SELECT * FROM order_list AS O WHERE O.order_id LIKE '20190401%'
UNION
SELECT * FROM order_list AS O WHERE O.order_id LIKE '20190407%';
--后面可以继续UNION，但其实同一张表的UNION意义不大，可以直接用WHERE，因此真正有意义的是UNION不同的表
--如果来自不同的表，注意字段的选择要保持一致
--UNION ALL相比起UNION不会去重

实操案例

--找出所有洁云牌抽纸的销售流水（标量子查询）
SELECT P.prod_id FROM prod_info AS P WHERE P.prod_name = '抽纸' AND P.brand = '洁云';
--嵌套获取销售流水
SELECT O.* FROM order_list AS O WHERE O.prod_id = (SELECT P.prod_id FROM prod_info AS P WHERE P.prod_name = '抽纸' AND P.brand = '洁云');

--找出各类商品中售价高于产品类别均价的产品部分
SELECT * FROM prod_info AS P WHERE P.sale_price > (SELECT AVG(P2.sale_price) FROM prod_info AS P2 WHERE P2.class = P.class GROUP BY P2.class);

--用联结的方法改写第一题
SELECT P.*, O.* FROM prod_info AS P INNER JOIN order_list AS O ON P.prod_id = O.prod_id AND P.prod_name = '抽纸' AND P.brand = '洁云';

--归总所有商品的销售量
SELECT O.prodname, COUNT(O.order_id) FROM order_list AS O GROUP BY O.prodname;

--按照类别归总销售量
SELECT P.class, COUNT(O.order_id) FROM order_list AS O LEFT OUTER JOIN prod_info AS P ON P.prod_id = O.prod_id GROUP BY P.class;

四、表中数据（记录）的增删改操作

1.插入新记录

--插入一个完整行
INSERT INTO prod_info2 VALUES ('T0001','测试商品','test','test','test',16,20,'NJ0001');
SELECT * FROM prod_info2;

--插入部分行，需要写明字段
--主键不允许空值也不允许改变
INSERT INTO prod_info2(prod_id, prod_name, brand, type) VALUES ('T0002','测试商品','test','test');
SELECT * FROM prod_info2;
--字段名和后面的插入值一一对应
--将查询结果插入另一个表格中
SELECT * FROM prod_info WHERE prod_id = '10001';
INSERT INTO prod_info2 SELECT CONCAT('T',prod_id), prod_name, brand, type, class, cost, sale_price, supplier_id FROM prod_info WHERE prod_id = '10001';
--用SELECT语句直接替代VALUES语句，注意主键需要拼接字符，因为不能重复

--查询结果插入且只替代部分行
INSERT INTO prod_info2(prod_id, prod_name, brand, type) SELECT CONCAT('T',prod_id), prod_name, brand, type FROM prod_info WHERE prod_id = '10002';

--插入多行查询结果
INSERT INTO prod_info2 SELECT CONCAT('T',prod_id), prod_name, brand, type, class, cost, sale_price, supplier_id FROM prod_info WHERE prod_id LIKE '2000_';
SELECT * FROM prod_info2;
--这样下来一下子就增加了七行

2.更新数据

--表格数据的更新
UPDATE prod_info2 SET class = '日用品';
UPDATE prod_info2 SET class = '零食' WHERE prod_id LIKE 'T2%';
--在插入和更新之前先试验一下过滤条件
UPDATE prod_info2 SET class = '饮料' WHERE prod_id LIKE '3%';
UPDATE prod_info2 SET class = '零食' WHERE prod_id LIKE '2%';
SELECT * FROM prod_info2;
--全场商品打9折处理，更新价格
UPDATE prod_info2 SET sale_price = sale_price*0.9;
UPDATE prod_info2 SET sale_price = sale_price*0.9 WHERE prod_name = '抽纸'OR class = '饮料';

--同时更新两个字段
UPDATE prod_info2 SET sale_price = sale_price*0.9, cost = cost*0.9;
UPDATE prod_info2 SET sale_price = sale_price/0.9, cost = cost/0.9;
--从关联表取数据进行更新，更新一张表的同时关联另一张表（例：将p1的售价变为p2的成本）
UPDATE prod_info2 AS P2 INNER JOIN prod_info AS P1 SET P2.cost = P1.sale_price WHERE P1.prod_name = P2.prod_name AND P1.brand = P2.brand AND P1.class = P2.class;
SELECT * FROM prod_info2;

3.删除数据和清除表

--数据删除
DELETE FROM prod_info2 WHERE prod_name = '测试商品';
--养成对筛选条件先进行检验的习惯
TRUNCATE TABLE --清空表的操作

五、更改表操作

1.创建新表

--创建新表
CREATE TABLE pet2 (name VARCHAR(255) NOT NULL, owner varchar(255),species varchar(255),birth DATE);
INSERT INTO pet2 VALUES ('Brows','Diane','dog','2019-04-07')
SELECT * FROM PET2;
--新建一张表从另一张表复制
SELECT * FROM pet2;
CREATE TABLE pet4 AS SELECT * FROM pet2;
SELECT * FROM pet4;
--只复制表结构不要数据
CREATE TABLE pet4 AS SELECT * FROM pet2 AS p2 WHERE 1=2;
--选择一些字段或者记录复制为新表
CREATE TABLE pet4 AS SELECT P2.name,p2.owner FROM pet2 AS p2;

2.删除整张表

--删除表
DROP TABLE pet4;

3.表字段操作

--给表增加字段
SELECT * FROM pet4;
ALTER TABLE pet4 ADD sex CHAR(1);
--删除字段
ALTER TABLE pet4 DROP birth, DROP sex;
ALTER TABLE pet4 DROP COLUMN sex;
--修改表字段
ALTER TABLE pet4 MODIFY COLUMN sex VARCHAR(255);
ALTER TABLE pet4 MODIFY COLUMN sex CHAR(1) NOT NULL;
--有关字段的增删改都是ALTER开头，和增删改记录是不一样的

4.表格重命名

--给表重命名
RENAME TABLE pet4 TO pet5;

六、MySQL语句固化与存储过程

Navicat中的操作：

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_num`(IN `t_num` integer)
BEGIN
		CREATE TABLE pet4 AS SELECT * FROM pet2;
		ALTER TABLE pet4 ADD age INT;
		UPDATE pet4 SET age = t_num;
		SELECT * FROM pet4;
		DROP TABLE pet4;
END

MySQL中的操作：

先创建存储，再调用存储，用CALL PROCEDURE

七、数据库事物管理

BEGIN;回车后默认进入事物管理过程，

在COMMIT之前，ROLLBACK都是有效的

最后COMMIT，提交结果，数据永久更改，ROLLBACK无效。

回退点的使用

开始事务管理过程，并且在两次删除记录后都设置回退点

在回到回退点sp1之后，再回退sp2会报错，因为回到sp1点的时候sp2并没有创建。

回退点的释放，为了节省存储空间，避免成为数据库中的垃圾代码。

标签：语句,总结,--,price,sale,MySQL,prod,id,SELECT
From： https://blog.csdn.net/weixin_69471669/article/details/139376518

相关文章

  • 【LInux内核中IO多路复用 背景+原理+直白总结+优缺点】EPoll篇 及大总结
    Linux内核中的epoll多路复用原理是基于事件驱动的一种高效I/O处理机制，它主要用于监视多个文件描述符（filedescriptors，简称fd）的状态并进行事件驱动的I/O操作。epoll相比传统的select和poll机制，在处理大量并发连接时具有更高的效率和更低的资源消耗。以下是epoll多路复用原理......
  • [转帖]TPCH 22条SQL语句分析
    https://www.cnblogs.com/xibuhaohao/p/11413669.html 使用TPC-H进行性能测试，需要有很多工作配合才能获得较高性能，如建立索引，表数据的合理分布（使用表空间和聚簇技术）等。本文从查询优化技术的角度，对TPC-H的22条查询语句和主流数据库执行每条语句对应的查询执行计划进行分析，......
  • 【问题解决】MySQL恢复数据库报错Unknown command '\''.
    问题使用以下命令备份恢复数据库，恢复失败提示ERRORatline39595:Unknowncommand'\''.#备份数据库mysqldump-uusername-p--no-create-db-Rdatabasename>dump.sql#恢复数据库mysql-uusername-pdatabasename2<dump.sql问题原因及解法原因：中文字符的问题......
  • builder导致的mp、mysql对应内容出错
    本来今天高高兴兴，BUG毁了这一天1、报错内容org.springframework.dao.DataIntegrityViolationException:Errorattemptingtogetcolumn'name'fromresultset.Cause:java.sql.SQLDataException:Cannotdeterminevaluetypefromstring'a';Cannotdetermine......
  • 学习前端的知识总结10
    CSS浮动网页布局方式有以下五种:标准流（普通流、文档流)︰网页按照元素的书写顺序依次排列浮动定位Flexbox和Grid(自适应布局)标准流是由块级元素和行内元素按照默认规定的方式来排列，块级就是占一行，行内元素一行放好多个元素。1.浮动浮动最典型的应用:可以让多个块级元素一行......
  • 学习前端的知识总结11
    1.浮动和标准流的父盒子搭配:先用标准流的父元素排列上下位置,之后内部子元素采取浮动排列左右位置2.一个元素浮动了，理论其余的兄弟元素也要浮动:一个盒子里面有多个子盒子，如果其中一个盒子浮动了，那么其他兄弟也应该浮动，以防止引起问题。浮动的盒子只会影响浮动盒子后面的......
  • 学习前端的知识总结12
    8.定位浮动可以让多个块级盒子一行没有缝隙排列显示，经常用于横向排列盒子。定位则是可以让盒子自由的在某个盒子内移动位置或者固定屏幕中某个位置，并且可以压住其他盒子。1.定位组成:定位︰将盒子定在某一个位置，所以定位也是在摆放盒子，按照定位的方式移动盒子。定位=定位模......
  • PTA题目集4-6总结
    1.前言知识点：主要考察类的设计与继承题量：每次题目主要是一道大题目难度：答题判题程序-4比较简单，只对题目类进行了改动，在题目类上做了继承家居强电电路模拟程序-1略难，主要是题目比较难懂，对类的设计要进行思考，做出合理的类设计家居强电电路模拟程序-2略难，主要为加入并联......
  • 总结常用9种下载（限速、多线程加速、ZIP、导Excel）
    一、前言下载文件在我们项目很常见，有下载视频、文件、图片、附件、导出Excel、导出Zip压缩文件等等，这里我对常见的下载做个简单的总结，主要有文件下载、限速下载、多文件打包下载、URL文件打包下载、Excel导出下载、Excel批量导出Zip包下载、多线程加速下载。二、搭建SpringBoo......
  • YOLOv10的改进、部署和微调训练总结
    YOLO模型因其在计算成本和检测性能之间的平衡而在实时目标检测中很受欢迎。前几天YOLOv10也刚刚发布了。我们这篇文章就来看看YOLOv10有哪些改进，如何部署，以及微调。YOLOv10通过无nms的训练解决了延迟问题，作者为无nms训练引入了一致的双任务，同时获得了具有竞争力的性能和低推理延......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99