马士兵orcal笔记
尚学堂马士兵老师oracle笔记 (2008-10-30 10:17:39)
第一课：客户端
        1. Sql Plus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。
        2. 从开始程序运行:sqlplus，是图形版的sqlplus.
        3. http://localhost:5560/isqlplus
      
        Toad：管理， PlSql Developer:

第二课：更改用户
        1. sqlplus sys/bjsxt as sysdba
        2. alter user scott account unlock;(解锁)
第三课：table structure
      
        1. 描述某一张表：desc 表名
        2. select * from 表名
第四课：select 语句：
       1.计算数据可以用空表：比如：.select 2*3 from dual
       2.select ename,sal*12 annual_sal from emp;与select ename,sal*12 "annual sal" from emp;区别，加双引号保持原大小写。不加全变大写。
     
       3. select ename || "abcd" 如果连接字符串中含有单引号，用两个单引号代替一个单引号。
第五课：distinct
        select deptno from emp;
        select distinct deptno from emp;
        select distinct deptno from emp;
        select distinct deptno ,job from emp
        去掉deptno,job两者组合的重复。更多的项，就是这么多项的组合的不重复组合。
第六课：Where
        select * from emp where deptno =10;
        select * from emp where deptno <>10;不等于10      
        select * from emp where ename ='bike';
        select ename,sal from emp where sal between 800 and 1500 (>=800 and <=1500)
        空值处理:
        select ename,sal,comm from emp where comm is (not) null;
        select ename,sal,comm from emp where ename ( not)in ('smith','king','abc');
        select ename from emp where ename like '_A%';_代表一个字母,%代表0个或多个字母. 如果查询%
        可用转义字符.\%. 还可以用escape '$'比如:select ename from emp where ename like '%$a%' escape '$';
第七课: orderby
      
         select * from dept;
         select * from dept order by dept desc;(默认:asc)
         select ename,sal,deptno from emp order by deptno asc,ename desc;
第八课: sql function1:  
        select ename,sal*12 annual_sal from emp
        where ename not like '_A%' and sal>800
        order by sal desc;
        select lower(ename) from emp;
        select ename from emp
        where lower(ename) like '_a%';等同于
        select ename from emp where ename like '_a%' or ename like '_A%';
        select substr(ename,2,3) from emp;从第二字符截,一共截三个字符.
        select chr(65) from dual 结果为:A
        select ascii('a') from dual 结果为:65
        select round(23.652,1) from dual; 结果为: 23.7