MySQL数据的增删改查排（什么五字真言）

于 2025-01-08 20:35:24 发布
阅读量1.6k 收藏 54
点赞数 47
文章标签： mysql 数据库 sql 数据结构
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/2301_77654321/article/details/145009059
版权

一、插入数据

insert插入数据

INSERT  [INTO] 表名  [字段名] VALUES  (值列表);

 作为示例，我们在一个预先设置好的表中插入一段数据：

insert into students (id,name,age,height,gender,cls_id,is_delete)values (0,'小金',18,180.00,2,1,0)

 

在此前我插入了14个其他数据，小金作为第15个数据插入。

当然，我们也可以同时插入多个数据

insert into students (id,name,age,height,gender,cls_id,is_delete)
values (0,'佩奇',8,130.00,2,1,0),(0,'超人',36,180.00,1,2,0);

 

注意：如果我们可以看到代码中佩奇和超人的id是0，那是因为0可以自增长，否则需要按顺序写如下代码（按我的顺序是16和17）

insert into students (id,name,age,height,gender,cls_id,is_delete)
values (16,'佩奇',8,130.00,2,1,0),(17,'超人',36,180.00,1,2,0);

二、删除数据

delate删除数据

DELETE FROM 表名 [WHERE条件];

接着上文，我们来删除佩奇。

DELETE FROM students where name='佩奇';

 

这样就删掉佩奇了。

可见我的表很长，如果想全部删掉，就需要用另一个命令。

truncate删除表的所有数据

TRUNCATE TABLE 表名;

使用后会显示空，意思是没有数据了。

我们要注意：truncate命令只能用表名，如果写如下命令会报错

TRUNCATE TABLE students where id = 1;

三、修改数据

update更新数据

UPDATE 表名 SET 字段1=值1,字段2=值2,…,字段n=值n [WHERE 条件];

我重新添加佩奇和超人进入表。然后我发现填错了，现需要把佩奇的名字改为小金。

我们输入如下的命令：

update students set name='小金' where name='佩奇';

注意：1、更新多列数据使用逗号隔开；2、添加条件限制

四、查询数据

基本查询

select查询表的数据

SELECT 列名 FROM 表名;

细心的同学会发现，我会给大家展示数据。

如图，就是使用的select命令。使用*会显示所有数据，如果想单独显示某一列，就直接输入那一列的列名。

运算符

算术运算符 + - * / %（取余）

关系运算符 > < = <> >=

赋值运算符 =

逻辑运算符 and（&&） or（||） not（！）

where条件查询

SELECT 列名 FROM 表名 WHERE 条件

如查询id为2学生的姓名，性别 

select name,gender from students where id = 2;

 

如图所示。

where多条件查询

SELECT 列名 FROM 表名 WHERE 条件 运算符 条件;

where查询一次行查询多个条件的情况。

如我们查询id为2和3的学生姓名和性别。

select  name,gender   from   students   where   id=2  or  id=3;

 

这样就能查询多人了。

like模糊查询

SELECT 列名 FROM 表名 WHERE 字段 LIKE '值'

 

我多添加了一些学生信息。

如图所示。大家能发现小金和超人的gender字段是数字，其他人则是男女，代表这是能自由设置的。

我现在想查询名字里面包含'小'的学生的信息。

select name from students where name like '小_';

 

如图所示，当然，我们也可以使用%来替换_。如果是查询height，找一米七几，那我们的like后可以写'17_'。

IN关键字查询

SELECT 列名 FROM 表名 WHERE 字段 IN (值1，值2，....);

 如我们要查询id为1,,5,6,10的学生的信息

select * from students where id in (1,5,6,10);

BETWEEN关键字查询

SELECT 列名 FROM 表名 WHERE 字段 BETWEEN  值1 and 值2;

比如我们找身高在170到180之间的学生。

select * from students where height between 170 and 180;

 

DISTINCT关键字查询【去重】

SELECT DISTINCT 字段名1,字段名2...... FROM 表名;

如查询性别有几种分类 

select distinct gender from students;

这样我们可以知道一个文字段有哪些值。

ORDER BY关键字查询

SELECT <字段名列表> FROM <表名> [WHERE <查询条件>] [ORDER BY <排序的列名> [ASC 或 DESC]];

 示例：将学生的身高按照升序排列

select * from students order by height;

 如图所示，我们发现，身高从上往下逐渐增加。

注意：我们默认是升序排列

那我们反过来：将学生的身高按照降序排列

select * from students order by height desc;

 如图所示，这样就是降序排列。

LIMIT关键字查询

SELECT <字段名列表> FROM <表名> [WHERE <查询条件>] [ORDER BY <排序的列名> [ASC 或 DESC]] [LIMIT <行数>];

如只看前2条学生信息

select * from students limit 2;

 

同理，我们找出身高在178及以下的女生。

select * from students where height<=178 and gender='女' limit 3;

我们发现，我的limit设置的是3，但是合适的只有两人，所以只显示两人。

五、连接查询

（长难句警告）

准备工作

我们为接下来的命令做一些提前准备，

INSERT INTO `students` (`id`, `name`, `age`, `height`, `gender`, `cls_id`, `is_delete`) VALUES (1, '小明', 18, 180, '女', 1, 0);
INSERT INTO `students` (`id`, `name`, `age`, `height`, `gender`, `cls_id`, `is_delete`) VALUES (2, '小月月', 18, 180, '女', 2, 1);
INSERT INTO `students` (`id`, `name`, `age`, `height`, `gender`, `cls_id`, `is_delete`) VALUES (3, '彭于晏', 29, 185, '男', 1, 0);
INSERT INTO `students` (`id`, `name`, `age`, `height`, `gender`, `cls_id`, `is_delete`) VALUES (4, '刘德华', 59, 175, '男', 2, 1);
INSERT INTO `students` (`id`, `name`, `age`, `height`, `gender`, `cls_id`, `is_delete`) VALUES (5, '黄蓉', 38, 160, '女', 1, 0);
INSERT INTO `students` (`id`, `name`, `age`, `height`, `gender`, `cls_id`, `is_delete`) VALUES (6, '凤姐', 28, 150, '保密', 2, 1);
INSERT INTO `students` (`id`, `name`, `age`, `height`, `gender`, `cls_id`, `is_delete`) VALUES (7, '王祖贤', 18, 172, '保密', 1, 1);
INSERT INTO `students` (`id`, `name`, `age`, `height`, `gender`, `cls_id`, `is_delete`) VALUES (8, '周杰伦', 36, NULL, '男', 3, 0);
INSERT INTO `students` (`id`, `name`, `age`, `height`, `gender`, `cls_id`, `is_delete`) VALUES (9, '程坤', 27, 181, '男', 2, 0);
INSERT INTO `students` (`id`, `name`, `age`, `height`, `gender`, `cls_id`, `is_delete`) VALUES (10, '刘亦菲', 25, 166, '男', 2, 0);
INSERT INTO `students` (`id`, `name`, `age`, `height`, `gender`, `cls_id`, `is_delete`) VALUES (11, '猪猪侠', 33, 162, '保密', 3, 1);
INSERT INTO `students` (`id`, `name`, `age`, `height`, `gender`, `cls_id`, `is_delete`) VALUES (12, '静香', 12, 180, '女', 2, 0);
INSERT INTO `students` (`id`, `name`, `age`, `height`, `gender`, `cls_id`, `is_delete`) VALUES (13, '郭靖', 12, 170, '男', 3, 0);
INSERT INTO `students` (`id`, `name`, `age`, `height`, `gender`, `cls_id`, `is_delete`) VALUES (14, '周杰', 34, 176, '女', 1, 0);

INSERT INTO `teachers` (`id`, `name`) VALUES (1, '赵老师');
INSERT INTO `teachers` (`id`, `name`) VALUES (2, '孙老师');
INSERT INTO `teachers` (`id`, `name`) VALUES (3, '李老师');
INSERT INTO `teachers` (`id`, `name`) VALUES (4, '周老师');

INSERT INTO `classes` (`id`, `teacher_id`, `cls_content`, `cls_date`, `name`) VALUES (1, 1, '班级学生有较强的学习能力', '2024-10-01 00:00:00', 'python_01期');
INSERT INTO `classes` (`id`, `teacher_id`, `cls_content`, `cls_date`, `name`) VALUES (2, 2, '班级学生比较顽皮', '2024-11-01 00:00:00', 'python_02期');
INSERT INTO `classes` (`id`, `teacher_id`, `cls_content`, `cls_date`, `name`) VALUES (3, 2, '数据结构班', '2024-11-01 00:00:00', 'data_struct_01期');

注意：以上代码不可以直接使用，如果不存在对应的表，就会报错。（建议把之前创建的students表的数据删干净，或者直接把表删掉）

create table students(
    id int auto_increment primary key,
    name varchar(20),
    age int,
    height float,
    gender char(10),
    cls_id int,
    is_delete int
);
 
create table teachers(
    id int primary key,
    name varchar(20)
);
 
create table classes(
    id int primary key,
    teacher_id int,
    cls_content varchar(100),
    cls_date datetime,
    name varchar(50)
);

如图所示，我们需要创造对应的表，然后才能输入数据。（如果只删除students的数据，保留表的话，省略第一个创造students表的命令）。

这样就可以哩。

查看三个表（students，teachers，classes），如果操作正确，会是上面三个图。

内连接

显示内连接查询

SELECT  字段  FROM  表1 INNER JOIN  表2 ON  连接条件 [WHERE 条件]

 举个例子，我现在要查看学生所在班级。

那我们的命令是：

select s.name,c.name from students s inner join classes c on s.cls_id=c.id;

效果为：

我们对这段长难句拆分：

首先我们想看的是一名学生是哪个班级的。

在students表中，我们需要看的是学生姓名和他们的班级（cls_id）

但我们发现，cls_id是以数字的形式呈现的，我们需要更直观的方式来观察。

所以我们要看代表班级的classes表和students表有什么关联，我们现在知道，cls_id就是代表班级，students的cls_id和classes表的id一一对应。

select s.name,c.name from students s inner join classes c on s.cls_id=c.id;

好了，现在来看这段长难句。

查询学生名（s.name）和班级名(c.name)，这些信息是来自students表的（在students的后面写s就可以用s来指代students，这就是为什么前面写的是s.name，写students.name也对，只是不够快捷，c.name同理），将classes的id替换students的cls_id加入students里。

注意注意：有个小细节，我们是用students表的外键（cls_id）来和classes的主键（id）连接的，我们必须遵循这个规则才能连接成功。

如果说两个name还是过于抽象，我们可以改一下命令：

select s.name as '学生姓名',c.name as '班级名称' from students s inner join classes c on s.cls_id=c.id;

这里的as就是把我们查看的数据命名展示，就直观很多。

那么，我们再进阶一点：查看在python_01期班的学生有哪些

select s.name as '学生姓名',c.name as '班级' from students s inner join classes c on s.cls_id = c.id and c.id=1;

注意尽量别打错命令，挺长的。成功的话就如图所示。

隐式内连接查询

SELECT  字段 FROM  表1,表2 WHERE 表1.条件=表2.条件

我们依旧查看学生所在班级

 select s.name as '名字',c.name as '班级' from students s, classes c where s.cls_id = c.id;

结果如图所示，相比之下，这个长难句更好理解一些。

小总结(GPT说的奥)

  • 显式内连接使用 join 和 on 来明确指定连接条件，推荐使用这种方式，因为它更清晰、可扩展性更强、符合 SQL 标准。
  • 隐式内连接通过 from table1, table2 和 where 子句来指定连接条件，虽然语法较旧，但仍然能工作，尤其在早期的 SQL 版本中使用较多。

 外连接

左外连接查询

左外连接是以左表为基表，返回左表中所有记录及连接表中符合条件的记录的外连接。

SELECT  字段  FROM  表1 LEFT [outer]  JOIN  表2 ON  连接条件 [WHERE 条件];

比如说，查看老师所在班级

select t.name,c.name from classes c left join teachers t on c.teacher_id=t.id;

 正确的话如图所示。

右外连接查询

右外连接是以右表为基表，返回右表中所有记录及连接表中符合条件的记录的外连接。

SELECT  字段  FROM  表1 RIGHT [outer] JOIN  表2 ON  连接条件 WHERE 条件

 将上文的命令的left替换成right

select t.name,c.name from classes c right join teachers t on c.teacher_id=t.id;

我们会发现两个结果是有差别的。

对比左右外连接查询

我们会发现在这两条命令中，以left或right为分界，表classes在左边，表teachers在右边

客观的看这两个表，得到的结果是python_01是赵老师教，python_02期是孙老师教，data_struct_01期是孙老师，李老师和周老师没有任课。

左外连接查询时，以classes为基表，一共只有三门课，也对应三个结果。

右外连接查询时，以teachers为基表，每个老师都列了出来，有任课的老师会显示，没任课的老师也会显示。

六、聚合函数

如果我想统计所有人的身高怎么办？

这就要用聚合函数。聚合函数是可以对一组值进行计算，并返回单个值的函数。

SELECT 聚合函数<字段> FROM 表名 [WHERE 条件] [GROUP BY 聚合函数]
  1. count()-计数
  2. sum()-求和
  3. max()-最大值
  4. min()-最小值
  5. avg()-平均值

 以上是一些聚合函数。说回正题，我想统计所有人身高，怎么办？

select avg(height) as '平均身高' from students;

这样输入就好。

如果我只想统计一班的学生，那就

select avg(height) as '平均身高' from students where cls_id=1;

加上where条件查询即可。

七、子查询【查询嵌套】

如何只查询比刘德华高的同一班的学生信息？——子查询

定义:子查询是在一个查询的内部包括另一个查询的查询方式

简单子查询

现在我想查看刘德华同学的同班同学。可能大家会觉得从students表来看cls_id直接查询会更简单，但如果你只知道刘德华这个名字和字段却不提供其他信息呢？

select * from students where cls_id = (select cls_id from students where name = '刘德华');

这个命令很长，大家可以学我用回车键分段写。只要没有;结束，那就不会执行命令哦。

ANY/SOME子查询

查看赵老师所带的学生信息

select * from students where cls_id = any(select id from classes where teacher_id = (select id from teachers where name='赵老师'));

这种二级嵌套，我们要加上any。

结果如图所示。

All子查询

查看学生所在班级

select * from students where cls_id >= all(select id from classes where teacher_id = (select id from teachers where name='赵老师'));

EXISTS子查询与NOT EXISTS子查询

示例：

DROP TABLE IF EXISTS temp;

意思为，删除一个名叫temp的表，如果这个表能找到的话。

如果没有if exists，那么就会报错，有就不会。

如果大家在有students表的情况下再建一个students表，那就会报错，但如果我们在命令中加入not exists，意思就变成如果没有查到students表，那就建一个students表，就可以避免批量建表时出现重复创建表的情况。

eg: drop table if exists students;

create table IF NOT EXISTS teachers(id int primary key, name varchar(20));

结束，感谢观看！

v维焓
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值