sql学习笔记-1

创建我们所需的数据库表及其数据,(将代码保存为sql文件并执行)

/*
Navicat MySQL Data Transfer

Source Server         : Z
Source Server Version : 80011
Source Host           : localhost:3306
Source Database       : stroe

Target Server Type    : MYSQL
Target Server Version : 80011
File Encoding         : 65001

Date: 2023-07-24 20:38:03
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for account
-- ----------------------------
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` int(11) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `type` bit(1) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `point` int(11) DEFAULT NULL,
  `money` double DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `id_new` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of account
-- ----------------------------
INSERT INTO `account` VALUES ('1', '111', '111', '\0', '管理员', null, null, '2023-03-07 16:50:00', null);
INSERT INTO `account` VALUES ('2', '222', '222', '', '张三', '0', '350', '2023-03-11 16:00:18', null);
INSERT INTO `account` VALUES ('3', '333', '333', '', '李四', '0', '268.5', '2023-03-12 16:00:27', null);
INSERT INTO `account` VALUES ('4', '444', '444', '', '王五', '0', '956.3', '2023-03-13 16:00:37', null);

-- ----------------------------
-- Table structure for cart
-- ----------------------------
DROP TABLE IF EXISTS `cart`;
CREATE TABLE `cart` (
  `cart_id` int(11) DEFAULT NULL,
  `good_no` int(11) DEFAULT NULL,
  `num` int(11) DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of cart
-- ----------------------------
INSERT INTO `cart` VALUES ('1', '1', '2', '2', '2023-03-11 15:46:18');
INSERT INTO `cart` VALUES ('2', '2', '2', '3', '2023-03-12 15:46:26');
INSERT INTO `cart` VALUES ('3', '4', '10', '4', '2023-03-13 15:46:36');
INSERT INTO `cart` VALUES ('4', '6', '20', '3', '2023-03-12 15:46:52');
INSERT INTO `cart` VALUES ('5', '9', '2', '2', '2023-03-12 15:46:59');
INSERT INTO `cart` VALUES ('6', '2', '10', '4', '2023-03-13 15:55:31');

-- ----------------------------
-- Table structure for category
-- ----------------------------
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `no` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of category
-- ----------------------------
INSERT INTO `category` VALUES ('1', '零食');
INSERT INTO `category` VALUES ('2', '蔬菜');
INSERT INTO `category` VALUES ('3', '水果');
INSERT INTO `category` VALUES ('4', '电子产品');
INSERT INTO `category` VALUES ('5', '酒水');

-- ----------------------------
-- Table structure for goods
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
  `good_no` int(11) DEFAULT NULL COMMENT '商品编号',
  `goods_name` varchar(50) DEFAULT NULL,
  `cost` double DEFAULT NULL COMMENT '商品成本',
  `price` double DEFAULT NULL COMMENT '商品售价',
  `count` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `category_no` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES ('1', '火腿肠', '0.5', '2.5', '50', '2023-03-14 20:50:57', '1');
INSERT INTO `goods` VALUES ('2', '薯片', '1.5', '3.5', '30', '2023-03-11 15:14:28', '1');
INSERT INTO `goods` VALUES ('3', '鼠标垫', '20.5', '25.5', '20', '2023-03-08 15:14:41', '4');
INSERT INTO `goods` VALUES ('4', '萝卜', '0.8', '1.5', '50', '2023-03-09 15:14:51', '2');
INSERT INTO `goods` VALUES ('5', '白菜', '0.2', '0.8', '50', '2023-03-10 15:15:02', '2');
INSERT INTO `goods` VALUES ('6', '车厘子', '30', '50', '50', '2023-03-11 15:15:14', '3');
INSERT INTO `goods` VALUES ('7', '芒果', '5', '8', '50', '2023-04-12 15:15:21', '3');
INSERT INTO `goods` VALUES ('8', '五粮液', '1350', '1700', '60', '2023-03-13 15:15:28', '5');
INSERT INTO `goods` VALUES ('9', '鼠标', '40', '50', '20', '2023-03-14 15:15:36', '4');

我们进行如下的练习。

表的数据如下：

 

 

1.查询操作

2.

 

3.

 

 4.

5. 

6. 

7. 

8. 

9. 

 10.