MySQL数据库---记录相关操作

序  

表中记录的相关操作一共四种:插入,更新,删除、查询。其中使用最多,也是最难的就是查询。

 

记录的插入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1. 插入完整数据(顺序插入)
语法一:
INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
 
语法二:
INSERT INTO 表名 VALUES (值1,值2,值3…值n);
 
2. 指定字段插入数据
语法:
INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
 
3. 插入多条记录
语法:
INSERT INTO 表名 VALUES
(值1,值2,值3…值n),
(值1,值2,值3…值n),
(值1,值2,值3…值n);  

例子:

1
2
3
insert into db1 values(1, "aaa", 20, "male");
 
insert into db1 values(1, "aaa", 20, "male"), (2, "bbb", 22, "female");

 

记录的更新

1
UPDATE 表名 SET 字段1=值1, 字段2=值2, WHERE CONDITION;

 例子:

1
update db1 set name = "ccc" where name = "aaa";

 

记录的删除

1
DELETE FROM 表名 WHERE CONITION;

 例子:

1
delete from db1 where name ="xxx";

 

记录的查询

1.单表查询

1
2
3
4
5
6
7
格式:
select distinct 字段1,字段2,... from 表名
    where 条件
    group by 分组条件
    having 对分组后的数据进行筛选的条件
    order by 排序
    limit 分组
1
2
3
4
5
6
7
8
9
关键字的执行优先级:
from            找到表:from
where           拿着where指定的约束条件,去表中取出一条条记录
group by        将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组
having          将分组的结果进行having过滤
select          执行select
distinct        去重
order by        将结果按条件排序:order by
limit           限制结果的显示条数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
数据准备:
create table employee(
    id int not null unique auto_increment,
    name varchar(20) not null,
    sex enum('male','female') not null default 'male',
    age int(3) unsigned not null default 28,
    hire_date date not null,
    post varchar(50),
    post_comment varchar(100),
    salary double(15,2),
    office int,
    depart_id int
);
 
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
    ('aaa','male',18,'20170301','tracher',7300.33,401,1),
    ('bbb','male',78,'20150302','teacher',1000000.31,401,1),
    ('ccc','female',18,'20110211','teacher',9000,401,1),
    ('111','female',48,'20150311','sale',3000.13,402,2),
    ('222','female',38,'20101101','sale',2000.35,402,2),
    ('333','female',18,'20110312','sale',1000.37,402,2),
    ('xxx','male',28,'20160311','operation',10000.13,403,3),
    ('yyy','male',18,'19970312','operation',20000,403,3),
    ('zzz','female',18,'20130311','operation',19000,403,3);

1.1 where查询:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
where查询:
    查询所有员工的姓名和薪水
    select name,salary from employee;
     
    查询所有女性员工的姓名和薪水
    select name,salary from employee where sex = 'female';
     
    查询薪资在10000到30000之间的员工的姓名和薪水
    select name,salary from employee where salary between 10000 and 30000;
     
    查询薪资是19000或是7300.33员工的姓名和薪水
    select name,salary from employee where salary in (19000, 7300.33);
     
    匹配名称以a开头的员工的姓名和薪水(%:任意长度任意字符  _:一个任意字符)
    select name,salary from employee where name like 'a%';

1.2 group by分组:

1
2
3
4
5
6
7
8
9
10
group by分组:
    分组:将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等
    注意:分组发生在where之后,即分组是基于where之后得到的记录而进行的
    注意:可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,如果想查看组内信息,需要借助于聚合函数
 
    按照职位进行分组,查询职位的名称
    select post from employee group by post;
     
    按照职位进行分组,查询职位的名称和每一种职位上的员工个数,注意不能单独使用id字段,但是可以使用聚合函数
    select post, count(id) from employee group by post;

1.3 聚合函数:

1
2
3
4
5
6
7
聚合函数:
    注意:聚合函数聚合的是组的内容,若是没有分组,则默认一组
    count()  计算组内个数
    max()    计算组内最大值
    min()    计算组内最小值
    sum()   计算组内和
    avg()   计算组内平均值

1.4 having:

1
2
3
4
5
having
    注意:Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
     
    统计平均薪资超过10000的职位
    select post, avg(salary) from employee group by post having avg(salary) > 10000;

1.5 select:

1
2
3
4
5
6
7
8
select
    注意:执行的是字段,字段也是可以进行计算的
     
    查询所有员工的姓名和薪资
    select name, salary from employee;
     
    查询所有员工的姓名和年薪
    select name, salary * 12 as nian_xing from employee;

1.6 distinct:

1
2
distinct
    注意:distinct后面接多个字段时,表示多个字段接续之后字符串不重复

1.7 order by:

1
2
3
4
5
6
order by
    按照薪资升序排列
    select name, salary from employee order by salary asc;
     
    按照薪资降序排列
    select name, salary from employee order by salary desc;

1.8 limit:

1
2
3
4
5
6
limit:
    按照薪资升序排列, 从第0开始,即先查询出第一条,然后包含这一条在内往后查3条
    select name, salary from employee order by salary asc limit 0, 3;
     
    按照薪资升序排列, 从第3开始,即先查询出第6条,然后包含这一条在内往后查3条
    select name, salary from employee order by salary asc limit 3, 3;

1.9 正则表达式查询:

1
2
3
4
5
6
7
8
9
10
正则表达式查询:
 
    查询所有名称以aa开头的员工
    select * from employee where name regexp '^aa';
     
    查询所有名称以aa结尾的员工
    select * from employee where name regexp 'aa$';
     
    查询所有名称以aa开头且以bb结尾的员工
    select * from employee where name regexp '^aa.*[bb]$';

  

2.多表查询

2.1 表与表之间的关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
表与表之间的关系:
一对一 foreign key + unique
一对多 foreign key
多对多 新建第三张表,建立两个字段,每个字段分别是两张表的外键
 
判断表关系的方法:
 
先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录,如果是,则证明左表的一个字段foreign key 右表一个字段(通常是id)
 
再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录,如果是,则证明右表的一个字段foreign key 左表一个字段(通常是id)
 
总结:
多对一:
  如果只有步骤1成立,则是左表多对一右表
  如果只有步骤2成立,则是右表多对一左表
 
多对多
  如果步骤1和2同时成立,则证明这两张表时一个双向的多对一,即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
 
一对一:
  如果1和2都不成立,而是左表的一条记录唯一对应右表的一条记录,反之亦然。这种情况很简单,就是在左表foreign key右表的基础上,将左表的外键字段设置成unique即可

2.2 查询格式  

1
2
格式:
select 字段列表 from 表1 inner | left | right join 表2 on 表1.字段 = 表2.字段;

2.3 数据准备  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create table department(
    id int,
    name varchar(20)
);
 
create table employee(
    id int primary key auto_increment,
    name varchar(20),
    sex enum('male','female') not null default 'male',
    age int,
    dep_id int
);
 
#插入数据
insert into department values
(200,'aaa'),
(201,'bbb'),
(202,'cc'),
(203,'dd');
(205,'ee');
 
insert into employee(name,sex,age,dep_id) values
('zhansan','male',18,200),
('lisi','female',48,201),
('wangwu','male',38,201),
('zhaoliu','female',28,202)
('chengyi','female',29,204);

2.4 查询方式  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
内连接:
    不适用任何匹配条件。生成笛卡尔积
    select * from employee inner join department;
     
    根据部门ID相同过滤
    select * from employee inner join department on employee.dep_id = department.id;
     
左连接:
    将左表中没有匹配成功的内容也输出
    select * from employee left join department on employee.dep_id = department.id;
     
右连接:
    将右表中没有匹配成功的内容也输出
    select * from employee right join department on employee.dep_id = department.id;
 
 
子查询:
    子查询是将一个查询语句嵌套在另一个查询语句中
    内层查询语句的查询结果,可以为外层查询语句提供查询条件
    子查询中可以包含:inanyall、exists等关键字
    还可以包含比较运算符:= 、 !=、> 、<等
     
    in:表示值存在子查询结果集中
     
    any:表示满足子查询中的任意一个条件
     
    all:表示满足所有子查询的条件
     
    exists:子查询为真,外层循环正常执行;只循环为假,直接返回
     
    查询平均年龄在25岁以上的部门名
    select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);
 
    查询大于所有人平均年龄的员工名与年龄
    select name,age from emp where age > (select avg(age) from emp);
 
    查询为空,因为子查询返回false
    select * from employee where exists (select id from department where id = 204);

  

posted @   Yong_无止境  阅读(325)  评论(0编辑  收藏  举报
(评论功能已被禁用)
编辑推荐:
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 现代计算机视觉入门之:什么是图片特征编码
· .NET 9 new features-C#13新的锁类型和语义
阅读排行:
· Spring AI + Ollama 实现 deepseek-r1 的API服务和调用
· 《HelloGitHub》第 106 期
· 数据库服务器 SQL Server 版本升级公告
· 深入理解Mybatis分库分表执行原理
· 使用 Dify + LLM 构建精确任务处理应用
点击右上角即可分享
微信分享提示