创建两个表:
CREATE TABLE detail
(
id
bigint(20) NOT NULL AUTO_INCREMENT,
content
varchar(5255) DEFAULT NULL,
createtime
datetime DEFAULT CURRENT_TIMESTAMP,
title
varchar(255) DEFAULT NULL,
authorid
bigint(20) NOT NULL,
authorname
varchar(255) DEFAULT NULL,
category
varchar(255) DEFAULT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
CREATE TABLE user
(
name
varchar(100) DEFAULT NULL,
pwd
varchar(100) DEFAULT NULL,
id
int(11) NOT NULL AUTO_INCREMENT,
age
int(11) DEFAULT NULL,
sex
varchar(1) DEFAULT NULL,
email
varchar(255) DEFAULT NULL,
mobile
varchar(255) DEFAULT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
练习语句:
select * from detail;
– as 表示取别名, 也可以直接空格代替as
select d.authorid,d.title from detail as d;
– 两个表通过d.authorid=u.id 连接,只有id的值在两个表都有才出现
select d.authorid,d.title,u.age from detail as d, user u where d.authorid=u.id;
– inner join 表示内连接 两个表的交集
select d.authorid,d.title,u.age from detail as d inner join user u on d.authorid=u.id ;
– left join 表示左连接 ,左边所有的记录+两个表的交集
select d.authorid,d.title,u.age from detail as d left join user u on d.authorid=u.id ;
– left join 表示右连接 ,右边所有的记录+两个表的交集
select d.authorid,d.title,u.age from detail as d right join user u on d.authorid=u.id ;