create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11,2)
);
insert into emp(name,role,salary) values
('马云','服务员',1000.20),
('马化腾','游戏陪玩',2000.99),
('孙悟空','游戏角色',999.11),
('猪八戒','游戏角色',333.5),
('沙和尚','游戏角色',700.33),
('隔壁老王','董事长',12000.66);
细细体会这三条指令
select role from emp;
select role,max(salary)from emp group by role;
select role,max(salary),min(salary) from emp group by role;
联合查询
实际开发中往往数据来自不同的表,所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积。
表1
a1 | a2 |
---|---|
a | aa |
b | bb |
表2
b1 | b2 |
---|---|
1 | 11 |
2 | 22 |
笛卡尔积后
a | aa | 1 | 11 |
---|---|---|---|
a | aa | 2 | 22 |
b | bb | 1 | 11 |
b | bb | 2 | 22 |
用代码来表示,大概是这个意思,for循环嵌套操作
class A{
private B b;
}
class B{
}
List<A> as;
List<B> bs;
for(A a:as){
for(B b:bs){
}
}
为了更好的说明,我们插入这么四个表
create table classes(
id int primary key auto_increment,
name varchar(20),
dest varchar(100)
);
insert into classes(name,dest) values
('机械172','学习了机械设计'),
('bite63','学习了C语言'),
('物流174','学习了高数');
================================================================
drop table if exists student;
create table student(
id int primary key auto_increment,
sn int unique,
name varchar(20) default 'unkown',
qq_mail varchar(20),
classes_id int,
foreign key(classes_id) references classes(id)
);
insert into student(sn,name,qq_mail,classes_id) values
('001','张三','zhangsan@qq.com',1),
('002','李四','lisi@qq.com',1),
('003','王五','wangwu@qq.com',1),
('004','赵六','null',2),
('005','陈七','chenqi@qq.com',3);
========================
drop table if exists course;
create table course(
id int primary key auto_increment,
name varchar(20)
);
insert into course(name) values
('C语言'),
('中国传统文化'),
('机械设计'),
('高数'),
('Java'),
('计算机原理');
=================
drop table if exists score;
create table score(
id int primary key auto_increment,
score decimal(3,1),
student_id int,
course_id int,
foreign key (student_id) references student(id),
foreign key (course_id) references course(id)
);
insert into score(score,student_id,course_id) values
--张三
(70.5,1,1),(98.5,1,3),(33,1,5),(98,1,6),
--李四
(60,2,1),(59.5,2,5),
--王五
(33,3,1),(68,3,3),(99,3,5),
--赵六
(67,4,1),(70.5,4,3),(56,4,5),(72,4,6),
--陈七
(81,5,1),(37,5,5);
1.内连接
select 字段 from 表1 别名1[inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
这里的连接条件就是主外键的连接。
案例1:查询张三同学的成绩(学生成绩关联)
select sco.score from student stu inner join score sco on stu.id=sco.student_id
and stu.name='张三';
select sco.score from student stu ,score sco where stu.id = sco.student_id
and stu.name = '张三';
+-------+
| score |
+-------+
| 70.5 |
| 98.5 |
| 33.0 |
| 98.0 |
+-------+
打印所有同学的成绩
select student.name,score.score,score.course_id
from student,score where student.id=score.student_id;
+--------+-------+-----------+
| name | score | course_id |
+--------+-------+-----------+
| 张三 | 70.5 | 1 |
| 张三 | 98.5 | 3 |
| 张三 | 33.0 | 5 |
| 张三 | 98.0 | 6 |
| 李四 | 60.0 | 1 |
| 李四 | 59.5 | 5 |
| 王五 | 33.0 | 1 |
| 王五 | 68.0 | 3 |
| 王五 | 99.0 | 5 |
| 赵六 | 67.0 | 1 |
| 赵六 | 70.5 | 3 |
| 赵六 | 56.0 | 5 |
| 赵六 | 72.0 | 6 |
| 陈七 | 81.0 | 1 |
| 陈七 | 37.0 | 5 |
+--------+-------+-----------+
案例2:查询c语言成绩大于60的信息(成绩课程关联)
select * from score sco ,course cou
where sco.course_id=cou.id and cou.name = 'c语言' and sco.score > 60;
--或者
select * from score sco join course cou
on sco.course_id=cou.id
where cou.name = 'c语言' and sco.score > 60;
案例三,将三张表关联起来
select stu.name,cou.name,sco.score
from score sco , course cou,student stu
where sco.course_id = cou.id and stu.id = sco.student_id;
+--------+-----------------+-------+
| name | name | score |
+--------+-----------------+-------+
| 张三 | C语言 | 70.5 |
| 张三 | 机械设计 | 98.5 |
| 张三 | Java | 33.0 |
| 张三 | 计算机原理 | 98.0 |
| 李四 | C语言 | 60.0 |
| 李四 | Java | 59.5 |
| 王五 | C语言 | 33.0 |
| 王五 | 机械设计 | 68.0 |
| 王五 | Java | 99.0 |
| 赵六 | C语言 | 67.0 |
| 赵六 | 机械设计 | 70.5 |
| 赵六 | Java | 56.0 |
| 赵六 | 计算机原理 | 72.0 |
| 陈七 | C语言 | 81.0 |
| 陈七 | Java | 37.0 |
+--------+-----------------+-------+
我们看到表头的名好像重复了,我们可以这样在查询时这样修改一下
select stu.name student_name,cou.name course_name,sco.score
from score sco , course cou,student stu
where sco.course_id = cou.id and stu.id = sco.student_id;
外连接
外连接分为左外连接和右外连接。如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完全显示我们就说是右外连接;
语法
–左外连接,表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;–右外连接,表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;
案例:查询所有同学的成绩及同学的个人信息,如果该同学没有成绩,也需要展示
--左外连接
select * from student stu left join score sco on stu.id=sco.student_id;
--右外连接
select * from student stu right join score sco on stu.id=sco.student_id;
自连接(不同行同一列)
自连接是指在同一张表连接自身进行查询。
案例:
显示所有“计算机原理”成绩比“Java”成绩高的成绩信息。
--先查询“计算机原理”和“Java”课程的id。
select id,name from course where name='Java' or name='计算机原理';
--再查询成绩表中,''计算机原理”成绩比“Java”成绩好的信息。
select s1.*
from score s1,score s2
where s1.student_id = s2.student_id
and s1.score < s2.score
and s1.course_id = 5
and s2.course_id = 6;
--也可以用join on 语句来进行自连接查询
结果:
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 3 | 33.0 | 1 | 5 |
| 12 | 56.0 | 4 | 5 |
+----+-------+------------+-----------+