SQL组队学习DAY3

最新推荐文章于 2024-08-05 17:39:02 发布
最新推荐文章于 2024-08-05 17:39:02 发布
阅读量169 收藏
点赞数
文章标签： sql 学习 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Serendipity_101/article/details/126457450
版权 
SELECT * FROM products;
CREATE TABLE shop_product
(shop_id    CHAR(4)       NOT NULL,
 shop_name  VARCHAR(200)  NOT NULL,
 product_id CHAR(4)       NOT NULL,
 quantity   INTEGER       NOT NULL,
 PRIMARY KEY (shop_id, product_id));

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0001',	30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0002',	50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0003',	15);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0002',	30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0003',	120);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0004',	20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0006',	10);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0007',	40);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0003',	20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0004',	50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0006',	90);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0007',	70);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000D',	'福冈',		'0001',	100);

DROP VIEW productsum;
CREATE VIEW productsum(product_type, count_product)
AS
SELECT product_type,count(*)
FROM products
GROUP BY product_type;
SELECT * FROM productsum;

CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
AS
SELECT product_type, sale_price, shop_name
FROM products, shop_product
WHERE products.product_id = shop_product.product_id;

SELECT * FROM view_shop_product;

SELECT product_type, cnt_product
FROM(
SELECT *
FROM (SELECT product_type, count(*) AS cnt_product
FROM products
GROUP BY product_type) AS product_sum
WHERE cnt_product = 4) AS product_sum2;

# 标量子查询,返回一个结果，一行或一列
SELECT product_id, product_name, sale_price, (SELECT avg(sale_price) FROM products) AS avg_sale_price
FROM products;
SELECT product_id, product_name, sale_price
FROM products
WHERE sale_price > (SELECT avg(sale_price) FROM products);

# 关联子查询
SELECT product_type, product_name, sale_price
FROM products AS P1
WHERE sale_price > (SELECT avg(sale_price) 
					FROM products AS P2
                    WHERE P1.product_type = P2.product_type
                    GROUP BY product_type);

# 练习
CREATE VIEW Viewpractice 
AS 
SELECT product_name, sale_price, regist_date
FROM products
WHERE sale_price >= 1000 AND regist_date = "2009-09-20";
SELECT * FROM Viewpractice;

INSERT INTO Viewpractice VALUES ("刀子", 300 ,"2009-11-02");  # 会报错，
SELECT * FROM products;  # product_id, product_name,product_type 设置了非空限制
# 通过视图插入数据也会向原表中插入数据，因此必须将非空字段都补齐;  但是，在试图只定义了三个字段，其他字段无法插入，导致原表中的数据无法被插入。这也是尽量避免通过视图修改数据的原因之一
INSERT INTO Viewpractice VALUES ('0009', "刀子", "办公用品", 300, NULL, "2009-11-02");  # 报错字段数不匹配，因为视图中只有三列

SELECT product_id, product_name, product_type, sale_price, (SELECT AVG(sale_price) FROM products) AS sale_price_avg
FROM products AS p1;

SELECT product_id, product_name, product_type, sale_price, (SELECT AVG(sale_price) FROM products AS p2
															WHERE p1.product_type = p2.product_type
															GROUP BY product_type) AS sale_price_avg_type
FROM products AS p1;

-- WHERE sale_price = (SELECT sale_price FROM products AS p2
-- 					 WHERE p1.product_type = p2.product_type
--                      GROUP BY product_type);

USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m NUMERIC(10,3),
 n INT,
 p INT);
 
START TRANSACTION; -- 开始事务
INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);
COMMIT;

SELECT * FROM samplemath;
SELECT m, ABS(m) as abs_col, n, p, mod(n,p) as mod_col, round(m,2) as round_col
FROM samplemath;

# 创建一个新的工作表用于文本函数
USE  shop;
DROP TABLE IF EXISTS samplestr;
CREATE TABLE samplestr
(str1 VARCHAR (40),
str2 VARCHAR (40),
str3 VARCHAR (40)
);
-- DML：插入数据
START TRANSACTION;
INSERT INTO samplestr (str1, str2, str3) VALUES ('opx',	'rt', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc', 'def', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('太阳',	'月亮', '火星');
INSERT INTO samplestr (str1, str2, str3) VALUES ('aaa',	NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (NULL, 'xyz', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('@!#$%', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('ABC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('aBC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc哈哈',  'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('micmic', 'i', 'I');
COMMIT;
-- 确认表中的内容
SELECT * FROM samplestr;
-- concat拼接函数，substring 字符串截取,replace(a,b,c):将a中的b字符串替换为c
SELECT str1, str2, str3, CONCAT(str1, str2, str3) AS concat_str,
LENGTH(str1) AS len_str1,
LOWER(str1) AS lower_str,
REPLACE(str1, str2, str3) AS rep_str,
SUBSTRING(str1 FROM 3 FOR 2) AS sub_str  # substring(a from b for c):将a字符串从第b个字符开始截取c个字符
FROM samplestr;
 # 按照索引截取substring_index
SELECT SUBSTRING_INDEX("WWW.SQL.COM", ".", 2);
SELECT SUBSTRING_INDEX("WWW.SQL.COM",".", -2);
# 获取中间字段
SELECT SUBSTRING("WWW.SQL.COM" FROM 5 FOR 3);   # 局限性很大，不适用
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX("WWW.SQL.COM", ".", 2), ".", -1);
# 重复函数 repeat
-- SELECT REPEAT("CAT*", 3);

# 日期函数
SELECT CURRENT_DATE;
SELECT CURRENT_TIME;
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_TIMESTAMP AS now, 
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day;

# 转化函数：cast, COALESCE 
SELECT CAST(-1.222 as SIGNED INTEGER) AS int_col, CAST("2001-01-11" AS DATE) AS date_col;
SELECT COALESCE(NULL, 11) as col_1, COALESCE(NULL, "CAT", NULL) AS col_2, COALESCE(NULL, NULL, NULL, "bba", "DDA")as col_3;
-- 该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值

# 谓词，返回值为真值（TRUE, FALSE, UNKNOWN）的函数
-- DDL ：创建表
CREATE TABLE samplelike
( strcol VARCHAR(6) NOT NULL,
  PRIMARY KEY (strcol)
);
-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO samplelike (strcol) VALUES ('abcddd');
INSERT INTO samplelike (strcol) VALUES ('dddabc');
INSERT INTO samplelike (strcol) VALUES ('abdddc');
INSERT INTO samplelike (strcol) VALUES ('abcdd');
INSERT INTO samplelike (strcol) VALUES ('ddabc');
INSERT INTO samplelike (strcol) VALUES ('abddc');
COMMIT; -- 提交事务
SELECT * FROM samplelike;

# LIKE:用于查找部分相同的字符串
SELECT * 
FROM samplelike
WHERE strcol LIKE "ddd%";  -- %表示0个或多个任意字符串，_代表一个字符
SELECT * FROM samplelike
WHERE strcol LIKE "%ddd%";
# 范围查询：between
SELECT product_name, sale_price
FROM products 
WHERE sale_price BETWEEN 500 AND 5000; # 闭区间
# is null, is not null 
SELECT product_name, purchase_price
FROM products
WHERE purchase_price is null;
# in: 多个or的结合体, not in:in的反面
SELECT product_name, purchase_price
FROM products
WHERE purchase_price IN (500,1000,5000);

-- DDL ：创建表
DROP TABLE IF EXISTS shopproduct;
CREATE TABLE shopproduct
(  shop_id CHAR(4)      NOT NULL,
 shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4)      NOT NULL,
  quantity INTEGER      NOT NULL,
PRIMARY KEY (shop_id, product_id) -- 指定主键
);
-- DML ：插入数据
START TRANSACTION; -- 开始事务
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT; -- 提交事务
SELECT * FROM shopproduct;
# 谓词in和子查询
SELECT product_name, sale_price 
FROM products
WHERE product_id IN (SELECT product_id 
					 FROM shopproduct
					 WHERE shop_id = "000C");
# 查找不在东京销售过的商品的售价
SELECT product_name, sale_price
FROM products
WHERE product_id NOT IN (SELECT product_id FROM shopproduct WHERE shop_id = "000A");
#谓词exists
-- 选取出大阪门店在售商品的销售单价
SELECT product_name, sale_price
FROM products AS p
WHERE EXISTS (SELECT *
			  FROM shopproduct AS s
			  WHERE s.shop_id = "000C" and s.product_id = p.product_id);
-- EXIST 通常会使用关联子查询作为参数。
SELECT product_name, sale_price
FROM products AS p
WHERE NOT EXISTS (SELECT * FROM shopproduct AS s
				  WHERE s.product_id = p.product_id AND s.shop_id = "000A");
                  
# case表达式：条件分支
SELECT product_name, 
	   CASE WHEN product_type = "衣服" then concat("A:",product_type)
			when product_type = "办公用品" then concat("B:",product_type)
			when product_type = "厨房用具" then concat("C:",product_type)
			ELSE NULL
	   END AS abc_product_type
FROM products;
-- 行方向的聚合  
SELECT product_type, sum(sale_price) AS sum_sale_price
FROM products
GROUP BY product_type;
-- 列方向的聚合
SELECT 
	   sum(CASE WHEN product_type = "衣服" then sale_price else 0 end) AS sum_clothes_price,
	   sum(case when product_type = "办公用品" then sale_price else 0 end) AS sum_price_office,
	   sum(case when product_type = "厨房用具" then sale_prie else 0 end) AS sum_price_kitchen
FROM products;


# 练习
-- 不一定
SELECT purchase_price FROM products;
SELECT sum(purchase_price) FROM products; # 含有空值可以进行求和运算
SELECT mod(purchase_price, 100) FROM products;

SELECT product_name, purchase_price
FROM products
WHERE purchase_price NOT IN (500, 2800, 5000);

SELECT product_name, purchase_price
FROM products
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
-- 没有任何结果返回，全空
-- 原因：null不能参与比较运算符，即与任何数据比较结果都为null;
-- NOT IN (500, 2800, 5000, NULL)相当于purchase_prise<>500 and <>2800 and <>5000 and <>null 
-- <>null 返回null, 任何表达式 and null，结果都为null，判定条件不成立，查询不出来数据。
-- not in后面不要跟null

SELECT 
COUNT(CASE WHEN sale_price <=1000 THEN product_id ELSE NULL END) AS low_price,
COUNT(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN product_id ELSE NULL END) AS mid_price,
COUNT(CASE WHEN sale_price > 3000 THEN product_id ELSE NULL END) AS high_price
FROM products;

Serendipity_101
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
练习题 4
m0_54046402的博客
08-25 238
练习题 4.1 找出 product 和 product2 中售价高于 500 的商品的基本信息。 SELECT * FROM product WHERE sale_price > 500 UNION SELECT * FROM product2 WHERE sale_price > 500; 4.2 借助对称差的实现方式, 求product和product2的交集。 SELECT * FROM product WHERE product_id NOT IN (SELECT product_id
SQL编程task03作业-复杂一点的查询
xpl_1620的博客
09-18 391
文章目录1 学习内容2 练习题-第一部分2.1 创建视图2.2 给视图插入数据2.3 使用标量子查询2.4 使用关联子查询3 练习题-第二部分3.1 含有NULL的运算3.2 IN语句3.3 CASE+IN语句 1 学习内容 MySQL-learning03 复杂一点的查询 DataWhale SQL组队学习 2 练习题-第一部分 product表格： 2.1 创建视图 创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用product（商品）表作为参照表，假设表中包含初始状
ch03复杂一点的查询
m0_70965436的博客
05-22 210
学习笔记 （1）视图 创建视图 Create view <视图名> as <select语句> 修改视图 Alter view <视图名> as <select 语句> 删除视图 drop view <视图名> （2）子查询 子查询分为嵌套子查询、标量子查询（只返回一个值）、关联子查询（将嵌套子查询内外两个查询通过一些标志连接起来，比如外部循环表as p1，内部循环的表as p2，筛选条件 p1.na...
组队学习sql之综合练习3
charie411的博客
12-21 135
基础及前5题的综合练习见：组队学习sql之基础+综合练习1 6~8题见：组队学习sql之综合练习2 MySQL Leetcode练习 Task06综合练习一共11题，本篇实现9~11。 将项目7中的employee表清空，重新插入以下数据（其实是多插入5,6两行）： 185. 部门工资前三高的所有员工 题目 +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------
DataWhale组队SQL组队学习连接汇总
Shirley_L77的博客
12-17 160
DAY2:https://blog.csdn.net/Shirley_L77/article/details/111240425 DAY1:https://blog.csdn.net/Shirley_L77/article/details/111178026 DAY0:https://blog.csdn.net/Shirley_L77/article/details/111148054
SQL编程-组队学习打卡task06-秋招秘籍ABC
sadbird1729的博客
07-26 626
leetcode精选习题和SQL经典问题
SQL编程-组队学习打卡task03-复杂一点的查询
sadbird1729的博客
07-17 701
视图，子查询，函数，谓词，case
DataWhale SQL 组队Day5—窗口函数
Shirley_L77的博客
12-23 114
1 知识点梳理 1.1 窗口函数 窗口函数的基本语法： --[ ] 中的内容可以省略 <窗口函数> OVER ([PARTITION BY <列名>] ORDER BY <排序用列名>) 1.2 窗口函数的的应用 1.2.1 基本语法 --PRECEDING（“之前”）， 将框架指定为 “截止到之前 n 行”，加上自身行 <窗口函数> OVER (ORDER BY &l
Datawhale线上组队学习汇总
老马的程序人生
12-02 2095
组队学习就是一群志同道合的小伙伴，一起学习讨论，一起克服拖延症，一起组队打boss。没有老师，没有教学，有的是一群热爱学习和渴望改变的人，交流学习，互促共进。 <font color='red'><b>如果您对哪门开源内容感兴趣，请在下方留言区写上课程的名称。我们就邀请该课程设计者开设该课程的组队学习。</b></font>
postgresql 双重排序后 重复项 标识次序
cuisidong1997的博客
08-04 204
在这个查询中，ROW_NUMBER()会为每个product_id分组内的行分配一个唯一的sale_sequence。PARTITION BY product_id表示按product_id分组，ORDER BY sale_date表示在每个分组内按sale_date排序。最外层的ORDER BY product_id, sale_date确定了查询结果的最终排序。一个常见的方法是使用ROW_NUMBER()窗口函数，它会为每个分组内的行分配一个唯一的序号。
力扣高频SQL 50题（基础版）第三十八题
m0_70882914的博客
08-02 439
1484.按日期分组销售产品
力扣SQL50 修复表中的名字 字符串函数
人言束负
08-03 315
【代码】力扣SQL50 修复表中的名字 字符串函数。
SQL时间盲注
最新发布
2301_78549931的博客
08-05 852
在注入时，information_schema库的作用就是获取 table_schema table_name, column_name .如果数据库名的第一个字符的ascii码值等于115，则页面沉睡3秒，如果数据库名的第一个字符的ascii码值不等于115，则页面不沉睡。这个库 就像时MYSQL的信息数据库，他保存着mysql 服务器所维护的所有其他的数据库信息， 包括了 库名，表名，列名。根据表名知道可能用户的账户和密码是在users表中，接下来我们就是得到该表下的字段名以及内容。
MyBatis 如何通过拦截器修改 SQL
Hello World
08-03 393
假如我们想实现多租户，或者在某些 SQL 后面自动拼接查询条件。在开发过程中大部分场景可能都是一个查询写一个 SQL 去处理，我们如果想修改最终 SQL 可以通过修改各个 mapper.xml 中的 SQL 来处理。但实际过程中我们可能穿插着 ORM 和 SQL 的混合使用，隐藏在代码中不容易被发现，还有假如项目中有很多很多的 SQL 我们不可能一个一个的去修改解决。这个时候我们就需要通过 mybatis 拦截 SQL 并且最终修改 SQL。实现Interceptor接口，并写相关逻辑。
【SQL Server】端口安全配置：SQL Server的安全最佳实践与防火墙规则配置
weixin_43298211的博客
08-03 938
在进行任何网络或数据库系统的部署时，确保安全是至关重要的。SQL Server，作为企业级数据库平台，提供了丰富且强大的安全功能。使用加密连接是保护 SQL Server 通信安全的重要手段。防火墙是 SQL Server 安全的第一道防线。完成上述步骤后，Windows 防火墙将允许端口 1434 上的流量，从而确保 SQL Server 可以接收客户端连接。命令配置 SQL Server 使用证书进行加密连接。在上面的示例中，先加载必要的 PowerShell 模块，定义了证书的拇指印。
力扣SQL50 2016年的投资 窗口函数
人言束负
08-03 305
👨‍🏫 参考题解
SQL中的去重
rookie636的博客
08-02 442
DISTINCT 关键字用于返回唯一不同的值，一般放在查询语句中的第一个字段前使用。DISTINCT会将NULL值也看做成一个不同的值。DISTINCT去重假如有一张员工表：需要获取所有不重复的员工薪资和年龄的组合，如下编写SQL即可。
SQL约束
weixin_58468790的博客
08-02 666
SQL约束，小白入门
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值