Orcale学习(二)

前言:上一篇我们学习了Orcale的发展史以及其结构等，今天我们又学习了Orcale的SQLs语言基础

1.SQL语言分类(基本和SQL Server一样)

　　(1).数据定义语言(DDL):用于创建、修改和删除数据库对象

　　(2).数据操纵语言(DML):用于操纵数据库(Insert,Update,Delete,Select)

　　(3).数据控制语言(DCL):用于执行授权和撤销权限的操作(GRANT:授权,REVOKE:撤销权限)

　　(4).事务控制语言(TCL):用于维护数据的一致性(COMMIT:提交,ROLLBACK:回滚,SAVEPOINT:保存点)

2.SQL语句编写规则

　　(1).SQL关键字不区分大小写

　　(2).对象名与列名不区分大小写

　　(3).字符值与日期值区分大小写

　　(4).在SQL*Plus中，每条SQL语句要以分号结束

3.Orcale的常用数据类型

　　(1).字符类型

　　　　a.CHAR:固定长度的字符(最多2000个字符，不足时使用空格补充)

　　　　b.VARCHAR2:可变长度字符串(最多可存储4000个字符)

　　(2).数值类型

　　　　a.NUMBER(M,N):M表示精度(总位数),N表示小数点右侧数字的位数

　　(3).日期类型

　　　　a.DATE:用于存储表中的日期和时间数据

　　　　b.TIMESTAMP:用于存储日期的年、月、日以及时间的小时、分和秒值

　　(4)大对象类型

　　　　a.CLOB:用于存储可变长度的字符数据(最大4G)

　　　　b.BLOB:用于存储较大的二进制对象(最大4G)

4.SQL语句

　　1.Create Table

Create table tb_shop
(
Id number(10) primary key,
shopId varchar2(20) unique not null,
shopName varchar2(20) not null,
price number(6,2) not null,
shopTypeId number(10) not null,
manufacturingDate date not null,
constraint ck_price check(price>0),
constraint fk_shopTypeId foreign key(shopTypeId) references tb_shopType(Id)
);

　　2.Alter Table

--添加字段
alter table tb_shop add memo varchar2(100);

--修改字段
alter table tb_shop modify meno varchar2(50);

--删除字段
alter table tb_shop drop columy memo;

　　3.Truncate Table

--快速删除表中内容
truncate table tb_shop;

--删除表及表结构
drop table tb_shop;

　　4.Insert

--向表tb_shop中插入数据(和SQL Server中一样)
insert into tb_shop values(3,'S003','电脑',3400.00,1,'11-3月-11')

　　5.Update

--将tb_shop表中ID为3的数据的shop_name='电脑'改为'笔记本电脑'
update tb_shop set shop_name='笔记本电脑' where Id=3;

　　6.Delete

--删除tb_shop表中Id为3的数据
delete from tb_shop where Id=3;

　　7.Select

　　在查询之前，我们先了解一下Orcale的权限分配，大家知道Orcale有很多账户，不同的账户权限不一样，当我们用除sys和system账户登录外的其他账户登录后，在未授权的情况下我们只能操作示例数据表和当前账户自己创建的表，当前用户要想操作其他用户的表需要用sys或system或需要访问表的所有者账户给当前账户授权，如：我当前登录账户是scott,而我要访问hr账户的emp表，这个时候要是没有sys,system或hr账户的授权，scott账户是无权访问的

--在使用hr账户时，可能hr还未解锁，先解锁hr账户(以sys或system账户登录)
alter uer hr account unlock;
--解锁后的账户默认密码为空，需要设置新密码(我们设置hr账户的新密码为tiger)
alter user hr identified by tiger

--给scott查询hr账户的emp表的权利(以sys或system或hr账户登录)
grant select on hr.emp to scott;

　　受过权后，我们就可以查询了

--使用scott账户登录查询hr账户的emp表数据
select * from hr.emp;

 

　　当我们使用完后，可以通过Revoke关键字撤销刚才的授权

--将scott账户查询hr.emp的权限撤销(以sys,system或hr账户登录)
revoke select on hr.emo from scott;

　　8.子查询

　　　　a.当行子查询

--查询employees表中工资最高的员工信息
select * from employees where salary=(select max(salary) from employees)

　　　　b.多行子查询(多行运算符:in,not in,exists,not exists,all和any)

--查询工资高于部门为20的员工的工资所有员工信息
select * from employees where salary>=all(select salary from employees where department_Id=20)

　　　　c.多列子查询

--查询各部门中工资最低的员工信息
select * from employees where (salary,department_id) in
(
select min(salary),department_id from employees group by department_id
)order by department_Id;

　　　　d.相关子查询

--查询负责管理其他员工的所有员工信息
select * from employees a where exists
(
select * from employees b where b.manager_id=a.employee_id
)order by department_id,employee_id;

 

5.使用TCL事务控制语言

　　数据存储的逻辑单位是数据块，数据操作的逻辑单位是事务

　　1.提交

--当我们在插入数据时，只有当我们使用了Commit后，数据才正真插入的数据表中
--此情况只存在与使用SQL*plus时
insert into emp values(207,'张三丰','12-2月-10',90)
--此时以创建一行，我们查询是却看不到
--只有当我们Commit了，才可以看到数据
commit;

　　2.回滚

--仅限于使用SQL*Plus
--先删除emp表所有数据
delete from emp;
--此时再查询时，emp表中没有数据
--当我们使用rollback回滚后
rollback;
--查询时，数据依然存在

　　3.回滚部分事务

--删除emp_id为101的员工
delete from emp where emp_id=101;
--设置保存点
savepoint p1;
--再删除emp_id为102的员工
delete from emp where emp_id=102;
--此时使用rollback回滚到p1
rollback to p1;
--查询结果发现emp_id为101的数据被删除，emp_id为102的员工没有被删除

 

 6.Orcale中常用函数

　　在书函数之前，向大家介绍Orcale中的虚拟表dual，它有一行一列，所有者是sys，但可以被所有账户使用。

　　1.单行函数

　　　　(1).日期函数

　　　　　　a.ADD_MONTHS:用于返回指定的日期加上指定的月数后的日期值

--根据dual表获取系统当前时间
select sysdate 当前系统时间 from dual;
--比如当前时间是10-7月-11(2011年7月10日)
select add_months(sysdate,1) 新的日期 from dual;
--则返回新的日期是:10-8月-11(2011年8月10日)

　　　　　　b.MONTHS_BETWEEN:用于返回两个日期之间的月份数

--假设当前系统时间为:10-7月-11
select months_between('10-10月-11',sysdate) 相差的月份 from dual;
--此时可能返回的是一个小数，我们可以使用floor函数取整
select floor(months_between('10-10月-11',sysdate) 相差的月份 from dual;)

　　　　　　c.LAST_DAY:用于返回指定日期对应月份的最后一天

--假设当前时间是:10-7月-11
select last_day(sysdate) 当月的最后一天 from dual;
--此时返回:31(7月的最后一天是31号)

　　　　　　d.NEXT_DAY:用于返回指定下一个星期几的日期

--假设当前时间为10-7月-11(星期日)
select next_day(sysdate,'星期五') next_day from dual;
--返回下个星期五的日期(如果当前时间是星期一，则返回当前星期五的日期)
--上面的SQL语句返回15-7月-11(星期五)

　　　　　　e.EXTRACT:用于提取日期中特定部分

　　　　　　EXTRACT(fmt from d):d表示指定的日期,fmt表示提取部分的格式(其值可以为year,month,day,hour,minute,second)

--假设当前时间为:10-7月-11
select extract(year from sysdate) 年,extract(month from sysdate) 月,extract(day from sysdate) from dual;
--返回:
         --年:2011,月:7,日:10

　　　　(2)字符函数

　　　　　　　　a.LOWER(c):小写转换

select lower('Scce') from dual;
--返回:scce

　　　　　　　　b.UPPER(c):大写转换

select upper('scce') from dual;
--返回:SCCE

c.LTRIM(c1,[,c2]):删除c1左侧所包含的c2中的任何字符，遇到不属于c2中的字符时结束，并返回剩余字符串，若c2省略，默认为空格

select ltrim('adminscce','admin') from dual;
--返回:scce

　　　　　　　　d.RTRIM(c1,[,c2]):删除c1右侧所包含的c2中的任何字符，遇到不属于c2中的字符时结束，并返回剩余字符串，若c2省略，默认为空格

select rtrim('adminscce','scce') from dual;
--返回:admin

 　　　　　　　　e.REPLACE(c1,c2[,c3]):将c1中出现的c2都替换成c3并返回剩余字符串。c3默认为null,如果c3为null,则所有出现c2的地方的字符串都删除。如果c2为null,则返回c1,如果c1为null,返回null.

select replace('a*b*c','*') from dual;
--SQL中c3为null,将c1('a*b*c')中的c2('*')都删除,所以返回:abc

　　　　　　　　f.SUBSTR(c,m[,n]):返回c的子串,其中m是子串的开始位置，n为子串的长度。如果n省略，则返回从m开始的所有子串(注:Orcale中的索引是从1开始的)

select substr('adminscce',6,4) from dual;
--返回:scce

　　　　　　　　g.CONCAT(c1,c2):连接字符串

select concat('admin','scce') from dual;
--返回:adminscce

 　　　　　　　　h.LENGTH(c):返回字符串的长度

select length('adminscce') from dual;
--返回:9

 　　　　(3)数学函数

　　　　　　a.ABS:取绝对值

select abs(-1) from dual;
--返回:1

 　　　　　　b.CEIL:向上取整

select ceil(1.33) from dual;
--返回:2

 　　　　　　c.FLOOR:向下取整

select floor(1.33) from dual;
--返回:1

 　　　　　　d.POWER:求幂

select power(2,3) from dual;
--返回2的3次方:8

 　　　　　　e.MOD:取余

select mod(10,3) from dual;
--返回:1

 　　　　　　f.ROUND:四舍五入

select round(12.126,2) from dual;
--返回:12.13

 　　　　　　g.TRUNC:截断

select trunc(12.126,2) from dual;
--返回:12.12

 　　　　(4)转换函数

　　　　　　a.TO_CHAR(d|n [,fmt]):用于将日期或数字以指定的格式转换为VARCHAR2数据类型的值

　　　　　　　　d表是日期类型，n表示数据类型，fmt指定日期或数字的格式

--假设当前时间为10-7月-11
select tr_char(sysdate,'yyyy-mm-dd') 格式日期 from dual;
--返回:2011-07-10
select to_char(12.3456,'9999.99') 格式数据 from dual;
--返回:12.35
select to_char(12.3456,'L9999.99') 格式数据 from dual;
--返回:￥12.35

　　　　　　b.TO_DATE(char[,fmt]):将字符类型转换为日期数据类型

　　　　　　　　char表示字符类型，fmt日期格式

select to_date('2011-7-10','yyyy-mm-dd')-to_date('2011-7-15','yyyy-mm-dd') 日期差距 from dual;
--返回:-5

 　　　　　　c.TO_NUMBER(char):将包含数字的字符串转换为NUMBER类型

 　　　　　　　　　　char表示字符类型

select to_number('123')+2 求和 from dual;
--返回:125

 　　　　(5)其他函数

　　　　　　a.NVL(expr1,expr2):用于将空值替换为指定值

　　　　　　如果expr1不是null,则返回expr1，否则返回exr2

 

　　　　　　b.NVL2(expr1,expr2,expr3):与NVL一样

　　　　　　如果expr1不为null,则返回expr2，否则返回expr3

 

　　　　　　c.DECODE:相当于一个条件语句

　　　　　　DECODE(input_value,value,result[,value,result...][,default_result]);

　　　　　　input_value:表示视图处理的值

　　　　　　value:表示一组成序偶的数值。如果输入值与之匹配成功，则返回相应的结果

　　　　　　result:表示一组成序偶的结果值

　　　　　　default_value:表示未能与任何序偶匹配成功时函数返回的默认值

select decode(id,1,100,2,200,3,300,400) from dual;
--如果id=1,则返回100
--如果id=2,则返回200
--如果id=3,则返回300
--如果id为其他值,则返回400

　　　　(6)查询函数

　　　　　　a.OVER:用于计算基于组的某种聚合值，该函数针对每个组返回多行

--创建示例使用的表
create table tb_sale
(
ID number(10) primary key,
saleDate date not null,
sale number(10) not null
);

select * from tb_sale;
--向示例表中添加数据
insert into tb_sale values(1,'1-1月-10',100)
insert into tb_sale values(2,'2-1月-10',200)
insert into tb_sale values(3,'3-1月-10',300)
insert into tb_sale values(4,'4-1月-10',400)


--OVER窗口函数
select a.saledate,a.sale,sum(a.sale) over(order by a.saledate) salesum from tb_sale a;

 　　　　　　b.RANK函数:用于计算一个值在一组值中的排位，排位是以1开始的连续整数

　　　　　　RANK() OVER([PARTITION BY column] ORDER BY order_by_clause) RANK

　　　　　　PARTITION BY column:表示参考的组列，可以省略

　　　　　　order_by_clause:表示需要参与计算排位的列　　　　　　

 

--示例使用的表
create table tb_score
(
ID number(10) primary key,
STUDID varchar2(10) not null,
STUDNAME varchar2(20) not null,
MATH number(2) not null,
ENGLISH number(2) not null,
CHINA number(2) not null
)

--添加数据
insert into tb_score values(1,'S001','张三',60,70,80)
insert into tb_score values(2,'S002','李四',60,70,80)
insert into tb_score values(3,'S003','王五',80,75,80)
insert into tb_score values(4,'S004','赵六',70,80,90)
insert into tb_score values(5,'S005','张小小',70,80,90)

select * from tb_score
--rank函数结合over窗口函数使用
select a.ID,a.studID,a.studname,a.math+a.english+a.china sumscore,rank() over(order by a.math+a.english+a.china desc) rank from tb_score a

 　　　　　　c.ROLLUP:将rollup函数与group by命令配合使用，可以提供信息汇总功能

　　　　　　语法:GROUP BY ROLLUP(分组的列);

--示例使用的表
create table tb_emp
(
ID number(10) primary key,
EMPNO varchar2(20) not null,
SHOPID varchar2(20) not null,
SALES number(10) not null,
SALEYEAR number(10) not null
)

select * from tb_emp
--添加数据
insert into tb_emp values(1,'10','S001',100,2007)
insert into tb_emp values(2,'10','S001',300,2008)
insert into tb_emp values(3,'10','S002',300,2007)
insert into tb_emp values(4,'10','S003',300,2008)
insert into tb_emp values(5,'20','S001',150,2007)
insert into tb_emp values(6,'20','S001',350,2008)
insert into tb_emp values(7,'20','S002',200,2007)
insert into tb_emp values(8,'20','S002',500,2008)
--对销售员的销售记录表进行分组显示，计算个员工个商品的销售总额，并实现汇总小计功能
select a.EMPNO,a.SHOPID,sum(a.SALES) sumsales from tb_emp a group by rollup(a.EmpNo,a.ShopID)

 　　　　　　d.LAG函数:lag分析函数可以在一次查询中取出同一字段的钱N行的数据，一般与OVER窗口函数结合使用

　　　　　　  语法: LAG(列名,N) OVER(.....)

 

select a.empno,a.ename,lag(a.empno) over(order by a.empno) lag from emp a;
--结果:

 　　　　　e.first_value分析函数:用于返回结果集中排在第一位的值

　　　　　　　　语法:FIRST_VALUE(expr) OVER(....)

--查询deptno为20和30的各部门最富有的员工
select a.empno,a.ename,a.sal,a.deptno,first_value(a.ename) over(partition by a.deptno order by a.sal desc) rich_men from emp a where a.deptno in(20,30);
--结果:

 

　　好了，今天我们先学习到这儿，期待下次我们的一起学习。