Mysql 表连接基本操作。
WampServer修改MySQL ROOT用户密码
- ##################################################
- #表连接操作
- ##################################################
- create table A
- (
- id smallint(5) unsigned auto_increment,
- name varchar(50) not null,
- primary key (id)
- )engine=innodb default charset=utf8;
- desc A;
- create table B
- (
- id smallint(5) unsigned auto_increment,
- address varchar(50) not null,
- A_id smallint(5) unsigned,
- primary key (id)
- )engine=innodb default charset=utf8;
- desc B;
- insert into A(name)
- values('小陈张'),('小陈李'),('小陈王');
- insert into B(address,A_id)
- values('小陈深圳',1),('小陈上海',3),('小陈南京',10);
- #内连接，推荐使用，效率和性能比较高
- SELECT A.name,B.address FROM A,B WHERE A.id = B.A_id;
- #左连接，以左边为依据查询，右边没有匹配就为空
- SELECT A.name,B.address FROM A LEFT JOIN B ON A.id = B.A_id;
- #右连接，以右边为依据查询，左边没有匹配就为空
- SELECT A.name,B.address FROM A RIGHT JOIN B ON A.id = B.A_id;
- #子查询
- SELECT * FROM A WHERE id IN(SELECT A_id FROM B WHERE address LIKE '%深圳%');
评论