一、Oracle数据库操作
1、创建数据库
create database databasename
2、删除数据库
drop database dbname
3、备份数据库
完全备份
exp demo/demo@orcl buffer=1024 file=d：\back.dmp full=y
demo：用户名、密码
buffer: 缓存大小
file: 具体的备份文件地址
full: 是否导出全部文件
ignore: 忽略错误，如果表已经存在，则也是覆盖
将数据库中system用户与sys用户的表导出
exp demo/demo@orcl file=d:\backup\1.dmp owner=(system,sys)
导出指定的表
exp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)
按过滤条件，导出
exp demo/demo@orcl file=d：\back.dmp tables=（table1） query=\" where filed1 like 'fg%'\"
导出时可以进行压缩；命令后面 加上 compress=y ；如果需要日志，后面： log=d:\log.txt
备份远程服务器的数据库
exp 用户名/密码@远程的IP:端口/实例 file=存放的位置:\文件名称.dmp full=y
4、数据库还原
打开cmd直接执行如下命令，不用再登陆sqlplus。
完整还原
imp demo/demo@orcl file=d:\back.dmp full=y ignore=y log=D:\implog.txt
指定log很重要，便于分析错误进行补救。
导入指定表
imp demo/demo@orcl file=d:\backup2.dmp tables=(teachers,students)
还原到远程服务器
imp 用户名/密码@远程的IP:端口/实例 file=存放的位置:\文件名称.dmp full=y
二、Oracle表操作
1、创建表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：select * into table_new from table_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only<仅适用于Oracle>
2、删除表
drop table tabname
3、重命名表
说明：alter table 表名 rename to 新表名
eg：alter table tablename rename to newtablename
4、增加字段
说明：alter table 表名 add (字段名 字段类型 默认值 是否为空);
例：alter table tablename add (ID int);
eg：alter table tablename add (ID varchar2(30) default '空' not null);
5、修改字段
说明：alter table 表名 modify (字段名 字段类型 默认值 是否为空);
eg：alter table tablename modify (ID number(4));
6、重名字段
说明：alter table 表名 rename column 列名 to 新列名 （其中：column是关键字）
eg：alter table tablename rename column ID to newID;
7、删除字段
说明：alter table 表名 drop column 字段名;
eg：alter table tablename drop column ID;
8、添加主键
alter table tabname add primary key(col)
9、删除主键
alter table tabname drop primary key(col)
10、创建索引
create [unique] index idxname on tabname(col….)
11、删除索引
drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
12、创建视图
create view viewname as select statement
13、删除视图
drop view viewname
三、Oracle操作数据
1、数据查询
select <列名> from <表名> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]];
select * from table1 where field1 like ’%value1%’;
2、插入数据
insert into 表名 values(所有列的值);
insert into test values(1,'zhangsan',20);
insert into 表名(列) values(对应的值);
insert into test(id,name) values(2,'lisi');
3、更新数据
update 表 set 列=新的值 [where 条件] -->更新满足条件的记录
update test set name='zhangsan2' where name='zhangsan'
update 表 set 列=新的值 -->更新所有的数据
update test set age =20;
4、删除数据
delete from 表名 where 条件 -->删除满足条件的记录
delete from test where id = 1;
delete from test -->删除所有
commit; -->提交数据
rollback; -->回滚数据
delete方式可以恢复删除的数据，但是提交了，就没办法了 delete删除的时候，会记录日志 -->删除会很慢很慢
truncate table 表名
删除所有数据，不会影响表结构，不会记录日志，数据不能恢复 -->删除很快
drop table 表名
删除所有数据，包括表结构一并删除，不会记录日志，数据不能恢复-->删除很快
5、数据复制
表数据复制
insert into table1 (select * from table2);
复制表结构
create table table1 select * from table2 where 1>1;
复制表结构和数据
create table table1 select * from table2;
复制指定字段
create table table1 as select id, name from table2 where 1>1;
6、查询后处理
最基本的查询处理语句：
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
7、高级查询运算词sql语句示例
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
8、外连接sql语句示例
A、left （outer） join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right （outer） join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full/cross （outer） join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
7、分组:Group by sql语句示例
一张表，一旦分组完成后，查询后只能得到组相关的信息。
组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准)
在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据
在selecte统计函数中的字段，不能和普通的字段放在一起；
9、提升
看到这的你,才开始了你的数据库之旅
9.1 复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select into b from a where 1<>1（仅用于SQlServer）
法二：select top 0 into b from a
9.2 拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
9.3 跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in ‘”&Server.MapPath(“.”)&”/data.mdb” &”’ where..
9.4 子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
9.5 显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
9.6 外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
9.7 在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
9.8 between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9.9 in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
8.10 两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
9.11 四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where …..
9.12 日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff(‘minute’,f开始时间,getdate())>5
9.13 一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
具体实现：
关于数据库分页：
declare @start int,@end int
@sql nvarchar(600)
set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’
exec sp_executesql @sql
注意：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如果top后还有具体的字段，这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引）
9.14 前10条记录
select top 10 * from table1 where 范围
9.15 选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
9.16 包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
9.17 随机取出10条数据
select top 10 * from tablename order by newid()
9.18 随机选择记录
select newid()
9.19 删除重复记录
1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,…)
2),select distinct into temp from tablename
delete from tablename
insert into tablename select from temp
评价： 这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作
3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段
alter table tablename
–添加一个自增列
add column_b int identity(1,1)
delete from tablename where column_b not in(
select max(column_b) from tablename group by column1,column2,…)
alter table tablename drop column column_b
9.20 列出数据库里所有的表名
select name from sysobjects where type=’U’ // U代表用户
9.21 列出表里的所有的列名
select name from syscolumns where id=object_id(‘TableName’)
9.22 列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when ‘A’ then pcs else 0 end),sum(case vender when ‘C’ then pcs else 0 end),sum(case vender when ‘B’ then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
9.23 初始化表table1
TRUNCATE TABLE table1
9.24 选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
9.25 技巧
1、1=1，1=2的使用，在SQL语句组合时用的较多
“where 1=1” 是表示选择全部 “where 1=2”全部不选，
如：
if @strWhere !=”
begin
set @strSQL = ‘select count() as Total from [’ + @tblName + ‘] where ’ + @strWhere
end
else
begin
set @strSQL = ‘select count() as Total from [’ + @tblName + ‘]’
end
我们可以直接写成
set @strSQL = ‘select count(*) as Total from [’ + @tblName + ‘] where 1=1 安定 ‘+ @strWhere
————————————————
四、ORACLE常用的系统函数
1、数值函数
函数 返回值 样例 显示
CEIL(n) 大于或等于数值n的最小整数 SELECT CEIL(10.6) FROM TABLE_NAME; 11
FLOOR(n) 小于等于数值n的最大整数 SELECT FLOOR (10.6) FROM TABLE_NAME; 10
MOD(m,n) m除以n的余数,若n=0,则返回m SELECT MOD (7,5) FROM TABLE_NAME l; 2
POWER(m,n) m的n次方 SELECT POWER (3,2) FROM TABLE_NAME; 9
ROUND(n,m) 将n四舍五入,保留小数点后m位 SELECT ROUND (1234.5678,2) FROM TABLE_NAME; 1234.57
SIGN(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 SELECT SIGN (12) FROM TABLE_NAME; 1
SQRT(n) n的平方根 SELECT SQRT (25) FROM TABLE_NAME; 5
## 2、常用字符函数
INITICAP(char) 把每个字符串的第一个字符换成大写 SELECT INITICAP ('mr.ecop') FROM TABLE_NAME; Mr.Ecop
LOWER(char) 整个字符串换成小写 SELECT LOWER ('MR.ecop') FROM TABLE_NAME; mr.ecop
REPLACE(char,str1,str2) 字符串中所有str1换成str2 SELECT REPLACE ('Scott','s','Boy') FROM TABLE_NAME; Boycott
SUBSTR(char,m,n) 取出从m字符开始的n个字符的子串 SELECT SUBSTR ('ABCDEF',2,2) FROM TABLE_NAME; CD
LENGTH(char) 求字符串的长度 SELECT LENGTH ('ACD') FROM TABLE_NAME; 3
|| 并置运算符 SELECT 'ABCD'||'EFGH' FROM TABLE_NAME; ABCDEFGH
## 3、日期型函数
SYSDATE 当前日期和时间 SELECT SYSDATE FROM TABLE_NAME;
LAST_DAY 本月最后一天 SELECT LAST_DAY(SYSDATE) FROM TABLE_NAME;
ADD_MONTHS(d,n) 当前日期d后推n个月 SELECT ADD_MONTHS (SYSDATE,2) f FROM TABLE_NAME;
MONTHS_BETWEEN(d,n) 日期d和n相差月数
SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('20020812','YYYYMMDD')) FROMTABLE_NAME;
NEXT_DAY(d,day) d后第一周指定day的日期
SELECT NEXT_DAY (SYSDATE,'Monday') FROM TABLE_NAME;
day 格式 有 'Monday' 星期一 'Tuesday' 星期二
'wednesday' 星期三 'Thursday' 星期四 'Friday' 星期五
'Saturday' 星期六 'Sunday' 星期日
## 4、特殊格式的日期型函数
Y或YY或YYY 年的最后一位，两位，三位 SELECTTO_CHAR(SYSDATE,'YYY') FROM TABLE_NAME;
Q 季度,1-3月为第一季度 SELECT TO_CHAR(SYSDATE,'Q') FROM TABLE_NAME;
MM 月份数 SELECT TO_CHAR(SYSDATE,'MM') FROM TABLE_NAME;
RM 月份的罗马表示 SELECT TO_CHAR(SYSDATE,'RM') FROM TABLE_NAME; IV
month 用9个字符表示的月份名 SELECT TO_CHAR(SYSDATE,'month') FROM TABLE_NAME;
ww 当年第几周 SELECT TO_CHAR(SYSDATE,'ww') FROM TABLE_NAME l;
w 本月第几周 SELECT TO_CHAR(SYSDATE,'w') FROM TABLE_NAME;
DDD 当年第几天,一月一日为001 ,二月一日032 SELECT TO_CHAR(SYSDATE,'DDD') FROM TABLE_NAME;
DD 当月第几天 SELECT TO_CHAR(SYSDATE,'DD') FROM TABLE_NAME;
D 周内第几天 SELECT TO_CHAR(SYSDATE,'D') FROM TABLE_NAME; 如 sunday
DY 周内第几天缩写 SELECT TO_CHAR(SYSDATE,'DY') FROM TABLE_NAME; 如 sun
hh12 12小时制小时数 SELECT TO_CHAR(SYSDATE,'hh12') FROM TABLE_NAME;
hh24 24小时制小时数 SELECT TO_CHAR(SYSDATE,'hh24') FROM TABLE_NAME;
Mi 分钟数 SELECT TO_CHAR(SYSDATE,'Mi') FROM TABLE_NAME;
ss 秒数 SELECT TO_CHAR(SYSDATE,'ss') FROM TABLE_NAME;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:24:mi:ss') FROM TABLE_NAME;
TO_NUMBER() 将合法的字符串转换为数字 SELECT TO_NUMBER ('88877') FROM TABLE_NAME; 88877
TO_CHAR() 将数字转换为字符串 SELECT TO_CHAR(88877) FROM TABLE_NAME; '88877'
## 5、字符函数
字符函数主要用于修改字符列。这些函数接受字符输入，返回字符或数字值。Oracle 提供的一些字符函数如下。
1. CONCAT (char1, char2)
返回连接“char2”的“char1”。
示例 SELECT CONCAT( CONCAT( COLUMN_NAME, ' is a '), job) FROM TABLE_NAME;
2. INITCAP(string)
将“string”的字符转成大写。
SELECT INITCAP( COLUMN_NAME) from TABLE_NAME;
3. LOWER (string)
将“string”转成小写。
SELECT LOWER( COLUMN_NAME) from TABLE_NAME;
4. LPAD(char1,n [,char2])
返回“char1”，左起由“char2”中的字符补充到“n”个字符长。如果“char1”比“n”长，则函数返回“char1”的前“n”个字符。
SELECT LPAD( COLUMN_NAME,15,'*') FROM TABLE_NAME;
5. LTRIM(string,trim_set)
从左边删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim_set” 是我们要去掉的字符的集合。
SELECT LTRIM('abcdab','a') FROM TABLE_NAME;
6. REPLACE(string, if, then)
用 0 或其他字符代替字符串中的字符。“if”是字符或字符串，对于每个出现在“string”中的“if”，都用“then”的内容代替。
SELECT REPLACE('JACK and JUE','J','BL') FROM TABLE_NAME;
7. RPAD(char1, n [,char2])
返回“char1”，右侧用“char2”中的字符补充到“n”个字符长。如果 “char1”比“n” 长，则函数返回“char1”的前“n”个字符。
SELECT RPAD( COLUMN_NAME,15,'*') FROM TABLE_NAME;
8. RTRIM(string,trim_set)
从右侧删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim_set” 是我们要去掉的字符的集合。
SELECT RTRIM('abcdef', 'f') FROM TABLE_NAME;
9. SOUNDEX(char)
返回包含“char”的表意字符的字符串。它允许比较英语中拼写不同而发音类似的字。
SELECT COLUMN_NAME FROM TABLE_NAME WHERE SOUNDEX( COLUMN_NAME) = SoUNDEX('SMYTHE');
10. SUBSTR(string, start [,count])
返回“string”中截取的一部分。该命令截取“string”的一个子集，从“start”位置开始，持续“count”个字符。如果我们不指定“count”，
则从“start”开始截取到“string”的尾部。
SELECT SUBSTR('ABCDEFGIJKLM',3,4) FROM TABLE_NAME;
11. TRANSLATE(string, if, then)
“if”中字符的位置，并检查“then”的相同位置，然后用该位置的字符替换 “string”中的字符。
SELECT TRANSLATE( COLUMN_NAME,'AEIOU', 'XXXXX') FROM TABLE_NAME;
12. UPPER(string)
返回大写的“string”。
SELECT UPPER('aptech computer education') FROM TABLE_NAME;
13. ASCII(string)
该命令是“American Standard Code for Information Interchange”的缩写。它是使用数字表示可打印字符的基本规则。
该函数返回 “string”中第一个（最左边）字符的 ASCII 值。
SELECT ASCII('APTECH') FROM TABLE_NAME;
14. INSTR (string, set[, start[, occurrence] ] )
该命令“string”中从“start”位置开始查找字符集合的位置，再查找“set”出现的第一次、第二次等等的“occurrence”（次数）。
“start”的值也可以是负数，代表从字符串结尾开始向反方向搜索。该函数也用于数字和日期数据类型。
SELECT INSTR('aptech is aptech','ap',1,2) FROM TABLE_NAME;
15. LENGTH(string)
返回“string”的长度值。
SELECT COLUMN_NAME, LENGTH( COLUMN_NAME) FROM TABLE_NAME;
## 6、NULL函数
1.COALESCE(expr)
返回表达式列表中第一个不为NULL的函数，如果都为NULL，则返回一个null
SELECT COALESCE(NULL,9-9,NULL) FROM TABLE_NAME;
2.LNNVL(condition)
返回除了condition要求的条件之外的数据，包括NULL的条件，通常用于WHERE条件中
SELECT COLUMN_NAME1,...COLUMN_NAME2 FROM TABLE_NAME WHERE LNNVL(condition);
3.NVL(expr1,expr2[,expr3])
NVL(expr1,expr2)，替换NULL值，如果expr1为NULL值，则返回expr2的值，否则返回expr1的值。该函数要求两个参数类型一致至少相互
之间可以进行隐式的转换
SELECT NVL(COLUMN_NAME1,COLUMN_NAME2) FROM TABLE_NAME;
NVL(expr1,expr2,expr3),，替换NULL值，如果expr1为NULL值，则返回expr2的值，否则返回expr3的值。该函数要求expr2、expr3参数
类型一致至少相互之间可以进行隐式的转换
SELECT NVL(COLUMN_NAME1,COLUMN_NAME2,COLUMN_NAME3) FROM TABLE_NAME;
## 7、集合函数
1.AVG([distinct l all] expr)
求取列的平均值，表示某组的平均值，返回数值类型
distinctlall：去除重复的值
all：表示所有的值，包括重复的，也是默认值
expr：表达式，只能是数值类型
SELECT AVG(ALL COLUMN_NAME) FROM TABLE_NAME [WHERE CONDITION];
2.MAX([distinct l all] expr) MIN([distinct l all] expr)
指定列的最大值和最小值
SELECT * FROM TABLE_NAME WHERE COLUMN_NAME = (SELECT MAX(COLUMN_NAME) FROM TABLE_NAME);
SELECT * FROM TABLE_NAME WHERE COLUMN_NAME = (SELECT MIN(COLUMN_NAME) FROM TABLE_NAME);
3.SUM([distinct l all] expr) MIN([distinct l all] expr)
求和函数，返回制定列的数值和
SELECT SUM(ALL COLUMN_NAME) FROM TABLE_NAME [WHERE CONDITION];
4.COUNT([distinct l all] expr) MIN([distinct l all] expr)
返回查询的记录数
SELECT COUNT(ALL COLUMN_NAME) FROM TABLE_NAME [WHERE CONDITION];
## 8、其他函数
1.USER
返回登录名函数
SELECT USER FROM DUAL;
2.USERENV(parameter)
返回当前会话信息
parameter:Language 返回当前会话对应语言和字符集
parameter:SESSIONID 返回当前会话ID
parameter:ISDBA 返回当前用户是否DBA
SELECT USERENV('ISDBA') FROM TABLE_NAME [WHERE CONDITION];
3.SYS_CONTEXT(namespace,parameter)
返回oracle已经创建的context，名为USERENV的属性对应值
4.DECODE(expr,search,result[,search1,result1][,default])
表达式匹配函数，当expr符合条件search时返回result的值，该过程可以重复多个，如果没有匹配结果放回默认值default，这是一对一匹配过程，
类似switch
