SQL查漏补缺(04)

一、基础知识

1.SQL中的DUAL表

DUAL表是Oracle系统中对所有用户可用的一个实际存在的1行1列的表，这个表不能用来存储信息，在实际应用中仅用来执行SELECT语句。可以使用DUAL表来查询系统的信息。

在mysql中，DUAL是为了方便那些要求所有SELECT语句都应该具有FROM和其他子句的人。MySQL可能会忽略该条款。如果没有引用表，MySQL不需要从DUAL。DUAL表无法删除或者创建，并且，对于select * from dual。Mysql会报错。

2.模式，内模式，外模式

在数据库中，模式（schema）、内模式（internal schema）和外模式（external schema）是重要的概念。它们用于定义和描述数据库的不同层次和视图以及它们之间的关系。在本文中，我们将详细解释这些概念。

外模式（External Schema）是数据库的用户视图，它定义了用户或应用程序可以访问的数据的逻辑结构。它包括用户或应用程序能够看到和使用的部分数据，而隐藏了数据库的其他部分数据和结构。

外模式通常是由用户或应用程序创建和维护的，它对应于用户或应用程序所需的数据和结构。外模式是面向用户和应用程序的抽象层，它提供了一种方便、简单的方式来访问和操作数据库。

内模式（Internal Schema）是数据库的物理结构，它定义了数据在磁盘上的存储方式、索引、排序和存储格式等信息。它描述了如何将数据存储在磁盘上，并指定了数据存储的方式和方法。

内模式通常是由数据库管理系统（DBMS）创建和维护的，它对应于数据库中的实际物理存储结构。内模式是DBMS的内部表示，它包含了数据存储在磁盘上的详细信息，如存储文件的位置、文件的大小、存储记录的方式等。

3.oracle中schema和Database的差别？

数据库DB是数据库对象的物理集合，而schema则是数据库内部用于组织管理数据库对象的逻辑集合，

schema名字空间之下则是各种应用程序会接触到的对象，比如表，索引，数据类型，函数，操作符等。

因此角色不特定于某个单独的数据库，但角色如果需要登录数据库管理系统则必须连接到一个数据库上。

角色可以拥各种数据库对象。

4.Oracle中的函数使用

oracle中的函数声明举例如下所示：：

CREATE [OR REPLACE] FUNCTION Function_name
[ (identifier [ { IN ] Type [DEFAULT value],
identifier [ { OUT | IN OUT } ] Type,...) ]
RETURN Type
[ AUTHID { DEFINER | CURRENT_USER }]
{ IS | AS }
<变量声明>
BEGIN
<执行部分>
END [ Function_name ];

例如F_DEMO创建函数

create or replace function f_demo1(v_in varchar)
return varchar
is
begin
return v_in||'被调用了';
end;
Demo3 为调用函数 F_Demo1
create or replace procedure demo3(v_out_3 out varchar)
is
begin
v_out_3:=f_demo1('f_demo1');--调用 F_Demo1
end;

二、实战演练

4.已知table city_info(user_name varchar(20) primary key,city varchar(20))记录了多位用户所在的城市情况如下所示：

现在需要合并城市情况，要求输出格式：

cities

city1,city2,city3...

由于city可能存在重复，这里要求输出不重复的城市名称，并按照字母顺序升序排列。

在mysql中listagg无法使用，使用group_concat作为代替，如下所示:

 5.2021年八月份练题总数

 6.leetcode 游戏玩法分析IV

 

 代码如下，正常通过

思路：通过将每个player打游戏的日期分别进行rank得到D00,再将D00拷贝一份得到D01，以D00作为主表，D01作为从表，查找D00是第一天登录，且D01的登陆时间和D00相差一天的结果。得到第二日登录玩家情况D02，再将D02和玩家总数相除就能得到结果。

7. 第N高的薪水(函数的使用)

 

 8.leetcode-删除重复的电子邮件

 9.换座位

10.leetcode-三角形

 练习if的使用。

11.分组计算练习题

题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

用户信息表：user_profile

30天内活跃天数字段（active_days_within_30）

发帖数量字段（question_cnt）

回答数量字段（answer_cnt）

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	北京大学	3.4	7	2	12
2	3214	male		复旦大学	4.0	15	5	25
3	6543	female	20	北京大学	3.2	12	3	30
4	2315	female	23	浙江大学	3.6	5	1	2
5	5432	male	25	山东大学	3.8	20	15	70
6	2131	male	28	山东大学	3.3	15	7	13
7	4321	male	26	复旦大学	3.6	9	6	52

12.注册时间最早的三个人