OracleDBA表管理
下面是Oracle表管理的部分,用到的测试表是oracle数据库中scott用户下的表做的测试,有的实验也用到了hr用户的数据,以下这些东西是我的麦库上存的当时学Oracle的学习笔记今天拿出来和大家分享一下,转载请注明出处,下面用的Oracle的版本是10g,用的时WinServer2003的操作系统,可能有些命令和Oracle11g的有所不同,但大部分是一样的,接下来还会陆续的分享一下Oracle中对数据库的管理,对表的管理,还有Oracle中的存储过程和PL/SQL编程。用到的Oracle的管理工具是PL/SQL Developerl和SQL PLUS,欢迎大家批评指正。 1.表名和列名的命名规则: altersessionsetnls_date_fomat="yyyy-mm-dd"; 4.大数据(存放媒体) 1sql>createtablestudent(--创建名为student的数据库表2 namevarchar2(20),--名字10个变长3 idcarchar(18),--身份证18个定长字符4 sexchar(2),--性别2个定长字符5 gradenumber(5,2)--成绩为浮点数,有效5位小数位为2位;6) 4.oracle中往已有的表中新增列; sql>altertablestudentadd(classidnumber(2)); 5.修改已有字段的长度 sql>altertablestudentmodify(namevarchar2(10)); 6.删除表中的已有字段 sql>altertablestudentmodify(namevarchar2(10)); 7.表的重命名; sql>renamestudenttostd; 9.往表中插入数据: sql>insertintostudentvalues('name','231','男',234.89); 2.给部分字段赋值 sql>insertintostudent(name,idcar)values('TOM','123'); 3.查询idcard字段为空的学生 sql>select*fromstudentwhereidcardisnull; 10.修改表中的数据: sql>updatestudentsetname='cat'whereid=1;
1.回滚之前先创建保存点 sql>savepointpointName; 2.删除表中的记录 sql>deletefromstudent;; 3.回滚 sql>rollbacktopointName; truncatetablestudent;--删除表中的所有的数据,不写日志,无法回滚,删除速度极快; Oracle中的select语句的练习,这也是难点 1.emp表中的内关联查询:给出每个雇员的名字以及他们经理的名字,使用表的别名; sql>selecta.ename,b.enamefromempa,empbwherea.mgr=b.empno; 2.去除重复的行,重复的行的意思是行的每个字段都相同; distinct sql>selectdistinctemp.job,emp.mgrfromemp; 3.查询SMITH的薪水,职位和部门: SQL>selectemp.enameas姓名,emp.salas薪水,emp.jobas工作,dept.dnameas部门2fromemp,deptwhereemp.ename='SMITH'andemp.deptno=dept.deptno; 姓名薪水工作部门------------------------------------------SMITH800.00CLERKRESEARCH 4.打开显示sql语句运行时间 sql>settimingon; 5.查询SMITH的年工资;--nvl 处理为null的字段,在表达式里如果有一个值为null则结果就为null用nvl()函数处理为空的字段,例如nvl(comm,0):如果为null则用0替换; selectemp.ename"名字",emp.sal*12+nvl(emp.comm,0)*12"年薪"fromempwherename='SMITH'; 6.模糊查询like %代替多个字符,_代替一个字符; select*fromempwhereemp.enamelike'S%'; 7.or的升级in查询 select*fromempwhereemp.empnoin(7369,7788);
SQL>select*fromempwhere(emp.sal>500oremp.job='MANAGER')andemp.enamelike'J%'; 9.按照工资从低到高排序 order by语句; desc是降序(从高到低),asc是升序(从低到高 默认) SQL>select*fromemporderbyemp.salasc; 10.按照部门号升序(asc),员工号降序(desc) SQL>select*fromemporderbyemp.deptno,emp.empnodesc; 11.使用列的别名排序:按年薪降序(desc) SQL>selectemp.sal*12"年薪"fromemporderby"年薪"desc; 数据的分组――――min,max,avg,sum,count; selectmax(sal)"最高工资",min(sal)"最低工资"fromemp; 2.查询所有员工的工资总和和平均工资 sun() 和 avg() 的使用; SQL>selectsum(sal)"工资总和",avg(sal)"平均工资"fromemp; 3.查询员工的总人数: SQL>selectcount(*)fromemp; 4.把最高工资的员工的信息输出(用到了子查询) SQL>select*fromempwheresal=(selectmax(sal)fromemp);//ERROR不能使用分组函数 error:select*fromempwheresal=max(sal);--error;error:selectename,max(sal)fromemp;-error; select 后面若有分组函数子可以跟分组函数
SQL>select*fromempwheresal=(selectmax(sal)fromemp);//ERROR不能使用分组函数 error:select*fromempwheresal=max(sal);--error;error:selectename,max(sal)fromemp;-error;
group by 和 having子句
selectavg(sal),max(sal),deptnofromempgroupbydeptno; 2.显示每个部门的每种岗位的平均工资和最高工资 SQL>selectavg(sal),deptno,jobfromempgroupbydeptno,emp.joborderbydeptno; 3.显示平均工资小于2000的部门号和他们的平均工资: SQL>selectemp.deptno,avg(sal)fromempgroupbyemp.deptnohavingavg(sal)<2000; DEPTNOAVG(SAL)----------------301566.66666 分组函数只能出现在选择列表,having,order by子句中 多表查询: SQL>selectemp.empno,emp.sal,dept.dnamefromemp,deptwhereemp.deptno=dept.deptno; 2.显示部门号为10的雇员名,雇员工资,所在部门名称 SQL>selectemp.empno,deptwhereemp.deptno=10andemp.deptno=dept.deptno; 3.显示雇员名,雇员工资,工资的级别; SQL>selectemp.ename,salgrade.gradefromemp,salgradewhereemp.salbetweensalgrade.losalandsalgrade.hisal; 子查询: SQL中执行顺序是从右到左执行 SQL>select*fromempwhereemp.deptno=(selectemp.deptnofromempwhereemp.ename='SMITH'); 2.查询和部门10的工作相同的员工的信息 SQL>select*fromempwhereemp.jobin( selectemp.jobfromempwhereemp.deptno=10); 3.显示工资比部门号为30的所有员工的工资都高的员工信息;(用 all() 或 max()实现) SQL>select*fromempwheresal>all(selectsalfromempwhereemp.deptno=30); 或者(下面的效率要高的多) SQL>select*fromempwheresal>(selectmax(sal)fromempwhereemp.deptno=30); 4.显示工资比部门号为30的一个员工的工资都高的员工信息; SQL>select*fromempwheresal>any(selectsalfromempwhereemp.deptno=30); 或者(下面的效率要高的多) SQL>select*fromempwheresal>(selectmin(sal)fromempwhereemp.deptno=30); 返回多字段的子查询: SQL>select*fromempwhere(deptno,job)=(selectdeptno,jobfromempwhereename='SMITH'); 2.查询员工比自己部门的平均工资高的员工信息;(把查询出的信息当作一张表起一个别名) SQL>select*fromempa,(selectdeptno,avg(sal)mysalfromempgroupbydeptno)a2wherea.deptno=a2.deptnoanda.sal>a2.mysal; 在from中使用子查询时查询的结果会当作一个视图来对待,因此也叫做内嵌视图必须给内嵌视图命一个别名 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |