Oracle-基本SQL语句

数据类型：

字符型数据

      VARCHAR2：可变长度的字符数据，使用数据库字符集存储；通常设置该字符类型时会指定相应的长度，例如varchar2(100)，这里需要注意的是后面的100表示100字节，即100byte，这在保存数字或者英文时没有什么区别，但是保存汉字时，如果数据库字符集是GBK，那么该列可以保存100/2=50个汉字，如果是UTF-8，该列可以保存100/3=33个汉字。数据库字符集是数据库创建时设置的，通常不能更改。

      NVARCHAR2：这个和VARCHAR2一样，区别在于该类型使用国家语言字符集存贮；国家语言字符集可以认为是为oracle选择的附加字符集，这通常是为了增强Oracle字符处理能力；通常使用这种字符集存储的数据类型使用频率不高。

       CHAR：和VARCHAR2的区别在于是定长类型，同样保存在数据库字符集中，如果保存的数据长度没有达到设定的长度，则会使用空格填充。

 数字型数据  

        NUMBER：数字数据，可以指定它的精度和小数位数；通常格式NUMBER ( precision, scale) ，precision表示精度(1-38)，指的有效数字的位数，scale表示小数位数(-84-127)。

        FLOAT：一个ANSI数据类型，精度为126位二进制、38位十进制的浮点数。另外Oracle还提供BINARY_FLOAT和BINARY_DOUBLE作为备选。

        INTEGER：存储整型数据，小数位数为0，相当于NUMBER(P,0)。

---

日期型数据

        DATE：存储日期类型，可以精确到秒，通常存储格式是年月日时分秒；常用。

        TIMESTAMP：和DATE相比，这个可以精确到纳秒，存储时间精度更高；常用。

        TIMESTAMP WITH TIMEZONE：和TIMESTAMP一样，只不过可以在设置时候指定时区。

        INTERVAL YEAR TO MONTH：记录两个DATE或TIMESTAMP之间的以年和月的时间间隔。

        INTERVAL DAY TO SECOND：记录两个DATE或TIMESTAMP之间的以天和秒的时间间隔。

---

大对象数据类型

        CLOB：保存在数据库字符集中的字符数据，大小没有限制；

        NCLOB：作用和CLOB一样，但是保存在国家语言字符集中；     

---

RAW和ROWID数据类型

        RAW：可变长度的二进制数据；

        ROWID：指向表中行的位置的指针，他里面是物理地址，是Oracle专有数据类型。

表的创建：

create table student(

       id number,

       name varchar2(64),

       sex char(2),

       birthday date,

       score number(4,2),

       resume clob

);

数据表的修改
添加列：

alter table 表名 add(新的列名 列的类型);

alter table 表名 add(新的列名 列的类型，新的列名2 列的类型2);

删除列：

alter table 表名 drop column(列名);

alter table 表名 drop (列名，列名2);--删除多列不能添加column

修改字段的名称：

alter table 表名 rename column 旧列名 to 新列名;

修改字段的类型：

alter table 表名 modify(列名 列的新类型);

alter table 表名 modify(列名 列的新类型，列名2 列2的新类型);

修改表名

rename 旧表名to 新表名;

删除表

drop table 表名;

INSERT
插入的数据应与字段数据类型相同。
数据的大小应该在规定范围之内。
在values中列出的数据位置必须与被加入的列的排列位置相对应。
字符和日期型数据应包含在单引号中。
插入空值，不指定’’或给null
insert into stu values(1,'ouyang','男');

insert into stu(id) values(1);

insert into stu values(1,null,’’);

 

UPDATE
update 表名 set 列名=表达式 {+}  where 条件

 

update语法可以使用新值更新原有表行中的各列。

set子句指示要修改哪些列和给与哪些值。

where子句指定更新哪些行。如没有where子句，则更新所有的行（特别小心）。

DELETE
delete from 表名where 条件

truncate table 表名;删除所有记录，不写日志，速度快，无法恢复。

COMMIT和ROLLBACK
commit 提交

rollback 回滚

比较运算符

> 大于

< 小于

>= 大于等于

<= 小于等于

=  等于

!= 不等于

<>不等于

SELECT
select * from表名 where 条件

select 列名 from 表名 where 条件

 

select 指定查询哪些列的数据。

column指定列名

 

使用scott用户下的三张表：dept，emp，salgrade进行查询演示。

基本查询语句 select [distinct] *|[column 1，column 2 ……] from表名 [where (条件)]
示例：

查询所有列。      select * from emp;

查询指定列。     select distinct job from emp;

不显示重复列。   select distinct job from emp;

查询中使用算术表达式 select sal*12 from emp;
查询中使用函数 select sal*12+nvl(comm,0)  from emp;
说明： NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身。

拼接字符串select ename||'是一个'||job from emp;
说明：在查询时，如果希望将多列拼接起来作为一个字符串可以使用||

where子句的用法
查询工资高于3000的员工

select * from emp where sal>3000;

 

查询1982年1月1日之后入职的员

select * from emp where hiredate>to_date('19820101','yyyymmdd');

 

显示工资在2000和2500之间的员工信息

select * from emp where sal>=2000 and sal<=2500;

between  and  在两者之间
select * from emp where sal between 2000 and 2500;--闭区间

select * from emp where sal not between 2000 and 2500;

in -not in
select * from emp where ename in ('ALLEN','WARD','MARTIN');

select * from emp where ename not in ('ALLEN','WARD','MARTIN');

使用like模糊查询
%表示0到多个字符

_表示一个字符

select * from emp where ename like '%ITH';

select * from emp where ename like '_ITH';    --没有数据

select * from emp where ename like '_MITH';

select * from emp where ename not like '%LEN';  --不常用

is null的用法
显示没有上级的员工

select * from emp where mgr is null;

使用逻辑运算符
select * from emp where mgr is null or comm is null;

select * from emp where mgr is null and comm is null;

增加排序功能order by
select [distinct] *|[column 1，column 2 ……] from表名 [where (条件) order by 列名 ]

 

select * from emp order by sal;--默认升序

select * from emp order by sal asc;--升序

select * from emp order by sal desc;--降序

select * from emp where job = 'CLERK' order by sal desc;--降序

select * from emp where job = 'CLERK' order by empno,sal desc;--按照员工号升序工资降序

select * from emp order by 8;--使用数字排序  按照表列的顺序

select empno,ename,deptno from emp order by 3;--使用数字排序  按照查询列的顺序

 

别名排序
select sal*12+nvl(comm,0)  yearSal  from emp order by yearsal;

 

注意：

oracle 关键字，列名，表名等都不区分大小写，但是表中的数据是区分大小写的。

 

扩展：

select * from user_tables;  查询用户下的所有表

select * from dba_users;--查询所有用户信息，使用管理员用户

 

 

DML,DDL,DCL
DML（data manipulation language）：

它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

DDL（data definition language）：

DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用

DCL（Data Control Language）：

是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

 

统计函数
max，min，sum，avg，count

查询员工数量

select count(*) from emp;

查询有奖金的人

select count(comm) from emp;  --空值不统计

查询最高工资。

select max(sal) from emp;

查询最低工资。

select min(sal) from emp;

查询最高年薪。

select max((sal+nvl(comm,0))*12) from emp;

查询平均工资。

select avg(sal) from emp;

select avg(comm) from emp;--奖金不一定有，没有的不参与

查询总工资。

select sum(sal) from emp;

查询真正的平均工资

select sum(sal)/count(*) from emp;

 

group by  和 having子句
group by 列名 用于分组

having 表达式  针对于一组数据，譬如求

  

两者一般结合使用，单独使用也可以。

按照部门分组查询部门平均工资和最高工资

select avg(sal),max(sal),deptno from emp group by deptno;

select avg(sal),max(sal),deptno from emp;--错误 不是单组分组函数

 

查询部门不同岗位的平均工资和最高工资

select avg(sal),max(sal),deptno,job from emp group by deptno,job;

 

查询部门不同岗位的平均工资和最高工资并按照部门和岗位排序。

select avg(sal),max(sal),deptno from emp group by deptno,job order by deptno,job;

 

查询部门平均工资低于2000的部门的平均工资和最高工资。【典型用法】

select avg(sal) ,max(sal),deptno from emp group by deptno having avg(sal)<2000;

 

select avg(sal) ,max(sal),deptno from emp  where avg(sal)<2000 group by deptno; --错误，此处不允许使用分组函数

 

单独使用having

select avg(sal) ,max(sal),deptno from emp group by deptno having 1>2;--扩展 特殊形式

 

为什么不能使用having替代where

select * from emp having avg(sal)>20;--不是group表达式

 

当同时使用group by   having   order by  的顺序问题

推荐使用顺序 group by   having  order by

查询部门工资低于2000的部门不同岗位的平均工资和最高工资并按照部门和岗位排序。

select avg(sal),max(sal),deptno from emp group by deptno,job having avg(sal)<2000 order by deptno,job;--推荐写法

 

select avg(sal),max(sal),deptno from emp having avg(sal)<2000  group by deptno,job order by deptno,job;--不推荐写法

 

select avg(sal),max(sal),deptno from emp order by deptno,job having avg(sal)<2000  group by deptno,job ;--错误写法

 

其他常用语句
select * from all_tables where owner = 'SCOTT';--查询scott用户下的所有表

select * from user_tables;--查询当前用户的所有表