一.数据库对象及其操作
1. 表
1.创建表
create table table_name (
user_id number ,
user_name varchar2(20),
birthday date,
telpnone number(11)
);
第二种：从其他表中创建表
create table 表名 as select语句.但是这个select语句如果涉及到long数据类型，就不行了。
创建表时，把较小的不为空的字段放在前面。可以给字段加上约束条件。
2. 删除
drop table table_name;
3.删除表的数据
truncate table table_name;
4. 表的重命名 ，删除增加一列
添加列 alter table 表名 add 列定义
更改列 alter table 表名 modify （列名 新属性, ......）;
删除列 alter table 表名 drop column 列名s [cascade constraint]
alter table 表名 drop unused colunm
未用列 alter table 表名 set unused column 列名 [cascade constraint]
更改表名 alter table table_name rename to new_name;
视图
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY]
OR REPLACE ：若所创建的试图已经存在，ORACLE自动重建该视图； FORCE ：不管基表是否存在ORACLE都会自动创建该视图； NOFORCE ：只有基表都存在ORACLE才会创建该视图： alias ：为视图产生的列定义的别名； subquery ：一条完整的SELECT语句，可以在该语句中定义别名；
CREATE OR REPLACE VIEW dept_sum_vw (name,minsal,maxsal,avgsal) AS SELECT d.dname,min(e.sal),max(e.sal),avg(e.sal) FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.dname;
--给用户授权创建视图 -- grant create all view to zdgshr; 所有数据库都能创建视图的用户 grant create view to zdgshr; --创建简单视图 create view temp as select * from zd_member_basic_info; --测试 select * from temp where rownum=1; --创建简单视图：只读 create or replace view temp1 as select id,job_number,name,dept_id from zd_member_basic_info with read only; --测试 insert into temp1(id,job_number,name,dept_id) values(1,0,'张三',1300);
视图的删除：
DROP VIEW VIEW_NAME语句删除视图。
删除视图的定义不影响基表中的数据。
只有视图所有者和具备DROP VIEW权限的用户可以删除视图。
视图被删除后，基于被删除视图的其他视图或应用将无效。
3、索引
普通索引：create index idx_dpt_dname on dept(dname);
联合索引：create index idx_dept_dname_deptno on dept(dname, deptno);
--唯一索引
create unique index idx_emp_ename on scott.emp(ename);
--反向键索引
create index idx_emp_rev_no on scott.emp(empno) reverse;
--位图索引
create bitmap index idx_emp_name on scott.emp(dname);
--索引组织表,一定要有主键
create table tab (
id int primary key,
name varchar2(20)
) organization index;
--索引组织表的insert效率非常低
--分区表索引
create index idx_name on table(col) local/global;
--索引分区
提示：当给表创建主键或唯一键约束时，系统也会创建一个约束给该字段；同样创建索引也会占用数据库空间；索引在访问、查询的时候效率有提高，但是在修改表的时候效率就会降低；
索引特点：
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
索引不足：
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
1.4 应该建索引列的特点：
1）在经常需要搜索的列上，可以加快搜索的速度；
2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
1.5 不应该建索引列的特点：
第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引
4、序列
序列的概念：
当我们向表里插入数据时，表的主键对应的值是唯一的，我们希望在每次插入数据之后主键可以保持自动增长；
创建序列：
create sequence sequence_name
increment by n
start with n
maxvalus n | nomaxcvalue
minvalus n | nominvalus
cycle | nocycle
cache nocache
从序列取值：CURRVAL 取当前值，
NEXTVAL 取下一个值
例如：
create sequence seq;
select seq.nextval from dual;
insert into tab values(sql.nextval, ‘music’);
create sequence seqtab
start with 2 –从2开始
increment by 3—每次加3
nomaxvalue—没有最大值
minvalue 1—最小值1
nocycle—不循环
nocache;--不缓存
--修改序列 ，不能修改起始值
alter sequence seqtab
maxvalue 1000;
6、创建同义词
当数据库用户A要访问数据库用户B中的一张表 Table1的时候，需要加前缀
Select * from B.table1
但我们要通过DB-LINK访问另一个数据库中的某张表的时候我们需要加@后缀
Select * from table1@db-link-name
同义词，顾名思义就是说别名、或是另一个名字。
create synonym scott_emp for scott.emp;
create public synonym scott_dept for scott.dept;
select * from scott_emp;
select * from scott_dept;
7、创建表空间
create tablespace HooMS
datafile 'E:HooMS.dbf'
size 5M
autoextend on next 2M maxsize 10M;
--创建用户、分配可以操作表空间
create user hoo
identified by hoo
default tablespace HooMS
temporary tablespace temp;
--创建表空间
create tablespace myMS
datafile 'c:myMS.dbf'
size 1M
autoextend on;
--扩展表空间--修改表空间大小
alter database
datafile 'c:myMS.dbf'
resize 2M;
--扩展表空间--添加数据文件
alter tablespace myMS
add datafile 'c:myMS_2.dbf'
size 1M;
--设置dbf文件自动增长
alter database
datafile 'c:myMS_2.dbf'
autoextend on next 2M maxsize 4M;
--表空间重命名
alter tablespace myMS
rename to hooMS;
--分离表空间（脱机）
alter tablespace hooMS
offline temporary;
--归档模式下脱机
alter tablespace hooMS
offline immediate;
--使表空间联机
alter tablespace hooMS online;
--删除无数据的表空间
drop tablespace hooMS;
--删除带数据的表空间
drop tablespace hooMS
including contents;
约束
--主键约束(Primary Key constraint):要求主键列的数据唯一，并且不允许为空。
--唯一约束(Unique Constraint)：要求该列唯一，允许为空，但只能出现一个空值。
--检查约束(Check Constraint):某列取值范围限制、格式限制等，如有关年龄的约束。
--默认约束(Default Constraint):某列的默认值，如我们的男性同学较多，性别默认为男。
--外键约束(Foreign Key):用于在两表之间建立关系需要制定引用主表的哪一列。
语法如下
alter table 表名
add constraint 约束名 约束类型具体的约束说明
alter table table_name add constraint constraint_name constraint_type (column..)
示例：
--添加主键约束
alter table stuInfo
add constraint PK_stuNo primary key(stuNo)
--添加唯一键约束
alter table stuInfo
add constraint UQ_stuID unique(stuID)
--添加默认约束
alter table stuInfo
add constraint DF_stuAddress default('地址不详') for stuAddress
--添加检查约束
alter table stuInfo
add constraint CK_stuAge check(stuAge between 15 and 40)
alter table RJ_TRAINING_CLASSMATE
add constraint CK_Age check(Age between 18 and 30);
ALTER TABLE RJ_TRAINING_CLASSMATE
ADD constraint CK_SEX check (SEX in ('男','女'));
查询约束
select * from user_constaints
where table_name=表名；表名大写
SELECT * FROM USER_CONSTRAINTS
WHERE TABLE_NAME='RJ_TRAINING_CLASSMATE';
删除约束
alter table 表名 drop constraint 约束名
约束失效和生效
失效
ALTER TABLE employees DISABLE CONSTRAINT emp_emp_id_pk CASCADE;
生效：
alter table employees enable constraint emp_emp_id-pk ;
数据库常用的函数
1.数据库常用的函数主要分为单行函数和多行函数；
单行函数： 输入一个返回一个；例如字符型，日期，数字，转换，通用函数
多行函数：输入多个返回一个；例如求和函数，最大值等
1.字符型函数
1.1大小写转换函数（Oracle数据库大小写是敏感的）
函数 结果
LOWER('SQL Course') sql course
UPPER('SQL Course') SQL COURSE
INITCAP('SQL course') Sql Course
1.2字符操作函数
substr('helloworld',a,b) 参数A表示截取的起始点，参数b表示截取的长度，若b省略则默认截取到字符串的末尾；
instr(string1,string2[,start_position[,nth_appearence]])
string1：要在此字符串中查找。
string2：要在string1中查找的字符串。
start_position：从string1开始查找的位置。可选，默认为1，正数时，从左到右检索，负数时，从右到左检索。
nth_appearence：查找第几次出现string2。可选，默认为1，不能为负。
注：如果没有查找到，返回0。
例如：
select instr('helloworld','l','1',1) from dual;
select instr('helloworld','l','1',3) from dual;
select instr('helloworld','low','1',1) from dual;
该函数可以用于模糊查询以及判断包含关系：
eg: select code, name, dept, occupation from staff where instr(code, '001') > 0;
等同于
select code, name, dept, occupation from staff where code like '%001%' ;
2.数字操作函数
round表示四舍五入，包括两个参数，一个是要操作的数字，另一个是需要保留的位数；
trunc舍弃超出的位数
3.日期操作函数
months_between 主要用来计算两个日期之间相差的月份数；主要是第一个日期减去第二个日期；
select months_between(to_date('20080228', 'yyyymmdd'),
to_date('20090228', 'yyyymmdd')) as months
from dual; -12
add_months(time,months)函数可以得到某一时间之前或之后n个月的时间
- 如 select add_months(sysdate,-6) from dual;
该查询的结果是当前时间半年前的时间
2.select add_months(sysdate,6) from dual;
该查询的结果是当前时间半年后的时间
3next_day:第一个参数是对应的时间，第二个主要是星期或者数字1-7，其中1代表周一，按照日历排序；
SELECT NEXT_DAY(SYSDATE, 'Sunday') FROM DUAL;
SELECT NEXT_DAY(SYSDATE, 1) FROM DUAL;
4.--last_day: 某个日期当月的最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL;
5.--对日期进行四舍五入
SELECT ROUND(SYSDATE, 'MONTH') 月, ROUND(SYSDATE, 'YEAR') FROM DUAL;
6.--对日期进行截断
SELECT TRUNC(SYSDATE, 'MONTH') 月, TRUNC(SYSDATE, 'YEAR') FROM DUAL;
转换函数
1 .TO_CHAR() 函数：日期到字符串的转换
select to_char(sysdate,'yyyy-mm-dd-dy') from dual;
select to_char(sysdate,'yyyy-mm-dd-dy') from dual;
select to_char(888989,'l9,999,999') from dual;
4.其他单行函数
5.Decode
DECODE(value,if1,then1,if2,then2,if3,then3,...,else)
表示如果value 等于if1时，DECODE函数的结果返回 then1,如果不等于任何一个if值，则返回else；
6.case when
CASE
WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
区别：
DECODE 只能用做相等判断,但是可以配合sign函数进行大于，小于，等于的判断
CASE 可用于=,>=,<,<=,<>,is null,is not null 等的判断;
单行函数补充
1.replace
REPLACE('string','s1','s2')
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SQL> select replace('he love you','he','i') from dual;
http://www.3lian.com/edu/2013/08-02/86216.html
分析函数总结
多行函数
分组计算函数：相对于单行函数，也可称之为多行函数，它的输入是多个行构成得一个行集（这个行集可以是 一张表的所有行，也可以是按照某个维度进行分组后的某一组行），而输出都是一个值；
备注：MIN, MAX 可用于任何数据类型，但AVG ， SUM，STDDEV, VARIANCE仅适用于数值型字段。
1count函数
count详解：
count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）.
distinct 列名,得到的结果将是除去值为null和重复数据后的结果
如果你的数据表没有主键，那么count(1)比count(*)快
如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快
如果你的表只有一个字段的话那count(*)就是最快的啦
count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(*),自动会优化指定到那一个字段。所以没必要去count(?)，用count(*),sql会帮你完成优化的
2 使用GROUP BY 子句进行分组: 不能在Where 条件中使用分组计算函数表达式，当出现这样的需求的时候，使用Having 子句。
SELECT department_id, AVG(salary) FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000
order by department_id ;
据库的操作
1.select
1.select * from employees;
多表操作时
select table1.col1,table1.col2,table2.col1 from table1,table2;
2.select 语句操作时可以进行四则运算，例如select col1,col2*2,col3+400 from table1;
3.select 语句操作时可以对查询的列进行重新命名，关键字为AS;
eg: select col1as name,col no salary*3 avg from table1;
3.字符串连接符||
可以使用||将表中的多个列进行合并或者可以添加自己想要添加的字符
eg:
select last_name || ' _' || email from employees;
可以将下划线添加到对应的地方 ，只是需要使用单引号，同时需要多使用一个连接符；
4.distinct
使用distinct消除重复的行；
select department_id from employees;
条件限制和排序
1.使用where关键字进行条件查询，
eg:
select last_name ,department_id from employees
where department_id=90;
使用where可以在查询的结果中在进行一次过滤或者加上where的条件之后在进行查询；
2.比较操作符
比较操作符主要涉及>,<=,>=,<,<>(不等于)以及between。。and ,in(set),like 以及is null
其中between and
select last_name,salary from employees
where salary between 5000 and 8000;
in主要用于查询结果是否在对应的集合中；
select last_name,department_id from employees
where department_id in (101,90);
like 主要用于通配符的匹配，例如使用‘—’表示匹配一个字符，使用‘%’表示匹配多个字符；
null 主要用于判断查询的列是否为空,主要使用 is null ;
select * from employees where manager_id is null;
3.逻辑操作符的组合
and ,or ,not;
4使用order by 进行排序
asc表示升序；
DESC表示降序；
select * from employees
where salary>8000
order by salary;
4.子查询
SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');
注意点: 单行比较必须对应单行子查询（返回单一结果值的查询）； 比如= ， >
多行比较必须对应多行子查询（返回一个数据集合的查询）；比如 IN , > ANY, > ALL 等
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
2.DML
DML: Data Manipulation Language , 数据操纵语言；简单的说就是SQL中的增、删、改 等语句。
DDL 数据库定义语言
merge into ceshi
create table PRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);
insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
commit;
create table NEWPRODUCTS
(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(60),
CATEGORY VARCHAR2(60)
);
insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
commit;
select * from products;
select *from NEWPRODUCTS for update;
MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
UPDATE SET p.product_name = np.product_name, p.category = np.category
when not matched then
insert values (np.product_id, np.product_name, np.category);
数据库事物控制
在Commit 或者 Rollback前后数据的状态：
1、在数据已经被更改，但没有Commit前 ，被更改记录处于被锁定状态，其他用户无法进行更改； 2、在数据已经被更改，但没有Commit前 ，只有当前Session的用户可以看到这种变更 ，其他Session的用户 看不到数据的变化。
3、在数据已经被更改，并且被Commit后，被更改记录自动解锁，其他用户可以进行更改；
4、在数据已经被更改，并且被Commit后，其他Session的用户再次访问这些数据时，看到的是变化后的数据。
事物的特性
1.什么是事务，事务的特性是什么？
A是原子性(atomic)：事务中包含的各项操作必须全部成功执行或者全部不执行。任何一项操作失败，将导致整个事务失败，其他已经执行的任务所作的数据操作都将被撤销，只有所有的操作全部成功，整个事务才算是成功完成。
C 是一致性(consistent)：保证了当事务结束后，系统状态是一致的。那么什么是一致的系统状态？例如，如果银行始终遵循着"银行账号必须保持正态 平衡"的原则，那么银行系统的状态就是一致的。下面的转账例子中，在取钱的过程中，账户会出现负态平衡，在事务结束之后，系统又回到一致的状态。这样，系 统的状态对于客户来说，始终是一致的。
I是隔离性(isolated)：使得并发执行的事务，彼此无法看到对方的中间状态。保证了并发执行的事务顺序执行，而不会导致系统状态不一致。
D是持久性(durable)：保证了事务完成后所作的改动都会被持久化，即使是发生灾难性的失败。可恢复性资源保存了一份事务日志，如果资源发生故障，可以通过日志来将数据重建起来。
银行转帐的例子是最经典的事务范例：
用 户把钱从一个银行账号转账至另一个银行账号，需要将资金从一个银行账号中取出，然后再存入另一个银行账号中。理想来说，这两次操作都应该成功。但是，如果 有错误发生，则两次操作都应该失败，否则的话，操作之后其中一个账号中的金额将会是错误的，整个操作过程应该是原子性的，两个操作都是一个原子事务操作的 一部分
锁
锁的概念：
Oracle中的锁的主要作用就是：防止 并发事务对相同的资源（所谓资源是指 表、行、共享的数据结构、 数据字典行等）进行更改的时候，相互破坏。 锁有既有隐式的，也有显式的； 但某用户对某一批数据进行更改，而未提交之前，Oracle会隐式的进行加锁； 当然用户也可以显式的加锁，比如： Select … from TableA Where … For UPDATE NoWait
数据库查锁语句
select a.*, C.type, C.LMODE
from v$locked_object a, all_objects b, v$lock c
where a.OBJECT_ID = b.OBJECT_ID and a.SESSION_ID = c.SID and
b.OBJECT_NAME = ‘table_name’;
多表查询和关联
等于链接（笛卡尔积）
内连接：相当于Oracle的“等于链接” ， 关键字：INNER JOIN。
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
自然链接：相当于Oracle的“等于连接”，只不过是让系统自己去找两张表中字段名相同的字段作为 “等于连接”条件；
“不等链接” 语法： 使用不等链接符，包括> , < , !=, between、
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
左外链接
左外连接主要用于全表显示主表时，左边的为主表，右边为附表
eg:
SELECT table1.column, table2.column
FROM table1 t1 left join table2 t2 on t1.column_id =t2.column_id;
自链接
“自链接： 其实是一种概念，某个table和自己本身链接 ，比如：table1给另一个“自己”起别名为table2
SELECT worker.last_name || ' works for ' || manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
全外连接 SELECT e.last_name, e.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id) ;
全外连接
SELECT e.last_name, e.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
数据库用户权限
一、创建
sys;//系统管理员，拥有最高权限
system;//本地管理员，次高权限
scott;//普通用户，密码默认为tiger,默认未解锁
二、登陆
sqlplus / as sysdba;//登陆sys帐户
sqlplus sys as sysdba;//同上
sqlplus scott/tiger;//登陆普通用户scott
三、管理用户
create user zhangsan;//在管理员帐户下，创建用户zhangsan
alert user scott identified by tiger;//修改密码
四、查看权限
select * from user_sys_privs;//查看当前用户所有权限
select * from user_tab_privs;//查看所用用户对表的权限
五、权限传递
即用户A将权限授予B，B可以将操作的权限再授予C，命令如下：
grant alert table on tablename to zhangsan with admin option;//关键字 with admin option
grant alert table on tablename to zhangsan with grant option;//关键字 with grant option效果和admin类似
六、角色
角色即权限的集合，可以把一个角色授予给用户
create role myrole;//创建角色
grant create session to myrole;//将创建session的权限授予myrole
grant myrole to zhangsan;//授予zhangsan用户myrole的角色
drop role myrole;删除角色
--导入导出命令
ip导出方式： exp demo/demo@127.0.0.1:1521/orcl file=f:/f.dmp full=y
exp demo/demo@orcl file=f:/f.dmp full=y
imp demo/demo@orcl file=f:/f.dmp full=y ignore=y
2225
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
