课前准备
解压 Red Hat Enterprise Linux 5.6&Oracle10g&SQL.rar
VMware 安装解压后的linux (里面有配好的oracle)
SQL*PLUS执行环境
命令存放路径: $ORACLE_HOME/bin
oracle安装过程见:
Oracle10g Install for Redhat5.6.docx
打开新linux,登录oracle
#su - oracle
(oracle切换到root的话,直接用 'su -' 就可以了)
在oracle用户中打开终端,执行如下操作
1) [oracle@localhost ~]$lsnrctl start #启动监听
2) [oracle@localhost ~]$sqlplus / as sysdba #进入sql连接
3) 在sql连接中执行 SQL>startup #启动oracle数据库实例
4) SQL>select sysdate from dual;,若出现当前系统日期,则表示数据库启动成功。
编辑命令:[L]IST , [A]PPEND ,[C]HANGE , [I]NPUT , DEL , / (执行),EDITOR
SQL> DEFINE_EDITOR=vi
SQL> select first_name from employees;
SQL> L (查看缓存中的sql,可用edit命令进行编辑,ctrl+d退出来【注意先设置:DEFINE _EDITOR=vi】)
1* select first_name from employees
SQL> A wheredepartment_id = 90 (在原先的基础上加了个限定条件,显示如下,可用‘/’直接进行执行)
1* select first_name from employees where department_id = 90
SQL> C/90/80/
1* select first_name from employees wheredepartment_id = 80
SQL> I (接着上面的语句添加sql语句)
2 where department_id=90;
SQL> /
SQL> DEL (执行一个del会删除一条语句)
SQL> @/home/oracle/sqldemo/summit1.sql --执行sql文件
SQL> spool info (新建一个info文件保存后续操作)
SQL> spool off 退出
在Oracle主目录下回产生一个文件info.lst
----??
SQL> ^? ^H
stty erase ^? | ^H
-------
SQL> clear screen --清屏
-----------------------------iSQL*PLUS执行环境
[oracle@Redhat ~]$isqlplusctl start
登录名:system
密码:shjdora
注:在外部访问,要关闭Linux操作系统防火墙。(setup进行操作)
-------------------PLSQL Developer开发工具& sqldeveloper开发工具
大家对sql应该都比较熟了,以下是一些例子,ppt里没什么精华,去掉了。。。
-----------------------PPT1:RetrievingData Using the SQLSELECTStatement
*******************************************************************************
select first_name, *
from employees;
ERROR atline 1:
ORA-00936:missing expression
*******************************************************************************
关键字名称一般为大写,其他一般为小写(增强可读性)。
SELECT first_name,last_name
FROM employees;
**********************************************************************
select first_name,salary
from employees
where salary+100< 5000;
注:运算也可以用在WHERE条件上,除了From子句都可以。
*******************************************************************************
select sysdate,sysdate+1, sysdate-1, systimestamp, systimestamp+1, systimestamp-1 from dual;
*******************************************************************************
select salary "Sal($)"
from employees;
*******************************************************************************
select department_name ||
', it's assigned Manager Id: '
|| manager_id
AS "Department and Manager"
from departments;
ERROR:
ORA-01756:quoted string not properly terminated
select department_name ||
q'[, it'sassigned Manager Id: ]'
||manager_id
AS"Department and Manager"
from departments;
注:这里的转义符可以是[ ], { }, ( ), or < >。
等价于
select department_name ||
q'\, it'sassigned Manager Id: \'
||manager_id
AS"Department and Manager"
from departments;
等价于
select department_name ||
', it''sassigned Manager Id: '
||manager_id
AS"Department and Manager"
from departments;
*******************************************************************************
select department_id, job_id
from employees;
比较
select distinct department_id, job_id
from employees;
注:这里会把(department_id,job_id)一起重复的数据过滤。
*******************************************************************************
PPT2:Restricting and Sorting Data
SELECT last_name,job_id, department_id
FROM employees
WHERE last_name = 'King' ;
比较
SELECT last_name,job_id, department_id
FROM employees
WHERE last_name = 'KING' ;
SELECT last_name,job_id, hire_date
FROM employees
WHERE hire_date='17-JUN-87';
比较
SELECT last_name, job_id, hire_date
FROM employees
WHERE hire_date='17-JUN-1987';
比较
select LAST_NAME,HIRE_DATE
from employees
where HIRE_DATE='1997-08-17';
ERROR at line 3:
ORA-01861: literal does not match format string
注:字符是大小写敏感的,日期是格式敏感的。
*******************************************************************************
SELECT last_name,salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500 ;
等价于
SELECT last_name,salary
FROM employees
WHERE salary>= 2500 AND salary <= 3500;
*******************************************************************************
SELECT employee_id, last_name, job_id
FROM employees WHERE job_id LIKE '%SA_%'
比较
SELECTemployee_id, last_name, job_id
FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';
*******************************************************************************
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL ;
比较
SELECT last_name, manager_id
FROM employees
WHERE manager_id = NULL ;
注:IS NULL条件用于空值测试,不能用"="等号测试空值 ,因为null不能等于或不等于任何值。
*******************************************************************************
SELECT last_name, job_id
FROM employees
WHERE job_id NOT IN ('IT_PROG','ST_CLERK', 'SA_REP') ;
等价于
SELECT last_name, job_id
FROM employees
WHERE job_id !='IT_PROG' or job_id != 'ST_CLERK' or job_id != 'SA_REP';
The symbols != and ^= can also represent the not equal to condition.
****************************************************************************