1.先建三个表:
老师表和学生表:
CREATE TABLE student(
sid int primary key,
NAME VARCHAR(6) not null,
sex enum('男','女') not null DEFAULT '男');
CREATE TABLE teacher(
tid int primary key,
NAME VARCHAR(6) not null,
sex enum('男','女') not null DEFAULT '男');
老师教哪个学生的表:
CREATE TABLE dno(
`tid` int(10) NOT NULL,
`sid` int(10) NOT NULL,
CONSTRAINT `f_sid` FOREIGN KEY (`sid`) REFERENCES `ooo`.`student` (`sid`),
CONSTRAINT `f_tid` FOREIGN KEY (`tid`) REFERENCES `ooo`.`teacher` (`tid`)
);
2.多表查询:SELECT * from student,teacher where tid=11;
可以不加条件。他这个是笛卡尔积。我反正觉得特别扭,不想用。
自身链接查询说一个:
SELECT * FROM student t1, student t2 where t1.sid=t2.sid;
给表起了别名t1和t2,加了这个条件后一列没有重复的哈哈哈哈哈
3.外链接查询
把学生表和授课关系表链接,查学生老师是谁
SELECT * FROM student INNER JOIN dno on student.sid=dno.sid;
左连接:
SELECT * FROM student left JOIN dno on student.sid=dno.sid;
右连接同理,把left换成right。
三张表相连(把老师信息也打出):
SELECT * FROM student left JOIN dno on student.sid=dno.sid
left join teacher on dno.tid = teacher.tid;
4.嵌套查询
一个老师教那些学生:
SELECT * FROM student where sid =(SELECT sid FROM dno WHERE tid in (SELECT tid FROM teacher where name ='十二'));
数据库的这些内容是笔记哈,跟着教学视频总结的。