数据库使用心得
个人使用数据库工具的心得小结，只是小结，自己的自己懂，如有不懂，可以问问
ORACLE 11G
--
1. oracle11g介绍与安装
1.1 oracle简介
1.1.1 常见的数据库
1）关系数据库
MS access、MS sql server、mysql、db2、oracle等
2）嵌入式数据库
sqlite
3）OOP的数据库
db4o
4）大型分布式数据库
如Hadoop等支持各种文件类型数据的存储以及集合数据类型的存储，一般用来海量数据非实时处理
1.1.2 Oracle数据库管理系统
Oracle（甲骨文）公司核心产品
主要版本Oracle8i/9i、Oracle10g/11g
主要基于C/S系统结构
当前最流行的数据库
1.2 oracle安装与配置
参考（安装手册）。
数据库实例（一组后台进程）
1.3 oracle关键服务
OracleService* 核心服务(必须启动)
*TNSListener 监听器服务，核心服务，在使用远程访问或PL/SQL Developer工具时必须启动
*Controleorcl 数据库控制台服务，需要使用企业管理器的时候必须启动
*RecoveryService 恢复、闪回等操作需要开启该服务
*ClrAgent：Oracle数据库.NET扩展服务的一部分。 (非必须启动)
1.4工具使用
1.4.1 sqlplus工具
直接打开程序使用
位置：(程序－>oracle-oracle11ghome1->应用程序开发－>sqlplus)
登陆方式：username/password@orcl [as sysdba]
命令行使用：
sqlplus username/password@orcl [as sysdba] --作为dba身份进入
1.4.2 sql developer工具
提供图形界面操作。
1.4.3 plsql developer工具
第三方客户端工具，提供图形界面操作。
2. 表空间
数据库由若干表空间构成，表空间由一到多个数据文件组成，每个数据文件只能属于同一表空间。
2.1分类
永久性表空间：一般保存表、视图、过程和索引等的数据
临时性表空间：只用于保存系统中短期活动的数据
撤销表空间：用来帮助回退未提交的事务数据
2.2表空间使用示例
创建表空间语法：
CREATE TABLESPACE 表空间名
DATAFILE
'数据文件路径' SIZE 大小
[AUTOEXTEND ON] [next 大小]
[maxsize 大小];
修改表空间语法：
ALTER TABLESPACE 表空间名
ADD DATAFILE
'文件路径' SIZE 大小
[AUTOEXTEND ON] [next 大小]
[maxsize 大小];
删除表空间语法：
DROP TABLESPACE 表空间名;
DROP TABLESPACE表空间名INCLUDING CONTENTS AND DATAFILES;
--创建表空间示例，E盘需要先创建oracle_data目录，以存放数据文件
CREATE TABLESPACE ts_itcast
DATAFILE
'e:\oracle_data\tp_itcast28.dbf' SIZE 30M
AUTOEXTEND ON;
--查看表空间，ts_itcast名字得大写
SELECT file_name,tablespace_name,bytes,autoextensible
FROM dba_data_files
WHERE tablespace_name='TS_ITCAST';
--调整ts_itcast表空间大小,向表空间内添加数据文件
ALTER TABLESPACE ts_itcast
ADD DATAFILE
'E:\oracle_data\tp_itcast29.DBF' SIZE 20M
AUTOEXTEND ON;
-- 删除ts_itcast表空间
DROP TABLESPACE ts_itcast;--只删除表空间
DROP TABLESPACE ts_itcast INCLUDING CONTENTS AND DATAFILES;--删除表空间及数据文件
3. 用户与权限
3.1系统用户
sys用户：超级用户，主要用来维护系统信息和管理实例，以SYSDBA或SYSOPER角色登录。
system用户: 默认的系统管理员，拥有DBA权限，通常用来管理Oracle数据库的用户、权限和存储，以Normal方式登录。
scott用户：示范用户，使用users表空间。
3.2用户与模式
模式为数据库对象（如表、索引等）的集合，oracle会为每个用户创建一个模式，和用户名一样。
3.3创建用户与权限
--创建用户语法：
CREATE USER 用户名 IDENTIFIED BY 密码
DEFAULT TABLESPACE 表空间;
--权限操作语法
grant 角色权限（角色）[,角色权限] to user_itcast;
grant 操作 on 模式.对象 to user_itcast;
revoke 角色权限（角色）[,角色权限] from user_itcast;
revoke 操作 on 模式.对象 from user_itcast;
--修改密码语法
ALTER USER 用户名 identified by 密码;
--删除用户
DROP USER 用户名 CASCADE;
--DROP TABLESPACE 用户表空间 INCLUDING CONTENTS AND DATAFILES;--删除表空间及数据文件
--创建表空间，以便新建用户时使用
CREATE TABLESPACE ts_itcast
DATAFILE
'e:\oracle_data\tp_itcast26.dbf' SIZE 30M
AUTOEXTEND ON next 20M;
/
--创建用户
CREATE USER user_itcast IDENTIFIED BY itcast
DEFAULT TABLESPACE ts_itcast
TEMPORARY TABLESPACE temp;
/
--查询user_itcast用户
SELECT *
FROM dba_users
WHERE username='USER_ITCAST';
/
-- 给户user_itcast用户授权，回收权限
GRANT connect, resource TO user_itcast; --授予CONNECT和RESOURCE两个角色（讲解权限）
GRANT SELECT ON SCOTT.emp TO user_itcast; --允许用户查看 EMP 表中的记录(讲解模式)
GRANT UPDATE ON SCOTT.emp TO user_itcast; --允许用户更新 EMP 表中的记录
REVOKE connect, resource FROM user_itcast; --撤销CONNECT和RESOURCE两个角色
/
--修改密码
ALTER USER user_itcast identified by user10;
-- 删除用户
DROP USER user_itcast CASCADE;
--DROP TABLESPACE ts_itcast INCLUDING CONTENTS AND DATAFILES;--删除表空间及数据文件
4.oracle数据类型
4.1dual表
理解为:是一张只有一行记录的表.不存主题数据，我们也称为“伪表” ，便于select特定对象.
4.2字符数据类型
CHAR：存储固定长度的字符串，单字节字符，长度为1－2000.
VARCHAR2：存储可变长度的字符串，单字节字符，长度为1－4000。
NCHAR和NVARCHAR2：存储Unicode字符集类型(双字节字符），NCHAR长度为1－1000，NVARCHAR2长度为1－2000
4.3数值数据类型
NUMBER：存储整数和浮点数，格式为NUMBER(p)或NUMBER(p, s)，P为有效数位（小数点和－号不计），长度为1－38，s为小数点右边的数字位数(则有效的整数位‘‘不为0开始算’’为p-s位)。
4.4日期时间数据类型
DATE：存储日期和时间数据 ，设置格式：环境变量添加：nls_date_format的值为YYYY-MM-DD HH24:MI:SS
插入日期示例：insert into 表名 values(列值, ……,to_date( '2014-10-10 12:12:22','YYYY-MM-DD HH24:MI:SS'));可以此基础上去掉（值和格式都要去掉）秒，分，时，日，月等，分秒去掉默认为0，时分秒去掉，就只有年月日，日去掉默认为1号，月去掉默认为系统当前月。
TIMESTAMP：秒值精确到小数点后6位，
插入数据示例：insert into tt12 values(1,0.333333333,to_timestamp('2014-10-10 12:12:25.112233','YYYY-MM-DD HH24:MI:SS.FF '));
可以此基础上去掉（值和格式都要去掉）秒或秒后的精确实值，分，时，日，月等，分秒去掉默认为0，时分秒去掉，时默认为12点（可能跟设置有关），日去掉默认为1号，月去掉默认为系统当前月。
4.5 其它数据类型
VARCHAR、 INTEGER、FLOAT、DOUBLE、lob(BLOB、CLOB、BFILE、NCLOB)
5.伪列
末存储在表中，只能查询，不能增删改。
5.1Rowid
数据库中行的地址（唯一），可快速定位行。
SELECT ROWID,字段名……
FROM 表名；
rowid 可以在查询时通过行ID去查找，尤其是部分重复数据行，用行ID查找就很方便了
删除重复行：
DELETE FROM 表 WHERE ROWID NOT IN (SELECT MIN (ROWID) FROM 表 GROUP BY 所有字段);
5.2Rownum
给查询结果返回一个数值表示次序，可以用来限制返回的条数，例如分页查询。
SELECT emp.*,rownum
FROM Scott.emp
WHERE ROWNUM<11;
以上语句返回查询结果的前10条记录。
5.2.3分页查询
注意：rownum大于１的其它整数时，条件不成立；等于2或2以上的正整数时条件不成立；
所以查询某页的记录：例如第4到7条记录，就不能写成：
select rownum rn ,t.* from tt12 t where rownum<8 and rownum>3;
可以写生如下语句：
select * from (select rownum rn ,t.* from tt12 t where rownum<8) where rn>4;
5.2.3排序
取数据的时候产生的序号，想对指定排序后的数据去指定rowmun行号时需要用子查询
例如：
--创建表
create table testtime(
ttid varchar2(2),
tttime timestamp
);
--修改时间，插入多条测试记录，
insert into testtime(ttid,tttime) values('1',to_Timestamp('2014-10-11 12:12:25.112233','YYYY-MM-DD HH24:MI:SS.FF'));
insert into testtime(ttid,tttime) values('2',to_Timestamp('2014-10-14 12:12:25.112233','YYYY-MM-DD HH24:MI:SS.FF'));
insert into testtime(ttid,tttime) values('3',to_Timestamp('2014-10-12 12:12:25.112233','YYYY-MM-DD HH24:MI:SS.FF'));
insert into testtime(ttid,tttime) values('4',to_Timestamp('2014-10-13 12:12:25.112233','YYYY-MM-DD HH24:MI:SS.FF'));
commit;
--按时间进行排序，但是rownum顺序将是乱的
select rownum,t.* from testtime t order by tttime;
--按时间进行排序，采用子查询后rownum顺序和排序一致
select rownum,tt.* from (select t.tttime from testtime t order by tttime) tt;
6. SQL语言简介
6.1数据定义语言
主要是create\alter\truncate\drop的使用
6.1.1建表示例
/*
\创建学生表
*/
CREATE TABLE student
(
stu_no CHAR(4) PRIMARY KEY NOT NULL, --学号，主键，非空
stu_name VARCHAR2(30) NOT NULL,--姓名,非空
stu_id VARCHAR2(18), --身份证号，代表18位整数
stu_age NUMBER(3,0) --年龄
);
--利用现有表创建新表，select 后边有多少字段，新表将有多少个字段,主键不会被创建
CREATE TABLE stu_info AS SELECT stu_no,stu_age FROM student;
6.1.2改表示例
/*
\修改student表，添加stu_seat(座号)和stu_Address(住址） 两个列
*/
ALTER TABLE student
ADD (stu_seat NUMERIC(2,0) ,
stu_address VARCHAR2(20));
/*
\修改student表的列的定义
*/
ALTER TABLE student
MODIFY (stu_name VARCHAR2 (50),
stu_address VARCHAR2(100）
);
/*
\删除student表中stu_ address和stu_seat列
*/
ALTER TABLE student
DROP (stu_address,
stu_seat);
/*
\修改student表列名
*/
ALTER TABLE student RENAME COLUMN stu_no TO rempno;
6.1.3删表示例
/*
\删除student表结构及数据
*/
DROP TABLE student;
--只删除表数据，每条删除的记录不写日志，省资源，效律比delete高
truncate table student;
6.2数据操纵语言
用于增删改查数据，主要是insert/update/select/delete的使用。
6.2.1准备工作
创建学生表和添加测试数据：
/*
\创建学生表
*/
CREATE TABLE student
(
stu_no CHAR(4) PRIMARY KEY NOT NULL, --学号，主键，非空
stu_name VARCHAR2(30) NOT NULL,--姓名,非空
stu_id VARCHAR2(18), --身份证号，代表18位整数
stu_age NUMBER(3,0) --年龄
);
6.2.2新增数据
往student表插入数据
insert into student(stu_no,stu_name,stu_id,stu_age) values('a001','张大','441521199909092111',19);
insert into student(stu_no,stu_name,stu_id,stu_age) values('a002','张二','441521199909092112',19);
insert into student(stu_no,stu_name,stu_id,stu_age) values('a003','张三','441521199909092113',18);
insert into student(stu_no,stu_name,stu_id,stu_age) values('a004','张四','441521199909092114',18);
commit;
6.2.3修改数据
--修改学号为‘a002’学员的名字为‘李二’
update student set stu_name='李二' where stu_no='a002';
--所有学员的年龄都加1
update student set stu_age=stu_age+1 ;
6.2.3查询数据
查询（结果）无重复数据行，distinct关键字使用：
--查询结果包括学员名字和年龄，如果存在学员名字和年龄都一样的多条记录将只返回一条
select distinct stu_name,stu_age from student;
别名、order by\group by\where\having\like\in\子查询等使用略。
6.2.4删除数据
--根据条件删除表数据
delete from student where stu_no='a004'
--请空表，不写日志，省资源，效律高，属于数据定义语言
truncate table student;
6.3事务控制语言
事务控制语句，主要由commit\rollback\savepoint savepoint_name\rollback to savepoint_name组成。以下为使用示例：
/*
\\事务控制语句应用举例
*/
--DROP TABLE student;
--执行步骤一：创建student表
CREATE TABLE student
(
stu_no CHAR(4) PRIMARY KEY NOT NULL, --学号，主键，非空
stu_name VARCHAR2(30) NOT NULL,--姓名,非空
stu_id VARCHAR2(18), --身份证号，代表18位整数
stu_age NUMBER(3,0) --年龄
);
--执行步骤二：插入数据
insert into student(stu_no,stu_name,stu_id,stu_age) values('a001','张大','441521199909092111',19);
insert into student(stu_no,stu_name,stu_id,stu_age) values('a002','张二','441521199909092112',19);
insert into student(stu_no,stu_name,stu_id,stu_age) values('a003','张三','441521199909092113',18);
insert into student(stu_no,stu_name,stu_id,stu_age) values('a004','张四','441521199909092114',18);
--执行步骤三：操作student表
SAVEPOINT a;
insert into student(stu_no,stu_name,stu_id,stu_age) values('a005','张五','441521199909092115',18);
SAVEPOINT b;
insert into student(stu_no,stu_name,stu_id,stu_age) values('a006','张6','441521199909092116',18);
--执行步骤四：查看student表,
SELECT * FROM student;
ROLLBACK TO SAVEPOINT b;
SELECT * FROM student;
insert into student(stu_no,stu_name,stu_id,stu_age) values('a007','张7','441521199909092117',18);
--执行步骤五：查看student表,
SELECT * FROM student;
ROLLBACK TO SAVEPOINT a;
SELECT * FROM student;
--执行步骤六：回滚
ROLLBACK;
--执行步骤七：查看student表
SELECT * FROM student;
6.4数据控制语言
-- 给户user_itcast用户授权，回收权限
GRANT connect, resource TO user_itcast; --授予CONNECT和RESOURCE两个角色（讲解权限）
REVOKE connect, resource FROM user_itcast; --撤销CONNECT和RESOURCE两个角色
GRANT SELECT ON SCOTT.emp TO user_itcast; --授予用户user_itcast查看 用户SCOTT 模式下的EMP 表中的记录权限
REVOKE SELECT ON SCOTT.emp from user_itcast; --取消用户user_itcast查看 用户SCOTT 模式下的EMP 表中的记录权限
GRANT UPDATE ON SCOTT.emp TO user_itcast; --授予用户user_itcast更新 EMP 表中的记录权限
7.运算符
7.1算术运算符
+、-、*、/等略。
7.2比较（关系）运算符
＝、！＝、< 、 > 、 <= 、 >= 、 between...and... 、 in 、 like 、 is null。
is null使用注意：
insert into student(stu_no,stu_name,stu_id,stu_age) values('a008','张8',null,18);--stu_id值为null
insert into student(stu_no,stu_name,stu_id,stu_age) values('a009','张9','',18);--stu_id值为null
insert into student(stu_no,stu_name,stu_id,stu_age) values('a008','张8','null',18);--stu_id值不为null
insert into student(stu_no,stu_name,stu_id,stu_age) values('a009','张9',' ',18);--stu_id值不为null
7.3逻辑运算符
and 、 or 、 not。
7.4连接运算符
｜｜
示例：SELECT stu_id||stu_name FROM student;
7.5集合运算符
union（并集无重复）
union all（并集有重复）
intersect（交集，共有部分）
minus（减集，第一个查询具有，第二个查询不具有的数据）
使用注意：
列数相关，对应列的数据类型兼容，不能含有Long类型的列，第一个select语句的列或别名作为结果标题