MySQL数据操作

最新推荐文章于 2023-07-12 18:54:11 发布
最新推荐文章于 2023-07-12 18:54:11 发布
阅读量456 收藏
点赞数
分类专栏： MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43347659/article/details/114359462
版权

文章目录

数据的增、删、改、查

插入数据

-- 1.常规操作，使用字段对应的数值直接插入数据表中
INSERT [INTO] tbl_name [(col_name, ...)] VALUES (value, ...)[, (value, ...)];

-- 2.为指定的字段赋值
INSERT [INTO] tbl_name SET col_name = value,...;

-- 3.使用SELECT查询将查询结果插入数据表中
INSERT [INTO] tbl_name[(col_name,...)]
SELECT col_name,... FROM tbl_name1
[WHERE ...];

如果不指定字段名，则需要插入的值与表中所有的字段一一对应

示例：
创建数据表sys_employee表结构如下
在这里插入图片描述
使用常规方法插入数据
在这里插入图片描述
通过第二种方式插入数据
在这里插入图片描述
此时创建另一个表用于存放员工的工号和密码，表结构如下
在这里插入图片描述
使用SELECT语句插入数据表
在这里插入图片描述

删除数据

-- 删除限定条件之内的数据（若不加WHERE条件则会删除表中的所有数据）
DELETE FROM tbl_name [WHERE ...];

-- 清空数据表
TRUNCATE TABLE tbl_name;

使用DELETE删除数据的时候AUTO_INCREMENT属性的初始值不会改变需要使用MODIFY语句进行修改
使用TRUNCATE删除数据时，会直接重置AUTO_INCREMENT的值再次从1开始

在这里插入图片描述
删除序号为8的数据，重新修改序号的默认值再插入值时，才能继续按照序号顺序排列
在这里插入图片描述
使用TRUNCATE清空数据表，再重新插入数据
在这里插入图片描述
可以看到no字段重新从1开始排序

修改数据

UPDATE tbl_name SET col_name = newValue, ... [WHERE ...];

查询数据

单表查询

SELECT [tbl_name|数据表别名.]*|sql_expr [AS 别名] FROM [db_name.]tbl_name [AS 别名] WHERE ...
  1. 使用通配符*查询数据表中所有的字段
  2. 无论是数据表还是检索的字段都可以起别名，用来标识
  3. 对于查询的数据表可以在表名前加所在的数据库的库名，这种操作可以在不切换数据库的情况下访问其他数据库的数据表

WHERE查询条件

筛选条件描述
比较运算符> >= < <= != <> <=> =
<=>和=的区别在于<=>可以检测NULL值
判断字段是否为空IS NULL 或 IS NOT NULL
范围集合[NOT] BETWEEN A AND B
指定集合[NOT] IN (…)
逻辑判断AND 逻辑与
OR 逻辑或
匹配字符[NOT] LIKE ‘…’

示例：
当前sys_employee表数据有：
在这里插入图片描述
查询数据表中部门为NULL的数据
在这里插入图片描述
检测字段是否为NULL可以使用<=>和IS NULL而直接使用department = NULL是检索不到结果的。

范围查询，查询年龄在20-30岁间的员工
在这里插入图片描述
既可以使用BETWEEN也可使用比较运算符进行限制。

多表联查

除了单表查询，还可以进行多个表的联合查询，多表联查主要有三种方式：

  • 笛卡尔积查询
  • 内链接查询
  • 外链接查询

创建3个关联表
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
对应表插入数据

INSERT INTO sys_employee (no, empId, name, age, sex, salary, department, city) VALUES (DEFAULT, 'SOO1', 'Mary', 30, '女', 34000, 1, 1);
INSERT INTO sys_employee (no, empId, name, age, sex, salary, department, city) VALUES (DEFAULT, 'SOO2', 'Tom', 25, '女', 30000, 1, 2);
INSERT INTO sys_employee (no, empId, name, age, sex, salary, department, city) VALUES (DEFAULT, 'SOO3', 'Jack', 34, '男', 20000, 1, 3);
INSERT INTO sys_employee (no, empId, name, age, sex, salary, department, city) VALUES (DEFAULT, 'SOO4', 'Mary1', 50, '女', 34000, 2, 4);
INSERT INTO sys_employee (no, empId, name, age, sex, salary, department, city) VALUES (DEFAULT, 'SOO5', 'Tom1', 22, '女', 30000, 2, 5);
INSERT INTO sys_employee (no, empId, name, age, sex, salary, department, city) VALUES (DEFAULT, 'SOO6', 'Jack1', 33, '男', 20000, 3, 6);

INSERT INTO tb_department (name) VALUES ('研发部');
INSERT INTO tb_department (name) VALUES ('市场部');
INSERT INTO tb_department (name) VALUES ('销售部');
INSERT INTO tb_department (name) VALUES ('产品部');

INSERT INTO tb_city (name) VALUES ('北京');
INSERT INTO tb_city (name) VALUES ('上海');
INSERT INTO tb_city (name) VALUES ('南京');
INSERT INTO tb_city (name) VALUES ('江苏');

笛卡尔积查询用两个表相互关联，用一条记录匹配另一个的所有依次匹配，效果较差，一般不使用这种查询方式。

内联查询：
在这里插入图片描述
通过两个表的关联字段sys_employee中的department和tb_department中的id字段相互关联可以得到查询结果。对于内联查询

如果存在找不到对应的数据，就不会再显示在查询结果中，例如：
在这里插入图片描述
所以使用INNER JOIN就是采用两个表的交集

外链接查询：

外链接包括两种形式，左链接和右链接。左链接则以左侧的表为主，如果在右表中找不到对应的值则用NULL来表示，右链接相反。
在这里插入图片描述
在这里插入图片描述

针对于这种无法产生对应值的数据也被称为脏数据，一般会在表中加入外键来防止脏数据的插入

特殊形式的查询

子查询

子查询主要是在条件约束中加入另一个查询结果作为条件，主要形式包含一下几种：

  • IN条件子句查询
  • 逻辑条件子句查询
  • EXISTS条件子句查询
  • ANY SOME ALL子句查询

创建学生成绩表（tb_student）和学生评级表（tb_grade）

CREATE TABLE IF NOT EXISTS tb_student (
    stuId VARCHAR(4) PRIMARY KEY,
    stuName VARCHAR(10) NOT NULL,
    score INT
)ENGINE = innoDB;

CREATE TABLE IF NOT EXISTS tb_grade (
    grade CHAR(1) PRIMARY KEY,
    score INT
)ENGINE = innoDB;

INSERT INTO tb_student (stuId, stuName, score) VALUES ('S001', 'Jack1', 95);
INSERT INTO tb_student (stuId, stuName, score) VALUES ('S002', 'Jack2', 87);
INSERT INTO tb_student (stuId, stuName, score) VALUES ('S003', 'Jack3', 65);
INSERT INTO tb_student (stuId, stuName, score) VALUES ('S004', 'Jack4', 50);
INSERT INTO tb_student (stuId, stuName, score) VALUES ('S005', 'Jack5', 78);
INSERT INTO tb_student (stuId, stuName, score) VALUES ('S006', 'Jack6', 97);
INSERT INTO tb_student (stuId, stuName, score) VALUES ('S007', 'Jack7', 90);
INSERT INTO tb_student (stuId, stuName, score) VALUES ('S008', 'Jack8', 84);

INSERT INTO tb_grade (grade, score) VALUES ('A', 90);
INSERT INTO tb_grade (grade, score) VALUES ('B', 80);
INSERT INTO tb_grade (grade, score) VALUES ('C', 70);
INSERT INTO tb_grade (grade, score) VALUES ('D', 60);

查询学生表中刚好到达评级分数的数据，就可以使用IN子句查询：
在这里插入图片描述
查询学生表中成绩评价为B的学生成绩：
在这里插入图片描述

EXISTS主要用于判断数据是否存在若存在则继续执行，若不存在则不执行
在这里插入图片描述

ANY SOME ALL的区别

运算符ANYSOMEALL
> >=最小值最小值最大值
< <=最大值最大值最小值
=任意值任意值
!= <>任意值

查询学生成绩在评级中最低值和最大值以上的学生
在这里插入图片描述

联合查询

联合查询是将多个表的数据汇总在一起进行查询，通过关键字UNION和UNION ALL来连接多个表
例如：查看员工表和学生表的所有姓名
在这里插入图片描述
而使用UNION ALL的结果为
在这里插入图片描述

UNION 连接时会自动去除重复的内容，UNION ALL则会将所有内容打印出来

GROUP BY分组

把值相同放到一个组中，最终查询出的结果只会显示组中一条记录，分组配合GROUP_CONCAT()查看组中某个字段的详细信息。

SELECT sql_expr FROM tbl_name GROUP BY col_name;

通过对性别进行分组查询，并查询分组中包含的员工名称
在这里插入图片描述
在进行分组时还有5种聚合函数配合分组查询

  • COUNT(*)：计数，对分组的记录数进行统计，参数可以使用*或者字段名称，需要注意的是如果使用字段名称是无法检测NULL值的
  • SUM(col_name)：求和，对分组记录中的字段进行求和
  • MAX(col_name)：求最大值，查询分组中字段的最大值
  • MIN(col_name)：求最小值，查询分组中字段的最小值
  • AVG(col_name)：求平均值，对分组记录中的字段求平均值

在这里插入图片描述
在语句最后如果加上WITH ROLLUP则会将以上结果进行汇总
在这里插入图片描述
在GROUP BY语句中使用HAVING关键字可以再对分组结果进行筛选，例如：
在这里插入图片描述

ORDER BY排序

SELECT * FROM tbl_name ORDER BY col_name ASC|DESC;

ORDER BY排序的关键字主要是ASC顺序排序和DESC倒序排序
在这里插入图片描述

如果不书写排序方式则默认按顺序排列

LIMIT限制结果查询数量

SELECT * FROM tbl_name LIMIT row_count;
SELECT * FROM tbl_name LIMIT offset, row_count;

第一种语句直接返回数据表中的前多少条，第二种语句带有偏移量，查询从偏移量开始之后的多少条，偏移量从0开始

在这里插入图片描述

外键约束

外键约束在建表时的创建

只有在使用InnoDB的存储引擎时才能使用外键。

删掉上文描述的员工信息表和部门表，重新创建包含外键的数据表。以部门表（tb_department）为父表，员工表（tb_employee）为子表，子表的depId字段作为外键关联父表的depId。
查看部门表的表结构：
在这里插入图片描述

查看员工表的建表语句与表结构：
在这里插入图片描述
在这里插入图片描述

由系统给出的建表语句可以看出，对于创建的外键的字段会自动创建一个空索引，而且也会给外键一个默认的名称，子表的外键字段和主表的主键字段类型要相似；如果是数值型要求一致，并且无符号也要一致；如果是字符型，要求类型一致，长度可以不同，同时子表的外键必须是主表的主键。

先插入一些有效数据：

INSERT INTO tb_department (depId, depName) VALUES (1, '研发部');
INSERT INTO tb_department (depId, depName) VALUES (2, '市场部');
INSERT INTO tb_department (depId, depName) VALUES (3, '销售部');
INSERT INTO tb_department (depId, depName) VALUES (4, '产品部');

INSERT INTO tb_employee(empId, name, depId) VALUES ('S001', 'Jack', 1);
INSERT INTO tb_employee(empId, name, depId) VALUES ('S002', 'Mary', 2);
INSERT INTO tb_employee(empId, name, depId) VALUES ('S003', 'Tom', 3);

要先插入主表，再插入子表

  • 如果子表中有对应主表的值，则主表的记录不能被删除或更新
  • 不能向子表中插入外键不在主表中的值
  • 不能更新子表外键为主表中不存在的值

对于以上三种情况数据库都会报
在这里插入图片描述

动态创建外键和删除外键

ALTER TABLE tbl_name ADD [CONSTRAINT 外键名称] FOREIGN KEY (外键字段) REFERENCES 主表名(主表关联字段);
ALTER TABLE tbl_name DROP FOREIGN KEY 外键名称;

在动态创建外键时要求子表中不能存在脏数据，否则不能创建外键

外键约束的参照要求

  • CASCADE：从主表中删除或更新数据时，子表对应的外键也会自动删除或更新
  • SET NULL：从主表中删除或更新数据时，可以设置子表的值为NULL
  • NO ACTION | RESTRICT：拒绝对主表进行删除和更新操作

删除原来的员工表，重新建立是得更新或删除主表时子表也会自动更新或删除

CREATE TABLE IF NOT EXISTS tb_employee
(
    empId VARCHAR(4) NOT NULL,
    name  VARCHAR(10) DEFAULT NULL,
    depId TINYINT     DEFAULT NULL,
    PRIMARY KEY (empId),
    KEY depId (depId),
    CONSTRAINT depId_fk_dep FOREIGN KEY (depId) REFERENCES tb_department (depId)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE = InnoDB;

INSERT INTO tb_employee(empId, name, depId) VALUES ('S001', 'Jack', 1);
INSERT INTO tb_employee(empId, name, depId) VALUES ('S002', 'Mary', 2);
INSERT INTO tb_employee(empId, name, depId) VALUES ('S003', 'Tom', 3);

重新插入数据后，更新或删除主表的值可以看到子表也会跟着变化
在这里插入图片描述
在这里插入图片描述

Do_GH
关注
专栏目录
datax实现mysql数据同步
congge
02-04 6万+
datax 同步数据使用详解
MySQL数据库的基本操作以及数据类型
未见花闻的博客
03-13 7348
本篇文章介绍MySQL（数据库）基本的操作和数据类型，如显示服务端的数据库，创建数据库，认识MySQL基本的数据类型。
mysql基于while do语法编写存储过程
weixin_39726863的博客
12-11 1336
Delimiter // —-标识符 create procedure testone() begin DECLARE I INT DEFAULT 0; --定义int变量i的起始值为0 DECLARE test_no VARCHRA(50);--定义变量的类型及长度 while i<5 do --定义后while do循环的次数，这里为循环5次 SET test_no = concat(‘1000’,i+1);--数据修改的起始字段，此为拼接，值为10001 insert into ‘test
MySQL 基础---数据操作
mazaiting的博客
10-11 91
数据的操作(CRUD): 插入数据记录(CREATE) 查询数据记录(READ) 更新数据记录(UPDATE) 删除数据记录(DELETE) 插入数据记录("INSERT INTO") 插入数据: 插入完整数据记录、插入数据记录一部分、插入多条数据记录、插入查询结果。 (1) 插入完整数据记录 # 创建表 create table t_dept(...
对于MYSQL数据操作语句的总结
键步随风
01-21 1096
数据操作SELECT --从数据库表中检索数据行和列INSERT --向数据库表添加新数据行DELETE --从数据库表中删除数据行UPDATE --更新数据库表中的数据SELECT一般形式：select *(字段名1...) from table_name(表名...)                                   where (Where子
MySQL数据库基本操作
热门推荐
Drw_Dcm的博客
09-09 5万+
MySQL数据库基本操作
C# MySQL数据库操作类库 含源码
04-17
C#写的一个类库 其中可以对MySQL数据库进行数据库的创建/删除，对表的创建删除，同时还有增删改查功能，是源码
mysql百万级测试数据下载 300W条
05-09
首先，`test.sql`文件是一个MySQL数据库的SQL脚本文件，通常包含创建表结构、插入数据等操作。在这个场景中，它包含了300万条记录，这对于测试数据库性能、查询优化、并发处理能力等提供了充足的数据基础。 1. **...
mysql数据操作_MySQL数据操作
weixin_42516007的博客
01-28 95
添加记录添加记录：INSERT [INTO] tbl_name [(字段名,...)] {VALUE|VALUES} (值...);不指定字段名称：INSERT tbl_name VALUE(值...)需要按照建表时的字段顺序给每一个字段赋值列出指定字段：INSERT tbl_name(字段名称,...) VALUE(值,...);INSERT...SET的形式：INSERT tbl_name S...
mysql 数据操作_MySQL数据操作
weixin_35813209的博客
01-18 99
添加记录添加记录：INSERT [INTO] tbl_name [(字段名,...)] {VALUE|VALUES} (值...);不指定字段名称：INSERT tbl_name VALUE(值...)需要按照建表时的字段顺序给每一个字段赋值列出指定字段：INSERT tbl_name(字段名称,...) VALUE(值,...);INSERT...SET的形式：INSERT tbl_name S...
MySQL数据库操作
qq_51725723的博客
03-08 956
数据库概念 数据库就是存放数据的仓库，他是一个按照数据结构来存储和管理数据的计算机软件系统。 MySQL数据库是一种C/S模型（即客户端和服务器模型），客户端通过用户名，密码登录连接服务器，连接成功后，才可以进行数据库操作（增删改查等） RDBMS：关系数据库管理系统（relational data base management system） SQL ：结构化查询语言 数据库分类 关系型数据库 关系模型就是指二维表格，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。 关系型数据库的核
mysql数据操作_MySQL-数据操作
weixin_29230649的博客
01-18 108
阅读目录一 介绍MySQL数据操作： DML========================================================在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括使用INSERT实现数据的插入UPDATE实现数据的更新使用DELETE实现数据的删除使用SELECT查询数据以及。=========================...
如何操作MySQL数据库数据
最新发布
Liu_Fang_Hong的博客
07-12 580
包括定义全局数据结构、局部逻辑数据结构、存储结构，保密模式以及信息格式等功能，保证存储在数据库中的数据正确、有效和相容，防止不合语义的错误数据被输入或者输出。
mysql对数据操作_对MySQL的数据进行操作
weixin_29371981的博客
02-08 204
1.对于MySQL的基本操作1.删除语句1. DELETE FROM + 表名称 (不改变表格结构)；2.DROP TABLE + 表格名 (删除表，即连同表格结构整个删除)；3.TRUNCATE TABLE + 表名称(只删除表的数据)；注意：DELETE 与 DROP 、 TRUNCATE 的区别：DELETE是数据库操作语言(DML)，事务提交之后才生效；DROP 和 T...
mysql数据库的简单操作
weixin_53841720的博客
05-20 1150
一、启动mysql服务 1、启动mysql服务 win+R键打开运行，输入services.msc打开本地服务，找到MySQL，进行启动、停止等操作。 2、连接mysql 1、win+R键打开运行，输入cmd打开cmd窗口，进入MySQL的安装位置 2、输入mysql –h 主机地址 –u 用户名 –p 3、输入密码 3、退出 输入exit 二、数据库的简单操作（命令不区分大小写） 1、创建mysql的库: create database 库名; 2、查看mysql中所
mysql数据的基本操作（增删改查）及示例
F来日可期的博客
07-16 423
一、添加数据 格式：INSERT INTO 表名(字段) VALUES(值) 1、同时给所有字段添加数据 2、给指定字段添加数据 3、不指定字段添加数据（不建议使用） 所有的字段都必须给值，如果不给值需要使用null进行占位 4、给某一字段批量添加数据 二、修改数据 格式: UPDATE 表名 SET 字段名=’value’ WHERE 字段=值; 注意：必须添加where条件，如果不加，会将表中所有的数据进行更新 三、删除数据 格式：DELETE FROM 表名 WHERE 字段=值;
MySQL基本操作之数据操作
weixin_42893756的博客
04-16 153
3.数据操作 数据操作就是在数据表中对相应字段中所存储的内容进行增删改查操作。 增添数据： 新增数据可分为给全表字段添加数据和部分字段添加数据： 1）给全表字段插入数据，不需要指定字段列表；要求数据的值出现的顺序必须与表中字段顺序一致；非数值数据都需要使用引号扩住 Insert into 表名 values（值列表）[ ,(值列表) 可一次性插入多条数据] # 给学生表全表字段添加数据 ...
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值