连接Oracle
最高权限的用户不做校验
conn scott/tiger as sysdba （加了as sysdba之后，后面scott的密码不管对不对，都能连接进去）
conn scott/tiger 不加as sysdba之后，密码不对是连接不上去的
oracle对高高权限的用户是不做校验的，不管密码对不对都能连接上去oracle
解锁用户
alter user scott identified by oracle account unlock;
sql:
select * from tab; 查看当前用户有哪些表
oracle大部分都是不区分大小写的
只有一小部分才区分大小写
去重 distinct select distinct(empno) from emp;
select empno,sal*12 from emp;
select empno,sal*12+100 from emp;
select 是控制列，where是控制有多少行
select empno,sal from emp where sal in(1600,1500); in是或者1500或者是1600
select empno,sal from emp where sal=1600 or sal=1500;
select empno,sal,deptno from emp where sal between 1000 and 3000 and (deptno in (10,20) );
各个and，执行顺序，现在Oracle不分了
日期
修改时间格式
alter session set nls_date_format='yyyy-mm-dd' session是当前会话有效
alter system (永久生效）
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select empno,hiredate from emp where hiredate>to_date('1981-02-20','yyyy-mm-dd')
select empno,hiredate from emp where hiredate>'1981-02-20'; oracle内部做了隐式转换，这样也可以，但性能不会太好
ORder by
select empno,sal from emp order by 2 desc
select empno,sal from emp where sal >1000 and sal < 4000 order by sal desc;
模糊查询Like 在做like 模糊查询的时候，区分大小写的
表的连接
复杂查询
select ename from emp where sal>(select sal from emp where ename='SMITH'); where后面接一个子查询
2个表进行关联的查询
没有重名的就可以不用写表名
select ename from emp,dept where emp.deptno=dept.deptno and dname='SALES';
select ename from emp where deptno=(select deptno from dept where dname='SALES');
count(*)是所有的
count(empno)
count(mgr)
select deptno,count(*） from emp group by deptno
select 有几个,group by 就有多少个
薪资最高的前3个人
select * from (select ename,sal from emp order by sal desc ) where rownum<4;
select ename,sal from emp where rownum<4 order by sal desc; 这样就实现不到预想的结果
ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
SMITH 800
create table emp01 (
2 empName varchar2(50),
3 empSex CHAR(2),
4 empJoinDate DATE,
5 empSal number(10,2)
6 );
create table emp01_bak select * from emp01;
create table emp01_bak select * from emp01
*
ERROR at line 1:
ORA-00922: missing or invalid option
create table emp01_bak as select * from emp01; 应该加 as
表的重命名
alter table emp01_bak rename to emp01_copy;
SCOTT@orcl 19-MAY-13>select to_char(sysdate,'yyyy-mm-dd') from dual;
TO_CHAR(SY
----------
2013-05-19
SCOTT@orcl 19-MAY-13>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2013-05-19 04:17:14
SCOTT@orcl >desc student
Name Null? Type
----------------------------------------- -------- ----------------------------
XH NUMBER(4)
XM VARCHAR2(10)
SEX CHAR(2)
BIRTHDAY DATE
SAL NUMBER(7,2)
insert into student values(2,'S2','M',to_date('2013-05-19','yyyy-mm-dd'),200);
create table stu_1 as select birthday from student;
create table stu_2 as select * from student where 1=2;
select keyword from v$reserved_words; 查看关键字
插入语句的练习
insert into a(ename,cdate) values('landy',to_date('2010-10-1','yyyy-mm-dd') );
insert into a(id) values(10);
insert into a values(2,'a',null);
SCOTT@orcl 19-MAY-13>select * from a;
ID ENAME CDATE
---------- ------------------------------ ---------
1 a
2 a
10
landy 01-OCT-10
3 aaa 10-JAN-01
显示时间是按oracle默认的时间格式，如果需要改成我们自己需要的格式，改参数nls_date_format
alter session set nls_date_format='yyyy-mm-dd'; (session是本会话session生效）
SCOTT@orcl >select * from a;
ID ENAME CDATE
---------- ------------------------------ ----------
1 a
2 a
10
landy 2010-10-01
3 aaa 2001-01-10
create table x as select * from dept where 2=3;
insert into x select * from dept;
create table y as select deptno,dname from dept where 1=2;
insert into y select deptno,dname from dept;
insert into y select deptno,dname from dept where deptno=10;
记录当前有哪些约束
约束类型
- NOT NULL
- UNIQUE
- PRIMARY KEY 不能为空，唯一，默认会创建索引，一个表只能创建一个主键
- FOREIGE KEY 外键的值必须是在主键里面有的，否则会报错，保持一致性
- CHECK
user_constraints
select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints;
alter table ep add constraint che_sal check (sal>=0);
insert into ep values('landy',-100); 加了check约束之后，sal插入负值就会报错
insert into ep values('landy',-100)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHE_SAL) violated
把所操作的 命令导入到某个文件里面用下面的方法
spool /home/oracle/1.txt
spool off
薪资最高的前3个人
SCOTT@orcl 19-MAY-13>select rownum,empno,sal from (select rownum rownuminside,empno,sal from emp_cc order by sal desc) A where rownum<4 ;
ROWNUM EMPNO SAL
---------- ---------- ----------
1 7839 5000
2 7788 3000
3 7902 3000
SCOTT@orcl 19-MAY-13>select rownum,A.* from (select rownum rownuminside,empno,sal from emp_cc order by sal desc) A where rownum<4 ;
ROWNUM ROWNUMINSIDE EMPNO SAL
---------- ------------ ---------- ----------
1 9 7839 5000
2 8 7788 3000
3 13 7902 3000
SCOTT@orcl 19-MAY-13>select * from (select rownum,empno,sal from emp_cc order by sal desc) A where rownum<4 ;
ROWNUM EMPNO SAL
---------- ---------- ----------
9 7839 5000
8 7788 3000
13 7902 3000
