多表查询sql语句

首先随机创建两张表，这里我们创建两个表分别为hero和table

代码mysql> create table hero(
    -> id int primary key auto_increment comment "英雄编号",
    -> name varchar(50) not null comment "英雄编号",
    -> age int default 18,
    -> tel char(11),
    -> email varchar(255) unique,
    -> join_time datetime,
    -> salary float,
    -> place_id int
    -> );

和

mysql> create table place(
    -> id int primary key auto_increment,
    -> name varchar(50) unique not null,
    -> intro text
    -> );

为表添加内容：

mysql> insert into place(name, intro) values("水浒传", "英雄起义");
Query OK, 1 row affected (0.01 sec)

mysql> insert into place(name, intro) values("西游记", "西天取经");
Query OK, 1 row affected (0.01 sec)

mysql> insert into place(name, intro) values("红楼梦", "阶级矛盾");
Query OK, 1 row affected (0.01 sec)

mysql> insert into place(name, intro) values("三国演义", "国家纷争");
Query OK, 1 row affected (0.01 sec)

和

mysql> insert into hero(name, tel, email, join_time, salary, place_id)
    -> values("宋江", "1110", "110@qq.com", '2020-3-4 8:20:35', 5000, 1);

mysql> insert into hero(name, tel, email, join_time, salary, place_id)
    -> values("李逵", "11120", "1160@qq.com", '2020-4-4 7:20:35', 5000, 1);

mysql> insert into hero(name, tel, email, join_time, salary, place_id)
    -> values("孙悟空", "1123510", "1123510@qq.com", '2020-3-8 8:28:35', 5000, 2);

mysql> insert into hero(name, tel, email, join_time, salary, place_id)
    -> values("张飞", "11111110", "11111110@qq.com", '2020-10-4 8:20:38', 15000, 4);

mysql> insert into hero(name, tel, email, join_time, salary, place_id)
    -> values("林黛玉", "111110", "111110@qq.com", '2021-3-4 10:20:35', 35000, 3);

下图为添加内容后的表：

select * from  hero limit 1,3;和select * from hero limit 2,4;指令意思分别是hero表中2-4条数据记录和hero表中3-5条数据记录。如下图：

查找英雄表中英雄的id,name,place_id的信息；

select id,name,place_id from hero;

从英雄表中查询place_id等于1和3的英雄

select * from hero where place_id='1' OR place_id='3';

从hero表中查询每个place_id中有多少人

mysql> SELECT place_id,COUNT(place_id) FROM hero group BY place_id;

从hero表中查询工资的最大值

select place_id,max(salary) from hero group by place_id;