Oracle学习笔记

Oracle数据库学习笔记2009-04-16 21:25Oracle数据库学习笔记 
 
-------12-8--SQL语句-------------------------------------
创建数据库
create database <数据库名字>;

删除数据库
drop database <数据库名字>;

创建表
create table <表名>
(
列名1 类型 约束(primary key DEFAULT 'nullName' NOT NULL)，
列名2 类型 约束，
列名3 类型 约束，
其他
);

删除表
drop table <表名>;

复制表
create table <表名> as select * from <参照表表名>；
create table <表名>(ID,Name) as select xxID,Name from <参照表表名>；
create table <表名> as select * from <参照表表名> where 1=0；     # 只复制表结构

约束
check约束
constraint check_xx_Age check(age>18 and age<40),
主键约束
constraint PK_xx PRIMARY KEY(studentID)
外键约束
constraint FK_xxx FOREIGN KEY(FK_studentID) REFERENCES xx(Studentid)

添加列
alter table 表名 add 列名 类型 约束;
删除列
alter table 表名 drop column 列名;
更改列
alter table 表名 modify 列名 类型;
约束
alter table 表名 drop constraint PK_xx;
alter table 表名 add constraint PK_xx primary key(studentID,scort);

创建索引
create index 索引名 ON 表名(创建索引的列名);

查看数据库中的表
select * from cat;

查看表结构
desc 表名;

查寻
select * from 用户名.表名;        # 访问非本用户创建的表
select * from 表名;
select * from 表名 where 条件;
select * from 表名 where between 条件 and 条件;
select * from 表名 where 条件 and 条件;
select * from 表名 where 条件 or 条件;
select * from 表名 where 列名 in (值1，值2);
select * from 表名 where not 条件;
select distinct 列名 from 表名;     # 去处相同的数据

select * from where 列名 like 'a%'   # 以a开头的行       %-通配符,多个字符,可以为空
    like '__a' # 第三个字母是a的行 _-通配符,单个字符
    is null     # 是否为空有 is null

别名
select 列名1 as 列别名1,列名2 as 列别名2 from 表名 表别名；

可以在列名上做运算
select '姓名：'||' '||列名 from 表名；       # mysql用‘+’
select 列名*1.1 from 表名；

排序
select * from 表名 order by 列名1，列名2 desc;

添加数据
insert into 表名(列名1,列名2) values(列1的值,列2的值);
删除数据
delete from 表名 where 条件；
truncate table 表名;
更新数据
update 表名 set 列名1 = 值1，列名2 = 值2 where 条件

回滚
rollback;

--------12-11--数据分组与汇总---------------------------------
聚合函数
count
select count(*) from 表名;                  # 计算所有行,包括NULL值
select count(列名) as 别名 from 表名;     # 计算该列的非NULL值的行
select count(列名1),count(列名2) from 表名; # 计算这2列的非NULL值的行
select count(distinct 列名) from 表名;      # 不算相同行
sum
select sum(列名) as 别名 from 表名;        # 计算该列的非NULL值的行
select sum(列名1),sum(列名2) from 表名;    # 计算这2列的非NULL值的行
select sum(distinct 列名) from 表名;      # 不算相同行
同样的还有以下几个
avg
max
min

分组
select count(*) from 表名 group by 列名;
select 列名A,count(*) from 表名 group by 列名A;

select 列名A,count(*) from 表名 where 列名B的 group by 列名A;
执行顺序where→group by(分组)→count(合计函数);
having
select studentID from studentexam group by studentID having avg(mark)>70;

执行顺序where→group by(分组)→count(合计函数);
编写顺序select→count(合计函数)→from→where→group by→having→order by

select * from 表名 limit 1,3; # 1行后3行
select * from 表名 where rownum<3;# rownum只能用＜ rownum是语句结束后的行

双引号 大小写敏感
单引号 表示字符串

--------12-12--函数---------------------------------
dual 是Oracle 的一个虚表 主要作用是补全语法

处理字符串
select length('aaaaa') from dual; #字符串长度
select ascii('a') from dual;   #ASCII值
select ascii('aaaaa') from dual; #第一个字符的ASCII值

select 'siyn'||ltrim(' siyn ')||'siyn' from dual; #去除左边的空格
select 'siyn'||rtrim(' siyn ')||'siyn' from dual; #去除右边的空格
select 'siyn'||trim(' siyn ')||'siyn' from dual;   #去除左右两边的空格
处理数字
select floor(列名) from 表名;      # 小于该值的最大整数
select ceil(列名) from 表名;       # 大于该值的最小整数
select round(列名,精度) from 表名; #四舍五入为指定精度的数
select power(2,3) from dual;       #2的3次方
处理时间
select current_date from dual;      #获得当前时间
select current_timestamp from dual; #获得当前时间戳
select to_char(sysdate,'yy-mm-dd hh:mi:ss') from dual;   # 格式输出,12小时制
select to_char(sysdate,'yy-mm-dd hh24:mi:ss') from dual; #24小时制

处理NULL值
select nvl(列名,值0) from 表名; #如果列值为NULL就返回 值0
select nvl2(comm,值1,值0) from emp; #如果列值为NULL就返回 值0 否则返回 值1

其他函数
select user from dual;                   #返回当前用户
select uid from dual;    #返回当前用户ID
select userenv('language') from dual;    #返回当前用户环境(语言环境)

--------12-12--组合查询---------------------------------
子查询
create temp1
(
name varchar2(30),
sal int,
p    int
);

insert into temp1(name,sal,p) values('a',10000,10);
insert into temp1(name,sal,p) values('b',20000,10);
insert into temp1(name,sal,p) values('c',30000,10);
insert into temp1(name,sal,p) values('d',10000,11);
insert into temp1(name,sal,p) values('e',20000,11);
insert into temp1(name,sal,p) values('f',10000,12);

查询各个部门工资最高的员工及工资
select name,sal from temp1 where sal in(select max(sal) from temp1 a group by p);