与MySQL的零距离接触(二.实例:记录操作)

2024-01-30 15:59

本文主要是介绍与MySQL的零距离接触(二.实例:记录操作),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!


道德三皇五帝,功名夏侯商周,五霸七雄闹春秋,顷刻兴亡过手,
青石几行名姓,北邙无数荒丘,前人播种后人收,说甚龙争虎斗。


一、操作数据表中的记录

1、MySQL插入记录INSERT

这里写图片描述

创建新表,并添加记录。

这里写图片描述

2、MySQL插入记录INSERT SET-SELECT

这里写图片描述

INSERT users3 SET username='Ben',password='456';

这里写图片描述

3、MySQL单表更新记录UPDATE

这里写图片描述

//表示原有年龄的基础上加5 更新一列
UPDATE users3 set age=age+5;
//更新多列(年龄改变为当前年龄减去id,sex值都为0)
UPDATE users3 SET age =age-id,sex=0;

这里写图片描述

//使用条件更新记录
UPDATE users SET age = age+10 WHERE id%2=0;

这里写图片描述

4、MySQL单表删除记录DELETE

这里写图片描述

DELETE FROM users3 WHERE id=2;

这里写图片描述

删除一条记录,在添加一条记录,添加的id为删除前最大的id号加1

5、MySQL查询表达式解析

这里写图片描述

这里写图片描述

SELECT id,username FROM users3;
//使用别名查找
SELECT id AS userId,username AS uname FROM users3;

6、MySQL where语句进行条件查询

这里写图片描述

7、MySQL group by 语句对查询结果分组

这里写图片描述

ASC升序,DESC降序

SELECT sex FROM users3 GROUP BY sex;

8、having语句设置分组条件

这里写图片描述

对GROUP BY的查询结果进行过滤
在HAVING字句中,查询对象必须出现在查询表达式中或使用聚合函数

 SELECT sex FROM users3 GROUP BY 1 HAVING count(id)>=2;

9、order by语句对查询结果排序

这里写图片描述

SELECT * FROM users3 ORDER BY id DESC;

10、limit语句限制查询数量

这里写图片描述

SELECT*FROM users LIMIT 2;    表示记两条记录,第0条开始SELECT*FROM users LIMIT 3,2;  表示在第3条开始记录,共2条记录
//把查询到的结果的username插入到另外一张表
INSERT test(username) SELECT username FROM users3 WHERE age>=20;

这里写图片描述

二、子查询与连接

1、数据准备

1》创建tdb_goods数据表

这里写图片描述

CREATE TABLE tdb_goods(-> goods_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,//商品id-> goods_name VARCHAR(150) NOT NULL,//商品名称-> goods_cate VARCHAR(40) NOT NULL,//商品分类-> brand_name VARCHAR(40) NOT NULL,//商品品牌-> goods_price DECIMAL(15,3) UNSIGNED NOT NULL,//商品价格-> is_show TINYINT(1) NOT NULL DEFAULT 1,//商品是否上架-> is_saleoff TINYINT(1) NOT NULL DEFAULT 0//商品是否销售一空-> );

查看数据表:SHOW TABLES;
查看数据表结构:SHOW COLUMNS FROM tdb_goods;

修改数据表属性:

ALTER TABLE tdb_goods MODIFY goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0.000;

2》写入记录

 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini MD531CH/A 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD788CH/A 9.7英寸平板电脑 (16G WiFi版)','平板电脑','苹果','3388',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 (16G WiFi版)','平板电脑','苹果','2788',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP 台式电脑 (i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux )','台式机','宏碁','3699',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X3250 M4机架式服务器 2583i14','服务器/工作站','IBM','6888',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('玄龙精英版 笔记本散热器','笔记本配件','九州风神','',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

3》以网格线的形式查看插入的数据

ELECT * FROM tdb_goods\G;

这里写图片描述

如果显示乱码则修改客户端的显示编码格式

SET NAMES gbk;

2、MySQL子查询简介

这里写图片描述

子查询是一种常用计算机语言SELECT-SQL语言中嵌套查询下层的程序模块。当一个查询是另一个查询的条件时,称之为子查询。
例句:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

其中,SELECT * FROM t1 …称为Outer Query[外查询](或者Outer Statement),
SELECT column1 FROM t2 称为Sub Query[子查询]。

所以,我们说子查询是嵌套在外查询内部。而事实上它有可能在子查询内部再嵌套子查询。
子查询必须出现在圆括号之间。

这里写图片描述

这里写图片描述

3、由比较运算符引发的子查询

这里写图片描述

查找所有商品的平均价格

//AVG为求平均值的聚合函数
SELECT AVG(goods_price) FROM tdb_goods;

这里写图片描述

//计算价格平均值,四舍五入保留小数点后2位
SELECT ROUND(AVG(goods_price),2)FROM tdb_goods;
SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= 5636.36;

这里写图片描述

通过子查询的方式实现

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2)FROM tdb_goods);

寻找超极本的价格

SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本';

这里写图片描述

查询哪些商品的价格大于超级本

这里写图片描述

由于查询到多个结果,所以报错,需要使用下面的方式进行修饰

这里写图片描述

这里写图片描述

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate ='超级本');

这里写图片描述

由此看出都是大于4299

这里写图片描述

4、由[NOT]IN/EXISTS引发的子查询

这里写图片描述

这里写图片描述

5、使用INSERT…SELECT插入记录

在上面的数据中,goods_cate和goods_name字段有很多是重复的,且汉字所占字节数要比数字要多,如果数据量比较大的话,查询起来会越来越慢。我们可以通过外键的方式进行更改数据表。
新创建一张数据表 tdb_goods_cates

 CREATE TABLE IF NOT EXISTS tdb_goods_cates(-> cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> cate_name VARCHAR(40) NOT NULL-> );
//查看分类
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

这里写图片描述

这里写图片描述

-- 将分组结果写入到tdb_goods_cates数据表INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

这里写图片描述

6、多表更新

这里写图片描述

这里写图片描述

这里写图片描述
在mysql,join,cross join和inner join 是等价的。
left [outer] join,左外连接
right [outer] join,右外连接

//INNER JOIN 内连接UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name-> SET goods_cate = cate_id;

这里写图片描述

7、多表更新之一步到位

这里写图片描述

这里写图片描述

 CREATE TABLE tdb_goods_brands-> (-> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> brand_name VARCHAR(40) NOT NULL-> )-> SELECT brand_name FROM tdb_goods GROUP BY brand_name;

这里写图片描述

更新tbd_goods数据

mysql> UPDATE tdb_goods INNER JOIN tdb_goods_brands ON brand_name = brand_name-> SET brand_name = brand_id;
ERROR 1052 (23000): Column 'brand_name' in field list is ambiguous

错误信息:brand_name含义不明确

我们使用别名的方式进行更改

UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;

这里写图片描述

但是并不能更改表结构

这里写图片描述

因此我们要记得修改表结构

 ALTER TABLE tdb_goodsCHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL, CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

这里写图片描述

我们分别在tdb_goods_cates和tdb_goods_brands表插入记录

INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');
-- 在tdb_goods数据表写入任意记录INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');

平时,我们需要将多个表的数据展示给用户观看,就需要使用连接来完成。

8、连接的语法结构

这里写图片描述

这里写图片描述

这里写图片描述

9、内连接INNER JOIN

这里写图片描述

这里写图片描述

仅显示符合连接条件的记录

这里写图片描述

SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

这里写图片描述

并没有查找出我们新更新的数据内容,因为它们不满足查询的条件

INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');
INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');
INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');

10、外连接OUTER JOIN

这里写图片描述

这里写图片描述

内连接:显示左表及右表符合连接条件的记录,A ∩ B
左外连接:显示左表中的全部和右表符合连接条件的记录,A ∪ (A ∩ B)
右外连接:显示右表中的全部和左表符合连接条件的记录,B ∪ (A ∩ B)

11、多表连接

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g -> INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id-> INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

12、关于连接的几点说明

这里写图片描述

这里写图片描述

13、无限级分类表设计

这里写图片描述

 CREATE TABLE tdb_goods_types(type_id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,//分类idtype_name VARCHAR(20) NOT NULL,//分类名称parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0//父类id); 
INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);

这里写图片描述

这里写图片描述

自身连接:同一个数据表对其自身进行连接。

SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;

这里写图片描述

SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN  tdb_goods_types s ON s.parent_id = p.type_id;

这里写图片描述

-- 查找所有分类及其子类的数目SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;-- 为tdb_goods_types添加child_count字段ALTER TABLE tdb_goods_types ADD child_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0;-- 将刚才查询到的子类数量更新到tdb_goods_types数据表UPDATE tdb_goods_types AS t1 INNER JOIN ( SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id ) AS t2 ON  t1.type_id = t2.type_id SET t1.child_count = t2.children_count;-- 复制编号为12,20的两条记录SELECT * FROM tdb_goods WHERE goods_id IN (19,20);-- INSERT ... SELECT实现复制INSERT tdb_goods(goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (19,20);

14、多表删除

这里写图片描述

-- 查找重复记录SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;-- 删除重复记录DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2  ON t1.goods_name = t2.goods_name  WHERE t1.goods_id > t2.goods_id;

这篇关于与MySQL的零距离接触(二.实例:记录操作)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


原文地址:https://blog.csdn.net/wtdask/article/details/79272655
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.chinasem.cn/article/660766

相关文章

MySQL8.0设置redo缓存大小的实现

《MySQL8.0设置redo缓存大小的实现》本文主要在MySQL8.0.30及之后版本中使用innodb_redo_log_capacity参数在线更改redo缓存文件大小,下面就来介绍一下,具有一... mysql 8.0.30及之后版本可以使用innodb_redo_log_capacity参数来更改

Python调用Orator ORM进行数据库操作

《Python调用OratorORM进行数据库操作》OratorORM是一个功能丰富且灵活的PythonORM库,旨在简化数据库操作,它支持多种数据库并提供了简洁且直观的API,下面我们就... 目录Orator ORM 主要特点安装使用示例总结Orator ORM 是一个功能丰富且灵活的 python O

python使用fastapi实现多语言国际化的操作指南

《python使用fastapi实现多语言国际化的操作指南》本文介绍了使用Python和FastAPI实现多语言国际化的操作指南,包括多语言架构技术栈、翻译管理、前端本地化、语言切换机制以及常见陷阱和... 目录多语言国际化实现指南项目多语言架构技术栈目录结构翻译工作流1. 翻译数据存储2. 翻译生成脚本

Springboot中分析SQL性能的两种方式详解

《Springboot中分析SQL性能的两种方式详解》文章介绍了SQL性能分析的两种方式:MyBatis-Plus性能分析插件和p6spy框架,MyBatis-Plus插件配置简单,适用于开发和测试环... 目录SQL性能分析的两种方式:功能介绍实现方式:实现步骤:SQL性能分析的两种方式:功能介绍记录

使用 sql-research-assistant进行 SQL 数据库研究的实战指南(代码实现演示)

《使用sql-research-assistant进行SQL数据库研究的实战指南(代码实现演示)》本文介绍了sql-research-assistant工具,该工具基于LangChain框架,集... 目录技术背景介绍核心原理解析代码实现演示安装和配置项目集成LangSmith 配置(可选)启动服务应用场景

oracle DBMS_SQL.PARSE的使用方法和示例

《oracleDBMS_SQL.PARSE的使用方法和示例》DBMS_SQL是Oracle数据库中的一个强大包,用于动态构建和执行SQL语句,DBMS_SQL.PARSE过程解析SQL语句或PL/S... 目录语法示例注意事项DBMS_SQL 是 oracle 数据库中的一个强大包,它允许动态地构建和执行

前端原生js实现拖拽排课效果实例

《前端原生js实现拖拽排课效果实例》:本文主要介绍如何实现一个简单的课程表拖拽功能,通过HTML、CSS和JavaScript的配合,我们实现了课程项的拖拽、放置和显示功能,文中通过实例代码介绍的... 目录1. 效果展示2. 效果分析2.1 关键点2.2 实现方法3. 代码实现3.1 html部分3.2

0基础租个硬件玩deepseek,蓝耘元生代智算云|本地部署DeepSeek R1模型的操作流程

《0基础租个硬件玩deepseek,蓝耘元生代智算云|本地部署DeepSeekR1模型的操作流程》DeepSeekR1模型凭借其强大的自然语言处理能力,在未来具有广阔的应用前景,有望在多个领域发... 目录0基础租个硬件玩deepseek,蓝耘元生代智算云|本地部署DeepSeek R1模型,3步搞定一个应

SQL 中多表查询的常见连接方式详解

《SQL中多表查询的常见连接方式详解》本文介绍SQL中多表查询的常见连接方式,包括内连接(INNERJOIN)、左连接(LEFTJOIN)、右连接(RIGHTJOIN)、全外连接(FULLOUTER... 目录一、连接类型图表(ASCII 形式)二、前置代码(创建示例表)三、连接方式代码示例1. 内连接(I

在MySQL执行UPDATE语句时遇到的错误1175的解决方案

《在MySQL执行UPDATE语句时遇到的错误1175的解决方案》MySQL安全更新模式(SafeUpdateMode)限制了UPDATE和DELETE操作,要求使用WHERE子句时必须基于主键或索引... mysql 中遇到的 Error Code: 1175 是由于启用了 安全更新模式(Safe Upd