*********************************
** oracle 学习笔记第一天 **
***********************************
ORACLE是什么？
数据库产品
流行的数据库
oracle oracle
IBM db2
sybase ASE
Informxs
oracle 提供什么？
rdbms（关系型数据库管理系统）
中间件 Application Server
DT开发工具
ERP解决方案
关于oracle 版本
企业版 标准版 个人版
version 8-->8i-->9i-->10g
我们目前使用的是oralce 9i 9201 版本（企业版）
i---internet g---gred
关系DB操作
投影（projection）
过滤（filter）
关联（join）
约束（constraint）
索引（index)
oracle 环境
which sqlplus
sqlplus是在安装ORACLE数据后安装的
登陆oracle的命令：
sqlplus 用户名/密码
出现SQL>
表示你已经与数据库建立连接
oracle中表
user table
system table (data dictinary)
user table的DDL操作会触发system table的DML操作
你的第一条select 语句
select table_name from user_tables;(当前用户下用那些表）
运行select的标志是在语句后加分号
重复运行最后一条语句
键入/回车
desc user_tables;(查看当前用户下系统表的结构）
desc是sqlplus命令
sqlplus命令特点：
可以执行的命令：
sql命令，
sqlplus命令，
操作系统命令（实际是一个子进程去执行使用！或host)
是sqlplus特有的命令
可以缩写（desc全写是describe）
如果你在执行语句时报错怎么解决
掌握了以上两条命令你将解决以下问题：
表名不存在
字段名不存在
利用错误号来查错误
！oerr ora 出错代码
例如：SQL>!oerr ora 942 (装完系统后会装一个oerr工具，用于通过错误号来查看错误的具体信息)
!--实现sqlplus 与shell的切换（实际上！是sqlplus的子进程）
host命令 同！使用
SQL>! 回车
shell>
shell>exit
回到sqlplus
掌握 s_emp，s_dep表结构
show user 显示当前登陆的身份.
set pause on
set pause off 分页显示.
clear screen 清屏
oracle中默认日期和字符是左对齐，数字是右对齐
table or view does not exist ; 表或示图不存在
edit 命令用于自动打开vi修改刚修执行过的sql的命令。
修改方法二：
l 3 先定位到行
c /旧串/新串
1,$s/旧串/新串/g
保存刚才的sql语句： save 命令 第二次保存时要替换之前的角本 save 文件名 replace
把刚才保的sql重新放入 buffer中
spool 文件名
此命令会把所有的操作存在某个文件中去
spool off
练习1：查看s_emp表中员工的年工资
select first_name , salary*12 salary from s_emp;
select first_name , salary*12 "annual salary" from s_emp;
给列起别名的命令：
方式1字段后空格跟别名
方式2利用关键字 as
注：使用双引号将大小写敏感保持引号内容原样输出，如果不加双引号时，默认为大写
拼接操作：
select first_name||','||last_name from s_emp ;
select first_name||last_name "employees" from s_emp ;
oracle中表达字符串用单引号来表达：
select first_name||' '||last_name from s_emp;(在两个字段之间拼接一个空格)
查看当前用户所有的表：
练习2：显示当前用户下所有表在每个表前有select * from字符串
select 'select * from' || table_name ||';'
from user_tables;
动态的拼sql语句写入文件
spool test.sql
select 'select * from' || table_name ||';'
from user_tables;
spool off
注：spool命令用储存终端
动态生成脚本例子
shell>edit crtsql.sql
set head off--去除表头
set feed off -- 去除最后一行
set echo off -- 去除执行SQL的显示
spool test.sql
select 'select * from' || table_name ||';'
from user_tables;
spool off;
存盘
注:
运行脚本
方式1：SQL>@crtsql.sql
方式2：shell> sqlplus 用户名/密码 @srtsql.sql
练习3：(查出s_emp表中所有员工的一年的总收入)
select first_name , salary*12*( 1+nvl(commission_pct/100 , 0 ) ) " year salary " from s_emp;
nvl函数 将空值转为实际意义的值专用于处理空值的影响.
nvl函数实现重载支持date、char、number
空值可认为无穷大
练习4：列出公司所有部门（排重）
select distinct name
from s_dept;
练习5：不同的部门不同的职位（复合唯一）
select distinct dept_id,title
from s_emp;
column 定义格式化输出
SQL>column last_name
SQL>column last_name Heading Employee|Name' format a15;
SQL>column last_name clear;
SQL>column salary justify left format $99,999.00 ( 定义工资的显示形式 )
L-local currency（受本地环境变量影响）
set LANG = AMERICAN_AMERICA.US7ASCII//English
setenv NLS_LANG 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'//chinese
$ echo $LANG
zh_CN.hp15CN
$ echo $NLS_LANG
simplified chinese_china.zhs16cgbk
下午
ORDER BY 排序 升序和降序 ASC 升序(默认) DESC 降序
select * from s_emp order by dept_id , salary desc 部门号升序，工资降序
关键字distinct也会触发排序操作。
过滤操作： where 子句
select * from s_emp where dept_id=42; 查看部门号为42的所有员工
select * from s_emp where salary>1000 查看工资高于1000的所有员工
select salary from s_emp where first_name='Geroge' 找出名字为Geroge的员工的工资数
select table_name from user_tables where table_name='S_EMP'; 查某个具体表名时，表名的字符串必须要为大写
日期的默认的格式 DD-MON-RR(缺省使用单引号）
BETWEEN AND 在什么之间 NOT BETWEEN AND 注意区间：[ ]是一个闭区间
IN( LIST) 在某个集合中 NOT IN (list) 空值会有影响 (等于list其中任何一个就行，为提高效率常把比例高的放在前面)
LIKE 模糊配置 NOT LIKE 通配比较
IS NULL 是空
AND
OR
NOT
练习4:(找出表名以S_开头的所有表)对于一些特殊字符，要用到escape转义
select table_name from user_tables where table_name like 'S\_%' escape '\';
单行函数： (dual 哑表 )
字符函数：
lower 转小写 select lower('SQLPLUS') from dual;
upper 转大写 select upper('sqlplus') from dual;
initcap 首字符大写 select initcap('tarena') from dual;
concat 连接字符串 select concat(first_name , last_name) from s_emp;
substr 求子串 select substr('tarenasd2008' ,1,6) from dual; (取前六个字符) select substr('tarenasd0603',-2) from dual; (取后两个字符)
length 求字符长度 select length('tarena') from dual;
nvl 空值函数
select first_name , substr(first_name , -2 ) from s_emp; (查出s_emp表中所有用户名字的最后两个字符)
练习5： select first_name , salary from s_emp where lower(first_name)='george';
数值函数：
round 函数 select round(45.935, 2) from dual; 不带参数时默认为0位小数
trunc 函数 select trunc(45.995, 1) from dual;
日期函数：DD-MON-RR 日期格式敏感
世纪、年、月、日
sysdate 返回当前系统时间 select sysdate from dual;
更改当前会话的设置格式：
alter session set nls_date_format='yyyy mm dd hh24:mi:ss';
select sysdate-1, sysdate+1, sysdate , sysdate+1 from dual; 注意单位是以天为单位
MONTHS_BETWEEN (DATE1 , DATE2 ) 求两个日期之前相差的月数
add_months(date , 4 ) 在 date上再添加4个月
select next_day(sysdate,'FRIDAY') from dual ; 求这个日期的下一个FRIDAY
last_day 求月的最后一天
round 函数: select round(sysdate, 'MONTH') from dual; 参数可以为： MONTH YEAR(看上半年还是下半年)
select trunc(last_day(sysdate)+1) from dual;
select add_months(trunc(sysdate, 'MONTH'), 1 ) from dual ;
关于日期的两种形式：
转换函数：
to_char
从数字转化为char
从日期转化为char to_char(date, 'fmt' ) select to_char(sysdate, 'yyyy mm dd hh24:mi:ss') from dual;
select to_char(sysdate ,'YEAR MONTH dy
to_date
字符转日期 select to_date('2000 11 20', 'yyyy mm dd ') from dual;
select round(to_date('10-OCT-06' ,'dd-mon-RR') ) from dual;
to_number
字符转数字
select to_number('10') from dual
怎么插一个日期到表里
方式1：to_date()
方式2：缺省日期格式