九、多表查询

1.1 复制表
作用？ 备份表 和 快速建表
命令格式？ create table 库.表 sql查询命令；

例子？
create table db3.user2 select * from db3.usertab;

create table db3.user3 select username,uid,shell from db3.usertab limit 5;

create database db4;
create table db4.t1 select * from db3.usertab where 1 =2;

create table db4.t2 select id,username,uid,homedir from db3.usertab where 1 =2;

1.2 where嵌套查询
select username,uid from db3.usertab where uid < (select avg(uid) from db3.usertab）;

mysql> select username,uid from db3.usertab where uid > (select avg(uid) from db3.usertab);

select username from db3.usertab
where username in
(select user from mysql.user where host="localhost");

1.3多表查询
mysql> create table db4.t3
-> select username,uid,shell,homedir from db3.usertab
-> limit 3;

mysql> create table db4.t4
-> select username,uid,gid from db3.usertab limit 5;

3 * 5 = 15
select * from t3,t4; 迪卡尔集

mysql> select t3.username,t4.username from t3,t4
-> where
-> t3.username = t4.username;

mysql> select t3.*,t4.username from t3,t4 where t3.username = t4.username;

select * from t3,t4
where
t3.uid = t4.uid ;

select t3.* , t4.gid from t3,t4
where
t3.uid = t4.uid ;

select t3.username , t4.username from t3,t4
where
t3.uid = t4.uid ;

select t3.username,t4.username from t3,t4
where
t3.uid = t4.uid
and t3.username is not null
and t4.username is not null;

1.4 连接查询
mysql> create table db4.t5
select username,uid,gid,shell from db3.usertab
where uid>=100 and uid<=500;

mysql> create table db4.t6
select username,uid,gid,shell from db3.usertab
where uid>=100 and uid<=500 limit 3;

select * from t6 right join t5 on
t6.uid = t5.uid;

select * from t6 left join t5 on t6.uid = t5.uid;

select t5.username,t6.username from t6 right join t5 on
t6.uid = t5.uid;

select t5.username,t6.username from t6 left join t5 on t6.uid = t5.uid;