SQL语句总结

SQL语句总结
1：GROUP BY, HAVING, ORDER BY使用
select * from tab1 where id = 2 group by name having col > 5 order by ASC

 

2：join
   1> 两表正常关联inner join
      select A.id,B.name from A inner join on B where A.id = B.id;
   2> 使用左表关联右表left join
      select A.id,B.name from A left join on B where A.id = B.id;
   3> 使用右表关联左表right join
      select A.id,B.name from A right join on B where A.id = B.id;

 

3：alter
   alter table tab1 add col Integer not null default 1;
   alter table tab1 drop column col;
   alter table tab1 modify col Integer not null default 1;

 

4：escapse
   select * from tab1 where name like '%lu/_y%' escape '/';

 

5：rename tab1 to tab2;

 

6：Add user
   add user xxx identified by yyy default tablespace users;
   grant connect,resource to xxx;

 

7：select tab1_copy as select * from tab1;

 

8：create index IDX_id on tab1(id asc);

 

9：create or replace view tab1_view as select id, name from tab1 with check option;