OracleDBA之表管理

1 sql>create table student( --创建名为student的数据库表
2 　　name varchar2(20),    --名字10个变长
3 　　idcar char(18),     --身份证18个定长字符
4 　　sex char(2),     --性别2个定长字符
5 　　grade number(5,2)    --成绩为浮点数，有效5位小数位为2位；
6 )

 

4.oracle中往已有的表中新增列；

sql>alter table student add(classid number(2));

5.修改已有字段的长度

sql>alter table student modify(name varchar2(10));

6.删除表中的已有字段

sql>alter table student modify(name varchar2(10));

7.表的重命名；

sql>rename student to std;

9.往表中插入数据：
　　1.省略字段名

sql>insert into student values(‘name‘,‘231‘,‘男‘,234.89);

　　2.给部分字段赋值

sql>insert into student(name,idcar) values(‘TOM‘,‘123‘);

　　3.查询idcard字段为空的学生

sql>select * from student where idcard is null;

 

10.修改表中的数据：

sql>update student set name=‘cat‘ where id=1;

 

11.oracle中的回滚：（要养成创建保存点的习惯）--commit后所有的保存点都没有了

　　1.回滚之前先创建保存点
　　　　sql>savepoint pointName;
　　2.删除表中的记录
　　　　sql>delete from student;;
　　3.回滚
　　　　sql>rollback to pointName;
　　　　truncate table student; --删除表中的所有的数据，不写日志，无法回滚，删除速度极快；

 

 

 

Oracle中的select语句的练习，这也是难点

　　1.emp表中的内关联查询：给出每个雇员的名字以及他们经理的名字, 使用表的别名；

sql>select a.ename,b.ename from emp a,emp b where a.mgr=b.empno;

　　2.去除重复的行，重复的行的意思是行的每个字段都相同； distinct

sql>select distinct emp.job,emp.mgr from emp;

　　3.查询SMITH的薪水，职位和部门：

SQL> select emp.ename as 姓名,emp.sal as 薪水, emp.job as 工作,dept.dname as 部门
2 from emp,dept where emp.ename=‘SMITH‘ and emp.deptno=dept.deptno;

姓名 薪水 工作 部门
---------- --------- --------- --------------
SMITH 800.00 CLERK RESEARCH

 

　　4.打开显示sql语句运行时间　　

sql>set timing on;

 

　　5.查询SMITH的年工资；--nvl 处理为null的字段，在表达式里如果有一个值为null则结果就为null用nvl()函数处理为空的字段，例如nvl(comm,0)：如果为null则用0替换；

select emp.ename "名字", emp.sal*12+nvl(emp.comm,0)*12 "年薪" from emp where name=‘SMITH‘;

 

　　6.模糊查询like %代替多个字符，_代替一个字符；

select * from emp where emp.ename like ‘S%‘;

 

　　7.or的升级in查询

select * from emp where emp.empno in(7369,7788);

　　8.查询工资高于500或者是岗位是manager同时名字以J开头的雇员

SQL> select * from emp where (emp.sal>500 or emp.job=‘MANAGER‘) and emp.ename like ‘J%‘;

 

　　9.按照工资从低到高排序 order by语句； desc是降序（从高到低），asc是升序（从低到高 默认）

SQL>select * from emp order by emp.sal asc;

　　10.按照部门号升序（asc）,员工号降序（desc）

SQL>select * from emp order by emp.deptno ,emp.empno desc;

 

　　11.使用列的别名排序：按年薪降序（desc）

SQL>select emp.sal*12 "年薪" from emp order by "年薪" desc;

 

数据的分组————min,max,avg,sum,count;

　　1.查询员工的最高工资和最低工资； min()和max() 的使用

select max(sal) "最高工资", min(sal) "最低工资" from emp;

　　2.查询所有员工的工资总和和平均工资 sun() 和 avg() 的使用；

SQL> select sum(sal) "工资总和", avg(sal) "平均工资" from emp;

　　3.查询员工的总人数：

SQL> select count(*) from emp;

　　4.把最高工资的员工的信息输出(用到了子查询)

SQL>select * from emp where sal=(select max(sal) from emp);
//ERROR 不能使用分组函数
error: select * from emp where sal = max(sal);--error;
error: select ename,max(sal) from emp; -error;

　　　　select 后面若有分组函数子可以跟分组函数

　　5.显示工资高于平均工资的员工信息：

SQL>select * from emp where sal=(select max(sal) from emp);
//ERROR 不能使用分组函数
error: select * from emp where sal = max(sal);--error;
error: select ename,max(sal) from emp; -error;

　　　

 

group by 和 having子句
　　group by 用于对查询的结果进行分组统计
　　having子句用于限制分组显示结果

　　1.显示每个部门的平均工资和最高工资；

　select avg(sal),max(sal),deptno from emp group by deptno;

　　2.显示每个部门的每种岗位的平均工资和最高工资

SQL> select avg(sal),max(sal),deptno,job from emp group by deptno,emp.job order by deptno;

 

　　3.显示平均工资小于2000的部门号和他们的平均工资：

SQL> select emp.deptno,avg(sal) from emp group by emp.deptno having avg(sal)<2000;

DEPTNO AVG(SAL)
------ ----------
30 1566.66666

分组函数只能出现在选择列表，having，order by子句中
如果select中同时有group by ,having ,order by 则三者的顺序为group by ,having, order by;

 

 

多表查询：
　　1.显示雇员名，雇员工资，所在部门名称；

SQL> select emp.empno,emp.sal,dept.dname from emp,dept where emp.deptno=dept.deptno;

　　2.显示部门号为10的雇员名，雇员工资，所在部门名称

SQL> select emp.empno,emp.sal,dept.dname from emp,dept where emp.deptno=10 and emp.deptno=dept.deptno;

 

　　3.显示雇员名，雇员工资，工资的级别；

SQL> select emp.ename,emp.sal,salgrade.grade
          from emp,salgrade
        where emp.sal between salgrade.losal and salgrade.hisal;    

 

子查询： SQL中执行顺序是从右到左执行
　　1.查询与SMITH在同一部门的所有员工；

SQL> select * from emp where emp.deptno=(
         select emp.deptno from emp where emp.ename=‘SMITH‘);
    

 

　　2.查询和部门10的工作相同的员工的信息

SQL> select * from emp where emp.job in( 
         select emp.job from emp where emp.deptno=10);      

 

　3.显示工资比部门号为30的所有员工的工资都高的员工信息；（用 all() 或 max()实现）

SQL> select * from emp where sal>all(
         select sal from emp where emp.deptno=30);
或者（下面的效率要高的多）
SQL> select * from emp where sal>(
         select max(sal) from emp where emp.deptno=30);

 

　　4.显示工资比部门号为30的一个员工的工资都高的员工信息；

SQL> select * from emp where sal>any(
         select sal from emp where emp.deptno=30);
或者（下面的效率要高的多）
SQL> select * from emp where sal>(
         select min(sal) from emp where emp.deptno=30);

 

返回多字段的子查询：
 　　1.查询与SMITH在同一部门并且职位也相同的员工信息；

SQL> select * from emp where (deptno,job)=(
         select deptno,job from emp where ename=‘SMITH‘);

 

    2.查询员工比自己部门的平均工资高的员工信息；（把查询出的信息当作一张表起一个别名）

SQL> select * from emp a,(
         select deptno,avg(sal) mysal from emp group by deptno) a2
         where a.deptno = a2.deptno and a.sal>a2.mysal;

 

在from中使用子查询时查询的结果会当作一个视图来对待，因此也叫做内嵌视图
必须给内嵌视图命一个别名

OracleDBA之表管理

标签：dba   10个   回滚   between   eve   search   操作系统   values   distinct