Re0:从零开始的MySQL速成——Day3

多表查询 --一对多

首先依旧是新建一个查询
按住ctrl+S保存，取名DML02

#多表联合查询 -- 一对多

#前言 -- 创建表

#创建老师表，并添加数据
CREATE TABLE teacher(
		id INT(3) PRIMARY KEY auto_increment,
		name VARCHAR(32)
)
INSERT INTO teacher(name) VALUES('大老师');
INSERT INTO teacher(name) VALUES('小老师');
INSERT INTO teacher(name) VALUES('苍老师');

#创建学生表，并添加数据
CREATE TABLE student(
		id INT(3) PRIMARY KEY auto_increment,
		name VARCHAR(32),
		t_id INT(3)
)
INSERT INTO student(name,t_id) VALUES('樱小路露娜',1);
INSERT INTO student(name,t_id) VALUES('诗音',1);
INSERT INTO student(name,t_id) VALUES('明日香',1);
INSERT INTO student(name,t_id) VALUES('小红',2);
INSERT INTO student(name,t_id) VALUES('小绿',2);
INSERT INTO student(name) VALUES('小黄');

# 内连接---------------------------------------------------------
# 理解：查询出多张表之间有联系的数据

SELECT s.name,t.name FROM student s,teacher t WHERE s.t_id=t.id;

SELECT s.name,t.name FROM student s INNER JOIN teacher t ON s.t_id = t.id;

# 外连接---------------------------------------------------------

#左连接(针对于左边表查询数据)
SELECT s.name,t.name FROM student s LEFT JOIN teacher t ON s.t_id = t.id;

#右连接(针对于右边表的查询)
SELECT s.name,t.name FROM student s RIGHT JOIN teacher t ON s.t_id =t.id;

# 全连接--------------------------------------------------------

# 合并查询结果
SELECT s.name,t.name FROM student s LEFT JOIN teacher t ON s.t_id = t.id
UNION ALL
SELECT s.name,t.name FROM student s RIGHT JOIN teacher t ON s.t_id = t.id;

# 全连接
# 注意：MySQL中没有全连接的sql命令，使用合并查询+去重实现全连接的思想
SELECT s.name,t.name FROM student s LEFT JOIN teacher t ON s.t_id = t.id
UNION
SELECT s.name,t.name FROM student s RIGHT JOIN teacher t ON s.t_id = t.id;

创建表的时候发现表已存在，删除掉之前的表

多表联合查询 – 多对多

按住ctrl+S保存，取名DML03

# 多表联合查询 -- 多对多

# 前言 - 创建表

#创建学科表，并添加数据
CREATE TABLE course(
		id INT(3) PRIMARY KEY auto_increment,
		name VARCHAR(32)
)
INSERT INTO course(name) VALUES('语文');
INSERT INTO course(name) VALUES('数学');
INSERT INTO course(name) VALUES('英语');

#创建学生表，并添加数据
CREATE TABLE student(
		id INT(3) PRIMARY KEY auto_increment,
		name VARCHAR(32)
)
INSERT INTO student(name) VALUES('樱小路露娜');
INSERT INTO student(name) VALUES('诗音');
INSERT INTO student(name) VALUES('明日香');

#创建成绩表(中间表)，并添加数据
CREATE TABLE scores(
		id INT(3) PRIMARY KEY auto_increment,
		s_id INT(3),
		c_id INT(3),
		score FLOAT(4,1)
)
INSERT INTO scores(s_id,c_id,score) VALUES(1,1,99);
INSERT INTO scores(s_id,c_id,score) VALUES(1,2,91);
INSERT INTO scores(s_id,c_id,score) VALUES(1,3,87);
INSERT INTO scores(s_id,c_id,score) VALUES(2,1,91);
INSERT INTO scores(s_id,c_id,score) VALUES(2,2,100);
INSERT INTO scores(s_id,c_id,score) VALUES(2,3,89);
INSERT INTO scores(s_id,c_id,score) VALUES(3,1,87);
INSERT INTO scores(s_id,c_id,score) VALUES(3,2,99);
INSERT INTO scores(s_id,c_id,score) VALUES(3,3,100);

#需求；查询出学生姓名，学科名，成绩
SELECT stu.name,cou.name,sco.score FROM scores sco INNER JOIN course cou INNER JOIN student stu ON sco.s_id=stu.id AND sco.c_id=cou.id;

#查询出各个学生的平均成绩(学生姓名，平均成绩)
SELECT stu.name,AVG(sco.score) FROM scores sco INNER JOIN student stu ON sco.s_id=stu.id GROUP BY sco.s_id;

#需求：查询出各个学生最高成绩(学生姓名，最高成绩的学科，最高成绩)
SELECT stu.name,cou.name,sco.score FROM scores sco INNER JOIN(SELECT sco.s_id,max(sco.score)AS 'maxScore'FROM scores sco GROUP BY sco.s_id) xx INNER JOIN student stu INNER JOIN course cou ON sco.score =xx.maxScore AND sco.s_id =xx.s_id AND sco.s_id=stu.id AND sco.c_id=cou.id;
-- 樱小路露娜	语文	99.0
-- 诗音	数学	100.0
-- 明日香	英语	100.0

DCL

# DCL：开子账号，设置权限

# 创建子账号
create user 'hhy'@'localhost' identified by '123123';

# 设置权限
grant select,insert on learn.* to 'hhy'@'localhost';

# 删除权限
revoke insert on learn.* from 'hhy'@'localhost';

# 删除子账号
drop user 'hhy'@'localhost';