sql

看过请留个言，转载请注明出处，尊重作者劳动成果，谢谢！

SQL主要分为三类:

DCL:grant revoke commit rollback

DDL:create alter drop

DML:insert delete update (select )

 

SQL语言基础-alter

alter table tableName add columnName varchar2(30);

alter table tableName drop column columnName;

alter table tableName modify columnName varchar2(30);

alter table tableName rename column columnName to newColumnName;

 

SQL语言基础-select

•         条件查询

比较运算符:>,>=,<,<=,=,!=

between a and b,in(a,b,c),not exists,is null,like ‘%_’,or,and, any,all

 

•         模糊查询

select * from scott.dept where dname like ‘_A%’

select * from scott.dept where dname like ‘%S%’

 

•         集合函数

•         sum,count,max,min,avg

•         select count(empno) as 员工人数 from scott.emp;

 

•         分组语句

group by 字段名 having 组过滤条件

select deptno,count(empno) from scott.emp group by deptno having deptno>=20

 

•         子查询

select * from scott.dept d where not exists (select * from scott.emp e where e.deptno=d.deptno)

 

•         表的连接

select e.ename,d.* from scott.emp e,scott.dept d where e.deptno=d.deptno

select e.ename,d.* from scott.emp e left outer join scott.dept d on e.deptno=d.deptno

select e.ename,d.* from scott.emp e  join scott.dept d on e.deptno=d.deptno(+)