sql 工作常用语法总结1

修改表中字段

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起始值总是从1开始的

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)