连接查询 一对多

2021-06-29 20:10发布

CREATE DATABASE day07 CHARSET utf8;

USE day07;


CREATE TABLE student(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(10)

);


CREATE TABLE teacher(

tid INT PRIMARY KEY,

tname VARCHAR(10)

);


INSERT INTO student VALUES(1,'张三'),(2,'李四'),(3,'王五'),(4,'赵六'),(5,'孙七');

INSERT INTO teacher VALUES(1,'张老师'),(2,'李老师'),(3,'王老师');


SELECT *FROM student,teacher;


-- 给teacher表添加外键

ALTER TABLE teacher ADD sid INT;


-- 给两个表加约束

ALTER TABLE teacher ADD CONSTRAINT fk_uo FOREIGN KEY(sid) REFERENCES student(sid);


-- 查询张三的老师

-- 通过张三查询到主键

SELECT sid FROM student WHERE sname = '张三';

SELECT tname FROM teacher WHERE sid = 1;


-- 连接查询

-- 隐式内连接 where 先连接两张表 连接条件外键

SELECT *FROM student,teacher WHERE student.sid = teacher.sid;

SELECT *FROM student s,teacher t WHERE s.sid = t.tid;


-- 查询张三的老师

SELECT *FROM student s,teacher t WHERE s.sid = t.sid AND sname = '张三';

SELECT t.tid,t.tname FROM teacher t, student s WHERE t.sid = s.sid AND sname = '张三';


-- 显示内连接

SELECT *FROM student s INNER JOIN teacher t ON t.sid = s.sid AND s.sname = '张三';


-- 左外连接

SELECT *FROM teacher t LEFT JOIN student s ON t.sid = s.sid AND s.sname = '张三';


-- 右外连接

SELECT *FROM teacher t RIGHT JOIN student s ON t.sid = s.sid AND s.sname = '张三';