Oracle学习笔记（二）

三、函数

3.1 函数介绍

​ 根据函数的返回结果，分为单行函数和多行函数。

1. 单行函数：对应在表记录时，一条记录返回一个结果。
2. 多行函数：也称组函数或聚合函数，此类函数可同时对多条记录进行操作，并返回一个结果。

3.2 常用单行函数

3.2.1 字符函数

concat(x,y) 连接字符串x和y

instr(x,str,start,n) 在x中查找str，可以指定从start开始，也可以指定从第n次开始

length(x) 返回x的长度

lower(x) 转换为小写

upper(x) 转换为大写

举例：

查看10部门员工的姓名长度并且按照员工姓名字符长度进行升序排序
select ename,deptno,length(ename) from emp where deptno = 10 order by length(ename) asc;

查询20部门员工姓名和工资以及所属部门的编号，并且将姓名转换为小写
select ename,sal,deptno,lower(ename) lowerName from emp where deptno = 20;

3.2.2 数学函数

abs(x) x的绝对值

ceil(x) 向上取整

floor(x) 向下取整

round( ) 四舍五入

mod(x,y) 对x求y的余数

poer(x,y) x的y次幂

sqrt(x) x的平方根

3.2.3 日期函数

​ oracle提供了很多和日期相关的函数，包括日期的加减，在日期加减时的规律如下：

​ 日期 - 数字 = 日期

​ 日期 + 数字 = 日期

​ 日期 - 日期 = 数字

sysdate 当前系统时间

current_date 返回当前系统日期

last_day(d1) 返回日期d1所在月份最后一天的日期

months_between(d1,d2) 返回日期d1到日期d2之间的月份

3.2.4 转换函数

to_char(x,c) 将日期或数据x按照c的格式转换为char数据类型

to_date(x,c) 将字符串x按照c的格式转换为日期

to_number(x) 将字符串x转化为数字型

举例：

把小写的字符串转换成大写的字符
select upper('smith') from dual;

四舍五入
select round(12.534) from dual;

查询雇员进入公司的周数
select ename,round((sysdate-hiredate)/7) from dual;

查询所有雇员入职日期，将年月日分开
select empno,ename,
to_char(hiredate,'yyyy') 年,
to_char(hiredate,'mm') 月,
to_char(hiredate,'dd') 日，
from emp;

年月日不分开
select empno,ename,to_char(hiredate,'yyyy-mm-dd') from emp;

将字符串数据转换成日期类型
select to_date('1985-04-22','yyyy-mm-dd') from dual;

3.3 多行函数（聚合函数）

组函数同时对多条记录进行操作，并返回一个结果

avg( ) 平均值

sum( ) 求和

min( ) 最小值

max( ) 最大值

count( ) 统计

**注意：**null不参与运算

举例：

查询出所有员工的记录数
select count(*) from emp;
不建议使用count(*)，可以使用一个具体的列，以免影响性能
select count(ename) from emp;

查询出员工的最低工资
select min(sal) from emp;

查询出员工的最高工资
select max(sal) from emp;

查询出员工的平均工资
select avg(sal) from emp;

查询出20部门的员工的工资总和
select sum(sal) from emp t where t.deptno = 20;

3.4 group by 分组

​ 将满足条件的记录进一步按照某特性进行分组，提取每组记录中的共性。

select...from...where...group by...

3.5 having 过滤组信息

​ 当获取的族信息也需要满足一定条件时，通过having来过滤组的条件。

select...from...where...group by...having...

3.6 过滤行记录和组信息

​ 行记录的过滤是针对每条记录的筛选，组信息的过滤是针对组的筛选，是可以同时出现的，先筛选行，再过滤组。

where 筛选行，只能出现行信息

having 过滤组，只能出现组信息

select...from...where...group by...having...

举例：

查询每个部门的人数
select deptno,count(ename) from emp group by deptno;

按部门分组，查询出部门名称和部门的员工数量
select d.deptno,d.dname,count(ename)
from emp e,dept d where e.deptno = d.deptno
group by d.deptno,d.dname;

查询出部门人数大于5人的部门（需要给count(ename)加条件，不能使用where，可以使用having）
select d.deptno,d.dname,count(ename)
from emp e,dept d
where e.deptno = d.deptno
group by d.deptno,d.dnamae
having count(ename) > 5;

四、表的管理

4.1 介绍

SQL语句主要可以划分为以下三个类别：

1. DDL (Data Definition Languages) 语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create、drop、alter等。
2. DML (Data Mainipulation Languages) 语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括insert、delete、update、select等。
3. DCL (Data Control Languages) 语句：数据控制语句，用户控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别，常用的语句关键字包括grant、revoke等。

4.2 约束

​ 数据库中通过约束来对每个字段中的数据的合法性进行规范。

• 主键约束（primary key）
• 唯一性约束（unique）
• 非空约束（not null）
• 外键约束（foreign key）
• 检查约束（check）

主键：

1. 键列必须具有唯一性，且不能为空，相当于unique + not null
2. 一个表只允许有一个主键
3. 主键所在列必须具有索引（主键的唯一约束通过索引来实现），如果不存在，将会在索引添加的时候自动创建

唯一键：

​ 唯一性约束可作用在单列或多列上，唯一性约束保证每一行的唯一性。unique允许null值，unique约束的列可存在多个null。

非空约束：

​ 非空约束作用的列也叫强制列，强制键列中必须有值。若建表时使用default关键字指定了默认值，则不可输入。

外键约束：

​ 外键约束定义在具有父子关系的子表中，外键约束能使得子表的列对应父表的主键列，用于维护数据库的完整性。

1. 外键约束的子表中的列和对应父表中的列数据类型必须相同，列名可以不同。
2. 对应的父表列必须存在主键约束（primary key）或唯一约束（unique）
3. 外键约束列允许null值，对应的行就成了孤行了

检查约束：

​ 检查约束可用来实施一些简单的规则，如列值必须在某个范围内。

4.3 表操作

4.3.1 创建表

create table 表名(
 字段名  类型（长度）
 ...其他字段...
 );
 
 create table student(
 	sid number(10),
    sname varchar2(10),
    sbirthday date
 ) tablespace space;
 
插入数据：
insert into student(sid,sname,sbirthday)
values(1,"张三",to_date('1999-12-22','yyy-MM-dd'));

4.3.2 修改表结构

1. 修改表名

   rename 原表名 to 新表名

2. 修改列名

   alter table 表名 rename column 原列名 to 新列名

3. 修改字段类型

   alter table 表名 modify（字段 类型）

4. 添加列

   alter table 表名 add 字段 类型

5. 删除列

   alter table 表名 drop column 字段

举例：

在student表中添加列address
alter table student add(address varchar2(10));
把student表的address列的长度修改成20
alter table student modify(address varchar2(20));

4.3.3 删除表

drop table 表名

4.4 表数据的更新

1. insert（增加）

   标准写法：

   insert into 表名（列名1，列名2，…）values（值1，值2，…）;

   简单写法：

   insert into 表名 values（值1，值2，…）；

   一次插入多条记录：

   insert all

   into 表名1 values（值1，值2，…）

   into 表名2 values（值1，值2，…）

   select * from dual；

   **注意：**使用简单的写法必须按照表中的字段的顺序来插入值，而且如果有为空的字段使用null。

2. update（修改）

   全部修改：update 表名 set 列名1 = 值1，列名2 = 值2，…

   局部修改：update 表名 set 列名1 = 值1，列名2 = 值2，… where 修改条件；

3. delete（删除）

   delete from 表名 where 删除条件；

   **注意：**在删除语句中如果不指定删除条件的话就会删除所有的数据

​ 因为oracle的事务对数据库的变更的处理，必须做提交事务才能让数据真正的插入到数据库中，在同样的执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据库，如果事务提交后则不可以再回滚。

​ 提交：commit

​ 回滚：rollback