MySQL 多表联合操作

ernestwang 833 0

多表联合操作

  • 使用workbench连接数据库
  • 准备两张表:并准备数据
    CREATE TABLE `test`.`wesr` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `username` VARCHAR(20) NOT NULL,
      `gid` INT NOT NULL DEFAULT 0,
      PRIMARY KEY (`id`))
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8;
    
    CREATE TABLE `test`.`goods` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(20) NOT NULL,
      `price` FLOAT NULL,
      `category` VARCHAR(20) NULL,
      PRIMARY KEY (`id`))
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8;
  • 隐式内连接:没有join关键字
    select username, name from user, goods where user.gid=goods.id;
    查询哪个用户买了什么商品
  • 显式内连接:使用join关键字
    select username, name from user [inner/cross] join goods on user.gid=goods.id;
    功能同上,inner和cross都可以,通常可以省略
  • 左外连接:以左表为主,无论右表有无对应数据,左表字段都会显示
    select username, name from user left [outer] join goods on user.gid=goods.id;
  • 右外连接:以右表为主,无论左表有无对应数据,右表字段都会显示
    select username, name from user right [outer] join goods on user.gid=goods.id;
  • 记录联合:将不同的SQL语句查询结果拼接在一起
    • 格式:SQL1 union [all] SQL2
    • 示例:
    select username, name from user left outer join goods on user.gid=goods.id union all select username, name from user right outer join goods on user.gid=goods.id;
    • 说明:
      • union all:将两边的查询结果直接拼接在一起
      • union:是union all去重后的结果
  • 联合更新
    update user u, goods g set u.gid=0, g.price=g.price+0.2 where u.gid=g.id and u.id=1;
  • 子(嵌套)查询
    select * from user where gid in (1, 3, 5);
    select * from user where gid in (select id from goods);

标签: mysql

发布评论 0条评论)

还木有评论哦,快来抢沙发吧~

复制成功
微信号: irenyuwang
关注微信公众号,站长免费提供流量增长方案。
我知道了