杰普oracle笔记1

杰普oracle笔记1(oracle配置、函数、查询、连接、建表、数据字典)

配置环境

window 用户:
1.cmd
2.sqlplus system
3.password
4.create user briup identified by briup;
5.grant resource,connect to briup;
6.conn briup/briup;
7.show user;
8.alter session set nls_date_language=english;
9.执行脚本，导入数据表： @d:\summit2.sql 10.select * from tab; ---------------------------------------------------------- 连接数据库：
Briup$su - oracle 密码： oracle
oracle@briup-P4i65G:~$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 14 02:45:58 2011 Copyright (c) 1982, 2005, Oracle.
All rights reserved. Connected to an idle instance.
SQL> startup ORACLE instance started.
Total System Global Area 281018368 bytes Fixed Size        
1258440 bytes Variable Size        
88083512 bytes Database Buffers    
188743680 bytes Redo Buffers        
2932736 bytes Database mounted. Database opened.
SQL> show user USER is "SYS"
SQL> create user briup identified by briup;
User created.
SQL> grant resource,connect to briup; Grant succeeded. SQL> conn briup/briup Connected. SQL> show user USER is "BRIUP"
SQL> @ /home/briup/summit2.sql
Creating and populating tables and sequences. Please wait.
Tables and sequences created and populated.
SQL> select * from tab;

连接服务器：
打开终端 telnet 172.16.0.188 sqlplus briup/briup

select * from tab;

 

连接其他用户的方式（切换用户）

conn system/manager

conn sys/change_on_install as sysdba;

 

忘记Oracle System和Sys密码的解决方法

Oracle提供两种验证方式，一种是OS验证，另一种密码文件验证方式。

如果是第一种方式用以下方法修改密码：

sqlplus /nolog

connect / as sysdba

alter user sys identified by ***;

alter user system identified by ***;

如果是第二种方法用以下方法修改密码：

orapwd file=pwdxxx.ora password=你设定的新密码 entries=10

设定完后，重新启动服务，再次登陆就可以了。

 

cmd命令启动/关闭Oracle服务 .

方式一

启动Oracle,在cmd模式下依次启动:

net start oracleserviceORCL

lsnrctl start

关闭服务为:

lsnrctl stop

net stop oracleserviceORCL

方式二

开启服务的脚本如下

net start OracleCSService

net start OracleDBConsoleorcl

net start OracleOraDb10g_home1iSQL*Plus

net start OracleOraDb10g_home1TNSListener

关闭服务的脚本如下：

net stop OracleCSService

net stop OracleDBConsoleorcl

net stop OracleOraDb10g_home1iSQL*Plus

net stop OracleOraDb10g_home1TNSListener

 

利用系统自带脚本utlsampl.sql创建scott用户

1.确认一下数据库的版本，同时查看一下系统中是否已经存在scott用户

sys@ora10g>select * from v$version;

sys@ora10g>select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED from dba_users where USERNAME = 'SCOTT';

2.创建脚本路径$ORACLE_HOME/rdbms/admin/utlsampl.sql

sys@ora10g>@?/rdbms/admin/utlsampl.sql

3.验证用户是否创建成功

sys@ora10g>select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED from dba_users where USERNAME = 'SCOTT';

sys@ora10g>conn scott/tiger

Connected.

scott@ora10g>select * from cat;

 

配置显示格式：

 

设置每行显示的长度： set linesize 长度;

修改每页显示的记录的长度：set pagesize 行数

 

set linesize 300;

col first_name for a15;

col last_name for a15;

col title for a20;

col comments for a15;

--------------------------------------------

查询部门信息显示格式

1号部门是sale部门信息

select id||'first dept is'||name info from s_dept;

 

替换出现空值的列

NVL:把空值替换成自定义的值

select last_name,NVL(salary*commission_pct/100,0) comm from s_emp;

 

insert into s_emp(id,last_name) values(30,'JD1108');

commit;

 

查询员工的职称，如果没有以'No Hired'替代

select last_name,NVL(title,'No Hired') from s_emp;

 

日期类型

默认的日期格式：DD-MM-YY

查询当前的时间

select sysdate from dual;

查询员工的雇佣日期，没有以'01-JAN-11'代替

select last_name,NVL(start_date,'01-JAN-11') from s_emp;

 

distinct:消除完全重复行

查询员工的职称

select distinct title from s_emp;

select distinct last_name,title from s_emp;

 

sqlplus编辑命令

a(ppend)：追加

l:显示缓存中的命令

n:定位到某一行

/:执行缓存中的命令

c:把旧的命令替换成新的数据

l n：显示特定的行

i:在第n行后面再加一行

n text:替换第n行

cl buff:清空缓存

 

文件命令

save:把缓存区的命令保存至某个文件

get:把文件的命令读到缓存区

start/@:让执行sql脚本

edit:编辑sql脚本

exit:退出sqlplus

spool filename:

spool off:打开缓冲区记录所有sql操作保存到文件中

 

雇佣日期降序排序

alter session set nls_date_language=english

 

简单查询

查询2月就职并且姓名中不包含a的员工和薪水小于1500且部门不在40，50部门的所有员工信息。

select *

from s_emp

where start_date like '%FEB%'

and last_name not like '%a%

or salary<1500

and dept_id not in(40,50);

 

查询姓名以"_"开头的员工信息

select *

from s_emp

where last_name like '*_%' escape '*';

 

单行函数

lower:转换成小写

upper:转换成大写

initcap:首字母大写

concat:连接字符串

substr:返回子串

length:返回字符串的长度

nvl:替换空值

 

查询员工的姓名(首字母大写)，职称(大写)，职称为空替换为'no',userid(小写)

select initcap(last_name),nvl(upper(title),'no'),lower(userid)

from s_emp;

 

查询职称包含'st'的员工信息

select *

from s_emp

where lower(title) like '%st%';

 

查询姓名包含MA的员工信息，职称小写输出。

要求输出格式为：

Lisi

LISI的职称是sale

select upper(last_name)||'的职称是'||lower(title)

from s_emp

where upper(last_name) like '%MA%';

 

 

concat('Good','String') à GoodString

substr('String',1,3) à Str

length('Stirng') à 6

 

输出格式如：LISIZhangsan

select concat(upper(last_name),initcap(first_name))

from s_emp;

 

数学函数

round:四舍五入

trunc:截取

mod:取余

 

日期函数

months_between:两个日期之间的月数

add_months:在日期上加上指定的月数

 

alter session set nls_date_language=english;    //将本地的日期格式改成英文

sysdate

 

next_day:下个星期几的日期

查询下个星期五的日期

select next_day(sysdate,'friday')

from dual;

 

last_day:当前月的最后一天是几号

select last_day(sysdate)

from dual

 

查询5年后的那个月的最后一天的下个星期一的日期

select next_day(last_day(add_months(sysdate,60)),'monday')

from dual;

 

round:对日期指定部分四舍五入

trunc:截取日期的指定部分

 

round('25-MAY-95','MONTH')        01-JUN-95

round('25-MAY-95','DAY)        01-JUNE-95

trunc('25-MAY-95','MONTH')        01-MAY-95

trunc('25-MAY-95','MONTH')        

 

select round(to_date('15-JAN-11','dd-mon-yy'),'month')

from dual;

 

转换函数

 

to_char:转换成指定的字符串

 

yyyy:4位的年

YY:两位的年

 

两位的阿拉伯数字的年

select to_char(sysdate,'yy')

from dual;

 

四位的阿拉伯数字的年

select to_char(sysdate,'yyyy

from dual;

 

select to_char(sysdate,'yyyy-YY-rr-YEAR-Year-year')

from dual;

 

MM:阿拉伯数字的月

MON:大写的月缩写

Mon:首字母大写的月缩写

mon:小写的月缩写

MONTH:大写的月全称

Month:首字母大写的月全称

month:小写的月全称

 

DD/dd:一个月第几天

DDD:一年的第几天

d：一个星期的第几天

Day:首字母大写英文的星期几

DAY:大写的英文的星期几

day:小写的英文星期几

ddsp:一个月的第几天的基数词

ddspth:一个月的第几天的序数词

DY:大写的星期几的缩写

Dy:首字母大写的星期几的缩写

dy:小写的星期几的缩写

hh24:24小时制

hh12:12小时制

mi:分钟

ss:秒

 

显示当前日期格式为2011-07-07

select to_char(sysdate,'yyyy-mm-dd')

from dual;

 

查询在6月雇佣的员工信息，并且雇佣日期显示为"2011-1-1 15:00:00"格式

select last_name,to_char(start_date,'yyyy-mm-dd hh24:mi:ss')

form s_emp;

 

查询雇佣的员工信息，要求工作半年后的日期格式显示为"2011年7月7 日 15:00:00"

select last_name,to_char(add_months(start_date,6),'yyyy'||'年'||'mm'||'月'||'dd'||'日' 'hh24:mi:ss'))

form s_emp;

 

把'123'转换成数字与'456'相乘

select to_number('123')*to_number('456')

from dual;

 

多表查询(连接查询)

等值连接

查询员工名以及部门名

select s_emp.last_name,dept_id,s_dept.name

from s_emp,s_dept

where s_emp.dept_id=s_dept.id;

 

查询员工所在部门地区的名字并按员工薪水高低排序，薪水相同，按部门名字排序

select s_emp.last_name,s_emp.salary,s_dept.name,s_region.name

from s_emp,s_dept,s_region

where s_emp.dept_id=s_dept.id

and s_dept.region_id=s_region.id

order by s_emp.salary desc,s_dept.name

 

select e.last_name 员工名,e.salary sal,

d.name 部门名,r.name 地区名

from s_emp e,s_dept d,s_region r

where e.dept_id=d.id

and d.region_id=r.id

order by e.salary desc,d.name

 

查询亚洲的顾客姓名

select sc.name,sr.name

from s_customer sc,

s_region sr where sc.region_id=sr.id

and sr.name like 'As%';

 

查询欧洲销售部门的姓名包括a且在2月就职的员工信息

select se.*,sd.name,sr.name

from s_emp se,s_dept sd,s_region sr

where se.dept_id=sd.id

sd.region_id=sr.id

and sd.name='sale'

and sr.name='Europe'

and to_char(e.start_date,'mm')=2

order by e.salary desc;

 

 

create tabel s_grade(

id number(7) primary key,

name varchar2(20),

min number(7),

max number(7));

 

insert into s_grade values(1,'蓝领',0,1000);

insert into s_grade values(1,'白领',1000,2000);

insert into s_grade values(3,'金领',2000,3000);

commit;

查询员工的级别信息

select e.last_name,g.name

from s_emp e,s_grade g;

where e.salary between g.min and g.max;

 

不等值连接

create tabel s_grade(

id number(7) primary key,

name varchar2(20),

min number(7),

max number(7));

 

insert into s_grade values(1,'蓝领',0,1000);

insert into s_grade values(1,'白领',1000,2000);

insert into s_grade values(3,'金领',2000,3000);

commit;

查询员工的级别信息

select e.last_name,g.name

from s_emp e,s_grade g;

where e.salary between g.min and g.max;

 

外连接

 

查询所有员工的部门名称，即使该部门没有员工

select s.last_name,d.name

from s_emp s ,s_dept d

where s.dept_id(+)=d.id

 

select s.last_name,d.name

from s_emp s right join s_dept d

on s.dept_id=d.id

 

+：哪边数据少在哪边加'+'

+在=左边在右外连接

+在=左边在右外连接

oracle:

select s.last_name,d.name

from s_emp s left join s_dept d

where s.dept id(+)=d.id;

 

 

标准sql:

select s.last_name,d.name

form s_emp s right join s_dept d

on s.dept_id=d.id;

 

查询所有的部门名字和所有的员工名字

全外连接：

selelct s.last_name,d.name

from s_emp s full join s_dept d

on s.dept_id=d.id;

 

查询所有的姓名包括a的薪水在1000到1500的员工，即使员工没有部门，按雇佣日期降序排列。

select s.last_name,e.salary

form s_emp s left join s_dept d

where s.last_name like '%a%'

and salary between 1000 and 1500

order by start_date desc

 

select s.last_name,e.salary

form s_emp s,s_dept d

where s.dept_id(+)=d.id

and s.last_name like '%a%'

and salary between 1000 and 1500

order by start_date desc

 

自连接：

查询Ngao的手员工信息

select e.last_name,m.*

from s_emp e ,s_emp m

where e.id=m.manager_id

and e.last_name='Ngao'

order by e.last_name

 

查询亚洲销售部门员工经理的名字即使该员工没经理

select e.last_name,m.last_name

from s_emp e,s_emp m,s_dept d,s_region r

where e.region_id=r.region_id

and e.dept_id=d.id

and r.name='Asia'

and d.name='Sale'

and m.id=e.manager_id(+)

 

集合操作符(union)

union:相并，相交的部分只取一次

union all:两部相加，相交的部分取两次

minus:相减，第一部分减去第二部分

intersect:相交，取交集

 

rownum:伪列

1.=只对1有效

2.不能用>

3.<或者<=无限制

 

rowid: 伪列

唯一确定一条记录的字段

 

minus:查询员工表中第10到第15条记录(rownum)

select *

from s_emp

where rownum<=15

minus

select s_emp

from s_emp

where rownum>=10

 

组函数

查询员工的最大工资，薪水平均值，总工资，最小工资

select avg(salary),max(salary),sum(salary),min(salary)

from s_emp;

 

查询职位包含st的员工的最大薪水

select max(salary)

from s_emp

where lower(title) like '%st%';

 

查询Ngao手下员工薪水的平均值

select avg(salary)

from s_emp e,s_emp m

where e.manager_id=m.id

and m.last_name='Ngao';

 

select avg(salary)

from s_emp

where manager_id=(

select id from s_emp where last_name='Ngao');

 

count:计算(非空的记录数)

查询有多少员工

select count(*) from s_emp;

select count(rowid) from s_emp;

 

查询薪水大于1000和部门不在41，45的员工数

select count(*)

from s_emp

where salary>1000

or dept_id<>41 and dept_id<>45 ;

 

分组查询

查询各个部门的人数

select count(rownum)

form s_emp

group by dept_id;

 

select dept_id,scount(rownum)

from s_emp

group by dept_id;

 

注：select 后面没有被包含在组函数中的字段必须写在group by后面，但group by后面的字段可以不写在select后面。where后不能跟组函数。

组函数只能出现在：select,having,order by后边

 

查询各个部门的总薪水，部门号，按总薪水降序排序。

select dept_id,sum(salary) sal

from s_emp

group by dept_id

order by sal desc

 

查询部门平均薪水大于1000的部门名称

select e.dept_id,d.name, avg(e.salary) davg_sal

form s_emp e,s_dept d

where e.dept_id=d.id

group by e.dept_id,name

having davg_sal >1000 ;

 

查询职称包含sa的员工最小薪水大于800的职称

select e.last_name,e.title,min(salary) min_sal

from s_emp e

group by e.title,e.last_name

having lower(e.title) like '%sa%'

and min_sal>800;

 

select e.last_name,e.title,min(salary) min_sal

from s_emp e

where lower(e.title) like '%sa%'

group by e.title,e.last_name

having min_sal>800;

 

查询职称不包含sa,总薪水在4000到5000之间的职称，并按平均薪水升序排序。

select e.title,avg(salary) avg_sal

from s_emp e

where lower(e.title) not like '%sa%'

group by e.title

having sum(salary) between 1000 and 2000

order by avg_sal;

 

子查询

查询与Ngao同一部门的员工的平均工资

select avg(salary)

from s_emp

where dept_id=

(

    select id from s_dept where name='Ngao'

)

 

查询部门工资大于45号部门平均工资，并且在2月就职的员工信息

select last_name,start_date

from s_emp

where lower(last_name) like '%a%'

and salary>

(

    select avg(salary) from s_emp where dept_id=45

)

and to_char(start_date,'mm')=2;

 

查询和Ngao是同一职称的员工的平均薪水大于41号部门最小薪水的员工信息

select e2.avg(salary) avg_sal

from s_emp e1,s_emp e2

where e1.title=e2.title

and e1.last_name='Ngao'

 

运行时变量

 

select last_name &name from s_emp where &con ;

Enter value for name:ename

Enter value for con:salary>1000

old    1: select last_name &name from s_emp where &con ;

new    1: select last_name ename from s_emp where salary>1000 ;

 

define name='ename' 定义运行时变量name赋值

undefine name    删除运行时变量name

 

------------------------

第四天

设计(架构)

 

#*:主键

*:非空

0：无约束

 

建表

 

create table grade_cst

(

id number(8) constraint grade_cstid_pk primary key,

name varchar2(20) constraint grade_cst_name_nn not null

);

--drop table grade_cst

create table student_cst

(

id number(8) constraint student_cst_id_pk primary key,

name varchar2(20) constraint student_cst_name_nn not null,

sid number(7) constraint student_cst_sid_uk unique,

sex char(1) constraint student_cst_sex_ck check(sex in('f','m')),

grade_id number(7) constraint student_cst_grade_id_fk references grade_cst(id)

);

 

--drop table student_cst;

--drop table grade_cst;

 

查找约束的名字：

select constraint_name from user_constraints

where table_name=upper('student_cst');

 

primary key:主键约束

not null:非空约束

unique:唯一约束

check:检查约束

forgia

 

表级别约束：

联合主键

create table h

(

id number(7),

id2 number(7),

constraint h_id_id2_pk primary key(id,id2)

);

 

create table j

(

id number(7) constraint j_id_pk primary key

);

 

联合外键：

create table k

(

id number(7),

h_id number(7),

h_id2 number(7),

--j_id number(7) constraint k_j_id_fk references j(id),

j_id number(7) constraint k_j_id_fk references j(id) on delete cascade,

--constraint h_id_id2_fk foreign key(h_id,h_id2) references h(id,id2)

constraint h_id_id2_fk foreign key(h_id,h_id2) references h(id,id2) on delete cascade

);

on delete cascade:删除表的同时，也删除引入表的相关数据，无法删除引入表的结构。

 

--drop table k;

--drop table j;

--drop table h;

 

create table my_emp

as

select id,last_name,start_date

from s_emp

where dept_id=41;

--用这种方式建表除了not null 约束被复制，其它约束全都丢失

--drop table my_emp;

 

数据字典：

select count(*) from dictionary;

 

desc user_objects;

 

select created from user_objects where OBJECT_NAME='S_EMP';

select status from user_objects where OBJECT_NAME='S_EMP';

select object_name from user_objects where OBJECT_NAME='S_EMP';

select status from user_objects where OBJECT_TYPE='TABLE';

 

select constraint_name,constraint_type

from user_constraints

where table_name='S_EMP';