上一篇我们介绍了MySQL的比较基础的增删改查,这里介绍增删查改的一些进阶用法,说是进阶用法,实际上,最主要的进阶部分是查询部分,新增主要是把查询的结果在作为值插入;而查询的部分包含聚合查询、联合查询(多表查询);不涉及修改和删除的进阶。
MySQL 增删查改(基础)_Naion的博客-CSDN博客https://blog.csdn.net/Naion/article/details/125809324?spm=1001.2014.3001.5501这里继续使用idea远程连接数据库来进行说明。idea如何远程连接请点击链接。
数据库约束
如图所示,现在我的想法是:能不能当没有给id赋值的时候,给他一个默认的值?或者要求只能插入一个张三。要实现这些功能,需要用到数据库的约束。
约束类型
(1)NOT NULL – 指示某列不能存储 NULL 值,当插入的值为null的时候,报错;
(2)UNIQUE – 保证某列的每行必须有唯一的值,不能出现重复的值;
(3)DEFAULT – 规定没有给列赋值时的默认值;
(4)PRIMARY KEY – NOT NULL 和 UNIQUE 的结合,确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
(5)FOREIGN KEY – 保证一个表中的数据匹配另一个表中的值的参照完整性。
(6)CHECK – 保证列中的值符合指定的条件。但是MySQL不支持check语句。
比如:
not null约束
unique约束
default默认值约束
primary key主键约束
主键是not null 和 unique 的结合,对于整数类型的主键,经常和auto_increment使用,这样就可以只插入name,id就会自动给你计算,每插入一个,数值加1。
foreign key 外键约束
foreign key (字段名) references 主表(列)
用于关联其他表的主键或唯一键。关联不是主键或唯一键就会报错。比如,学生有自己的班级,张三和李四是1班的,王五是2班的。
create table class(class_id int primary key auto_increment,class_name varchar(20) unique);insert into class(class_name)values ('mysql1班'), ('mysql2班');create table student(id int primary key auto_increment,name varchar(20) not null,class_id int, # 班级idforeign key (class_id) references class(class_id));insert into student(name, class_id)values ('张三', 1), ('李四', 1), ('王五', 2);select *from student;
如果我插入了三班,就会报错。
新增
把查询的结果作为值插入。
INSERT INTO table_name [(column [, column …])] SELECT …
比如,创建了student2表,把student的内容拷贝到student2表中。
聚合查询
基础的查询和条件查询是针对列和列之间的查询。要针对行和行之间进行插入,就要使用聚合查询。
聚合函数
比如查询student表的人数,使用count()函数。
group by子句
select column1, sum(column2), .. from table group by column1,column3;
使用group by子句对指定的列进行分组,注意的是使用group by进行分组查询时,select指定的字段必须是“分组依据字段”,其他字段若想出现在select中则必须包含在聚合函数中。比如查询两个班分别有多少人。
当然也可以在group by子句之前使用where语句,达到先筛选后分组的效果,不允许where语句放在group by子句后面,否则使用having语句。
having
where放在group by之前,是先筛选在分组;group by之后要进行筛选,不允许使用where,而是使用having。where子语句作用于没有经过分组的表和视图,having语句作用于经过分组的组。where语句不能包含聚合函数,而having里面经常使用聚合函数。
比如分组后查询人数大于1的班级。
当然可以同时使用where 和 having 在group by子句中。
联合查询(多表查询)
在实际的开发中,需要使用多张表进行联合查询,比如,联合查询学生对应的班级,这里不是查询对应的id,而是每一个学生对应的班级名。多表查询就是对多张表数据取笛卡尔积。
笛卡尔积,又叫cross join,是SQL中两表连接的一种方式。假如A表中的数据为m行,B表中的数据有n行,那么A和B做笛卡尔积,结果为m*n行。
通常我们都要在实际SQL中避免直接使用笛卡尔积,因为它会使“数据爆炸”,尤其是数据量很大的时候。但是巧妙使用,反而快速帮助我们解决问题。
内连接
select * from A,B;
select * from A [inner] join B on 连接条件 and 其他条件;
联合查询学生对应的班级,先进行笛卡尔积。
我们可以看见查询的结果是非常多的,这个时候,需要确定连接条件,过滤无效信息。观察就可以知道,student表中的class_id一定是和class_id中的值一一对应的关系,在student表中,1班当然对应class表中的1班,加上这个条件。
现在看起来这个表的数据就好多了。我们需要的是学生对应的班级,所以在筛选一下列。
上面的步骤可以一气呵成,但是刚开始的时候,就需要慢慢来。
使用带join的语句。
select class.class_name, student.namefrom classjoin java_104.studentwhere class.class_id = student.class_id;# on class.class_id = student.class_id; # 或者使用on来连接
两个语法都能达到目的,join还有特殊的用法。
外连接
外连接分为左外连接和右外连接。如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完全显示我们就说是右外连接。
— 左外连接,表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
— 右外连接,表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;
现在在class表中新增班级,student表中新增学生,但是没有班级。
然后看看左外连接和右外连接的区别,以及和内连接的区别。
insert into class(class_name)values ('mysql3班');insert into student(name)values ('王麻子');select class.class_name, student.namefrom class left join student on class.class_id = student.class_id;select class.class_name, student.namefrom class right join student on class.class_id = student.class_id;
他们的结果可以使用图来清晰的表示。
自连接
自己连接自己进行查询。这种查询是一种奇技淫巧,用来把行转换成列进行查询。查询的过程中需要对表起别名,否则报错。比如在同一张表中进行比较就需要用到这种查询。这里只写用法。
子查询
子查询是嵌入在其他sql语句中的select语句,也是叫嵌套查询,俗称套娃。
单行子查询只返回一行记录,多行子查询使用 in 关键字返货多条查询。比如,查询学生id大于2的学生 id 和姓名,这里不使用条件查询。
合并查询
在实际应用中,为了合并多个select的执行结果,可以使用集合操作符 union,union all。使用UNION 和UNION ALL时,前后查询的结果集中,字段需要一致。该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行。
union 查询的结果会自动去掉结果集中的重复行,但是union all不会。
查询 id 小于3或者学生姓名为”张三”的结果。结果和使用or条件查询结果一致。