SQL基础查询语句（Oracle）

1. select distinct column1,solumu2 from table_name 删除重复值，此处distinct作用于column1,column2;
2. select column1 from table_name where rownum <=5 返回5行数据；
3. select column1 from table_name where rownum <=5  --注释；
4. /* select column1 from table_name*/ 注释代码；
5. select * from hrmsubcompany order by supsubcomid,id  按照order by后面依次排序；
6. select * from hrmsubcompany order by 1 按照第一列排序；
7. select * from hrmsubcompany order by 1 desc 降序排序；
8. select * from hrmsubcompany where id<'94' and id <>'90'  单引号是字符串；
9. select * from hrmsubcompany where ID between 1 and 93  ID在1-93之间；
10. select * from hrmsubcompany where subcompanycode is null 返回空值，不是0；
11. select * from hrmsubcompany where id in(1,98,103) = select * from hrmsubcompany where id ='1' or id='98' or id='103'；
12. select * from hrmsubcompany where id<'94' and not id ='90'  ID小于94但是不等于90；
13. select * from hrmsubcompany where id like '%1'  ID末尾是1的；
14. select * from hrmsubcompany where id like '1%1'  ID是1开头，1结尾的；（%可以表示0个，1个，多个字符）
15. select * from hrmsubcompany where id like '_1__' ID是四位数，第二位是1；
16. select id+tlevel from hrmsubcompany 两列数值相加；
17. select subcompanyname ||'('||ID ||')' from hrmsubcompany ||相当于+，Oracle不支持+；
18. select ID ,supsubcomid,ID*supsubcomid as total from hrmsubcompany  计算前两列的值，并取名total；
19. select 3*3 as total from dual 数据库计算3*3，返回值赋值给total；
20. select ecology_pinyin_search,upper(ecology_pinyin_search) as word from hrmsubcompany upper将小写英文转化为大写；
21. select length(ecology_pinyin_search) from hrmsubcompany 返回字段长度；
22. select id,sysdate,createdate,test from hrmresource where extract(year from test)=2023 提取字段类型是date的年份是2023年的；
23. select abs(-12) as jdz from dual 返回-12的绝对值；
24. select avg(id) as avg_id,sysdate,createdate from hrmresource group by createdate 求ID的平均值，sysdate是当前日期，和createdate是创建日期，无法实现平均值的显示，需要对createdate进行分组
25. select count(*) from hrmresource 对表中的数据进行行数统计，不忽略null值；
26. select count(loginid) from hrmresource 对loginid进行数量统计，忽略null值；
27. select max(id) as max,min(id) as min from hrmresource 取值ID的最大值和ID的最小值；
28. select min(createdate) from hrmresource 取值日期里面最小值；
29. select sum(id) from hrmresource ID的总和；
30. select sum(id*seclevel) from  hrmresource 两列计算后求和；
31. select count(id),seclevel from  hrmresource group by seclevel 根据ID统计seclevel分组数量；
32. select count(id),seclevel from  hrmresource group by seclevel order by seclevel 根据seclevel分组并排序
33. select locationid ,seclevel from  hrmresource group by seclevel,locationid  having seclevel <='0' seclevel小于0的取值；
34. select count(id) as total,seclevel from  hrmresource group by seclevel having count(id) <='100' having在数据分组后进行过滤（where在数据分组前进行过滤）；如果不指定group by,where和having非常的相似；
35. select * from hrmsubcompany where id in(select distinct subcompanyid1 from  hrmresource where managerid='624') SQL由内向外执行语句，先执行括号内的，然后执行括号外的；
36. select a.lastname,b.subcompanyname from hrmresource a,hrmsubcompany b where a.subcompanyid1=b.id  定义a,b表格，使公司id相等，得出姓名和公司名称；
37. select a.lastname,b.subcompanyname from hrmresource a inner join hrmsubcompany b on a.subcompanyid1=b.id  输出结果同36；
38. select a.id,a.lastname,b.subcompanyname,c.jobtitlename from hrmresource a,hrmsubcompany b,hrmjobtitles cwhere a.subcompanyid1=b.id and a.jobtitle=c.id and a.id='28654' 三表联查个人信息；
39. select * from hrmresource where id='690' union select * from hrmresource where jobtitle='709' union链接两个select语句，同时剔除重复项（id='690'在jobtitle='709'中存在）；
40. select * from hrmresource where id='690' union all select * from hrmresource where jobtitle='709' union all集合了两条语句的所有值；
41. insert into hrmjobtitles values('9569324','数据库插入测试','数据库插入测试',null,'516202',null,null,null,null,null,null,null) 插入对应数据 但是要注意的是ID是自生成的，不会执行插入
42. create table test1123 as select * from hrmjobtitles where jobactivityid='516202' 创建和hrmjobtitles一模一样的表单；
43. update hrmjobtitles set jobactivityid='1111' where id='8131'  修改ID是8138 jobactivityid这个字段；
44. update hrmjobtitles set jobactivityid=null where id='8131' 修改ID是8138 jobactivityid这个字段为空；
45. delete from hrmjobtitles where id='8131' 删除ID为8131的行项目；
46. create table products1127(prod_id  char(10)  not null,vend_id  char(10)  not null,prod_name  char(254)  not null,prod_price  decimal(8,2)  not null,prod_desc  varchar(1000)  null)  创建表单（字段，类型，是否为空）;
47. alter table Products1127 add time date 表单增加一列；
48. alter table Products1127 drop column time 表单减少一列；
49. drop table Products1127 删除表单；
50. create view companyview as select a.ID,a.subcompanyname, a.subcompanycode,b.lastname  from hrmsubcompany a,hrmresource_0703 b where a.id=b.subcompanyid1 创建一个select后面查询的视图；