多表操作练习
> > > MySQL基础
> > > MySQL的多表操作查询
> > > MySQL多表操作练习
> > > MySQL视图、存储过程、函数、触发器
👇👇👇创建表结构和添加数据的SQL在底部👇👇👇
多表查询练习
1、查询用户的编号,姓名,年龄,订单编号。
/*
分析:
需要查询的字段:u.id,u.name,u.age,o.number
查询的表:user表 orderlist表
所需要的条件:主键id = 外键uid
*/
SELECT u.id,
u.name,
u.age,
o.number
FROM USER U, ORDERLIST O
WHERE U.ID = O.UID;
2、查询所有的用户。用户的编号,姓名,年龄,订单编号。
/*
分析:
所有用户数据:USER为主表
需要查询的字段:u.id,u.name,u.age,o.number
查询的表:user表 orderlist表
所需要的条件:主键id = 外键uid
*/
SELECT u.id,
u.name,
u.age,
o.number
FROM USER U
LEFT JOIN ORDERLIST O -- 左连接USER LEFT(left左边表(USER)中的所有数据)
ON O.UID = U.ID;
3、查询所有的订单。用户的编号,姓名,年龄,订单编号。
/*
分析:
查询所有的订单数据:LEFT JOIN ORDERLIST O
需要查询的字段:u.id,u.name,u.age,o.number
查询的表:user表 orderlist表
所需要的条件:主键id = 外键uid
*/
-- 右连接
SELECT u.id,
u.name,
u.age,
o.number
FROM USER U
RIGHT JOIN ORDERLIST O
ON U.ID = O.UID;
-- 左连接
SELECT u.id,
u.name,
u.age,
o.number
FROM ORDERLIST O
LEFT JOIN USER U
ON U.ID = O.UID;
4、查询用户年龄大于23岁的信息。显示用户的编号,姓名,年龄,订单编号。
/*
分析:
需要查询的字段:u.id,u.name,u.age,o.number
查询的表:user表 orderlist表
所需要的条件:主键id = 外键uid AND AGE > 23;
*/
SELECT u.id,
u.name,
u.age,
o.number
FROM USER U, ORDERLIST O
WHERE U.ID = O.UID AND AGE > 23;
5、查询张三和李四的用户信息。显示用户的编号,姓名,年龄,订单编号。
/*
分析:
需要查询的字段:u.id,u.name,u.age,o.number
查询的表:user表 orderlist表
所需要的条件:主键id = 外键uid AND NAME IN ('张三','李四');
*/
SELECT u.id,
u.name,
u.age,
o.number
FROM USER U, ORDERLIST O
WHERE U.ID = O.UID
AND NAME IN ('张三','李四');
6、查询商品分类的编号、分类名称、分类下的商品名称。
/*
分析:
需要查询的字段:c.id,c.name,p.name
查询的表:category商品分类表 product商品表
所需要的条件:主键p.id = 外键c.pid ;
*/
SELECT C.ID,
C.NAME,
P.NAME
FROM CATEGORY C, PRODUCT P
WHERE C.ID = P.CID;
7、查询所有的商品分类。商品分类的编号、分类名称、分类下的商品名称。
/*
分析:
需要查询的字段:c.id,c.name,p.name
查询的表:category商品分类表 product商品表
所需要的条件:主键p.id = 外键c.pid ;
*/
SELECT C.ID,
C.NAME,
P.NAME
FROM CATEGORY C
LEFT JOIN PRODUCT P
ON C.ID = P.CID;
8、查询所有的商品信息。商品分类的编号、分类名称、分类下的商品名称。
/*
分析:
查询所有的商品分类:PRODUCT为主表
需要查询的字段:c.id,c.name,p.name
查询的表:category商品分类表 product商品表
所需要的条件:主键p.id = 外键c.pid ;
*/
SELECT C.ID,
C.NAME,
P.NAME
FROM CATEGORY C
RIGHT JOIN PRODUCT P
ON C.ID = P.CID;
9、查询所有的用户和所有的商品。显示用户的编号,姓名,年龄、商品名称。
SELECT U.ID,
U.NAME,
U.AGE,
P.NAME
FROM USER U, PRODUCT P, US_PRO PO
WHERE U.ID = PO.UID
AND P.ID = PO.PID;
10、查询张三和李四这两个用户可以看到的商品。显示用户的编号,姓名,年龄、商品名称。
SELECT U.ID,
U.NAME,
U.AGE,
P.NAME
FROM USER U , PRODUCT P , US_PRO PO
WHERE U.ID = PO.UID
AND P.ID = PO.PID
AND U.NAME IN ('张三','李四');
👇👇👇创建表结构和添加数据👇👇👇
-- 创建TB_TEST数据库
CREATE DATABASE TB_TEST;
USE TB_TEST;
-- 创建orderlist表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for orderlist
-- ----------------------------
DROP TABLE IF EXISTS `orderlist`;
CREATE TABLE `orderlist` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单主键id',
`number` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '订单编号',
`uid` int(11) NULL DEFAULT NULL COMMENT '用户id',
PRIMARY KEY (`id`) USING BTREE,
INDEX `fk_orderlist_tb_user_1`(`uid`) USING BTREE,
CONSTRAINT `fk_orderlist_tb_user_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of orderlist
-- ----------------------------
INSERT INTO `orderlist` VALUES (1, ' BM0001', 1);
INSERT INTO `orderlist` VALUES (2, 'BM0002', 1);
INSERT INTO `orderlist` VALUES (3, 'BM0003', 2);
INSERT INTO `orderlist` VALUES (4, 'BM0004', 2);
INSERT INTO `orderlist` VALUES (5, 'BM0005', 3);
INSERT INTO `orderlist` VALUES (6, 'BM0006', 3);
INSERT INTO `orderlist` VALUES (7, 'BM0007', 4);
INSERT INTO `orderlist` VALUES (8, 'BM0008', NULL);
SET FOREIGN_KEY_CHECKS = 1;
-- 创建user表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户姓名',
`age` int(11) NULL DEFAULT NULL COMMENT '用户年龄',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', 23);
INSERT INTO `user` VALUES (2, '李四', 24);
INSERT INTO `user` VALUES (3, '王五', 25);
INSERT INTO `user` VALUES (4, '赵六', 26);
INSERT INTO `user` VALUES (5, '邹七', 27);
SET FOREIGN_KEY_CHECKS = 1;
-- 创建product表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`cid` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `fk_product_category_1`(`cid`) USING BTREE,
CONSTRAINT `fk_product_category_1` FOREIGN KEY (`cid`) REFERENCES `category` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES (1, '华为手机', 1);
INSERT INTO `product` VALUES (2, '小米手机', 1);
INSERT INTO `product` VALUES (3, '联想电脑', 2);
INSERT INTO `product` VALUES (4, '苹果电脑', 2);
INSERT INTO `product` VALUES (5, '中华香烟', 3);
INSERT INTO `product` VALUES (6, '玉溪香烟', 3);
INSERT INTO `product` VALUES (7, '计生用品', NULL);
SET FOREIGN_KEY_CHECKS = 1;
-- 创建us_pro中间表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for us_pro
-- ----------------------------
DROP TABLE IF EXISTS `us_pro`;
CREATE TABLE `us_pro` (
`upid` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NULL DEFAULT NULL COMMENT '用户id',
`pid` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`upid`) USING BTREE,
INDEX `fk_us_pro_tb_user_1`(`uid`) USING BTREE,
INDEX `fk_us_pro_product_1`(`pid`) USING BTREE,
CONSTRAINT `fk_us_pro_product_1` FOREIGN KEY (`pid`) REFERENCES `product` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `fk_us_pro_tb_user_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of us_pro
-- ----------------------------
INSERT INTO `us_pro` VALUES (1, 1, 2);
INSERT INTO `us_pro` VALUES (2, 2, 1);
INSERT INTO `us_pro` VALUES (3, 3, 1);
INSERT INTO `us_pro` VALUES (4, 3, 3);
INSERT INTO `us_pro` VALUES (5, 2, 3);
INSERT INTO `us_pro` VALUES (6, 4, 2);
INSERT INTO `us_pro` VALUES (7, 3, 2);
INSERT INTO `us_pro` VALUES (8, 4, 1);
INSERT INTO `us_pro` VALUES (9, 5, 2);
INSERT INTO `us_pro` VALUES (10, 5, 5);
SET FOREIGN_KEY_CHECKS = 1;
-- 创建account表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for account
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT ' 账号id',
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '姓名',
`money` double NULL DEFAULT NULL COMMENT '余额',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO `account` VALUES (1, '张三', 1000);
INSERT INTO `account` VALUES (2, '李四', 1000);
INSERT INTO `account` VALUES (3, '王五', 5000);
-- 创建account_log
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for account_log
-- ----------------------------
DROP TABLE IF EXISTS `account_log`;
CREATE TABLE `account_log` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '日志id',
`operation` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作类型(INSERT,UPDATE,DELETE)',
`operation_time` timestamp NULL DEFAULT NULL COMMENT '操作时间',
`operation_id` int(11) NULL DEFAULT NULL COMMENT '操作表的id',
`operation_params` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作参数',
`bf_params` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作前的参数',
`af_params` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作后的参数',
`type` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '操作类型名(新增,更新,删除)',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of account_log
-- ----------------------------
INSERT INTO `account_log` VALUES (1, 'INSERT', '2021-12-18 15:27:58', 3, '{id=3,name=王五,money=3000}', '', '插入的值{id=3,name=王五,money=3000}', '新增');
INSERT INTO `account_log` VALUES (2, 'UPDATE', '2021-12-18 15:40:50', 3, '{id=3,name=王五,money=5000}', '修改前的值{id=3,name=王五,money=3000}', '修改后的值{id=3,name=王五,money=5000}', '更新');
SET FOREIGN_KEY_CHECKS = 1;
> > > MySQL基础
MySQL基础:https://blog.csdn.net/qq_51076413/article/details/122023535
> > > MySQL的多表操作查询
多表操作查询:https://blog.csdn.net/qq_51076413/article/details/122024324
> > > MySQL多表操作练习
MySQL多表操作练习:https://blog.csdn.net/qq_51076413/article/details/122024838
> > > MySQL视图、存储过程、函数、触发器
MySQL视图、存储过程、函数、触发器:https://blog.csdn.net/qq_51076413/article/details/122025461