数据库选择

本篇对SQL进行语法及实例的讲解，是基于MySQL 8.0.34版本来进行的，在开始学习前，前提前安装好数据库，可以选择MySQL5.7 及以上的数据库，或者MS SQLServer, Oracle 都可以。

涉及的演示数据库脚本

建立数据库

在要操作的数据库软件中，创建一个demo的数据库；

然后按照下面的步骤建立相应的表。

第一个是member表：

表结构：

对应的建表语句是：

CREATE TABLE `member` (
  `member_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `member_code` char(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '会员编号',
  `password` varchar(128) NOT NULL DEFAULT '' COMMENT '密码',
  `nick_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '呢称',
  `remarks` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '个人介绍',
  `location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '居住地',
  `status` int(2) unsigned NOT NULL DEFAULT '1' COMMENT '状态（0-冻结，1-正常）',
  PRIMARY KEY (`member_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8 COMMENT='会员信息表';

对应的数据初始化SQL为：

INSERT INTO `demo`.`member`(`member_id`, `member_code`, `password`, `nick_name`, `remarks`, `location`, `status`) VALUES (1010, '10001', '112331', '张三', 'xx', '广东', 1);
INSERT INTO `demo`.`member`(`member_id`, `member_code`, `password`, `nick_name`, `remarks`, `location`, `status`) VALUES (1011, '10002', '112333', '李四', 'xxx', '北京', 1);
INSERT INTO `demo`.`member`(`member_id`, `member_code`, `password`, `nick_name`, `remarks`, `location`, `status`) VALUES (1012, '10003', '1234567', '王五', 'xxx', '广东', 1);
INSERT INTO `demo`.`member`(`member_id`, `member_code`, `password`, `nick_name`, `remarks`, `location`, `status`) VALUES (1013, '10004', '112335', '张飞', 'xxx', '广东', 1);
INSERT INTO `demo`.`member`(`member_id`, `member_code`, `password`, `nick_name`, `remarks`, `location`, `status`) VALUES (1014, '10005', '112336', '关习', 'xxx', '上海', 1);
INSERT INTO `demo`.`member`(`member_id`, `member_code`, `password`, `nick_name`, `remarks`, `location`, `status`) VALUES (1015, '10006', '112337', '关平', 'xxx', '湖南', 1);
INSERT INTO `demo`.`member`(`member_id`, `member_code`, `password`, `nick_name`, `remarks`, `location`, `status`) VALUES (1016, '10007', '112338', '小李', 'xxx', '广东', 1);
INSERT INTO `demo`.`member`(`member_id`, `member_code`, `password`, `nick_name`, `remarks`, `location`, `status`) VALUES (1017, '10008', '112339', '韩飞', 'xxx', '深圳', 1);
INSERT INTO `demo`.`member`(`member_id`, `member_code`, `password`, `nick_name`, `remarks`, `location`, `status`) VALUES (1018, '10009', '112310', '刘备', 'xxx', '三国', 1);
INSERT INTO `demo`.`member`(`member_id`, `member_code`, `password`, `nick_name`, `remarks`, `location`, `status`) VALUES (1019, '10010', '112312', '吴越', 'xx', '广西', 1);
INSERT INTO `demo`.`member`(`member_id`, `member_code`, `password`, `nick_name`, `remarks`, `location`, `status`) VALUES (1020, '10011', '112312', '韩信', 'xxx', '南京', 1);
INSERT INTO `demo`.`member`(`member_id`, `member_code`, `password`, `nick_name`, `remarks`, `location`, `status`) VALUES (1021, '10012', '112331', '张包', 'xx', '广东', 1);

第二表：product表

表结构：

建表语句：

CREATE TABLE `product` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'pk',
  `product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '产品名称',
  `english_name` varchar(255) DEFAULT NULL COMMENT '英文名',
  `price` decimal(10,2) DEFAULT NULL COMMENT '价格',
  `category_id` int(11) DEFAULT NULL COMMENT '分类',
  `created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='产品表';

数据初始化SQL：

INSERT INTO `demo``product`(`id`, `product_name`, `english_name`, `price`, `category_id`, `created_time`) VALUES (1, '香蕉', 'Banana', 10.00, 1, '2023-10-22 11:44:18');
INSERT INTO `demo`.`product`(`id`, `product_name`, `english_name`, `price`, `category_id`, `created_time`) VALUES (2, '苹果', 'Apple', 15.00, 1, '2023-10-22 11:44:34');
INSERT INTO `demo`.`product`(`id`, `product_name`, `english_name`, `price`, `category_id`, `created_time`) VALUES (3, '桃子', 'Peach', 9.00, 1, '2023-10-22 11:44:44');
INSERT INTO `demo`.`product`(`id`, `product_name`, `english_name`, `price`, `category_id`, `created_time`) VALUES (4, '梨子', 'Pear', 8.00, 1, '2023-10-22 11:45:04');
INSERT INTO `demo`.`product`(`id`, `product_name`, `english_name`, `price`, `category_id`, `created_time`) VALUES (5, '鸡蛋', 'Eggs', 3.00, 2, '2023-10-22 11:45:16');
INSERT INTO `demo`.`product`(`id`, `product_name`, `english_name`, `price`, `category_id`, `created_time`) VALUES (6, '鸭蛋', 'Duck\'s egg', 4.00, 2, '2023-10-22 11:45:27');
INSERT INTO `demo`.`product`(`id`, `product_name`, `english_name`, `price`, `category_id`, `created_time`) VALUES (7, '猪肉', 'Pork', 12.00, 2, '2023-10-22 11:45:50');
INSERT INTO `demo`.`product`(`id`, `product_name`, `english_name`, `price`, `category_id`, `created_time`) VALUES (8, '花生油', 'Peanut oil', 20.00, 3, '2023-10-22 11:46:09');
INSERT INTO `demo`.`product`(`id`, `product_name`, `english_name`, `price`, `category_id`, `created_time`) VALUES (9, '面条', 'Nooddle', 2.00, 4, '2023-10-22 11:46:20');
INSERT INTO `demo`.`product`(`id`, `product_name`, `english_name`, `price`, `category_id`, `created_time`) VALUES (10, '笔记本', 'Notebook', 15.00, 5, '2023-10-22 11:46:34');