Oracle数据库的基本操作

六、Oracle数据库的基本操作。切换Linux用户su-root输入密码即可切换用户1.连接数据库Sqlplus/assysdba以sys的身份登陆数据库2.解锁用户alteruserhridentifiedbyhraccountunlock;将hr用户解锁（用户名/密码）3.连接到用户Coonhr/hr连接到hr用户（用户名/密码）4.打开关闭Shutdown关闭数据库Startup打开数据库5.查看Select*fromtab;查看当前用户下的表Showuser显示当前用户6.设置时间显示格式altersessionsetnls_date_format='yyyy-mm-ddhh24:mi:ss';结果：2013-07-2714:21:287.oracle的表的管理7.1显示和设置环境变量1.linesize说明：设置显示行的宽度，默认是80个字符showlinesizesetlinesize902.Pagesize说明：设置每页显示的行数目，默认是14用法和linesize一样7.2标识符命名规则表名和列的命名规则必须以字母开头长度不能超过30个字符不能使用oracle的保留字只能使用如下字符A-Z，a-z，0-9，$,#等7.3oracle支持的数据类型字符类char定长最大2000个字符。例子：char(10)‘小韩’前四个字符放‘小韩’，后添6个空格补全如‘小韩’varchar2(20)变长最大4000个字符。例子：varchar2（10）‘小韩’oracle分配四个字符。这样可以节省空间。clob(characterlargeobject)字符型大对象最大4Gchar查询的速度极快浪费空间，查询比较多的数据用。varchar节省空间数字型number范围-10的38次方到10的38次方可以表示整数，也可以表示小数number(5,2)表示一位小数有5位有效数，2位小数范围：-999.99到999.99number(5)表示一个5位整数范围99999到-99999日期类型date包含年月日和时分秒oracle默认格式1-1月-1999timestamp这是oracle9i对date数据类型的扩展。可以精确到毫秒。图片blob二进制数据可以存放图片/声音4G一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放，一般存放图片、视频的路径，如果安全需要比较高的话，则放入数据库。7.4创建表创建新表createtablestudent(---表名xhnumber(4),--学号xmvarchar2(20),--姓名sexchar(2),--性别birthdaydate,--出生日期salnumber(7,2)--奖学金);参照其他表结构创建新表CreatetablebiaomingAsselect*fromdempwhere1=2;新表t14的字段名称、类型、长度，和dept表完全相同,只是没有内容复制其他表到新表CreatetablebiaomingAsselect*fromdemp;新表t14的字段名称、类型、长度，值和dept表完全相同。7.5插入数据插入一行数据Insertintobiaomingvalues(,,,);commit提交插入的数据插入具体字段的数据Insertintobiaoming(ziduan1,ziduan2,ziduan3)Values(zhi1,zhi2,zhi3)赋值过程中，时间和字符串要加单引号；注意类型匹配，不然会发生隐式类型转换；设置默认值Altertablet10modify(enamedefault‘haha’)修改t10的表结构；(修改ename的default值)如果向t10中insert数据的时候，没有给ename赋值，则会以默认值’haha’进行赋值；或者显示以default值进行赋值时，也会以默认值’haha’进行赋值；其他未赋值的字段，如果没有定义default值，那显示null；当然也可以强制赋值为null，覆盖其自身的default值；使用绑定变量进行赋值Insertintot10(empno,ename,sal)Values(&haha,’&hehe’,’&xixi’);用其他的表进行插入赋值Insertintot10(empno,ename)Selectempno,enamefromempWhererownum=3;将emp表中的empno,ename前三行插入到t10中。7.6更新数据Updatet10commit;提交更改数据Setcomm=zhi,job=‘zhi’Whereempno=1002;如果忘记写where条件，则t10表中所有数据的comm都会被更新为200；7.7删除操作Deletefromt10from可以省略Whereempno=1002;7.8查询操作7.8.1显示当前用户下的表Select*fromtab;Select*fromemp;显示emo表的全部信息Descemp;显示emp表的结构7.8.2显示一张表指定的某些列的信息Selectempno,enamefromemp;7.8.3查询数据库系统时间Selectsysdatefromdual;dual为数据库中的虚表,仅充当select语句的结构7.8.4修改系统时间的显示格式；(session表明此修改限当前会话有效)Altersessionsetnls_date_fomat=‘yyyy-mm-ddhh24:mi:ss’;7.8.5调整时间Selectsysdate+2,sysdate+2/24,sysdate+2/24/60,sysdate+2/24/60/60fromdual;系统时间+2天系统时间+2小时系统时间+2分钟系统时间+2秒注意：以上时间格式的修改，限当前会话有效；得到的结果只是临时数据进行显示，并不会改变表中原有的数据值；7.8.6关于null的运算注意：null既不是0，也不是空字符串，而是一个不确定的值step1：描述表中数据的信息(注意comm字段的值)step2：检索comm为null或者非null的记录注意：where条件中不能使用=或者(不等号的表示!=或者或者^=，工作中多使用)step3：null值的计算注意：任何值与null四则运算之后的值仍未null；5.列的别名注意：列的别名中如果包含空格、特殊字符、关键词、或者大小写敏感的信息，需加双引号；6.连字运算符和单引号的使用注意：字符串信息需加单引号；6.1显示【名字】isanameof【编号】6.2显示【编号】‘snameis【名字】6.3显示【编号】isanumber,andit’snameis【名字】注意：两个单引号‘‘表示转义，打印结果时显示一个单引号；例：连接时间、字符串、数字7.显示不重复的信息7.1如果想知道emp表中总共有多少个部门，那怎么看？例：若直接查看，则会存在重复信息；使用distinct消除重复行；注意：distinct写在字段最前面，它会影响之后的所有字段信息；例：如果后面跟多个字段信息，将会把所有字段作为组合信息，与其他记录进行比较；注意：tempinfo对应的1234信息，只是作为临时数据进行显示，而emp表中并不包含此信息；将job与tempinfo作为组合信息，记录之间相互比较，留下不重复记录；7.8.7查找时限定约束条件1.在emp表中选择工资介于1500到2500的员工的信息Select*fromempwheresalbetween1000and1200;2.在emp表中选择位于10,20部门的员工的信息；Select*fromempwheresalin(10,20);3.在emp表中选择位于员工名字中包含大写字符‘O’的员工的信息；Select*fromempwhereenamelike‘%o%’4.检索包含%的记录信息；Select*fromempwhereenamelike‘%\%%’escape‘\’5.检索以%开头的记录信息Select*fromempwhereenamelike‘\%%’escape‘\’6.对于表中数据的排序Select*fromempwhereename=‘john’orderbysalasc;升序Select*fromempwhereename=‘john’orderbysalasc;降序asc或者desc影响的字段，仅仅是它紧挨着的那个字段升降顺序；如果orderby后面跟多个字段，则将结果集先按照第1个字段进行排序，【条件1】，再按照第2个字段进行排序；Null值的处理select*fromemporderbycommascnullsfirst;空值排在前select*fromemporderbycommdescnullslast;空值排在后7.9多表查询7.9.1等值连接Selecte.empno,e.ename,e.deptno,d.dnameFromempe,deptdWheree.deptno=d.deptno;7.9.2非等值连接Selecte.ename,e.sal,j.gradeFromempe,salgradejWheree.salbetweenj.losalandj.hisal;7.9.3内链接Selecte.empno,e.ename,e.deptno,d.dnameFromempeInnerjoindeptdOne.deptno=d.deptno;7.9.4外连接------------------------------------7.10子查询1.在emp表中，查询谁的工资比CLARK的多。Select*fromempWheresal(selectsalfromempwhereename=‘CLARK’);2.查询和Clark在同一部门、并且sal大于clark的员工的信息；Select*fromempWheredeptno=(selectdeptnofromempwhereename=‘clark’)Andsal(selectsalfromempwhereename=‘clark’);3.在emp表中，哪个员工的工资最少Select*fromempWheresal=(selectmin(sal)fromemp);4.查询位于sales部门的员工信息、个数---------------------------7.11函数7.11.1字符处理函数lower(s)，将所给的字符串s全部转换成小写；upper(s)，将所给的字符串s全部转换成大写；initcap(s)，将所给的字符串s中的每个单词转换成首字母大写；Selectlower(‘howAREyou’)resultfromdual;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；