随笔二、oracle基本用法与语法

小型数据库 access

中型数据库mysql,sql sever

大型数据库 负载级别sysbase<oracle<db2 安全性高 昂贵

客户端： a)、安装数据库客户端软件

     b)、配置本地服务(即tns文件),用于远程连接数据库,需要的配置信息有：(必须掌握)

       数据库服务器的IP(或主机名)

       全局数据库名称(也即sid)

       监听端口(默认是1521)

       登陆数据库还需要用户名和密码

一：对表结构操作；

1.=创建表结构：

create table 表名(

     列名1 数据类型 [primary key],

     列名2 数据类型 [unique],

     列名3 数据类型 [not null | null],

     列名4 数据类型 check(条件),

     列名5 数据类型 [references 表名(列名)]

     ...

     列名N 数据类型 [default 默认值] [各种约束]

);

列名命名规则：字母、数字、下划线(_)组成，不要以数字开头  

数据类型：在Oracle中有很多数据类型，我们需要掌握三种：字符串、数字、日期

数字：number可以表示整数，也可以表示小数number(5,2)一个数为5位数的有效数，2位小数-999.99~999.99 numbe(5) -99999~99999

日期：date

字符串：char、varchar2，在Oracle中，字符串用单引号引起来，用双引号引起来是别名

       char是定长，如char(12)，存储内容的最大长度是12个字节，若实际存储内容只有6个字节，该列仍然占用12个字节char(10) '小明'

       varchar2是可变长，如varchar2(12)，存储内容的最大长度是12个字节，若实际存储内容只有6个字节，该列仍然占用6个字节

       字节：字节是由二进制代码组成，二进制代码就是0和1组成，8个二进制位组成1个字节，即1B = 8b

       字符：是由字节经过特定的编码方式组成，编码就相当于一个翻译官，不同的翻译官所掌握的语言也不一样，UTF-8是掌握了8国语言的翻译官，即UTF-8是国际通用字符集数据库服务器可以使用不同的翻译官(即使用不同的字符集)若数据库使用GBK或GB2312做为翻译官，那么1个中文字符占2个字节若数据库使用UTF-8做为翻译官，那么1个中文字符占3个字节建议最保守的做法是：1个中文字符占3个字节对于英文字符来说，一个字符占用1个字节。

约束：

    唯一约束(unique)：值不能重复，但是可以为null。属于表对象(跟列一样)，必须要有名字，若没有指定名字，则Oracle随即分配一个唯一的名字。

    非空约束(not null)：值不能为空，一个列默认是可以为空。

    默认值(default)：当没有给列指定值的时候，则取默认值；在创建表时，若没有给列指定默认值，那么该列的默认值是空(即null)。

    检查约束(check)：检查值是否满足某个条件，属于表对象，必须要有名字。

    主键(primary key)：相当于身份证号码，包含非空约束和唯一约束，也是属于表对象，在设计一张表示，需要有主键列。

    外键(references)：也属于表对象，必须要有名字。sysdate：获取系统当前时间，格式是：年月日时分秒。

【例子】

create table t_user(

     u_id number primary key,   ---- 主键

     u_sex varchar2(12) check(u_sex = '男' or u_sex = '女'),   -----检查

     u_name varchar2(50) unique,   --- 唯一

     u_cardid varchar2(20) not null,  --- 非空

     u_birthdate date default sysdate,   ---- 默认值

     classid number references t_class(classid)   ----- 外键

);

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

3.=修改数据类型，约束：alter table表名modify(列名 数据类型，);

4.=修改列名：alter table表名rename column老列名to新列明；

5.=删除列：alter table表名drop column列名；

6.=修改表名：rename老表名to新表名；

7.=删除表结构：drop table表名；

二：对表数据操作；

数据库操作语句，常见的增、删、改、查(最重要)

事务：通俗理解就是做一件事情的过程，这个过程有两种结果：成功、失败，在数据库中发出一条dml语句即开启一个事务，开启事务就一定要结束事务，

结束事务的两种方式：

          a1)、commit，提交事务，所有的操作都生效，操作的结果直会永久保存在数据库中

          a2)、rollback，回滚事务，所有的操作都不会生效，操作的结果直接扔掉，不会永久保存到数据库中

1.=增加数据：insert into表名（列1,列2...列N）values（值1,值2...值N）；

字符串用单引号

2.=删除数据：delete from表名where过滤条件；

truncate table 表名---截断表，即删除表中所有的数据。

truncate，delete和drop的区别：

    delete：会发起事务，可以有where过滤条件(即删除指定的数据),属于dml语句，删除效率比truncate低

    truncate：不会发起事务，不能有where过滤条件(即只能删除表中所有的数据),属于ddl语句,删除效率比delete高，但有危险性

          若表中有列是其他表的外键，则不能在该表中使用truncate(即truncate会影响外键列)

    drop table 表名：即删除表的数据又删除表的结构。

3.=修改数据：update表名set列1 =值1,列2 =值2...列N =值N where过滤条件；

4.=查询数据：

select 列1,列2,列3...列N或*

from 表名

[where 过滤条件]

[group by 分组内容]

[having 组内条件]

[order by 排序方式]---  ORDER BY字段名ASC（默认升序）/DESC（降序）;

where ：按照指定的条件进行过滤，如“单值比较运算符”：>、<、=、!=或<>、<=、>=，两边的数据类型要保持一致。

distinct：对于指定的字段所选择的重复行，只返回一行。

多条件过滤之逻辑运算符:not、and、or。

多值比较运算符：(not)between值1 and值2，in，not in。

模糊查询: like。

%表示匹配任意长度的字符串

%test%:表示匹配中间字符串为test的任意长度的字符串

_表示仅能匹配一个字符

o_a:表示匹配长度为3，以字母o开头，且以字母a结尾的字符串，中间一个字符为任意字符。

is null 或 is not null：如果某字段为空，则要使用比较运算符"IS NULL"(非空使用"IS NOT NULL")，不能用"=NULL"或"!=NULL"表示。

4.1.=数据库常用函数：round（m，n），to_char(date, 'YYmmDD'),to_date('20150405','YYYYmmDD'),nvl(m, 0)。

4.2.=分组查询：

分组函数：count（*），count（字段名），min（字段名），max（字段名），avg（字段名），sum（字段名）。

如果要将各个分组再进行划分出不同的小组，则需要在"group by"子句中指定多个列名。

【例】：计算每个部门中每种工种各有多少职工数。

select deptno,job,count(*) from emp group by deptno,job;

【注】在包括"group by"子句的查询中，select子句后的列名表中，

除统计函数外，均应包含在"group by"子句中(即列名表中不能出现与分组子句无关的列名)，否则出错！

【注】1、分组函数只能出现在选择列表、having、order by子句中。

2、如果在select语句同时含有group by、having、order by那么他们的顺序是group by、having、order by。

3、在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则出错。

【注】select 后的字段，必须要么包含在group by中，要么包含在having后的聚合函数里。

1. GROUP BY 是分组查询,一般GROUP BY是和聚合函数配合使用group by有一个原则,就是select后面的所有列中,没有使用聚合函数的列,必须出现在group by 后面（重要）

有having 一定有group by。

4.3.= 多表查询：

4.3.1.=多表连接查询：

select 表别名1.列名1，表别名2.列名2....  或 *

from 表名1表别名1,表名2表别名2,...表名n,表别名n

where 表名1或表别名1.列名=表名2或表别名2.列名and表名(n-1)或表别名(n-1).列名=表名n或表别名n.列名

4.3.2.=自连接查询：

例：查找出职工号与职工的经理编号相同的职工的名字

select e.ename ,m.ename

from emp e,emp m

where e.empno=m.mgr;

注意：对表进行自身的连接时，必须使用表的别名，才能在where子句中给出正确的连接条件

4.3.3.=不等连接的查询

例：查出比"JONES"工资高的职工的工资、工种的情况

select x.sal,x.job from emp j,emp x

where j.ename= 'JONES' and j.sal < x.sal;

4.3.4.=外部连接查询(左右连接)

select 列名

from 表名1,表名2

where 表别名1.列名=表别名2.列名(+)或 表别名1.列名(+)=表别名2.列名;

select ... from table1 t1,table2 t2 where t1.id = t2.id(+); --左连接，加号在右边

select ... from table1 t1,table2 t2 where t1.id(+) = t2.id; --右连接，加号在左边

在where子句的条件中必须包含外部连接操作符"+"。当该操作符加在表的列名上时，每当该表没有行与另一表的一行连接时，Oracle将为该表列产生空值(需要哪个表产生空值，就在哪个表的字段上加上(+) )。

PS：右连接，等号右边的表为主表，等号右边的表为从表

4.3.5.=子查询：

单行值子查询：

-对于该子查询命令，系统执行时先执行子查询，返回一个值(即SMITH的部门号)作为主要查询(或外层查询)的条件，然后再根据该条件执行主查询选择出预期的结果，即与“SMITH”同一部门的所有职工。通常，主查询与子查询之间用"="、">"等比较运算符进行连接。

多行值子查询：

-多行子查询，是指子查询会返回多个值，此时，需要用到多值比较运算符：[NOT]IN, [NOT]ANY, [NOT]ALL(<>ALL的意思)

4.3.6.=多层嵌套查询：

在查询命令的where子句中可以包含所规定的条件与任意多个子查询的组合作主查询的条件，尤其是可以包含用AND和OR连接起来的条件与子查询的任意数量的组合。

例：查找出与职工JONES同工种，或者工资大于等于职工FORD的人员的有关信息，并按工种工资排序。

select * from emp

where job =(select job from emp where ename = 'JONES')

or sal>=(select sal from emp where ename = 'FORD') order by sal;

三：

1.rowid：属于表中的列，是由数据库自动增加的列，是真实存在的一个列，唯一标识一条数据的物理位置（查询语句首先得到数据的rowid，然后根据rowid到数据文件中唯一定位数据）

       因此，根据rowid查询的效率是最高的，数据在入库时(即成功插入数据)，Oracle数据就给这条数据分配一个唯一的rowid

  rownum：是给结果集增加的一个序号，不属于任何的表，先有结果集，才能有rownum，始终是从1开始编号(不能跨过1而存在)。

--rownum始终是从1开始编号(不能跨过1而存在)。--rownum不属于任何的表。

【例1】查询工资最高的前五名员工信息

select rowunm,T.* from

   (select ename, job, sal

     from emp order by sal desc) T

where rownum <= 5

【例2】查询最后五行的信息

select T.* from

    (select * from emp order by rownum desc) T where rownum <= 5;

【例3】查询最近入职的五名员工信息

select T.* from (select * from emp order by hiredate desc) T where rownum <= 5;

2.=分页查询：

select *  from (select st.*, rownum rownum1 from student st where rownum <= 20) t  where rownum1 >= 10;

select *  from (select st.*, rownum rownum1 from emp st where rownum <= (select count(*) from emp)) t

where rownum1 >= 10;

3.=视图：就是一张临时表，不能往视图中插入、修改、删除数据，只能查询数据。

4.=索引：目的：提升查询数据的效率。

5.=查询当前数据库中所有的表：

select * from all_tables； 有权限的表。

select * from dba_tables;  系统表。

select * from user_tables;  当前用户下的表。

6.=数据库备份

导入：将D:\zxcc.dmp中的数据导入zxcc数据库中。

   imp kf/zx@zxcc file=D:\zxcc.dmp

导出：将数据库zxcc完全导出，用户名kf密码zx导出到D:\zxcc.dmp中

   exp kf/zx@zxcc file=d:\zxcc.dmp full=y

7.2个结果集(A,B)查询

  intersect  交集AnB

  union  并集AUB（去重）

  union all 并集AUB（不去重）

  minus 减，A -(AnB)

8.decode函数的用法；

【例】学生表student，现在要用decode函数实现以下几个功能：

成绩>85，显示优秀；>70，显示良好；>60，显示及格；否则不及格。

select id ,decode(sign(score-85),1,'优秀',0,'优秀',-1,

decode(sign(score-70),1,'良好',0,'良好',-1,

decode(sign(score-60),1,'及格',0,'及格'-1,'不及格)))

 from student;