oracle基础sql语句

来源：互联网发布：网上约车软件编辑：程序博客网时间：2024/05/19 03:44

修改表结构：

SQL> alter table student add sex varchar(3); //添加一列

SQL> alter table student modify age number(3); //修改一列

SQL> alter table student drop column age; //删除一列

select e.ename,m.ename from emp e, emp m where e.childno=m.empno; //自关联

select ename from emp union select dname from dept; //集合操作 , union 去重复 , union all不去重

select ename, sal from emp where sal>(select sal from emp where ename='clerk'); //子查询

select ename,deptno from emp where deptno in (select deptno from dept where dname like '%C%'); //子查询

工资大于部门平均工资的员工出来

select ename,sal,salavg from emp, (select deptno,trunc(avg(sal),2) salavg from emp group by deptno) b where emp.deptno=b.deptno and sal>salavg;

update focal_price f set f.end_date=to_date('2014-12-31','yyyy-MM-dd') where f.end_date = to_date('2013-12-31','yyyy-MM-dd');

模糊查询

SQL> select * from emp where ename like '%S%'; //--找姓名中包含 S 的员工

SQL> select * from emp where ename like 'S%'; //--找姓名中 S起始的的员工

SQL> select * from emp where ename not like '%S%'; //--找姓名中不包含 S 的员工

SQL> select * from emp where ename like '_M%'; //--找姓名中第二个字母是M的员工

对null的查询

SQL> select * from emp where comm is null; //--查找没有comm的员工

SQL> select * from emp where comm is not null;

条件组合：

SQL> select * from emp where ename like '章%' or ename like '张%'; //-- 找姓章张的

SQL> select * from emp where sal>=2000 and sal <=4000; //-- 工资在2000-4000之间的员工

between and 就是 >= and <=

SQL> select * from emp where sal between 2000 and 4000;

--找800 1200 5000人

SQL> select * from emp where sal=800 or sal=1200 or sal=5000;

SQL> select * from emp where sal in(800,1200,5000);

排序

SQL> select * from emp order by sal asc; ---升序（默认，可以不用asc）

SQL> select * from emp order by sal desc; -- 降序

SQL> select * from emp where sal>1000 order by deptno,sal desc; //-- 找1000以上的员工，按部门排序,同部门的按sal排序（降序)

分组

select * from 表 where 行筛选 group by 分组列 having 组筛选; 一旦使用了group by子句，则select后只能跟分组列及聚合函数

SQL> select deptno from emp group by deptno; //--按照部门分组

SQL> select job from emp group by job; //--公司有几个工种

修改或者插入数据后要（提交）commit或者回滚rollback

insert into emp(name,age) values('张三',23);

update emp set dname='张三1', loc='北京1' where deptno=40;

commit;

delete from dept where deptno=80;

select distinct deptno from emp; //distinct 去重复

select distinct job,deptno from emp; //去掉两个方面都重复的

算术运算符 > < <> >= <= = ！= ！=一般用于数据，<>一般用于日期

select * from emp where hiredate=to_date('1981-4-2','yyyy-MM-dd');

select sysdate from dual; //取数据库的系统时间

select * from emp where hiredate between to_date('1981-1-1','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd');

select * from emp where sal in(800,1600); //只选择800和1600

select * from emp where sal not in(800,1600);

select * from emp where ename like '%T';

select * from emp where comm is null;

select * from emp order by hiredate desc,sal asc; //排序

select * from emp order by hiredate,sal;

select * from LOG_USER_LOGIN t where rownum <= 20 order by t.id desc; -- 查询前20条数据

select * from u_user_info t1, u_user_info t2 where t1.rowid != t2.rowid and t1.real_name=t2.real_name; --查询所有重名数据

select max(sal),min(sal),avg(sal),sum(sal) from emp where deptno=10;

select count(*) from emp where deptno=10;

select sum(comm) from emp;

select deptno,job,max(sal) from emp group by deptno,job;

select deptno ,max(sal) from emp group by deptno having max(sal)>4000;

oracle中表达字符串用单引号来表达

单行函数： (dual 哑表 )

select lower('SQLPLUS') from dual; //lower 转小写

select upper('sqlplus') from dual; //upper 转大写

select concat(first_name , last_name) from s_emp; //concat 连接字符串

substr 求子串
select substr('tarenasd2008' ,1,6) from dual; (取前六个字符)
select substr('tarenasd0603',-2) from dual; (取后两个字符)

select length('tarena') from dual; //length 求字符长度

select to_number('10') from dual; //to_number 字符转数字

select deptno from dept where deptno not in ( select deptno from emp ); //哪个部门里没有员工：

select * from s_emp where id not in ( select manager_id from s_emp); //哪些人是普通员工

第一范式：最简单的一种键方式，一张表只有一个主键
第二范式：表的自连接存在
第三范式：表连接存在，一张表引用其它一张表

约束：
primary key (pk) 主键约束不允许有重复和空值(唯一且非空）
foregin key (fk) 外键约束两张表存在父子关系
unique key (uk) 唯一可以为空(可以多个null它忽略空值)
not null

数据类型：

表名的命令规则：首字母为字母，不得超过30个字符

char(size) 定长

varchar2(size) 可变长

number 所有的数字类型都称为number

number(2,4)小数点后4 位，有效位2位

long 2GB 大文本一个表最多只允许定义一个 long 类型(不建议使用）

clob 大对象形式存放

blob 存二进制大对象(声音，图像之类）

打开Sql软件输入登录名：scott 密码：tiger 连接为：sysDBA(管理员) 数据库：ORCL

进入后打开Users 找到Scott并解锁

重新以普通人员形式进入输入登录名：scott 密码：tiger 连接为：normal 数据库：ORCL

此时就可以用emp文件了

使用Scott用户下的emp表，其中job表示雇员的职位，clerk为办事员、manager为经理、hiredate 字段：受雇日期；sal字段：薪金；comm字段：佣金；
--1、选择部门30中的雇员
select * from emp where deptno=30

--2、列出所有办事员的姓名、编号和部门
select ename,empno,deptno from emp where job='clerk'

--3、找出佣金高于薪金的雇员
select ename from emp where comm>sal

--4、找出佣金高于薪金60%的雇员
select ename from emp where comm>(sal*0.6)

--5、找出部门10中所有经理和部门20中的所有办事员的详细资料
select * from emp where (job='clerk' and deptno=20) or (job='manager' and deptno=10)

--6、找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
select * from emp where (job='clerk' and deptno=20) or (job='manager' and deptno=10) or (sal>=100 and job not in('clerk','manager'))

--7、找出收取佣金的雇员的不同工作
select distinct job from emp where comm is not null

--8、找出不收取佣金或收取的佣金低于100的雇员
select distinct job from emp where comm<100 or comm is null

--12、显示正好为5个字符的雇员姓名
select ename from emp where length(ename)=5

--15、显示所有雇员的姓名，用a替换所有'A'
select replace(ename,'A','a') as ename from emp

--17、显示雇员的详细资料，按姓名排序
select * from emp order by ename

select l.id, nvl(l.loan_Title,''),nvl(l.loan_Money,0), l.rate, '0' as lockYiju, '1' as lockRiqi FROM L_Loan_Info l WHERE l.id = 1498 --nvl函数

--查看数据库表空间使用情况

select sum(bytes)/(1024*1024) as free_space,tablespace_name from dba_free_space group by tablespace_name;

-- Create sequence 建表的自增序列sequence

create sequence U_USER_SN_INFO_SEQ

minvalue 1

maxvalue 99999999999999999

start with 1

increment by 1

cache 20;

--建表的trg (triggers), 对应表和序列，使表主键和序列对应（java框架中可通过hibernate或者Mybatis实现这个功能）

CREATE OR REPLACE TRIGGER "SA".U_USER_SN_INFO_TRG

before insert on U_USER_SN_INFO for each row

declare

begin

SELECT U_USER_SN_INFO_SEQ.Nextval INTO :NEW.ID FROM DUAL;

end U_USER_SN_INFO_TRG;

Oracle 11g 闪回(数据库恢复)

安装的是Oracle11g的数据库，在建表后删除该表，会产生一个类似于"BIN$1Oiy3qm/QJubov1BwBUOgw==$0"的表名，（select * from tab 即可查看到）

如：drop table books;的指令会将表books放到回收站里，
用 flashback table "BIN$1Oiy3qm/QJubov1BwBUOgw==$0" to before drop; 就能恢复被删除的表。

使用实例（不太好用，部分表和数据没回复）