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 = '张三';