数据库移植等0326.doc

1 ORACLE数据库导入与导出21.1 创建用户并授权：CREATE USER 用户 identified by 口令;GRANT 权限 TO 用户名;21.2 导入ORACLE数据备份文件:Imp UserId 用户/口令 Ignore=y FromUser=用户 ToUser=用户 File=源文件21.3 查看用户所有对象(user_objects)并增删改记录(Insert/Update/Delete)21.4 导出ORACLE数据备份文件:Exp 用户/口令 File=目标文件 Owner=(用户)21.5 删除用户其及所有的对象:Drop User 用户 Cascade;22 SQL数据库附加与分离32.1 SQL数据库附加32.2 SQL数据库分离53 表的创建与删除53.1 创建表:Create Table 表名(列名1 数据类型,列名n 数据类型)63.2 删除表:Drop Table 表名64 列增删改64.1 增加列:Alter Table 表名 Add 列名 数据类型;64.2 删除列:Alter Table 表名 Drop Column 列名;64.3 改列属性:Alter Table 表名 Modify 列名 数据类型;64.4 改列名称: Alter Table 表名 ReName Column 原列名 To 新列名;65 行增删改(日期操作)65.1 增加行:Insert Into 表名(列名1,列名n) values(值1,值n);65.2 删除行:Delete 表名 where 条件;75.3 修改行:Update 表名Set 列名=值;76 行查询86.1 简单查询96.1.1 查询所有行所有列96.1.2 指定列查询106.1.3 distinct查询116.1.4 统计函数126.1.5 常用函数136.1.6 SQL*Plus变量的使用146.1.7 SQL*Plus清屏(含缓冲区):Shift+Delete再单击确定176.1.8 SQL*Plus执行操作系统命令:Host 命令176.1.9 SQL*Plus显示表结构:Describe 表名176.1.10 显示表名:User_Objects和User_Tables|DBA_Tables176.2 排序查询所有行:Order By196.2.1 升序查询所有行:Order By 列名 Asc196.2.2 降序查询所有行:Order by 列名 Desc216.2.3 升降序查询所有行:Order by 列名 Desc/Asc,列名 Desc/Asc216.3 条件查询:Where 条件226.3.1 关系运算226.3.2 逻辑运算符236.3.3 Like查询246.3.4 IN查询256.3.5 算术求余后作为条件查询266.4 子查询276.5 分组统计查询:Group By 条件 Having 条件276.5 约束326.5.1 Default326.5.2 Primary Key331 ORACLE数据库导入与导出1.1 创建用户并授权：CREATE USER 用户 identified by 口令;GRANT 权限 TO 用户名;用system/orcl登录sqlplusw，并创建用户school，密码school,并给school赋于相应的权限。CREATE USER school identified by school;GRANT CREATE SESSION,RESOURCE,DBA TO school;1.2 导入ORACLE数据备份文件:Imp UserId 用户/口令 Ignore=y FromUser=用户 ToUser=用户 File=源文件将备份文件school.DMP复制到E盘根目，然后启动命令提示窗口，运行如下命令将school.DMP还原到服务器。Imp UserId=school/school Ignore=y FromUser=school ToUser=school File=e:school.DMP1.3 查看用户所有对象(user_objects)并增删改记录(Insert/Update/Delete)sqlplusw切换到school用户查看所有对象，发现有一个class表,并查询其内容，并增改删除。conn school/school;select SUBSTR(OBJECT_NAME,1,20) as 对象名,OBJECT_TYPE as 类型 from user_objects;Select * from class;Insert Into class values(201208811,D005,12软件1班);Insert Into class values(201208812,D005,12软件2班);Insert Into class values(201208813,D005,12软件3班);Select * from class;Update class Set DEPARTMENT=D001 where CID=201208813;Select * from class;Delete class where CID=201208811;Select * from class;Commit;1.4 导出ORACLE数据备份文件:Exp 用户/口令 File=目标文件 Owner=(用户)命令提示窗口中将school用户下的所有对象导出到d:school.DMPExp school/school File=d:school.DMP Owner=(school)1.5 删除用户其及所有的对象:Drop User 用户 Cascade;sqlplusw中切换回到system用户删除用户SCHOOL 以及用户SCHOOL所有的对象Conn system/orcl;Drop User school Cascade;/Cascade参数是级联删除该用户所有对象，经常遇到如用户有对象而未加此参数则用户删不了的问题，所以习惯性的加此参数同样方法实现vendition 的导入。首先创建用户vendition,并赋于相应的权限。Create User vendition identified by vendition;Grant Create SESSION,RESOURCE,DBA TO vendition;将备份文件vendition.DMP复制E盘根目，然后启动命令提示窗口，运行如下的还原命令。Imp UserId=vendition/vendition Ignore=y FromUser=vendition ToUser=vendition File=e:vendition.DMPExp vendition/vendition File=d:vendition.DMP Owner=(vendition)2 SQL数据库附加与分离2.1 SQL数据库附加2.2 SQL数据库分离3 表的创建与删除数据类型SQLAccessORACLE整型int数字整型Int同Integer浮点型float数字单精度型Float,Number(p,s)字符型varchar(20)文本varchar(20)同varchar2(20)日期型datetime日期/时间Date,TimeStamp备注型text备注CLOB图片imageOLE 对象BLOB布尔(位)bit是/否自动编号Int indentity自动编号序列实现类似自动编号功能3.1 创建表:Create Table 表名(列名1 数据类型,列名n 数据类型)Create Table stu(xh int primary key,xm varchar(8),cj FLOAT,BZ CLOB,XP BLOB);Insert Into Stu(xh,xm,cj,BZ) values(1,AAA,67,AAA);Insert Into Stu(xh,xm,cj,BZ) values(2,BBB,1234,BBB);Insert Into Stu(xh,xm,cj,BZ) values(3,CCC,83,CCC);SQL:Create Table stu(xh int primary key,xm varchar(8),cj FLOAT,BZ Text,XP Image);3.2 删除表:Drop Table 表名Drop Table Stu;4 列增删改4.1 增加列:Alter Table 表名 Add 列名 数据类型;Alter Table stu Add CS Date;SQL: Alter Table stu Add CS DateTime;4.2 删除列:Alter Table 表名 Drop Column 列名;Alter Table stu Drop Column XP;4.3 改列属性:Alter Table 表名 Modify 列名 数据类型;Alter Table stu Modify xm varchar(20);4.4 改列名称: Alter Table 表名 ReName Column 原列名 To 新列名;Alter Table stu ReName Column xm To 姓名;SQL:Alter Table stu Add CJ1 Integer,CJ2 Integer;SQL:Alter Table stu Drop Column CJ1;SQL:Alter Table stu Alter Column xm varchar(8);5 行增删改(日期操作)5.1 增加行:Insert Into 表名(列名1,列名n) values(值1,值n);SQL Insert Into Stu(xh,xm,cj,CS) values(5,AAA,67,Date2013-03-12);1 row created.SQL: Insert Into Stu(xh,xm,cj,CS) values(5,AAA,67,2013-03-12);ACCESS: Insert Into Stu(xh,xm,cj,CS) values(5,AAA,67,#2013-03-12#);或Insert Into Stu(xh,xm,cj,CS) values(5,AAA,67,2013-03-12);SQL select xh,xm,cs from stu where xh=5; XH XM CS- - - 5 AAA 12-3月 -135.2 删除行:Delete 表名 where 条件;SQL Delete Stu where xh=1;1 row deleted. Truncate Table Stu;等同于Delete Stu;5.3 修改行:Update 表名Set 列名=值;Update Stu Set CS=Date2013-03-12;SQL: Update Stu Set CS=2013-03-12;ACCESS: Update Stu Set CS=#2013-03-12#;或Update Stu Set CS=2013-03-12;将出生日期改为当前时间。ORACLE:Update Stu Set CS=SysDate;SQL:Update Stu Set CS=GetDate();Access:Update Stu Set CS=Now();显示系统当前时间。ORACLE:Select SysDate From Dual;SQL:Select GetDate()-1.0*38/(24*60);Access:Select Now();Select To_Char(SysDate,yyyy-mm-dd HH24:mi:ss) as 当前时间 From Dual;当前时间-2013-03-20 14:24:54Select To_Char(SysDate,yyyy-mm-dd HH12:mi:ss) as 当前时间 From Dual;当前时间-2013-03-20 02:24:54SQL Select XH,xm,cs From stu; XH XM CS- - - 1 AAA 12-3月 -13 2 BBB 12-3月 -13 3 BBB 12-3月 -13SQL Select XH,xm,To_Char(CS,yyyy-mm-dd) as BT From stu; XH XM BT- - - 1 AAA 2013-03-12 2 BBB 2013-03-12 3 BBB 2013-03-12SQL Select XH,xm,To_Char(CS,yyyy年mm月dd日) as BT From stu; XH XM BT- - - 1 AAA 2013年03月12日 2 BBB 2013年03月12日 3 BBB 2013年03月12日SQL Select XH,XM,To_Char(CS,yyyy)|年|To_Char(CS,mm) |月|To_Char(CS,dd)|日 as BT From stu; XH XM BT- - - 1 AAA 2013年03月12日 2 BBB 2013年03月12日 3 CCC 2013年03月12日以下语法不行Select XH,XM,To_Char(CS,yyyy年mm月dd日) as BT From stu;Select XH,XM, To_Char(CS,yyyy)+年+To_Char(CS,mm)+月+To_Char(CS,dd)+日 as BT From stu;SQL Create Table TMP(区别 varchar(13),日期 Date,时间 TimeStamp);SQL Insert Into TMP Values(日期时间区别:,SysDate,SysDate);SQL Select * From TMP;区别 日期- -时间-日期时间区别: 26-3月 -1326-3月 -13 09.54.08.000000 下午SQL Select 区别,To_Char(日期, yyyy年mm月dd日 HH24:mi:ss) as 日期,To_Char(时间, yyyy年mm月dd日 HH24:mi:ss ) as 时间 From TMP;区别 日期 时间- - -日期时间区别: 2013年03月26日 21:54:08 2013年03月26日 21:54:086 行查询首先创建用户vendition,并赋于相应的权限。Create User vendition identified by vendition;Grant Create SESSION,RESOURCE,DBA TO vendition;将备份文件vendition.DMP复制E盘根目，然后启动命令提示窗口，运行如下的还原命令。Imp UserId=vendition/vendition Ignore=y FromUser=vendition ToUser=vendition File=e:vendition.DMPExp vendition/vendition File=d:vendition.DMP Owner=(vendition)Conn vendition/venditionselect Column_Name| |Data_Type| |Char_Length from all_tab_columns where table_name=供应商信息;select Column_Name,Data_Type,Char_Length from all_tab_columns where table_name=供应商信息;Alter Table vendition.商品信息 Modify 产地 varchar(8);Alter Table vendition.商品信息 Modify 库存编号 varchar(8);Alter Table vendition.商品信息 Modify 商品编号 varchar(8);Alter Table vendition.商品信息 Modify 商品名称 varchar(28);Alter Table vendition.商品信息 Modify 供应商编号 varchar(10);Alter Table vendition.供应商信息 Modify 供应商名称 varchar(14);Alter Table vendition.供应商信息 Modify 供应商编号 varchar(10);Alter Table vendition.供应商信息 Modify 供应商所在城市 varchar(14);SQL Conn vendition/vendition;SQL Select SUBSTR(OBJECT_NAME,1,20) as 对象名,OBJECT_TYPE as 类型 from user_objects 2 where OBJECT_TYPE=TABLE;对象名 类型- -DATABASE_LOG TABLELOGIN TABLETRIGGER_STATUS TABLE出库单明细信息 TABLE出库单信息 TABLE供应商信息 TABLE客户信息 TABLE库存信息 TABLE入库单明细信息 TABLE入库单信息 TABLE商品信息 TABLE对象名 类型- -收入明细信息 TABLE业务员信息 TABLE6.1 简单查询6.1.1 查询所有行所有列SQL Select * From 商品信息;商品编号 商品名称 库存编号 供应商编号 产地 单价- - - - - -1001 Intel D915GVWB主板 1008 1002 广州市 863.51002 Maxtor 40G硬盘 1006 1003 南京市 514.251003 CORSAIR VS512MB内存 1004 1009 北京市 3501004 AMD Opteron 146CPU 1002 1004 深圳市 14551005 鹏宇 GF4 MX440-8X (64M)显卡 1007 1009 深圳市 2051006 鼎华新锐移动硬盘 1001 1009 北京市 10041007 金视浩海液晶显示器 1008 1005 南京市 14601008 CREATIVE SBS 2.1 380音箱 1004 1001 福州市 1901009 HP scanjet 3770扫描仪 1010 1007 广州市 9791010 MSI 945P Platinum主板 1001 1006 深圳市 14851011 夏普AR2616复印机 1008 1009 北京市 6605.5商品编号 商品名称 库存编号 供应商编号 产地 单价- - - - - -1012 DRAGONKING 1GB内存 1005 1001 广州市 8051013 STAR NX-350针式打印机 1002 1009 深圳市 27051014 AMD Sempron 3000 CPU 1008 1008 南京市 7401015 三星 795MB CRT显示器 1006 1002 南京市 1229.81016 CISCO 1721路由器 1007 1009 北京市 49631017 Maxtor 250G硬盘 1003 1005 南京市 8351018 钛子风 GeForce4 MX4000显卡 1009 1008 广州市 2851019 佳能 FAX-L360传真机 1008 1006 南京市 33581020 DeLUX 游戏王MG430机箱 1003 1004 深圳市 5456.1.2 指定列查询语法格式:Select 计算列1,计算列n From 表名;SQL Select 商品名称,产地 From 商品信息;商品名称 产地- -Intel D915GVWB主板 广州市Maxtor 40G硬盘 南京市CORSAIR VS512MB内存 北京市AMD Opteron 146CPU 深圳市鹏宇 GF4 MX440-8X (64M)显卡 深圳市鼎华新锐移动硬盘 北京市金视浩海液晶显示器 南京市CREATIVE SBS 2.1 380音箱 福州市HP scanjet 3770扫描仪 广州市MSI 945P Platinum主板 深圳市夏普AR2616复印机 北京市商品名称 产地- - DRAGONKING 1GB内存 广州市STAR NX-350针式打印机 深圳市AMD Sempron 3000 CPU 南京市三星 795MB CRT显示器 南京市CISCO 1721路由器 北京市Maxtor 250G硬盘 南京市钛子风 GeForce4 MX4000显卡 广州市佳能 FAX-L360传真机 南京市DeLUX 游戏王MG430机箱 深圳市6.1.3 distinct查询SQL Select 产地 From 商品信息;产地-广州市南京市北京市深圳市深圳市北京市南京市福州市广州市深圳市北京市产地-广州市深圳市南京市南京市北京市南京市广州市南京市深圳市20 rows selected.SQL Select distinct 产地 as 不同产地 From 商品信息;不同产地-北京市福州市广州市南京市深圳市SQL Select distinct 供应商编号 as 不同供应商编号 From 商品信息;不同供应商编号-1001100210031004100510061007100810096.1.4 统计函数(1)Count(*|distinct 字段名)：返回记录数|不同数据个数SQL Select Count(*) as 记录数 From 商品信息; 记录数- 20SQL Select Count(distinct 产地) as 产地数 From 商品信息; 产地数- 5SQL Select distinct Count(产地) as 产地数 From 商品信息; 产地数- 20(2)Min(字段名|计算列)：返回最小值(3)Max(字段名|计算列)：返回最大值(4)Sum(字段名|计算列)：返回总和(5)Avg(字段名|计算列)：返回平均值6.1.5 常用函数1、字符串函数(1)CHR(ASCII码):返回ASCII对应的字符(2)SubStr()SQL Select SubStr(张三丰,1,1) as 姓 from Dual;姓-张(3)LTrim()、RTrim()Select A|LTrim( 张三丰 )|B from Dual;A|LTRIM(-A张三丰 BSelect A|RTrim( 张三丰 )|B from Dual;A|RTRIM-A 张三丰B(4)Mod()求余SQL Select mod(学号,100) as XH From XS;(5)Floor()地板Select (Floor(47/10)*10)|(Floor(47/10)*10+9) as 范围 from Dual;范围-4049Select (Floor(56/10)*10)|(Floor(56/10)*10+9) as 范围 from Dual;范围-5059Select distinct (Floor(CJ/10)*10)|(Floor(CJ/10)*10+9) as 范围 from XS;范围-404950596069707980899099(5)sqrt()平方根Select SQRT(36)*10 From dual;SQRT(36)*10- 60(5)SysDate(6)To_Char(Date,yyyy-mm-dd)6.1.6 SQL*Plus变量的使用(1)定义变量Define 变量名=值(2)给变量输入值Accept 变量名 Number|Char Prompt 提示字符串(3)变量的引用&变量名Create Table GZ(XH integer Primary key,QY varchar(8),GZ float,RS Integer,PJ Float);Insert Into GZ(XH,QY,GZ,RS) Values(1,三钢,20,1);Insert Into GZ(XH,QY,GZ,RS) Values(2,三钢,10,1);Insert Into GZ(XH,QY,GZ,RS) Values(3,三钢,3,20);Insert Into GZ(XH,QY,GZ,RS) Values(4,三钢,2,40);Insert Into GZ(XH,QY,GZ,RS) Values(5,三钢,1,2000);Insert Into GZ(XH,QY,GZ,RS) Values(6,三化,20,1);Insert Into GZ(XH,QY,GZ,RS) Values(7,三化,10,4);Insert Into GZ(XH,QY,GZ,RS) Values(8,三化,2,50);Insert Into GZ(XH,QY,GZ,RS) Values(9,三化,1,4000);SQL select * From GZ; XH QY GZ RS PJ- - - - - 1 三钢 20 1 2 三钢 10 1 3 三钢 3 20 4 三钢 2 40 5 三钢 1 2000 6 三化 20 1 7 三化 10 4 8 三化 2 50 9 三化 1 4000SQL define GZ=20或SQL define GZ=20SQL select * From GZ Where GZ=&GZ;原值 1: select * From GZ Where GZ=&GZ新值 1: select * From GZ Where GZ=20 XH QY GZ RS PJ- - - - - 1 三钢 20 1 1.05237633 6 三化 20 1 1.02589396SQL accept GZ Number prompt 请输入年薪工资值:请输入年薪工资值:10SQL select * From gz where GZ&GZ;原值 1: select * From gz where GZ&GZ新值 1: select * From gz where GZ 10 XH QY GZ RS PJ- - - - - 1 三钢 20 1 1.05237633 6 三化 20 1 1.02589396SQL define GZ=GZ select * From GZ Where &GZ;原值 1: select * From GZ Where &GZ新值 1: select * From GZ Where GZ define QY=三化SQL select * From gz where qy=&QY;原值 1: select * From gz where qy=&QY新值 1: select * From gz where qy=三化 XH QY GZ RS PJ- - - - - 6 三化 20 1 1.02589396 7 三化 10 4 1.02589396 8 三化 2 50 1.02589396 9 三化 1 4000 1.02589396用两个单引号表示一个单引号(同Delphi语法)。SQL define qy=三化SQL select * From gz where qy=&QY;原值 1: select * From gz where qy=&QY新值 1: select * From gz where qy=三化 XH QY GZ RS PJ- - - - - 6 三化 20 1 1.02589396 7 三化 10 4 1.02589396 8 三化 2 50 1.02589396 9 三化 1 4000 1.02589396SQL accept qy char prompt 请输入企业名称:请输入企业名称:三化SQL select * From gz where qy=&QY;原值 1: select * From gz where qy=&QY新值 1: select * From gz where qy=三化 XH QY GZ RS PJ- - - - - 6 三化 20 1 1.02589396 7 三化 10 4 1.02589396 8 三化 2 50 1.02589396 9 三化 1 4000 1.02589396SQL accept