查询以将值插入表 [英] query to insert values into table

问题描述

如果学生在一个文本框中输入自己的名字，然后从列表框中选择他拥有的许多科目并提交，
插入学生姓名和所选学科的表的设计是什么，学科根据学生的选择而有所不同，对此的查询是什么?

if a student enters his name in one textbox and selects many subjects he has from listbox and submits ,
what will be the design of table to insert student name and selected subjects, subjects vary according to student choice, and what will be the query for this ?

推荐答案

这不是我们在这里喜欢的问题.您必须使用所学的知识或必须学习的知识.
您在此处描述的内容至少需要树表，因此无法在一个插入语句(不是查询！)中执行，但可以将它们组合在一起.极简主义的设计可能是这样的:
Students表:

Well, this is not the kind of question we like here. You have to use what you have learned, or learn what you have to.
What you described here needs at least tree tables, thus can not be performed in one insert statement (not query!), but you can combine them. The minimalist design could be this one:
Students table:

CREATE TABLE [dbo].[students](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_students] PRIMARY KEY ([id] ASC)
)

Subjects表:

Subjects table:

CREATE TABLE [dbo].[subjects](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[title] [varchar](50) NOT NULL,
 CONSTRAINT [PK_subjects] PRIMARY KEY (	[id] ASC )
)

Application表格:

Application table:

CREATE TABLE [dbo].[application](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[student_id] [int] NOT NULL,
	[subject_id] [int] NOT NULL,
 CONSTRAINT [PK_application] PRIMARY KEY ( [id] ASC ),
 CONSTRAINT [FK_application_students] FOREIGN KEY([student_id]) REFERENCES [dbo].[students] ([id]),
 CONSTRAINT [FK_application_subjects] FOREIGN KEY([subject_id]) REFERENCES [dbo].[subjects] ([id])
)

现在，让我们先添加一些主题:

And now, let''s add some subjects first:

insert into subjects(title) values('Math');
insert into subjects(title) values('Chimie');
insert into subjects(title) values('English');

最后，让我们在一个查询中添加一个带有其应用程序的学生:

And finally, let''s add a student with it''s applications in a single query:

insert into students(name) values('John');
DECLARE @newid int;
SET @newid = (SELECT IDENT_CURRENT('students'));
insert into application(student_id,subject_id) values(@newid, 1);//Math
insert into application(student_id,subject_id) values(@newid, 3);//English

您需要以下表格；
学生-StudentId，名称-学生的主表
主题-主题ID，名称-主题的主表
StudentSubjects-Id，StudentId，SubjectId-映射表，用于了解映射到学生的科目

要检索与学生相关的所有学科信息，您必须加入以下所有上述三个表格；

You need following tables;
Student - StudentId, Name - Master table for students
Subject - SubjectId, Name - Master table for subjects
StudentSubjects - Id, StudentId, SubjectId - Mapping table to know subjects mapped to a student

For retrieving all the subject information related to a student you have to join all the above 3 tables as below;

SELECT SB.STUDENTID,S.NAME AS STUDENTNAME,SJ.NAME AS SUBJECTNAME FROM STUDENTSUBJECTS SB INNER JOIN STUDENT S ON SB.STUDENTID=S.STUDENTID
INNER JOIN SUBJECT SJ ON SB.SUBJECTID=SJ.SUBJECTID WHERE SB.STUDENTID=1

这篇关于查询以将值插入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！