Oracle数据库的基础

如果 eExpression1 的计算结果为 null 值，则eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回 .NULL.

3、minus

“minus”直接翻译为中文是“减”的意思，在Oracle中也是用来做减法操作的，只不过它不是传统意义上对数字的减法，而是对查询结果集的减法。 A minus B就意味着将结果集A去除结果集B中所包含的所有记录后的结果，即在A中存在，而在B中不存在的记录。

4、连接join

各种join的详细简单的解释

5、数据库设计范式

数据库设计第一范式：（设计出没有数据冗余和数据维护异常的数据库结构）

1、数据库表中的所有字段都只具有单一属性

2、单一属性的列是由基本的数据类型所构成的

3、设计出来的表都是简单的二维表

数据库设计第二范式：

要求一个表中只具有一个业务主键，也就是说符合第二范式的表中不能存在非主键列对只对部分主键的依赖关系

数据库设计第三范式：

指每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主键属性对主键的传递依赖

数据库概念

长期储存在计算机内，有组织，可共享的大量数据的集合，就是数据库

数据库管理系统，（DBMS，databace mangement system），用于创建操作和管理数据库的应用程序

:xx,dnmcg2IR

数据库用来管理数据，QQ为什么能这么厉害，就是因为他有很多数据，这些数据是商业机密，使用数据库存储数据占用存储空间小，并且查询数据快Alter TABLE XS ADD ENG CHAR(20); --添加新的列

数据库操作语言

关系的基本概念

自己的某一个属性（或属性集合）在其他表中是主码，则在自己表中这个属性（或属性集合）是外码

实体完整性

实体完整性：（primary key）不能是空值，不能重复

参照完整性：（foreign key）可以是空值，

select id)

from xs

where to_char(birthday,'yyyy-mm-dd') ='2002-06-28';

将日期转化成char类型

一些函数控制字符

dual

dual是一个一行一列的虚拟表，常用来计算表达式

desc关键字

desc 查看表的结构，

Alter TABLE XS ADD ENG CHAR(20); --添加新的列

UPDATE XS SET ENG='ENGLISH' WHERE DEPT='计算机';将dept=计算机的ENG改为‘english’ SELECT SNAME, LOWER(ENG) AS LOW_ENG--将eng属性改为小写，重新命名成LOW_ENG,函数LOWER

FROM XS WHERE DEPT='计算机';

commit; -->提交数据

rollback; -->回滚数据

select * from user_tables;--查询用户下的所有表

having和 where的区别：

where在数据分组前进行过滤，having在数据分组过后进行过滤，

使用having时应该结合Group by语句，where 用于标准的行级过滤

2022 3-9

--2022 3-9
create table stu
(SNO CHAR(6) PRIMARY KEY,
SNAME VARCHAR2(10) UNIQUE,
DEPT VARCHAR2(20) NOT NULL,
SEX VARCHAR2(4) DEFAULT('男'),
TOTALCREDIT NUMBER(3, 0) CHECK(TOTALCREDIT>=0)
);
insert into stu values ('100001','hzy', 'jsj', '女', '100');
insert into stu values ('100001','hzz', 'jsj', '女', '100');
insert into stu(sno,dept) values('111','rjgc');
select * from stu;
update stu set sname='hjj' where sno='111';
select * from stu;
alter table stu modify sname varchar2(20);
alter table stu modify sno char(10);
alter table stu add nativeplace varchar2(20);
alter table stu drop column nativeplace;
alter table stu drop CONSTRAINT SYS_c007069;
alter table stu add constraint stu_sname_unique unique(sname);
create table stu1
as
select * from stu;
drop table stu1;
insert into stu values('100002', '张三',  '计算机','男', '10');
--INSERT INTO stu(sno, )
update stu
set sex=sex+10;
update stu
set dept='软件工程'
where sname='张三';
delete from stu
where TOTALCREDIT is null;
commit;
rollback;
select * from xs;

小知识点：

利用已有数据表建立一个新表，但不复制数据:

create TABLE S1

SELECT * FROM STUDENT WHERE 1=0;

UPDATE STU SET TOTALCREDIT=NVL(TOTALCREDIT, 0)+2;

NVL(TOTALCREDIT, 0):如果totalcredit不是空，就用原来的值，是空，就用0来代替；

FROM ZLGC WHERE CATEGORY='课程思政示范课程' AND COUNT(CATEGORY)>50 GROUP BY SCHOOL,CATEGORY ORDER BY COUNT(CATEGORY) DESC

ERROR: ORA-00934: 此处不允许使用分组函数

要使用having 来过滤分组

2022-3-19数据库实验心得

在本次实验中，我遇到的问题首先是1-1题目中extract函数的使用，通过从网上查询知道了，我学习到该函数的作用是将年份从date中分离出来单独计算从而得到年龄，第二个问题是2-4题目中过滤学生人数小于3的的语句，不能在group by 语句前面加where来过滤，如果使用了，错误信息是这样的，

查阅资料后学习到了，有group by 语句时where中不能使用聚合函数来过滤数据，此时只能使用having语句，并且having语句只能跟在group by 语句后面。这两个问题是我在本次实验中遇到的最大的难题，解决了之后，真的学习到了很多。

在本次实验中，我首先在创建user用户时就遇到了，用户名或角色名是不存在的问题，然后我先想着改一下用户名为users，结果成功了，后来才从老师那里得知是用户过期了的原因，要重新编辑用户信息才可以。

然后我在1-4题目中遇到了对设置组合主键，外键操作的不熟练问题，还有就是对于alter table tablename add colname VARCHAR(20)（添加列）;alter table tablename drop column colname（删除列）;alter table tablename drop constraint SYS_C007120;（去除限制）add constraint UQ_CNAME UNIQUE(CNAME)（添加限制）;这几个操作的不熟练，最后遇到的问题是赋值一表的结构到一个新表（不复制数据），从网上查阅资料后，知道了

最关键的一步是：SELECT * FROM STUDENT WHERE 1=0;设置一个不可能的条件，选择之后，就不会剩下数据了。解决了这些问题后，有很大的收获。

视图

里面存储的是SQL语句，不是查询结果

创建视图后不显示结果，要通过select语句来查询

视图的作用：简化查询语句

有些视图可以更新，有些不可以

SELECT * FROM A NATURAL JOIN B WHERE B.B = A.B;

联结表

sql的left join 、right join 、inner join之间的区别

　　left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录

　　right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

　　inner join(等值连接) 只返回两个表中联结字段相等的行

左连接
SELECT *
FROM student  # 主表（不会受到影响）
LEFT JOIN score  # 辅表（只有满足条件的才会显示）
ON student.ID = score.SID右连接SELECT *
FROM student  # 主表（只有满足条件的才会显示）
RIGHT JOIN score  # 辅表（不会受到影响）
ON student.ID = score.SID
全连接（是个笛卡尔积？）
SELECT *
FROM student
FULL JOIN score
select sname, cname, tname, grade
--自连接  ：只返回两张表连接列的匹配项。
--以下三种查询结果一样。
select * from student s inner join class c on s.classid=c.id; 
select * from student s join class c on s.classid=c.id;
select * from student s,class c where s.classid=c.id;
--笛卡儿乘积连接 ：即不加任何条件，达到 M*N 的结果集。
--以下两种查询结果一样。
select * from student s cross join class c;
select * from student,class;
--加上条件，产生跟自连接一样的结果。
select * from student s cross join class c where s.classid=c.id;

自然连接要注意：

SELECT * FROM A NATURAL JOIN B WHERE B.B = A.B;

SELECT * FROM A NATURAL JOIN B on B.B = A.B;

这两种方法都会报错，不能有限制条件。因为自然连接它自动的进行了等值比较，所以不需要加上限制条件让他们的连接起来，自然连接是一种特殊的等值连接。

自然连接与等值连接的区别：

sql server 等值连接和自然连接的用法_百度知道 (baidu.com)

SELECT * FROM A, B WHERE A.B=B.B;

这是等值连接，相同的属性列会保存

自然连接是一种特殊的等值内连接，它是由系统根据两表的同名字段自动作等值比较的内连接，不需要用ON关键字指定连接条件

SELECT * FROM A NATURAL JOIN B ;

这是自然连接，自然连接是一种将相同属性列删除的等值连接

自然连接四个表

FROM STUDENT NATURAL JOIN SCORE NATURAL JOIN COURSE NATURAL JOIN TEACH WHERE COURSE.CNAME='数学分析' and TEACH.TNAME='严敏';

ANY ALL

ANY ALL的区别

总结成一句话就是：all 是满足所有的， and and and；any 是满足其中一个就可以，or or or

2022-实验三总结

在这次实验过程中遇到的问题就很多了，首先第一题就来了一个下马威，分不清三种连接的区别，搞得无从下手，最后自己弄了两个表才弄懂了前两个的区别，如下图：等值连接相同的属性会保留，而自然连接就会将它们合并，自然连接是一种特殊的等值连接，终于理解了，自己找了几个小时。还有一个就是join后面总是忘掉加上on条件，报了三四次缺少关键字错误才发现，我学会了，做这种题目时要首先弄清楚概念就很好下手了。然后就是left join 、right join、full join ，区分这几个，首先区分：主表（不会受到影响），辅表（只有满足条件的才会显示），left join 左为主表，右辅表；right join 则相反，最后的not exists的双重否定，更是搞得稀里糊涂，现在还是有点不明白。每一次实验都是一次不错的成长。

数据放到表中，表再放到库中

表中的列相当于类中的属性，每一行相当于对象

'%'会过滤掉空值：

SELECT * FROM XS;

SELECT * FROM XS WHERE REMARKS LIKE '%';

2022-3-30

--2022-3-30
create index idx_xs_sname on xs(sname);--索引只创建，不自己使用，由系统调用
select * FROM XS WHERE SNAME LIKE '_%';
--视图：安全（设置用户权限），简化使用，保存的是查询语句
--视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，
--结果集被赋予一个名字，即视图名字。视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，
--当基表数据发生变化，视图数据也随之变化。
SELECT * FROM XS WHERE DEPT='计算机';--创建视图
--计算区分度
SELECT count (DISTINCT(SNAME))/count(*) FROM XS;

--计算区分度
--WITH CHECK OPTION 子句强制所有数据修改语句均根据视图执行，
--以符合定义视图的 SELECT 语句中所设的条件。
--WITH CHECK OPTION的作用?
--1.对于update,有with check option，要保证update后，数据要被视图查询出来；
--2.对于delete,有无with check option都一样；
--4.对于insert,有with check option，要保证insert后，数据要被视图查询出来；
--5.对于没有where 子句的视图，使用with check option是多余的。
 
SELECT *FROM XS;
CREATE VIEW DEPT3
AS
SELECT *FROM XS WHERE TOTALCREDIT>70 WITH CHECK OPTION;
UPDATE DEPT3
SET TOTALCREDIT=60
WHERE SNAME='林一帆';

PL/SQL:

PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。PL/SQL是Oracle对标准数据库语言SQL的过程化扩充，它将数据库技术和过程化程序设计语言联系起来（通俗的说，SQL与编程语言的混血儿），是一种应用开发语言，可使用循环，分支处理数据，将SQL的数据操纵功能与过程化语言数据处理功能结合起来.PL/SQL的使用，使SQL成为一种高级程序设计语言，支持高级语言的块操作，条件判断，循环语句，嵌套等，与数据库核心的数据类型集成，使SQL 的程序设计效率更高.

select 选择了一些列之后，如果里面有聚合函数，后面一定要有group函数否则会报错，原因如下：

最近写SQL的时候，有遇到过报出 ORA-00937的问题。

解释：

select 列表项中除了包含聚合函数外，还包含了表的某些列，那么你将必须使用group by语句，否则语法通不过。

例如：

SQL>

1 select deptno,job,avg(sal)

2 from emp

3 ;

ERROR 位于第 1 行:

ORA-00937: 非单组分组函数

其实这里也非常容易理解，你既然指定了聚合函数，又同时制定了其他列，还想不按照指定的列来分组，你到底想让oracle怎么做呢？

这根本就得不出结果。就像你需要统计班上男女生的人数，但是又不能分组，只能在一条数据里表示出来，怎么能办得到呢？

所以这里的group by是必须的。

上面的错误纠正为：

select deptno,job,avg(sal) from emp group by deptno,job;

就OK了

原文链接：聚合函数与列同时选择，需要Group

create sequence table1_id
minvalue 1             --自增字段最小值
nomaxvalue           --最大值 没有就算nomaxvalue
increment by 1      --每次增值1
start with 1           --起始值
nocache;             --不缓存
CREATE SEQUENCE TABLE_ID--创建顺序表
MINVALUE 1--最小值
NOMAXVALUE--无最大值
INCREMENT BY 1--递增量
START WITH 1--起始值
NOCACHE;--无缓存
--第三步：创建触发器
create or replace trigger SCORE1trigger
before insert on SCORE1 for each row
begin
select table1_id.nextval into:new.sid from dual;
end SCORE1trigger;


Mysql中：
create table UserTable (
USERID INT(4) PRIMARY KEY AUTO_INCREMENT NOT NULL,
UNAME VARCHAR(20) NOT NULL,
UPASSWARD VARCHAR(20) NOT NULL
)

2022-4-2实验四总结

在本次实验中，我首先遇到的问题是创建视图时，带上WITH CHECK OPTION选项与不带的区别：如果根据视图来更改数据，一定要符合创建视图的时候的where 的条件。第二个是创建两个字段的视图 grade_avg_view，select 选择了一些列之后，如果里面有聚合函数，后面一定要有group函数否则会报错。最后一个是设置自增型的主键，在mysql中使用AUTO_INCREMENT 可以直接创建，但是在Oracle中要先创建一个递增序列，然后再创建一个触发器才可以。

索引，范式和锁

索引：每个表如果设置了主键就会有有一个主键索引，在Mysql中，使用的时InnoDB引擎，每一个索引就是一颗B+树，也可以创建非主键索引，二级索引，二级索引和主键索引的不同之处在于其叶子节点上保存的值不一样，表中所有字段的值都被完整的保存在主键索引的叶子节点上，但是二级索引的叶子节点只保存对应主键的值。这里的叶子节点上只保存了主键的值，所以还需要通过获得的主键 ID 值再回到主键索引上查出所有字段的值，这个过程称作回表。

数据库方面，能会基本的增删改查即可，外带一些基本概念，比如事务怎么处理，JDBC里批处理怎么处理。

创建表时，凡是能通过计算出来的字段都不要把它当成属性列；

创建函数、过程

存储过程不一定要有返回值，函数一定要有

注意存储过程in out inout 三种参数类型，而函数只有in

创建函数：

CREATE OR REPLACE FUNCTION COUNT_CREDIT(STUNO in VARCHAR2) RETURN NUMBER
AS
CRED NUMBER:=0;
TOTAL_CRED NUMBER:=0;
BEGIN
FOR STUCUR IN(SELECT SNO, CNO, GRADE FROM cj WHERE SNO=STUNO 
AND GRADE>=60) LOOP
SELECT CREDIT INTO CRED FROM KC WHERE CNO=STUCUR.CNO;
TOTAL_CRED:=TOTAL_CRED+CRED;
END LOOP;
RETURN TOTAL_CRED;
END COUNT_CREDIT;

作业
create or replace function count_credit(stuno in varchar2) return varchar2
as 
cred number:=0;
total_cred number:=0;
begin
  for stucur in(select sno, cno, grade from score where sno=stuno and grade >60) loop
    select credit into cred from course where cno=stucur.cno;
    total_cred:=total_cred+cred;
  end loop;
  return total_cred;
end count_credit;

函数的执行：
1、select COUNT_CREDIT('96001') as total_credit from dual;
2、variable a number(10, 0)--声明变量
begin
:a:=COUNT_CREDIT('96001');
end;
/
后面加上一句：
print a;

创建过程：

create or replace procedure count_credi
(stusno in varchar2 , creditj out number )--warning：in out
as
cred number:=0;
total_cred number:=0;
begin
for cjcur in (select * from cj where sno= stusno) loop
select credit into cred from kc where cno=cjcur.cno;
total_cred:=total_cred+cred*cjcur.grade;
end loop;
creditj:=total_cred/count_credit(stusno);
end count_credi;

作业
create or replace procedure pro_findname(tmpname in VARCHAR2) as
begin
  for stucur in(select sno,sname from student where sname like tmpname ) loop
   dbms_output.put_line(stucur.sno || '' ||stucur.sname);-- 
  end loop;
  null;
end pro_findname;

set serveroutput off
1、set serveroutput on
begin
  pro_findname('%马%');
end;
/
2、exec pro_findname('%马%');--注意这里的调用时使用%，而不是在存储过程中

触发器：

触发器的用法

触发器与存储过程和函数不同，函数和存储过程需要用户显示调用，触发器时在某个事件发生时，自动的隐式的完成，所以叫触发或者点火（firing）

create or replace trigger sum_credit 
after insert on  score
for each row
begin
  update student 
  set TOTALCREDIT=TOTALCREDIT+(select credit from course where cno=:new.cno)
  where sno=:new.sno and :new.grade>60;
  null;
end;

触发器的语法

create trigger <触发器名称>
{ before | after}          # 之前或者之后出发
insert | update | delete   # 指明了激活触发程序的语句的类型
on <表名>                   # 操作哪张表
for each row  # 触发器的执行间隔，for each row
--通知触发器每隔一行执行一次动作，
--而不是对整个表执行一次。
<触发器SQL语句>

dbms_output.put_line的用法

涉及到的知识点如下： 1、enable：在serveroutput on的情况下，用来使dbms_output生效(默认即打开) 2、disable：在serveroutput on的情况下，用来使dbms_output失效 3、put：将内容写到内存，等到put_line时一起输出 4、put_line：不用多说了，输出字符 5、new_line：作为一行的结束，可以理解为写入buffer时的换行符 6、get_line(value, index)：获取缓冲区的单行信息 7、get_lines(array, index)：以数组形式来获取缓冲区的多行信息

oracle系统包—-dbms_output用法

博客