- 使用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
版权声明:除非特别标注,否则均为本站原创文章,转载时请以链接形式注明文章出处。
还木有评论哦,快来抢沙发吧~