尚学堂oracle笔记 - 蓝色的博客 - JavaEye技术网站
http://mengqingyu.javaeye.com/blog/433445[2010/7/19 14:37:42]
2009-07-26
尚学堂oracle笔记
文章分类:数据库
1. 第一课:客户端
2. 1. Sql Plus(客户端),命令行直接输入:sqlplus,然后按提示输入用户名,密码。
3. 2. 从开始程序运行:sqlplus,是图形版的sqlplus.
4. 3. http://localhost:5560/isqlplus
5.
6. Toad:管理, PlSql Developer:
7.
8. 第二课:更改用户
9. 1. sqlplus sys/bjsxt as sysdba
10. 2. alter user scott account unlock;(解锁)
11. --创建用户
12. create user username identified by password;
13. grant dba to username;
14. drop user username cascade;
15. 第三课:table structure
16. 1. 描述某一张表:desc 表名
17. 2. select * from 表名
18. 第四课:select 语句:
19. 1.计算数据可以用空表:比如:.select 2*3 from dual
20. 2.select ename,sal*12 annual_sal from emp;与select ename,sal*12 "annual sal" from emp;区
别,加双引号保持原大小写。不加全变大写。
21.
22. 3. select ename || "abcd" 如果连接字符串中含有单引号,用两个单引号代替一个单引号。
23. 第五课:distinct
24. select deptno from emp;
25. select distinct deptno from emp;
26. select distinct deptno from emp;
27. select distinct deptno ,job from emp
28. 去掉deptno,job两者组合的重复。更多的项,就是这么多项的组合的不重复组合。
29. 第六课:Where
30. select * from emp where deptno =10;
31. select * from emp where deptno <>10;不等于10
32. select * from emp where ename ='bike';
33. select ename,sal from emp where sal between 800 and 1500 (>=800 and <=1500)
34. 空值处理:
35. select ename,sal,comm from emp where comm is (not) null;
36. select ename,sal,comm from emp where ename ( not)in ('smith','king','abc');
37. select ename from emp where ename like '_A%';_代表一个字母,%代表0个或多个字母. 如果查询%
38. 可用转义字符.\%. 还可以用escape '$'比如:select ename from emp where ename like '%$a%' esc
ape '$';
39. 第七课: orderby
40.
41. select * from dept;
42. select * from dept order by dept desc;(默认:asc)
43. select ename,sal,deptno from
emp order by deptno asc,ename desc;
44. 第八课: sql function1:
45. select ename,sal*12 annual_sal from emp
46. where ename not like '_A%' and sal>800
47. order by sal desc;
48. select lower(ename) from emp;
49. select ename from emp
50. where lower(ename) like '_a%';等同于
51. select ename from emp where ename like '_a%' or ename like '_A%';
52. select substr(ename,2,3) from emp;从第二字符截,一共截三个字符.
53. select chr(65) from dual 结果为:A
54. select ascii('a') from dual 结果为:65
- 1
- 2
前往页