针对多个表的 MySQL/SQL 查询

Question

我有多个表，我正在尝试通过他们的 ID "studentID" 进行搜索。当我运行下面的查询时，MySQL或SQL Server冻结(加载...(。有什么建议吗？并非所有表都列出。谢谢。

表：

学生

•   studentsID  Int Pkey
•   Fname   string
•   Lname   string …    
•   studentsGender  Int Fkey
•   studentsGender  int Fkey
•   studentsRezAddressID    int Fkey
•   studentsBirthID int Fkey
•   studentsParentsID   int Fkey

student_address：

•   RezAddressID    Int Pkey
•   RezStreet   Varchar
•   RezCity Int Fkey
•   RezState    int Fkey
•   RezDistrict int Fkey
•   RezCountry  int Fkey
•   RezZipcode  int

student_edu：

•   eduID   Int Pkey
•   eduAcadYear Int Fkey
•   eduAdmitionTest Varchar
•   scholarship Varchar
•   study   Int Fkey
•   classroom   Int Fkey
•   studentStatus   Int Fkey
•   testStatus  Int Fkey
•   additionalInfo  Varcha

r

Academic_years

academicID  Int Pkey
academicYear    VarChar

城市

cityID  Int Pkey
city    VarChar

国家

countryID   Int Pkey
country VarChar

国家

districtsID Int Pkey
district    VarChar

种族

ethnicityID Int Pkey
ethnicity   VarChar

毕业生

gradID  Int Pkey
grad    VarChar
SELECT
students.studentsID,
students.studentsLname,
students.studentsMname,
students.studentsFname,
students.studentsMobile,
students.studentEmail,
students.joined_date,
genders.gender,
ethnicities.ethnicity,
races.races,
student_address.RezStreet,
student_address.RezZipcode,
grads.grad,
rooms.rooms,
studies.study,
student_edu.additionalInfo,
student_status.student_status,
academic_years.academicYear,
teststatus.testStatus,
student_edu.scholarship,
student_birth_place.birthDate,
cities.city,
states.states,
countries.country,
districts.district   
FROM
(
students,
student_edu,
student_address,
studies,
rooms,
student_status,
teststatus,
academic_years,
grads,
races,
genders,
ethnicities,
student_birth_place,
districts,
cities,
states,
countries
)
WHERE students.studentsID = “00012” ;

我也确实使用了 JOIN 语句，但问题仍然存在。

INNER JOIN grads As studGrad ON studentsEduID = student_edu.eduID INNER JOIN studies AS studyOption ON student_edu.study = studies.studiesID INNER JOIN rooms AS classRoom ON student_edu.classroom = rooms.roomsID INNER JOIN student_status AS stat ON student_edu.studentStatus = student_status.student_statusID INNER JOIN testStatus AS admiTest ON student_edu.testStatus = testStatus.testStatusID INNER JOIN student_address AS studAddre ON students.studentsRezAddressID = student_address.RezAddressID INNER JOIN student_birth_place AS stubBirth ON students.studentsBirthID = student_birth_place.birthID INNER JOIN cities AS ville ON student_address.RezCity = cities.cityID INNER JOIN states AS province ON student_address.RezState = states.statesID INNER JOIN districts AS commune ON student_address.RezDistrict = districts.districtsID INNER JOIN countries AS pays ON student_address.RezCountry = countries.countryID

Answer 1

从基本查询开始，如下一个查询，并在每一步使用 JOIN 子句从另一个表添加信息，例如：

SELECT
students.*,
student_address.*
FROM
students
LEFT JOIN
student_address ON student_address.RezAddressID = students.studentsRezAddressID
WHERE
students.studentsID = 12;

执行此操作，直到收到错误或查询挂起。另外，为什么将 studentsID 列与 WHERE 子句上的字符串进行比较，因为 studentsID 是一个整数列？

如果这个基本查询有效，那么我将帮助您逐步包含更多信息，只需告诉我...

Answer 2

你应该避免这么大的结果集。有几种选择可以做到这一点。

选项1：选择所需的最小列数。

选项 2：使用分页获取结果集中有限数量的行

Answer 3

使用 LEFT JOIN 并遵循 TOP DOWN 方法，例如。

学生表

•   studentsID  Int Pkey
•   Fname   string
•   Lname   string …    
•   studentsGender  Int Fkey
•   studentsGender  int Fkey
•   studentsRezAddressID    int Fkey
•   studentsBirthID int Fkey
•   studentsParentsID   int Fkey

在上表中，首先，选择所有 Fkey 并检查数据是否需要，如果需要，然后加入否则离开它，如果您遵循此策略，那么也许您会得到结果。