oracle-2-sql数据操作和查询

主要内容：

》oracle 数据类型

》sql建表和约束

》sql对数九的增删改

》sql查询

》oracle伪例

1、oracle的数据类型

oracle数据库的核心是表，表中常见的数据类型有：

char(length):存储固定长度的字符串，length默认1，最长2000

varchar2(length):存储可变长度的字符串，length默认长度是1，最大4000

number（p,s）存储浮点数或者是整数，p表示的是最大位数（包含小数点），p默认是38，s表示小数位数

date:存储日期和时间

timestamp;存储日期的年月日，时分秒，以及秒后面刘伟，同时包含时区

clob ; 存储大的文本，比如非结构化的XML文档

BLOB 存储二进制对象，如图形，视频，声音等

下面对上面的部分数据类型举例：

（1）number

格式             输入的数字        实际的存储

number         2.3334　　　　2.3334

number（5，2）4.223          4.22

number(5,2)     4.3333334　　输入的数字超过了指定的精度，数据库不能存储

对于日期类型，可以使用sysdate的内置函数可以获取当前的系统日期和时间，返回date类型，用systimestamp函数可以返回当前日期、时间和时区

SQL> select sysdate,systimestamp from dual;

SYSDATE
---------
SYSTIMESTAMP
---------------------------------------------------------------------------
20-APR-16
20-APR-16 10.45.35.192191 AM +08:00

注：oracle在查询的时候当查询单行函数的时候，from 后面使用dual 表，dual表在系统中只有一行一列，该表在输出单行函数的时候是为了select语法的完整性而使用的。

 

2》创建表和约束

代码演示

 SQL> create table yizheninfos
  2  (
  3  stuid varchar2(7) not null,
  4  stuname varchar2(10) not null,
  5  gender varchar2(2) not null,
  6  age number(2) not null,
  7  seat number(2) not null,
  8  enrolldate date,
  9  stuaddress varchar2(50) default '地址不详',
 10  classno varchar2(4) not null
 11  )
 12  /

Table created.

SQL> alter table yizheninfos add constraint PK_yizhen primary key(stuid)
  2  /

Table altered.----->这条sql语句的作用是创建一个主键约束,下面的其他sql语句的作用就是创建各种check 约束

SQL> alter table yizheninfos add constraint ck_yizhender check(gender = '男' or gender = '女')
  2  /

Table altered.

SQL> alter table yizheninfos add constraint ck_yizhent check(seat >= 0 and seat <= 50)
  2  /

Table altered.

SQL> alter table yizheninfos add constraint ck_yizhenssno check(classno > '1001' and classno <='1999' or
  2  (classno >='2001' and classno <='2999'))
  3  /

Table altered.

SQL> alter table yizheninfos add constraints un_stuname unique(stuname)
  2  /

Table altered.----》这条sql语句的作用就是创建一个唯一约束，表示该列值是唯一的，列中的值不能重复。

代码解析：

在oracle中，“/” 代表的是执行缓存区中的语句，由于缓冲区中只存储一条刚刚保存过的语句，由于每条语句没有用分号结尾，只是保存在缓冲区，因此每条语句后面都有单独一行“/”

 

现在来举例展示一下外键的约束定义

SQL> create table scores
  2  (
  3  id number,                             ----》ID
  4  term varchar2(2),                   ----》学期为s1或s2
  5  stuid varchar2(7) not null,       ----》学号
  6  examno varchar2(7) not null,   ----》考号
  7  writtenscore number(4,1) not null,  ----》笔试成绩
  8  labscore number(4,1) not null         ----》机试成绩
  9  )
 10  /

Table created.

SQL> alter table scores
  2  add constraint ck_scores_term check(term = 's1' or term ='s2')
  3  /

Table altered.

SQL> alter table scores
  2  add constraint fk_scores_yizheninfos_stuid foreign key(stuid) references yizheninfos(stuid)
  3  /

Table altered.---->这就是外键的约束定义

 

4.数据操作语言（DML）

DML用于对数据库的表中数据进行添加，修改，删除和select....for ,update等

 

》》4.1简单查询

数据查询是用select 命令从数据库的表中提取信息

select 列名  from  表名  where  条件  order by 列名

注：order by 要求在查询的结果中排序，默认是升序

 

》》4.2根据查询结果创建表

create table  表名  as  select 语句

代码演示

SQL> create table yizheninfosss as select * from yizheninfos ;

SQL> create table yizheninfossssss as select * from yizheninfos where 1=2;
第一条命令不存在认得的约束，并且把查询结果一起插入到新表中

第二条命令只是复制一个表结构，没有任何的数据（因为1=2的这个条件不成立）

 

》》4.3  数据的插入----insert

insert into 表名（列1，列2，列3......） values (值1，值2，值3.....)

语法解析：

（1）这里的列名是可以省略的，当省略列名的时候，默认是表中的所有列名，列名顺序为表定义中列的先后手顺序。

（2）这里的值的数量和顺序要和列名的数量和顺序一致，值的类型和列名的类型一致。

 

在 Oracle 中，日期是国际化的，不同的区域安装的数据库，默认的日期格式不同，

因此为了程序便于移植，日期的输入要使用 TO_DATE 函数对日期格式化后输入，采
用格式化字符串对日期进行格式化时，格式化字符串中字符不区分大小写，常见的
格式化字符如下：
1. yyyy 表示四位年份
2. mm 表示两位月份，比如 3 月表示为 03
3. dd 表示两位日期
4. hh24 表示小时从 0-23， hh12 也表示小时从 0-11。
5. mi 表示分钟
6. ss 表示秒
③ 在遇到存在默认值的列时，可以使用 default 值代替。
④ commit 是把用户操作（添加、删除、修改操作）提交，只有提交操作后，数据才
能真正更新到表中，否则其他用户无法查询到当前用户操作的结果。
在 Oracle 中，一个 INSERT 命令可以把一个结果集一次性插入到一张表中。使用的语句
是： INSERT INTO

比如说：

insert into info infos2 select * from yizheninfos;

这种情况，infos2和yizheninfos这两张表中的数据类型必须每一类的数据类型一致，这样就可以把yizheninfos表中的数据全部的插入到infos2这个表中

（这里也可以再后面的select 语句中添加条件，把符合一定条件的内同插入到infos2这个表中）

》》4.4更新数据

语法结构：

update  表名  set   列名1=值1，列名2=值2.....where  条件

这里执行完update,或者insert等之后需要执行commit 操作，具体原因参考：http://www.cnblogs.com/smail-bao/p/5431193.html

 

》》4.5 删除数据

删除数据的语法是：

delete  from 表名 where 条件

还有一条删除命令       truncate

truncate 命令可以把表中的所有数据一次性全部删除

语法结构：

truncate table   表名

truncate  和  delete  都可以把表中的数据全部删除，他们的区别是：

（1）truncate 是 DDL命令，删除的数据不可恢复，delete命令是ＤＭＬ命令，删除后的数据可以通过日志文件来恢复

（２）如果一个表中的数据记录了很多，truncate 相对delete命令速度快（确定没用的情况下，慎用，慎用。。。。。）

 

5.操作符

》》算术运算

》》关系运算

》》逻辑运算

》》5.1 算术运算

oracle 的算术运算只有+，—，*，/这四个，其中的除号“/”的结果是浮点数，其余的运算只能借助函数，比如说

MOD（x,y）返回的是x除以y的余数

 

》》5.2关系运算和逻辑运算

oracle 中where 字句经常见到关系运算和逻辑运算，常见的关系运算有：=   、 <   、 > 、 !=等 

逻辑运算符有三个，and、or、not

》》5.3字符串连接操作符（||）

在oracle中，字符串的连接用   ||   表示

注：oracle中字符串可以用单引号，也可以用双引号，当别名中存在空格的时候，必须用双引号，在表名、列名是用双引号

 

6.高级查询

 》》6.1消除重复行

在查询中如果出现若干行相同的情况，那么可以使用distinct 消除重复行

语法：select distinct  stuname from EMP;

 》》6.2NULL操作

如果某一条记录上缺少数据值，就是控制（NULL值），空值不等于0或者空格，空值是指未赋值，未知或者是不可用的值，任何数据类型的列都可以包括

NULL值，除非该列被定义为非空或者是主键

 条件查询的时候如果要查ＮＵＬＬ值的则：．．．．ｗｈｅｒｅ　ｎａｍｅ　ｉｓ　ｎｕｌｌ

 》》6.３　ｉｎ操作

ｗｈｅｒｅ条件查询的时候可以使用ｉｎ操作来查询其列值在指定的列表中的行

比如说：

where  job  in  （‘salesman’,'president','analyst'）

同理，也可以使用    not in,用法一样

 》》6.4  between.....and....指定的范围

比如说

where  age >20 and age < 50

也可以使用：

where age between 20 and 50

》》6.5   like   模糊查询

模糊查询结合字符匹配来检索所需要的数据行，字符匹配可以使用通配符

% ：  表示一个或者多个任意字符

_：     代表一个任意字符

注：匹配的字符串中，escape后面的“字符”作为转义字符。

比如说：

’%30\%%‘ escape '\'    ：指的就是包含%30的之歌字符串，“\”，表示的是转义字符，“\%”在字符串中表示一个字符“%”

’_s%‘:表示的是第二个字符为   s   的字符串

sql语句举例：

select  ENAME,JOB,SAL   from   yizheninfos   where  ENAME like 'B%S'

这就是找出ENAME 是以    B    开头的    S    结尾的所有ENAME,JOB,SALENAME,JOB,SAL信息

 

》》6.6 集合运算

注释：集合运算就是将两个或者多个结果集组合成为一个结果集，集合运算包括：

》intersect(交集)：

》union all(并集)：返回各个查询的所有记录，包括重复记录
》union（并集）：返回各个查询的所有记录，不包括重复记录

》minus(补集)：返回第一个查询检索出来的记录减去第二个查询检索出的记录之后剩余的记录

注：: 当使用集合操作的时候，要注意：查询所返回的列数以及列的类型必须匹配，列名可以
不同。

举例说明：

select depinfo from DEPT

minus（这里或者使用union,interset等等）

select depinfo from EMP

上面举例说的是并集

 

注：这里我们可以使用insert into .......select 把一个结果集插入到另一张表结构相同的表中，因为可以使用union把若干条查询记录一次性插入到一张表中

举例：

insert into yizheninfos

select 50,'公关部','台湾' from dual

union

select 60,'研发部','韩国' from dual

union

select 70,'市场部','香港' from dual

/

 

》》6.7连接查询

oracle  中对两个表或者若干张表之间的外联接使用   （+）   表示

内联接（inner join）

案列：查询出员工工资大于30000的员工姓名，部门，工作，工资
，部门名称在dept表中，其他的信息在emp中，需要内联接才能完成

select e.ename,e.job,e.sal,d.dname

from emp e,dept d

where e.deptid=d.deptid

and e.sal>30000

使用内连接的代码如下所示：

select e.ename,e.job,e.sal,d.dname

from emp e INNER JOIN dept d ON e.deptid=d.deptid

where e.sal>3000

这里的INNER JOIN中，关键字ＩＮＮＥＲ可以省略

 

案列：查询出每个部门下的员工姓名，工资案例分析：EMP表使用外键deptno引用Dept表中的deptno，在Dept表中如果有某些部门没有员工，那么用内联接，则没有员工的部门将无法显示，所以必须以Dept表为基准的外联接

select e.ENAME,e.JOB,e.SAL,d.DNAME

from EMP e.DEPT d

where e.DEPTNO(+)=d.DEPTNO

 

代码解析：这里的（+）表示的是Oracle专用的外联接符号，在条件中出现在左边就叫右外联接，出现在右边就叫做左外联接

上面的嗲吗也可以使用标准写法：

select e.ENAME,e.JOB,e.SAL,d.DNAME

FROM EMP e RIGHT OUTER JOIN DEPT d

ON e.DEPTNO=d.DEPTNO

关键字OUTER可以省略，和内连接差不多，推荐使用这种标准写法

 

本章学习总结：

 Oracle SQL 语句中有数据操纵语言（ DML）、数据定义语言（ DDL）、数据控制语言
（ DCL）、事务控制语言（ TCL）等等。
 DML 语句包括增删改查语句， DDL 语句包括数据库对象创建、修改和删除语句，数
据控制命令包括 GRANT、 REVOKE 等，事务控制命令有 COMMIT、 ROLLBACK 等。
 数据库中建表常用的类型有：数字类型 number （ p，s），可变字符串 varchar2 （ length），
日期 date。
 Oracle 中 default 是一个值，在 Oracle 中不存在 default 约束。
 Oracle 的增删改语句与 SQL Server 基本一致，都是使用 INSERT、 UPDATE、 DELETE
完成。
 Oracle 高级查询中要注意： DISTINCT、 NULL、 IN、 BETWEEN…AND…。
 集合操作有： UNION、 UNION ALL、 INTESECT、 MINUS。