数据库mysql、oracle的练习题

以下数据库中的问题，回答自百度知道的提问，整理出来做一个参考。sql语句以oracle为主，mysql为辅。因水平有限，有错误的地方，欢迎批评指正。

一、sql基础

1、oracle case when用法

例子：
select 
CASE sex 
WHEN '1' THEN '男' 
WHEN '2' THEN '女' ELSE '其他' 
END 
from 表

2、mysql 多表关联数据合并？
我现在有两张表，分别是系表和班级表，我现在想吧所有的系及系的班级查出来怎么查
dept ：id，name
1 综合系
2 艺术系
class：id，dept_id,name.
1 1 综合1班
2 1 综合2班
3 2 艺术1班
4 2 艺术2班
5 2 艺术3班
我想拿到这样的数据
[
{
id:1,
name:综合系,
class:[
{
id:1,
name: 综合1班
}
]
}
]

select b.id,b.name,a.id,a.name from dept b 
left join class a on a.dept_id=b.id where a.id=1

3、列转行 Unpivot 函数

SQL如何将一个字段的别名跟值分别赋值给两个字段？

现在有两个表表一跟表二（表一是已存在的，表二正在需要根据表一来创建），现在我想把表一的

干草和莫兰赋值给表二的物种这个字段，把干草跟莫兰的值（81，57和38，37）赋值给价格这个

字段，并且要求表二也是对应的，请问下各位大佬如何用SQL语句实现

 

4、oracle如何使用wm_concat()？
表1    
顾客ID    顾客信息    
10001    XXX1    
10002    XXX2    
10003    XXX3    

表2    

销售员编号    销售员姓名    
11             YYY1    
12             YYY2    
13             YYY3    
14             YYY4    

表3    
顾客ID    购买物品    销售员    
10001    苹果    11    
10001    草霉    11    
10001    西瓜    12    
10002    西瓜    13    
10002    桃子    13    
10002    芒果    14    

查询需要得到（把出售的物品按顾客和销售员进行合并）    

顾客ID    购买物品    销售员    销售员姓名
10001    苹果/草霉    11    YYY1
10001    西瓜            12    YYY2
10002    西瓜/桃子    13    YYY3
10002    芒果            14    YYY4

select 顾客id,replace(wmsys.wm_concat(购买物品),',','/') 购买物品,销售员,b.销售员姓名 
from 表3 a LEFT JOIN 表2 b on a.销售员=b.销售员编号 
group by a.销售员,a.顾客id,b.销售员姓名

5、sql 中sum中不能有sum，即 sum(sum()),如果不能我该怎么做？

对里面的sum函数进行分组groupby，再对外层sum求和

6、sql语句中数值型日期如何获取前一个月的日期？

类似20200101返回结果为20191201这样？

select to_char(add_months(sysdate,-1), 'yyyyMMdd') from dual;

7、SQL如何计算每一笔交易结余，刚接触数据库 
现在有一张库存交易明细表，类型为1代表入库，类型-1代表出库，想查询某个时间段每一笔交易的结余数

品号 类型 交易数 日期
001     1       100     1-1
001    -1         50     1-1
001    -1         20     1-2
001     1         60     1-3
想得出1-2到1-3号结余

品号 类型 出库数 入库数 日期 结余
001    -1     20         0       1-2     30
001     1       0       60       1-3     90

select * from (
select 品号,类型,
case when 类型>0 then 0 else 交易数 end 出库数,
case when 类型<0 then 0 else 交易数 end 入库数,
日期,sum(交易数*类型) 
over(order by 日期) 结余
from 表) a 
where a.日期 between '1-2' and '1-3'

8、oracle数据库如何将输出一个属性都赋同一个值？

update 表名 set 列='xxx'

9、oracle向表查数时没值,但查询有值啥原因？

核查数据库中表数据是否存在空值。

10、mysql 数据库 如何用sql语句查询数据后再插入本表？

如何在表table_a中查询字段m="abc"的数据，并将m字段值改成“efg”，再插入到table_a中？

第一种方法：复制m="abc"这条数据，取其中一条数据修改为m="efg"，插入到table_a表

第二种方法：如果存在多个m="abc"这条数据，则要加一个状态state列，没修改前为0，复制行数

据为1，之后查询m="abc" 和state=1作为条件，修改m="efg"，插入到table_a表

11、SQL查询交易流水,一张信用卡在一年内交易过几个月。 比如:1月交易过3次,4月交易过1次。

查询结果等于2？

对月份进行分组，统计count次数。

12、MySQL中如何修改商品表中的库存量等于库存量加进货量？

update 商品表 set 库存量=库存量+进货量 where 条件

13、怎么设置SQL数据库默认值？

默认值为"是"

字段默认值 `column` varchar(64) NOT NULL DEFAULT '是'

14、SQL语句报错

DROP teachersAndStudents;

DROP语句报错:
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'teachersAndStudents' at line 1

改为：DROP table teachersAndStudents;

15、请问mysql的group by如何取前n个？
select a,b from 表 group by a,b 现在如何取出每个分组的前两个b

select a.* from 表 a where exists
(select count(*) from 表 where A = a.A and B< a.B having count(*) < 2)
order by a.A

16、在oracle中查询公司中工资最高的两个人的员工号、lastname和工资,要求并列的

select 员工号、lastname、工资 from (
select 员工号、lastname、工资 from 表 order by 工资 desc )
where rownum<=2

17、oracle 如何判定一个月内交易次数达到10次以上？
怎么动态的时间呢

对月份进行分组，筛选交易次数达到10次以上的。

18、如何用MySQL建立数据库？

CREATE DATABASE 数据库名;

19、怎么用sql语句把两个表，两个字段进行比较？
表1中字段A和字段B
表2中字段E和字段F
然后用sql语句，对比A和E字段，如果两者数值相同，则把E字段对应的F字段值替换到A字段等同位置。
例如：
表1                 表2
A      B           E    F
201 556       201 988
201 557       202 992
201 558       203 997
两个表执行代码后
结果
988 556
988 557
988 558

select y.F,x.B from 表1 x left join 表2 y on x.A=y.E order by x.B

请问下，结果输出后第二列有很多空值，怎么把第二列为空的不显示，只显示有数据的。

可以加个条件 
select y.F,x.B from 表1 x 
left join 表2 y on x.A=y.E 
where x.B is not null order by x.B

20、SQL不用is查找null的数据用其他的东西查找null的数据？

可以借助length或者nvl函数。

21、oracle查找a,b表中姓刘用户的学生？

如果两个表有关联，找a、b表关联关系，模糊查询刘即可

22、PLsql中如何统计一秒钟内的数据量？

当前时间的前一秒为sysdate - interval '1' SECOND，可根据此函数统计

23、oracle建表保存的时候一直这个ORA-00904: "COLUMN1": 标识符无效的错误，请问是哪里的问题啊？
CREATE TABLE JCJZJSBXX 
(
KHMC VARCHAR2(200) NOT NULL 
, ULVAC1 VARCHAR2(20) 
, ULVAC2 VARCHAR2(20) 
, ULVAC3 VARCHAR2(20) 
, ULVAC4 VARCHAR2(20) 
, ULVAC5 VARCHAR2(20) 
, JZDS1 VARCHAR2(20) 
, JZDS2 VARCHAR2(20) 
, JZDS3 VARCHAR2(20) 
, JZDS4 VARCHAR2(20) 
, JZDS5 VARCHAR2(20) 
, TS1 VARCHAR2(20) 
, JEWY1 VARCHAR2(20) 
, YJHDBMQDTS1 VARCHAR2(20) 
, DDKNTS1 VARCHAR2(20) 
, JEWY2 VARCHAR2(20) 
, TS2 VARCHAR2(20) 
, JEWY3 VARCHAR2(20) 
, YJHDBMQDTS2 VARCHAR2(20) 
, DDKNTS2 VARCHAR2(20) 
, JEWY4 VARCHAR2(20) 
, TS3 VARCHAR2(20) 
, JEWY5 VARCHAR2(20) 
, YJHDBMQDTS3 VARCHAR2(20) 
, DDKNTS3 VARCHAR2(20) 
, JEWY6 VARCHAR2(20) 
, TS4 VARCHAR2(20) 
, JEWY7 VARCHAR2(20) 
, YJHDBMQDTS4 VARCHAR2(20) 
, DDKNTS4 VARCHAR2(20) 
, JEWY8 VARCHAR2(20) 
, TS5 VARCHAR2(20) 
, JEWY9 VARCHAR2(20) 
, YJHDBMQDTS5 VARCHAR2(20) 
, DDKNTS5 VARCHAR2(20) 
, JEWY10 VARCHAR2(20) 
, DCRHQDDD VARCHAR2(200) 
, CNKCJH VARCHAR2(20) 
, YJSBGRTS VARCHAR2(20) 
, SQR VARCHAR2(20) NOT NULL 
, SQRQ VARCHAR2(20) 
, CONSTRAINT JCJZJSBXX_PK PRIMARY KEY 
(
COLUMN1 
, COLUMN3 
, KHMC 
)
USING INDEX 
(
CREATE UNIQUE INDEX JCJZJSBXX_PK ON JCJZJSBXX (COLUMN1 ASC, COLUMN3 ASC, GSMC ASC) 
)
ENABLE 
)
保存的时候一直提示：ORA-00904: "COLUMN1": 标识符无效，请问是哪里的原因啊？

缺少以下字段
, COLUMN1 VARCHAR2(20)
, COLUMN3 VARCHAR2(20)
, GSMC VARCHAR2(20)

24、mysql 将同一年同一个月的多行数据提取出来作为一行输出？
我目前查找出来的数据如图1，我想要达到的最终效果

深圳 1月分总额 1999-01

深圳 2月份总额 1999-02

上海 2月份总额 1999-02

上海 3月份总额 1999-03

........

大概是上面这样

select city,sum(amount) ,substring(time,1,7) from 表 group by substring(time,1,7),city

25、execute immediate为什么对alter无效？
ORACLE数据库，有一个序列名为SEQ_CPES_BASE_AUTHLIST。现在想要将SEQ_CPES_BASE_AUTHLIST的next number调大150000。

如果直接执行语句，结果如预期的，生效了。
alter sequence SEQ_CPES_BASE_AUTHLIST increment by 150000 nocache;
select SEQ_CPES_BASE_AUTHLIST.nextval from dual;
alter sequence SEQ_CPES_BASE_AUTHLIST increment by 1 cache 20;
最终会发现序列值已经增大150000了。

但是改成execute immediate的方式，就不行了。
begin
execute immediate ' alter sequence SEQ_CPES_BASE_AUTHLIST increment by 150000 nocache ';
execute immediate ' select SEQ_CPES_BASE_AUTHLIST.nextval from dual ';
execute immediate ' alter sequence SEQ_CPES_BASE_AUTHLIST increment by 1 cache 20';
end;
执行成功，没有报错信息。但就是序列值还停留在原来的维度，只是加了1，因为中间执行了一句select SEQ_CPES_BASE_AUTHLIST.nextval from dual 。也就是说 execute immediate后面的alter语句并没生效。我就不明白了，大神解释下原因？是我哪里写的有问题？因为我最终的目的，是想将150000通过查询的方式查出来赋到变量里，再通过execute immediate的方式来改变序列值。

当时在plsql中测试的结果是：execute immediate后面的alter语句是执行了，只是两个alter，又变成了初始值increment by 1。。这里如果有懂原理的，有更好的答案，请指正。

26、mysql行转列的问题，但是不是并接？ 
有一张表，表里为左图，我想转换成右图这样，并且可以查询，用查询语句我之前写了代码但是只能显示了人名1，并不合适
SELECT 
(case WHEN 人名1 is not null then 人名1    
WHEN 人名2 is not null then 人名2    
WHEN 人名3 is not null then 人名3    
WHEN 人名2 is not null then 人名4) 人名
FROM table

select 人名1 人名,工作时间 from 表
union all
select 人名2 人名,工作时间 from 表
union all
select 人名3 人名,工作时间 from 表
ORDER BY 工作时间,人名

27、什么是内连接和Select常量表达法？ 

内连接也称为等值连接，返回两张表都满足条件的部分。select 常量 from 表名

 二、存储过程

1、请问这段t-sql语句是哪里出现了错误？

USE
darksoul;
CREATE FUNCTION average() RETURNS FLOAT AS BEGIN

END

如果是oracle，RETURNS 应该是RETURN；（）中要定义变量；缺少return

 

三、定时器

1、怎么样才能让sql数据库定期执行一句sql语句？

哪位大佬帮忙看一下如何设置这个SQL语句定时执行每隔5分钟执行一次，

UPDATE mac_vod SET vod_play_url= replace(vod_play_url, '第', '弟')

就是把第替换成弟，需要每隔五分钟或者十分钟执行一次，一个定时任务。

create or replace procedure MYPROCEDURE is
begin
UPDATE mac_vod SET vod_play_url= replace(vod_play_url, '第', '弟');
commit;
end MYPROCEDURE;
declare myjob number;
begin
DBMS_JOB.SUBMIT(
job => myjob,
what => 'myprocedure;',
next_date => SYSDATE,
interval => 'sysdate+5/(24*60)');
commit;
end;

四、触发器

1、oracle 触发器语法错误？

需求是当一条记录的一个字段A更新时，这行记录的字段b需要赋值给字段c。报错：ORA-24344: success with compilation error
代码如下：
CREATE OR REPLACE TRIGGER GAS_RJH_FXS_UPPER AFTER UPDATE OF FXS_BGZT ON GAS_RJH_FXS 
FOR EACH ROW
BEGIN 
IF :NEW.FXS_BGZT = 'YBG' THEN
:NEW.FXS_BGPFL=:NEW.FXS_BGSQL； 
--UPDATE GAS_RJH_FXS SET FXS_BGPFL=FXS_BGSQL WHERE GAS_RJH_FXS_ID = :OLD.GAS_RJH_FXS_ID ;
End if;

End;

CREATE OR REPLACE TRIGGER GAS_RJH_FXS_UPPER
BEFORE UPDATE ON GAS_RJH_FXS
FOR EACH ROW
BEGIN
IF :NEW.FXS_BGZT = 'YBG'
THEN
:NEW.FXS_BGPFL :=:new.FXS_BGSQL;
--UPDATE GAS_RJH_FXS SET FXS_BGPFL=FXS_BGSQL WHERE GAS_RJH_FXS_ID = :OLD.GAS_RJH_FXS_ID ;
End if;
End;

在navicat中会报错：

在navicat中执行后，编译报错，在plsql中发现少了end;，但是在plsql中编译是正常的，或者将

End if;End;写在同一行。