18 oracel数据库浅谈

1 使用什么工具连接oracel数据库? sqlplus?还是? --PLSql
2 首先安装oracel数据库 在安装连接工具连接,或者直接使用cmd …

连接oracel数据库

telnet 192.168.68.27 //数据库所在服务器
root/root //服务器账号和密码
sqlplus root/root //使用sqlplus登录数据库 //数据库访问账户和密码

与mysql一致的命令不写

Mysql的数据类型

数值类型
tinyint smallint int bigint
float double
字符串类型
char(n) varchar(n) text 大文本（长文本）类型
日期类型
date：年月日
time：时分秒
datetime：年月日 时分秒 --字符串类型
timestamp：时间戳(实际存储的是一个时间毫秒值)

Oracel数据类型

数值类型
number(n) 数字( 最长 n 位 )
number(n , m) 浮点数( 总长 n 为 , 小数点后 m 位 )
字符串类型
char(n) varchar(n)
varchar2(n) Oracle 自己定义的变长字符串
日期类型
date

小结:数据类型区别

最大的区别就是数值类型,oracel只需要**number(n)**即可

对空值处理的区别

mysql 是 ifnull(bonus,0)
oracel 是 nvl(d1,d2) //如果 d1 为 null 则用 d2 替代
示例
select ename , nvl(job, ‘no position’ ) from emp_02; // 如果没有职位 , 显示’no position’ , 如果有职位 , 显示员工的职位
select empno, ename || job detail from emp_xxx ; //"||" 符号表示两个数据串接起来 , 类似于 Java 中的两个字符串之间的+号
create table emp_xxx as select * from emp_yyy ; //复制表 emp_YYY 为 emp_XXX
select distinct job from emp_xxx ; // ** distinct 必须( 只能 )跟在 select 后边 **
select distinct deptno, job from emp_xxx ; //distinct 指所有列的唯一组合
select * from emp_xxx where lower(job) = ‘analyst’ ; //将字符数据转换为小写( 如果不知道职位的大小写形式 , 可以使用 lower 函数 , 忽略大小写 ) --upper(job) 将数据转换为大写
select count() from user_tables where table_name like ‘%EMP%’ ;//查询数据库中有多少个名字中包含 ‘EMP’ 的表
select count() from user_tables where table_name like ‘S_%’ escape ‘’ ; //查询数据库中有多少个名字中以 ‘S_’ 开头的表
注意：SQL 语句大小写不敏感 , 数据大小写敏感 mysql和oracel中都是

Oracel连接工具Sqlplus的命令 --不需要写“ ; ”分号 怎么感觉主要是连接工具的区别…

设置行的长度
set linesize 200 //显示 200 个字符再换行
oracel可以设置列的宽度
column name format a6 //设置列的宽度为6个字符 这里不能设置数字格式
设置分页显示
set pagesize 100 //每100行数据分页显示
set pages 0 //0表示不分页
查看当前用户账户 show user

SQL 语言的分类

数据定义语言 DDL( Data Definition Language ) , 是 SQL 语言集中负责数据结构定义与数据库对象定义的语言 , 主要有 create、alter、drop 和 truncate 四种常用语句。
DDL 对数据结构起作用。
create 数据库对象的创建
alter 修改数据库对象
drop 删除数据库对象
truncate 清空表数据
数据操纵语言
数据操纵语言DML( Data Manipulation Language ), 用户通过它可以实现对数据表的基本操作 ,即对表中数据的增、删、改。
DML 对数据起作用。
insert 插入操作
update 更新操作
delete 删除操作
数据查询语言
数据查询语言 DQL( Data Query Language ) , 用户主要通过它实现对数据的查询操作。
select 查询操作
事务控制语句
事务控制语句是用来对 DML 操作进行确认的。
commit 提交数据
rollback 数据回滚
savepoint 保存点
数据控制语言 DCL
grant / revoke ( 不用户权限相关 , 后续讲解 )

oracel中函数

**round( 数字 , 小数点后的位数 )**用于数字的四舍五入
select ename,round(salary,2) from emp;
**trunc( 数字 , 小数点后的位数 )**用于截取
mod 取模
select salary , mod( salary , 5000 ) from emp;

日期函数 sysdate // select sysdate from dual ; //–dual 为虚表 获取系统当前时间
日期数据相减 // select ename , hiredate , ( sysdate - hiredate ) days from emp;
日期数据相减 , 得到两个日期之间的天数差 ,不足一天用小数表示。可以用 round 函数处理一下
日期函数 months_between( ) //select ename , hiredate ,months_between(sysdate , hiredate) months from emp;
小数都可以用round()函数处理
日期函数 add_months( ) //select add_months(sysdate, -12) from dual; //计算 12 个月之前的时间点
日期函数 last_day ( ) // select last_day(sysdate) from dual; //计算本月的最后一天
转换函数 to_char(日期数据 , 格式): 把日期数据转换为字符数据
select to_char( sysdate , 'yyyy-mm-dd hh24：mi：ss ’ ) from dual ;
select to_char( sysdate , 'year month dd day dy ’ ) from dual ; //把时间数据按指定格式输出
select to_char( sysdate , ‘yyyy/mm/dd’ ) from dual ;
转换函数 to_date() //insert into emp_xxx( empno , ename , hiredate ) values( 1012 , 'amy ’ , to_date( '2011-10-10 ’ , 'yyyy-mm-dd ’ )) ;
to_number //
**小结：转换函数 to_date()和 to_char() *****
to_date()和 to_char()是时间处理的函数
to_date 将字符串数据 按指定格式 转换为 日期数据
to_char 将日期数据 按指定格式 转换为 字符串数据
函数 coalesce( 参数列表 ) 返回参数列表中第一个非空参数 , 参数列表中最后一个值通常为常量
select ename , bonus , salary ,coalesce( bonus , salary0.5 , 100 ) bonus rom emp;

1. 如果 bonus 不是 null , 发年终奖金额为 bonus
2. 如果 bonus 是 null , 发年终奖金额为 salary * 0.5
3. 如果 bonus 和 salary 都是 null, 发 100 元安慰一下
   case 语句
   select ename , salary , job ,
   case job when ‘Analyst’ then salary * 1.1 --注意这里没有“ , ”
   when ‘Programmer’ then salary * 1.05
   when ‘clerk’ then salary * 1.02
   else salary --else 相当于 Java 中 case 语句的 default
   end new_salary --end 是 case 语句的结束标识
   from emp; --new_salary是从case开始到end结束这部分的别名
4. 如果职位是 Analyst：加薪 10%
5. 如果职位是 Programmer：加薪 5%
6. 如果职位是 clerk：加薪 2%
7. 其他职位：薪水不变
   decode 函数
   decode()函数是Oracle 中等价于 case when 语句的函数 , 作用同 case 语句相同。
   decode 函数语法如下：
   decode(判断条件 , 匹配 1 , 值 1 , 匹配 2 , 值 2 , … , 默认值)
   表达的意思是：如果判断条件 = 匹配 1 , 则迒回值 1
   判断条件 = 匹配 2 , 则迒回值 2
   select ename , salary , job ,
   decode( job ,
   ‘Analyst’ , salary * 1.1 ,
   ‘Programmer’ , salary * 1.05 ,
   ‘clerk’ , salary * 1.02 ,
   salary) new_salary --new_salary是这部分的别名
   from emp;
   ** 排序可以用列名, 列别名 , 表达式 , 函数 , 甚至可以用数字 , 表示按第几列排序 **
   数据字典 user_tables --只读 , 不能改
   select count() from user_tables ;
   select count(hiredate) from emp_xxx ; //入职时间不是 null 的数据总数 count 函数忽略空值
   select lpad( ename , 10 , '’ ) from emp_xxx ; //将 ename 字段设置为 10 个长度 , 如果丌够左边用“*”号补齐
   通用函数：nvl / coalesce / decode
   ** All * **
   查询谁的薪水比所有叫张无忌的薪水都高 --比最高的都要高
   select ename from emp where salary > ALL( select salary from emp where ename = ‘张无忌’ );
   ** Any * **
   哪些人的薪水比任何一个叫张无忌的员工工资高(比只要有一个叫张无忌的高即可) --比最低的高
   select ename from emp where salary > ANY( select salary from emp where ename = ‘张无忌’ );
   单行比较运算符和 All、Any、in
   根据子查询返回的结果的行数选择使用：
   返回一行 > < >= <= = <>
   返回多行 >ALL >ANY <ALL <ANY in
   exists 关键字判断子查询有没有数据返回 , 有则为 ture , 没有则为 false
   not in (列表)：如果列表中有 null 值 , 将没有结果返回 ;in(列表)没有关系。
   结果集操作
8. 两个结果集必须结构相同
9. 合集 union 和 union all
   union 去掉重复记录 , union all 不去重
   union 排序 , union all 不排序
   在满足功能的前提下 , 优选 union all
10. 交集 intersect
11. 差集 minus( 两个集合做减法 )
    left outer join 以左边的表为驱动表
    right outer join 以右边的表为驱动表

日期格式

常用日期格式
yyyy 四位数字年 如：2011
year 全拼的年 如：twenty eleven
month 全拼的月 如：november 或 11 月( 中文 )
mm 两位数字月 如：11
mon 简拼的月 如：nov( 中文没有简拼 )
dd 两位数字日
day 全拼的星期 如：tuesday
dy 简拼的星期 如：tue
am 上午/下午 如：am/pm

字符函数：upper / lower / initcap/length / lpad / rpad / replace / trim *

l 表示left ; r 表示 right

1. upper 转换为大写
2. lower 转换为小写
3. initcap 转换为首字母大写
4. length 取长度
5. lpad 左补丁
6. rpad 右补丁
7. replace 字符替换
8. trim 去除前后的空格