多表查询的练习
本文最后更新于 194 天前,其中的信息可能已经有所发展或是发生改变。

多表的综合练习


create table corps(
    id int auto_increment primary key comment '兵团ID',
    name varchar(10) not null
 )comment '兵团表';
 insert into corps values (1,'调查兵团'),(2,'驻扎兵团'),(3,'宪兵团'),(4,'训练兵团'),(5,'普通人');
 create table people(
    id int auto_increment primary key comment '人员ID',
    name varchar(10) not null comment '名字',
    age int check(age>=10&&age<=100),
    job varchar(10),
    zhandouli int,
    entrydate date comment '入团时间',
    managerid int comment '直属领导ID',
    corps_id int comment '所属兵团'
 )comment '人员表';
 insert into people values (1,'许扬威',20,'战斗员',100,'2000-01-01',3,1),
                           (2, '刘子康', 20, '调查员',50, '2005-12-05', 3,1),
  (3, '卢开', 33, '调查团长', 10,'2000-11-03', null,1),
  (4, '王寅', 48, '分队长',100, '2002-02-05', null,2),
  (5, '庹凡', 15, '训练官',80, '2004-09-07', null,4),
  (6, '彭世威', 25, '研究员',60, '2004-10-12', 3,1),
  (7, '赵沛瑶', 23, '普通人',0, null, null,5),
  (8, '陈怡婷', 20, '调查员',90, '2006-06-02', 1,1),
  (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
  (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
  (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
  (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
  (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
  (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
  (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
  (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
  (17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);
 create table z(   grade int,   losal int,   hisal int) comment '战斗力等级表';
 insert into z values (1,0,3000);
 insert into z values (2,3001,5000);
 insert into z values (3,5001,8000);
 insert into z values (4,8001,10000);
 insert into z values (5,10001,15000);
 insert into z values (6,15001,20000);
 insert into z values (7,20001,25000);
 insert into z values (8,25001,30000);
 -- 添加外键约束
    alter table people add constraint people_id foreign key (corps_id) references corps(id);
 -- 查询人员id 姓名,年龄及其部门信息(隐式内连接)
    select people.id,people.name,people.age,corps.name from people,corps where people.corps_id=corps.id;
 -- 查询年龄小于30岁的人员的姓名、年龄、职位、部门信息(显式内连接)
     select people.id,people.name,people.age,corps.name  from people join corps on people.corps_id=corps.id where age<30;
 -- 查询拥有员工的部门ID、部门名称表: emp , dept连接条件: emp.dept_id = dept.id
     select distinct e.id , e.name from corps e , people d where d.corps_id = e.id;
 -- distinct用法 :列或者行去重
   
   -- 查询所有年龄大于40岁的员工, 及其归属的兵团; 如果员工没有分配兵团, 也需要展示出来(外连接)
     select people.*,corps.name from people left join corps on corps.id = people.corps_id where people.age>=40;
 
   -- 查询 "研发部" 员工的平均战斗力
      select avg(zhandouli) from people,corps where corps_id=people.corps_id && corps.name='调查兵团';
 --   查询战斗力比 "许扬威" 高的员工信息。
     select zhandouli from people where name='许扬威';
     select people.* from people where (zhandouli>(select zhandouli from people where name='许扬威'));
 -- 查询比平均战斗力高的员工信息
    select avg(zhandouli) from people;
    select *from people where zhandouli>( select avg(zhandouli) from people);
 -- 查询低于兵团平均战斗的员工信息
    select avg(zhandouli) from people where people.corps_id='1';
   select *from people p where zhandouli<(select avg(zhandouli) from people where people.corps_id=p.corps_id);
 -- 查询所有的兵团, 并统计兵团的员工人数
     select c.id,c.name ,( select count(*) from people p where p.corps_id = c.id ) '人数' from corps c;
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇