分类: Oracle
2013-03-16 09:48:07
修改表中字段
alter table DGDCDW.DB_V_DG_CELL modify(RELATED_BTS varchar2(96));
向表中添加一列
Alter table t_teacher add salary int ont null;
添加一个约束条件
Alter table t_dept add primary key(deptID)
删除表中的一列
Alter table t_teaher drop deptID
删除一个约束条件
Alter table t_dept drop primary key
使用case修改
Update t_teacher_salary
Set salary=
Case
When salary<=3000 then salary=salary+salary*0.1
When salary>3000 and salary<=4000 then salary=salary+salary*0.08
When salary>4000 then salary=salary_salary*0.05
Else salary
End
Grant insert,update on test1.t_teacher to user1;所有权限, to public 所以users
Grant select on test1.t_teacher to user1 with grant option;同时user有授予其他用户的权限
Grant createtab on database test to user;授予创建数据表的权限
Revoke select on test1.teacher from user1;回收权限
Create table t_result(
stuID varchar(15), UNIQUE---唯一约束条件
curID varchar(15),
result DOUBLE,
credit INT,
CHECK(credir BETWEEN 3 AND 8)----CHECK约束条件
foreign key(stuID) references t_student(stuID) on delete cascade,--删除方式为级联删除
primary key(stuID,curID)
)
2添加索引
使用列别名查询
Select stuID as 学生编号 from t_student;
使用IN
Select * from t_teacher where dept in (‘计算机’,’数学’)
---也可以dept=’计算机’ or dept=‘数学’
对比两个表的缺数网元;
select distinct ne_code FROM GW_100665881_HW a where trim(ne_code) like 'DGM%' and not exists
(select distinct ne_code FROM GW_100665882_HW b where a.ne_code=b.ne_code);
查找某行周围的行
select * from (SELECT rownum nn, t.* FROM DGDCDW.ORDER_LAIGCI_GCI t where ne_code='SZS68' ) where nn in (43510,43511,43512);
使用LIKE实现模糊查询----与_和%配合使用
Select * from t_teacher where stuID like ‘3%’;
使用转义字符
Where deptID like ‘%$_%’ ESCAPE ‘$’—这里使用转义字符”$”
使用REGEXP关键字进行模式匹配
Where stuName REGEXP ‘^王’
常用聚合函数
Count max min sum avg
Select count(distinct dept) as院系数量 From t_teacher;
使用group
Select dept,count(profession) from t_teacher group by dept;
多列分组
使用HAVING限制分组后的查询结果
Group by dept,profession HAVING max(salary)>3000
对分组结果进行排序
Group by dept order by max(salary) desc;
分组小计,根据多个分组
Oracle:
Select dept,profession,sum(salary) from t_teacher group by rollup (dept,profession);
Mysql:
Select dept,profession,sum(salary) from t_teacher group by dept,profession with rollup;
限制结果集行数
Mysql:
Select dept,count(profession) from t_teacher group by dept limit 3; (limit 3,3)
Oracle
ROWNUM
4-6
Select teaID,teaName,dept,profession from
(select ROWNUM as rn, teaID,teaName,dept,profession from t_teacher where ROWNUM<=6) where rn >=4;
自连接查询
Select C2.cueID,C2.curName,C2.credit
From T_curriculum C1, T_curriculum C2
Where C1.curName = ‘ccccc’
And C1.credit
查询一张表中学分数比cccc学分数多的课程信息
左连接
Oracle:
Select r.stuID,r.curID,c.curName
From t_resultr r,tcurriculum c
Where r.curID(+)=c.curID;
Mysql:
Select r.stuID,r.curID,c.curName
From t_resultr r left join tcurriculum c
On r.curID=c.curID;
Notice:左连接是左边的数据全部查出来,右边对比
右连接相反
Using
并操作:union,union all
Select 1
Union
Select 2
交操作:intersect
Select 1
intersect
Select 2
差操作:minus,返回在第一个select有,但第二个select没有的记录
Select 1
minus
Select 2
子查询:
In,not in ,any|all和运算符公用的子查询--区别
Exists,not exists 逻辑查询 返回true。False
常用函数:
Length:
Select length(‘aaaaaaaa ’) from dual;
Lower:将字符串转为小写;upper转为大写;initcap:首字母转为大写
Select lower(‘CREATE’) From dual;
Substr:截取字符串
Select substr(‘assdddffghgh’,5,3) from dual;
Trim:去掉空格;ltrim左空格,rtrim右空格
LPAD,RPAD
SELECT LPAD(NE_CODE,9,'AB') FROM CHERISH_AT;
Instr:取得指定的子字符串在字符串中的位置
Round(2.222222,3):四舍五入2.222
Mod(10,6):求两个数相除的余数4
Cell(10.3):大于指定数的最小整数11
Floor(10.3):取小于指定数的最大整数10
TO_CHAR,TO_DATE:时间函数
Least(10,25,6,15):比较函数6 greatest
Strcmp
Nvl(salary+pension,salary);nvl2(pensioon, salary+pension,salary)
If(epxpession,result1,result2)