一.数据库对象及其操作
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