oracle 第一天
SQLPLUS下的常用命令
show user 察看用户
connect 用户名/密码 连接到指定用户
connect scott/tiger
select * from tab where tabtype='TABLE'; 察看当前用户下的表
select count(*) from dept; 查询表dept中记录的行数
desc dept 察看表dept的结构
quit/exit 退出
clear screen 清除屏幕
set linesize 200 设置一行显示200个字符
set pagesize 20 设置每页显示20行
spool 文件名 (spool c:\abc.txt) 作日志文件命令
spool off
修改D:\oracle\ora90\sqlplus\admin\glogin.sql文件
可以设置SQLPLUS的环境
set linesize 200
set pagesize 20
ORACLE 企业管理器 (OEM) 图形界面
PL/SQL Developer 第三方软件 ORACLE不带的
SQLPLUS /nolog nolog是不登陆
浏览器使用oracle (isqlplus)
D:\oracle\ora90\Apache\Apache\conf\httpd.conf
修改80端口 -> 8001
http://localhost:8001/isqlplus
ORACLE中字段的数据类型
字符型 char 范围 最大2000个字节 定长
char(10) '张三' 后添空格6个把10个字节补满 '张三 '
性别 char(2) '男'
varchar2 范围 最大4000个字节 变长
varchar2(10) '张三' 在数据库中'张三'
大对象 字符型大对象 >4000字节 最大4G
CLOB (Character Large OBject)
数字 number 范围 10的-38次方 到10的38次方
可以表示小数 也可以表示整数
number(4) 最大表示4位整数 -9999 到 9999
number(5,2) 表示5位有效数字 2位小数的 一个小数 -999.99 到 999.99
日期 date 包含年月日和时分秒 7个字节
图片 blob 二进制大对象 图像/声音 4G
如何建表
学生表student
create table student( --学生表
xh number(4), --学号
xm varchar2(10), --姓名
sex char(2), --性别
birthday date, --日期
sal number(7,2) --奖学金
);
班级class
create table class( --班级表
classid number(2), --班级编号
cname varchar2(20) --班级名字
);
添加字段(学生所在班级classid)
alter table student add (classid number(2));
修改字段的长度
alter table student modify (xm varchar2(12)) ;
修改字段的类型(不能有记录的)
alter table student modify (xh varchar2(5));
删除一个字段
alter table student drop column sal;
删除表
drop table student;
表的名字修改
rename student to stu;
字段如何改名字
--先删除
a)alter table student drop column sal;
--再添加
b)alter table student add (salary number(7,2));
如何插入数据
插入数据 insert语句
所有字段都插入
insert into student values ('A001','张三','男','01-5月-05',10);
ORACLE中默认的日期格式'DD-MON-YY' dd 日子(天) mon 月份 yy 2位的年
'09-6月-99' 1999年6月9号
改日期的默认格式
alter session set nls_date_format = 'yyyy-mm-dd';
insert into student values ('A002','MIKE','男','1905-05-06',10);
恢复ORACLE默认格式
alter session set nls_date_format = 'dd-mon-yy';
察看日期的格式
set linesize 1000
select * from nls_session_parameters
where parameter='NLS_DATE_FORMAT';
永久设置日期格式
改注册表oracle/HOME0 加字符串NLS_DATE_FORMAT 值yyyy-mm-dd
部分字段插入
insert into student(xh,xm,sex) values ('A003','JOHN','女');
插入空值
insert into student(xh,xm,sex,birthday) values ('A004','MARTIN','男',null);
修改 update
改一个字段
update student set sex='女' where xh='A001';
改多个字段
update student set sex='男',
birthday='1980-04-01'
where xh='A001';
改为空值 (修改为空时=null)
update student set birthday=null where xh='A001';
把生日为空的人的班级编号改为20(条件中的空是is null / is not null)
update student set classid=20 where birthday is null;
错误的没有达到要求
update student set classid=20
where birthday=null;
不表示空值 表示xm是null的字符串
update student set classid=20 where xm='null';
删除 delete
delete from student; 删除所有记录,表结构还在,写日志,可以恢复的,速度慢
drop table student; 删除表的结构和数据
delete from student where xh='A001'; 删除一条记录
truncate table student; 删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快
查询 select
select * from student;
select xh,xm,sex from student;
select * from student where xh like 'A%1'; %任意多个字符
select * from student where xh like 'A__1'; _1个字符
select * from student where xh like '%A%'; select * from student where xh like 'A%';
select * from student where xh like '%A';
select * from student where xh = 'A%';
select * from student
order by birthday ; 升序 (order by birthday asc;)
select * from student
order by birthday desc; --降序
select * from student
order by birthday desc,xh asc; --按birthday 降序 按xh升序(asc/默认)
select * from student
where sex='女' or birthday='1999-02-01';
select * from student
where sex='女' and birthday='1999-02-01';
select * from student
where salary > 20 and xh <> 'B002'; (!=)
ORALCE的函数
单行函数 返回值只有一个
分组函数 返回值是多条记录
group by
sum
avg
单行函数
字符函数
concat 连接 ||
<1>显示dname和loc中间用-分隔
select deptno,dname||'----'||loc from dept;
dual哑元表 没有表需要查询的时候 可以用它
select 'Hello World' from dual;
select 1+1 from dual;
查询系统时间
select sysdate from dual;
<2> initcap 首字母大写
select ename,initcap(ename) from emp;
<3> lower 转换为小写字符
select ename,lower(ename) from emp;
<4> upper 转换为大写
update dept set loc=lower(loc);
update dept set loc=upper(loc);
<5> LPAD 左填充
select deptno,lpad(dname,10,' '),loc from dept;
<6> RPAD 右填充
<7> LTRIM 去除左边的空格
RTRIM 去除右边的空格
ALLTRIM 去除两边的空格
<8>replace 替换
translate 转换
select ename,replace(ename,'S','s') from emp;
用's'去替换ename中的'S'
select ename,translate(ename,'S','a') from emp;
<9> ASCII 求ASC码
chr asc码变字符
select ascii('A') from dual;
select chr(97) from dual;
select 'Hello'||chr(9)||'World' from dual;
'\t' ascii码是 9
'\n' ascii码是 10
select 'Hello'||'\t'||'World' from dual;
<10> substr 字符截取函数
select ename,substr(ename,1,3) from emp;
从第1个位置开始 显示3个字符
select ename,substr(ename,4) from emp;
从第4个位置开始显示后面所有的字符
<11> instr 测试字符串出现的位置
select ename,instr(ename,'S') from emp;
'S'第1次出现的位置
select ename,instr(ename,'T',1,2) from emp;
从第1个位置开始 测试'T'第2次出现的位置
<12> length 字符串的长度
select ename,length(ename) from emp;
日期和 时间函数
<1> sysdate 系统时间
select sysdate from dual;
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(sysdate,'DDD') from dual
select to_char(sysdate,'D') from dual
select to_char(sysdate,'DAY') from dual
select to_char(sysdate,'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;
select '''' from dual;
select to_char(sysdate,'SSSSS') from dual;
--从今天零点以后的秒数
<2> ADD_MONTHS 添加月份 得到一个新的日期
select add_months(sysdate,1) from dual;
select add_months(sysdate,-1) from dual;
select trunc(sysdate)-to_date('20050101','yyyymmdd') from dual;
select add_months(sysdate,12) from dual;
一年以后的今天
select add_months(sysdate,-12) from dual;
一年以前的今天
trunc(sysdate) 截取年月日
select sysdate+2 from dual;
数字代表的是天数
两个日期之间的差值代表天数
<3> last_day 某月的最后一天
select last_day(sysdate) from dual;
select add_months(last_day(sysdate)+3,-1) from dual;
本月第3天的日期
<4> months_between 两个日期之间的月数
select months_between(sysdate,'2005-02-01') from dual;
方向 sysdate - '2005-02-01'
select months_between('2005-02-01',sysdate) from dual;
转换函数
to_char 把日期或数字类型变为字符串
select to_char(sysdate,'hh24:mi:ss') from dual;
select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;
select sal,to_char(sal,'L9,999') from emp;
L本地货币
to_number 把字符串变成数字
select to_number('19990801') from dual;
to_date 把字符串变成日期
select to_date('19800101','yyyymmdd') from dual;
select to_char(to_date('19800101','yyyymmdd'),
'yyyy"年"mm"月"dd"日"') from dual;
数学函数
ceil(x) 不小于x的最小整数
ceil（12.4) 13
ceil(-12.4) -12
floor(x) 不大于x的最大整数
floor(12.5) 12
floor(-12.4) -13
round(x) 四舍五入
round(12.5) 13
round(12.456,2) 12.46
trunc(x) 舍去尾数
trunc(12.5) 12
trunc(12.456,2) 12.45
舍去日期的小时部分
select to_char(trunc(sysdate),'yyyymmdd hh24:mi:ss') from dual;
mod(x,n) x除以n以后的余数
mod(5,2) 1
mod(4,2) 0
power(x,y) x的y次方
select power(3,3) from dual;
混合函数
求最大值
select greatest(100,90,80,101,01,19) from dual;
求最小值
select least(100,0,-9,10) from dual;
空值转换函数 nvl(comm,0) 字段为空值 那么就返回0 否则返回本身
select comm,nvl(comm,0) from emp;
comm 类型和 值的类型是 一致的
复杂的函数
decode 选择结构 (if ... elseif .... elesif ... else结构)
要求:
sal=800 显示低工资
sal=3000 正常工资
sal=5000 高工资
只能做等值比较
select sal,decode(sal,800,'低工资',3000,'正常工资',5000,'高工资','没判断')
from emp;
表示如下的if else 结构
if sal=800 then
'低工资'
else if sal =3000 then
'正常工资'
else if sal = 5000 then
'高工资'
else
'没判断'
end if
sal > 800 sal -800 > 0
判断正负
sign(x) x是正 1
x是负 -1
x是0 0
select sign(-5) from dual;
如何做大于小于的比较？？？？
sal<1000 显示低工资 sal-1000<0 sign(sal-1000) = -1
1000<=sal<=3000 正常工资
3000<sal<=5000 高工资
select sal,decode(
sign(sal-1000),-1,'低工资',
decode(sign(sal-3000),-1,'正常工资',
0,'正常工资',1,
decode(sign(sal-5000),-1,'高工资','高工资')
)) as 工资状态 from emp;
一般的情况 decode(x,y1,z1,y2,z2,z3)
if x= y1 then
z1
else if x = y2 then
z2
else
z3
end if
分组函数 返回值是多条记录 或计算后的结果
group by
sum
avg
<1> 计算记录的条数 count
select count(*) from emp;
select count(1) from emp;
select count(comm) from emp; 字段上count 会忽略空值
comm不为空值的记录的条数
统计emp表中不同工作的个数 ????
select count(distinct job) from emp;
select distinct job from emp;
select distinct job,empno from emp;
select job,empno from emp;
得到的效果是一样的,distinct 是消去重复行
不是消去重复的列
<2>group by 分组统计
--在没有分组函数的时候
--相当于distinct 的功能
select job from emp group by job;
select distinct job from emp;
--有分组函数的时候
--分组统计的功能
统计每种工作的工资总额是多少??
select job,sum(sal) from emp
group by job; --行之间的数据相加
select sum(sal) from emp; --公司的工资总额
统计每种工作的平均工资是多少??
select job,avg(sal) from emp
group by job;
select avg(saL) from emp; --整个公司的平均工资
显示平均工资>2000的工作?？？
<1>统计每种工作的平均工资是多少
<2>塞选出平均工资>2000的工作
从分组的结果中筛选 having
select job,avg(sal) from emp
group by job
having avg(sal) > 2000;
group by 经常和having搭配来筛选
计算工资在2000以上的各种工作的平均工资？？？？
select job,avg(sal) from emp
where sal > 2000
group by job
having avg(sal) > 3000;
一般group by 和 having搭配
表示对分组后的结果的筛选
where子句 --- 用于对表中数据的筛选
<3> max min
select max(sal) from emp;
公司的最高工资
select min(sal) from emp ;
公司的最低工资
找每个部门的最高和最低的工资??
select deptno,max(sal),min(sal) from emp
group by deptno;
找每个工作的最高和最低的工资??
select job,max(sal),min(sal) from emp
group by job;
找每个部门中每种工作的最高和最低的工资??
select deptno,job,max(sal),min(sal)
from emp
group by deptno,job;
select max(sal),min(sal)
from emp
group by deptno,job;
单个字段如果没有被分组函数所包含，
而其他字段又是分组函数的话
一定要把这个字段放到group by中去
<4>关联查询
多张表,而表与表之间是有联系的
是通过字段中的数据的内在联系来发生
而不是靠相同的字段名来联系的或者是否有主外键的联系是没有关系的
select dname,ename from emp,dept;
笛卡尔积 (无意义的)
--当2个表作关联查询的时候一定要写关联的条件
--N个表 关联条件一定有N-1个
select dname,ename from mydept,myemp
where mydept.no = myemp.deptno;
多表查询的时候一定要有关联的条件
--使用的表的全名
select dname,ename from emp,dept
where emp.deptno = dept.deptno ;
--使用表的别名
select dname,ename,a.deptno from emp a,dept b
where a.deptno = b.deptno and a.deptno = 10;
--等值连接(内连接-两个表的数据作匹配a.deptno = b.deptno )
select dname,ename,a.deptno from
emp a inner join dept b
on a.deptno = b.deptno;
where a.deptno = 10;
--on写连接条件的
--where中写别的条件
--使用where/on
select dname,ename,a.deptno from emp a,dept b
where a.deptno = b.deptno and a.deptno=10;
--on中写连接条件
--where中写其他的条件
select dname,ename,a.deptno from
emp a inner join dept b
on a.deptno = b.deptno
where a.deptno = 10 ;
--外连接
左外连接 右外连接 全外连接
(+)写法只有在ORACLE中有效
select dname,ename,b.deptno
from emp a,dept b
where a.deptno(+) = b.deptno;
--标准写法
select dname,ename,b.deptno
from emp a right outer join dept b
on a.deptno = b.deptno;
select dname,ename,b.deptno
from emp a,dept b
where a.deptno = b.deptno(+);
--标准写法
select dname,ename,b.deptno
from emp a left outer join dept b
on a.deptno = b.deptno;
--标准写法(全外联)
select dname,ename,b.deptno
from emp a full outer join dept b
on a.deptno = b.deptno;
--自连接
select a.ename as 员工姓名,b.ename as 经理名字 from emp a,emp b
where a.mgr = b.empno(+);
a.empno = b.mgr ???????
