MySQL数据库高级查询和多表查询

吾爱主题阅读：172 2024-04-05 16:22:09 评论：0

添加练习表

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 -- 用户表（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 );

笛卡尔积

笛卡尔乘积是指在数学中，两个集合x和y的笛卡尔积（cartesian product），又称直积，表示为x × y，第一个对象是x的成员而第二个对象是y的所有可能有序对的其中一个成员。（笛卡尔积）百度百科
什么笛卡尔积，如下所示

1	`select` `*` `from` ` `user` `,`orders`;

像如上图查出来的数据，对我们程序员是没啥用的。
哪如何消除笛卡尔积呢？需要主外键的约束，去重复数据。

1	`select` `*` `from` ` `user` ` `as` u,`orders` `as` `o` `where` u.`id`=o.`user_id`;

1.内连接

1.1隐式内连接

from 后面直接出现多表表名，这个属于隐式内连接
select * from 表a，表b where a.id = b.a_id;

1	`select` `*` `from` ` `user` ` `as` u,`orders` `as` `o` `where` u.`id`=o.`user_id`;

1.2显示内连接（推荐使用）

使用 inner join 来链接表，后面 on 跟条件。（inner 可以省略）
select * from 表a inner join 表b on a.id = b.a_id;
查询成年用户和订单数据；

1	`select` `*` `from` ` `user` ` u `join` `orders` o `on` u.`id`=o.`user_id` `where` `age >= 18;`

2.外连接

外链接可以显示单表的全部数据，包括null；

2.1右外链接

显示右边表的全部数据
使用 right outer join 来链接表，后面 on 跟条件。（outer 可以省略）
select * from 表a right outer join 表b on a.id=b.a_id;

1	`select` `*` `from` ` `user` ` u `right` `join` `orders` o `on` u.`id`=o.`user_id`;

左边表数据（user）

右边表数据（orders）

2.2左外链接（推荐使用）

显示左边表的全部数据
使用 left outer join 来链接表，后面 on 跟条件。（outer 可以省略）
select * from 表a left outer join 表b on a.id=b.a_id;

1	`select` `*` `from` ` `user` ` u `left` `join` `orders` o `on` u.`id`=o.`user_id`;

左边表数据（user）

右边表数据（orders）

3.子查询

子查询，嵌套的感觉。查询出来的结果给另外一个查询当条件使用。
查询年龄最大的用户的订单数据

1 2 3 4 5 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 ) );