SQL

学习主题：SQL
学习目标：
1 掌握约束
2 掌握视图
3 掌握分页
4 掌握序列
5 掌握索引
6 掌握同义词
7 掌握创建用户,为用户授权
8 掌握执行计划的使用
9 掌握数据的导入和导出

1.修改表名与删除表
(1)删除表中的列语句的语法结构是什么？
delete 列名
(2)删除表中的列语句的语法结构是什么？
delete 列名
(3)截断表的语句是什么？
truncate table
(4)删除表的语句是什么？
drop table
(5)截断表与删除的区别是什么？
截断表只是删除表中所有的数据，但是保留表结构。删除表就是连带结构一起删除。

2.定义约束原则
(1)无
3.定义非空约束
(1)数据库中的约束有哪些类型？
1、NOT NULL
2、UNIQUE
3、PRIMARY KEY
4、FOREIGN KEY
5、CHECK
(2)每个约束的含义是什么？
1、NOT NULL ：指定列不能包含空值
2、UNIQUE ：指定列的值或者列的组合的值对于表中所有的行必须是唯一的
3、PRIMARY KEY ：表中的每行的唯一性标识
4、FOREIGN KEY ：在列和引用表的一个列之间建立并且强制一个外键联系
5、CHECK ：指定一个必须为真的条件

(3)定义约束的原则是什么？
创建一个约束：在创建表的同事或者在创建表之后都可以定义约束。可以给约束起名字，但是约束名不能相同，必须是唯一的。如果没有为约束起名字，Oracle服务器将用默认格式SYS_Cn产生一个名字，这里的n是一个唯一的整数，所以约束名是唯一的。

4.定义唯一性约束
(1)创建dept90表，包含如下列：id number具备唯一性约束、name varchar2(20)。
SQL> create table temp90 (id number constraint temp80_i unique,name varchar2(20));

5.定义主键约束
(1)创建dept70表，包含如下列：id number具备主键约束。
create table temp70 (id number constraint temp70_pk primary key,name varchar2(20));

6.定义外键约束
(1)创建dept40表，包含如下列：id number、d_id number并为d_id列分配键列约束参照DEPT60表的id列。
create table temp40 (id number ,name varchar(20),constraint temp40_fk foreign key (name) references temp60(name));

7.定义检查约束
(1)创建dept30表，包含如下列：id number、salary number(8,2)。为salary分配检查约束检查条件为salary>1000。
create table temp30 (id number,salary number(8,2) constraint temp30_ck check(salary>1000));

8.禁用与启用约束
(1)在Oracle中查看约束的数据字典表叫什么名字？
user_constraints
(2)禁用约束语句的语法结构是什么？
alter table temp40 disable constraint temp40_ck;
(3)级联禁用约束语句的语法结构是什么？
alter table temp40 disable constraint temp60_pk cascade;
(4)启用约束语句的语法结构是什么？
alter table temp40 enable constraint temp60_ck ;

9.什么是视图
(1)什么是视图？
可以通过创建表的视图来表现数据的逻辑子集或数据的组合。视图是基于表或另一个视图的逻辑表，一个视图并不包含它子集的数据，它像一个窗口，通过该窗口可以查看或改变表中的数据。视图基于其上的表称为基表。
(2)视图的优越性是什么？
1、视图限制数据的访问，因为视图能够选择性的显示表中的列
2、视图可以用来构成简单的查询以取回复杂查询的结果。例如，视图能哟够用从多表中查询信息，而用户不必知道怎样写连接语句。
3、视图对特别的用户和应用程序提供数据独立性，一个视图可以从几个表中取回数据。
(3)视图分为几种类型？
分为简单视图和复杂视图
(4)不同类型的视图的区别是什么？
简单视图：数据仅来自一个表，不包含函数，不包含数据分组，可以通过视图进行DML操作。
复杂视图：数据来自一个或多个表，可以包含函数，可以包含数据分组，不允许通过视图进行DML操作。

10.创建简单视图
(1)创建简单视图的语法结构是什么？
Create view emp80 as select e.employee_id,e.last_name,e.salary from employees e;

11.创建复杂视图
(1)创建一个名为DEPT_NAME的复杂视图，包含每个部门的部门名称，部门最低薪水、部门最高薪水以及部门的平均薪水。
create view temp_name as select d.department_name,min(e.salary) min,max(e.salary) max,avg(e.salary) avg from employees e,departments d group by d.department_name;

12.视图中 DML 操作的执行规则
(1)使用DML操作的执行规则是什么？
如果视图中包含下面的部分就不能修改数据：
1、组函数
2、GROUP BY子句
3、DISTINCT关键字
4、用表达式定义的列
(2)如何设置视图拒绝DML操作？
create view v_emp as select * from employees with read only;

13.删除视图
(1)如何删除视图？
drop view 视图名称
(2)删除视图后数据为什么不会丢失?
因为视图就是一个查看或者修改表数据的窗口，是基于数据库中的基本表的。

14.什么是内建视图
(1)什么是内建视图？
1、内建视图是一个带有别名的可以在SQL语句中使用的子查询。
2、一个主查询的在FROM子句中指定的子查询就是一个内建视图。
3、由于位于FROM子句中命名了别名的子查询创建。该子查询定义一个可以在主查询中引用数据源。

15.使用内建视图实现TOP-N分析
(1)什么是TOP-N分析？
TOP-N查询在需要基于一个条件，从表中显示最前面的n条记录或者最后面的n条记录时是有用的。该结果可以用于进一步分析。
(2)从 employees表中显示挣钱最多的 3 个人的名字及其薪水。
select rownum,last_name,salary from (select last_name, salary from employees order by salary) where rownum <=3;
(3)显示 employees表中4 个资格最老的雇员显示他们的入职时间与名字。
select rownum,last_name,hire_date from (select last_name,hire_date from employees order by hire_date) where rownum <=4;

16.Oracle的分页查询
(1)什么是分页查询？
当查询的结果集数据量过大时，可能会导致各种各样的问题发生，例如：服务器资源被耗尽，因为数据传输量过大而使处理超时时等等。最终都会导致查询无法完成。解决这个问题的一个策略就是“分页查询”，也就是说不要一次性查询所有的数据，每次只查询部分数据。这样分批次地进行处理，可以呈现出很好的用户体验，对服务器资源的消耗也不大。
原则：在内建视图中通过ROWNUM伪劣值的判断来指定获取数据的数量。
(2)查询雇员表中数据，每次只返回10条数据。
select em.* from(select rownum rn,e.* from employees e) em where em.rn >=1 and em.rn <=10;

17.什么是序列
(1)什么是Oracle的序列？
序列是用户创建的数据库对象，序列会产生位移的整数。序列的一个典型的用途是创建一个主键的值，它对于每一行必须是唯一的。序列由一个Oracle内部程序产生并增加或减少。
序列是一个节省时间的对象，因为它可以减少应用程序中产生序列程序的代码量。序列号独立于表被存储和产生，因此，相同的序列可以被多个表使用。
(2)创建序列的语法结构是什么？
create sequence 序列名
increment by n（多少间隔）
start with n(从多少开始)
maxvalue n（最大值）
cache | nocache (缓存)
cycle | nocycle （循环）

18.创建序列
(1)创建一个序列名称为：dept_seq,增长间隔为10，从120开始，最大值为9999，不缓存。不循环使用。
create sequence temp_sq increment by 10 start with 120 maxvalue 9999 nocache nocycle;
(2)通过PL/SQL Developer创建和dept_seq相同的序列，并命名为dept_seq2。

19.使用序列
(1)Oracle记录序列的数据字典表叫什么？
user_sequences
(2)Oracle的序列中包含多少伪列？每个伪列的特点是什么？
1、NEXTVAL ：返回下一个可用的序列值，它每次返回一个唯一的被引用值，计时对于不同的用户也是如此。
2、CURRVAL：获取当前的序列值

20.修改删除序列
(1)修改序列的语法结构是什么？
alter sequence 序列名称
increment by n
maxvalue n
nocache
nocycle;
(2)修改序列的原则是什么？
必须是被修改序列的所有者，或者有ALTER权限。
用ALTER SEQUENCE语句，只有以后的序列数会受影响。
用ALTER SEQUENCE语句，STARTWITH选项不能被改变。伪列以不同的数重新开始一个序列，该序列必须被删除或重新创建。

21.什么是索引
(1)什么是索引？
在关系型数据库中，索引是一种单独的，物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引提供对表中行的直接和快速访问，它的目的是用已索引的路径快速定位数据以减少磁盘IO。索引由Oracle服务器自动使用和维护，索引逻辑地和物理地独立于他们索引的表，这意味着索引可以在任何时候被创建或删除，并且不影响基表或其他的所有。当删除表时，相应的索引也被删除。

(2)索引有哪些类型？
1、唯一索引：在一个表中定义一个列为主键，或者定义一个唯一键约束时Oracle服务器自动创建该索引，索引的名字习惯上是约束的名字
2、非唯一索引：由用户创建，例如，可以创建一个FROEIGN KEY列索引用于一个查询中的连接来改进数据取回的速度。

(3)创建索引的方式有几种？
1、自动：在一个表的定义中，当定义一个PRIMARY KEY或UNIQUE约束时，一个唯一索引被自动创建
2、手动：用户能够在列上创建非唯一的索引来加速对行的访问。

22.索引的使用
(1)什么情况下创建索引？
1、一个列包含一个大范围的值
2、一个列包含很多的空值
3、一个或多个列经常同时在一个WHERE子句中或一个连接条件中被使用
4、表很大，并且经常的查询期望取回少于百分之2到4的行

(2)什么情况下不创建索引？
1、表很小
2、不经常在查询中作为条件被使用的列
3、大多数查询期望取回多于表中百分之2到4的行
4、表经常被更新

23.创建索引(单行索引，复合索引，函数索引)
(1)Oracle的非唯一性索引包含哪些类型？
单行索引，复合索引（组合索引），函数索引
(2)创建索引的语法结构是什么？
创建单行索引
create index temp_index on employees(last_name);
创建多行索引
create index emp_index on departments(department_id,location_id);
创建函数索引
create index dept_upper on departments(upper(department_name));
(3)删除索引的语法结构是什么？
drop index 索引名称

24.同义词的使用
(1)什么是Oracle的同义词？
同义词可以除去对象名必须带的方案限制，并提供给你一个可替换表名、视图名、序列名和存储过程名或其他对象名。该方法对具有特别长的对象的名字很有用。
(2)创建同义词的语法结构是什么？
create synonym 同义词名 for 表名
(3)删除同义词的语法结构是什么？
drop synonym 同义词名

25.创建用户
(1)什么是Oracle的用户？
Oracle的用户是用来连接数据库和访问数据库对象的。
(2)创建用户的语法结构是什么？
create user 用户名 indentified by 用户密码
(3)删除用户的语法结构是什么？
drop user 用户名
(4)删除用户同时删除该用户下的其他对象的语法结构是什么？
drop user 用户名 cascaed;

26.授予用户系统权限
(1)数据库控制语言的作用是什么？
为用户分配权限

27.撤销用户系统权限
(1)撤销权限的句法结构是什么？
revoke 权限 from 用户

28.通过角色为用户授权
(1)什么是角色？
角色是命名的可以授予用户的相关权限的组，该方法使得授予、撤回和维护权限容易的多。一个用户可以使用几个巨额色色，兵器几个用户也可以被指定相同的角色。
(2)创建角色的语法结构是什么？
create role 角色名;
(3)为角色授予权限的语法结构是什么？
grant 权限 to 角色名
(4)为用户授予角色的语法结构是什么？
grant 角色 to 用户（可以多个，用逗号隔开）
(5)撤销用户角色的语法结构是什么？
revoke 角色 from 用户

29.什么是执行计划
(1)什么是执行计划？
执行计划是一条查询语句在Oracle中的执行过程或访问路径的描述。
(2)执行计划中的Cardinality、Bytes、COST、Time字段分别表示什么含义？
Cardinality基数：Oracle估计的当前操作的返回结果集行数
Bytes字节：执行该步骤后返回的字节数
COST耗费、CPU耗费：Oracle故居的该步骤的执行成本，用于说明SQL执行的代价，理论上越小越好（该值可能与实际有出入）
Time时间：Oracle估计的当前操作所需的时间
(3)执行计划中的执行顺序是什么顺序？
缩进最多的最先执行；（缩进相同时，最上面的最先执行）

30.执行计划中的表与索引的访问方式
(1)数据库中的TABLE ACCESS FULL表示什么含义？
全表扫描
(2)数据库中的TABLE ACCESS BY INDEX ROWID表示什么含义？
通过ROWID的表存取
(3)数据库中的TABLE ACCESS BY INDEX SCAN表示什么含义？
索引扫描

31.执行计划的使用
(1)通过执行计划分析查询employees表中的所有数据的查询语句。

(2)通过执行计划分析查询employees表中employees_id为100的雇员的查询语句。

(3)通过执行计划分析查询雇员名字为Tarloy的雇员的查询语句。

(4)通过执行计划分析查询雇员名字中含有a的雇员的查询语句。

32.Oracle的数据导入与导出
(1)数据库导入导出需要注意什么？
1、目标数据库要与源数据库有着名称相同的表空间
2、目标数据在进行导入时，用户名尽量相同
3、目标数据库每次在进行数据导入前，应做好数据备份，以防数据丢失
4、弄清是导入导出到相同版本还是不同版本
5、目标数据导入前，弄清楚是数据覆盖，还是仅插入新数据或替换部分数据表
6、确定目标数据库磁盘空间是否足够容纳新数据，是否需要扩充表空间
7、导入导出时注意字符集是否相同，一般Oracle数据库的字符集只有一个，并且固定，一般不改变。
8、确定操作者的账号权限

(2)导出数据格式有几种？每种的特点是什么？
1、Dmp格式：.dmp是二进制文件，可跨平台，还能包含权限，效率好
2、Sql格式：.sql格式的文件，可用文本编辑器查看，通用性比较好，效率不如第一种，适合小数据量导入导出。尤其注意的是表中不能有大字段，如果有，会报错。
3、Pdc格式：.pdc格式的文件，.pdc为PL/SQL Developer自有的文件格式，只能用PL/SQL Developer工具导入导出，不能用文本编辑器查看。
33.exp与imp命令讲解
(1)导出数据的命令格式是什么？
exp 用户名/密码 @ 连接地址：端口/服务名 file =路径/文件名 .dmp full=y|tables(tablename,tablename…)|owner(username1,username2,username3)
(2)导入数据的命令格式是什么？
imp 用户名/密码 @ 连接地址：端口/服务名 file =路径/文件名 .dmp full=y|tables(tablename,tablename…)|owner(username1,username2,username3)