mysql-023.增删查改进阶-表的设计,查询进阶
数据库中的注释:
第一种注释--后至少要有一个空格
目录
数据库中的注释:
一.表的设计
一对一:
一对多
多对多
二.查询
1.查询搭配插入使用
2.聚合查询
count:返回查询到数据的数量
sum: 返回查询数据的总和
avg:计算平均值
max:计算最大值
min:计算最小值
3.group by：分组查询
4.多表查询/联合查询
5.外连接
6.自连接
7.子查询
8.合并查询
一.表的设计
在实际应用中,需要知道每张表的结构,表与表之间存在的关系
关系有一下四种:
一对一:
一个学生只有一个学号,一个学号也只属于一个学生
(一对一的表可以合并成一张表)
一对多
一个学生只属于一个班级,一个班级包含很多学生
(一般将学生表保存多条记录,班级表保存单一记录)
多对多
一个学生要学习很多门课,一课科目也有很多学生要修.
(可以通过第三张表,将两张表联系起来)
除了这三种,其余的都是没关系.
这四种结构,是数据库设计表结构的一种固定套路.
二.查询
1.查询搭配插入使用
创建两个user表,字段相同,
向user1表中插入三条记录,并将user1中的数据插入到user2中:
查看插入结果:
注意: 这个操作要满足 查询出来的列/类型 要和被插入的表的列一样.
2.聚合查询
表达式查询是针对列和列之间的运算,聚合查询是对行和行之间的运算.
常用的聚合函数:
创建学生成绩单表，id为主键
count:返回查询到数据的数量
若查询列有null值，该记录不会被计算到count中
sum: 返回查询数据的总和
avg:计算平均值
max:计算最大值
min:计算最小值
这些函数只针对能转换成double类型的数据，不能转换的数据无法计算。
对name求和，返回了0.
指定具体的列，可以进行去重
name列共有4条非空数据，其中两条数据相同，共有3条唯一数据。
3.group by：分组查询
select column1, sum(column2), .. from table group by column1,column3;
使用group by 进行分组，再对每个分组进行聚合查询
当前表中的数据：
对每个人的成绩求和：
group by 还可以搭配条件使用,要清楚条件是在分组之前,还是分组之后,
在分组之前设置条件,用where
select 列名,sum(列名) from 表名 where 条件 group by 列名;
分组之后设置条件,用having
select 列名,sum(列名) from 表名 group by 列名 having 条件;
查询每个人的平均分,除了张三的:
分组之前,设置条件
分组之后,设置条件:
这个查询,在分组前后设置条件,结果都是一样的,对于某些查询需要明确条件的位置,
查询每个人`的平均成绩,但除去平均成绩小于60的:
这个查询就要在分组之后,再设置条件.
也可以在分组前后同时设置条件:
4.多表查询/联合查询
多表查询是对多张表的数据取笛卡尔积。
查询之后新表的行数是原两张表的行数之积,列数是原两张表的列数之和.
创建两张表: student, class
有如下数据:
计算两张表的笛卡尔积:
这里面有很多记录是不合法的,筛选合法的记录:
再对需要显示的列进行查找:
就找到了每个同学的班级名称.
创建student表,class表,couse表,score表
查询zhangsan的成绩:
1.先对student表和score表进行笛卡尔积,并筛选出合法的记录
2.再加上条件:name=zhangsan
3.对列进行筛选,保留需要的列:
查询同学的总成绩和个人信息:
1.对student和score进行笛卡尔积额,并筛选合法记录:
2.按student.name进行分组.求每个同学的总成绩
5.外连接
上面的查询都属于内连接,
当两张表中的记录都是对应关系,内连接和外连接查询结果是一样的,若不是对应关系,则内连接和外连接的查询结果是不同的.
内连接:
select 列名 from 表1名 inner join 表2名 where 条件;
inner join可以用逗号,代替;inner 也可以不写.
- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
左外连接就是以左表为主表,显示左表的全部记录,当右表中没有对应的记录时,用NULL填充.
-- 右外连接，表2完全显示
select 字段名 from 表名1 right join 表名2 on 连接条件;
右外连接就是以右表为主表,显示右表的全部记录,当左表中没有对应的记录时,用NULL填充.
6.自连接
自连接 : 一张表,自己和自己连接.
查询语文成绩比数学成绩高的同学:
1.将score表进行笛卡尔积
这里注意要将表取别名,否则会报错
2.筛选出合法的记录:两表合并的记录要为同一个学生:
3.查询语文成绩和数学成绩对应的课程id
筛选s1表为语文成绩 id=3,s2表为数学成绩 id=4:
4.再筛选出s1表的语文成绩 比s2表的数学成绩高的同学:
在第三步中,看到s1表的成绩都小于s2表中的成绩,所以在这一步的查询中,结果为空.
7.子查询
子查询就是将多个简单的查询语句合并成一个复杂的查询语句.
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
查询和zhangsan是同一个班级的同学:
在结果中去除zhangsan:
这里的查询条件 classid 结果是一条数据,可以用 = ,若结果为多条数据,可以用 in /between and/> /<... ,都能使用.
8.合并查询
将多个查询的结果集合并到一起
使用合并查询时,两表/多表查询的字段的个数和类型要一致(名字可以不同).
union:合并时会将重复的行删除
查询id<5 或者classid<5的学生信息:
union all:合并时,会显示所有的行:
