我亲爱的爸爸 钢琴正谱:求带外键约束的查询sql server语句

来源:百度文库 编辑:神马品牌网 时间:2024/03/29 10:15:57
classes_def的字段manager_id与teacher_def的id相关联,现在想根据manager_id查询出classes_def.name,state,teacher.name
//教师
CREATE TABLE teacher_def(
id INT PRIMARY KEY IDENTITY(1,1),
name VARCHAR(20),
password VARCHAR(20),
UNIQUE(name,password))

//班级
CREATE TABLE classes_def(
id INT PRIMARY KEY IDENTITY(1,1),
name VARCHAR(10) NOT NULL UNIQUE,
state VARCHAR(10) DEFAULT '未开课' NOT NULL,
manager_id int,
FOREIGN KEY(manager_id) REFERENCES teacher_def(id))

select t.name,c.name,c.state from classes_def c left join teacher_def t on c.manager_id=t.id where t.id=?