CUUG官方博客: CUUG官网_CUUG怎么样_CUUG培训就业怎么样_CUUG学Oracle要多少钱

sql作业 5~9章 兰交大 司金成

分组函数:sum avg max min 

                例: select avg(sal),max(sal),min(sal),sum(sal)  from emp  where job like 'SALES%';

Count : 返回查询出的总行数

           例:        select count(*) from emp where deptno=30;             注:包含重复行和含有空值的行   

            select count(comm) from emp where deptno=30; 

group by : 分组     例:   select deptno dnum,avg(sal) from emp group by deptno;

                                             select deptno,job,sum(sal)  from emp  group by deptno,job order by deptno;

                                    注:select  语句中,没有使用分组函数的列必须在 group by 子句中

                       group by 后面的列可以不出现在 select 语句中                group by  后面不允许使用列的别名

having :  select deptno,max(sal) from emp  group by deptno  having max(sal) > 2900;

   不能在 where 子句中对聚组列作出限定,应使用 having 子句

分组函数嵌套:   例: select count(max(avg(sal))) from emp group by deptno

                                  注:分组函数最多嵌套两层

子查询:  可以在 select、from、where、having 子句中使用子查询   子查询要放在括号里面     子查询做好不要包含 order by      单行子查询使用单行比较操作符,多行子查询使用多行比较操作符(in,any ,all)。

any:  小于最大,大于最小                     all:    大于最大,小于最小

in    等价于  = any                               not in  等价于  != all    

union : 并集(排序,去重)

   例:select empno,ename,deptno from test1  union  select empno,ename,deptno from test2;

Union all : 并集(不排序,不去重)

Intersect: 交集(排序,去重)

                 例    select empno,ename,deptno from test1  intersect  select empno,ename,deptno from test2;

Minus: 差集(排序,去重)

   

子查询: 例:select ename from emp  where sal > ( select sal from emp where empno=7566);

                注: 子查询中不要包含order by子句

 查询工资比本部门平均工资高的员工  :

               例:select e.ename,e.sal,e.deptno from emp e where e.sal > (select avg(s.sal) from emp s where e.deptno = s.deptno);

 

替代变量:&或&&

      &: 使用带有一个&的变量值来提醒用户输入一个值

      例:select  empno,ename,sal,deptno  from emp  where  empno = &employee_num;

 使用单引号标志来替换变量中的日期或字符串

       例:select  ename,deptno,sal*12  from  emp  where  job='&job_title'; 

  select empno,ename,job,&column_name from emp where &condition order by &order_column;

     &&:使用带有两个&变量的可以使得的该变量重复使用

      例:select  empno,ename,job,&&col  from  emp  order by  &col;

define:查看定义的变量           undefine:删除定义的变量 例:undefine +变量名

Define: 创建char类型的用户变量    注:定义有空格的变量是用单引号将变量括起来

Accept: 读取用户输入并把它存入一个变量里(定制个性化提示)

verify on/off : 显示或不显示替换前后命令 


linesize/pagesize/time/timing  :  行长度/页面大小/系统当前时间/sql执行时间

        例: set linesize 100   set pagesize 10     set time on     set  timing on

set  echo  on  :  执行脚本时显示内容 反之不

feedback on : 显示查询结果的行数  

                  例: feedback  on  for  6 or more rows        6 行及以上显示     

heading :  显示头部或列名信息                                       例:    set  heading  on/off

set  colsep ‘|’  : 设置分隔符为 |

set  termout  on/off   :  显示脚本执行结果

col   ename clear :清除格式设置                                    clear columns : 清除所有设置

spool : 保存sql结果到文件中                                             例: spool  /home/oracle/scripts/emp1.sql

set trimspool on :  行显示紧凑

 

 

DML 语句: insert  delete  update

DDL语句(用于构建数据库对象的语言):create  drop  alter  truncate

DCL(数据控制语言): grant   revoke

TCL(事务控制语言):commit  rollback   savepoint

 

使用一条 SQL 语句,向三个表中插入数据

insert all

when deptno = 10 then

into emp10 values (empno,ename,deptno)

when deptno = 20 then

into emp20 values (empno,ename,deptno)

when deptno = 30 then

into emp30 values (empno,ename,deptno)

select empno,ename,deptno from emp;

 

事务

   sqlplus  异常中止时自动进行隐式回滚

   事务的 ACID 属性(关系型数据库):原子性   一致性    隔离性   持久性 

   隐式提交: DDL  DCL                  显示提交:commit

   SQL*Plus 退出命令 exit(quit),默认 exit commit,可以退出时 exit rollback

oracle锁:排他锁   共享锁

排他锁:只有一份,我加锁后,其他人不能加锁,只能等待。行级别,保护这行的数据,防                        止其他会话修改这些行

共享锁:可以加多份。表级别,保护表的结构,防止其他会话使用 DDL 语句修改表的定义。

排他锁:   例:select * from emp where empno=7788 for update;

               select * from emp where empno=7788 for update nowait;

 

 表      :列组成表的结构,行组成表的数据。

视    图:存储在数据字典中的一条 select 语句。

序    列:一种生成唯一数字的结构:有序的发出数字。

索    引:可以减少对表中行的访问次数、提高查询性能。

同义词:别名。

 

创建表   例:create table dept01  (deptno number(2),dname varchar2(14),loc varchar2(13));

 

order by  后面不能跟 LOB、LONG、LONG ROW 类型的列;

一个表中最多只能有一个 LONG 类型的列。

USER_:用户所有的对象的信息;

ALL_:用户拥有的和有权限操作的对象的信息;

DBA_:只有具有 DBA 角色的用户可以访问,里面包含数据库所有对象的信息;

V$_:动态性能视图(数据来自内存) 、数据库服务器的性能和锁的相关信息。

使用子查询创建表:

    例:create table dept30 as  select empno,ename,sal*12 ANASAL,hiredate  from emp

where deptno=30;

 alter table dept30 add (job varchar2(9));                                                       新增加的列成为最后一列。

alter table dept30 modify (ename varchar2(15));                                        修改当前列

alter table dept30 drop column job;                                                               删除列

alter table dept30 rename column empno to id;                                           重命名列

rename dept30 to department30;                                                                   改变对象名称

comment on table emp is 'Employee Information';                                       添加注释

comment on column emp.deptno is 'Department Number';

drop table dept;                                                                                                 删除表   

 

 


作者:司金成 分类:技术博文 浏览:539 评论:0
留言列表
发表评论
来宾的头像

点击关闭
  • 在线客服