oracle命令

查询

—查询emp表
select from emp;
—将表中名字转换为汉字
select ename “姓名” from emp;
—逗号必须为英文格式
select empno, ename from emp;
—求员工年薪
select ename, sal 12 from emp;
—nvl判断comm是否为空，若为空，将comm值设为0
select ename, sal + nvl(comm, 0) from emp;
—工资默认为由低到高排列
select ename, sal from emp where deptno = 30 order by sal;
—工资由高到低排列
select ename, sal from emp where deptno = 30 order by sal desc;
—统计员工数量count
select count(ename) from emp where deptno = 30;
—平均值，最大值，最小值
select avg(sal), max(sal), min(sal) from emp;
—字母大小写（upper,lower）
select upper(ename) from emp;
select lower(ename) from emp;
—like的使用
select ename from emp where ename like ‘%I%’;
—between and的使用，闭区间
select ename, sal from emp where sal between 800 and 3000;
—大于，小于，大于等于，小于等于
select ename, sal from emp where sal > 3000;
select ename, sal from emp where sal < 3000;
select ename, sal from emp where sal >= 3000;
select ename, sal from emp where sal <= 3000;
—to_char
select from emp where to_char(hiredate, ‘mm’) = 12;
—日期查询（大于，小于，大于等于，小于等于）
select from emp where hiredate > TO_DATE(‘1981-12-3’, ‘YYYY-mm-dd’);
select from emp where hiredate < TO_DATE(‘1981-12-3’, ‘YYYY-mm-dd’);
select from emp where hiredate >= TO_DATE(‘1981-12-3’, ‘YYYY-mm-dd’);
select from emp where hiredate <= TO_DATE(‘1981-12-3’, ‘YYYY-mm-dd’);
—null用法
select from emp where MGR is null;
select from emp where mgr is not null;
—连接操作符||
select ename || ‘ job is ‘ || job from emp;
—去重 distinct
select distinct deptno from emp;
—数字函数，round按照四舍五入运算结果
—dual伪表
select round(5.23) from dual;
—round(,1)其中1为指定小数点后位数
select round(5.635, 1) from dual;
select round(5.655, 2) from dual;
—trunc()函数的用法
select sysdate from dual;
select trunc(sysdate) from dual; —日期年月日
select trunc(sysdate, ‘yy’) from dual; —返回当年第一天
select trunc(sysdate, ‘mm’) from dual; —返回当月第一天
select trunc(sysdate, ‘dd’) from dual; —返回当前年月日
select trunc(sysdate, ‘d’) from dual; —（星期天为第一天）返回当前星期第一天
select trunc(sysdate, ‘hh’) from dual; —当前时间
select trunc(sysdate, ‘mi’) from dual; —没有秒的概念
—trunc(number,num_digits)取整，直接舍去，不存在四舍五入
select trunc(123.658) from dual; —取整
select trunc(123.456, 1) from dual; —取小数点后一位
select trunc(123.456, -1) from dual; —取小数点前一位，并将小数点前一位值设为0
select trunc(123.456, 0) from dual; —取整
—initcap(str)首字母大写，其余小写
select initcap(‘huaMi’) from dual;
—获取字符串长度length(str)
select length(‘huamin’) from dual;
—nvl2（str,value1,value2）如果str为非空，返回value1，否则，返回value2
select nvl2(‘2’, 0, 1) from dual;
select nvl2(‘’, 0, 1) from dual;
—替换replace(str,y,z),在字符串str中找到y，并将y替换为z
select replace(‘huamin’, ‘ua’, ‘i’) from dual;
—substr(str,start,length]),截取str从start开始，指定长度length的字符
select substr(‘huamin’, 2, 3) from dual;
—lpad(str1,x,str2),左侧添加，x为两个字符串的总和长度
select lpad(‘huamin’, 10, ‘chen’) from dual;
—rpad(star1,x,star2)右侧添加，x为两个字符串的总和长度
select rpad(‘huamin’, 10, ‘chen’) from dual;
—ltrim()去左侧空格；去掉左侧从左开始的字符串
select ltrim(‘ huahuaminh’) from dual;
select ltrim(‘huahuaminh’, ‘hua’) from dual;
—rtrim()去右侧侧空格；去掉右侧从右开始的字符串
select rtrim(‘huahuaminh ‘) from dual;
select rtrim(‘huamin’, ‘in’) from dual;
—加减乘除
select sum(sal) from emp;
select avg(sal) from emp;
select sum(sal) / count(1) from emp;
—not and or,between and
— <,>,<=,>=,
—like,in,not in
select from emp where deptno in (20, 10);—员工部门是20和10，in
select from emp where deptno = 20 or deptno = 10;—员工部门是20和10,or
select from emp where deptno not in (20, 10);—员工部门不是20和10,not in
—日期
select ename, to_char(hiredate, ‘fmDD MM YYYY’) hiredate from emp;
select ename, to_char(hiredate, ‘fmDD Month YYYY’) hiredate from emp;
select to_char(sal, ‘$9’) salary from emp;
select to_number(‘1872.34’) from dual;
select to_date(‘20201224’, ‘yyyy-mm-dd’) from dual;
select to_date(‘20201224’, ‘fmDD-MM-YYYY’) from dual;
—十进制和十六进制转换(互换)
select to_char(2463, ‘xxxx’) from dual;
select to_number(‘99f’, ‘xxxx’) from dual;
—select job,sal decode(job,’ANALYST’,sal5) from emp;错误
—分组函数和group by会经常一起使用，对查询的结果进行分组统计
—查询每个部门的员工总数
select deptno, count(1) from emp group by deptno;
—显示每个部门的每个岗位的最高工资和最低工资
select job, max(sal), min(sal) from emp group by job order by job desc;
—显示每个部门每个岗位的最高工资和最低工资
select deptno, job, max(sal), min(sal)
from emp
group by deptno, job
order by deptno;
—查询SMITH工资的等级
select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal
and e.ename = ‘SMITN’;
—查看表结构
desc emp;
—自连接 查找smith员工的经理是谁
select worker.empno, worker.ename, Manager.Mgr, Manager.ename
from emp worker, emp Manager
where worker.mgr = Manager.empno
and worker.ename = ‘SMITH’;
—左连接 左表的所有数据信息展示，右表只展示符合条件的数据
select from emp e, dept d where e.deptno = d.deptno(+) ;
select from emp e left join dept d on e.deptno=d.deptno;
—右连接 右表的所有数据信息展示，左表只展示符合条件的数据
select from emp e, dept d where e.deptno(+) = d.deptno ;
select from emp e right join dept d on e.deptno=d.deptno;
—综合查询
select job, sum(sal) payroll
from emp
where job not like ‘SALES%’ —职位是不是销售
group by job —根据职位进行分组
having sum(sal) > 5000 —薪资总和大于5000
order by sum(sal) desc; —根据薪资总和排序
—group by 列可不在select 列表中()
select avg(sal), deptno from emp group by deptno;
select avg(sal), job
from emp
group by job
having sum(sal) > 3000
order by job desc;
—子查询
select from emp where sal > (select sal from emp where empno = 7566);
select from emp where sal < (select sal from emp where empno = 7566);
select from emp where sal < (select sal from emp where sal < 2975) order by sal desc;
—any运算符
select empno,ename,job feom emp where sal > any (select sal from emp where job = ‘CLERK’) and job <> ‘CLERK’;
— — >ANY 指大于最小值
—all运算
select empno, ename, job, sal
from emp
where sal > all (select avg(sal) from emp group by deptno);
— >ALL 指大于最大值
—

插入

—对象的操作
—插入数据
—日期使用当前系统时间函数sysdate
insert into emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values
(7500, ‘ALLENN’, ‘SALESMAN’, 7900, sysdate, 1900.00, 300.00, 30);
—日期格式转换
insert into emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values
(8500,’YUCHEN’,’SALESMAN’,7900,to_date(‘2020/12/29’, ‘yyyy-mm-dd’),1900.00,300.00,30);
select from emp where empno in(7500,8500);
—允许为空的字段可以赋null值，字段允许为空时，插入也可以不出现该字段，默认赋null值
insert into emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)values
(7502,’YUCHENN’,’SALESMAN’,7900,to_date(‘2020/12/29’, ‘yyyy-mm-dd’),1900.00,300.00,null);
insert into emp(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM)values
(7503,’YUCHENQ’,’SALESMAN’,7900,null,1900.00,300.00);
—不写字段名
insert into emp values
(7504,’QYUCHEN’,’SALESMAN’,7900,to_date(‘2020/12/29’, ‘yyyy-mm-dd’),1900.00,300.00,10);
—复制emp表为emp_backup，将emp表中所有数据插入到emp_backup表中
—如果需要某些条件的数据，则可以通过指出列名和列位
insert into emp_backup —所有的列
select —所有的列
from emp;
insert into emp_backup(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values
(7502,’HUAMINI’,’SALESMAN’,7900,TO_DATE(‘2020-12-29’,’YYYY-MM-DD’),8000,0.00,null);
select * from emp_backup where empno=7502;

修改

—更新emp_backup表中的数据
—更新ename值
update emp_backup set ename=’chen’ where ename=’HUAMINI’;
—同时更新ename和job值
update emp_backup set ename=’chen’,job=’CLERK’ where empno=7502;
update emp_backup set ename=’chenhua’ where ename like’%MINI%’;
update emp_backup set deptno=20 where empno=7502;
—更新的数值从子查询中获取
update emp_backup set(job,deptno)= —set的列与子查询中查询的结果要对应
(select job,deptno from emp where empno=7502)
where empno=7698;
—对emp_backup操作 rollback 后得到的只有表结构，emp全部被拷入的数据被清除，无法撤回

删除

—delete删除表中某一行
delete from emp_backup;
—drop删除表的数据及表的空间,无法撤会
drop table emp_backup;
—删除表中的所有行，但表结构及其列、约束、索引等保持不变
truncate table emp_backup;

提交commit

回滚rollback

创建表

—创建STUDENT表
create table STUDENT
(SNO VARCHAR2(10) NOT NULL,
SNAME varchar2(10) NOT NULL,
SSEX VARCHAR2(10) NOT NULL,
SBIRTHDAY DATE,
CLASS NUMBER NOT NULL);

create table empcopy
as
select *
from emp;

常用的数据对象

sequence队列，procdure存储过程，tigger触发器，tables表，index索引

alter修改表

—add在表中添加列
alter table dept30 —修改表，增加一列使用alter table 表名 add 列名 类型(大小),多列时，使用（）
add (notes1 varchar2(10),
comments varchar2(10));
—modify修改列的类型
alter table dept30
modify (notes number(10),
comments varchar(10));
—rename修改表的字段名，column是关键字，不能省
alter table dept30 rename column notes1 to note;
—rename修改表名
rename dept30 to department;
select * from department;

rowid

—rowid并未存储在表中，所以不支持增删改操作，只能用于查询
select rowid,ename,sal,deptno from emp;
—员工表中工资最高的三位
select from
(
select from emp order by sal desc
)
where rownum<4;
—员工表中工资最高的4-6位
select from
(
select rownum as rn,a. from
(
select * from emp order by sal desc
) a
)
where rn>=4 and rn<=6;

约束

create table emp_bak(
empno number(4),
ename varchar2(10),
deptno number(7,2) not null,
constraint emp_empno_pk
primary key(empno));
create table huaminidept(
deptno number(2),
dname varchar2(14),
loc varchar2(13),
constraint dept_dname_uk unique(dname));

—创建一个stu表（stuno(PR),stuname,deptno(FR)）
create table stu(
stuno number(4),
stuname varchar2(10),
deptno number(5),
constraint stu_stuno_pk primary key(stuno),
constraint stu_deptno_fk foreign key(deptno)
referenceS studept(deptno));

—创建一个stusc表（stuno(PR),deptno(FR),score(NOT NULL)）
create table stusc(
stuno number(2),
deptno number(5),
score number(3) not null,
constraint stusc_stuno_pk primary key(stuno),
constraint stusc_deptno_fk foreign key(deptno)
referenceS studept(deptno));

—创建一个系名表studept（deptno(PR),deptname(unique)）
create table studept(
deptno number(5),
deptname varchar2(13),
constraint studept_deptno_pk primary key(deptno),
constraint studept_deptname_uk unique(deptname));

—在stu表中增加性别gender列
alter table stu add(gender varchar(10),
constraint stu_gender_ck
check(gender in(‘男’,’女’)));
—将stu表中的stuname设为唯一的
alter table stu add(constraint stu_stuname_uk unique(stuname));
—查看表的约束(表名大写)类型
select constraint_name,constraint_type,search_condition
from user_constraints
where table_name=’STU’

—视图View
create or replace view emp_bak1
as select empno,ename,job,deptno
from emp
where deptno=10;

select from emp_bak1;
—视图的列名使用别名
create or replace view salvu30
as select empno employee_name,ename name,sal salary,deptno “部门编号”
from emp
where deptno=10;

select from salvu30;
—在USER_VIEWS中查看用户所有的视图
select from USER_VIEWS;
—创建视图包含了复杂条件
CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.dname, MIN(e.sal), MAX(e.sal),
AVG(e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;

select from dept_sum_vu;
create or replace view empvu20
as select
from emp
where deptno=20
with check option constraint empvu20_ck;

select from empvu20;
update empvu20 set ename=’SMITH’ where ename=’SMITH’;
—drop view 视图名称; 删除视图
drop view empvu20;

队列

create sequence dept_deptno
increment by 1
start with 91
maxvalue 100
nocache
nocycle;
select * from dept_deptno;
insert into dept values(41,’develop’,’NEW YORK’);

create sequence sequence
increment by n —-增量
start with n —-起始
maxvalue n | nomaxvalue —最大
minvalue n | nominvalue —最小
cycle | nocycle —循环
cache n | nocache —存储

创建序列

create sequence book1_sequence start with 1 increment by 1;
SELECT book1_sequence.currval FROM DUAL;

创建触发器

Create or replace trigger book1_trigger
before insert on book1
for each row
begin
select book1_sequence.nextval into :new.bookId from dual;
end ;

创建索引

create index stuind on stu(stuname);
—对emp表中的ename列创建索引
create index empinx on emp(ename);
select from user_indexes;
—查询某表哪些列有索引
SELECT ic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = ‘EMP’;

—创建一个表为employee表，id（非空，PK），name（唯一），
—hiredate (默认系统时间),gender（check ,”男”,”女”，默认值为男）
create table employee(
id number(4) not null primary key,
name varchar2(14) unique,
hiredate date default sysdate,
gender varchar2(10) default(‘男’)
check(gender in(‘男’,’女’))
);

insert into employee(id,name) values(11,’seun’);
select from employee;

创建用户

create user [username]
identified by [password];

—使用sysbda用登录，授权scott用户创建表，创建序列和视图的权限
grant create table, create sequence, create view TO scott;
—1.普通导出：exp scott/H123456@orcl file=’D:\back\orcl_back0313.dmp’
—2.导出所有数据库对象：exp sys/H123456@orcl file=’D:\back\orcl_back0313.dmp’ full=y （登录用户必须是DBA权限）
—3.导出特定用户下的表：exp scott/H123456@orcl file=’D:\back\orcl_back0313.dmp’ owne=
—4.导出特定的表：exp scott/H123456@orcl file=’D:\back\orcl_back0313.dmp’ tables=(emp_bak,dept_bak)