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; 删除表
