表结构及其数据
-- MySQL dump 10.13 Distrib 8.0.30, for Win64 (x86_64)
--
-- Host: localhost Database: store
-- ------------------------------------------------------
-- Server version 8.0.30
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `goods`
--
DROP TABLE IF EXISTS `goods`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `goods` (
`good_no` int DEFAULT NULL COMMENT '商品编号',
`goods_name` varchar(50) DEFAULT NULL COMMENT '商品名称',
`cost` double DEFAULT NULL COMMENT '商品成本',
`price` double DEFAULT NULL COMMENT '商品售价',
`count` int DEFAULT NULL COMMENT '商品库存',
`create_time` datetime DEFAULT NULL COMMENT '商品上架时间',
`category_no` int DEFAULT NULL COMMENT '商品分类编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `goods`
--
LOCK TABLES `goods` WRITE;
/*!40000 ALTER TABLE `goods` DISABLE KEYS */;
INSERT INTO `goods` VALUES (1,'火腿肠',0.5,2.5,50,'2023-07-21 19:20:41',1),(2,'薯片',1.5,3.5,30,'2023-07-21 19:20:41',1),(3,'鼠标垫',20.5,25.5,20,'2023-07-21 19:20:41',4),(4,'萝卜',0.8,1.5,50,'2023-07-21 19:20:41',2),(5,'白菜',0.2,0.8,50,'2023-07-21 19:20:41',2),(6,'车厘子',30,50,50,'2023-07-21 19:20:41',3),(7,'芒果',5,8,50,'2023-07-21 19:20:41',3),(8,'五粮液',1350,1700,60,'2023-07-21 19:20:41',5),(9,'鼠标',40,50,20,'2023-07-21 19:20:41',4);
/*!40000 ALTER TABLE `goods` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `account`
--
DROP TABLE IF EXISTS `account`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `account` (
`phone` varchar(11) DEFAULT NULL COMMENT '用户手机号',
`password` varchar(50) DEFAULT NULL COMMENT '用户密码',
`type` bit(1) DEFAULT NULL COMMENT '用户类型0管理员1会员',
`name` varchar(50) DEFAULT NULL COMMENT '用户名',
`point` int DEFAULT NULL COMMENT '用户积分',
`money` double DEFAULT NULL COMMENT '用户余额',
`create_time` datetime DEFAULT NULL COMMENT '用户创建时间',
`id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `account`
--
LOCK TABLES `account` WRITE;
/*!40000 ALTER TABLE `account` DISABLE KEYS */;
INSERT INTO `account` VALUES ('111','111',_binary '\0','管理员',NULL,NULL,'2023-07-21 19:28:44',1),('222','222',_binary '','张三',0,350,'2023-07-21 19:33:14',1),('333','333',_binary '','张三',0,268.5,'2023-07-21 19:33:14',1),('444','444',_binary '','王五',0,956.3,'2023-07-21 19:33:14',1);
/*!40000 ALTER TABLE `account` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `cart`
--
DROP TABLE IF EXISTS `cart`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `cart` (
`cart_id` int DEFAULT NULL,
`goods_no` int DEFAULT NULL COMMENT '商品编号',
`num` int DEFAULT NULL COMMENT '商品数量',
`account_id` int DEFAULT NULL COMMENT '用户编号',
`create_time` datetime DEFAULT NULL COMMENT '加入购物车时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `cart`
--
LOCK TABLES `cart` WRITE;
/*!40000 ALTER TABLE `cart` DISABLE KEYS */;
INSERT INTO `cart` VALUES (1,1,2,2,'2023-07-21 19:39:15'),(2,2,2,3,'2023-07-21 19:39:15'),(3,4,10,4,'2023-07-21 19:39:15'),(4,6,20,3,'2023-07-21 19:39:15'),(5,9,2,2,'2023-07-21 19:39:15'),(6,2,10,4,'2023-07-21 19:39:15');
/*!40000 ALTER TABLE `cart` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `category`
--
DROP TABLE IF EXISTS `category`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `category` (
`no` int DEFAULT NULL COMMENT '商品分类编号',
`name` varchar(50) DEFAULT NULL COMMENT '商品分类别名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `category`
--
LOCK TABLES `category` WRITE;
/*!40000 ALTER TABLE `category` DISABLE KEYS */;
INSERT INTO `category` VALUES (1,'零食'),(2,'蔬菜'),(3,'水果'),(4,'电子产品'),(5,'酒水');
/*!40000 ALTER TABLE `category` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2023-07-21 22:29:48
Mysql
练习题
火腿肠被谁买了
mysql> select goods.goods_name,account.name from account,cart,goods
-> where
-> cart.goods_no = goods.good_no
-> and
-> cart.account_id = account.id
-> and
-> goods.goods_name = '火腿肠';
+------------+------+
| goods_name | name |
+------------+------+
| 火腿肠 | 张三 |
+------------+------+
零食被谁买了
mysql> select goods.goods_name,account.name from account,goods,cart,category
-> where
-> cart.goods_no = goods.good_no
-> and
-> cart.account_id = account.id
-> and
-> goods.category_no = category.no
-> and
-> category.name = '零食';
+------------+------+
| goods_name | name |
+------------+------+
| 火腿肠 | 张三 |
| 薯片 | 李四 |
| 薯片 | 王五 |
+------------+------+
张三买了那些商品
mysql> select goods.goods_name from account,goods,cart
-> where
-> cart.account_id = account.id
-> and
-> cart.goods_no = goods.good_no
-> and
-> account.name = '张三';
+------------+
| goods_name |
+------------+
| 火腿肠 |
| 鼠标 |
+------------+
各个用户分别买了什么商品
mysql> select account.name,sum(cart.num*goods.price) as 花费 from account,goods,cart,category
-> where
-> cart.goods_no = goods.good_no
-> and
-> cart.account_id = account.id
-> and
-> goods.category_no = category.no
-> group by account.name;
+------+------+
| name | 花费 |
+------+------+
| 李四 | 1007 |
| 王五 | 50 |
| 张三 | 155 |
+------+------+
求购买购物车里面的商品后,用户还有多少余额
#先看它买了什么
select goods.goods_name,goods.price,account.name,cart.num from account,goods,cart
-> where
-> cart.goods_no = goods.good_no
-> and
-> cart.account_id = account.id;
+------------+-------+------+------+
| goods_name | price | name | num |
+------------+-------+------+------+
| 火腿肠 | 2.5 | 张三 | 2 |
| 薯片 | 3.5 | 王五 | 10 |
| 薯片 | 3.5 | 李四 | 2 |
| 萝卜 | 1.5 | 王五 | 10 |
| 车厘子 | 50 | 李四 | 20 |
| 鼠标 | 50 | 张三 | 3 |
+------------+-------+------+------+
#再看他的花费
mysql> select account.name,sum(cart.num*goods.price) as 花费 from account,goods,cart,category
-> where
-> cart.goods_no = goods.good_no
-> and
-> cart.account_id = account.id
-> and
-> goods.category_no = category.no
-> group by account.name;
+------+------+
| name | 花费 |
+------+------+
| 李四 | 1007 |
| 王五 | 50 |
| 张三 | 155 |
+------+------+
#再看他的余额
mysql> select account.name,(account.money-sum(cart.num*goods.price)) as 余额 from account,goods,cart,category
-> where
-> cart.goods_no = goods.good_no
-> and
-> cart.account_id = account.id
-> and
-> goods.category_no = category.no
-> group by account.name,account.money;
+------+--------+
| name | 余额 |
+------+--------+
| 李四 | -738.5 |
| 王五 | 906.3 |
| 张三 | 195 |
+------+--------+
3 rows in set (0.00 sec)
加上购物车东西全部销售,周几的营业额最高
mysql> select cart.create_time,(cart.num*goods.price) as 销售额 from goods,cart
-> where
-> cart.goods_no = goods.good_no
-> order by 销售额 desc;
+---------------------+--------+
| create_time | 销售额 |
+---------------------+--------+
| 2023-07-25 19:39:15 | 1000 |
| 2023-07-26 19:39:15 | 150 |
| 2023-07-27 19:39:15 | 35 |
| 2023-07-23 19:39:15 | 15 |
| 2023-07-22 19:39:15 | 7 |
| 2023-07-21 19:39:15 | 5 |
+---------------------+--------+
张三在什么时候购物
mysql> select cart.create_time from account,cart
-> where
-> cart.account_id = account.id
-> and
-> account.name = '张三';
+---------------------+
| create_time |
+---------------------+
| 2023-07-21 19:39:15 |
| 2023-07-26 19:39:15 |
+---------------------+
2 rows in set (0.00 sec)
商品剩多少库存
mysql> select goods.goods_name,(goods.count-sum(cart.num)) as 剩余 from goods,cart
-> where
-> cart.goods_no = goods.good_no
-> group by goods.goods_name,goods.count;
+------------+------+
| goods_name | 剩余 |
+------------+------+
| 火腿肠 | 48 |
| 薯片 | 18 |
| 萝卜 | 40 |
| 车厘子 | 30 |
| 鼠标 | 17 |
+------------+------+
购物车里的商品销售后,赚了多少钱
mysql> select sum(cart.num*goods.price-cart.num*goods.cost) as 赚了
-> from goods,cart
-> where
-> cart.goods_no = goods.good_no;
+------+
| 赚了 |
+------+
| 465 |
+------+
求哪个商品利润率最高
利润率 =(价格-成本 )/成本
mysql> select goods.goods_name,round((goods.price-goods.cost)/goods.cost,2) as 利润 from goods
-> order by 利润 desc;
+------------+------+
| goods_name | 利润 |
+------------+------+
| 火腿肠 | 4 |
| 白菜 | 3 |
| 薯片 | 1.33 |
| 萝卜 | 0.87 |
| 车厘子 | 0.67 |
| 芒果 | 0.6 |
| 五粮液 | 0.26 |
| 鼠标 | 0.25 |
| 鼠标垫 | 0.24 |
+------------+------+
求2023年3月12日前一周上架的商品
mysql> select goods.goods_name,goods.create_time from goods
-> where
-> goods.create_time between '2023-03-12' and '2023-03-5';
Empty set (0.00 sec)
Mysql
查询笔记已经错误常见
安关键字排序—ORDER BY
- 使用order by 语句来实现排序
- 排序可以针对一个字段或者多个字段
ASC
:升序排序,默认就是升序排序DESC
降序排序- order by 排序语法
- SELECT
column1,colum2
… from table_name order by 前面有几个列,order by 后面就得写几个列,如果不写够的时候就会报错only_full_group_by(only_full_group_by是MySQL的一个SQL_MODE,
它的目的是确保在GROUP BY查询中,SELECT列表中的非聚合列要么在GROUP BY子句中列出,要么作为聚合函数的参数。)
多个条件排序
select colum1,colum2,colum3 from tablename order by colum1,colum2 asc,colum3 asc;
对结果进行分组—group by
-
使用group by对结果进行分组
-
通常使用聚合函数一起使用
-
可以按一个结果货值多个结果进行分组
-
语法结构
-
SELECT column_name, aggregate_function(column_name)FROM table_name WHERE column_name operator valueGROUP BY column_name;
-
在这个查询中,您需要将以下部分替换为实际的表名、列名、聚合函数和条件:
column_name
:需要选择和汇总的列名。aggregate_function
:要应用的聚合函数,例如SUM、AVG、COUNT等。table_name
:表的名称。operator
:条件运算符,例如等于(=)、大于(>)、小于(<)等。value
:与列进行比较的值。
请注意,在使用聚合函数时,经常需要配合使用GROUP BY子句,以便对数据进行分组计算。这将根据指定的列名对结果进行分组,并为每个分组计算聚合函数的结果。
确保在实际使用时使用正确的表名、列名和条件,以及适当的聚合函数,以满足您的查询需求。
显示结果条目----limit
- 只返回select查询结果的第一行或者前几行
- 使用limit语句限制条目
- 语法结构:
select
colum1,colum2...from tablename limit [从第几条开始显示],[每一页显示几条];
通配符
- 通配符通常配合where来使用
%
:表示一个或多个或者没有-
表示一个或者没有- 语法:
select * from tablename where tablecolum like "%fds%"