【数据库增删查改进阶版】保姆级教程带大家去学习更加复杂的sql语句,各种各样的约束以及各种各样的查询

前言：
大家好，我是良辰丫🍅🍅🍅，上一篇数据库我们一起学习了基础版本的增删查改，今天我们将接触更高级的增删查改，主要是学习一些约束条件，你们准备好了嘛？开始发车了。👒👒👒

🧑个人主页：良辰针不戳
📖所属专栏：数据库
🍎励志语句：生活也许会让我们遍体鳞伤，但最终这些伤口会成为我们一辈子的财富。
💦期待大家三连，关注，点赞，收藏。
💌作者能力有限，可能也会出错，欢迎大家指正。
💞愿与君为伴，共探Java汪洋大海。

---

---

1、数据库约束

所谓约束就是在使用数据库的时候对数据库中存储的数据提出一定的要求和限制。下面是一个约束条件表，先简单看一下，后面会有详细解释。

约束条件	解释
NOT NULL	指示某列不能存储 NULL 值
UNIQUE	保证某列的每行必须有唯一的值
DEFAULT	规定没有给列赋值时的默认值
PRIMARY KEY	主键，就像身份证一样，唯一标识
FOREIGN KEY	外键，保证一个表中的数据匹配另一个表中的值的参照完整性
CHECK	保证列中的值符合指定的条件

1.1 NULL约束

创建表时，指定某个属性的性质，表名该属性不可以为空。下面我们来举一个简单的例子.

create table stu(id varchar(10) not null,name varchar(20));

上面的代码中我们创建了一个表,表的属性具有了约束条件,不能为空,如果我们填入的属性为null时就会报错.

我们来看一下我们创建的表结构.

1.2 unique约束

保证每列的每行具有唯一属性.

create table stu2 (id int unique,name varchar(20));

此时stu2的id属性只能唯一,当我们创建相同的id时,就会报错.

1.3 default设置默认值

规定没有给列赋值时的默认值

 create table stu3 (id int,name varchar(20) default '未知');insert into(id) stu3 values(5);

1.4 主键约束

唯一标识,象征你的唯一性,比如你的身份证,在学校你的学号…这些都是唯一标识

create table stu4 (id int primary key,name varchar(20));
insert into stu4 values(1,'叶良辰');
insert into stu4 values(1,'张三');

插入一条id为1的数据时候,它会正常插入,然而呢,第二次,插入id仍然为1的数据时,就会报错,这下大家明白主键的基本作用了吧.这时就引入了自增主键的概念.

1.5 自增主键

给自增主键插入数据的时候,我们可以自己指定一个主键,也可以插入一个空值,让他自己分配.

 create table stu5 (id int primary key auto_increment,name varchar(20));insert into stu5 values(null,'叶良辰'),(null,'李四');

我们指定的id为null,但是它默认给我们赋值,这就是自增主键.

1.6 外键

保证一个表中的数据匹配另一个表中的值的参照完整性,简而言之,就是父表与子表的约束关系.我们需要创建两张表来说明其中的联系.

create table class (classID varchar(20) primary key);

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

需要注意的是,父表中被依赖的属性必须是主键

insert into class values('一班');

在拥有一班的情况下会正常插入数据.

上述插入的数据是1班,class中没有1班的数据,所以会报错
注意
一班和1班不一样,一个是汉字一,一个是数字1哦.

父表在约束子表的同时,子表也在约束父表.

drop table class;

因为子表约束父表,因此上面的删除父表操作会失败.

删除一班这一行的数据也会报错.

2、表的设计

所谓表的设计,简单粗暴的理解就是如何去弄一个数据库,各个表进行联系等.数据库中,有几个表,每个表有怎样的属性,每个表的作用是什么等.
实体:在java中面向对象是找对象,在数据库中,面向对象是找实体,比如一个学生是一个实体,一个老师也是一个实体.

明确三种关系

• 一对一:在学校中,一个学生只能有一个学号.
• 一对多:一个班级中可以包含多个学生.
• 多对多:一个学生可以选择多个课程,一个课程也可以提供给多个学生.

友情提示:
在设计表之前,一般需要画简图,去描述各个表的功能以及联系.

3、查询结果作为新增数据

insert into stu select * from stu;

4、进阶版查询

4.1 聚合查询

查询过程中,表的行与行进行一定的运算,聚合查询依赖于聚合函数.

4.1.1 聚合函数

函数	说明
count	数据的数量
sum	数据总和(非数据没有意义)
avg	平均值(非数据没有意义)
max	最大值(非数据没有意义)
min	最小值(非数据没有意义)

create table stu (id int,name varchar(20));
insert into stu values(1,'张三'),(2,'李四');
select count(id) from stu;

由于聚合函数方法比较简单,我只列举了一个例子,大家可以自己尝试写一下,熟能生巧,不能眼高手低哦.

聚合函数可以与表达式进行连接哦!!!

select sum(chinese + math) from student;

4.1.2 group by子句

• 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT中则必须包含在聚合函数中.
• group by指定一个列,就会把相同的属性放到一个组中.

下面创建了一个工资表

create table salary(id int,name varchar(20),role varchar(20),salary numeric(10,2));
insert into salary values(1,'张三','老板',50000),
(2,'李四','员工',3000),
(3,'王五','员工',3500),
(4,'赵六','厨师',8000),
(5,'刘大宝','厨师',7000);

查询该公司各个职业的平均工资

4.1.3 having

接下来,我们需要搞清楚,分组的条件是分组前还是分组后.

• where是分组之前的
• having是分组之后的

使用having进行条件判定

同时使用having和where进行条件判定

4.2 联合查询(多表查询)

把多个表联合在一起进行查询,在学习联合查询之前,我们需要了解笛卡尔积,联合查询就行基于笛卡尔积.(其实就是所谓的组合)

4.2.1 了解联合查询

1. 商品编号表

name	id
钢笔	1
圆珠笔	2

2. 商品价格表

id	price
1	15
2	8

3. 两表进行笛卡尔积

name	id	id	price
钢笔	1	1	15
钢笔	1	2	8
圆珠笔	2	1	15
圆珠笔	2	2	8

大家可以清楚的看到,笛卡尔积之后会产生大量无效数据,那么,什么是无效数据呢?比如上面钢笔,第三张表中,第二个钢笔的id不同,这时它就是无效数据,下图红色圈住的都是无效数据.

因此呢,我们使用联合查询将多表联系在一起的时候需要使用一定的条件去掉无效数据.
笛卡尔积 + 条件 = 联合查询

 create table goods(name varchar(20),id int);create table price(id int,price int);insert into goods values('钢笔',1),('圆珠笔',2);insert into price values(1,15),(2,8);

笛卡尔积的结果

联合查询的结果

join…on也可以进行笛卡尔积运算

4.2.2 内连接

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
其实上述的普通笛卡尔积运算就是内连接
如果是三张表,两两合并,前两个合并在与第三张表合并.

4.2.3 外连接

左外连接,以左边表作为参考,如果左边表没有的属性,连接后将不再显示.

右外连接,以右边表作为参考,如果右边表没有的属性,连接后将不再显示.

4.2.4 自连接

自己和自己做笛卡尔积,把行转成列进行一系列的操作.

下面会报错,因为自连接需要指定一下表的别名(规定)

指定别名后就可以正常输出

4.2.5 子查询

其实就是套娃操作,把多个查询语句合在一起.

1. 单行子查询

select name from goods where id = (select id from price where price = 8);

2. 多行子查询

select * from goods where id in (select id from price where price = 8 or price = 15);

下面查找id为1和2的商品

下面为多行子查询

需要注意的是,多行子查询需要关键词in,in后面加条件.

exists关键字也可以多行子查询(了解一下)

4.2.6 合并查询

所谓合并查询,就是把两个查询的语句合并在一起,通过关键字union来实现.

1. 通过or查询两条语句

2. 通过union来合并查询两条语句

注意:
union不会去重,union all会自动去重

后序:
今天的数据库学习就到这里了,期待大家的三连,希望小小的文章可以帮助到大家,我会不断努力,去更新内容,真诚希望与大家一起进步.💞💞💞