查询的多种方法
- 数据库的约束类型
- 表的设计
- 新增
- 查询
- 聚合查询
- 联合查询
数据库的约束类型
约束类型:
在数据库中一共有5中约束类型,其中分别如下:
1.not null类型:
功能:指某列不能出现null类型:
例如:创建上表,此时我们添加一个id为null的值就会出错。
2.unique约束:
功能:唯一约束,对列使用后,这列的数据就是唯一的,不能重复。
例如:
我们创建以上表,并对其加入id相同的数据,如下:
那么就会出错。
3.default约束:
功能:设置默认值,当一个数据插入是为空时,可以设置其为默认值,而不是空。
例如:
创建如上表后,其默认值直接变为0。
4.primary key约束:
功能:主键,约束的列不能有重复的数据,且不能为空(相当于not null约束和unique约束的结合)
并且其一般情况下与auto_increment来共同使用。
auto_increment:自增功能,不给值的时候会使用最大值+1。
注意:一个表中,只能有一个主键。
例如:
如上表。5.foreign key约束
①:功能:用于关联其他表的主键或者唯一键。
②:语法:foreign key (字段名) references 主表(列);
具体用法如下:
创建班级表:
创建学生版,并使用班级表的id主键
结果如下:
表的设计
表的设计一共有三大常用范式,分别为以下三种:
①:一对一,例如:
一个人只有一张身份证。
②:一对多,例如:
一个班级有多个学生。
③:多对多:
例如:其中创建一个课程表,将学生和班级联系起来如下:
新增
功能:查询插入结果
语法:insert into table_name(列名) select ....
其中:
①:table_name为需要插入的表名。
②:列明为插入的是那几列。
③:select为从哪里去找。
④:…为找的地方。
例如:
创建以下用户表:
然后通过学生表将学生的name和id插入,如下:
查询
聚合查询
1.聚合函数:
比如求总数,平均值都可以用聚合函数来进行操作,如表为常用的聚合函数:
函数 | 说明 |
count | 返回查询到的数据的总数 |
sum | 返回查询到数据的总和 |
avg | 返回查询到数据总和的平均数 |
max | 返回查询到数据的最大值 |
min | 返回查询到数据的最小值 |
①:count
例如,统计学生表中的总数:
②:sum
例如,统计学生年龄总数:
③:avg
例如,查询返回来数据的平均值:
④:max
例如,查询返回来数据的最大值:
⑤:min
例如,查询返回来数据的最小值:
2.group by语句
功能:select可以通过group by 语句对指定的列进行分组查询。
前提:select指定的字段必须是“分组依据字段”,其他字段如果想要出现在select中则必须包含在聚合函数中。
语法:select column1,sum(column2).......from table_name grop by column1....;
其中:
①:column1:表示列名。
②:table_name:表示表名。
如下图,创建如下表:
查询每个角色的最高工资、最低工资和平均工资。
如下:
3.having语句
功能:GROUP BY 子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用 WHERE 语句,而需要用 HAVING。
例如,显示平均工资低于1500的角色和它的平均工资:
联合查询
联合查询是针对多表进行的,需要对这多个表进行取笛卡尔积,这样就可以将多个表链接起来进程联合查询。
1.笛卡尔积:
就是将两张表联系为一张表,如下图:
假设a表对b表是一对多,那么就如上图所示,对a表和b表取笛卡尔积就得到其下面的那张表。
注意:在多表查询的时候,可以使用别名。
首先我们先创建以下表,作为使用:
2.内连接:
①:语法格式:
语法①:select 字段 表1 别名1 [inner] join 表2 别名2 on 连接条件=其他条件;
语法②:select 字段 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
注意:
①:任何一个语法都可以使用,并且别名可以用也可以不用,看个人是喜好。
②:[]中的inner可加可不加。
②:具体操作:
(1):例如:查询许仙同学的成绩:
(2):例如:查询所有同学的成绩,及同学的个人信息。
(3):例如:查询所有同学的成绩,及同学的个人信息。
注意:用了别名之后就必须所有的名字都得用别名。
2.外连接
①概念:外连接分为左外连接和右外连接。
如果联合查询,左侧的表完全显示,那么我们就称之为左外连接。
如果右侧的表完全显示,那么我们就称之为右外连接。
②语法:
左连接:select 字段名 from 表名1 left join 表名2 on 连接条件;
右连接:select 字段名 from 表名1 right join 表名2 on 连接条件;
例如:查询所有同学的成绩,及同学的信息,如果同学没有成绩,也需要显示。
左连接如下:
最后一行的学生没有成绩,但是还是显示出来了。
再看右连接:
由于是右连接,所以有分数的会被显示,则左连接的最后一位同学不见了。3.自连接:
①:概念:是指在同一张表上,对自己进行连接。
注意:这个语句的使用需要别名。
例如:显示所有“计算机原理”成绩比“Java”成绩高的成绩信息
4.子查询
①:概念:也叫嵌套查询。
- 单行子查询:返回一行记录的子查询
例如:查询与‘不想毕业’的同学的同班同学: - 多行子查询,返回多条记录的子查询
例如:查询“语文”或“英文”课程的成绩信息:
in与not in语句:
in语句:
not in语句: - 在from子句中使用子查询,子查询语句出现在from子句中。这里要用到数据查询的技巧,把一个子查询当做一个临时表使用。
例如,查询所有比“中文系”平均分高的成绩信息:
5.合并查询
①:概念:合并多个select的结果
②:操作符:union,union all
③:注意:使用操作符时,前提的结果集中,字段相同。
- union
说明: 该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行。
例如:查询id小于3,或者名字为“英文”的课程: - 并且union可以用or来实现,对于上面操作的语句,相应的or语句为:
select * from coures where id<3 or name = '英文';
- union all
说明:该操作符用于取得两个结果集的并集。当使用该操作符时,不会自动去掉结果集中的重复行。
例如:查询id小于3,或者名字为“Java”的课程