服务-DOS-启动
OracleOraDb11g_home1TNSListener
停止:isnrctl stop 启动:isnrctl start
OracleServiceORCL
停止:net stop oracleserviceorcl 启动:net start oracleserviceorcl
sqlplus连接方法:
1.程序
2.运行:a. sqlplus sys as sysdba/@macey b.输入口令
b. sqlplus scott/scott@orcl
3.运行:a.
安装后的 Oracle SQL Developer 不能用，出现一个对话框提示：Enter the full pathname for java.exe
解决方法:F:\app\zhangjianbo\product\11.2.0\dbhome_1\jdk\bin
常用工具：D:\app\Administrator\product\11.2.0\dbhome_1\BIN
1.查看当前用户 sys
show user
2.解锁用户sys
alter user sys account unlock;
3.修改用户sys密码为nihao
alter user sys identified by nihao;
4.查询当前数据库的所有表名称
select table_name from user_tables;
select * from tab;
5.查询表字段及数据类型
desc xue_sheng;
6 拷贝记录
spool d:123.txt
SQL> select * from xue_sheng;
...
SQL> spool off
7.清屏
clear screen
8.显示最后一条命令,进行txt编辑
edit
9.显示时间到语句前
set time on
10.取消时间在语句前显示
set time off
11.调用DOS命令
host (DOS命令) host mkdir d:\123
12.执行SQL语句
@d:\123.sql
13.取系统当前时间
select sysdate from dual;
按固定格式显示时间：
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
修改系统当前显示格式(仅对当前连接生效)：
alter session set nls_date_format='yyyy-mm-dd';
取系统时间，精确到六位小数点
select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ssxff6') from dual;
14.创建学生表
create table student(sno number(6),sname varchar(10),birthday date);
14-1.删除表
drop table student; ---可恢复
drop table student purge; ---不可恢复
15.查询表字段描述
desc student;
16.增加表列
alter table student add tel varchar2(11);
17.修改表列的长度
alter table student modify tel varchar2(20);
18.删除表中列
alter table student drop column tele;
18-1.重命名表名
rename student fto student1;
18-2.重命名表列
alter table student rename column sno to sno1;
19.向表里插入数据
insert into student values(1,'张建波','1984-03-23','135********')；
insert into student values(2,'冯丽娜','1985-09-25','130********');
insert into student values(3,null,to_date('19810223','yyyymmdd'),'130********');
insert into student(sno,birthday,tel) values(4,to_date('19800213','yyyymmdd'),'130********');
20.查询数据
select * from xue_sheng where sname is null;
21.复制整张表到student2
create table student2 as select * from student;
22.仅复制表结构到student3
create table student2 as select * from student where 1>2;
23.清除表数据
a.truncate /暗含提交 /不可恢复
truncate table student2;
b.delete /必须提交生效 /可恢复
delete from student;
24.修改表数据
update student
set sname='A' where sno=1;
25.查询行数
select count(*) from student;
26.在列值中增加"'",如"A'B",二个单引号代表一个单引号
update student2 set sname='A''B' where sno='4';
27.like
select * from student2 where sname like 'A%'; --以A开头的
select * from student2 where sname like 'A_'; --以A开头，开后跟一个字符
28.length:注意汉字，一样对待
select * from student2 where length(sname)=4;
29.取别名
select sno 学号,sname "姓 名" from student2;
30.成绩表
create table chengji(sno number,km varchar2(100),score number);
insert into chengji values(1,'语文',60);
insert into chengji values(1,'数学',60);
insert into chengji values(1,'英语',60);
insert into chengji values(2,'语文',70);
insert into chengji values(2,'数学',70);
insert into chengji values(3,'语文',80);
31.常用函数的使用
select sno,sum(score) from chengji group by sno;
select km,avg(score) from chengji group by km;
SQL> select km,avg(score) from chengji group by km
2 having(avg(score)>64)
3 order by sno;
32.连接符||
select '学号是: '||sno||'姓名是: '|| sname from student2;
一、字符函数
33.第一个字符转换成大写
select initcap('hello') from dual;
34.全部转换为小写
select lower('FUN') from dual;
35.全部转换为大写
select upper('sun') from dual;
36.截掉字符串
select ltrim('xyzadams','xyz')from dual;
37.替换字符串
select translate('jack','j','b')from dual;
select replace('jack','j','b')from dual;
38.查询字符串的所在位置
select instr('worldwide','d') from dual;
39.获取字符串
select substr('abcdefg',3,2) from dual;
40.连接字符串
select concat('Hello','world') from dual;
select 'Hello'||'world' from dual;
41.将ASCII码转换为字符
select chr(97) from dual;
42.将字符转换为ASCII码
select ascii('a') from dual;
43.从左边按给定的长度及字符补位
select lpad('abcd',10,'x') from dual;
44.从右边按给定的长度及字符补位
select rpad('abcd',10,'x') from dual;
二、日期函数
45.add_months 某给定时间，N个月后的日期
select add_months(sysdate,5) from dual;
46.months_between 二日期相差的月份
select months_between(sysdate,to_date('2009-06-05','yyyy-mm-dd')) from dual;
https://www.doczj.com/doc/56999651.html,st_day 取给定日期所在月的最后一天
select last_day(sysdate) from dual;
48.round 存在四舍五入
select round(to_date('2010-06-01','yyyy-mm-dd'),'year') from dual; --2010-01-01
select round(to_date('2010-06-01','yyyy-mm-dd'),'month') from dual; --2010-06-01
select round(to_date('2010-07-01','yyyy-mm-dd'),'year') from dual; --2010-07-01
49.next_day
50.trunc 不存在四舍五入
select trunc(to_date('2010-06-01','yyyy-mm-dd'),'year') from dual; --2010-01-01
select trunc(to_date('2010-06-01','yyyy-mm-dd'),'month') from dual; --2010-06-01
select trunc(to_date('2010-07-01','yyyy-mm-dd'),'year') from du
al; --2010-01-01
51.extract 从给定日期分别取年、月、日
select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;
三、数字函数
52.截止数学的小数位（不四舍五入）
select floor(4.37) from dual;
52.1.sign()
当()中的值或表达式为正数时，返回1；为0时，返回0；否则为-1
四、转换函数
53. to_char 将数字转换为字符
select to_char(33,'L99.99') from dual; --人民币
select to_char(33,'$99.99') from dual;--美元
select to_char(sysdate,'yyyy"年"fmmm"月"fmdd"日" hh24:mi:ss') from dual;
54.to_date 将字符转换为日期
select to_date('2013-10-05','yyyy-mm-dd') from dual;
55.to_number 将字符转换为数字
select to_number('123') from dual;
五、混合函数
56. nvl 第一为空返回第二，否则返回第一
select nvl(2,1) from dual;
select nvl('',1) from dual;
select nvl(null,1) from dual;
57.nvl2 第一不空返回第二，否则返回第三
select nvl2(2,1,3) from dual;
select nvl2(2,'',3) from dual;
select nvl2('','',3) from dual;
58.decode
59.nullif 两个表达式，相等则返回空，否则返回第一个
select nullif(2,2) from dual;
select nullif(2,22) from dual;
六、分组函数
60. avg
61. min
62. max
63. sum
64. count
等值连接
select s.sno,s.sname,a.sno,a.zz from student s,address a
where s.sno=a.sno
select s.sno,s.sname,a.sno,a.zz from student s
inner join address a
on s.sno=a.sno
左连接
select s.sno,s.sname,a.sno,a.zz from student s,address a
where s.sno=a.sno(+)
select s.sno,s.sname,a.sno,a.zz from student s
left outer join address a
on s.sno=a.sno
全连接
select s.sno,s.sname,a.sno,a.zz from student s
full outer join address a
on s.sno=a.sno
集合操作符
union 过滤掉交集部分，即每条记录不重复
select * from student
union
select * from student where sno=1
unnion all 将所有的记录查询出来，即允许重复
select * from student
union all
select * from student where sno=1
intersect 求交集
select * from student
intersect
select * from student where sno=1
minus 用大集合 减去 小集合
select * from student
minus
select * from student where sno=1
SQL语句的执行顺序
from ,where,group by,having,select,order by 依次
exists使用 结果为1或0
select * from student
where exists(select * from adderess where zz='郑州'）；
自连接：
select case when使用
方法一：
select case
when substr('20090310',5,2)='01' then '一月份'
when substr('20090310',5,2)='02' then '二月份'
when substr('20090310',5,2)='03' then '三月份'
when substr('20090310',5,2)='04' then '四月份'
else null
end
from dual;
方法二：
select case substr('20090310',5,2)
when '01' then '一月份'
when '02' then '二月份'
when
'03' then '三月份'
when '04' then '四月份'
else null
end
from dual;
分析函数：
create table chengjibiao(sno number,km varchar2(10),score number);
insert into chengjibiao values(1,'语文',60);
insert into chengjibiao values(1,'数学',60);
insert into chengjibiao values(1,'英语',60);
insert into chengjibiao values(2,'语文',70);
insert into chengjibiao values(2,'数学',70);
insert into chengjibiao values(3,'语文',80);
row_number 返回连续的排序，不论值是否相等
select sno,km,score,row_number() over(order by score desc) from chengjibiao;
rank 具体相等值的行排序相同，充数随后跳跃
select sno,km,score,rank() over(order by score desc) from chengjibiao;
dense_rank 具有相等值的行排序相同，序号是连续的
select sno,km,score,dense_rank() over(order by score desc) from chengjibiao;
decode中的if-then-else逻辑
在逻辑编程中，经常用到if-then-else进行逻辑判断
语法：decode(value,if1,then1,if2,then2,if3,then3,...else)
例一：
create table stop(id number,name varchar2(10),sex char(1));
insert into stop values(1,'张','1');
insert into stop values(2,'王','2');
insert into stop values(3,'李','1');
a.
select id,name,decode(sex,1,'男',2,'女'）from stop;
b.
select id,name,decode(sex,1,'男','女'）from stop;
例二：
create table sales(month char(2),sales_tv number,sales_computer number);
insert into sales values('01',10,18);
insert into sales values('02',28,20);
insert into sales values('03',36,33);
select month,decode(sign(sales_tv-sales_computer),1,sales_tv,0,sales_tv,-1,sales_computer) from sales;
---注意截成二段，可能报错
oracle 行列转换
create table sale(sname varchar2(100),jidu char(2),amount number);
insert into sale values('电视机','01',100);
insert into sale values('电视机','02',200);
insert into sale values('电视机','03',300);
insert into sale values('空调','01',50);
insert into sale values('空调','02',150);
insert into sale values('空调','03',180);
select sname,sum(decode(jidu,'01',amount)) 第一季度,
sum(decode(jidu,'02',amount)) 第二季度,
sum(decode(jidu,'03',amount)) 第三季度,
sum(decode(jidu,'04',amount)) 第四季度
from sale
group by sname;
rownum 使用
对查询结果，输出前若干条记录
注意：只能与<,<=,between and 连用
create table yggz(bh number(6),gz number);
insert into yggz values(1,1000);
insert into yggz values(2,1100);
insert into yggz values(3,900);
insert into yggz values(4,2000);
insert into yggz values(5,1500);
insert into yggz values(6,3000);
insert into yggz values(7,1400);
insert into yggz values(8,1200);
显示3至5行记录:
a.
select * from yggz where rownum<=5
minus
select * from yggz where rownum<3;
b.
select * from (select yggz.*,rownum rn from yggz) where rn>=3 and rn<=5;
删除重复记录
create table stud(sno num
ber(6),sname varchar2(10),sage int);
insert into stud values(1,'AA',21);
insert into stud values(2,'BB',22);
insert into stud values(3,'CC',23);
insert into stud values(3,'CC',24);
insert into stud values(3,'CC',25);
insert into stud values(3,'CC',26);
所有表都隐含rowid,信息有，数据库对象号，数据文件号，数据块号，行号
作用：表示唯一行
方法一：
delete from stud
where sno in (select sno from stud group by sno having(count(*)>1))
and rowid not in (select min(rowid) from stud group by sno having(count(*)>1));
方法二：
delete from stud
where rowid in
(select a.rowid from stud a ,stud b where a.sno=b.sno and a.rowid>b.rowid);
方法三：
delete from stud d
where d.rowid >
(select min(rowid) from stud x where d.sno=x.sno);
group by grouping sets 使用
来进行分组自定义汇总，可以应用它来指定你需要的总数组合。
例如：对于emp表，查询：各部门sal大于2000的员工，进行了汇总，得到各部门的sal总和，以及总共的sal总和
select * from emp;
select case
when a.deptno is null then '合计'
when a.deptno is not null and a.empno is null then '小计'
else ''||a.deptno
end deptno,a.empno,a.ename,
sum(a.sal) total_sal
from emp a where a.sal>2000 group by
grouping sets((a.deptno),
(a.deptno,a.empno,a.ename),());
运行结果：
deptno empno ename total_sal
10 7782 clark 2450
10 7839 king 5000
小计 7450
20 7566 jones 2975
20 7788 scott 3000
20 7902 ford 3000
小计 8975
30 7698 blake 2850
小计 2850
合计 19275