SQL语言

一、数据查询语言DQL第一部分

1、查询全部数据：select * from 表名;

select * from user_tab;

2、查询表中的指定列：select 列名1,列名2,列名3,...  from 表名;

select user_id,name,phone,email from user_tab; 

3、列的别名使用：select 列名1 "别名1",列名2 "别名2" from 表名;

select user_id "用户编号",name "用户姓名",phone "电话",email "邮箱" from user_tab;

　　别名的使用方法说明：

　　1)可用汉字做别名，如上举例；

　　2)也可用普通字符做别名：select name "user_name" from user_tab;

　　3)可以用关键字做别名，但是一定要用双引号引上。
　　　　select name "select" from user_tab;

　　4)不是关键字的可以不加双引号：select name user_name from user_tab;但是为了规范，养成良好习惯，别名统一都用双引号引上；

　　5)除了用空格隔开取别名，还提供了取别名的关键字as。
　　　　select name as "select" from user_tab;

　　用一个例子概括用法；
　　select user_id "用户编号",name user_name,phone as "电话",email "select" from user_tab;

　　双引号标识：取别名，定义表时表名、列名

4、连接符||，将多个列连接起来：select 列名1||列名2||... from 表名;

连接姓名与电话：
select name||phone from user_tab;

select name||'的电话是'||phone||';' from user_tab;

5、根据条件查询，需要使用where关键字

　where语句产生的结果为三种，真、假、空，但是只查询出为真的记录。

　空值(null)介绍：

　空值是一种不确定状态，是无效的、不确定的、未知的或不可预知的值；注：空值不是空格，也不是0；

　空值做基本运算(加、减、乘、除、比较等)，其结果都为空值。

　根据空值查询数据：

查询email为空的用户信息：
select * from user_tab where email is null;

查询email不为空的用户信息：
select * from user_tab where email is not null;

select * from user_tab where email = null;
查询不出任何数据，因为与空值做比较运算结果为空值。

6、根据单条件精确查询：select [*][列名1,列名2,......] from 表名 where 列名1='xxx' ......;

　　查询条件中字符型的数据一定要用单引号括起来，并且区分大小写：

select user_id,name,phone,email from user_tab where user_id='zf';

查询条件中是区分大小写的；
select user_id,name,phone,email from user_tab where user_id='ZF';

7、模糊查询，通配符%、_

　　%表示0到多个字符：

前半部分内容确定，模糊匹配后半部分内容；
select * from user_tab where user_id like 'z%';

后半部分内容确定，模糊匹配前半部分；
select * from user_tab where user_id like '%m';

只知道部分内容，不确定完整信息是什么，模糊匹配查询包含有该内容的信息；
select * from user_tab where user_id like '%m%';   --最常用，最实用

　　_表示一个字符：

select * from user_tab where user_id like '_m';
知道长度为两个字符，并且知道第二个是m

　　%和_的比较，_只匹配一个字符，而%匹配0到多个字符：

select * from user_tab where user_id like 'z%';

select * from user_tab where user_id like 'z_'; 

　　%和_的联合使用：

查询名字中第二个字母为z的行；
select * from user_tab where user_id like '_z%';

8、不等于操作符!=、<>

查询用户编号不是wm的用户信息：
select * from user_tab where user_id!='wm';

查询用户姓名不是zz的用户信息：
select * from user_tab where name <> 'zz';

9、去重

查询出结果后有重复信息，没意义，需要去重，使用关键字distinct：
select distinct job,deptno from emp;

　　练习：按照以下的格式进行结果输出，如 NO:7469,Name:SMITH,Job:CLERK

SELECT'NO:'||empno||',Name:'||ename||',Job:'||job FROM emp;

10、oracle常用数据类型

　　CHAR(size)：固定长度的字符数据，其长度为size个字节，size的最大值为2000字节。最小值和默认值为1

　　VARCHAR2(size)：可变长度的字符串，其最大长度为size个字节，size的最大值是4000，最小值为1；必须指定一个varchar2的size。

　　NUMBER(size) 数值有效位数为size的值

　　NUMBER(p,s)：p：精度位，precision，是总有效数据位数，取值范围是38，默认是38，可以用字符*表示38。
　　s：小数位，scale，是小数点右边的位数，取值范围是-84~127，默认值取决于p，如果没有指定p，那么s是最大范围，如果指定了p，那么s=0。
　　精度位p表示数值最多能有多少个有效数字，而小数位s表示最多能有多少位小数。
换句话说，p表示一共有多少位有效数字（即小数点左边最多有p-s位有效数字），s表示小数点右边有s位有效数字。
　　如number(5,2)类型的数据，就表示小数点左边最多有3位有效数字，右边最多有2位有效数字，加起来就是最多有5位有效数字，超过这个范围的数字就不能正确的存储下来。
　　P、S原理：
　　　最高整数位=p-s（当一个数的整数部分的长度 > p-s 时，Oracle就会报错）
　　　s正数，小数点右边指定位置开始四舍五入；
　　　s负数，小数点左边指定位置开始四舍五入；
　　　s是0或者未指定，四舍五入到最近正数；
　　　当p<s时候，表示数值是绝对值小于1的数字，且从小数点右边开始的前s-p位必须是0，保留s位小数

　　date：日期类型，日期型的数据只有加、减运算，没有乘除运算。

取当前时间是通过sysdate关键字获取；
select sysdate from dual;

select sysdate+1 from dual;

　　CLOB 可变的内存空间，存储大数据字符串，最高可存储2GB->了解

　　BLOB 存储较大的二进制数据，如图片等->了解

11、多条件查询，操作符

　　and：与，查询出多个条件同时满足的数据：select * from 表名 where 列名1='xx1' and 列名2='xx2' and ......

查询用户编号为wm并且姓名为吴妹的用户信息：
select * from user_tab where user_id='wm' and name='吴妹';

　　or：或者，查询出多个条件分别满足的数据集：select * from 表名 where 列名1='xx1' or 列名2='xx2' or ...

查询用户编号为wm或者姓名为赵峰的用户信息：
select * from user_tab where user_id='wm' or name='赵锋';

　　and和or结合使用，and的运算符优先级高于or：

select * from emp where deptno='30' and mgr='7698' or job='CLERK';

select * from emp where deptno='30' or mgr='7698' and job='CLERK';

查询结果分析比较；略

　　in()：in操作符，查询在括号中列出的取值的数据信息：

查询姓名为“吴妹、赵峰、系统管理员”的用户信息：
select * from user_tab where name in('吴妹','赵锋','系统管理员');

等同于多个or并列在一起，如下：
select * from user_tab where name='吴妹' or name='赵锋' or name='系统管理员';

not in()：查询不在括号内列出的取值信息；
查询姓名不为“吴妹、赵峰、系统管理员”的用户信息：
select * from user_tab where name  not in('吴妹','赵锋','系统管理员'); 

　　between ... and ...取两端闭区间的值，用于数值类型的数据

查询薪水在800到1500之间的职工信息：800和1500这两个值也会查询出来
select * from emp where sal between 800 and 1500;

等同于>= and <=，如下：
select * from emp where sal>=800 and sal<=1500;

　　not()：用于过滤条件外，取反的意思。其他任何查询条件外都可以加not操作，用于取反。

不加not是查询user_id以z开头的用户信息，加了not查询的是不是以z开头的用户信息：
select * from user_tab where not( user_id like 'z%')

没有not查询的是薪水在800与1500闭区间内的职工信息，加了not查询的是薪水小于800，大于1500的职工信息：
select * from emp where not(sal>=800 and sal<=1500);

12、排序查询：select [*][列名...] from 表名 order by 列名 [asc][desc];asc升序关键字，desc降序关键字，默认为升序。

升序：
select * from user_tab order by user_id;
select * from user_tab order by user_id asc;

降序：
select * from user_tab order by user_id desc;

通过多列进行排序：
select * from user_tab order by name,user_id;
先按name升序排，name排不出来，再按user_id升序排

多列排序时不同列可选择不同的排序方式：
select * from user_tab order by name asc,user_id desc;
先按name升序排，再按user_id降序排

可根据列号排序：
select * from user_tab order by 2;
根据第2列排序，就是name列。

13、where与order by结合使用，针对过滤条件查询出的结果进行排序：

　　select [*][列名...] from 表名 where 条件 order by 列名 ...;

　　练习：要求对雇员的工资由低到高进行排序

　　查看出部门号为10的雇员信息，查询的信息按照工资从高到低，若工资相等则按雇用日期从早到晚排列

1、SELECT * FROM emp order BY sal;
2、SELECT * FROM emp
WHERE deptno=10
order BY sal DESC,hiredate ASC;

 二、数据查询语言DQL第二部分

 1、rownum介绍

/*ROWNUM伪列练习*/
 /*ROWNUM采用自动编号的形式出现*/
SELECT ROWNUM,empno,ename FROM emp;
 
 
/*加入只想显示前5条记录，那么ROWNUM<=5*/
SELECT ROWNUM,empno,ename FROM emp WHERE ROWNUM<=5;
 
/*但是如果要查询5-10条的记录的话，则查询不出，只能采用子查询的方式*/
SELECT * FROM (SELECT ROWNUM m,empno,ename
      FROM emp
      WHERE ROWNUM<=10) tmp
WHERE tmp.m>5;

　　是oracle系统顺序分配为从查询返回的行的编号，返回的第一行是1，第二行是2

特点：
1.rownum只支持<或者<=，不支持>、>=、=（其中=1和>=1是例外支持）；
2.rownum必须从1开始；
3.rownum可以限制查询返回的总行数；
4.rownum是虚拟的，伪列，不是真实存在的列；

查询课程的前三行数据
select * from course where rownum<4;

select * from course where rownum=4;
select * from course where rownum>1;
查不出数据。
=只支持等于1，因为从1开始，所以支持
select * from course where rownum=1;
select * from course where rownum>=1;

--前10条数据
select * from (select * from S_EMP a order by rownum)
where rownum<11;

--后10条
select * from (select * from S_EMP a order by rownum desc)
where rownum<11;

2、函数运用

1）集合函数count(),avg(),max(),min(),sum()

　　注：除了count，其他函数都是针对数值类型的列；
　　用于统计的，函数不可用于查询条件中

select count(*) from student;
select count(sage) from student;
select max(sage) from student;
select min(sage) from student;
select avg(sage) from student;
select sum(sage) from student;
select sum(sage)/count(sage) from student;

2）字符函数lower(),upper(),initcap(),substr(,,),length()

　　可用于查询条件中也可以用户查询返回的列中

substr(字符串,截取开始位置,截取长度) //返回截取的字
substr('Hello World',0,1) //返回结果为 'H'  *从字符串第一个字符开始截取长度为1的字符串
substr('Hello World',1,1) //返回结果为 'H'  *0和1都是表示截取的开始位置为第一个字符
substr('Hello World',2,4) //返回结果为 'ello'
substr('Hello World',-3,3)//返回结果为 'rld' *负数(-i)表示截取的开始位置为字符串右端向左数第i个字符

select * from course where cid='sch01';
select * from course where cid=upper('sch01');
select * from course where lower(cid)='sch01';

select initcap(cid),cname,chour from course;

select length('skdfjkldsfj') from dual;
select substr('skdfjkldsfj',1,3) from dual;  --oracle下角标从1开始，不管是数组还是字符串

3）数字函数trunc(,)截取,round(,)四舍五入

1.1 trunc函数处理数字
　　trunc函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
其具体的语法格式如下

　　TRUNC（number[,decimals]）
其中：
number 待做截取处理的数值
decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分。
select trunc(123.98)from dual; /*123 */
select trunc(123.123,2)from dual; /*123.12 */
select trunc(123.123,-1)from dual; /*120 */

思考：-2，-3
　　注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。与取整类似，比如参数为1即取整到十分位，如果是-1，则是取整到十位，以此类推；如果所设置的参数为负数，且负数的位数大于或等于整数的字节数的话，则返回为0。
如：TRUNC(89.985,-3)=0。

1.2 trunc函数处理日期
　　trunc函数返回以指定元元素格式截去一部分的日期值。
其具体的语法格式如下：

TRUNC（date,[fmt]）

其中：
date为必要参数，是输入的一个日期值
fmt参数可忽略，是日期格式，用以指定的元素格式来截去输入的日期值。忽略它则由最近的日期截去

下面是该函数的使用情况：
trunc(sysdate,'yyyy') --返回当年第一天.
trunc(sysdate,'mm') --返回当月第一天.
trunc(sysdate,'d') --返回当前星期的第一天.
select trunc(sysdate,'YYYY')from dual;
select trunc(sysdate,'MM')from dual;
select trunc(sysdate,'D')from dual;

2.round函数(四舍五入）
　　描述 : 传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果。

SELECT ROUND( number, [ decimal_places ] ) FROM DUAL

参数:
number : 欲处理之数值
decimal_places : 四舍五入 , 小数取几位 ( 预设为0 )

Sample :
select round(123.456, 0) from dual; 回传 123
select round(123.456, 1) from dual; 回传 123.5
select round(-123.456, 2) from dual; 回传-123.46

3.ceil和floor函数
　　ceil和floor函数在一些业务数据的时候，有时还是很有用的。
　　ceil(n) 取大于等于数值n的最小整数；
　　floor(n)取小于等于数值n的最大整数。　

应用：对于每个员工，显示其加入公司的天数。

SQL> select floor(sysdate-hiredate) "入职天数",ename from emp;

或

SQL> select trunc(sysdate-hiredate) "入职天数",ename from emp;

4）数据转换函数to_char(),to_date()

　　to_char：将x转换成为一个VARCHAR2的字符串，可以指定可选参数format来说明格式；

　　to_date：将x转换成为一个DATE的字符串，可以指定可选参数format来说明格式。

SELECT TO_CHAR(12345.67,'99,999.99') FROM dual; /* 12,345.67 */
 
oracle 默认日期格式为：DD-mon-yy

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_LANGUAGE='SIMPLIFIED CHINESE';
alter session set nls_language='AMERICAN'

SELECT ADD_MONTHS('2003-04-12',13) FROM dual ;
SELECT LAST_DAY('2008-08-07') FROM dual;
SELECT NEXT_DAY(’2008-08-07’,1) FROM dual;
SELECT SYSDATE FROM dual;

to_char()主要应用在将日期型数据转换成字符型
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

to_date()将字符型数据转换成日期型
select to_date('1990-01-01','yyyy/mm/dd')+2 from dual;

3、子查询，嵌套查询

　　查询条件中有查询语句，子查询分为单行子查询和多行子查询。
　　子查询使用原则：
　　1.子查询要包含在括号内；
　　2.将子查询放在比较条件的右侧；
　　3.除非进行Top-N分析，否则不要在子查询中使用order by子句；
　　4.单行比较操作符对应单行子查询，多行比较操作符对应多行子查询；
　　5.使用子查询时，数据类型要一致。

　　多行子查询的比较操作符：
　　in：等于列表中的任意一个；
　　any：和子查询返回的任意一个值比较（大于最小的，小于最大的）；
　　all：和子查询返回的所有值比较（大于最大的，小于最小的）

查询年龄比“张萍莉”大的学生
select * from student where sage>(select sage from student where sname='张萍莉');

查询分数为80的学生信息
select * from student where sid in(select sid from score where grade=80);

查询年龄大于“王五”、“王丽君”、“华嘉”当中任意一人的学生信息
select * from student where sage>any(select sage from student where sname in('王五','王丽君','华嘉'));

查询年龄全部大于“王五”、“王丽君”、“华嘉”这三人的学生信息
select * from student where sage>all(select sage from student where sname in('王五','王丽君','华嘉'));

4、多表查询

　　多表查询应用在有关联的表中。连接就是用连接符号连接了两个或者多个表中的字段。连接符号就是前面介绍的过滤条件中的各种操作符。

1）笛卡尔积，不加任何条件

select * from student,course;
将两表数据相乘,两两结合

select * from student s,department d where s.did=d.did;

select * from score s1,course c,student s2 where s1.sid=s2.sid and s1.cid=c.cid;

　　连接n个表，至少需要n-1个有效的连接条件。例如：连接3个表至少需要2个条件；

 2）单行子查询可以转换成多表查询

select * from student where sage>(select sage from student where sname='张萍莉');

select s1.* from student s1,student s2 where s1.sage>s2.sage and s2.sname='张萍莉';

3）内连接，是笛卡尔积的特殊形式；可以得到与笛卡尔积相同的结果，但是性能上优于笛卡尔积

select * from student s,department d where s.did=d.did;

select * from student s inner join department d on s.did=d.did;

select s1.* from student s1,student s2 where s1.sage>s2.sage and s2.sname='张萍莉';

select s1.* from student s1 inner join student s2 on s1.sage>s2.sage and s2.sname='张萍莉';

select * from score s1,course c,student s2 where s1.sid=s2.sid and s1.cid=c.cid;

select * from score s1 inner join course c on s1.cid=c.cid 
inner join student s2 on s1.sid=s2.sid;

4）外链接

左外连接：将符合连接条件的数据和左表中不符合查询条件的数据都查询出来，如果左表的某行在右表中没有匹配行，则在结果集行中右表的所有选择列表列均为空；

select * from student s left outer join department d on s.did=d.did;

右外连接：与左外连接相反，除了符合连接条件的数据，也会将右表中不符合查询条件的数据也查询出来；

select * from student s right outer join department d on s.did=d.did;

全连接：除了符合连接条件的数据，把左右两张表中不符合条件的数据都查询出来；

select * from student s full outer join department d on s.did=d.did;

5、分组查询　

　　select与from之间只能出现使用被by的字段和组函数。按某个列分组统计

--组函数前加上列，必须要分组，需要加上GROUP BY子句：

select title,max(salary)
from s_emp
group by title;

GROUP BY子句中若出现多列时是按照多列联合唯一进行分组：

select dept_id,title,count(*)
from s_emp
group by dept_id,title;

--GROUP BY子句，放在WHERE语句之后：

select dept_id,count(*) "number"
from s_emp
where dept_id=41 or dept_id=42
group by dept_id;

--HAVING子句用来限定结果集：用了having一定要用group 

select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>2000;

--查询平均薪资高于32号部门平均薪资的部门号和平均薪资
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>(select avg(salary)
          from s_emp
          where dept_id=32
              );

　　练习：

/*查询工资排在4~6的员工*/
1、select * from (select * from (select * from emp where sal is not null order by sal desc) where rownum<=6 order by sal) where rownum<=3 order by sal desc;

/*显示所有雇员的姓名及姓名的后3个字符*/
2、SELECT ename,SUBSTR(ename,-3,3) FROM emp;

/*将雇员姓名变为开头字母大写*/
3、SELECT INITCAP(ename) FROM emp;

4、/*先确定工资等级表的内容*/
 SELECT * FROM salgrade; 
/*查询每个雇员的姓名、工资、部门名称和工资在公司的等级*/
SELECT e.ename,e.sal,d.dname,s.grade
FROM emp e,dept d,salgrade s
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;
/*查询其领导姓名及工资所在公司的等级*/
SELECT e.ename,e.sal,d.dname,s.grade,m.ename,m.sal,ms.grade
FROM emp e,dept d,salgrade s,emp m,salgrade ms
WHERE e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal
AND e.mgr = m.empno AND m.sal BETWEEN ms.losal AND ms.hisal;

5、SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>2000;

6、SELECT job, SUM(sal) totalSal  FROM emp WHERE job <> 'SALESMAN' GROUP BY job HAVING SUM(sal) > 5000 ORDER BY totalSal;

7、SELECT * FROM emp
WHERE sal>(SELECT sal FROM emp WHERE empno=7654)
AND job=(SELECT job FROM emp WHERE empno=7788);
 
8、/*查询部门员工数、部门平均工资*/
SELECT deptno,COUNT(empno),AVG(sal)
FROM emp
GROUP BY deptno;
/*查询部门的名称，及最低收入雇员姓名，要进行表关联(子查询)*/
SELECT d.dname,ed.c,ed.a,e.ename
FROM dept d,(
 SELECT deptno,COUNT(empno) c,AVG(sal) a,MIN(sal) min
 FROM emp
 GROUP BY deptno) ed, emp e
WHERE d.deptno=ed.deptno AND e.sal = ed.min;

三、数据操作语言DML

　　操作语言能够开启事务，一个事务中的操作要么全成功，要么全失败；
　　事务是由完成若干项工作的DML语句组成的逻辑单位。
　　事务的特点：
　　1.原子性（不可分性）：指所包含的操作要么全做，要么全不做；
　　2.隔离性：事务结束前对事务的操作只对当前操作窗口可见；
　　3.永久性：执行结束永久有效；
　　4.一致性：事务结束后，数据保持一致。

　　事务中的操作数据只对当前操作窗口可见，只有提交后其他操作窗口才可见。

1、提交事务commit;

2、回滚事务rollback;

　　在事务中可以设置保存点。当回滚时从下往上进行回滚的，当指定回滚到保存点时，会将保存点后面的DML语句；
　　被回滚。事务进行过程中状态丢失、恢复到事务开始前的状态、其他用户对这些记录进行更新 操作、系统失败或非正常的终止SQLPlus，都将会rollback。

　　savepoint设置回滚点，通过命令rollback to回退到回滚点

3、插入insert 

1）往表里的所有列写入数据：
insert into 表名 values(列1值,列2值,列3值......);

insert into user_tab values('test1','testname','testname','18123451351','test1@163.com',sysdate);

再
commit;

2）往表里的指定的列写入数据:

insert into 表名(列1名,列2名,列3名......) values(列1值,列2值,列3值......);

insert into user_tab(user_id,name,passwd,create_date) values('test2','testname2','testname2',sysdate);


再
commit;

3）INSERT INTO 子查询

INSERT INTO EMP_41
SELECT ID,LAST_NAME,USERID,START_DATE
FROM S_EMP
WHERE DEPT_ID=41;

4、修改update

　　update 表名 set 列1=列1新值,列2=列2新值...... [where 过滤条件];

update user_tab set passwd='haha' where user_id='test1';
将user_id为test1的用户密码修改为haha；


再
commit;

5、删除delete

　　delete from 表名 [where 过滤条件];

删除刚刚插入的数据：
delete from user_tab where user_id in('test1','test2');


再
commit;

　另外一种删除数据的方法，通过truncate删除：truncate table 表名;

备份表数据：
select 'insert into user_tab values('''||user_id||''','''||name||''','''||passwd||''','''||phone||''','''||email||''',to_date('''||to_char(create_date, 'dd-mm-yyyy hh24:mi:ss')||''', ''dd-mm-yyyy hh24:mi:ss''));' from user_tab;


再
truncate table user_tab;

　　truncate与delete的区别：

　　delete会开启事务，可以回滚(有commit;)；truncate不可以回滚，
　　truncate是DDL语言，被隐式提交。

注：不能删除被其他表使用的数据

　　练习：

复制一张表，例如复制EMP表为MYEMP
1、 CREATE TABLE MYEMP AS SELECT * FROM emp;
 
将编号为7899的雇员的领导取消
2、 UPDATE myemp SET mgr=null WHERE empno=7899;
 
更新时，一定要注意不能批量更新(加上WHERE子句)，多列更新例子如下
3、UPDATE myemp SET mgr=null,comm=null WHERE empno IN(7369,8899);
 
删除掉全部领取奖金的雇员
4、 delete FROM emp WHERE comm is NOT NULL;

在emp表重插入两条数据，要求雇佣时间（1、当前时间；2、2017-4-8）
5、insert into emp values('8000','kathleen','tester','7788',sysdate,'9999','1500.00','20');
insert into emp values('8001','jack','dev','7788',to_date('2017-04-08','yyyy/mm/dd'),'9999','2500.00','20');

插入多条数据（从某张表复制数据）：
insert into myemp( empno,ename,job,mgr,hiredate,sal,comm,deptno)
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno  from emp e;

 四、数据定义语言DDL

 1、create创建

　　create table 表名(字段1名 数据类型,字段2名 数据类型......);

create table girl(
gname varchar2(20),
age number(3),
city varchar2(10));

表名和列名的命名规则：

必须以字母开头；
必须在1~30个字符之间；
必须只能包含A~Z、a~z、0-9、_、$、#；
必须不能喝用户定义的其他对象重名；
必须不能是oracle的关键字

2、alter修改表

　　添加列：alter table 表名 add(列1 数据类型,列2 数据类型,...);

给girl表增加height和weight两列
alter table girl add(height number(5,2),weight number(5,2));

　　修改列：alter table 表名 modify(列名 数据类型,...);一般为修改列名数据类型的大小

将girl表中gname字段长度改为30；
alter table girl modify(gname varchar2(30));

　　删除一列：alter table 表名 drop column 列名;

　　删除多列：alter table 表名 drop (列1名,列2名...);

删除表girl的height列；
alter table girl drop column height;

　修改列名：alter table 表名 rename column 旧列名 to 新列名;

alter table girl rename column gname to girl_name;

修改表名
rename 旧表名 to 新表名；
rename girl to girl_new;

3、drop删除

　　删除表：drop table 表名;

删除girl表
drop table girl;

　　练习：

1\如果只能复制一张表的结构，但不复制内容，则加上一个不可能成立的条件即可
CREATE TABLE tmp AS (SELECT * FROM emp WHERE 1=2);

2\/*创建表Person*/
CREATE TABLE person(
 pid VARCHAR2(18),
 name VARCHAR2(30),
 age NUMBER(3),
 birthday DATE,
 sex VARCHAR(2) DEFAULT 'M'
);
/*如果发现创建表后需要添加特定的列，例如address列，则可以使用ALTER TABLE命令*/
 ALTER TABLE person ADD(address VARCHAR2(20));
 
/*修改表中的列属性*/
ALTER TABLE person MODIFY(address VARCHAR2(30));
 
/*在数据库程序开发中，很少去修改表结构，在IBM DB2中就没有提供ALTER TABLE命令
在Oracle中提供RENAME命令对表进行重命名*/
RENAME person to personer;
 
/*在Oracle中要清空一张表的数据，但又不需要回滚，需要立即释放资源(与DELETE区别)*/
TRUNCATE TABLE personer;
或者drop TABLE personer;
 

4、约束介绍

　　约束就是对表中的数据进行限制，允许什么样的值，不允许什么样的值。就是为了保持数据的实体完整性和参照完整性，避免出现脏数据。

　　约束有五类：主键约束primarykey、外键约束foreignkey、非空约束notnull、唯一约束unique、检查约束check；

　　主键约束：“第二范式”要求行必须具有唯一性，主键就是来标识行的唯一性的关键。
　　设置为主键的列将会自动被创建索引；
　　主键不允许空值；
　　主键约束可以定义在一个列上，也可以定义在多个列的组合上。

　　主键通常分为两类：自然主键和代理主键；
　　自然主键：主键所在的字段有确定的意义；
　　代理主键：像id一样的东西，没有确切的意义，
　　只用来标识一条记录。推荐使用；

　　主键一般为一个，不推荐使用联合主键

创建一个学历表
create table province(
pro_id varchar2(10),
pro_name varchar2(30),
primary key(pro_id));

　　外键约束：为确保“参照完整性”，必须使用外键约束。外键可以有多个。

　　唯一约束：在表中每一行中所定义的这列或这些列的值都不能相同。必须保证唯一性。否则就会违反约束条件。不能包含重复值，但允许为空。
　　可以为一个列定义唯一约束，也可以为多列的组合定义唯一约束。
　　系统将自动为唯一约束的列创建索引。

　　检查约束：用于用户自定义的约束，如对于学生性别，我们可以约束为只允许取“男”或“女”两个值，对于学生年龄，可以约束为15~50岁之间的值等。
　　常用的检查约束：
　　算术运算：如 grade<=100，任何算术运算符均可使用
　　逻辑运算：sname is not null
　　指定值：如 ssex in('M','F')
　　范围约束：如sage between 15 and 50

5、约束的使用方法：在建表之时和建表之后都可创建约束，一般在建表之时创建约束；一般放到后面

1）创建表

　　创建无约束表

create table person (pid number(7),
name varchar2(30),
age number(3),
telephone number(11));

　　创建表级别约束：表级别不能使用NOT NULL直接约束，可通过修改列约束方式约束

create table human(
id varchar2(20),
name varchar2(20),
age number(3),
province varchar2(10),
sex char(1),
telephone varchar2(15),
primary key(id),
check (sex in('男','女')),
unique (telephone),
foreign key(province) references province(pro_id));

　　创建列级别约束

create table book (bid number(20) primary key,
bname varchar2(100) constraint book_bname_nn not null,
pid number(7));

　　创建表时，创建外键约束

--使用表级外键约束
CREATE TABLE  table_name
(column_1  datatype ,
column_2  datatype ,
...
CONSTRAINT fk_column
  FOREIGN KEY (column_1, column_i, ... column_n)
  REFERENCES parent_table (column_1, column_i, ... column_n)
);


--使用列级外键约束
CREATE TABLE  table_name
(column_1  datatype ,
column_2  datatype  CONSTRAINT fk_column  REFERENCES  parent_table (column_name),
...
);

2）添加约束语法：

ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(约束字段);
约束类型命名规则：
PRIMARY KEY:主键字段_PK
UNIQUE：字段_UK
CHECK：字段_CK
FOREIGN KEY：父字段_子字段_FK

alter table person add constraint person_pid_pk primary key(pid);

alter table person add constraint person_name_uk unique(name);

alter table person add constraint person_age_ck check(age between 0 and 150);

alter table book add constraint person_book_pid_fk foreign key(pid) references person(pid);

3）增加非空约束语法：alter table table_name modify column_name [constraint constraint_name] not null;

alter table human  modify age constraint human_age_nn  not null

4）删除约束语法：ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;

ALTER TABLE person DROP CONSTRAINT person_age_ck;

ALTER TABLE book DROP CONSTRAINT person_book_pid_fk;

5）建表后添加约束，无constraint的增加、修改约束方法：

　　不命名约束，oracle会自动产生特定的约束，名字以sys_c+数字

　　先删除human表，再建不带约束的human表

--添加非空约束
alter table human modify(age not null);

--添加主键约束
alter table human add(primary key(id));

--添加检查约束
alter table human add(check (sex in('男','女')),check (age between 1 and 150));

--添加唯一约束
alter table human add(unique(telephone));

--添加外键约束
alter table human add(foreign key(province) references province(pro_id));
key(当前表的字段名，即外键的字段名)

6、有了约束对于表的使用就有了限制

　　插入、修改数据时五种约束限制，非空字段不能插入空值；
　　主键不能为空不能重复，取值要在检查约束内，唯一约束的列不能重复但可以为空，
　　外键列的值要在外键对应的表中有相应取值才可以；

　　删除数据时不能删除正在被其他表使用的记录，只有当其他表不再使用这个记录才能被删除。

　　练习：创建表时添加主键约束、删除、表中添加主键约束

/*主键约束添加删除
1、创建表的同时创建主键约束*/
/*无命名 */
create table accounts ( accounts_number number primary key, accounts_balance number );
 
/*删除表中已有的主键约束*/
 
SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME='ACCOUNTS';
/*找出主键名 */
ALTER TABLE ACCOUNTS DROP CONSTRAINT SYS_C0011131;
 
/*向表中添加主键约束 */
ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY(ACCOUNTS_NUMBER);

7、设计表的要求：数据库范式

1）第一范式：无重复的列

　　关系表中每一列都是不可分割的基本数据项。同一列中不能有多个值。

　　基本类型：整型、实数、字符型、逻辑型、日期型等

　　在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
　　所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属 性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只 包含一个实例的信息。例如，员工信息表，不能将员工信息都放在一列中显示，也不能将其中的两列或多列在一列中显示；员工信息表的每一行只表示一个员工的信息，一个员工的信息在表 中只出现一次。简而言之，第一范式就是无重复的列。

　　举例：联系人表中要存联系方式，将手机号、联系地址、邮编、邮箱全部存在一个列中就不正确，如果要检索手机号怎么检索？没法检索，应该建立手机号、联系地址、邮编、邮箱这4列分别存放对应信息。

2）第二范式：无重复的行，依赖于主键

　　第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。
第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。
为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。如 员工信息表中加上了员工编号（emp_id）列，因为每个员工的员工编号是惟一的，因此每个员工可以被惟一区分。这个惟一属性列被称为主关键字或主键、主码。
　　第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这 一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二 范式就是非主属性非部分依赖于主关键字。

　　需要有可以唯一标识一行的主键字段，比如每个班的学生信息，都有学号，就是用来唯一标识一个学生的，通过姓名是不能够的，因为有同名同姓的。

3）第三范式：主表与外表

　　满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。
　　那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。
　　如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。

　　练习：下面以一个学校的学生系统为例分析说明，这几个范式的应用。

drop table person;
 drop table selectCourse;
--第一范式：所有的信息都集中在一张表上，例如
CREATE TABLE person(
 pid NUMBER(4) PRIMARY KEY,
 name VARCHAR2(50),
 info VARCHAR(200)
);
 
--第一范式会出现问题，例如创建一张学生选课表
CREATE TABLE selectCourse(
 stuno VARCHAR2(50),
 stuname VARCHAR2(50),
 stuage NUMBER,
 cname VARCHAR2(50),
 grade NUMBER,
 credit NUMBER
);
 
/*以上不仅所有的课程信息冗余了，而且还存在以下的问题：
1、没有学生选该门课，那么该门课就消失了
2、课程本身有编号，按照以上设计，课程编号肯定重复
3、要更改课程信息，则要修改许多记录*/
 
drop table selectCourse;
drop table student;
drop table course;
--使用第二范式进行修改
CREATE TABLE student(
 stuno  VARCHAR2(50) PRIMARY KEY,
 stuname VARCHAR2(50),
 stuage NUMBER
);
 
CREATE TABLE course(
 cid NUMBER(5)PRIMARY KEY,
 cname VARCHAR2(20),
 credit NUMBER
);
 
CREATE TABLE selectCourse(
 stuno VARCHAR2(50),
 cid  NUMBER(5),
 grade NUMBER
 --设置主-外键关系
);
 
/*以上设计解决了以下问题：
1、学生不选课，课程不会消失
2、更新课程的时候直接更新课程表
3、所有关联关系在关系表中体现
 这里是完成了多-多关系*/
 
 
drop table student;
 drop table collage;
/*使用第三范式：
按照第二范式的设计一张学生表，包括学号、姓名、年龄、所在院校、学院地址、学院电话等
会出现一个学生同时在多个学院同时上课，正常应该是：一个学院包含多个学生，一个学生属于一个学院C*/
 
CREATE TABLE collage(
 cid NUMBER(5)PRIMARY KEY,
 cname VARCHAR2(20),
 caddress VARCHAR2(20),
 ctel VARCHAR2(20)
);
 
CREATE TABLE student(
 stuno VARCHAR2(50) PRIMARY KEY,
 stuname VARCHAR2(50),
 stuage NUMBER,
 cid NUMBER(5)
-- 建立主-外键关联
);
--以上是很明确的1对多的关系

五、数据库对象介绍

介绍数据库自己的数据字典表

Oracle数据包括用户表和数据字典两部分
　　数据字典包含数据库的相关信息：
　　1.数据库用户 的名字
　　2.授权给用户的权限信息
　　3.数据库对象的信息
　　4.表的约束信息

根据权限分类：
　　USER：用户所创建对象对应的数据字典表，如：USER_objects,user_tables等
　　ALL：所有用户所能访问对象，如：all_objects,all_tables
　　DBA：所有对象对应的数据字典表，如：dba_objects,dba_tables
　　V$：描述系统性能相关的数据字典表
　　DICTIONARY：一个特殊的数据字典表，用来描述数据字典表相关信息的数据字典表
　　TABLES_PRIVILEGES：数据表权限

 dictionary:存放数据字典中所有表的信息

user_objects:存放了当前用户下所有的对象（包括表、视图、索引、序列和约束等）

user_constraints:存放当前用户下的所有约束信息

user_cons_columns:用来查看和约束相关的列

user_users:存放当前用户的信息

user_tables:当前用户名下的表的信息（包括所属表空间等）

all_user:存放数据库中所有用户的信息

user_indexes:存放当前用户的所有索引信息

all_indexes:存放当前用户的所有表信息

all_tables:存放数据库中所有用户的表信息

user_view:存放数据库中所有用户的视图信息

user_synonyms:存放当前用户所有表的同义词

user_tab_privs_made带privs均是和权限相关的表

1、表

　　表：存储数据的二元组，有行和列组成。表存放在哪里，放在表空间中。

　　表空间是一个数据结构，用于组合被相似地访问的数据。　　

　　表空间是一个逻辑概念，用于存放某一个或多个用户的数据库对象（如表，索引，用户，存储过程等），如需要正常使用数据库，必须首先为其创建表空间。系统安装时默认自带了几个表空间，如SYSAUX，SYSTEM，TEMP，UNDOTBS1，USERS等，我们可以选择使用USERS这个表空间来保存我们自己的数据库对象。但是建议全新创建一个表空间，一方面不破坏系统现有的配置，另一方面也需要了解如何创建表空间及一些注意事项。

　　简单创建表空间语句
create tablespace 表空间名
datafile 'D:\xxx.dbf'
size xxxm autoextend on next xxm[maxsizexxxxm][maxsizeunlimited] extent management local;

extent management 有两种方式 
extent management local（本地管理）; 
extent management dictionary（数据字典管理）

以sys用户登录到数据库中
create tablespace wuwu datafile 'D:\wuwu.dbf' size 100m autoextend on next 10m  maxsize 200m;

--创建表空间
CREATE TABLESPACE TESTSPACE
DATAFILE 'd:\oracledata\DBSPACE.DBF'
SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M
EXTENT MANAGEMENT LOCAL;

--创建临时表空间
CREATE TEMPORARY TABLESPACE TEST_TEMP
TEMPFILE 'd:\oracledata\TEST_TEMP.DBF'
SIZE 32M
AUTOEXTEND ON NEXT 32M MAXSIZE 256M
EXTENT MANAGEMENT LOCAL;

　　创建表时可以指定表空间

create table t1(
id varchar2(20))
tablespace wuwu;

修改表所在的表空间
alter table t1 move tablespace users;

　　删除表空间

DROP TABLESPACE TESTSPACE
INCLUDING CONTENTS AND DATAFILES;

　　只删除临时表空间，不能删除临时表空间文件

DROP TABLESPACE TEST_TEMP
INCLUDING CONTENTS AND DATAFILES;

　　能删除临时表空间和文件

DROP TABLESPACE TEST_TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

2、视图

　　是虚拟表，是从多个表中通过查询语句引用过来的。视图带有行和列，但是在数据库中并不存储数据，数据是通过查询语句从被引用的表中获取。
　　本质：一条有名字的SELECT语句
　　表现：一到多张表的部分内容

/*问题：使用scott登录Oracle以后，创建视图，提示“权限不够”，怎么解决？
   这是因为scott这个帐户目前没有创建视图的权限。
解决方法为:
 首先使用system帐户进行登录
 
然后执行：*/
    grant create any view to scott;
  
/*提示：授权成功。
再使用scott登录就可以创建视图了*/
/*1、创建视图
CREATE VIEW 视图名称 AS 子查询
这条子查询是非常复杂的语句*/
CREATE VIEW empv20 AS
 SELECT empno,ename,job,hiredate
 FROM emp
 WHERE deptno=20;
--2、查询视图
 SELECT* FROM empv20;
 
--3、删除视图
 DROP VIEW empv20;
 
/*如果要修改视图，则要先删除视图，在Oracle为了方便用户修改视图，提供了一个替换的命令
CREATE OR REPLACE 视图名称 AS 子查询
视图可以封装复杂的查询，例如查询部门名称，部门的人数，平均工资以及最低工资的雇员*/
 CREATE OR REPLACE VIEWempv20AS
 SELECT d.dname,ed.c,ed.a,e.enameFROMdept d,(
 SELECT deptno,COUNT(empno) c, AVG(sal) a,MIN(sal) minsal
 FROM emp
 GROUP BY deptno) ed,emp e
WHERE d.deptno=ed.deptno  AND e.sal=ed.minsal;
 --2、查询视图
 SELECT* FROM empv20;
--在开中发每次都写这么长的SQL语句不方便，可以将其建立成视图
 
--如果对视图进行更新操作，在视图中不应该包含真实数据，按以下命令进行操作
UPDATE empv20 SET deptno=30 WHERE empno=7369;
/*发现视图已经正常更新，因为emp表中7369编号已经修改为30了，所以在创建视图是有条件的
SQL提供了两个重要的参数*/
 
--WITH CHECK OPTION：不能更新视图的创建条件
CREATE OR REPLACE VIEW empv20 AS
 SELECT* FROM emp WHERE deptno=20
 WITH CHECK OPTION;
 
--创建条件不能进行更新了，但其他字段仍然可以更新
UPDATE empv20 SET ename='wilson'WHEREempno=7369;
 
--所以这时可以使用视图的第2个条件：创建只读视图
CREATE OR REPLACE VIEW empv20 AS
 SELECT* FROM emp WHERE deptno=20
 WITH READ ONLY;

　　VIEW优点：

1.限制数据库的访问
2.简化查询
3.数据独立性，可以进行增删改查
4.对同一数据有不同的表现，不是原表。

　　创建：create  view 视图名 as  select查询语句

3、索引

　　是以表中列为基础的数据库对象，数据库用其加快检索速度。它保存着表中排序的索引，并且记录了索引列在数据表中的物理存储位置（ROWID），实现了表中数据的逻辑排序。

--为EMP表的ENAME列创建唯一索引。
CREATE UNIQUE INDEX UQ_ENAME_IDX ON EMP(ENAME);  
 
--为EMP表的SAL列创建索引。
CREATE INDEX IDX_SAL ON EMP(SAL);  
 
--在查询中可能经常使用job的小写作为条件的表达式，因此创建索引时，可以先对JOB列中的所有值转换为小写后创建索引，而这时需要使用lower函数，这种索引称为基于函数的索引。
 
 CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB));
 --在emp表的job,ename列上建立一个组合索引，然后查看索引信息。
 CREATE INDEX IND_NAME ON EMP(JOB,ENAME);
--查看索引名
select * from user_indexes;
--删除索引
drop index UQ_ENAME_IDX;

　　自动创建索引：在创建主键约束、唯一键约束以及使主键约束、唯一键约束生效时会自动创建唯一索引。

　　手动创建索引：用户可以在列上创建非唯一性的索引。

　　索引分类：
1.唯一性索引（自动创建）
2.非唯一性索引（手动创建）
3.单列索引
4.组合索引（多列）

　　索引使用的场合：
1.列频繁用于WHERE子句或连接条件中
2.列的取值范围较广
3.表很大，记录数量较多
4.查询返回的结果占总记录的百分比在2%-4%内

　　create index 索引名 on 表名(列名1[,列名2...])

　　两张索引表：
1.USER_INDEXES包含索引的名字和它的唯一性索引
2.USER_IND_COLUMNS包含索引名、表名和列名

4、序列

　　是Oracle一个命名的顺序编号生成器，能够以串行方式生成一系列顺序整数。
　　序列可被设置为递增或递减，有界或无界，循环或不循环等方式。主要运用在主键中。

--创建SEQUENCE
CREATE SEQUENCE SEQ_S_DEPT
INCREMENT BY 1
START WITH 60
MAXVALUE 9999999
NOCACHE
NOCYCLE;
 
--查看SEQUENCE值
SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,LAST_NUMBER
FROM USER_SEQUENCES;
 
select * from S_DEPT;
 
--使用SEQUENCE
INSERT INTO S_DEPT(ID,NAME,REGION_ID)
VALUES (SEQ_S_DEPT.NEXTVAL,'Finance',2);
 
--删除SEQUENCE
DROP SEQUENCE SEQ_S_DEPT;　

　　主键：
1.代理主键：流水账号，依次递增
2.自然主键：有字符、数字

create sequence 序列名 
increment by n1 步长
start with n2 开始值
maxvalue n3 最大值
minvalue n4 最小值
cycle|nocycle 是否循环，到最大值后开始循环
cache n|nocache   是否产生n个值放在缓存中，可以提高性能，不加cache其值默认为20


使用SEQUENCE:通过NEXTVAL和CURRVAL进行调用
1.NEXTVAL:每次获得不同的SEQUENCE值,每调用一次，序列号+1
2.CURRVAL:获得当前指向的SEQUENCE值

删除SEQUENCE: DROP SEQUENCE seq_name

5、同义词

　　是指数据库对象的一个别名，经常用于简化对象访问和提高对象访问的安全性；可以创建同义词的数据库对象有：表、视图、同义词、序列、存储过程、函数、程序包、java类。

--创建同义词
 
-----附给tester账号创建同义词的权限------
GRANT CREATE SYNONYM TO tester;
 
-----切换账号，创建同义词-----
 
CREATE SYNONYM S_S_S FOR S_EMP;
 
------查询同义词----
SELECT * FROM  S_S_S;
SELECT * FROM USER_SYNONYMS WHERE SYNONYM_NAME='S_S_S';
 
--删除私有同义词
DROP SYNONYM S_S_S;
 
 
--创建公共同义词
GRANT CREATE SYNONYM TO tester;--DBA授权创建私有同义词权限
 
GRANT CREATE PUBLIC SYNONYM TO tester;--DBA授权创建公共同义词权限
CREATE SYNONYM S_S_S FOR S_EMP;--创建私有同义词
 
CREATE PUBLIC SYNONYM S_S_S_S FOR S_EMP;--创建公共同义词
 
GRANT SELECT ON S_EMP TO tester;--授权查询权限
 
SELECT LAST_NAME FROM tester.S_EMP;--查询其他账户已授权的表，需用"用户名.表名"调用
 
 
--删除公共同义词
DROP PUBLIC SYNONYM S_S_S_S;

　　create [public] synonym 同义词名 for 对象名;

　　普通用户可以通过赋权限方法，创建、删除synonym对象，但不可以删除public对象；
加一个public，是公共同义词,普通用户即便有创建私有同义词的权限，还需要赋予创建公共同义词的权限；公共同义词，删除只能由DBA执行；其他用户可以通过这个同义词来访问对应的对象。

　　删除同义词
DROP SYNONYM 同义词名;

　　普通用户删除
DROP SYNONYM 同义词名;

　　DBA用户删除
DROP PUBLIC SYNONYM 同义词名;

　　练习：

/*一、序列的使用
 
在很多数据库系统中都存在一个自动增长的列，如果在Oracle中要完成自动增长的功能，只能依靠序列完成
 
序列的创建格式
CREATE SEQUENCE sequence
[INCREMENT BY n][START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE n| NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}]*/
 
--创建一个myseq的序列
 CREATE SEQUENCE myseq;
/*创建完该序列之后，所有的自动增长应该由用户自己处理
nextVal:取得序列的下一个内容
currVal:取得序列的当前内容*/
 
--建立一张表验证序列
CREATE TABLE testseq(
 next NUMBER,
 curr NUMBER
);
 
 INSERT INTO testseq(next,curr)VALUES(myseq.nextval,myseq.currval);
--将这条SQL执行5次，然后进行查表操作
 SELECT * FROM testseq;
 
/*可以发现,nextval的内容始终在自动增长，而curr使用取出当前操作的序列的结果，该序列增长幅度为1
如果要进行修改，则加上 INCREMENT BY 长度的语句*/
 
 DROP SEQUENCE myseq;
 CREATE SEQUENCE myseq INCREMENT BY 2;
--查看序列
select * from user_sequences;
--如果需要查看某个特定的序列，如下：
select * from user_sequences  where  sequence_name like '%MYSEQ%';
 --注意：序列名区分大小写。
 
--发现每次取出的结果都是奇数 1,3,5,7,9，序列是从1开始的，我们可以指定序列的开始位置，例如
 CREATE SEQUENCE myseq MAXVALUE 10 INCREMENT BY 2 START WITH 2 CACHE 2 CYCLE;
 
--二、同义词的概念(了解)
 
 SELECT SYSDATE FROM dual;
--dual是一张虚拟表，该表在SYS用户下有定义，可以使用以下语句查询到
 SELECT * FROM tab WHERE TNAME='DUAL';
/*此表在SYS下，但SCOTT用户却可以直接通过表名称访问，正常情况下我们是需要使用"用户名.表名称"
该情况就是同义词的作用*/
 
--创建同义词：
--语法：CREATE [PUBLIC] synonym  同义词名称 FOR 用户名.表名称';
--例如，将scott.emp 定义 emp 的同义词
 CREATE public synonym  stemp FOR scott.mytemp;
 
--删除同义词
 DROP synonym  stemp;
--同义词这种特性只适合于Oracle数据库

六、数据控制语言DCL

　　create user 用户名 identified by 密码;

创建用户：
create user zhangsan;//在管理员帐户下，创建用户zhangsan
 
alert user scott identified by tiger;//修改密码

　　权限分为系统权限：对于数据库的权限

　　对象权限：操作数据库对象的权限；

　　赋系统权限grant 权限1,权限2... to 用户名;
create session;创建会话，及登录
create table; 创建表
create sequence; 创建序列
create view; 创建视图
create procedure; 创建存储过程

新建的用户也没有任何权限，必须授予权限　　
grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限
grant unlimited tablespace to zhangsan;//授予zhangsan用户使用表空间的权限
grant create table to zhangsan;//授予创建表的权限
grante drop table to zhangsan;//授予删除表的权限
grant insert table to zhangsan;//插入表的权限
grant update table to zhangsan;//修改表的权限
grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)

　　在对象所有者下，赋对象权限grant select,insert,delete on 对象名 to 用户名;

grant select 
on emp 
to user;

grant select 
on emp 
to user
with grant option;
被分配权限的用户也可以将该权限分配给其他用户

　　connect、resource和dba三个角色的权限

grant resource,connect to tester;--给新用户授权，赋予用户拥有connect、resource角色的权限

connect：提供了登录和执行基本函数的能力。可以链接数据库以及在这些表中进行对数据的查询、插入、修改、以及删除的权限；

Connect 角色，是授予最终用户的典型权利，最基本的权利，能够连接到Oracle数据库中，并在对其他用户的表有访问权限时，做SELECT、UPDATE、INSERTT等操作。
Alter session--修改会话；
Create cluster--建立聚簇；
Create database link--建立数据库连接；
Create sequence--建立序列；
Create session--建立会话；
Create synonym--建立同义词；
Create view--建立视图。
 

resource：建立对象的能力；

Resource 角色，是授予开发人员的，能在自己的方案中创建表、序列、视图等。
Create cluster--建立聚簇；
Create procedure--建立过程；
Create sequence—建立序列；
Create table--建表；
Create trigger--建立促发器；
Create type--建立类型。

DBA角色，是授予系统管理员的，拥有该角色的用户就能成为系统管理员了，它拥有所有的系统权限。

　　revoke select,insert on s_dept from 用户名; 回收权限

revoke create table from zhangsan;

　　练习：

--创建用户: CREATE USER 用户名 IDENTIFIED BY 密码;
 CREATE USER test IDENTIFIED BY 123456;
 
--打开一个新的窗口使用test用户登录，发现其没有session权限无法进行登录，此时要进行授权
 GRANT CREATE SESSION TO test;
--将创建SESSION权限给test用户，之后该用户可以正常登录，但是其没有创建表的权限
 
/*Oracle中可以将多个权限定义成一组角色，分配该角色给用户即可
在Oracle中主要提供了两个角色：CONNECT、RESOURCE，将这两个角色赋予test用户*/
 GRANT CONNECT,RESOURCE TO test;
 
--管理员对用户密码进行修改：
 ALTER USER test IDENTIFIED BY hello;
--在一般系统中，在用户进行第一次登录时可以修改密码，可以使用如下方式
ALTER USER 用户名 PASSWORD EXPIRE;
 ALTER USER test PASSWORD EXPIRE;
--这时会提示用户输入旧口令及新的密码(sqlplus下执行)
 
--锁住用户和对用户解锁
 ALTER USER test ACCOUNT LOCK;
 ALTER USER test ACCOUNT UNLOCK;
 
--此时，想查询SCOTT用户下的表EMP，发现没有权限，执行如下命令即可
 GRANT SELECT,DELETE ON scott.emp TO test;
 
--收回权限的命令：
 REVOKE SELECT,DELETE ON scott.emp FROM test;
 
 --上述执行需在管理员sys权限下操作，否则提示权限不足

　　

笔记