oracle中只想部分,最常用SQL快速参考（其中某些部分只适合Oracle）

关系型数据库模型由Codd博士在1970年提出

SQL成为ANSI标准是在1986年

最基本查询

select * from table1 where name=’ Lincoln ’;

select col1,col2 from table1;

无重复查询

select distinct amount from checks;

(distinct在一个语句中只能用一次，放在所有字段之前)

MySQL的一些常用命令

show databases;显示所有数据库

show tables;显示当前数据库的表

use db1;当前数据库切换到db1

describe table1;显示table1表的表结构

运用表达式和别名

select item, wholesale+0.15 from price;

select item, (wholesale+0.15) retail from price;

select * from friends where state<=’la’;

select * from friends where firstname<>’al’;

select * from parts where location like ’%back%’;

select * from friends where st like ’C_’;

select firstname||lastname entirename from friends; (mysql中不能用)

(mysql可以用select concat(name1, ’ ’, name2) name from friends;)

select lastname from vacation where years<=5 and leavetaken>20;

select * from vacation where lastname not like ’B%’;

集合运算，MySQL不一定能用

select name from softball union select name from football;合集

select name from softball union all select name from football;全合集

select * from football intersect select * from softball;交集

select * from football minus select * from softball;差集

In与Between运算符

select * from friends where areacode in(100,381,204);

select * from price where wholesale between 0.25 and 0.75;

内置函数的使用

count求总数

sum求总和

avg求平均

max求最大

min求最小

variance标准方差

stddev标准差

add_month给日期类型增加一月

add_date给日期类型增加一天

last_day返回月份的最后一天

month_between日期相差的月份

sysdate系统时间

abs绝对值

ceil“天花板”

floor“地板”

exp指数函数

log上面的反函数

powa^b

sign正1零0负-1

sqrt根号

chr转换成字符

concat连接字符串

initcap首字符大写，其他小写

lower小写

upper大写

lpad左填充，原始，长度，字符

rpan右填充

ltrim左剪切

rtrim右剪切

trim剪切

replace字符替换，原始，替换，替换成

substr获取子字符串，原始，起始，长度

instr查找字符串，原始，查找，开始，序号

length字符串长度

to_char转换成字符串

to_number转换成数字

子句的运用(Where, Group by, Order by, Having)

select * from checks where amount>100;

select * from checks order by check;可以加上asc代表升序

select * from checks order by payee desc;

select * from checks order by remarks, payee;

select * from checks order by 1;

select payee, sum(amount), count(payee) from checks group by payee;

select sum(amount), count(payee) from checks group by payee, remarks;

where之句中不允许用合计函数，因此下面的语句是错误的。

select team, avg(salary) from orgchart where avg(salary)<3800 group by team;

这时候需要having子句：

select team, avg(salary) from orgchart group by team having avg(salary)<3800;

……having team in(’pr’,’research’);

交叉连接(笛卡尔积)

select * from table1, table2;

select o.orderedon, o.name, o.partnum, p.partnum, p.description

from orders o, part p

where o.partnum=p.partnum;

内部连接

select p.partnum, p.price, o.name, o.partnum

from part p inner join orders o on orders.partnum=54;

除了使用了“on”来代替“where”之外，和交叉连接没多少区别。

外部连接

左连接：左边的表全部显示

select p.partnum, p.description, p.price, o.name, o.partnum

from part p left outer join orders o on o.partnum=54;

右连接：右边的表全部显示

select p.partnum, p.description, p.price, o.name, o.partnum

from part p right outer join orders o on o.partnum=54;

子查询(MySQL不支持)

select * from orders

where partnum=(select partnum from part where description like “ROAD%”);

下面是较复杂查询示例：

select o.name, o.orderedon, o.quantity * p.price total

from orders o, part p

where o.partnum=p.partnum and o.quantity * p.price >

(select avg(o.quantity * p.price)

from orders o, part p

where o.partnum=p.partnum);

Exist使用(判断集合是否存在)

select name, orderedon from orders where exists

(select * from orders where name=’Mostly harmless’)

数据库三范式

1、数据集合分成多张表而不只是一张大表，分成的每张表都有主键；主键

2、找出仅仅依赖于主键的列，将其存储在另一个表中；仅依赖抽出

3、从一个表中删除不依赖于主键的列。不依赖抽出

缺点：降低性能。

建立数据库

很难吧？很难！不难吧？不难。仅仅告诉你最简单情况：

create database PAYMENTS;

建立表

关于数据类型请参考《Oracle数据类型.doc》。

create table bills(name char(30), amount number, account_id number);

create table empname(id number not null, ename char(30));

create table newtable as select * from oldtable;

改变表：增加列

alter table emp add new_col_name char(20);

改变表：修改列

alter table emp modify new_col_name char(21);

改变表：改列名(Oracle 9i Release 2才能用)

alter table emp rename column new_col_name to old_col_name;

改变表：删除列

alter table emp drop column old_col_name;

删除表

drop table tablename;

删除数据库

drop database databasename;

主键、非空、唯一约束

主键primary key

非空not null

唯一unique除了排序，和primary key功能一致

create table emp

(emp_id char(9) primary key,

emp_name varchar2(40) not null,

phone number(10) null unique);

外键约束foreign key

create table emp_pay

(emp_id char(9) not null,

position varchar2(15) not null,

pay_rate number(4,2) not null);

alter table emp_payadd constraintemp_id_fkforeign key(emp_id)

references emp(emp_id);

校验约束check

create table emp

(emp_id char(9) not null primary key,

emp_name varchar2(40) not null,

emp_rate number(4,2) not null,

zip number(5) not null);

alter table empadd constraintchk_zipcheck(emp_zip = '46234');

alter table empadd constraintchk_zipcheck(emp_zip in ('47634', '13451'));

alter table empadd constraintchk_zipcheck(emp_rate < 12.5);

emp_rate为校验约束名。

删除约束

alter table emp drop constraint emp_no_constraint;

emp_no_constraint为约束名。

更新记录

update collection set worth = 555, price = 666 where itemid = 1110;

删除记录

delete from collection where itemid = 1113;

事务处理

……

commit;

……

rollback;

……

savepoint save_it;

……

rollback to savepoint save_it;

日期时间

掌握两个函数就可以了。

to_char(empdate, 'YYYY/MM/DD HH24:MI:SS');

将date转变成字符串：“2004/11/12 16:30: 02 ”

to_date('1981/11/12 00:03:16', 'YYYY/MM/DD HH24:MI:SS');

将字符串“1981/11/12 00:03: 16 ” 转变成日期类型。

还有要注意的事项，在中文Oracle中，'26-JAN-03'并不被认为是合法的日期，

'26-1月-03'才是合法的，真别扭，也蛮郁闷的。

建立视图

create view debts as select * from bills;

删除视图

drop view debts;

建立索引

create index empno_index on emp(empno);

删除索引

drop index empno_index;

创建用户

create user jguogang identified by mypassword;

授予用户角色

grant connect to jguogang;

grant resource to jguogang;

grant dba to jguogang;

删除用户的角色

revoke resource from jguogang;

给予系统特权

grant alter any type to public;

其中“alter any type”为一种系统特权，将被授予全部用户。

grant drop any trigger to jguogang;

将系统特权“drop any trigger”授予用户“jguogang”。

grant create user to connect;

将系统特权“create user”授予角色“connect”。

收回系统特权

revoke alter any type from public;

给予对象特权

grant select on emp to jack;

授予用户“jack”：对“emp”的“select”对象特权。

grant select, update(salary) on emp to jill;

授予用户“jill”：对“emp”的“select”、“salary”列的“update”对象特权。

收回对象特权

revoke select on emp from jack;

字典：我是谁

select * from user_users;

字典：我们是谁

select * from all_users;

字典：我能干什么

select * from user_sys_privs;

字典：我充当什么角色

select * from user_role_privs;

字典：我拥有什么表、视图和“SEQUENCE”

select * from user_catalog;

字典：我可访问什么表、视图和“SEQUENCE”(多)

select * from all_catalog;

字典：我拥有什么对象

select * from user_object;

字典：我可访问什么对象(多)

select * from all_catalog;

……关于字典，暂时就介绍那么点了。

表的注释

comment on table emp is 'Employee';

列的注释

comment on column emp.ename is '名字';

列出所有包含注释的表

select * from user_tab_comments where comments is not null;

系统时间取得

select sysdate from dual;

20050906笔记

查看未提交的事务

select * from v$transaction;

select * from v$locked_object;

通过locked_object视图获知被锁定的对象的ID(object_id)

那么通过下面的语句能获知到底哪个对象被锁定

select * from sys.all_objects t where object_id = ###;

alter systemkill session 30;

修改用户密码相关

select username,password from dba_users;

alter user aaa identified by aaaspwd;