首先随机创建两张表,这里我们创建两个表分别为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;