MySQL表的增删改查2（进阶）

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 |
+----+-------+------------+-----------+