数据库基本增删改查语法和多表链接查寻的方式

最新推荐文章于 2024-01-10 09:43:38 发布

初见#

最新推荐文章于 2024-01-10 09:43:38 发布

阅读量412

点赞数 9

文章标签：数据库 java sql

本文链接：https://blog.csdn.net/Alsace_/article/details/126823886

版权

前言

一、DML(Data Manipulation Language)数据操作语言

1.增加数据的insert语句

（1）全字段插入

（2）不完全字段插入(字段与值需要一一对应)

（3）一次性添加多条数据

（4）查询插入（查询表和插入表都要存在&&字段数量类型要对应）

（5）查询创建（查询表存在，插入表不存在）

（3）truncate(将整张表的数据清空，连带索引一起清空)

[delete/truncate/drop的区别]：

二、DQL(Data Query Language)数据查询语言

（3）left join外联查询（左外联）

（4）right join外联查询（右外联）

前言

今天我们来说一下数据库的基本增删改查语法和多表链接查询的方式，整篇均以下面两张表结构为基础做操作

student表
sid	sname	ssex	classid

class表
classid	classname

course表
cid	cname

score表
sid	cid	score

一、DML(Data Manipulation Language)数据操作语言

数据库基本操作的语言，包括增(insert)、删(delete)、改(update)

[注意]：

字符串类型的数据使用''或""引用起来，但是引用方式需要统一

#示例：
#单引字符串里可以有双引号字符，双引字符串里也可以有单引号字符
"萨尔"  |  '萨尔'  |  "初'见'"  |  '初"见'

日期类型的数据应使用字符串的格式来书写

示例：
#这样书写不会产生歧义，系统有可能会认为-为减号去做减法运算
"2022-09-12"

词语词之间空格换行不作要求，但一般应该写的美观一些，使代码的可读性变强
```
#示例：
select * 
from 
student where sname = 
"萨尔";
```

1.增加数据的insert语句

（1）全字段插入

语法：insert into 【表名】 values (值,值...)；

#示例：
insert into student values(1,"Alsace","男",1);

（2）不完全字段插入(字段与值需要一一对应)

语法：insert into 【表名】(字段,字段...) values (值,值...);

#示例
insert into student(sid) values(1);
insert into student(sid,sname) values(1,"Alsace");

（3）一次性添加多条数据

语法：insert into 【表名】 (字段,字段...) values (值,值...),(值,值...)

#示例：
insert into student values(1,"萨尔","男",1),(2,"萨罗姆","男",3);

（4）查询插入（查询表和插入表都要存在&&字段数量类型要对应）

--不推荐使用

语法：insert into 【表名】 select [查询语句]

#示例：
insert into student2 select * from student;

（5）查询创建（查询表存在，插入表不存在）

--该操作相当于创建一个新的表，再将查询到的数据插入到新表之中

语法：create table 【表名】 select 【查询语句】

#示例：
creaate table student2 select * from student;

[注意]：

value和values本质上没有区别，唯一的区别是：

-- value一次只能添加一条数据

-- values一次可以添加多条数据

2.删除数据的delete语句

（1）删除（危险操作）

语法：delete from 【表名】;

#示例：
#删除一般需要带条件，否则像下面这样会删除整张表的数据
delete from student;

（2）带条件的删除

语法：delete from 【表名】【where子句】;

#示例：
#删除名字为萨尔的学生
detele from student where sname = '萨尔';

（3）truncate(将整张表的数据清空，连带索引一起清空)

在我们在有主键自增的数据表中插入一条数据后，再将该数据删除，这时我们在插入另外一条数据会发现该数据的主键不会连着上一条数据，而是连着删除的那条数据的主键，而truncate可以清空主键的自增

语法：truncate 【表名】;

#示例：
truncate student;

[delete/truncate/drop的区别]：

delete：删除表中一条或多条数据
truncate：删除表中所有的数据，并且清空主键自增
drop：直接删除表结构（将数据表删除）

3.修改数据的update语句

（1）无条件的修改（一般不用）

语法：update 【表名】set 【字段名】= 【值】;

#示例：
#此操作会将表中该字段的所有数据全部修改为指定值
#将学生表中所有学生的名字修改为"格罗姆"
update student set sname = "格罗姆";

（2）带条件的修改

语法：update 【表名】set 【字段名】= 【值】【where子句】;

#示例：
#将学生表中名字为格罗姆的学生的班级改为1班
update student set classid = 1 where sname = "格罗姆";

（3）多条件的修改

                语法：

                方式一：

                update 【表名】set 【字段名】= 【值】where 【条件一】and 【条件二】

                方式二：

              update 【表名】set 【字段名】= 【值】where 【字段】between 【值】and【值】

#示例：
方式一(and)：
#将名字为格罗姆的男生的班级修改为1班
update student set classid = 1 where sname = "格罗姆" and ssex = "男";
方式二(between)：
#该方式只能用于数值型字段，两个数值为闭合区间，使用时规定小的数值写前面，大的数值写后面
#将班级号大于等于2且小于5的学生的班级号修改为3
update student set classid = 3 where classid between 2 and 5;

二、DQL(Data Query Language)数据查询语言

数据库最核心、最常用、最重要的语言，用来查询数据库中的字段、记录等一系列数据

[注意]：所有的查询结果均为虚拟表

1.基本查询（单表查询）

（1）最简单的查询（直接查询数据）

语法：select 【数据】；

#示例：
select 'a';    -- 结果为字符：a
select 10;     -- 结果为：10
select 1+1;    -- 结果为：2

（2）全字段的查询

                语法：

                select * from 【表名】;

                或

                select 【所有字段】from 【表名】;

#示例：
#查询所有的学生信息
select * from student;
select sid,sname,ssex,classid from student;

（3）部分字段查询

语法：select 【字段名,字段名...】from 【表名】;

#示例：
#查询学生表中的学生id和学生姓名
select sid,sname from student;
#也可以嵌套最简单的直接查询，给查询出的每一条数据加上该数据
select sid,sname,'数据库管理员' from student;

（4）给查询字段起别名

                语法：

                方式一：select 【字段】as "别名" from 【表名】;

                方式二：select 【字段】"别名" from 【表名】;

                方式三：select 【字段】别名 from 【表名】;

#示例：
#查询学生表中所有的学生姓名
-- 方式一
select sname as "学生姓名" from student;
-- 方式二
select sname "姓名" from student;
-- 方式三
select sname 名字 from student;

（5）去除重复的数据

                语法：

                单字段去重：select distinct【字段名】from 【表名】；

                多字段组合去重：select distinct【字段一，字段二】from 【表名】;

#示例：
-- 单字段去重
select sid,distinct sname from student;
-- 多字段组合去重
select sid,distinct(sname,classid) from student;

（6）带条件的查询

语法：select 【字段】 from 【表名】where 【条件】;

#示例：
#查询学生表中所有的女童鞋
select * from student where ssex = "女";

（7）多条件查询

语法：select 【字段】from 【表名】where 【条件一】and 【条件二】;

#示例：
#查询二班所有的女童鞋
select * from student where ssex = "女" and classid = 2;

（8）数值/范围性条件的查询

                语法：

        方式一：

        select 【字段】from 【表名】where 【条件一】> 【值】and 【条件二】< 【值】；

        方式二：

        select 【字段】from 【表名】where between 【条件一】and 【条件二】；

#示例：
#查询学号大于3且小于5的学生
-- 方式一
select * from student where sid > 3 and sid < 5;
-- 方式二（注意between为闭合区间，左闭右开）
select * from student where between 4 and 5;

（9）模糊查询

                -- like模糊：

                语法：select 【字段】from 【表名】where 【字段】like 【值】;

                模糊符号：%表示任意多个字符，%key(前模糊)，key%(后模糊)，%key%(前后模糊)

                                _表示一个字符，同上;

                -- in模糊：表示在某个特定范围内

                语法：select 【字段】from 【表名】where 【字段】in 【范围】;

                -- null查询：查询是否为空的字段

                语法：select 【字段】from 【表名】where 【字段】is null\is not null;

#示例：
#like模糊查询
#查询名字以'王'开头的学生
select * from student where sname like '王%';
#查询名字以'王'结尾的学生
select * from student where sname like '%王';
#查询名字中包含'王'的学生
select * from student where sname like '%王%';
#查询名字为三个字且第二个字为'雨'的学生
select * from student where sname like '_雨_';
#查询学号前3的学生(in后可以嵌套select查询语句)
select * from student where sid in (1,2,3);
#查询没有班级的学生
select * from student where classid is null;
#查询班级号不为空的学生
select * from student where classid is not null;

（10）聚合查询

                常用聚合函数：

                        -- count()：统计总个数，不会统计null

                        -- sum()：累加总和

                        -- max()：求最大值

                        -- min()：求最小值

                        -- avg()：求平均值

#示例：
-- 统计学生表有多少男同学
select count(*) from student where ssex = '男';
-- 查询1号同学的总成绩
select sum(score) from score where sid = 1;
-- 查询2号同学的最高分
select max(score) from score where sid = 2;
-- 查询3号课程的最低分
select min(score) from score where cid = 3;
-- 查询4号学生的平均分
select avg(score) from score where sid = 4;

（11）分组查询

分组查询是在聚合的基础上来进行的，使用group by指定要依据的分组字段，having 跟在group by后面指定查询条件，在分组查询中替换了where

语法：select 【聚合】from 【表名】group by【分组的字段】having【条件】

#示例：
#查询每个班都有多少人
select classid,count(*) from student group by classid;
#查询平均成绩高于60分的每个同学的学号和平均成绩
select sid,avg(score) from score group by sid having avg(score) > 60;

（12）排序查询

                使用order by对查询的结果进行排序

                        -- ASC：升序(mysql默认为升序)

                        -- DESC：降序

                可以多字段排序，先写的优先排，条件相同时根据下一个字段排序

                语法:

                -- select 【字段】from 【表名】where 【条件】order by 【字段】ASC\DESC;

#示例：
#查询所有的学生，根据学号降序排序
select * from student order by sid desc;
#查询所有的女同学，根据班级升序，学号降序排序
select * from student where ssex = '女' order by classid asc,sid desc;

2.高级查询

数据表中的每一条记录的每一个字段都为一个笛卡尔积，我们在进行多表查询时，如果不进行处理就会产生两表笛卡尔积总数之积的笛卡尔积，这样的数据通常对我们来说都很冗余，所以我们使用多表联查来解决这个问题。

（1）等值查询

语法：select 【字段】from 【表1】，【表2】where 【表1.字段】= 【表2.字段】;

#示例：
-- 查询所有的学生和所对应的班级信息
select * from student,class where student.classid = classid.classid;

（2）inner join内联查询

                内联查询会以表1为主表查询出表1的信息，再将表2中需要的信息拼接到表1

                查询到的是表1和表2共同的数据

                语法：

                select 【字段】from 【表1】inner join 【表2】on 【表1.字段】= 【表2.字段】;

#示例：
-- 查询所有的学生信息以及所对应的班级信息（没有班级的学生不会被查到）
select * from student inner join class on student.classid = class.classid;

（3）left join外联查询（左外联）

                左外联会以left左边的表为主表，查询出该表所有的信息，再将另一张表需要的信息拼接到该表（查询到的是左表的所有数据和右表中和左表有关系的数据）

                语法：

                select 【字段】from 【表1】left join 【表2】on 【表1.字段】= 【表2.字段】;

#示例：
-- 查询所有的学生信息以及所对应的班级信息（没有班级的学生也会被查到）
select * from student left join class on student.classid = class.classid;

（4）right join外联查询（右外联）

                左外联会以right右边的表为主表，查询出该表所有的信息，再将另一张表需要的信息拼接到该表（查询到的是右表的所有数据和左表中和右表有关系的数据）

                语法：

                select 【字段】from 【表1】right join 【表2】on 【表1.字段】= 【表2.字段】;

#示例：
-- 查询所有的班级信息和所对应的学生信息
select * from student right join class on student.classid = class.classid;

（5）union联合查询

                union可以查询两个集合的并集：

                        -- 数据类型不同也可以进行合并

                        -- 两个集合的列数要一样

                        -- 表头会变成第一个集合的信息（字段\别名）

                        -- union会自动去重

                使用：用在两个集合中间

#示例：
-- 查询没有班级的学生
select * from student left join class
on student.classid = class.classid 
where class.classid is null;
-- 查询没有学生的班级
select * from student right join class
on student.classid = class.classid
where student.sid is null;

-- union的使用：查询没有班级的学生和没有学生的班级
select * from student left join class
on student.classid = class.classid 
where class.classid is null;
union
select * from student right join class
on student.classid = class.classid
where student.sid is null;

（6）子查询（where）

where子查询是在where子句里面嵌套一个新的查询，这个嵌套可以多次嵌套。

#示例：
-- 查询学过java课的学生信息
select * from student where sid in (select sid from score where cid = 
(select cid from course where cname = 'java'));

（7）子查询（from）

                from子查询是在from后面嵌套一个新的查询，将该查询的结果当做新的表去查询其中的数据

                [注意]：所有的from子查询都需要给虚拟表起一个名字

                             格式：select * from (虚拟表) as 名字;

                             as 可以省略

#示例：
-- 查询学号大于2的女同学（使用from子查询）
select * from(select * from student where ssex = '女') as stu1 where sid > 2;

（8）exists子查询

exists子查询通常跟在where后面，exists子查询语句称为子句，前面的语句称为父句，当子句有数据时，父句执行，否则父句不执行。

语法：select * from 【表名】where exists(子查询语句);

#示例：
-- 如果学生表中有女同学，则查出所有的学生信息
select * from student where exists(select * from student where ssex = '女');

总结

        [查询语句的语法规则]：

        select [distinct] {*|表1.*|[表1.字段1[as 字段表名] [,表1.字段2[as 字段别名2]] [,...]]}

        from 表1 [as 表别名]

        [left|right|inner join 表2 on 表之间的关系]

        [where]

        [group by]

        [having]

        [order by]

        [limit { [ 位置偏移量, ] 行数 }];



        其中：

                ·"[ ]"包含的内容可以省略;

                ·"{ }"包含的内容必须存在;