【MySQL多表操作练习】

多表操作练习

> > > 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