oracle笔记

客户端

DOS命令行下,sqlplus用户名:scott,密码:tiger

sqlplus scott/tiger

更改用户：

sqlplus sys/tiger as sysdba

alter user scott account unlock;

 

table_structures

1.desc 表名

2.select * from 表名

 

select语句

1.select ename,sal*12 annual_sal from emp;

select ename sal* 12 "annual sal" from emp;

双引号可以保持原大小写，并且中间可以加空格，不加全变大写

2.desc dual

select 2*3 fro dual;

dual 是系统自带的一张空表，计算数据时可以使用该表

3.任何还有空值的算术表达式的计算机结果是空值

4.select ename || sal from emp;   -- || 表示连接符

5.select ename || 'abcdefg' from emp;

6.select ename|| 'abcd''efg' from emp; 

当字符串中含有单引号时，可用两个单引号代表一个单引号

7.set linesize 200;--用设定每行显示的宽度

8.set pagesize 30;--设置显示的页数

 

distinct

1.select distinct deptno from emp;

2.select distinct deptno,job from emp;

 

where

1.select * from emp where empno =10;

2.select * from emp where empnp <>10;

3.select * from emp where ename = 'hebe';

4.select * from emp where sal (not) between 800 and 1500;

5.select * from emp where comm is (not) null;

6.select * from emp where ename (not) in ('smith','philip','jay');

7.select * from emp where ename like '-A%';

8.select * from emp where ename like '_\%a%';

--系统默认转义字符是\,可以自己指定转义符.

select * from emp where ename like '_$%a%' escape '$';

 

order by 

select * from dept;

select * from dept order by dept desc;(默认为asc)

select ename,sal,deptno from emp where sal>2000 order by deptno asc,ename desc;