MySQL 的增删改查

1. CRUD

注释：在SQL中可以使用“--空格+描述”来表示注释说明
CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。

create table student(id int,name varchar(20));

1.1 新增

语法：

insert [into] table_name [column] values(values_list);

这里 into 可加可不加

column 是表中属性这里后面会说

values_list 这里是按照表属性的顺序来添加数据

1.1.1 单行添加

insert into student values(1,"张三");

这里可以用中文是因为在创建数据表的时候用了 utf8；

这的字符串可以用单引号也可用双引号

1.1.2 指定列插入

insert into student (id) values (2);

这里的意思是添加一个 id为 2的数据其余为默认值 null

1.1.3 多行插入

insert into student values(3,"李四"),(4,"王五");

这里有一个要注意的点

多行插入的效率比单行插入要高

datatime 类型如何填入？

可以用一个固定的字符串来表示日期

insert into student values(1,'张三','2000-01-01 12:00:00');

表示现在的时间可以直接用函数 now();

insert into student values(2,'李四',now());

1.2 查询（select）

select 条件查询的执行顺序

遍历表中每个记录
把当前值带入条件，根据条件进行筛选
如果合格记录条件成立，就要保留，进行列上的表达式计算

1.2.1 全列查询

语法：select*from + 要查询的表

select*from exam_result;

把表中所有的行和列全部展示出来

*表示通配符表示所有列

危险操作：

读取硬盘，把硬盘的 IO 跑慢了，此时程序的其他部分像访问就非常慢

操作网络，也可能把网卡跑满，此时其他客户端想通过网络访问服务器也非常慢

1.2.2 指定列查询

 select id,name,birthday from exam_result;

1.2.3 查询字段为表达式

select name,english-10 from exam_result;

注意这个并不会改变我们的原始数据只是在最终响应里做了计算

进行查询的时候，是把服务器这里的数据提取迟来，返回客户端，并且以临时表的形式进行展示

所以这里只是修改了临时表

可以计算每个同学的总成绩

select name,chinese+english+math from exam_result;

查询时可以进行简单的统计操作

1.2.4 查询的时候指定别名(给表也可以) （as）

select 表达式 as 别名 from 表名;

1.2.5 去重（distinct）

select distinct 列名 from 表名;

效率很低

1.2.6 排序（order by）

select 列名，列名 from 表名 order by 列名 asc/desc;

asc 为升序

desc 为降序

默认 asc

这里前面的列名是返回临时表中展现的后面的列名是依靠这个列来排序的意思

没有 order by 字句的查询，返回的顺序是未定义，永远不要依赖这个数据
null 数据排序视为比任何值小，升序时出现在最上面
null 跟任何数相加还是 null

加入上面的别名（注意顺序）

select name,chinese+math+english as total from exam_result order by total desc;

对多个字段进行排序这里的意思是这样的

优先数学降序然后如果数学成绩相同则看英语成绩升序排序

select name,chinese,english,math from exam_result order by math desc,english, chinese desc;

1.2.7 条件查询 where

比较运算符：

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于
between a0 and a1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
in (option, ...)	如果是 option 中的任意一个，返回 TRUE(1)
is null	是 NULL
is not null	不是 NULL
like	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

逻辑运算符

运算符	说明
and	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
or	任意一个条件为 TRUE(1), 结果为 TRUE(1)
not	条件为 TRUE(1)，结果为 FALSE(0)

这里注意

null 默认为 false

where 条件可以使用表达式，但不能用别名

and的优先级高于or 这边不建议记如果需要优先进行可以直接用 ();

基本查询

select name,chinese from exam_result where chinese > 80;
select name,chinese+math+english 总分 from exam_result where chinese+math+english < 200;

这里 where 后面的 chinese+math+english 不可以写成总分

and 与 or

select * from exam_result where chinese > 80 and english < 70;
select * from exam_result where chinese > 80 or english < 70;

between ... and ...

select name,english,chinese from exam_result where chinese between 80 and 90;

select name,chinese from exam_result where chinese in (98,87,82,88);

模糊查询like

通配符 % 匹配多个字符（包括0）

select name from exam_result where name like '孙%';// 孙悟空 孙权

孙% 查询开头为孙的

%孙查询结尾为孙的

%孙% 查询包含孙的

通配符 _ 匹配一个字符

select name from exam_result where name like '孙_';// 孙权
select name from exam_result where name like '孙__';// 孙悟空

Null 的查询这里要注意

select * from exam_result where chinese = null;

我们使用 = 查询null 即使有 null 也会显示没有

因为在 mySQL 中 null 默认为 false

当他查询到 null 会被认为是空然后空又默认为 false 所以咧无法查询到结果

这里就要使用：

 select * from exam_result where chinese <=> null;//可以看到多个列
 select * from exam_result where chinese is null;//只能看到一个列
 select * from exam_result where chinese <=> english;

1.2.8 分页查询 limit

语法：

select * from exam_result limit 3;
select * from exam_result limit 3 offset 3;

查询开头三行数据

offset 的意思是偏移量

1.3 修改（update）

语法

update 表名 set 列名 = 值 where 条件;

模拟面试题掌握

如果不写条件就全修改

Rows matched: 7  Changed: 7  Warnings: 1

Rows matched：参与改变的行数

Changed：被改变的行数

Warnings：错误 show warnings 这里可以用这行代码看错误点在哪里

-- 将孙悟空同学的数学成绩变更为 80 分
update exam_result set chinese = 80 where name = "孙悟空";
-- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
update exam_result set math = 60,chinese = 70 where name = "曹孟德";
-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
update exam_result set math = math + 30 order by chinese+english+math limit 3;
-- 将所有同学的语文成绩更新为原来的 2 倍
update exam_result set chinese = chinese*2;

1.4 删除(delete)

语法

delete from 表名 where / order by / limit ;

案例：

delete from exam_result where name = "孙悟空";
-- 删除这个表数据
delete from exam_result;

delete 跟 drop 不一样

delete 是删除表内数据但是不删除表

drop 整个表删除

2. 数据库约束

数据库中的数据是有一定约束的

有些数据认为是合法数据有些是非法数据

数据库，自动的对数据的合法性进行晓燕检查的一系列机制

目的保证数据库中能够避免被插入/修改一些非法数据

MySQL 中提供了以下约束：

not null ：指示某列不能存储 Null 值
unique：保证某列的每行必须是唯一的值
default：规定没有给列赋值时的默认值
primary key：not null 和 unique 的结合
foreign key: 外键约束

2.1 not null

create table if not exists student(id int not null,name varchar(20));

创建表时指定列不可以为空

2.2 unique

指定列必须是唯一值不可以重复的

每次使用这个约束都会先进行查询操作

create table if not exists student(id int unique);

2.3 default

给指定列赋默认值

create table if not exists student(name varchar(20) default "未命名");

2.4 primary key

设置主键即为不重复以及不能为空

这里注意一个表中只有一个主键，但是主键不一定是一个列可以多个列组成一个主键

create table if not exists student(id int primary key);

数据库有一个自增让数据库自己去分配

对于整数类型的主键，常配搭自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1。

create table if not exists student(id int primary key auto_increment,name varchar(20));

2.5 foreign key

外键约束：

语法：

foreign key (字段名) references 主表(列);

案例：

先建一个 class 表

create table class(
	id int primary key,
    name varchar(20),
);

再建一个 student 表与其产生外键约束

create table student(
	id int primary key,
    name varchar(20),
    classId int,
    foreign key classId references class(id),
);

这里 student 的 classId 与 class中的 id 产生了外键约束

class 为父表

student 为子表

特性：

父表约束子表

classId 只能是 id中的其中一个也就是引用了 id 中的值

如果 id 中没有这个值就会报错

子表反向约束父表

如果 classId中已经引用了 id 中的某一个值

那么这个值在父表中不可以被修改或者删除

同样如果子表跟父表已经产生了外键约束的关系

即便两个表都是空表在子表还在的情况下无法直接删除父表
父表中必须有主键/unique 否则无法构成外键约束

3.表设计

一对一

比如让你设计两个一对一的表

用户以及身份证

一个用户对应一个身份证号

如何设计

我们可以设计一个 person 表以及 identity 表

create table person(id int primary key,name varchar(20));
create table identity(id int,name varchar(20),password int,
                     foreign key id references person(id));

让他们之间有一个属性互相关联即可

一对多

比如

一个班级有多个学生

一个学生只有一个班级

class -----> student
```
create table class(
    classId int primary key,
    name varchar(20)
);
create table student(
    id int primary key,
    name varchar(20),
    classid int,
    foreign key classid references class(classid)
);
```
让每个学生去对应班级

其实也可以用另外一种就是让

班级中存放每个学生的 id

但是 mySql 中没有数组这种基础结构所以 mySQL 不适用
多对多

一个学生可以选多种课

一个课程中有多个学生
```
create table student(
    studentId int primary key,
    name varchar(20);
);
create table course(
	courseId int primary key;
    name varchar(20);
);
create table student_coutse(
	studentId int;
    courseId int;
);
```
这里写了一个连接表比如

student 中有（1，张三）（2，李四）

course 中有（1，语文）（2，数学）

这里的连接表 student_course

就可以这样存（1，1）（1，2）

代表张三选了语文课张三选了数学课

4.新增增强

插入查询的结果

这里有一个必要的前提条件就是你想要插入的和查询的必须列数一样不然胡报错

insert into test(name,email) select name,email from student;

5.查询

5.1 聚合查询

5.1.1 聚合查询

一些 mySQL 提供的聚合函数

函数	说明
count()	返回查询到的数据的数量
sum()	返回查询到的数据的总和，不是数字没有意义
avg()	返回查询到的数据的平均值，不是数字没有意义
max()	返回查询到的数据的最大值，不是数字没有意义
min()	返回查询到的数据的最小值，不是数字没有意义

count()
```
-- 查询 student 的 所有行数
select count(*) from student;
-- 查询 student 中 name 的行数 
select count(name) from student;
```
说明：

这里的通配符 * 是会将 null 也一起计算的

但是 name 就不会

同样的这里的括号内也是可以添加表达式像上面一样
sum()
```
select sum(chinese) from exam_result;
```
说明：

查看 chinese 的总和

同样也是可以加入表达式的
```
select sum(chinese+english+math) from exam_result;
```
这里会新建一个临时表计算 chinese+english+math 然后再进行输出

注意

字符串的运算他不会报错它会将字符串转换为 double 结构进行运算如果无法转换则会变成0 并且产生一个错误
```
select sum(name) from exam_result;
```
```
7 warnings
```
avg()
max()
min()

5.1.2 group by语句

分组按照字段进行分组

语法：

select name,role,min(salary),max(salary),avg(salary) from emp group by role;

可以衔接 where 语句

查询每个岗位的平均工资但是排除张三的

先排除后计算

直接使用 where where 语句一般在 group by 前面

select name,rolo,avg(salary) from emp where name != "张三" group by role;

查询每个岗位的平均工资但是排除平均工资大于2w的结果

算计算后排除

使用 having 语句，having语句一般在 group by 后面

select name,role,avg(salary) from emp group by role having avg(salary) < 20000;

同时完成以上的两种查询

查询每个岗位的平均薪资不算张三平且排除大于2w 的结果

select name,avg(salary) from emp where name != "张三" group by role having avg(salary) < 20000;

5.2 联合查询

可以用，克制使用，不能非常广泛的使用

多个表进行同时查询

关键思路：

理解 ”笛卡尔积“ 工作过程

进行笛卡尔积
```
select * from student join score;
```

筛选条件

select * from student join score where student.id = score.student_id;

精简列

select sn,name,score from student join score where student.id = score.student_id;

聚合

select sn,name,sum(score) from student join score where student.id = score.student_id group by name;

5.3 子查询

嵌套查询两个 sql 用一个sql 完成

单行子查询

我想查询不想毕业同学的同班同学

-- 先查询 不想毕业同学 的班级号
select classes_id from student where name = "不想毕业";
-- 再使用 班级号 来查询其他同学
select id,name from student where classes_id = 1;

子查询就是将上面合二为一进行嵌套

select id,name from student where classes_id =(select classes_id from student where name = "不想毕业");

5.4 合并查询

使用合并查询前后查询的结果之中必须保证字段一致

union

查询id小于3 或者名字为英文的

取两个结果集的并集
```
-- 可以利用 or 
select * from course where id<3 or name = "英文";
-- 利用 union
select * from course where id < 3 union select * from course where name = "英文"; 
```
or 跟 union 的区别

union 的逻辑是将两个临时表进行合并然后去重

or 则是进行遍历

而且 union 可以进行不同表之间的合并查询

or 只能针对单个表
union all

合并后不进行去重操作取得两个结果的并集

posted @ 2024-10-01 16:39 Gargenone 阅读(364) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· MySQL

· MySQL 的索引和事务

· MySQL 表的CRUD与复合查询

· day03-MySQL基础知识02

· MySQL数据库CRUD

阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了

公告

昵称： Gargenone
园龄： 10个月
粉丝： 0
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

随笔分类

MySQL(4)

ljy2003

MySQL 的增删改查

MySQL 的增删改查

1. CRUD

1.1 新增

1.1.1 单行添加

1.1.2 指定列插入

1.1.3 多行插入

1.2 查询（select）

1.2.1 全列查询

1.2.2 指定列查询

1.2.3 查询字段为表达式

1.2.4 查询的时候指定别名(给表也可以) （as）

1.2.5 去重（distinct）

1.2.6 排序（order by）

1.2.7 条件查询 where

1.2.8 分页查询 limit

1.3 修改（update）

1.4 删除(delete)

2. 数据库约束

2.1 not null

2.2 unique

2.3 default

2.4 primary key

2.5 foreign key

3.表设计

4.新增增强

5.查询

5.1 聚合查询

5.1.1 聚合查询

5.1.2 group by语句

5.2 联合查询

5.3 子查询

5.4 合并查询

公告

搜索

常用链接

随笔分类

随笔档案

阅读排行榜

ljy2003

MySQL 的增删改查

MySQL 的增删改查

1. CRUD

1.1 新增

1.1.1 单行添加

1.1.2 指定列插入

1.1.3 多行插入

1.2 查询 （select）

1.2.1 全列查询

1.2.2 指定列查询

1.2.3 查询字段为表达式

1.2.4 查询的时候 指定别名(给表也可以) （as）

1.2.5 去重（distinct）

1.2.6 排序（order by）

1.2.7 条件查询 where

1.2.8 分页查询 limit

1.3 修改（update）

1.4 删除(delete)

2. 数据库约束

2.1 not null

2.2 unique

2.3 default

2.4 primary key

2.5 foreign key

3.表设计

4.新增增强

5.查询

5.1 聚合查询

5.1.1 聚合查询

5.1.2 group by语句

5.2 联合查询

5.3 子查询

5.4 合并查询

公告

搜索

常用链接

随笔分类

随笔档案

阅读排行榜

1.2 查询（select）

1.2.4 查询的时候指定别名(给表也可以) （as）