SQL学习笔记

最新推荐文章于 2024-04-22 09:02:54 发布
最新推荐文章于 2024-04-22 09:02:54 发布
阅读量8k 收藏 7
点赞数 1
分类专栏： 笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43730955/article/details/98228400
版权

https://blog.csdn.net/moguxiansheng1106/article/details/44258499

  1. Having与where的区别：https://baijiahao.baidu.com/s?id=1600513158500665764&wfr=spider&for=pc
  2. LEFT JOIN 关键字从左表（Websites）返回所有的行，即使右表（access_log）中没有匹配。
  3. 好评率是会员对平台评价的重要指标。现在需要统计2018年1月1日到2018年1月31日，用户’小明’提交的母婴类目"花王"品牌的好评率（好评率=“好评”评价量/总评价量）:
    用户评价详情表：a
    字段：id（评价id，主键），create_time（评价创建时间，格式’2017-01-01’）， user_name(用户名称)，goods_id(商品id，外键) ，
    sub_time（评价提交时间，格式’2017-01-01 23:10:32’），sat_name（好评率类型，包含：“好评”、“中评”、“差评”）
    商品详情表：
    b 字段：good_id（商品id，主键），bu_name（商品类目）, brand_name(品牌名称)
SELECT SUM(CASE

           WHEN sat_time = '好评' THEN 1

           ELSE 0

           END)/COUNT(sat_time) AS "好评率"

FROM a JOIN b ON a.good_id = b.good_id

WHERE user_name = '小明' AND sub_time BETWEEN ('2017-1-1') AND ('2017-1-31') AND b.bu_name = '母婴' AND b.brand_name ='花王'
  1. 考拉运营"小明"负责多个品牌的销售业绩，请完成：
    （1）请统计小明负责的各个品牌，在2017年销售最高的3天，及对应的销售额。
    销售表 a:
    字段：logday（日期，主键组），SKU_ID（商品SKU，主键组），sale_amt(销售额)
    商品基础信息表 b:
    字段：SKU_ID（商品SKU，主键）,bu_name（商品类目），brand_name(品牌名称)，user_name（运营负责人名称）
    （2）请统计小明负责的各个品牌，在2017年连续3天增长超过50%的日期，及对应的销售额。

(1)

select * from
(select b.brand_name, a.logday,row_number() OVER (PARTITION BY b.brand_name ORDER BY a.sale_amt desc) as num
from a join b on a.sku_id = b.sku_id
where year(a.logday)='2017' and b.user_name = '小明')c
where c.num <= 5
group by b.brand_name

(2)两表合并c->按品牌和时间分组，计算对应的销售额->前一天表和当天表按品牌合并->计算每个品牌在每一天的增长率->(未实现)
https://bbs.csdn.net/topics/392421965?page=1

create table a(
SKU_ID INT NOT NULL,
logday datetime NOT NULL,
sale_amt INT NOT NULL);
insert into a values (1,'2019-06-01',100);
insert into a values (2,'2019-06-01',200);
insert into a values (1,'2019-06-01',100);
insert into a values (4,'2019-06-01',300);
insert into a values (1,'2019-06-02',200);
insert into a values (2,'2019-06-02',150);
insert into a values (3,'2019-06-02',200);
insert into a values (4,'2019-06-02',100);
insert into a values (1,'2019-06-03',400);
insert into a values (3,'2019-06-03',80);
insert into a values (4,'2019-06-03',190);
insert into a values (4,'2019-06-04',390);
insert into a values (3,'2019-06-04',150);
insert into a values (1,'2019-06-05',150);
insert into a values (2,'2019-06-05',300);
insert into a values (3,'2019-06-05',200);
insert into a values (4,'2019-06-05',180);
insert into a values (2,'2019-06-05',300);
insert into a values (2,'2019-06-06',250);
insert into a values (2,'2019-06-06',350);

create table b(
SKU_ID INT NOT NULL,
brand_name varchar(12) NOT NULL,
user_name varchar(12) NOT NULL
);
insert into b values (1,'A','xm');
insert into b values (2,'A','xm');
insert into b values (3,'B','xm');
insert into b values (4,'B','xm');
with cte_1
as
(
select A.logday,B.brand_name, sum(A.sale_amt)as sum_amt
 from  A
 join  B on A.SKU_ID=B.SKU_ID
 where user_name='xm' 
 group by A.logday,B.brand_name),
cte_2
as
 (select logday, brand_name
 from cte_1 
)
select * from cte_2

在这里插入图片描述

  1. with as用法
    WITH AS短语，也叫做子查询部分（subquery factoring）,如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。
    with cr as( select CountryRegionCode from person);
    select * from cr;
    https://www.cnblogs.com/firstdream/p/7356481.html
    表有三个字段：id、node_name、parent_id。这个表中保存了一个树型结构，分三层：省、市、区。其中id表示当前省、市或区的id号、node_name表示名称、parent_id表示节点的父节点的id。现在有一个需求，要查询出某个省下面的所有市和区（查询结果包含省）
    SQL SERVER
create table t_tree
(
id int not null
,node_name varchar(50) not null
,parent_id int not null
,[description] varchar(255) null
)

insert into t_tree
(
id, node_name, parent_id
)

values
(1,'A',0),
(2 ,'B' ,1),
(3 ,'C' ,1),
(4 ,'D' ,2),
(5 ,'E' ,2),
(6 ,'F' ,2),
(7 ,'G' ,8)
with
district as
(
select * from t_tree where node_name=  'A'
union all
select a.* from t_tree a, district b
where a.parent_id = b.id
)
select * from district

https://www.w3school.com.cn/sql/sql_union.asp
在这里插入图片描述
6. INNER JOIN
INNER JOIN 与 JOIN 是相同的。INNER JOIN 关键字在表中存在至少一个匹配时返回行。
7. LEFT OUTER JOIN
left join 是 left outer join 的简写，两者含义一样的。一个LEFT OUTER JOIN包含“左”表中的所有记录，即使它与在此连接中指定的“右”表并不存在任何匹配。
8. PRIMARY KEY 约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。在表中，此主键可以包含单个或多个列（字段）。
9. 创建sql表
MYSQL5.6

-- borrowed from https://stackoverflow.com/q/7745609/808921
CREATE TABLE IF NOT EXISTS `docs` (
  `id` int(6) unsigned NOT NULL,
  `rev` int(3) unsigned NOT NULL,
  `content` varchar(200) NOT NULL,
  PRIMARY KEY (`id`,`rev`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`id`, `rev`, `content`) VALUES
  ('1', '1', 'The earth is flat'),
  ('2', '1', 'One hundred angels can dance on the head of a pin'),
  ('1', '2', 'The earth is flat and rests on a bull\'s horn'),
  ('1', '3', 'The earth is like a ball.');

  1. sql server中的real数据类型
    float 和 real 数据类型被称为近似数据类型。近似数值数据类型并不存储为许多数字指定的精确值，它们只储存这些值的最近似值。float 和 real 的使用遵循有关近似数值数据类型的 IEEE 754 规范。IEEE 754 规范提供四种舍入模式：舍入到最近、向上舍入、向下舍入以及舍入到零。Microsoft SQL Server 2005 使用向上舍入。
    decimal(numeric ) 用于精确存储数值（整型）
    money 用于精确存储数值（浮点型）
    float 和 real 不能精确存储数值

  2. 利用SQL创建表时的 NOT NULL 约束：

CREATE TABLE test 
( 
age int(10), 
sex varchar(20), 
name varchar(20) NOT NULL 
)

NOT NULL 约束强制列不接受 NULL 值。
约束，就是限制某些东西不能干什么，或者说不能是什么样子。

  1. ord1表 user_id,ord_id,ord_amt,create_time，act_user
    act1表 act_id，user_id,create_time
    第一问：每个活动类型所有用户的总订单额，订单数
    第二问：每个活动类型活动开始时间（第一个用户报名的时间）到今天，平均每天产生的订单数
    创建ord1表, act1表 (MY SQL/ MS SQL SERVER)
create table ord1(
user_id INT NOT NULL,
ord_id  INT NOT NULL,
ord_amt REAL NOT NULL,
create_time datetime NOT NULL
);
INSERT INTO ord1 VALUES(1,10,100,'2019-07-02 10:00:01');
INSERT INTO ord1 VALUES(1,11,120,'2019-07-01 11:00:01');
INSERT INTO ord1 VALUES(1,11,120,'2019-07-02 11:00:01');
INSERT INTO ord1 VALUES(1,12,150,'2019-07-03 10:00:01');
INSERT INTO ord1 VALUES(2,13,200,'2019-07-02 10:00:01');
INSERT INTO ord1 VALUES(2,14,300,'2019-07-05 11:00:01');
INSERT INTO ord1 VALUES(3,15,100,'2019-07-02 10:00:01');
INSERT INTO ord1 VALUES(3,16,300,'2019-07-03 10:00:01');
INSERT INTO ord1 VALUES(4,17,200,'2019-07-02 10:00:01');
INSERT INTO ord1 VALUES(5,18,100,'2019-07-02 10:00:01');

create table act1(
act_id VARCHAR(15) NOT NULL,
user_id  INT NOT NULL,
create_time datetime NOT NULL
);
INSERT INTO act1 VALUES('A',1,'2019-07-02 10:00:01');
INSERT INTO act1 VALUES('A',2,'2019-07-02 11:00:01');
INSERT INTO act1 VALUES('A',3,'2019-07-02 10:00:01');
INSERT INTO act1 VALUES('B',4,'2019-07-02 10:00:01');
INSERT INTO act1 VALUES('B',5,'2019-07-02 10:00:01');
INSERT INTO act1 VALUES('C',6,'2019-07-02 10:00:01');

统计每个活动类型所有用户的总订单额，订单数

select 
	b.act_id
    ,count(ord_id)
    ,sum(ord_amt)
from ord1 a
join act1 b
on a.user_id = b.user_id
where a.create_time >= b.create_time
group by b.act_id;

每个活动类型活动开始时间（第一个用户报名的时间）到今天，平均每天产生的订单数

select 
	b.act_id
    ,sum(ord_amt)/DATEDIFF(day, min(a.create_time),'2019-7-28')  as avg
from ord1 a
left join act1 b
on a.user_id = b.user_id
where a.create_time >= b.create_time
group by b.act_id;
  1. log表（userid，opr_type,log_time)
    第一问：每天的访客数量
    第二问：每天执行opr_type=A到B，先A后B，而且必须紧紧挨着的用户数
    创建数据表
create table logtable(
user_id INT NOT NULL,
opr_type VARCHAR(15) NOT NULL,
log_time datetime NOT NULL);
insert into logtable values (1,'A','2019-06-01 10:10:01');
insert into logtable values (1,'B','2019-06-01 10:11:01');
insert into logtable values (1,'C','2019-06-01 10:12:01');
insert into logtable values (9,'A','2019-06-01 10:13:01');
insert into logtable values (9,'B','2019-06-01 10:14:01');
insert into logtable values (2,'A','2019-06-01 10:10:02');
insert into logtable values (2,'C','2019-06-01 10:11:01');
insert into logtable values (2,'B','2019-06-01 10:12:01');
insert into logtable values (3,'A','2019-06-02 10:10:01');
insert into logtable values (3,'B','2019-06-02 10:11:01');
insert into logtable values (4,'A','2019-06-03 10:10:01');
insert into logtable values (4,'C','2019-06-03 10:11:01');
insert into logtable values (4,'B','2019-06-03 10:12:01');

每天的访客数量

select date_format(log_time,'%y-%m-%d')
       ,count(distinct user_id)
from logtable 
group by date_format(log_time,'%y-%m-%d');

每天执行opr_type=A到B，先A后B，而且必须紧紧挨着的用户数
思路：增加新标签usertime(同user_id下大于当前log_id的注册时间的最小值), 并将其加入原数据表中；再通过特定标签进行右连接以保证A到B，先A后B，而且必须紧紧挨着的。最后通过注册时间进行分组，利用count（）计算符合条件的用户数。

select date_format(t1.log_time,'%y-%m-%d')
	   ,count(distinct t1.user_id)
from (
select *,(select min(log_time) from logtable l2 where l2.user_id = l1.user_id and l2.log_time > l1.log_time) as usetime
from logtable as l1
) as t1
right join logtable as t2
on  t2.user_id=t1.user_id
where t2.opr_type = 'B'
and t1.opr_type = 'A'
and t2.log_time = t1.usetime
group by date_format
最低0.47元/天 解锁文章
小力子～
关注
  • 1
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql 商品规格表_shop数据库表设计，整理一下商品规格相关表的字段说明
weixin_39973492的博客
01-18 2966
商品信息表首先绑定商品分类表，其中商品信息表中cid为分类信息表的id。商品图片对对应绑定商品信息表id与商品信息表一一对应。复杂的表就是商品规格绑定表，此表共对应表单有3张表，分别为：商品信息表、规格名称表、规格属性表。对在规格名称表及属性表为一对多的关系。其中在规格绑定表中product_id 为商品信息表直接绑定商品信息、properties为分别绑定对应的规格名称—规格属性表、此处为一对多...
商品表有哪些字段_【Tableau小钉子】1212：如何实现商品关联分析？（购物篮分析）...
weixin_39771301的博客
12-16 650
【Tableau小钉子】1212：如何实现商品关联分析？(购物篮分析)今天在做Tableau技能培训时，有个学员提到了一个问题：学员的企业是个连锁超市，想知道在Tableau中如何实现关联分析，场景是想知道超市中指定的商品与其它商品的关联度，具体的说就是想知道，购买A商品的同时，客户还会买什么商品？同时，还想知道与A商品关联度最高的B商品，反过来看看B商品关联度最高的又是什么商品？网上关...
商品领域十二张基础表设计思路与实现
最新发布
LoveSummer
04-22 1012
本文通过介绍类目、属性、品牌、SPU、SKU、SN 引出商品十二张基础表：一级类目表二级类目表三级类目表属性表属性值表类目与属性关系表品牌表SPU 表SKU 表SN 表SPU 与属性关系表SKU 与属性关系表这些十二张基础表从不同侧面描述了商品信息，其它复杂场景可以在这些基础表上进行扩展。同时在第五章节我们讨论了五个延伸知识，希望本文对大家有所帮助。
Mysql基础
Mr_ccy的博客
01-08 480
MySql进阶 1.单表储存的问题 假如要增加一个商品分类,但是分类里没有商品 要修改分类信息,修改的数据庞大,影响性能 解决方案: 拆表: 一个商品信息表product,一个分类信息表category 2.多表储存的问题 拆分成两张表： category：只存储分类信息 product：只存储商品信息，商品所属分类使用了字段cid，字段值就是所属分类的id 解决的问...
商品表有哪些字段_商品首页模板index.html的数据填入
weixin_39611043的博客
12-15 884
模型类的字段改一下G:dailyfreshappsgoodsmodels.py让url变为一个字符串类型先来填入商品分类找到新鲜水果的代码块删掉其它的只留一个做为模子遍历商品种类的查询结果根据商品表字段填入数据填入后的效果关注forloop.counter效果轮播商品的数据填入先找到模板中的位置删掉重复的，只留一个做模子开始遍历后端传来的一步步的操作给图片添加链接，加上a标签活动页面遍历看后端传过...
商品表有哪些字段_商品详情页设计
weixin_39846289的博客
12-06 2095
商品详情页的设计需要考虑的问题​ 1.考虑高并发的问题​ 2.考虑商品数量规模的问题，商品详情页的数据规模，数据多，重量大商品系统需要保存哪些数据​ 如果将这些数据设计一张商品表全部放进去，撑不了多少数据量和并发商品基本信息怎么保存​ 1.基本信息，比如商品主副标题，价格，颜色，可以用一张表来保存商品的基本信息。设计商品信息基本表的时候，一定要保留商...
SQL学习笔记(pdf)
03-17
这份"SQL学习笔记"涵盖了SQL的基础概念、语法以及高级特性，是学习数据库管理和数据分析的宝贵资料。 1. **SQL基础** - 数据库概念：了解什么是数据库，它的作用以及数据库管理系统（DBMS）如何工作。 - SQL简介...
sql学习笔记
08-31
sql练习中易错笔记，order by ，group by，distinct，内连接，外连接等，一些mysql和oracle使用的区别：sql4种连接 两个表中有相同的列，根据共有的列值匹配行 left join左外连接，不管左表是否在右表中有匹配行，都...
SQL学习笔记.docx
09-08
"SQL学习笔记" 本文档是关于 SQL 学习笔记的总结，涵盖了 SQL 的基本概念、函数、语法、应用场景等方面。下面是对标题、描述、标签和部分内容的详细解释： SQL 基本概念 SQL（Structured Query Language）是一种...
初识 SQL
chenjadeli的博客
08-16 250
初识 SQL SQL是为操作数据库而开发的语言。国际标准化组织（ISO）为 SQL 制定了相应的标准，以此为基准的SQL 称为标准 SQL。 完全基于标准 SQL 的 RDBMS 很少，通常需要根据不同的 RDBMS 来编写特定的 SQL 语句，原则上，本课程介绍的是标准 SQL 的书写方式。 根据对 RDBMS 赋予的指令种类的不同，SQL 语句可以分为以下三类. DDL DDL（Data Definition Language，数据定义语言） 用来创建或者删除存储数据用的数据库以及数据库中的表等对象
（二）购物商城数据库设计-商品表设计
热门推荐
axe的专栏
05-29 10万+
大家好，今天我们来设计一下购物商城的商品表。 我们的目标是表结构能够满足下面这张图的搜索： 在设计表之前，我们先来了解下商品中的一个概念：SPU和SKU SPU SPU(Standard Product Unit)：标准化产品单元。是商品信息聚合的最小单位，是一组可复用、易检索的标准化信息的集合，该集合描述了一个产品的特性。通俗点讲，属性值、特性相同的商品就可以称为一个SPU。 SK...
电商系统，商品属性表和功能设计，可用于各种实体的属性
wuda0112的专栏
09-05 2694
简介 定义一种通用的属性体系,可以表示任何实体的属性,同时也像数据库列一样,可以为属性指定数据类型.在电商系统中, 每个商品都有多个属性,并且这些属性不能提前设定,它们以无模式的key/value形式存在,我们可以简单的只保存key/value， 也可以把这种需求抽象成更通用，功能更强大的属性体系,这样就可以适用更多的场景. 数据库 property_key: 属性key property_key_definition：属性key定义，就好像MySQL column definition pr
电商项目数据库设计 | 参考京东商城详细讲解商品数据库设计
weixin_42046751的博客
06-30 2万+
围绕商品设计表 之前也写过类似的数据库设计方案，这一篇是为大家详细讲解参考京东商城围绕商品怎么来设计数据库，需要关注的细节很多，对字段进行详解，结合功能实现分析每一个字段设计的意义 大家看完这篇文章后可以看看前面四篇文章电商项目数据库设计方案，跟这一篇有点不同，之前是大多是参照苏宁易购来设计的 我们在设计数据库的时候，大多都是按照我们自己的实际需求来设计，提供这几篇仅为大家参考学习，能够举一反三参与到项目实战中，希望大家可以学到东西，同时，文章内容如果有错误的位置希望大家可以指正，共同学习。 接下来
数据库设计之商品表分析1
多凡的博客
08-26 5572
1. 思路 一个全品类的电商网站，因此商品的种类繁多，每一件商品，其属性又有差别。为了更准确描述商品及细分差别，抽象出两个概念：SPU和SKU。 1.1 SPU和SKU联系 SPU：Standard Product Unit （标准产品单位） ，一组具有共同属性的商品集 SKU：Stock Keeping Unit（库存量单位），SPU商品集因具体特性不同而细分的每个商品 以下图为例： 本页的...
商品表有哪些字段_Axure教程：商品列表及排序筛选
weixin_39837041的博客
12-17 361
本文带大家一起来看一下Axure中如何商品列表及排序筛选，enjoy~喜欢网购的小伙伴们可能会发现这样一种现象——我们在电商平台浏览商品的时候，这些商品会以方块格子的形式展示一个个商品，每个格子的格式都是一样的，都包含了商品图片，价格以及评价数量等这些信息，而且在格子中的位置都是固定的，唯一不同的就是对应的信息值不一样。另外，用户可以对这些商品列表按照价格排序以及筛选条件来过滤数据。如果觉得光看文...
甲骨文数据库oracle个人学习（sql部分）.docx
06-20
"这篇文档是关于甲骨文数据库Oracle的学习，特别是SQL部分。作者BYBINGOSUMMER分享了从基础的SQL*PLUS工具到各种SQL查询语句的使用，涵盖了连接运算符、去重、描述表、WHERE子句、BETWEEN、IN、LIKE、IS NULL等条件...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值