oracle 基础

解锁用户：alert user scott account unlock 用系统管理员账号登陆数据库：conn sys/sys as sysdba conn scott/tiger 或者connect user/pwd 切换用户 ed或者edit//打开一个缓冲文件 操作回滚:rollback，一旦提交(commit)则不可回滚; 数据操纵语言dml，数据定义语句ddl，数据控制语言dcl 显示表结构：desc emp --单行注释 /* */多行注释 SQL>@d:\student.sql //执行sql文件 -------------------------------------------------- --创建雇员表emp create table emp ( empno number(4) not null ,--员工号 ename varchar2(10),--员工姓名 job varchar2(9),--工种 mgr number(4),--所属经理编号 hiredate date,--入职日期 sal number(7,2),--薪水 comm number(7,2),--奖金 deptno number(2),--部门号 primary key(empno) ); --创建部门表dept create table dept ( deptno number(2) not null ,--部门号 dname varchar2(14),--部门名称 loc varchar2(13),--部门位置 primary key(deptno) ); --创建工资等级表salgrade create table salgrade ( grade number not null ,--等级 losal number,--最低薪水 hisal number,--最高薪水 primary key(grade) ); --------------------------------------------------------------------- select ename,sal*12 from emp; select 2*3 from emp; select 2*3 from dual;--计算表达式 select sysdate from dual;--获取系统当前时间 ----------------------------------------------------------------------- --字段取别名 select ename, sal*12 anuual_sal from emp;--取别名 select ename, sal*12 as anuual_sal from emp;--取别名 select ename, sal*12 "anuual sal" from emp;--取别名，可以显示空格，保持大小写 ------------------------------------------------------------------------------- --连接符 select ename||'aa''aa' from emp;--"||"连接符号，两个单引号表示一个单引号 ----------------------------------------------------------------------------- --distinct,不重复 select distinct deptno,job from emp;--先查询，再排除重复记录 ------------------------------------------------------------------------------ --between，范围之内 select * from emp where sal between 800 and 1500;--between相当于>= and <= -------------------------------------------------------------------------------- --null,空 空值是指不可用来分配的值 空值不等于空或空格 任何类型的数据都有空值 空值相加任何数据还为空 select ename,sal from emp where comm=null; --没有记录，没有实际用处 select ename,sal from emp where comm is null; --为空，有记录 select ename,sal from emp where comm is not null;--不为空 ------------------------------------------------------------------------- --in，在一个枚举的范围内 select ename,sal from emp where sal in(800,1500,1300); select ename,sal from emp where sal not in(800,1500,1300); -------------------------------------------------------------------------- --系统默认的日期格式 select ename,hiredate from emp where hiredate>'20-2月 -81'; -------------------------------------------------------------------------- --通配符，"_"匹配一个字符，"%"匹配任意的字符串 select ename,sal from emp where ename like '%a%'; select ename,sal from emp where ename like '_a%'; select ename,sal from emp where ename like '%\%%';--系统默认的转义字符为"\" select ename,sal from emp where ename like '%$%%' escape '$';--escape，指定转义字符 -------------------------------------------------------------------------------------- --order by,先查询，后排序，desc：降序，asc：升序（系统默认） select * from dept order by deptno desc; select * from dept where deptno<>10 order by deptno asc; select ename,sal,deptno from emp order by sal desc,deptno asc;--先按工资降序排列，工资相同的列，再按升序排列 --------------------------------------------------------------------------------------- --单行函数 --lower(),将字符串转化为小写 select ename from emp where lower(ename) like '%y%'; select ename from emp where ename like '%y%' or ename like '%Y%'; --upper(),将字符串转化为大写 select ename from emp where upper(ename) like '%Y%'; --substr()字符串截取 select substr(ename,1,3) from emp;--从ename中的第一个字符开始截取三个字符 --chr(),将数字转化其对应的ascii码字符 select chr(65) from dual; --ascii(),将对应的字符转化为其ascii码对应的数字 select ascii('A') from dual; --round()四舍五入,默认精确到各位 select round(23.652) from dual; select round(23.652,1) from dual; select round(23.652,-1) from dual; --to_char()将数字或日期转化为字符串 select to_char(sal) from emp; select to_char(sal,'$999,999,999.99') from emp;--将sal,安装相应的格式转化为字符串 select to_char(sal,'L999,999,999.99') from emp; select to_char(sal,'L000,000,000.00') from emp; $:美元，L或者l:本地货币 select to_char(hiredate) from emp; select to_char(hiredate,'yyyy-mm-dd hh:mi:ss') from emp; select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from emp; --to_date()，将当前固定格式的字符串转化为日期 select ename,hiredate from emp where hiredate>=to_date('2008-09-03 00:00:00','yyyy-mm-dd hh24:mi:ss'); --to_number(),将当前的固定格式的字符串转化为数字 select sal from emp where sal>to_number('$1,050.00','$9,999.99'); --nvl(),对为空的字段进行处理 select ename,sal*12+nvl(comm,0) from emp;--不会空，则加comm，为空则加0； ------------------------------------------------------------------------------- --组函数 --max():最大值，min()：最小值，avg():平均值，sum():求和，count():统计记录数 select max(sal) from emp; select min(sal) from emp; select avg(sal) from emp; select sum(sal) from emp; select count(*) from emp; select count(comm) from emp;--不统计字段为空的记录 --group by分组 select deptno,avg(sal) from emp group by deptno; select deptno,job,avg(sal) from emp group by deptno,job;--deptno，job相同的记录为一组 ---------------------------------------------------------------------------------------------- --having，对分组进行过滤 select deptno,avg(sal) from emp group by deptno having avg(sal)>1000; ------------------------------------------------------------------------------------------------- select deptno,avg(sal) from emp where sal>1000 group by deptno having avg(sal)>1200 order by avg(sal) asc; -------------------------------------------------------------------- [ 本帖最后由 yiliren2000 于 2008-10-6 23:57 编辑 ] 本帖最近评分记录 L 下载币 +20 感谢分享 2008-10-5 17:11 一卷书，二人谈，三味斋，四方客，五音六律，七嘴八舌，九寻盏，十得杂语一堂。

UID 42434  帖子 93  精华 0  下载币 50 枚  金币 0 枚  阅读权限 1  在线时间 17 小时  注册时间 2008-8-23  最后登录 2008-11-27  查看详细资料

TOP

yiliren2000 浪曦会员 下载币 50 枚  金币 0 枚  发短消息 加为好友 当前离线

2# 大 中 小 发表于 2008-10-5 01:14  只看该作者 --case when,分支 统计empno<1003,empno<1005,和其他的记录的个数 select count(*), (case when empno<1003 then 1 when empno<1005 then 2 else 3 end) no from emp group by ( case when empno<1003 then 1 when empno<1005 then 2 else 3 end ); ---------------------------------------------------------------- --sql,1999新标准 select ename,sal from emp join (select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno=t.deptno); --交叉连接 select ename,dname from emp cross join dept; --等值连接 select * from emp,salgrade where emp.sal between salgrade.losal and salgrade.hisal; select ename,dname from emp join dept on(emp.deptno=dept.deptno); select ename,dname from emp join dept using(deptno); --非等值连接 select ename,dname,grade from emp e join dept d on (e.deptno=d.deptno) join salgrade s on (e.sal between s.losal and s.hisal) where ename not like '_A%'; --自连接 select e1.ename ,e2.ename from emp e1 join emp e2 on (e1.mgr=e2.empno) --外连接 select e1.ename,e2.ename from emp e1 left join emp e2 on(e1.mgr=e2.empno); select e1.ename,e2.ename from emp e1 right outer join emp e2 on(e1.mgr=e2.empno); select e1.ename,e2.ename from emp e1 full outer join emp e2 on(e1.mgr=e2.empno); --------------------------------------------------------------------------------------- --导出导出数据 drop user liuchao cascade;--删除用户liuchao exp--导出当前用户的表结构，和数据，在cmd中执行 --创建一个用户liuchao，密码liuchao，数据存在默认的表空间users，在表空间users分配大小为10M的空间 create user liuchao identified by liuchao default tablespace users quota 10M on users grant create session,create table,create view to liuchao;--授权liuchao用户可以连接数据库，创建表，创建视图 imp--导入表结构 create table emp2 as select * from emp;--复制emp表的数据到新创建的表emp2 ----------------------------------------------------------------------------------------------- --rownum，列号:每一个表都默认有一个显示列号的字段rownum select rownum,emp.* from emp; select rownum,ename from emp where rownum<=3; --运用虚列选出第n-m条的记录（效率较高） select * from ( select rownum row_num,emp.* from emp order by empno ) where row_num between 2 and 3; --利用分析函数:row_number() over ( partition by col1 order by col2 )选出第n-m条的记录 select * from ( select row_number() over (order by empno) no,emp.* from emp ) where no between 2 and 3; ----------------------------------------------------------------------------------------- --列约束条件 create table stu ( id number(6) primary key,--主键约束 name varchar2(20) constraint stu_name_nn not null,--不为空约束 sex number(1) check(sex in(0,1)),--检查约束 age number(3), sdate date, grade njmber(2) default 1,--默认约束 class number(4) references class(id),--外键约束 email varchar2(50) unique--唯一约束 ) create table stu ( id number(6), name varchar2(20), sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4) , email varchar2(50), constaint stu_id_pk primary key(id), constaint stu_name_email_uni unique(email,name) constaint stu_class_fk foreign key(class) ) --添加，删除约束条件 alert table stu add(addr varchar2(100)); alert table stu add drop addr; alert table stu modify(addr varchar2(150)); alert table stu drop constraint stu_class_fk; alert table stu add constraint stu_class_fk foreign key(class) reference class(id) ; ----------------------------------------------------------------------------------------- 数据字典表的存储表（dictionary） desc dictionary select tablename from dictionary; 常用数据字典表(user_tables,user_views,user_constraints) desc user_tables; select table_name from user_tables; select view_name from user_views; select constraint_name from user_constraints; select constraint_name,table_name from user_constraints; select index_name from user_indexes; ---------------------------------------------------------------- --索引（提高对经常访问的字段读取速度） create index idx_stu_email on stu(email);--创建索引 drop index idx_stu_email;--删除索引 --视图（就是一个子查询） create view v$_stu as select id,name,age from stu;--创建视图 desc v$_dept_avg_sal_info;--显示表结构中的所有视图 ----------------------------------------------------------------------------- --序列（一般用于作为主键） create table article ( id number, title varchar2(1024), cont long ); create sequence seq;--创建序列 drop sequence seq;--删除序列 start with 1;--从1开始 select seq.nextval from dual;查询下一个序列值 insert into article values (seq.nextval,'a','b');--将虚列作为主键 --------------------------------------------------------------------- --思考题 部门平均薪水的等级 哪些人是经理 不用组函数求最高薪水 平均薪水最高的部门编号与名称 比普通员工的最高薪水还要高的经理人名称 求部门经理人中平均薪水最低的部门名称 求比普通员工的最高薪水还要高的经理人名称 求薪水最高的前五名雇员 求薪水最高的第六名的到十名雇员 -------------------------------------- --三范式 第一范式：要有主键，列不可分 第二范式：不能存在部分依赖， 第三范式：不能存在传递依赖