inner join 关键字返回行

  • 栏目:数据 时间:2020-04-07 10:02
<返回列表

在表中存在至少一个匹配时,inner join 关键字返回行。

inner join 关键字语法select column_name(s)from table_name1inner join table_name2 on table_name1.column_name=table_name2.column_name注释:inner join 与 join 是相同的,inner join 关键字在表中存在至少一个匹配时返回行。如果 a 中的行在 b 中没有匹配,就不会列出这些行。

多表联合查询

mysql教程mysql create table books( - bookid smallint not null primary key, - booktitle varchar(60) not null, - copyright year not null - ) - engine=innodb;query ok, 0 rows affected (0.09 sec)

mysqlmysqlmysql insert into books values (12786, 'java', 1934), - (13331, 'mysql', 1919), - (14356, 'php教程', 1966), - (15729, 'perl', 1932), - (16284, 'oracle', 1996), - (17695, 'pl/sql', 1980), - (19264, '网页特效', 1992), - (19354, '', 1993);query ok, 8 rows affected (0.05 sec)records: 8 duplicates: 0 warnings: 0

mysqlmysqlmysql create table authors( - authid smallint not null primary key, - authfn varchar(20), - authmn varchar(20), - authln varchar(20) - ) - engine=innodb;query ok, 0 rows affected (0.05 sec)

mysqlmysqlmysql insert into authors values (1006, 'h', 's.', 't'), - (1007, 'j', 'c', 'o'), - (1008, 'b', null, 'e'), - (1009, 'r', 'm', 'r'), - (1010, 'j', 'k', 't'), - (1011, 'j', 'g.', 'n'), - (1012, 'a', null, 'p'), - (1013, 'a', null, 'w'), - (1014, 'n', null, 'a');query ok, 9 rows affected (0.03 sec)records: 9 duplicates: 0 warnings: 0

mysqlmysqlmysql create table authorbook( - authid smallint not null, - bookid smallint not null, - primary key (authid, bookid), - foreign key (authid) references authors (authid), - foreign key (bookid) references books (bookid) - ) - engine=innodb;query ok, 0 rows affected (0.06 sec)

mysqlmysqlmysql insert into authorbook values (1006, 14356), - (1008, 15729), - (1009, 12786), - (1010, 17695), - (1011, 15729), - (1012, 19264), - (1012, 19354), - (1014, 16284);query ok, 8 rows affected (0.05 sec)records: 8 duplicates: 0 warnings: 0

mysqlmysqlmysql select * from authors;+--------+--------+--------+--------+| authid | authfn | authmn | authln |+--------+--------+--------+--------+| 1006 | h | s. | t || 1007 | j | c | o || 1008 | b | null | e || 1009 | r | m | r || 1010 | j | k | t || 1011 | j | g. | n || 1012 | a | null | p || 1013 | a | null | w || 1014 | n | null | a |+--------+--------+--------+--------+9 rows in set (0.00 sec)

mysql select * from books;+--------+----------------+-----------+| bookid | booktitle | copyright |+--------+----------------+-----------+| 12786 | java | 1934 || 13331 | mysql | 1919 || 14356 | php | 1966 || 15729 | perl | 1932 || 16284 | oracle | 1996 || 17695 | pl/sql | 1980 || 19264 | javascript | 1992 || 19354 | | 1993 |+--------+----------------+-----------+8 rows in set (0.00 sec)

mysql select * from authorbook;+--------+--------+| authid | bookid |+--------+--------+| 1009 | 12786 || 1006 | 14356 || 1008 | 15729 || 1011 | 15729 || 1014 | 16284 || 1010 | 17695 || 1012 | 19264 || 1012 | 19354 |+--------+--------+8 rows in set (0.00 sec)

mysqlmysqlmysql select booktitle, authid from books inner join authorbook;+----------------+--------+| booktitle | authid |+----------------+--------+| java | 1006 || mysql | 1006 || php | 1006 || perl | 1006 || oracle | 1006 || pl/sql | 1006 || javascript | 1006 || | 1006 || java | 1008 || mysql | 1008 || php | 1008 || perl | 1008 || oracle | 1008 || pl/sql | 1008 || javascript | 1008 || | 1008 || java | 1009 || mysql | 1009 || php | 1009 || perl | 1009 || oracle | 1009 || pl/sql | 1009 || javascript | 1009 || | 1009 || java | 1010 || mysql | 1010 || php | 1010 || perl | 1010 || oracle | 1010 || pl/sql | 1010 || javascript | 1010 || | 1010 || java | 1011 || mysql | 1011 || php | 1011 || perl | 1011 || oracle | 1011 || pl/sql | 1011 || javascript | 1011 || | 1011 || java | 1012 || mysql | 1012 || php | 1012 || perl | 1012 || oracle | 1012 || pl/sql | 1012 || javascript | 1012 || | 1012 || java | 1012 || mysql | 1012 || php | 1012 || perl | 1012 || oracle | 1012 || pl/sql | 1012 || javascript | 1012 || | 1012 || java | 1014 || mysql | 1014 || php | 1014 || perl | 1014 || oracle | 1014 || pl/sql | 1014 || javascript | 1014 || | 1014 |+----------------+--------+64 rows in set (0.00 sec)

mysqlmysql drop table authorbook;query ok, 0 rows affected (0.02 sec)

mysql drop table books;query ok, 0 rows affected (0.06 sec)

mysql drop table authors;query ok, 0 rows affected (0.03 sec)

二个表连接

mysql select employee.first_name, job.title, duty.task - from employee, job, duty - where (employee.id = job.id and employee.id = duty.id);+------------+------------+-----------+| first_name | title | task |+------------+------------+-----------+| jason | tester | test || alison | accountant | calculate || james | developer | program || celia | coder | test || robert | director | manage || linda | mediator | talk || david | proffessor | speak || james | programmer | shout |+------------+------------+-----------+8 rows in set (0.00 sec)

韦德体育 ,mysqlmysqlmysqlmysql drop table duty;query ok, 0 rows affected (0.00 sec)

mysql drop table job;query ok, 0 rows affected (0.01 sec)

mysql drop table employee;query ok, 0 rows affected (0.00 sec)

总结

inner join 连接两个数据表的用法:select * from 表1 inner join 表2 on 表1.字段号=表2.字段号

inner join 连接三个数据表的用法:select * from (表1 inner join 表2 on 表1.字段号=表2.字段号) inner join 表3 on 表1.字段号=表3.字段号

inner join 连接四个数据表的用法:select * from ((表1 inner join 表2 on 表1.字段号=表2.字段号) inner join 表3 on 表1.字段号=表3.字段号) inner join 表4 on member.字段号=表4.字段号

inner join 连接五个数据表的用法:select * 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.字段号

上一篇:韦德体育先按年龄降序排列 下一篇:在asp.net中不使用linq访问数据库

更多阅读

inner join 关键字返回行

数据 2020-04-07
在表中存在至少一个匹配时,inner join 关键字返回行。 inner join 关键字语法select column_name(s)fr...
查看全文

在asp.net中不使用linq访问数据库

数据 2020-04-07
asp教程.net中不使用linq访问 mssql server 2005数据库教程解决办法 asp.net教程应用程序的数据访问是...
查看全文

韦德体育先按年龄降序排列

数据 2020-04-05
聚合函数 count,max,min,avg,sum... select count (*) from T_Employeeselect Max(FSalary) from T_Employee 排序 ASC升序...
查看全文

友情链接: 网站地图

Copyright © 2015-2019 http://www.koi-bumi.com. 韦德体育有限公司 版权所有