oracle10g笔记

SQL>startup mount正常启动
SQL>alter database archivelog
SQL>alter database open
SQL>startup nomount丢失恢复启动
SQL>shutdown immediate正常关闭
SQL>shutdown等待关闭
SQL>shutdown transactional等待立即关闭
SQL>DEL 4删除缓存区某一行的命令，比如第四行
SQL>DEL 2 3
SQL>save c:\oracle\test.txt将缓存区中的命令保存到文件中
SQL>get c:\oracle\test.txt获取文件中的命令
SQL>edit 调用记事本编辑刚刚输入的语句
SQL>COLUMN deptno HEADING "编号"为查询的某一列别名显示，或者写成COL方法
SQL>UPDATE aa SET a3='aa';
SQL>SELECT LENGTH(A2),LENCTH(A3) FROM aa;
SQL>SELECT SUBSTR('abcdefg',2,3) FROM dual;
SQL>SELECT SUBSTR('sbcdefg',LENGTH('qbcdefg')-3+1,3) FROM dual;
SQL>SELECT pub,books_name,sum(price*qty) FROM books GROUP BY pubs,books_name;
SQL>SELECT pub,sum(price*qty) FROM books GROUP BY pub,books_name;
SQL>SELECT pub,sum(price*qty) FROM books WHERE price>30 GROUP BY pubs;
SQL>SELECT * FROM aa WHERE a1 LIKE 'a_';
SQL>INSERT INTO abc VALUES('bcd','123')
SQL>UPDATE abc SET b='ttt'
SQL>UPDATE abc b='YYY' WHERE a='abc';
SQL>DELETE FROM abc WHERE a='abc';
SQL>SELECT * FROM abc;
SQL>SELECT * FROM dept;
SQL>exit退出sqlplus模式，退出后缓存中的别名列自动消失
SQL>sqlplus scott/tigger，通过scott方式登录，不需要引号
SQL>COL deptno FORMAT 999,999,999格式化显示
SQL>connect "sys/test1234 as sysdba"通过sysdba查看数据文件的大小，以999,999,999格式显示
SQL>SELECT TO_NUMBER('00333') FROM dual;
4.聚集函数
SQL>SELECT * FROM books
SQL>SELECT max(price) FROM books;
SQL>SELECT min(price) FROM books;
SQL>SELECT sum(price) FROM books;
SQL>SELECT * FROM dept;
SQL>DESCRIBE用于描述一个对象，或者写成DESC
SQL>DESC dept比如描述dept表的结构
SQL>COL dname FORMAT A10为某列格式化显示
Sபைடு நூலகம்L>COL dname FORMAT A1O HEADING "部门名称"
oracle10g笔记
一、oracle数据库结构
服务器参数文件oracle\product\10.1.0\Db_2\database\spfile*.ora
oracle\10.1.0\oradata\
1.参数文件
2.*.CTL 控制文件
3.*.DBF 数据文件
4.*.LOG 日志文件
常用系统函数
1.字符
SQL>SELECT LENGTH('ABCDEF') FROM dual;
SQL>SELECT LTRIM(' ABCDEF') FROM dual;
SQL>SELECT RTRIM('ABCDEF ') FROM dual;
SQL>SELECT TRIM(' ABCDEF ') FROM dual;
SQL>SELECT pub,sum(price*qty) FROM books GROUP BY pub HAVING sum(price)>60;
SQL>SELECT a1,count(a1) FROM aa GROUP BY a1 HAVING COUNT(a1)>1;
模糊查询
SQL>SELECT * FROM aa;
SQL>SELECT a1,nvl(a2,'未输入') a2 FROM aa;对空值处理，填充'未输入'
SQL>SELECT * FROM aa WHERE a2=NULL;
SQL>SELECT * FROM aa ORDER BY a1 ASC;
SQL>SELECT * FROM aa ORDER BY a1 DESC;
SQL>edit c:\oracle\spool.txt用记事本打开spool.txt文件，查看保存是否成功
二、结构化查询语句
1.DDL
CREATE
ALTER
DROP
2.DCL
GRANT
REVOKE
3.DML
SELECT
INSERT
DELETE
UPDATE
SQL>connect scott/tiger
SQL>SELECT DISTINCT a1 FROM aa;
SQL>SELECT ALL a1 FROM aa;
分组语句
SQL>SELECT * FROM books;
SQL>SELECT sum(price*qty) FROM books
SQL>SELECT sum(price*qty) FROM books GROUP BY pubs;
3.转换
SQL>SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') FROM dual;
SQL>SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM dual;
SQL>SELECT TO_DATE('12-3月-04') FROM dual;
SQL>shutdown abort强制关闭
SQL>help index查看sqlplus帮助命令
SQL>SELECT * FROM dept;
SQL>SET SQLBLANKLINES ON设置sqlplus行输入连接为开
SQL>SELECT * FROM dept WHERE deptno=&tt设置输入参数
查询pub列名称相同的books_name的统计结果，显示为计数
SQL>spool c:\oracle\spool.txt将查询结果保存到文件中，表示开始点
以下是要查询保存的语句，将查询结果保存到spool.txt中
SQL>SELECT * FROM books;
SQL>spool off表示查询结果保存结束点
SQL>connect tt/tt11;
SQL>connect scott/tiger;
SQL>SELECT * FROM e;
SQL>SELECT SUM(DECODE(SEX,'男',1,0)) 男人数,SUM(DECODE(SEX,'女',1,0) 女人数 FROM e;判断计数，为真加1，否则加零
5.大共享池
6.固定SGA
ORACLE逻辑结构
表空间tablespace(块block、盘区extent、段segment)
表空间tablespace(数据文件datafile)
二、sqlplus的使用方法
isqlplus打开方式http://主机名:oracle访问端口/isqplus
c:\>sqlplus "sys/password as sysdba"登录系统
SQL>DESC v$controlfile描述控制文件
SQL>SELECT status,name FROM v$controlfile查看控制文件
SQL>DESC v$datafile描述数据文件