search
HomeDatabaseMysql Tutorial用 INNER JOIN语法联接多个表建记录集

多表联接建立记录集是十分有用的,因为某些情况下,我们需要把数字数据类型显示为相应的文本名称,这就遇到了多表联接建立记录集的问题。比如作一个会员注册系统,共有五个表,会员信息数据表member、会员身份表MemberIdentity、会员权限表MemberLevel、会员类别表MemberSort和会员婚姻状况表Wedlock。如果想把会员注册信息全部显示出来,肯定要将这四个表连起来,否则大家看到的某些会员信息可能只是数据编号。
以会员类别表来说,在其数据表中,1代表普通会员,2代表高级会员,3代表终身会员,在显示时,如果不将会员类别表与会员详细数据表相关联,那么假如我们现在看到的是一名普通会员的注册信息,我们只能看到其类别为1,而谁又会知道1代表的是普通会员呢?所以要将会员类别表与会员详细数据表相关联,关联后,1就显示为普通会员,2就显示为高级会员,3就显示为终身会员,这样多好?同理,其它两个表也要与会员详细数据表相关联才能把数据编号显示为相应的名称。

步骤一:用Access软件建立一个名为Member的数据库,在其中建五个表,分别为:会员信息数据表member、会员身份表MemberIdentity、会员权限表MemberLevel、会员类别表MemberSort和会员婚姻状况表Wedlock。

●会员信息数据表member:
MemberID:自动编号,主键(ID号)
MemberSort:数字(会员类别)
MemberName:文本,会员姓名
Password:文本(会员密码)
MemberLevel:数字(会员权限)
MemberIdentity:数字(会员身份)
Wedlock:数字(婚姻状况)
MemberQQ:文本(QQ号码)
MemberEmail:文本(会员邮箱)
MemberDate:日期/时间(会员注册日期)

●会员身份表MemberIdentity:
MemberIdentity:自动编号,主键(ID号)
IdentityName:文本(会员身份名称)

●会员权限表MemberLevel:
MemberLevel:自动编号,主键(ID号)
LevelName:文本(会员权限名称)

●会员类别表MemberSort:
MemberSort:自动编号,主键(ID号)
SortName:文本(会员类别名称)

●会员婚姻状况表Wedlock
Wedlock:自动编号,主键(ID号)
WedlockName:文本(会员婚姻状况类别)
说明:五个表建好后,您可以自行设置您想要的类别,如会员权限,您可以设置两个类别--“未付费会员”和“已付费会员”,编号分别为“1”、“2”,如您设置了三个选项,那么第三个选项的编号当然就是“3”了。
下面我们所要作的工作就是把“1”、“2”之类的编号显示为“未付费会员”和“已付费会员”,否则,大家谁会知道“1”代表的是“未付费会员”,“2”代表的是“已付费会员”?

步骤二:建DSN数据源,建记录集
●运行Dreamweaver MX软件,在会员注册信息显示页面建一个名为ConnMember(您也可以起其它的名称)的DSN数据源。

●点击服务器行为面板中的“绑定”,建一个名为MemberShow的数据集,“连接”选择ConnMember,“表格”选择Member,“列”全选,“排序”选择MemberDate,降序。点击“高级”按钮,修改SQL框中自动生成的代码:
原代码为:
SELECT *
FROM Member
ORDER BY MemberDate DESC

将代码修改为:
SELECT *
FROM (((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity) INNER JOIN Wedlock ON Member.Wedlock=Wedlock.Wedlock
ORDER BY MemberDate DESC
修改完代码后,点击“确定”,大功告成!
现在,您可以打开记录集看一下,五个表中的字段全部集成在MemberShow记录集中,您只要将相应的字段绑定在该字段想显示的单元格中即可。这下好了,所有的数字编号全部变成了相应的名称,如会员权限,不再是“1”和“2”的数字形式了,而是变成了相应的名称“未付费会员”和“已付费会员”。其它的数字编号也变成了显示的文本名称,是不是很开心呢?

注意事项:
●在输入字母过程中,一定要用英文半角标点符号,单词之间留一半角空格;
●在建立数据表时,如果一个表与多个表联接,那么这一个表中的字段必须是“数字”数据类型,而多个表中的相同字段必须是主键,而且是“自动编号”数据类型。否则,很难联接成功。
●代码嵌套快速方法:如,想连接五个表,则只要在连接四个表的代码上加一个前后括号(前括号加在FROM的后面,后括号加在代码的末尾即可),然后在后括号后面继续添加“INNER JOIN 表名X ON 表1.字段号=表X.字段号”代码即可,这样就可以无限联接数据表了:)

语法格式:
其实 INNER JOIN ……ON的语法格式可以概括为:
FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表X ON Member.字段号=表X.字段号
您只要套用该格式就可以了。

现成格式范例:
虽然我说得已经比较明白了,但为照顾初学者,我还是以本会员注册系统为例,提供一些现成的语法格式范例,大家只要修改其中的数据表名称和字段名称即可。

连接两个数据表的用法:
FROM Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort
语法格式可以概括为:
FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号

连接三个数据表的用法:
FROM (Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel
语法格式可以概括为:
FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

连接四个数据表的用法:
FROM ((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity
语法格式可以概括为:
FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号

连接五个数据表的用法:
FROM (((Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort) INNER JOIN MemberLevel ON Member.MemberLevel=MemberLevel.MemberLevel) INNER JOIN MemberIdentity ON Member.MemberIdentity=MemberIdentity.MemberIdentity) INNER JOIN Wedlock ON Member.Wedlock=Wedlock.Wedlock
语法格式可以概括为:
FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Reduce the use of MySQL memory in DockerReduce the use of MySQL memory in DockerMar 04, 2025 pm 03:52 PM

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

How to solve the problem of mysql cannot open shared libraryHow to solve the problem of mysql cannot open shared libraryMar 04, 2025 pm 04:01 PM

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

How do you alter a table in MySQL using the ALTER TABLE statement?How do you alter a table in MySQL using the ALTER TABLE statement?Mar 19, 2025 pm 03:51 PM

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

Run MySQl in Linux (with/without podman container with phpmyadmin)Run MySQl in Linux (with/without podman container with phpmyadmin)Mar 04, 2025 pm 03:54 PM

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

What is SQLite? Comprehensive overviewWhat is SQLite? Comprehensive overviewMar 04, 2025 pm 03:55 PM

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

How do I configure SSL/TLS encryption for MySQL connections?How do I configure SSL/TLS encryption for MySQL connections?Mar 18, 2025 pm 12:01 PM

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

Running multiple MySQL versions on MacOS: A step-by-step guideRunning multiple MySQL versions on MacOS: A step-by-step guideMar 04, 2025 pm 03:49 PM

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?Mar 21, 2025 pm 06:28 PM

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools