Oracle数据库学习之查询的嵌套、事务控制语言、集合操作符和SQL函数讲解

1.查询的嵌套

select * from (select loginname,password from

(select id ,loginname, password ,name fromIWEB_TBL_USER t where name like '%张%'))

where loginname= 'zhangyin';

(由内到外。)

有序的查询:

select * from tbi_user order by id;

2.插入

insert into iweb_tbl_user(,,,..)values(,,,..);

commit;

3.更新变大写函数

update iweb_tbl_user set loginname=upper(loginname)||’wh’where id=16;

4.删除

delect from *;

5.时间

select sysdate-to_date('2000-10-10','yyyy-MM-dd') fromdual;

6. 分页查询语句

select id,loginname,name,sex,age, rownum fromiweb_tbl_user;

select * from

(select id,loginname,name,sex,age, rownum rn fromiweb_tbl_user where rownum < =15)

where rn>=11;

7用查询的结果创建新表

createtable tbl_text as

select * fromiweb_tbl_user;

8 去重

select distinct sexfrom iweb_tbl_user;

selectdistinct sex ||age from iweb_tbl_user;

9取别名
select distinct (sex||age) as kk from iweb_tbl_user;

select distinct (sex||age) as kk ,t.* from iweb_tbl_user t;

二丶事务控制语言

commit 提交

rollback 撤销实务中已完成的工作

savepoint 标记事物中可以回滚的点

三丶算数操作符

1.+ - * \

update iweb_tbl_user set id = id-13 ;

select age, age +1 newage fromiweb_tbl_user;

2.比较 in ,like

slect * from iweb_tbl_user

where age!=20;

…

where age between 20and 22;

…

where agein(20,21,22,23);

…

where name like ‘%李%’

…

where age is null;

where age is notnull;

3.集合操作符

select * fromiweb_tbl_user

where age > 20union all (把两个select语句联合)

select * fromiweb_tbl_user

where age <= 20;

union(或称为联合)的作用是将多个结果合并在一起显示出来。

union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

select * fromiweb_tbl_user

where age > 18Intersect

select * fromiweb_tbl_user

where age <= 20;

Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

minus:两个集合相减

3.链接字符串

||

四丶SQL函数

1)日期函数

add_months

months_between

trunc 按指定的格式截取输入的数据返回时间格式

round 四舍五入 返回时间格式

last_day

next_day

extract 返回数字

select id, book_id,user_id, borrowdate, returndate, returndate-borrowdate inter,

add_months(returndate,3)m3

from iweb_tbl_record;

selectlast_day(sysdate) from dual; 计算某日期所在月份最后一天

selectnext_day(sysdate, '星期一') from dual;

select extract(yearfrom returndate) from iweb_tbl_record;

2)字符函数

Lnitcap() 第一个字母大写

lower() 全部小写

Upper() 全部大写

Ltrim() 筛选

……

chr(66)

ascii(‘s’)

lpad(loginname, 10,'*') 左边补充

rpad(loginname, 10,'*') 右边补充

decode (sex,’男’,’male’,’女’,’female’) 重新解释

trim(' ab ')去除前后空格

3）数字函数

abs(-15) 取正

ceil(15.89) 16 向上取整数

floor(15.2222)15 向下取整数

power(2,22) 第一位的多少次方

mod(24,22) 取余数

round(26.22,1) 向上保留小数

trunc(26.22,1) 向下保留小数

sqrt(9) 开根号

sign(45439) 判断正负

……

4）分组函数

1.groupby

分组与 聚合

select sex,age,sum(age) sumage,count(name) cn from iweb_tbl_user

group by sex,age;

2.having

select sex,age,sum(age) sumage,count(name) cn from iweb_tbl_user

group by sex,agehaving count(name) > 1;

5)分析函数

1.row_number 连续

它会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

select t.*,

row_number() over(partition by sex order by age) tk

from iweb_tbl_usert;

2.rank() 排序

selectt.*,

rank()over (partition by sex order by age) tk

fromiweb_tbl_user t;

3. dense_rank 既连续又排序

selectt.*,

dense_rank() over (partition by sex order by age) tk

fromiweb_tbl_user t;

6）多表查询

内链接

selectr.id, book_id, title, user_id, name, borrowdate, returndate

from iweb_tbl_record r, iweb_tbl_book b,iweb_tbl_user u

wherer.book_id = b.id and r.user_id = u.id;

外链接

selectr.id, book_id, user_id, name, borrowdate, returndate

from iweb_tbl_record r right joiniweb_tbl_user u

onr.user_id = u.id;