Oracle数据库的基本操作.doc

六、 Oracle数据库的基本操作。
切换Linux用户 su - root 输入密码 即可切换用户
1.连接数据库
Sqlplus / as sysdba 以sys的身份登陆数据库

2.解锁用户
alter user hr identified by hr account unlock; 将hr用户解锁（用户名/密码）
3.连接到用户
Coon hr/hr 连接到hr用户（用户名/密码）
4. 打开关闭
Shutdown 关闭数据库
Startup 打开数据库
5.查看
Select * from tab; 查看当前用户下的表
Show user 显示当前用户
6.设置时间显示格式
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
结果：2013-07-27 14:21:28
7.oracle的表的管理
7.1显示和设置环境变量
1. linesize
说明：设置显示行的宽度，默认是80个字符
show linesize
set linesize 90
2. Pagesize
说明：设置每页显示的行数目，默认是14
用法和linesize一样
7.2标识符命名规则
表名和列的命名规则
· 必须以字母开头
· 长度不能超过30个字符
· 不能使用oracle的保留字
· 只能使用如下字符 A-Z，a-z，0-9，$,#等
7.3 oracle支持的数据类型
字符类
char    定长 最大2000个字符。
例子：char(10)  ‘小韩’前四个字符放‘小韩’，后添6个空格补全 如‘小韩’
varchar2(20)  变长  最大4000个字符。
例子：varchar2（10） ‘小韩’ oracle分配四个字符。这样可以节省空间。
clob(character large object) 字符型大对象 最大4G
char 查询的速度极快浪费空间，查询比较多的数据用。
varchar 节省空间
数字型
number范围 -10的38次方 到 10的38次方
可以表示整数，也可以表示小数
number(5,2)
表示一位小数有5位有效数，2位小数
范围：-999.99到999.99
number(5)
表示一个5位整数
范围99999到-99999
日期类型
date 包含年月日和时分秒   oracle默认格式  1-1月-1999
timestamp 这是oracle9i对date数据类型的扩展。可以精确到毫秒。
图片
blob 二进制数据 可以存放图片/声音  4G   一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放，一般存放图片、视频的路径，如果安全需要比较高的话，则放入数据库。
7.4创建表
创建新表
create table student (    ---表名
          xh       number(4),   --学号
          xm    varchar2(20),   --姓名
          sex      char(2),     --性别
          birthday date,         --出生日期
          sal      number(7,2)   --奖学金
);
参照其他表结构创建新表
Create table biaoming
As select * from demp where 1=2;
新表t14的字段名称、类型、长度，和dept表完全相同,只是没有内容
复制其他表到新表
Create table biaoming
As select * from demp;
新表t14的字段名称、类型、长度，值和dept表完全相同。
7.5插入数据
插入一行数据
Insert into biaoming values( , , ,); commit 提交插入的数据
插入具体字段的数据
Insert into biaoming(ziduan1,ziduan2,ziduan3)
Values(zhi1,zhi2,zhi3)
赋值过程中，时间和字符串要加单引号；注意类型匹配，不然会发生隐式类型转换；
设置默认值
Alter table t10 modify(ename default ‘haha’)
修改t10的表结构；(修改ename的default值)
如果向t10中insert数据的时候，没有给ename赋值，则会以默认值’haha’进行赋值；
或者显示以default值进行赋值时，也会以默认值’haha’进行赋值；
其他未赋值的字段，如果没有定义default值，那显示null；当然也可以强制赋值为null，覆盖其自身的default值；
使用绑定变量进行赋值
Insert into t10(empno,ename,sal)
Values(&haha,’&hehe’,’&xixi’);
用其他的表进行插入赋值
Insert into t10(empno,ename)
Select empno,ename from emp
Where rownum<=3;
将emp表中的empno,ename 前三行插入到t10中。
7.6更新数据
Update t10 commit;提交更改数据
Set comm = zhi,job = ‘zhi’
Where empno = 1002;
如果忘记写where条件，则t10表中所有数据的comm都会被更新为200；
7.7删除操作
Delete from t10 from 可以省略
Where empno = 1002;
7.8 查询操作
7.8.1显示当前用户下的表
Select * from tab;
Select * from emp; 显示emo表的全部信息
Desc emp ; 显示emp表的结构
7.8.2显示一张表指定的某些列的信息
Select empno,ename
from emp;
7.8.3查询数据库系统时间
Select sysdate from dual; dual为数据库中的虚表,仅充当select语句的结构
7.8.4修改系统时间的显示格式；(session表明此修改限当前会话有效)
Alter session set nls_date_fomat = ‘yyyy-mm-dd hh24:mi:ss’;
7.8.5调整时间
Select sysdate+2,sysdate+2/24,sysdate+2/24/60,sysdate+2/24/60/60 from dual;
系统时间+2天 系统时间+2小时 系统时间+2分钟 系统时间+2秒

注意：以上时间格式的修改，限当前会话有效；
得到的结果只是临时数据进行显示，并不会改变表中原有的数据值；
7.8.6关于null的运算
注意：null既不是0，也不是空字符串，而是一个不确定的值
step 1：描述表中数据的信息(注意comm字段的值)
step 2：检索comm为null或者非null的记录
注意：where条件中不能使用=或者<>(不等号的表示!=或者<>或者^=，工作中多使用<>)

step 3：null值的计算
注意：任何值与null四则运算之后的值仍未null；

5. 列的别名
注意：列的别名中如果包含空格、特殊字符、关键词、或者大小写敏感的信息，需加双引号；

6.连字运算符和单引号的使用
注意：字符串信息需加单引号；
6.1显示【名字】is a name of 【编号】

6.2显示【编号】‘s name is 【名字】
6.3显示【编号】is a number, and it’s name is 【名字】
注意：两个单引号‘‘表示转义，打印结果时显示一个单引号；

例：连接时间、字符串、数字

7.显示不重复的信息
7.1 如果想知道emp表中总共有多少个部门，那怎么看？
例：若直接查看，则会存在重复信息；使用distinct消除重复行；
注意：distinct写在字段最前面，它会影响之后的所有字段信息；

例：如果后面跟多个字段信息，将会把所有字段作为组合信息，与其他记录进行比较；
注意：temp info对应的1234信息，只是作为临时数据进行显示，而emp表中并不包含此信息；
将job与temp info作为组合信息，记录之间相互比较，留下不重复记录；

7.8.7查找时限定约束条件
1.在emp表中选择工资介于1500到2500的员工的信息
Select * from emp where sal between 1000 and 1200;
2. 在emp表中选择位于10,20部门的员工的信息；
Select * from emp where sal in(10,20);
3.在emp表中选择位于员工名字中包含大写字符‘O’的员工的信息；
Select *　from emp where ename like ‘%o%’
4.检索包含%的记录信息；
Select * from emp where ename like ‘%\%%’ escape ‘\’
5.检索以%开头的记录信息
Select *　from emp where ename like ‘\%%’ escape ‘\’
6. 对于表中数据的排序
Select *　from emp where ename = ‘john’ order by sal asc; 升序
Select *　from emp where ename = ‘john’ order by sal asc; 降序
asc或者desc影响的字段，仅仅是它紧挨着的那个字段升降顺序；
如果order by后面跟多个字段，则将结果集先按照第1个字段进行排序，【条件1】，再按照第2个字段进行排序；
Null值的处理
select * from emp order by comm asc nulls first; 空值排在前
select * from emp order by comm desc nulls last; 空值排在后
7.9 多表查询
7.9.1 等值连接
Select e.empno,e.ename,e.deptno,d.dname
From emp e,dept d
Where e.deptno = d.deptno;
7.9.2 非等值连接
Select e.ename,e.sal,j.grade
From emp e,salgrade j
Where e.sal between j.losal and j.hisal;
7.9.3 内链接
Select e.empno,e.ename,e.deptno,d.dname
From emp e
Inner join dept d
On e.deptno = d.deptno;
7.9.4 外连接
------------------------------------
7.10 子查询
1.在emp表中，查询谁的工资比CLARK的多。
Select *　from emp
Where sal > (select sal from emp where ename = ‘CLARK’);
2.查询和Clark在同一部门、并且sal大于clark的员工的信息；
Select * from emp
Where deptno = (select deptno from emp where ename = ‘clark’)
And sal > (select sal from emp where ename = ‘clark’);
3. 在emp表中，哪个员工的工资最少
Select *　from emp
Where sal = (select min(sal) from emp);
4. 查询位于sales部门的员工信息、个数
---------------------------
7.11 函数
7.11.1 字符处理函数
lower(s)，将所给的字符串s全部转换成小写；
upper(s)，将所给的字符串s全部转换成大写；
initcap(s)，将所给的字符串s中的每个单词转换成首字母大写；
Select lower(‘how ARE you’) result from dual;
concat(s1，s2)：将字符串s1和字符串s2拼接起来；
rownum是表中数据的行号，是数据库自动添加的，但是在select * from …查询时看不到，需要显示的进行指定才可；同样还有rowid，也是数据库自动添加的，64进制的数；(此了解即可，一般编程用rownum时较多，做数据文件维护时两者会结合使用)
substr(s，m，[n])：表示从所给的字符串s中取得指定范围的子串；
注意：m表示从字符串s的第几位开始，正整数表示从左到右数第几位开始，负整数表示从右至左数第几位开始；但是截取的方向都是向右；
n表示截取子串的长度，如果不写，即从第m位开始向右直到结束；
length(s)：表示返回所给字符串s的字符数，即长度(如果有半角空格，则算1个字符；当然全角空格算2个字符宽度)
instr(s,s1,[m],[n]):表示子字符串s1在字符串s中，从第m位开始检索，第n次出现的时候，所在的位置是哪；
注意：如果m是负整数，表示从右第m开始向左检索；
如果m，n都省略，表示从左边第1位开始向右检索，在s中第1次出现s1的位置在哪；
lpad(s，n，s1)：在字符串s左边补充子串s1，直到长度为n；
rpad(s，n，s1)：在字符串s右边补充子串s1，直到长度为n；
trim(leading|trailing|boths1 from s)：表示将子字符串s1从s中去除；
注意：leading表示将s左边的s1去除；trailing表示将s右边的s1去除；both表示将s两边的s1去除；（默认）
如果s中间包含s1，则不作处理；
replace(s,s1,s2)：表示将字符串s中的s1用s2替换掉；
7.11.2数字处理函数(主要针对数字的格式进行设定)
round(c，[n])：表示求数字c的四舍五入值；
注意：n表示保留到小数点后面多少位；如果n省略，则保留成整数；如果n为负数，则保留到小数点左边多少位；
trunc(c，[n])：表示将数字c截断，保留到小数点后第n位；
注意：n表示保保留到小数点后第n位，则第n位之后的数字，不考虑四舍五入，直接舍去；

如果n省略，则只保留成整数，小数部分直接舍去；如果n为负数，则保留到小数点左边第n位，之后的数字，不考虑四舍五入，直接舍去；
nvl(e1,e2)：如果第1个参数e1的值为null，则返回e2的值；
nvl2(e1,e2,e3)：如果第1个参数e1的值为null，则返回e3的值；
如果第1个参数e1的值不为null，则返回e2的值；
7.11.3组函数
Avg([ditingct|all] expr): 计算expr的平均值
Sum([distinct|all] expr):计算expr的总计值
Max([distinct|all] expr):计算expr的最大值
Min([distinct|all] expr):计算expr的最小值
Count(*[distinct|all] expr):计算表中数据总数
说明：distinct 去除重复值，all 所有值，空值被自动忽略
Group by 创建数据组
Select deptno,job,sum(sal),count(*),avg(sal)
From emp
Group by deptno,job
Order by deptno;
使用where子句对分组前的的数据进行筛选，使用having子句对分组后的数据进行筛选
组函数不能使用在where中，可以使用在having中

Select * from emp group by deptno having avg(sal)<200
Order by deptno; 正确
Select * from emp group by deptno where avg(sal)<200
Order by deptno; 错误

SQL语句的编译顺序 from--where--group by--having--select--order by
7.12 约束