mysql多表查询
添加练习表
-- 用户表(user) create table `user`( `id` int auto_increment primary key comment '用户id(主键)', `username` varchar(50) comment '用户姓名', `age` char(3) comment '用户年龄' ); -- 订单表(orders) create table `orders`( `id` int auto_increment primary key comment '订单id(主键)', `price` double comment '订单价格', `user_id` int comment '用户id(外键)' ); -- 给已经存在的表添加外键,语法如下 -- alter table 表名 add constraint [外键名字] foreign key (外键字段) references 父表(主键字段); alter table orders add constraint user_fk foreign key (user_id) references `user` (id); -- 向user表中添加数据 insert into user values(1,'第一',11); insert into user values(2,'小二',12); insert into user values(3,'张三',33); insert into user values(4,'李四',24); insert into user values(5,'王五',17); insert into user values(6,'赵六',36); insert into user values(7,'七七',18); insert into user values(8,'粑粑',null); -- 向orders 表中插入数据 insert into orders values(111,1314,3); insert into orders values(112,122,3); insert into orders values(113,15,4); insert into orders values(114,315,5); insert into orders values(115,1014,null); insert into orders values(116,666,6); insert into orders values(117,1111,1); insert into orders values(118,8888,null);
笛卡尔积
select * from `user`,`orders`;
select * from `user` as u,`orders` as o where u.`id`=o.`user_id`;
1.内连接
1.1隐式内连接
select * from `user` as u,`orders` as o where u.`id`=o.`user_id`;
1.2显示内连接(推荐使用)
select * from `user` u join `orders` o on u.`id`=o.`user_id` where age >= 18;
2.外连接
2.1右外链接
select * from `user` u right join `orders` o on u.`id`=o.`user_id`;
左边表数据(user)
右边表数据(orders)
2.2左外链接(推荐使用)
select * from `user` u left join `orders` o on u.`id`=o.`user_id`;
左边表数据(user)
右边表数据(orders)
3.子查询
select * from orders o where o.`user_id` in ( select u.`id` from `user` u where u.`age` in( select max(u.`age`) from `user` u ) );
4.全连接(mysql不支持)
mysql其它文章,请看下面链接
end…
到此这篇关于mysql数据库高级查询和多表查询的文章就介绍到这了,更多相关mysql高级查询和多表查询内容请搜索移动技术网以前的文章或继续浏览下面的相关文章希望大家以后多多支持移动技术网!
如对本文有疑问, 点击进行留言回复!!
mysql·update语句报错:Data truncation: Truncated incorrect DOUBLE value: ‘系统开小差啦~请稍后再试‘
MySQL Fix Product Name Format(trim去空格+upper/lower大小写)
mysql pxc LIMITATIONS 8.0.19-10
网友评论