1、ALTERDATABASESETDEFAULTBIGFILETABLESPACE;//修改表空间数据文件类型
2、ALTERTABLESPACE旧表空间名称TO新表空间名称;//修改表空间名称
8、orapwdfile=%ORACLE_HOME%\database\pwd
10、selectsysdatefromdual//查询数据库日期
11、select*fromnls_database_parameters;select*fromv$nls_parameters;select*fromprops$;//查询数据库安装时确定的参数值
12、exportNLS_LANG="simplifiedchinese_china".zhs16gbk或zhs16cgb231280exportORA_NLS33=$ORACLE_HOME/nls/admin/data//安装oracle时设置中文字符集的环境变量
13、updateprops$setvalue$='ZHS16GBK'wherename='NLS_CHARACTERSET';//修改oracle数据库核心字符集
14、expgiapsys/giapfile=/home/giapsys.dmplog=/home/giapsys.logdirect=y//导出giapsys用户的所有数据
15、createpfilefromspfile;//使用默认的服务器参数文件在默认路径中创建默认的文本参数文件
16、createspfilefrompfile;//使用默认的文本参数文件在默认路径中创建默认的服务器参数文件
26、selectt.table_name,t.initial_extent,t.next_extent,t.min_extents,t.max_extents,t.pct_increasefromuser_tablestselectb.segment_name,b.tablespace_name,b.extent_id,b.extent_id,b.bytes,b.blocksfromuser_extentsb
第一步：查看表空间的名字及文件所在位置：
selecttablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_spacefromsys.dba_data_filesorderbytablespace_name第二步：增大所需表空间大小：
alterdatabasedatafile'表空间位置'resize新的尺寸例如：
alterdatabasedatafile'\oracle\oradata\anita_2008.dbf'resize4000m对于oracle数据库的表空间，除了用手动增加大小外，还可以增加数据文件等方式扩展表空间大小。方法一：增加数据文件个数
altertablespace表空间名称adddatafile'新的数据文件地址'size数据文件大小例如：
altertablespaceESPS_2008adddatafile'\oracle\oradata\anita_2010.dbf'size1000m方法二：设置表空间自动扩展。
alterdatabasedatafile'数据文件位置'autoextendonnext自动扩展大小maxsize最大扩展大小例如：
alterdatabasedatafile'\oracle\oradata\anita_2008.dbf'autoextendonnext100mmaxsize10000m方法三：查询表空间使用情况：
selecta.tablespace_name,a.bytes/1024/1024"sumMB",(a.bytes-b.bytes)/1024/1024"usedMB",b.bytes/1024/1024"freeMB",round(((a.bytes-b.bytes)/a.bytes)*100,2)"used%"from(selecttablespace_name,sum(bytes)bytesfromdba_data_filesgroupbytablespace_name)a,(selecttablespace_name,sum(bytes)bytes,max(bytes)largestfromdba_free_spacegroupbytablespace_name)bwherea.tablespace_name=b.tablespace_nameorderby((a.bytes-b.bytes)/a.bytes)desc;一.数据文件的扩展1.alterdatabasedatafile'$home/data/df1.dbf'antoextendon;alterdatabasedatafile'$home/data/df1.dbf'autoextendoff;2.创建时启用createtablespacetestuserdatafile'$home/data/df1.dbf'autoExtendonnext10Mmaxsix500M3.DBA_DATA_FILES,AUTO_EXTENDselectname,auto_extendfromdba_data_files;
4.自动扩展属性--浪费性能一般关掉-----》手动扩展--》1.alterdatabasedatafile'/u03/oradata/userdata02.dbf'resize200M-->2.增加新文件，altertablespaceuserdataadddatafile'$home/'size500M
二.移动非SYSTEM数据文件--》使表空间脱机altertablespaceuserdataoffline;--》OScopy文件--》重命名altertablespaceuserdatarenamedatafile'11'to'22';--》使表空间连机--》使OS命令删除文件
三.移动SYSTEM数据文件1.关闭数据库2.使用OS移动3.startupmount4.alterdatabaserenamefile'system01.dbf'to'/user/system01.dbf';
四.删除表空间--system表和有激活undo的段无法，删除1.includingcontents删掉所有的段。2.includingcontentsanddatafiles删除掉对应的操作系统文件。3.cascadeconstraints;
五.得到表空间的信息得到表空间的信息dba_tablespacesv$tablespace得到数据文件的信息dba_data_files;v$datafile得到临时文件的信息dba_temp_files;v$tempfile;
六.临时表空间temp（只有一个临时段）--先sortmemory,后sortdisk---v$sysstatselect*fromv$sysstatwherenamelike'sortx';临时表的工作方式:一次分配，循环，共享使用。
1.创建createtemporarytablespacetemptempfile'/u01/oradata'size10M;
2.默认临时表空间（只能有一个）创建用户时，如果没有指定临时表空间，会使用数据库级别的临时表空间。
3.修改数据库临时表空间alterdatabasedefaulttemporarytablespacetemp2;
4.默认表空间不能被删除，不能offline.
归档路径管理：showparameterarchive;altersystemsetlog_archive_dest_1='location=d:\oracle\archivelog'scope=both;createpfilefromspfile;ALTERSYSTEMSWITCHLOGFILE;
删除表空间：DROPTABLESPACEusersINCLUDINGCONTENTS;DROPTABLESPACEusersINCLUDINGCONTENTSANDDATAFILES;
开启归档模式：SHUTDOWNSTARTUPMOUNTALTERDATABASEARCHIVELOG;ALTERDATABASEARCHIVELOGMANUAL;ALTERDATABASEOPEN;SHUTDOWNIMMEDIATE
系统进行一次归档：ALTERSYSTEMARCHIVELOGALL;ALTERSYSTEMSETLOG_ARCHIVE_MAX_PROCESSES=3;
1.创建表：
createtableIT_EMPLOYEES(EMPLOYEE_IDNUMERIC(6)NOTNULLUNIQUE,FIRST_NAMEVARCHAR2(20),LAST_NAMEVARCHAR2(25)NOTNULL,EMAILVARCHAR2(25),PHONE_NUMBERVARCHAR2(10),JOB_IDVARCHAR2(10),SALARYNUMBER(8,2),MANAGER_IDNUMBER(6));
2.创建视图：
(1)
createviewprog_employees_1asselectemployee_id,first_name,last_name,email,phone_number,salary,manager_idfromit_employeeswherejob_id='IT_PROG';
(2)
createviewprog_employees_1asselectemployee_id,first_name,last_name,email,phone_number,salary,manager_idfromit_employeeswherejob_id='IT_PROG';withcheckoption;
（3）删除视图
dropviewprog_employees;
3.创建索引
（1）Create【unique】【cluster】INDEX<索引名>ON<表名>(<列名><次序>)
例：
createindexIT_LASTNAMEONIT_EMPLOYEES(LAST_NAME);
用户可以在查询频率最高的列上建立聚簇索引，由于聚簇索引是将索引和表记录放在一起存储，所以在一个基表上只能建立一个聚簇索引。在建立聚簇索引之后，由于更新索引列数据时会导致表中记录的物理顺序的变更，系统代价较高，因此对于经常更新的列不适宜建立聚簇索引。
（2）删除索引
Dropindex<索引名>;
4.Alter
(1)增加一列
altertableIt_EmployeesaddBIRTH_DATEDATE;
（2）修改字段
altertableIt_Employeesmodifymanager_idnumber(8);
（3）删除约束条件
altertableIt_Employeesdropunique(employee_id);
4.select/groupby/having
注意：select子句后面只有两类表达式，统计函数和进行分组的列明。
Having子句对分组的结果进一步筛选，having子句与分组有关，而where子句与单个行有关。
selectdeptno,avg(sal),sum(sal),max(sal),min(sal),count(sal)fromempgroupbydeptno;
selectdeptno,avg(sal),sum(sal),max(sal),min(sal),count(sal),count(*)fromempgroupbydeptnohavingavg(sal)>2000;
5.内连接innerjoin/外连接（左leftouterjoin/右外连接rightouterjoin/全外连接full
区别：内连接进行多表查询时，返回的查询结果集中仅仅包含符合查询条件（where条件和having条件）和连接条件的行。内连接消除了与另一个表中的任何行不匹配的行，而外连接扩展了内连接的结果集，除返回所有匹配的行外，还返回不匹配的行。
selectempno,ename,dnamefromempinnerjoindeptonemp.deptno=dept.deptnowherejob='SALESMAN';
17499ALLENSALES
27654MARTINSALES
37844TURNERSALES
47521WARDSALES
55555lipengOPERATIONS
测试数据：deptno为null的
insertinto
empvalues(6666,'lipeng','SALESMAN',6666,to_date('2013-04-02','yyyy/mm/dd'),5000.00,1000.00,null);
selectempno,ename,dnamefromempleftouterjoindeptonemp.deptno=dept.deptnowherejob='SALESMAN'
15555lipengOPERATIONS
26666lipeng
37499ALLENSALES
57654MARTINSALES
67844TURNERSALES
从结果可以看出：做外连接不仅包含连接相匹配的行，而且还包含左表emp中所有满足where限制的行，而不论是否与右表相匹配。
5.union/unionall/intersect/minus
union将集合中的重复记录滤除，而unionall包含两个子结果集重复的行。
selectename,SALfromempwhereenamelike'S%'ORenamelike'J%'unionallselectename,SALfromempwhereenamelike'J%'ORenamelike'C%';
6.子查询in/exists/比较运算符
（1）selectempno,ename,deptnofromempwheredeptnoin(selectdeptnofromdeptwhereloc='CHICAGO');
执行顺序：先执行括号内的子查询，然后将查询到的deptno结果跟emp中的deptno进行比较，若列值存在于这些返回值中，则外层查询结果会在结果集中显示该行。
（2）selectempno,enamefromempwhereexists(select*fromdeptwhereemp.deptno=dept.deptnoandloc='CHICAGO');
（3）查询emp表，将将薪资大于本职位平均薪资的雇员信息显示出来。
selectempno,ename,sal,jobfromempwherejob='SALESMAN'andsal>(selectavg(sal)fromempwherejob='SALESMAN');
7.数据操纵INSERT/UPDATE/DELETE/TRUNCATE
updateempsetsal=(selectavg(sal)fromempwherejob='SALESMAN')whereempno=6666;commit;
deletefromempwhereempno=5555;
DELETE/TRUNCATE区别：
如果确定要删除表中所有记录时，建议使用TRUNCATE，因为TRUNCATE删除数据时要比DELETE快的多。但是TRUNCATE删除数据后，不能用rollback来恢复数据，但是delete可以用。
Reusestorage/dropstorage
Truncatetableempreusestorage;
Reusestorage表示删除记录后保存记录占用的空间。
dropstorage表示删除记录后立刻回收记录占用的空间。
grantselectonemptosup2db;
revokeselectonempfromsup2db;
9.字符类函数
selectASCII('A')big_A,ASCII('a')small_afromdual;
selectCHR(65),CHR(97)fromdual;
selectconcat('oracle','11g')oraclenamefromdual;
selectinitcap('helloworld')namefromdual;
selectreplace('feelblue','blue','yellow')fromdual;
日期函数：
selectadd_months(hiredate,1)fromemp;
第七章oracle数据库管理操作
一．增加安全性
解决的问题：例如，销售经理需要访问数据库中有关该部门员工的信息。但是该经理没有理由访问有关其它部门员工的信息。
connsys/change_in_stallassysdba;
（2）激活人力资源示例数据库账户，连接到HR示例数据库
Alteruserhridentifiedbyhraccountunlock;
Connhr/hr;
查看hr用户下面的所有表：
（1）连接到hr用户
（2）Selecttable_namefromuser_tables;
或者使用Select*fromtab;
(3)为销售经理创建用户ID，用户名salesmanger，密码sales，并将connect权限授予销售经理。
(4)Connsys/change_in_stall;
grantconnecttosalesmangeridentifiedbysales;
（4）定义一个查看员工编号和姓名的视图，将数据库中员工等信息隐藏起来。
createviewemp_sales
as
selectemployee_id,first_name,last_namefromhr.employees;
}
(5)授予salesmanger查看emp_sales视图的权限
grantselectonemp_salestosalesmanger;
connsalesmanger/sales
select*fromsys.emp_sales;
二．隐藏数据的复杂性
现有一机场数据库，拥有pilotSkills表和hanger表，
其中表pilotSkills描述了飞行员和他们能够驾驶的飞机信息，表hanger描述了停在飞机棚中的飞机信息。
现在要求查询能够驾驶飞机棚中每一架飞机的飞行员的姓名。
--创建pilotSkills表
createtablepilotSkills(pilotchar(15)notnull,planechar(15)notnull,primarykey(pilot,plane));createtablehanger(planechar(15)primarykey);
创建视图，实现“找出能够驾驶飞机棚中每一架飞机的飞行员的姓名”
CreateviewQualifiedPilots(pilot)AsSelectps.pilotfrompilotSkillsps,hangerhWhereps.plane=h.planeGroupbyps.pilotHavingcount(ps.plane)=(selectcount(plane)fromhanger);
selectpilotfromQualifiedPilots;
三、实现记录的唯一性
--增加主键
Altertablestuaddconstraitc1primarykey(sno);
--删除主键约束
Altertablestudropconstraitc1;
--创建一个名为ind1的唯一索引
Createuniqueindexind1onemp(empno);
当插入一条重复数据的时候会报错。
四．实现数据的完整性
Altertablestudentmodifysnamenotnull;
第八章数据库用户管理
1.创建一个用户使其具有登陆，连接的系统权限
createuserstuidentifiedbystu;
grantcreatesessiontostu;
2.用户HR将Employees表的查询、查询、更改表的对象的权限授予stu，那么stu具有了对HR的employees表的select对象权限，但不具备其它对象权限。
connhr/hr;
grantselect,insert,updateonemployeestostu;
selectfirst_name,last_name,job_id,salaryfromhr.employeeswheresalary>15000;
数据字典dba_roles可以了解数据库中全部的角色信息。
select*fromdba_roles;
角色connect、resource和DBA主要用于数据库管理。对于数据库管理员分别授予Connnect、resources和DBA角色。
创建角色:
createroleaccess_database;
Createroleaccess_databaseidentifiedby123;
grantcreatesession,createtable,createviewtoaccess_database;
（3）可将角色授予用户，使用户获得该角色所拥有的所有权限。
grantaccess_databasetoscott;
4.修改用户的默认角色
（1）设置用户的角色失效
Alteruserscottdefaultrolenone;
（2）设置用户角色生效
Alteruserscottdefaultroleall;
（3）查看session_roles视图，确认会话所用的角色
connectstu/stu;
select*fromsession_roles;
结果：
ROLE
------------------------------
CONNECT
RESOURCE
(4)为当前用户启用ACCESS_DATABASE角色。
Setroleaccess_database;
5.回收权限
*逐一回收
Connectsys/change_in_stallassysdba;
（1）系统权限的回收
收回scott用户的selectanydictionary系统权限。
Revokeselectanydictionaryfromscott;
（2）对象权限的回收
Hr用户回收scott对employees表的select对象权限。
Connecthr/hr
Revokeselectonemployeesfromscott;
用户HR将基表Employees的所有权限从public用户回收。
Revokeallonemployeesfrompublic;
6.删除角色
Droproleaccess_database;
删除用户：
Dropuserstu;
7.使用数据库连接
是为了访问远程数据库而创建的数据库通信链路。
链接到指定的用户：
CREATEDATABASELINKlink_nameCONNECTTOUSERIDENTIFIEDBYpasswordUSINGserver_name;
link_name:表示要链接到远程数据库名。
Server_name：表示远程数据库的服务名。
1.设置其它表空间初值
例如学生信息库，要把有关学生信息的一些表放到一个表空间中，如果有2000多名学生，则存储学生个人信息最多也不超过100MB的空间，但是为了保险，我们可以设置两个数据文件，每个数据文件的大小均设置为100MB
createTablespacestudent_info
datafile'C:\oracle\product\10.2.0\oradata\orcl\student01.dbf'size100M,'C:\oracle\product\10.2.0\oradata\orcl\student02.dbf'size100Mdefaultstorage(initial10Mnext10Mminextents1maxextents10pctincrease20)online;
说明：
Storage指定表空间的存储参数，这些参数对于数据库的性能影响很大，选着时要慎重。
initial10M：--表空间student_information初始空间大小为10MB
next10M：--当初始区间填满后，分配第二个区间的大小为10MB
pctincrease20：--当在填满时，按照20%的增长速率分配区间大小。
minextents1：--初始为该表空间分配1个区间
maxextents10：--最多为该表空间分配10个区间
2.空间充足的管理
（1）使用数据字典动态监视
这里使用的数据字典是dba_free_space和user_free_space,可以查看其内容来得到有关表空间的空间信息。
select*fromdba_free_space;
（2）向表空间增加数据文件
altertablespacestudent_infoadddatafile
'C:\oracle\product\10.2.0\oradata\orcl\student03.dbf'
size2M;
注意：通过上述方法给表空间分配太多的空间并不好，因为这样做无疑造成较大空间的浪费。因此建议做好空间估计，并合理利用空间，无论是对减少资源浪费还是提高系统性能都有好处。
3.解决空间不足的方法
扩充数据库存储空间常用的方法有如下三种：
（1）增加SYSTEM表空间中数据文件的大小
（2）创建新的表空间
（3）创建新的数据文件
数据库中的数据其实都是存储在数据文件中的，SYSTEM表空间的数据文件是在创建数据库的时候给定的，并且给其大小给定了一个初值。那么System表空间不够时就可以AlterDataBase命令动态的增加SYSTEM表空间数据文件的大小。
查11g
AlterDATABASEorclDatafile'C:\oracle\product\10.2.0\oradata\orcl\system01.dbf'resize750M;
(2)创建新的表空间
表空间其实是一个逻辑概念，它所有数据和结构信息都存储在一个或者多个数据文件当中，当需要扩充数据库存储空间时，可以创建新的表空间并指定它的数据文件，系统就会划出一块磁盘空间给这个表空间.
注意：创建数据库时最好能创建几个私用的表空间，因为SYSTEM表空间是系统表空间，其中存储数据字典和数据库结构等重要信息，他是数据库运行的基础，若是把所有信息都存放在这个表空间里，一方面会迅速占满它的空间，另一方面也加大了出错的可能性。
1.Createtablespace命令简介
Createtablespace表空间名
Datafile{
文件名[autoextend{off|onnext数值maxsize数值}]，
Mininumextend数值
Logging|nologging
Defaultstorage{...}
Online|offline
Permanent|temporary;
2.用createtablespace创建表空间
createtablespacetestdatafile'C:\oracle\product\10.2.0\oradata\orcl\test01.dbf'size2Mdefaultstorage(initial2Mnext2Mminextents2maxextents10pctincrease20)online;
向表空间中增加数据文件使用的命令：
Altertablespace表空间名
Loggin|nologging
Adddatafile{数据库文件名[autoextend],...}
Renamedatafile原文件名to新文件名
Coalesce
Defaultstorage
Online|offline[normal|temporary|immediate|forrecover]
[begin|end]backup
Readonly|write
Permanent|temporary
Adddatafile：用于增加数据文件，可在联机或者是脱机下增加，但所增加的数据文件不能是其它表空间或者数据库已经使用的，它同样可带autoextend参数选项。
Coalesce：用于所有相连的空间范围合并到相邻较大的范围当中去，这一项不能被其它命令所指定。
[begin|end]backup:用于开始或者结束联机备份表空间的数据文件，在备份过程中用户可以继续访问该表空间，但备份过程中不能将表空间脱机，也不能关闭数据库。
Readonly|write：其中Readonly表示此表空间的内容是只读的，不能像其中写入任何数据，而readwrite则可以对此表空间的数据进行读写操作。
向表空间test中增加两个大小为10MB的数据文件
Altertablespacetest
Adddatafile
'C:\oracle\product\10.2.0\oradata\orcl\test02.dbfsize10M';
createtableEmployee(
idintprimarykey,
namechar(20),
birthdayDate,
addressvarchar(30),
cityvarchar(10),
sexchar(2),
salarynumeric(10,2),
dnoint,
pnoint
unique(name,birthday)
);
idint,
dnointReferenceDepartment(deNumber),//外键约束
unique(name,birthday)//候选键
primarykey(id)//员工表的主键
外部键约束增加了关系数据库表的关联完整性，可以较好的保证数据库表之间的关联完整性。主键所在的表成为父表，为主控方，对应的外部键所在的表为子表，为被控方。
dnointReferenceDepartment(deNumber)工作人员所属的部门号(dno)是相对于Department部门表的外部键。这样定义以后若想添加一个工作人员的记录，添加的记录中dno列的值必须在Department记录中存在与其相同的值，否则就无法添加记录。
也可以用另一种方式添加外键，若对应的主键是多个列的组合，那么外部键的定义只能放在所有列之后。只能是这种定义：
dnoint，
pnoint，
foreignkey(dno)referencesDepartment(deNumber)
在对子表中的外部键或对父表的主键进行更新时（可能会产生的关联完整性的问题的四种情况）总结：
1.在子表中添加记录：
在子表Employee中添加一个记录，其dno列的值必须与Department表中dNumber列中的一个值相同。或者添加dno列值为NULL，否则将破坏数据库的完整性。但是向父表Department中添加新记录不会产生这个问题。
2.更新子表外部键的值：用update更新后的值必须与父表中的一个主键相匹配，或者更新为NULL，否则将产生错误。
3.删除父表中的记录：从父表中删除一个记录，子表中对应该值的外部键的记录不在与父表中的任何一个主键值相匹配。例如从父表Department表中删除dNumber列值为3的记录，那么子表Employee表中所有dno列值为3的记录（即员工所在部门为3号）的记录都将产生没有匹配主键值的问题。然而从子表中删除记录并不会产生这个问题。
4.更新父表中主键的值：父表Department中更新一个主键的值后，子表Employee中与原来值相对应的外部键的值也将不再与父表中任一主键的值相匹配。
下面是解决上面问题的方法：
1.使用Restrict关键字：父表中的主键值在子表中有许多具有该值的外部键的记录时，若在约束中指定Restrict关键字，则企图删除父表中的语句将被拒绝，企图更新父表中记录的主键值也将被拒绝。
2.CASCADE关键字：当父表中一个记录被删除时，cascade可以使其子表中所有与该记录的主键值对应的记录的（例如父表Department中的dNumber为3的值被删除，在子表中外键dno为3的记录都将被删除）都将从子表中删除。而更新父表中一个主键值时，CASCADE可以使其子表中对应外部键的值也自动被修改。
3.SETNULL：当父表中一个记录被删除时，SETNULL可以使其子表中所有与该记录的主键值对应的外部键的值将自动被赋值为NULL。更新同理。
4.SETDEFAULT：当父表中一个记录被删除时，SETDEFAULT可以使其子表中的所有与该记录的主键值对应的外部键的值都将被赋值为缺省值。
NULL值并不表示0值或空值，而是表示一个丢失、不知道，不可用的值。
举例子说明该约束：例如，Employee表中，如果没有name就无法知道工作人员的名字，一般来说公司都要记录工作人员的名字，因此name的值可以规定为NOTNULL,而工作人员的生日，地址，等并不是一定要有的，也允许其丢失，这些列值允许为NULL。
作为区分每个表的主键的值，不可以为空值，而对于外部键来说，NULL值是允许存在的。
举例来说明：在Company数据库中，Employee表中pno列是相对于Project的外部键，并不是公司的一个员工都参与一个工程项目的工作，如总裁等。因此Employee表中的pno允许有NULL值存在，（员工表Employee的总裁的外部键pno可以为NULL）。
举例来说明：在Employee增加校验约束条件
SalaryNUMERIC（10,2）CHECK（Salary>1000.00）后Employee表中拒绝小于等于1000的所有记录。
用UPDATEEmployeeSetSalary=900.00whereid=2002，该语句产生错误。
在员工表中sex列一般限制男和女两个值，可以增加一个检验约束来实现对sex列的限制。
sexchar(2)check(sexin('男'，'女')),
Constraintsex_checkCHECK(sexin('男','女'))，
Constraintsalary_checkCHECK(salary>1000.00)，
使用Constraint关键字进行检验约束的定义。这样我们可以在以后必要的时候对定义的约束进行激活和撤销。
举例：salarynumeric(10,2)default3000.00，当对表添加记录时，若对设有默认值的列没有指定明确的值，系统自动会进行默认的添加。
InsertintoEmployeevalues('peter','2012-02-20','','北京','男',1004,1,3);
1.Employee表：
namechar(15)NOTNULL,/*员工名字*/
birthdayDate,/*员工生日*/
addressvarchar(30),/*员工住址*/
citychar(10),/*员工所在城市*/
sexchar(2)check(sexin('男'，'女'))default'男',
salarynumeric(10,2),/*员工工资*/
dnointReferenceDepartment(dnumber),/*外键约束部门号*/
pnoint,
foreignkey(dno)referencesDepartment(pnumber),/*外键约束项目号*/
--unique(name,birthday)//候选键
--primarykey(id)//员工表的主键
Constraintsalary_checkCHECK(salary>1000.00),
2.Department部门表
CreatetableDepartment(
dnumberintprimarykey,/*主键约束部门号*/
dnamechar(20)unique,/*公司中每个部门的名称应该是不同的*/
mgridint,/*部门负责人的id号*/
mgrdateDATE/*负责人上任日期*/
3.Project项目表的
CreatetableProject(
pnumberintprimarykey,/*项目号*/
pnamevarchar(20)unique,/*项目名称*/
pmgridint,
budgetNUMRIC(10,2),/*项目预算*/
GrossNUMRIC(10,2),/*预计总收入*/
4.Paccpeter项目接收方表：
说明：由于不同项目的接收方可以相同，因此以pnumber列作为表的主键。同时pnumber列是Paccpeter表相对于Project表的外部键。若父表Project不存在的项目号在子表Paccpeter也不能存在。
CreatetablePaccpeter(
accepterchar(20)NOTNULL,/*项目接收方不允许为空*/
citychar(10),
acceptdateDate,/*接收日期*/
pnumberreferenceProject(pnumber)/*外键pnumber*/
5.Dep_Pro部门和项目连接表的创建：
说明：表中只包含两列：记录部门号的dnumber和记录项目号的pnumber，该表中以二者的组合作为主键。同时dnumber是相对于Department表的外部键，而pnumber列是相对于Project表的外部键。
CREATETABLEDep_Pro(
dnumberINT,
pnumberINT,
PRIMARYKEY(dnumber,dnumber),
dnumberREFERENCESDepartment(dnumber),
pnumberREFERENCESDepartment(pnumber),
1.在单列上创建索引：
在员工表id列上创建索引：
CREATEINDEXid_indexONEmployee(id);
Selectname,sex,id,salaryfromEmployee;
查询的结果会按照索引的顺序进行排列。
2.在多列上创建索引：
CREATEINDEXname_salary_indexONEmployee(name,salary);
在多列上创建索引时将按照列出的顺序进行排列。先将按照name进行索引，然后在按照salary进行索引。
3.创建唯一索引：
唯一索引可以在单列也可以在多列上创建，唯一索引可以避免相同值的多个记录的出现，唯一索引是指两个记录中没有同一个索引值。
CREATEUNIQUEINDEXid_indexONEmployee(id);
1.向表中添加列：
ALTERTABLETablename
ADDcolumnnamedatatype
[DEFAULTexpression]
[REFERENCESTablename(columnname)]
[CHECKconstraint]
例如：如果要在Department表中添加city列,默认部门所在城市为北京。
AltertableDepartmentaddcitychar（10）default'北京';
也可以添加新列的约束：例如，公司新规定每个项目的预计总收入不得少于10000.00元，
则可以为Project表中添加一个约束：
ALTERTABLEProjectCONSTRAINTgross_check(gross>10000.00);
2.修改已有列
MODIFYcolumnname[datatype]
例如，将Employee表中的name列的长度变成30个字符长：
ALTERTABLEEmployeeMODIFYNAMECHAR(30);
不能修改数据类型，即不能从int改到char，可以减少字符串的长度，然而，
不能长度少于已有数据中长度最大的值。
也可以修改列所具有的约束：
例如，将Employee表中sex列的默认值从“男”修改成“女”：
ALTERTABLEEmployeeMODIFYsexDEFAULT'女';
在SQL中，也可以使用ALTERTABLE语句删除表中已有的列。
例如从Employee表中删除birthday：
ALTERTABLEEmployeeDROPbirthday;
Dropindexindexname;
使用索引的原因总结：
1.适合使用索引的情况：
（1）大量值：表中的记录在该列上含有大量不同的值时，为表建立索引可以起到很好的作用。
（2）经常在查询中使用：某列在查询中使用的越多，在该列上创建的索引所能起到的加快速度的作用越明显。
（3）查询返回记录相对较少时：当返回的记录大大少于表中的记录总量的时候，索引能很好的加快查询速度。若总是返回大量的记录，由于索引本身的开销，索引并不能很好的提高检索的速度。
（4）用于两个表连接的操作：由于索引使每个表中的记录按顺序排列，所以使用被索引的列进行连接操作，可以提高连接速度。
2.不适和建立索引的情况：
(1)小型表：对于小型表，由于表中的记录不多，而索引自身也会增加开销，通过使用索引并不能提高性能，所以小型表不使用索引。
(2)只有很少值的列不适合建立索引。
(3)用户查询方式经常变化的表不适合建立索引。
(4)在具有较多的NULL值的列不适合创建索引。
(5)定期更新或者修改的列不断的更新或者修改导致索引页需要进行不断的更新，额外的消耗过多。
10.多表连接查询：
1.什么是连接？
定义：连接是将多个表的数据结合到一起的查询，即连接操作可以在一个select语句中完成从多个表中查找和处理数据。使用连接时可以使用相同名字的列，例如可以使用pnumber连接Project表与Paccpter表；也可以使用不同名字的列进行连接，例如使用Employee表中的id列与Department的mgrid列进行连接。进行连接时用于连接的必须是可连接的列，即要求它们具有相同的数据类型。
一般来说，连接的语法可以分为二种：
（1）传统的连接语法：selectfrom/where包含多个表的连接查询
传统的FROM/WHERE连接语法基本格式如下：
SELECTselect_list
FROMTable1name,[Table2name,...]
WHERE[Table1name.]columnoperator[Table2name.]column
用上面的表：
例如：查询部门负责人的负责的部门号、负责人名字、id号、性别及工资。
分析：需要连接连个表：Employee表和Department表
原因：在Employee表中并没有存储谁是什么部门的负责人，负责人的id号存储在Department表中。（参照上面的表）
Selectdnumber,name,id,sex,salaryfromEmployee,Departmentwhereid=mgrid;
（2）SQL连接语法：join关键字来实现连接的操作JOINCROSSJOIN及NATURAL
FROMTable1name[CROSS|NATURAL]JOINTable2name
[ON[Table1name.]columnoperator[Table2name.]column]
[WHEREconditions]
●第一行中的select_list仍然是选择的列的名称，但是需要注意的是，如果使用两
个表中有相同名称的列，则必须限定是哪个表中的列；
●第二行中关键字CROSS与NATURAL是可选项，可以使用也可以不使用。如果
选择了CROSS或NATURAL关键字，那么就不能使用ON关键字；
●单独使用JOIN关键字时，需要使用ON关键字来设定连接的条件；
●使用CROSSJOIN时，不能使用关键字ON，因此必须使用WHERE子句设定连
接的条件。因为如果不设定连接条件，将返回表之间的每一种可能组合(即笛卡
尔积)，连接结果将非常庞大；
●NATURALJOIN只有在两个表有相同名称的列时才能使用，将在同名列上进行连
接。因此，不必使用关键字ON或WHERE子句设定连接条件。当然，使用WHERE
子句限制查找的行仍然是可以的。
例如查询：部门负责人的负责的部门号、负责人名字、id号、性别及工资。
用join连接：
Selectdnumber,name,sex,id,salaryfromEmployeejoinDepartmentonmgrid=id;
Results
dnumbernamesexidsalary
——————————————————
6魏成男60015000.00
1林志千男10016000.00
2陈广海男20015000.00
3张宇男30015000.00
4张峰男40017000.00
5李志深男50015000.00
用crossjoin连接：
SELECTdnumber,name,sex,id,salaryFROMEmployeeCROSSJOINDepartment
WHEREid=mgrid
结果是与上面相同的。
但是这里不能用NATURALJOIN连接。虽然Employee的id列和Department表mgrid列的数据类型和存储的内容相同，但是名称不相同，所以不能用自然连接。
Project的表和Paccpter表中有两个相同的列pnumber，则可以对这两个表进行NATURALJOIN连接。
Selectpnumber,accepter,budgetfromProjectNATURALJOINPaccpter;
pnumberaccepterbudget
—————————————————
101蓝科通讯公司110000.00
102华夏技术有限公司140000.00
103汉升咨询公司150000.00
104神州发展有限公司120000.00
105四海贸易有限公司100000.00
201化天通讯公司200000.00
202日胜公司220000.00
203华田汽修公司250000.00
204东胜有限公司240000.00
301科华贸易公司300000.00
302新安有限公司320000.00
303华田汽修公司360000.00
也可以用传统的from/where循环代替：
Selectpnumber,accepter,budgetfromProject，PaccpterwhereProject.pnumber=Paccpter.pnumber;
用joinon代替：
Selectpnumber,accepter,budgetfromProjectjoinPaccpteronProject.pnumber=Paccpter.pnumber;
2处理连接：
两个表有意义的连接到一起，必须有公共数据。
Project表：
SELECTpnumber,budgetFROMProject;
pnumberbudget
—————————
101110000.00
102140000.00
103150000.00
104120000.00
105100000.00
201200000.00
202220000.00
203250000.00
204240000.00
301300000.00
302320000.00
303360000.00
Paccepter表:
SELECTpnumber,accepterFROMPaccepter;
pnumberaccepter
———————————
101蓝科通讯公司
102华夏技术有限公司
103汉升咨询公司
104神州发展有限公司
105四海贸易有限公司
201化天通讯公司
202日胜公司
203华田汽修公司
204东胜有限公司
301科华贸易公司
302新安有限公司
303华田汽修公司
在理想的情况下，对数据库中的表进行连接时使用表的主键与相应外部键。因为，设计数据库时，主键与外部键在逻辑上就是互相联系的，并且主键与外部键之间具有一致性，会保持主键列与外部键列在值上的一致。主键与外部键可以很好的保持数据库完整性。
较好的连接应该符合以下要求：
●FROM子句中必须包含用于连接的所有表，不能仅包含一部分用于连接的表；
●进行连接的列名称可以不同，除非要使用NATURALJOIN，如果需要使用相同
名称的列必须为其加上表名作为限制，不然会产生混淆；
●用于连接的列必须有相同的数据类型，或可以自动进行类型转换。不同类型的数
据是不能进行连接的。例如，不能使用Employee表中的birthday列与Department
表中的dname列进行连接；
●用于连接的列必须具有相同的意义，这是最重要的一点，没有相同意义的列的连
接是毫无用处的。例如，虽然Employee表中的name列与Department表中的dname
列数据类型相同，但是这两个列毫不相干，连接是没有意义的。
查询的时候必须指定查询条件：
例如：查找正工作在与项目接收方所在城市相同的工作人员。
方法一：Selectid,name,sex,cityfromEmployee,PaccepterwhereEmployee.city=Paccepter.city;
方法二：selectid,name,sex,cityfromEmployeejoinPaccepterwhereEmployee.city=Paccepter.city;
如果没有连接条件，查询的结果将是两个表的笛卡尔积。结果行数将大的惊人。
3．笛卡尔积
表一：
ABC
——————
abc
def
bcd
表二：
DE
————
de
ef
则表一和表二的笛卡尔积为：
ABCDE
——————————
abcde
abcef
defde
defef
bcdde
bcdef
最后可以有：3*2=6条记录。
用于指定连接的表越多，其笛卡尔积的结果就越惊人，越需要很好的指定连接条件。
由于连接过程中使用了笛卡尔积，因此连接操作会导致性能的下降。
使用连接时应该遵循一定的原则：
●用于连接的列已经创建了索引。因为索引会单独保存在磁盘上，且将数据按照一
定顺序进行了排列，索引的使用可以加快访问的速度；
●用于连接的列具有相同的数据类型，包括是否允许空值。如果需要系统自动进行
4.连接中运算符号的使用：
Selectpnumber,accepter,penddate,acceptdatefromProject,Paccepterwherependdate>acceptdateANDProject.pnumber=Paccepter.pnumber;
Results:
pnumberaccepterpenddateaccedate
——————————————————————
201化天通讯公司2001-12-202001-12-15
303华田汽修公司2002-04-252002-04-15
[2rows]
Selectpnumber,accepter,penddate,acceptdatefromProject,Paccepterwherependdate<>acceptdateANDProject.pnumber=Paccepter.pnumber;
204东胜有限公司2002-08-012002-08-06
[3rows]
在进行连接的时候还可以使用where子句限制查找到的行。
例如，只需要查看项目号小于201的所有项目的项目编号、接收方及项目成本。
方法一:
Selectpnumber,accepter,budgetfromProject,PaccpeterwhereProject.pnumber=Paccpeter.pnumberANDProject.pnumber<201;
方法二：
Selectpnumber,accepter,budgetfromProjectjoinPaccpeteronProject.pnumber=Paccpeter.pnumberwhereProject.pnumber<201;
———————————————
101科华贸易公司110000.00
103科华贸易公司150000.00
[5rows]
5．多表的连接：
需要知道工作人员本身以及其所属的部门和所参与的项目的详细信息，
Employee表、Department表、Project表这三个表以得到所需的信息。
但是多个表连接或者多个表连接时引起的性能下降更加严重。
例如：将返回工作人员本身以及其所属的部门和所参与的项目的详细信息：
分析：需要连接三个表：Employee表，Department表，Project表。
Selectid,name,dnumber,dname,mgrid,pnumber,pname,grossfromEmployee,Department,Projectwheredno=dnumberANDpno=pnumber;
数据库设计时介绍了三种关系和数据库连接的关系：
（1）一对一的关系：
例如：项目Project表和项目接收Paccepter表时一对一的关系：
可以通过项目号pnumber进行连接：
方法一：Selectpnumber,accepter,budgetfromProjectjoinPaccepteronProject.pnumber=Paccepter.pnumberwhereProject.pnumber=102;
方法二：Selectpnumber,accepter,budgetfromProject,PaccepterwhereProject.pnumber=Paccepter.pnumberANDProject.pnumber=102;
Result：
——————————————
102华夏技术有限公司140000.
[1row]
（2）一对多的关系：
一个表的一条记录可以对应另一个表的若干条记录：
查询的时也可以对两个表进行连接：例如：部门和工作人员之间就是一对多的关系。
希望由工作人员记录得出其所属的部门的信息就需要进行连接：
Selectid，name，dnumber,mgridfromEmployee,Departmentwheredno=dnumberANDname=‘lipeng’;
Selectid,name,dnumber,mgridfromEmployeejoinDepartmentondno=dnumberwherename=‘lipeng’;
nameiddnumbermgrid
李明100211001
(3)多对多的关系：
无法对多对多的两个表进行连接，则必须使用创建数据库时专门为多对多的关系创建的连接表来实现。因此需要包含三个表的组合来连接数据。
例如：一个部门可能同时进行一个或者多个项目，一个项目可能由一个或者多个部门来配合才能很好的完成。
部门与项目间存在着多对多的关系。可以通过连接表来实现部门与项目的连接。
SELECTdnumber,mgrid,pnumberFROMDepartmentJOINDep_pro
ONDepartment.dnumber=Dep_pro.dnumberProjectJOINDep_proONProject.pnumber=Dep_pro.pnumberWHEREProject.pnumber=301
SELECTdnumber,mgrid,pnumberFROMDepartment,Project,Dep_pro
WHEREDepartment.dnumber=Dep_pro.dnumberAND
Project.pnumber=Dep_pro.pnumberAND
Project.pnumber=301
oracle导入导出，如何创建一个账户，并在这个账户中导入导出表
1.如何在数据库中创建一个账户：
第一,启动sql*puls第二，以system/manager登陆第三，createuser用户名IDENTIFIEDBY密码（例如：bione1/bione1）第四，GRANTCREATEUSER,DROPUSER,ALTERUSER,CREATEANYVIEW,DROPANYVIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,DBA,CONNECT,RESOURCE,CREATESESSIONTO用户名字
2.导入，导出
导入导出命令：Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。exp命令可以把数据从远程数据库服务器导出到本地的dmp文件，imp命令可以把dmp文件从本地导入到远处的数据库服务器中。利用这个功能可以构建两个相同的数据库，一个用来测试，一个用来正式使用。执行环境：可以在SQLPLUS.EXE或者DOS（命令行）中执行，DOS中可以执行时由于在oracle10i中安装目录ora101BIN被设置为全局路径，该目录下有EXP.EXE与IMP.EXE文件被用来执行导入导出。oracle用java编写，SQLPLUS.EXE、EXP.EXE、IMP.EXE这两个文件有可能是被包
将D:\bione.dmp表中的数据导入到orcl数据库中。
（1）当创建完用户后，找到安装oracle的bin目录，例如C:\oracle\product\10.2.0\db_1\bin。
（2）打开cmd窗口，输入cdC:\oracle\product\10.2.0\db_1\bin，然后再输入：impbione1/bione1@orclfile=D:\bione.dmpignore=y;
（3）导入成功。
导出：：（1）将数据库TEST完全导出,用户名system密码manager导出到D:daochu.dmp中expsystem/manager@TESTfile=d:daochu.dmpfull=y（2）将数据库中system用户与sys用户的表导出expsystem/manager@TESTfile=d:daochu.dmpowner=(system,sys)（3）将数据库中的表inner_notify、notify_staff_relat导出expaichannel/aichannel@TESTDB2file=d:datanewsmgnt.dmptables=(inner_notify,notify_staff_relat)（4)将数据库中的表table1中的字段filed1以"00"打头的数据导出expsystem/manager@TESTfile=d:daochu.dmptables=(table1)query="wherefiled1like'00%'"上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。也可以在上面命令后面加上compress=y来实现。
1.eda
@a
通过记事本的方式编译：
Setlinesize300;
Setpagesize30;
2.
3.在sqlplus中也可以使用其他用户连接，例如之前了解过的sys和system用户；
使用如下命令：conn用户名/密码[ASSYSDBA/SYSOPER]
例如：connsystem/manager;连接system用户
Connsys/change_on_installassysdba;连接sys用户
4.如果在不同用户（scott用户/sys用户）下面想访问emp表的话，在必须加上用户名。即：表的完整名称：“scott.emp”
5.如果想知道当前连接的是哪个用户怎么办呢？
可以通过showuser；显示当前正在连接的用户是哪一个。
使用select*fromtab；查找数据库中有多少个表
用desc+表名的形式查看表的结构。
6.你可以输入一个“/”,表示重复执行上一条语句的操作。
7.上面的是在scott/tiger模式下面的操作，如果想进入hr模式，因为默认的hr模式已经被锁定，就需要对该用户模式解锁。