尚学堂马士兵oracle笔记53节完整版..doc

尚学堂马士兵老师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/密码 as sysdba??????? 2. alter user scott account unlock;(解锁账号) 第三课：table structure???????(系统自带的表有emp、salgrade、dept、bonus、dual)??????? 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 ,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); 模糊查询 like ： %代表任意数量的任意字符 _代表一个任意字符 ??????? select ename from emp where ename like _A%; 如果要查询含有%的，要用转义字符 \ 转义字符可以自定义： escape 自定义的转义字符 比如: select ename from emp where ename like %$a% escape $; 第七课: order by??????????????? select * from dept; 默认按升序(asc)排列，要按降序(desc)用如下语句：???????? select * from dept order by dept desc; ???????? 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 sal800??????? 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 e