oracle数据库的基本操作

6、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( 用其他的表进行插入赋值 Insert into t10(empno,ename) Select empno,ename from emp Where rownum(不等号的表示!=或者) step 3：null值的计算 注意：任何值与null四则运算之后的 值仍未null ； 5. 列的 别名 注意：列的别名中如果包含空格、特殊字符、关键词、或者大小写敏感的信息，需加双引 号； 6.连 字运算符和单引号的使用 注意：字符串信息需加单引号； 6.1显示【名字】is a name of 【编号】 6.2显示【编号】 s name is 【名字】 6.3显示【编号】is a number, and its 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 Ord