Java基础之MySQL（二）

一、分组查询

1、group by 分组字段1[，分组字段2，...][having 分组后的筛选条件]

2、注意：分组字段应该与select后的查询字段一致，否则查询结果无意义。

分组查询经常会用聚合函数一起使用

（一）先建一张商品表（product）

create table product(

pro_id int auto_increment primary key,

name varchar(20) not null,

price double not null,

number int not null,

type varchar(20) not null

);

（二）插入一些数据

insert into product(name,price,number,type)values('锅巴',2.5,30,'零食'),('洗面奶',30.5,50,'日用品'),

('铅笔',1.5,100,'文具'),('辣条',2,100,'零食'),('护肤霜',55,50,'日用品'),('牙刷',3.5,100,'日用品'),('笔记本',5.5,50,'文具'),

('LV包',1000,2,'奢侈品');

1、计算每一种商品的平均价格

select type as 商品种类,avg(price) as 平均价格 from product group by type;

2、计算日用品种类的平均价格

select type as 商品种类,avg(price) as 平均价格 from product group by type having type='日用品';

二、连接查询

创建两张表学校表（父表）和学生表（子表）

创建学校表(school)：

create table school(

school_id int auto_increment primary key,

school_name varchar(20) not null,

history text

);

创建学生表(student)：

create table student(

stu_id int auto_increment primary key,

stu_name varchar(20) not null,

score double,

student_school_id int,

foreign key(student_school_id) references school(school_id) on delete cascade on update cascade

);

insert into school(school_name,history)values('清华大学','清华大学是国内最好的大学'),('北京大学','北京大学历史悠久'),('西安交通大学','西安交通大学是陕西著名大学');

insert into student(stu_name,score,student_school_id)values('易小川',92.5,2),('萨摩耶',89,2),('童路',100,1);

1、等值连接

select 字段1,字段2,... from 表A,表B,... where 连接条件[其他过滤条件];

如查询学生的姓名、成绩与所在的学校及学校历史

select student.stu_name,student.score,school.school_name,school.history 

from student,school

where student.student_school_id=school.school_id;

2、内连接

select 字段1,字段2,...from 表A inner join 表B on 连接条件[其他过滤条件];

使用内连接的方式实现上述问题

select student.stu_name,student.score,school.school_name,school.history 

from student inner join school on student.student_school_id=school.school_id;

3、外连接

左外连接：

select 字段1,字段2,... from 表A left join 表B on 连接条件;

注意：左外连接的查询结果是，除了返回表中符合条件的记录外还要加上左表中剩下的全部记录。

如使用左外连接查询学生表与学校表的相关记录，学校表为左表

select student.stu_name,student.score,school.school_name,school.history 

from school left join student on school.school_id=student.student_school_id;

右外连接：

select 字段1,字段2,... from 表A right 表B on 连接条件;

注意：右外连接的查询结果是，除了返回表中符合条件的记录外还要加上右表中剩下的全部记录。

使用右外连接查询学生表与学校表的相关记录，学校表为右表

select student.stu_name,student.score,school.school_name,school.history

from student right join school on student.student_school_id=school.school_id; 

补充：子查询（嵌套查询）

子查询是指在外部查询语句中嵌套着一个内部查询（子查询），子查询的结果一般都是作为外部查询的条件。

子查询要用括号括起来。

子查询的常用关键字：

in 判断某个（些）字段值是否在指定的子查询结果集中

all 判断某个字段值是否满足指定操作符对子查询结果集所有记录的筛选

any 判断某个字段值是否满足指定操作符对子查询结果集的任一个值

三、外键

外键的定义：

[constraint 约束名称] foreign key(作为外键的字段名) references 父表名称(父表主键);

外键的作用：用来约束子表中的记录，使之与父表的记录相对应。

四、“一对多”关系表设计

通过在子表上设置外键的方式，关联父表的主键。这样，父表的一条记录就可以关联子表的多条记录。

eg：学校表(school)与学生表(student)。学校表(school)中的一条记录就可以对应学生表(student)中的多条记录；

学生表(studet)只能对应学校表(school)中的一条记录。

“多对多”关系表设计

通过设置中间表的方式实现“多对多”关系表的设计。中间表至少有两个外键字段，分别关联于两张“多”表的主键，

而且这两个外键字段的组合值不能重复（一种方式是通过将这两个字段设置为联合主键，以确保它们的组合值不重复）。

创建学生表(student)、科目表(subject)和中间表(student_subject)

学生表

create table student(

stu_id int auto_increment primary key,

stu_name varchar(20) not null,

score double

);

科目表

create table subject(

sub_id int auto_increment primary key,

sub_name varchar(20) not null

);

中间表

create table student_subject(

student_id int,

subject_id int,

foreign key(student_id) references student(stu_id),

foreign key(subject_id) references subject(sub_id),

primary key(student_id,subject_id)

);

为学生表插入数据

insert into student(stu_name,score)values('小白',62),('大白',63),('陌陌',80);

为科目表插入数据

insert into subject(sub_name)values('语文'),('数学'),('英语'),('物理');

为中间表插入数据

insert into student_subject(student_id,subject_id)values(1,1),(1,3),(2,3),(2,4),(3,3);

学号为2的学生选择了哪些课程？

第一步：在中间表中查询2号学生选择的课程号

select subject_id from student_subject where student_id=2;

第二步：在课程表中根据课程号查询相应的课程名

select sub_name from subject where sub_id in (

select subject_id from student_subject where student_id=2);

查询学号为2的学生和其选的课程

方式一：

select stu_name,sub_name from student,subject where sub_id in

(select subject_id from student_subject where student_id=2) and stu_id=2;

方式二：

select stu_name,sub_name from student,subject,student_subject

where stu_id=2 and student.stu_id=student_subject.student_id and subject.sub_id=student_subject.subject_id;