SQL中的多表查询查询

1、创建表

CREATE TABLE `user` (
  `userid` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
   PRIMARY KEY (`userid`),
  KEY `FK36EBCBC2C7D040` (`usertype`)
) CREATE TABLE `userpostmess` (
  `userpostmessid` int(11) NOT NULL AUTO_INCREMENT,
  `userid` bigint(20) DEFAULT NULL,
  `posttime` datetime DEFAULT NULL,
  PRIMARY KEY (`userpostmessid`),
  UNIQUE KEY `userpostmessid` (`userpostmessid`)
)

多表查询：select  a.* ,b.* from user a,userpostmess b 

多表按条件查询：select a.* ,b.* from user a,userpostmess b  where a.userid=b.userid;

//内连接仅仅显示符合条件的数据

多表内连接查询：select a.* ,b.* from user a inner join userpostmess b  on a.userid=b.userid

//左连接左边全部数据显示、右边仅显示符合条件的数据

多表左连接（左外连接）：select  a.*,b.* from user a left join userpostmess b on a.userid=b.userid

//右外连接显示数据与左连接相反

多表右外连接：select a.*  ,b.* from user a right  join userpostmess b on a.userid=b.userid

//多表按条件排序

select a.userid  ,b.userid from  user a  right  join  userpostmess b   on a.userid=b.userid  group by a.userid desc