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:287.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 biaomingAs select * from demp where 1=2;新表t14的字段名称、类型、长度，和dept表完全相同,只是没有内容复制其他表到新表Create table biaomingAs 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 = zhiWhere 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 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.dnameFrom emp e,dept dWhere e.deptno = d.deptno;7.9.2 非等值连接Select e.ename,e.sal,j.gradeFrom emp e,salgrade jWhere e.sal between j.losal and j.hisal;7.9.3 内链接Select e.empno,e.ename,e.deptno,d.dnameFrom emp eInner join dept dOn 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,jobOrder by deptno;使用where子句对分组前的的数据进行筛选，使用having子句对分组后的数据进行筛选组函数不能使用在where中，可以使用在having中Select * from emp group by deptno having avg(sal)200Order by deptno; 正确Select * from emp group by deptno where avg(sal)200Order by deptno; 错误SQL语句的编译顺序 from-where-group by-having-select-order by7.12 约束