20210713:编写一个 SQL 查询,查找所有至少连续出现三次的数字。
Logs 表:
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
Result 表:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
1 是唯一连续出现至少三次的数字。
解答思路:巧用递增的ID-连续出现数字的row_number的差值是固定的这一特性
select distinct num as "ConsecutiveNums"
from (select num,id - row_number() over(partition by num order by id) as rn
from logs)
group by num,rn
having count(rn) >= 3;
1.rowid、rownum的定义
答:rowid是物理地址,是用于定位数据表中数据存储的位置,唯一的,不会改变
rownum是SQL输出结果的排序,同一条记录不同查询条件对应的rownum也会不同
2.Oracle中function何procedure的区别?
答:function是函数,procedure是存储过程,函数有1个返回值,存储过程有多个或者没有;函数可以在查询语句中调用,而存储过程只能单独调用;
3.Oracle的导入导出有几种方式,有何区别?
1)dmp文件方式
描述:dmp文件是二进制的,可以跨平台,并且包含权限,支持大字段数据,是用的最广泛的一种。
导出语法:exp 用户名/密码@监听器路径/数据库实例名称 file=e:数据库文件.dmp full=y ignore=y ;其中full = y ,表示整个数据库操作; ignore=y,忽略错误,继续操作;
重点内容
导出举例:exp jojo/jojo@localhost/my_database file=e:my_database.dmp full=y ignore=y
导入语法:imp 用户名/密码@监听器路径/数据库实例名称 file=e:数据库文件.dmp full=y ignore=y ;
导入举例:imp jojo/jojo@localhost/my_database file=e:my_database.dmp full=y ignore=y
2)sql文件方式
SQL文件可用文本编辑器查看,有利于可读性,但效率不如dmp文件,适合小数据量导入导出。尤其注意的是表中不能有大字段(blob,clob,long),如果有,会提示不能导出(提示如下: table contains one or more LONG columns cannot export in sql format,user Pl/sql developer format instead)
3)pde文件
第三种是导出为pde格式,pde格式是PL/SQL 自带的文件格式,且且适用于PL/SQL工具,编辑器无法查看,一般不常用。
4.Oracle数据库sql查询把数据为空的项显示为0,用sql查询表示?
答:select decode(name,NULL,0,name) from table;
5.truncate和delete命令的异同?
答:truncate是DDL,隐形提交,不能回滚,delete是DML,每次删除都有记录;
执行速度上truncate>delete;
truncate是通过释放数据页来达到删除效果,所以日志文件只有一条删除记录,delete每删除一条就有一条记录;
6.数据库和数据仓库有什么区别?
答:数据库是面向事务,而数据仓库是面向主题的;数据库的设计是尽量避免冗余,因此符合范式,数据仓库反过来是尽可能引入冗余,利用反范式来设计;另外数据库面向的都是一些在线数据,数据仓库都是历史数据。还有之所以说数据仓库是数据库的升级优化版,是因为数据库是用来存放捕获数据,数据仓库在次基础上对数据进行分析。
7.Oracle中in与exists的区别?
答:exists返回的是一个布尔值,ture或false,in返回的是一个数据组吧。从查询效率角度分析,当外循环(主循环)返回的结果集大于子循环返回的结果值时,用in,反之用exists。
8.char()和varchar()有什么区别?
答:1.char的长度是不可变的,而varchar的长度是可变的;
2.定义一个char[10]和varchar[10],如果存进去的是‘abcd’,那么char所占的长度依然为10,除了字符‘abcd’外,后面跟六个空格,而varchar就立马把长度变为4了,取数据的时候,char类型的要用trim()去掉多余的空格,而varchar是不需要的
3.char的存取数度还是要比varchar要快得多
4.char的存储方式是,对英文字符(ASCII)占用1个字节,对一个汉字占用两个字节;而varchar的存储方式是,对每个英文字符占用2个字节,汉字也占用2个字节,两者的存储数据都非unicode的字符数据。
选择适当的数据结构建表(省市县三级关系)
create table china ( c_id varchar(7) NOT NULL comment '地区编号', c_name varchar(50) NOT NULL comment '地区名称', c_fid varchar(7) default -1 comment '父级id', c_jibie char(5) NOT NULL comment '省=1,市=2,县=3', primary key (c_id) )
查询成绩表score中每科成绩前三名
select a.* from score a left join score b on a.c_id=b.c_id and a.s_score<b.s_score group by a.s_id,a.c_id having count(b.s_id)<3 order by a.c_id,a.s_score desc;
写一个存储过程,实现输入数字n,输出1到n的和
create procedure p(in n int,out result int) begin declace i int default 1; declace sum int default 0; while i<n set sum=sum+i; set i=i+1; //如果奇数相加则i=i+2 end while set result=sum; end
用PL/SQL块实现在一个表中插入1000条数据,要求每100条提交一次
delimiter || create procedure p(in n int) begin declare i int default 0; set i=0; lp:loop insert into kkk values(i+1); set i=i+1; if mod(i,10)=0 then commit; end if; if i=n then leave lp; end if; end loop; end||
有3个表 A(userid(用户ID),time(时间),fee(话费)),B(userid(用户ID),time(时间)), C(userid(用户ID),fee(话费))各有1000万的数据, 3个表的userid是相同的,要求用B表的time字段,C表的fee字段更新A表的相应字段,用存储过程实现。
create procedure p1 cursor abc_cur is select b.userid,b.time,c.fee from b join c on b.userid=c.userid; begin for i in abc_cur loop update a set a.time=i.time,a.fee=c.fee where a.userid=i.userid; end loop; end ||
有上百万数据用户表,删除其中的重复数据,保留最小id号
delete from kxf k where k.name in(select name from kxf group by name having count(*) >1) and k.id not in (select min(id) from kxf froup by name);
用户资料表:serv( serv_id number(10), pro_id number(10), user_type varchar2(30), terminal_name varchar2(30) )
其中,serv_id 为用户标识,是serv表的主键,prod_id为产品标识;user_type为用户类型;terminal_name为终端类型
终端类型临时表:terminal(serv_id number(10),terminal_name varchar2(30))
初始化的情况下,serv表的serv_id,prod_id,user_type字段是已知的,terminal_name字段是空的
要求:根据prod_id,user_type字段的值来更新terminal_name字段
更新条件为:
当条件满足“prod_id = 1 and user_type='A'“时,terminal_name更新为‘固话’
当条件满足”prod_id = 1 and user_type='B'“时,terminal_name更新为‘小灵通’
当条件满足”prod_id = 2“时,terminal_name更新为‘宽带’
当条件满足”user_type=‘C’“时,terminal_name更新为‘CDMA’
以上条件均不满足时,terminal_name更新为 -1;
create procedure p is begin update serv s set s.terminal_name = case when prod_id=1 and user_type='A' then 固话 when prod_id=1 and user_type='B' then 小灵通 when prod_id=2 then 宽带 when user_type='C' then CDMA else '-1' end; update terminal a set a.terminal_name = (select t.terminal_name from serv t where t.serv_id = a.serv_id) where exists (select * from serv t where t.serv_id = s.serv_id); end;