ORACLE数据库练习题整理（2）

1.

Oracle数据库的服务：
OracleServiceOrcl/OracleServicesid：数据库实例服务，是数据库启动的基础，只有该服务启动数据库才能正常进行
OracleOraDb11g_home1TNSListener/OracleHome_nameTNSListener：监听器服务，服务只有在需要远程访问时才需要。
OracleMTSRecoveryService：服务端控制，该服务允许数据库充当一个微软事务服务器MTS、COM、COM+对象和分布式环境下的事务的资源管理器。
OracleDBConsoleorcl/OracleDBConsoleSID：oracle数据库控制台服务，当运行Entreprise Manager的时候需要启动这个服务
OracleJobSchedulerORCL/ OracleJobSchedulerSID：oracle定时器（作业调度）服务
OracleOraDb11g_home1ClrAgent/OracleOraHome_nameClrAgent：oracle.NET拓展服务的一部分

对应数据库启动过程中的三个状态：startup nomount、startup mount、startup open

事务维持四个特性的最小单位是数据块

SQL PLUS 、Navicat、PL/SQL都是Oracle常用的前端工具。
oracle的sqlplus是与oracle数据库进行交互的客户端工具。
SQLPlus是Oracle公司推出的一个交互式管理工具,用于数据库的管理。

表空间——段——区——块

Users表空间
Oracle表空间：
表空间分为：系统表空间、永久表空间、临时表空间和撤销表空间

• 系统表空间：系统表空间包括SYSTEM和SYSAUX表空间，系统表空间是所有数据库必须且自动创建的，一般存放Oracle的数据字典表及相应数据。
• 永久表空间：永久表空间用户保存永久性数据，如系统数据、应用系统数据。每个用户都会被分配一个永久表空间，以便保存其相关数据。除了撤销表空间以外，相对于临时表空间而言，其他表空间就是永久表空间，如系统表空间。
• 临时表空间：由于oracle工作时经常需要一些临时的磁盘空间，这些空间主要在查询带有排序（如Group by、Order by等）算法时使用，当用完后就立即释放，对记录在磁盘区的信息不再受伤hi用，因此称为临时表空间。一般 安装之后只有一个TEMP临时表空间。
• 撤销表空间：从Oracle 9i后，提供了一种全新的撤销空间管理方法，从而使得DBA能够很容易地管理撤销空间，即“自动撤销管理”。而与此相对应，通过回滚段惊喜撤销空间管理的方法被称为“手工撤销管理”。

默认表空间是为用户指定一个表空间，不指定分配Users，指定则分配其他表空间。

sys模式
sys模式中的表只由数据库操作，不允许用户修改
system模式中的存储一些用来显示管理信息的表和视图，以及用于各种数据库选项和工具的内部表和视图，永远不要使用system来存储非管理性用户的表。

SYSDBA
Oracle缺省安装后会产生：
SYS：sys是特殊管理员（超级用户）。所有的数据字典和视图都在sys模式中存放。数据字典存储了用来管理数据库对象的所有信息。数据字典主要用来维护系统信息和管理示例。sys用户只能以sysoper或sysdba的身份登入。
STYSTEM：一般管理员。具有可收回的dba权限。该用户具有Oracle管理工具使用的内部表和视图。system用户只能以默认方式登入，不能使用sysoper和sysdba的身份。
SCOTT：普通用户。包含四个示范表，比如emp。使用Users表空间
Oracle创建用户，会为其分配一个默认表空间和一个临时表空间，如果没有指定表空间，则分配Users为默认表空间。TEMP为临时表空间。

2.

%表示任意字符，[0,n]。

null运算后还是null

左所有，右匹配。左外连接

System表空间是oracle最重要的表空间，在创建数据库的时被最先创建，其中包含了数据库的元数据，对于数据库来说生死攸关。

• Ssytem表空间永远不可能offine，否则数据库无法打开
• 如果system表空间故障，则数据库就要进行介质恢复。
• 数据库启动过程中也需要system表空间进行引导。

3.

add_month(date,month_count) 返回一个计算后的date。比如前进3个月和倒退3个月：
add_month(sysdate,3);
add_month(sysdate,-3);

语法错误

Oracle没有text类型


number§
number(p,s)的意思


oracle三种建表方式：（1）DBCA工具（2）命令行（3）批处理语句
oracle表名、列名命名规范：

• 1.必须以字母开头
• 2.长度不能超过30字符
• 3.不能使用oracle的保留字
• 4.只能使用如下字符 字母，数字，$，#等

oracle可以用正常sql语法创建表
oracle可以用一张表创建另一张表

• create TABLE Emp_bak as SELECT * FROM emp;//所有列
• create TABLE Emp_bak as SELECT * FROM emp WHERE 1=2;//部分行，所有列
• create table Emp_bak(id,name) as select empno,ename from emp;//部分列

也可以用一张表的查询结果插入另一张表
insert into kkk(myid,myname,mydept) select empno, empname,deptno from emp;

可以创建临时表：
create global temporary table();
临时表有两种，可以不写属于哪一种
on commit preserve rows;
on commit delete rows;

4.

索引是占有物理存储的，索引会被自动使用，因为索引对用户透明，表中数据dml时，系统也会自动管理索引。

字段值唯一的建主索引，字段值不唯一建普通索引
所以父表建立主索引，子表建立普通索引。

oracle没有top也没有limit
SQLServer /MS access:top
Mysql:limit
oracle:rownum

oracle sequence有nomaxvalue也有nominvalue
但是alter sequence不能修改开始值，如果需要改必须删除重新创建序列

举例说明参照完整性、级联更新、级联删除，并说明他们之间的关系
1.参照完整性
参照完整性是控制数据一致性的规则，当对表中的数据进行插入、更新或删除操作时，通过参照引用相互关联的另一个表中的数据来检查对表中的数据是否正确，以保持已定义的表间关系。
例如:

• ·当父表中没有关联记录时，记录不得添加到相关表中;
• ·如果父表的值改变，导致相关表中出现孤立记录，则父表的值不能改变;·若父表记录在相关表中的匹配记录，则该父表记录不能被删除。

2.级联更新
级联更新是指用父表中新的关键字的值更新子表中所有相关记录。
举例:B与A做级联更新，当A表数据修改时，B表相应数据也跟着修改。
3.级联删除
级联删除是指删除父表中的记录时，会自动删除子表中的所有相关记录。举例:B与A做级联删除，当A表数据删除时，B表相应数据也跟着删除。
4.三者的关系
参照完整性还体现在对主表中的删除和更新操作，例如，如果删除主表中的一条记录，则从表中凡是外键的值与主表的主键值相同的记录也会被同时删除，将此称为级联删除;如果修改主表中主关键字的值，则从表中相应记录的外键值也随之被修改，将此称为级联更新。

所以级联删除，删除父表记录会删除相关子表记录。如果没有级联删除，子表中相关表记录存在，不会删除父表记录。

alter table启用和禁用约束：
alter table name enable constraint pk;
alter table name disable constraint pk;

alter table修改表的列时，可以写column关键字也可以省略，但是table关键字必须写在表名前面。

oracle中有三个视图：dba_tables,all_tables,user_tables.
all_tables表示用户拥有及有权限查看的表
user_tables表示用户拥有（创建）的表
dba_tables:dba拥有及有权限访问的表

带有检查约束with check option的视图不能更新
with check option 仅在视图定义中含有where子句的情况下起作用。
with check option 选项仅对insert/update操作有效。
with check option 的约束条件就是不能违反前面的where条件
带有只读with read only的视图不能增删改

创建聚簇Cluster
CREATE CLUSTER personnel
(department NUMBER(4)) SIZE 512 STORAGE (initial 100K next 50K);
创建index on cluster
CREATE INDEX idx_personnel ON CLUSTER personnel;
视图上不能创建索引

5.

函数：必须有一个返回值 必须有return子句 作为表达式的一部分执行
过程：可以返回很多值（out参数） 没有return子句 作为一条单独的PL/SQL语句执行

相关触发器？

pl/sql分为：声明部分（可选）、执行部分（必须）、异常处理部分（可选）

[declare]
begin
[exception]
end

声明部分：declare变量
变量赋值：
1.变量的名称name 数据类型type := 给变量赋初始值
2.在执行部分 select 列名 into 变量名；
is/as后默认为声明段（declare）在as 和begin之间不需要加declare 不然会报错 在begin里需要加declare

执行部分主要有赋值语句和流程控制语句
plsql块标识符：必须以字母开头，长度在30字符以内，不区分大小写，不能用-减号，不能和sql保留字重名

execute 对函数用法：给变量赋值
SQL> VARIABLE v_get_value NUMBER
SQL> EXECUTE :v_get_value := CALC_PLAYER_AVG(31)
不能从PL/SQL块中直接调用函数因为这个函数返回一个值
并且这个值必须分配给PL/SQL或者绑定变量
START是用于执行一个脚本

A：
也就是函数不能单独使用，必须用到它的返回值


A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
This restriction applies to all triggers that use the FOR EACH ROW clause.
When a trigger encounters a mutating table, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application.
变异表是正在由约束的影响修改的表，或陈述或表。
发布触发语句的会话不能查询或修改变异表。

?应用程序触发器 数据库触发器

6.

declare

    v_name VARCHAR2(10);

begin
  SELECT ename INTO v_name FROM emp WHERE empno='1234';
  dbms_output.put_line('found');
  EXCEPTION
    WHEN no_data_found THEN
         dbms_output.put_line('not found!');
    dbms_output.put_line('others');    
end;

存储过程中最后结束是异常处理部分，不会再执行其他代码。exception——end中的语句都是异常处理。

第二个列已经换成别名，需要用别名order by，因为select在order by之前执行。

having 是从组中筛选行


from->on->join->后面一样

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是多余的。

也就是说，有where时，要有with check option

您需要设计一个学生注册数据库，其中包含存储学员信息的多个表。STUDENTS表存储有关学生的信息。STUDENT_GRADES表存储有关学生成绩的信息。这两个表都有一个名为STUDENT_ID的列。STUDENTS表中的STUDENT_ID列是主键。您需要在STUDENT_GRADES表的STUDENT_ID列上创建指向STUDENTS表的STUDENT_ID列的外键。  下列哪个语句完成了建表和上述要求

外键：
create table name(
…,
constraint 约束名 foreign key (本表字段) references 主表(主表字段)
)

select view需要有查询view的权限

max() min()是分组后的结果，需要放在having中使用

添加约束的语法：
alter table name modify 列名 constraint 约束名 约束规则
非空约束规则是 not null

列级外键语法：
本表列名 类型 constraint 约束名 references 主表名（主列名）
不需要foreign key

在EMP表的DEPTNO字段上定义了FOREIGN KEY约束说明是列级

视图更新：
不能有组函数
必须有所有not null列
需要更新的不是虚拟列（表达式列）
连接视图中不能有order by

1. 丢失修改
   两个事务T1和T2读入同一个数据并修改，T2提交的结果破坏了T1提交的结果，导致了T1的修改被丢失。典型例子：买飞机票或者火车票的例子。

2. 不可重复读
   不可重复读指的是事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取的结果。
   分为以下三种情况：
   （1）事务T1读取了某一数据后，事务T2对其做了修改，当事务T1再次读数据时，得到了与前一次不同的值。
   （2）事务T1读取了某一数据后，事务T2删除了其中的记录，当事务T1再次读数据时，发现某些记录神秘消失了。
   （3）事务T1读取了某一数据后，事务T2插入了一些新的记录，当事务T1再次读数据时，发现多了一些记录。
   后两种操作叫做幻影现象或幻读。

3.脏读（读“脏”数据）
读“脏”数据只得是事务T1修改了某一数据，并将其写回磁盘，事务T2读取某一数据后，T1由于某种原因撤销了操作，恢复原值，这时T2读到的数据就和数据库中的数据不一致，称为读“脏”数据。

查询2回滚到删除之前，查询3不能回滚truncate，查询4也是删除前的状态

alter table 添加主键约束语法 列名需要加括号，因为修改的是一个约束，可能有多个列组成

alter table Teacher add constraint PK_1 primary key (TeaId)–主键约束

alter table Teacher add constraint UN_1 unique(TeaName)–唯一约束

alter table Teacher add constraint CK_1 check (TeaAge>0)–范围约束

alter table Teacher add constraint DE_1 default ‘123’ for TeaAddress --默认约束

触发器是一种特殊类型的存储过程，它在指定的表中的数据发生变化时自动生效。唤醒调用触发器以响应INSERT、UPDATE或DELETE语句。触发器可以查询其它表，并可以包含复杂的Transact-SQL语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到严重错误(例如，磁盘空间不足)，则整个事务即自动回滚。
这里指的是DML触发器

子查询可以检索0或多行（包含单行子查询，检索一行，多行子查询，检索多行，结果不存在时检索一行）
当子查询与外部SQL语句中的不等式比较运算符一起使用时，子查询的SELECT子句中的列只能包含一列。

根据employees 表，子查询有效的是

AB选项，子查询都是多行子查询，因为部门有多个，查出来最小值也有多个，不能用单行子查询运算符。 CDE是多行子查询运算符，可以。F是group by后面不能使用分组函数进行分组。

mysql> DESCRIBE pet; # 列出 pet 表的所有列
用describe命令查询具体列的信息：describe guestbook id
可以对视图和表使用describe

？0.0



oracle中的事务是自动开始的，执行一个sql时，就开始了一个transaction，

直到commit或rollback语句执行，所有语句都属于一个事务

唯一约束允许值为空值

7.

您需要设计一个学生注册数据库，其中包含存储学员信息的多个表。students表存储有关学生的信息。student_grades表存储有关学生成绩的信息。这两个表都有一个名为student_id的列。students表中的student_id列是主键。您需要在student_grades表的student_id列上创建指向students表的student_id列的外键。下列哪个语句完成了建表和上述要求？

A.
constraint student_id_fk references (student_id)
foreign key students(student_id)

B.
student_id_fk foreign key (student_id)
references students(student_id)

C.
constraint foreign key (student_id)
references students(student_id)

D.constraint student_id_fk foreign key (student_id)
references students(student_id)

查询前尽量减少候选集的数据量
只选取需要的字段
运行前检查执行报告

哪个SQL语句定义了在EMP表的DEPTNO字段上定义了FOREIGN KEY约束
A.
constraint emp_deptno_fk foreign key deptno
references dept deptno

B.
constraint emp_deptno_fk references dept (deptno)

C.
constraint emp_deptno_fk references dept (deptno)
foreign key (deptno)

D.
constraint emp_deptno_fk references dept (deptno)

当前有一个空的学生表，现在经理让你给学生表的STUDENT_ID列添加主键，下列哪个SQL语句能够完成这个任务（）
A.
alter table students
add primary key student_id;

B.
alter table students
add constraint primary key (student_id);

C.
alter table students
add constraint stud_id_pk primary key student_id;

D.
alter table students
add constraint stud_id_pk primary key (student_id);

首先要确保primary key(列名)，其次修改约束必须要有 constraint 约束名。否则无法修改。
创建约束时可以不指定约束名，但同时也要省略constraint关键字。

1.索引的意义在于提升select的查询效率

2.设计索引的原则;

（1）考虑某列中值的分布，索引的列的基数越大，则索引效果越好。
也就是索引的唯一值数量与总值数量比例越大，索引效果越好。

例如：出生日期具有不同的值，但是性别只有男女，那么出生日期作为索引，区分度比较好。

（2）为经常需要查询的字段建立索引

（3）为经常需要order by 、group by 、union 等操作字段建立索引，减少性能消耗。

（4）并不是索引建立的越多越好

首先索引是要占据一定的空间的，多个索引会降低数据的写操作的性能，因为多个索引在更新数据时要进行同步更新。

（5）使用短索引。

在对字符串建立索引时，应指定一个前缀长度，无需对其全部进行索引，因为索引太长占用区间，导致磁盘io性能下降。

此外，一般前缀10到20个已经能达到的区分度很高了。

describe可以查看表和视图

8.MySql

5 6 8 11 24 28 30 33 34 35 37 39 40 41 42 46 47 48 49 50

9.

sno是主键列，不能为空
B选项的’NULL’是字符串，不为null
D选项的主键’10029’超过了长度限制，是char(5)，不符合char(4)
所以不能插入表中。