一步步学习Oracle

1、 ORACLE GROUP BY

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。
合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句。

语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

例子1：查找每个客户的总金额

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer;

例子2：GROUP BY 一个以上的列

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

2、ORACLE HAVING

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

语法：
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

例子1：查找订单总金额少于 2000 的客户

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000;

例子2：查找客户 "Bush" 或 "Adams" 拥有超过 1500 的订单总金额

SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500;

3、ORACLE ALTER TABLE

create table T_CLASS_INFO( 
 CLASSNO number(3) primary key, --班级序号
 CLASSNAME varchar2(10), 
 CLASSPLACE varchar2(13) 
); 
create table T_STUDENT_INFO( 
 STUNO number(3),--学号 
 CLASSNO number(3), 
 STUNAME varchar2(10), 
 STUSEX char(1),
 STUCONTECT number(11), --联系方式
 STUADD varchar2(50), 
 STUDETAILINFO varchar2(100) 
); 

--修改列名 
ALTER table T_CLASS_INFO RENAME column CLASSPLACE to LOCATION; 
--添加主键约束 
ALTER table T_STUDENT_INFO ADD constraint PK_STU_NO primary key(STUNO); 
--添加外键约束
ALTER table T_STUDENT_INFO ADD constraint FK_CLASSNO foreign key(CLASSNO) references T_CLASS_INFO(CLASSNO);
--添加check约束
ALTER table T_STUDENT_INFO ADD constraint CK_STU_INFO check (STUSEX in ('F','M'));
--添加not null约束 
ALTER table T_STUDENT_INFO MODIFY STUCONTECT constraint NOT_NULL_INFO not null;
--添加unique约束 
ALTER table T_STUDENT_INFO ADD constraint UQ_STU_CONTECT unique(STUCONTECT); 
--添加default约束
ALTER table T_STUDENT_INFO MODIFY STUSEX char(2) default'M'; 

--增加列
ALTER table T_STUDENT_INFO ADD STUID varchar2(18); 
ALTER table T_STUDENT_INFO ADD STUAGE DATE default sysdate not null;
--删除列 
ALTER table T_STUDENT_INFO DROP column STUDETAILINFO; 

--修改列的长度 
ALTER table T_CLASS_INFO MODIFY CLASSPLACE varchar2(50); 
--修改列的精度 
ALTER table T_STUDENT_INFO MODIFY STUNO number(2); 
--修改列的数据类型 
ALTER table T_STUDENT_INFO MODIFY STUSEX char(2); 
--修改列的默认值 
ALTER table T_STUDENT_INFO MODIFY STUAGE default sysdate+1; 

--禁用约束 
ALTER table T_STUDENT_INFO disable FK_CLASSNO; 
--启用约束 
ALTER table T_STUDENT_INFO enable FK_CLASSNO; 
--删除约束 
ALTER table T_STUDENT_INFO DROP FK_CLASSNO; 
--延迟约束 
ALTER table T_STUDENT_INFO ADD constraint FK_CLASSNO foreign key(CLASSNO) 
references T_CLASS_INFO(CLASSNO) 
deferrable initially deferred; 

--向表中添加注释 
comment on table T_STUDENT_INFO is '学生表'; 
--向列添加注释 
comment on column T_STUDENT_INFO.STUNAME is '学生姓名'; 
comment on column T_CLASS_INFO.CLASSNAME is '班级'; 
--清除表中所有数据 
truncate table T_STUDENT_INFO; 
--删除表 
DROP table T_STUDENT_INFO;