1.1 oracle的安装、配置
1.1.1将oracle配置到虚拟机上
在任务管理器中找到服务,看到以下五个服务即算作安装完成:
OracleJobSchedulerXE OracleJobSchedulerXE 已停止
OracleServiceXE 6344 OracleServiceXE 正在运行
OracleXEClrAgent OracleXEClrAgent 已停止
OracleMTSRecoveryService OracleMTSRecoveryService 已停止
OracleXETNSListener 104 OracleXETNSListener 正在运行
1.1.2 将PLSQL Developer安装到主机
*配置环境变量:path中放instantclient_19_5文件夹的路径
*在C:\instantclient_19_5文件夹中新建一个C:\instantclient_19_5\config文件夹,并放入一个sqlnet.ora配置文件,并将虚拟机的端口号信息写到里面
*在环境变量中新建TNS_ADMIN —>C:\instantclient_19_5\config这个文件夹路径
*在虚拟机的oracle安装路径中找到C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN
下的sqlnet.ora,在里面添加语句 NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)。
1.1.3解决中文乱码问题
中文乱码问题解决
1.查看服务器端编码
select userenv(‘language’) from dual;
我实际查到的结果为:AMERICAN_AMERICA.ZHS16GBK
2.执行语句 select * from V$NLS_PARAMETERS
查看第一行中PARAMETER项中为NLS_LANGUAGE 对应的VALUE项中是否和第一步得到的值一样。
如果不是,需要设置环境变量.
否则PLSQL客户端使用的编码和服务器端编码不一致,插入中文时就会出现乱码.
3.设置环境变量
计算机->属性->高级系统设置->环境变量->新建
设置变量名:NLS_LANG,变量值:第1步查到的值, 我的是 AMERICAN_AMERICA.ZHS16GBK
4.重新启动PLSQL,插入数据正常
1.2 Oracle体系结构
1.2.1 数据库
Oracle 数据库是数据的物理存储。这就包括(数据文件 ORA 或者 DBF、控制文件、联机日志、参数
文件)。其实 Oracle 数据库的概念和其它数据库不一样,这里的数据库是一个操作系统只有一个库。
可以看作是 Oracle 就只有一个大数据库。
1.2.2 实例
一个 Oracle 实例(Oracle Instance)有一系列的后台进程(Backguound Processes)和内存结构
(Memory Structures)组成。一个数据库可以有 n 个实例。
1.2.3 用户
用户是在实例下建立的。不同实例可以建相同名字的用户
1.2.4 表空间
表空间是 Oracle 对物理数据库上相关数据文件(ORA 或者 DBF 文件)的逻辑映射。一个数据库在逻
辑上被划分成一到若干个表空间,每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一
个表空间(称之为 system 表空间)。
每个表空间由同一磁盘上的一个或多个文件组成,这些文件叫数据文件(datafile)。一个数据文件只能
属于一个表空间。
1.2.5 段、区、块
1.2.6 数据文件(dbf、ora)
数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的,真正是在某一个或者多个数
据文件中。而一个表空间可以由一个或多个数据文件组成,一个数据文件只能属于一个表空间。一旦数
据文件被加入到某个表空间后,就不能删除这个文件,如果要删除某个数据文件,只能删除其所属于的
表空间才行。
注: 表的数据,是由用户放入某一个表空间的,而这个表空间会随机把这些表数据放到
一个或者多个数据文件中。
由于 oracle 的数据库不是普通的概念,oracle 是由用户和表空间对数据进行管理和存放的。但是表
不是由表空间去查询的,而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表!
这里区分就是用户了!
1.3创建表空间[理解]
create tablespace itlb
datafile 'c:\itlb.dbf'
size 100m
autoextend on
next 10m
itlb 为表空间名称
datafile 指定表空间对应的数据文件(物理结构)
size 表空间的初始大小
autoextend on 自动增长 ,当表空间存储都占满时,自动增长
next 一次自动增长的大小
1.4用户
1.4.1创建用户
create user itlbuser
identified by itlb
default tablespace itlb
[ACCOUNT LOCK|UNLOCK]
------------------------------------------------------------------------------------
itlbuser 是账号
identified by 后面是密码(密码不能用数字开头)
default tablespace 后面是表空间
LOCK|UNLOCK 创建用户时是否锁定,默认为锁定状态。锁定的用户无法正常的登录进行数据库操
作
*oracle 数据库与其它数据库产品的区别在于,表和其它的数据库对象都是存储在用户下的。
1.4.2 用户授权
尽管用户成功创建,但是还不能正常的登录 Oracle 数据库系统,因为该用户还没有任何权限。
Oracle 用户对数据库管理或对象操作的权利,分为系统权限和数据库对象权限。系统权限比如:
CREATE SESSION,CREATE TABLE 等,拥有系统权限的用户,允许拥有相应的系统操作。数据库对象
权限,比如对表中的数据进行增删改操作等,拥有数据库对象权限的用户可以对所拥有的对象进行对应
的操作。
还有一个概念就是数据库角色(role),数据库角色就是若干个系统权限的集合。Oracle 中已存在三
个重要的角色: connect 角色, resource 角色, dba 角色。
*CONNECT 角色: 是授予最终用户的典型权利,最基本的。主要应用在临时用户,特别是那些不需
要建表的用户,通常只赋予他们 CONNECT role。CONNECT 是使用 Oracle 的简单权限, 拥有
CONNECT 角色的用户,可以与服务器建立连接会话(session,客户端对服务器连接,称为会
话)。
CREATE SESSION 建立会话
*RESOURCE 角色:是授予开发人员的。更可靠和正式的数据库用户可以授予RESOURCE role。
RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器
(trigger)、索引(index)等。
CREATE PROCEDURE 建立过程
CREATE SEQUENCE 建立序列
CREATE TABLE 建表
CREATE TRIGGER 建立触发器
CREATE TYPE 建立类型
*DBA 角色:拥有全部特权,是系统最高权限,只有 DBA 才可以创建数据库结构,并且系统权限也
需要 DBA 授出,且 DBA 用户可以操作全体用户的任意基表,包括删除
可以把某个权限授予某个角色,可以把权限、角色授予某个用户。系统权限只能由 DBA
用户授权,对象权限由拥有该对象的用户授权,授权语法是:
-- GRANT 角色|权限 TO 用户
-- 例如:
grant CONNECT to itlbuser;
GRANT RESOURCE TO itlbuser;
--回收权限的语法是:
REVOKE 角色|权限 FROM 用户
--修改用户的密码语法是:
ALTER USER 用户名 IDENTIFIED BY 新密码
--修改用户处于锁定(非锁定)状态
ALTER USER 用户名 ACCOUNT LOCK|UNLOCK
1.5 Oracle数据类型
表中的列使用到的常见数据类型如下:
类型 | 含义 |
---|---|
CHAR(length) | 存储固定长度的字符串。参数length指定了长度,如果存储的字符串长度 小于length,用空格填充。默认长度是1,最长不超过2000字节。 |
VARCHAR | 字符串 |
VARCHAR2(length) | 存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是 1,最长不超过4000字符。 |
NUMBER(n) | NUMBER(n)表示一个整数,长度是 n |
NUMBER(m,n) | NUMBER(m,n):表示一个小数,总长度是 m(默认38),小数是 n,整数是 m-n |
DATE | 存储日期和时间,存储纪元、4位年、月、日、时、分、秒,存储时间从 公元前4712年1月1日到公元后4712年12月31日。 |
TIMESTAMP | 不但存储日期的年月日,时分秒,以及秒后6位,同时包含时区 |
CLOB | 存储大的文本,比如存储非结构化的XML文档,可存 4G |
BLOB | 存储二进制对象,如图形、视频、声音等,可存 4G |
对于日期类型,可以使用 sysdate 内置函数可以获取当前的系统日期和时间,返回 DATE类型,用
systimestamp 函数可以返回当前日期、时间和时区。
*在 Oracle 中,日期是国际化的,不同的区域安装的数据库,默认的日期格式不同,因
此为了程序便于移植,日期的输入要使用 TO_DATE 函数对日期格式化后输入,采用格
式化字符串对日期进行格式化时,格式化字符串中字符不区分大小写,常见的格式化
字符如下:
- yyyy 表示四位年份
- mm 表示两位月份,比如 3 月表示为 03
- dd 表示两位日期
- hh24 表示小时从 0-23,hh12 也表示小时从 0-11。
- mi 表示分钟
- ss 表示秒
1.6表的管理—>在用户下使用
1.6.1 创建表格
--
Create table 表名(
字段 1 数据类型 [default 默认值] [not null],
字段 2 数据类型 [default 默认值] [not null],
...
字段 n 数据类型 [default 默认值] [not null]
);
--
create table student(
sid number(10) not null,
name varchar2(10),
gender number(1) default 1,
birthday date
);
1.6.2删除表格
DROP TABLE 表名
1.6.3 修改表名
alter table student rename to students;
1.7修改表
#在 sql 中使用 alter 可以修改表:
1.7.1添加列
ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值],列名 2 类型
[DEFAULT 默认值]...)
alter table person add(phone number(11));
alter table student add(age number(5) default 18);
1.7.2修改列属性
ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值],列名 2 类型
[DEFAULT 默认值]...)
alter table student modify(age number(8) default 13);
1.7.3修改列名
ALTER TABLE 表名称 RENAME COLUMN 列名 1 TO 列名 2
alter table student rename column name to sname;
1.7.4删除列
ALTER TABLE 表名称 DROP COLUMN 列名;
alter table students drop column age;
1.7.5添加约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束内容
常见约束:
*not null (非空):如果在列上定义了not null,那么当插入数据时,必须为列提供,数据不能 为NULL。约束只能在列级定义,不能在表级定义。 |
---|
*unique (唯一):当定义了唯一约束后,该列值是不能重复的,但是可以为null。 |
*primary key (主键):用于唯一的标识表行的数据,当定义主键约束后,该列不但不能重复而 且不能为NULL。 一张表最多只能有一个主键(联合主键),但是可以由多个unique约束。创建主键或唯一约束后,ORACLE会自动创建一个与约束同名的索引(UNIQUENES为UNIQUE唯一索引)。需要注意的是:每个表只能有且有一个主键约束。 |
*foreign key (外键):用于定义主表和从表之间的关系,外键约束要定义在从表上,主表则必 须具有主键约束或是unique约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在 或是为NULL。用来维护从表(Child Table)和主表(Parent Table)之间的引用完整性. 外键约束是个有争议性 的约束,它一方面能够维护数据库的数据一致性,数据的完整性。防止错误的垃圾数据入库; 另 外一方面它会增加表插入、更新等SQL性能的额外开销,不少系统里面通过业务逻辑控制来取消外 键约束。例如在数据仓库中,就推荐禁用外键约束。 |
check (检查):用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal 列值在1000~2000之间,如果不在1000~2000之间就会提示出错。 |
default (默认):为列中的值设置默认值,default …,如果已经定了值,默认值就无效了 |
1.7.5.1 约束命名规范
非空约束 NN_表名_列名
唯一约束 UK_表名_列名
主键约束 PK_表名
外键约束 FK_表名_列名
条件约束 CK_表名_列名
默认约束 DF_表名_列名
1.7.5.2创建约束
1.not null(非空)
1. create table t1( pid number(10) not null);
2. create table t1(pid number(10) constraint nn_t1_id not null);
3. alter table t1 modify pid constraint nn_t1_id not null;
- unique(唯一)
1. create table t1(qq number, constraint un_t1_qq unique(qq));
2. create table t1(qq number constraint un_t1_qq unique);
2. alter table t1 add constraint un_t1_qq unique(qq);
- primary key(主键)
1. create table t1(id number, constraint pk_t1_id primary key(id));
2. create table t1(id number constraint pk_t1_id primary key;
3. alter table t1 add constraint pk_t1_id primary key(id);
- foreign key(外键)
1. create table t2(id number,cc number,constraint fk_t2_id foreign key(id)
references t1(id));
2. alter table t2 add constraint pk_t2_id foreign key(id) references t1(id);
其他关键字:
ON DELETE CASCAED :该选项用于指定级联删除选项。如果在定义外部键约束时指定了该选
项,那么当删除主表数据时会级联删除从表的相关数据。
ON DELETE SET NULL :该选项用于指定转换相关的外部键值为NULL,如果在定义外部键约
束时指定了该选项,那么当删除主表数据时会将从表外部键列的数据设置为NULL。
- check(检查性约束)
1. create table t3(id number,sal number,constraint ck_t3_sal check(sal
between 5000 and 50000));
-- 等同于
create table t3(id number,sal number,constraint ck_t3_sal check(sal >= 5000
and sal <=50000));
--
2. create table t3(id number,sal number constraint ck_t3_sal check(sal
between 5000 and 50000));
3. alter table t3 add constraint ck_t3_sal check(sal>5000);
1.7.5.3 修改约束名
-- 修改约束名
alter table emp rename constraint {name1} to {name2};
1.7.5.4禁止/激活/删除约束
禁止约束指使约束临时失效。当禁止了约束之后,约束规则将不再生效。
ALTER TABLE table_name DISABLE CONSTRAINT constaint_name [CASCAED];
--CASCAED用于指定级联禁止从表的外部键
激活约束是指使禁止的约束继续生效,需要注意激活约束前要先删除"违规"数据
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
当删除特定表的主键约束时,如果该表具有相关的从表,那么在删除主键约束时必须带有 CASCAED 选项
ALTER TABLE table_name DROP CONSTRAINT constraint_name ;
-- 例:删除主键约束,级联删除外键约束
-- alter table t1 drop primary key cascade;
1.7.5.5显示约束信息
select constraint_name,table_name,column_name from user_cons_columns where
table_name='T1';
总结
(1) 如果增加UNIQUE、PRIMARY KEY、FOREIGN KEY 和CKECK 必须使用ALTER TABLE语句的ADD子
句;
(2) 如果增加NOT NULL约束,那么必须使用ALTER TABLE语句的MODIFY子句
(3) 在同一个方案中,约束名必须唯一,并且约束名也不能与其他对象同名。
1.8数据操纵语言(DML)
1.8.1 新增数据
INSERT INTO 表名(列名,列名1,......)VALUES (数据,数据1,......)
1.8.2 查询数据
SELECT 列名 FROM 表名;查询单列
1.8.3 修改数据
UPDATE 表名 set 列名=数据,........ where 条件
1.8.4 删除数据
DELETE FROM 表名 where 条件
***delete和truncate的区别:
delete和truncate都能把表中的数据全部删除。他们的区别是:
1.truncate是DDL命令,删除的数据不能恢复;delete是DML命令,删除后的数据可以通过日志文件恢复
2.如果一个表中的数据记录很多,truncate相对delete速度快
3.如果用两者删除完了所有元素,表中自动递增列的起始不同。truncate是从初始值开始,delete是从删除前的下一个值开始
1.9序列(自动增长)
在很多数据库中都存在一个自动增长的列,如果现在要想在 oracle 中完成自动增长的功能,则只能依靠序
列完成,所有的自动增长操作,需要用户手工完成处理。序列中的可以升序生成,也可以降序生成。创建
序列的语法是:
CREATE SEQUENCE 序列名
--从某一个整数开始,升序默认值是 1,降序默认值是-1。
[START WITH num]
--增长数。如果是正数则升序生成,如果是负数则降序生成。升序默认值是 1,
--降序默认值是-1。
[INCREMENT BY num]
--指最大/最小值。
--NOMAXVALUE这是最大值的默认选项,升序的最大值是:10^27,降序默认值是-1
--NOMINVALUE这是默认值选项,升序默认值是 1,降序默认值是-10^26。
[MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE]
--CYCLE表示如果升序达到最大值后,从最小值重新开始;
--如果是降序序列,达到最小值后,从最大值重新开始。
--NOCYCLE :表示不重新开始,序列升序达到最大值、降序达到最小值后就报错。默认NOCYCLE。
[CYCLE|NOCYCLE]
--CACHE :使用 CACHE 选项时,该序列会根据序列规则预生成一组序列号。保留在内存中,当使用
--下一个序列号时,可以更快的响应。当内存中的序列号用完时,系统再生成一组新的序列号,并保
--存在缓存中,这样可以提高生成序列号的效率。Oracle 默认会生产 20个序列号。
--NOCACHE :不预先在内存中生成序列号。
[CACHE num|NOCACHE]
例:
-- 创建一个自增序列,每次增加1,不重新开始
CREATE SEQUENCE seqpersonid;
-- 创建一个从 1 开始,默认最大值,每次增长 1 的序列,要求 NOCYCLE,缓
-- 存中有 30 个预先分配好的序列号。
CREATE SEQUENCE MYSEQ
MINVALUE 1
START WITH 1
NOMAXVALUE
INCREMENT BY 1
NOCYCLE
CACHE 30;
--序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作:
--nextval :取得序列的下一个内容
--currval :取得序列的当前内容
select seqpersonid.nextval from dual;
select seqpersonid.currval from dual;
--在实际项目中每一张表会配一个序列,但是表和序列是没有必然的联系的,一个序列被哪一张表使用都
--可以,但是我们一般都是一张表用一个序列。
--序列的管理一般使用工具来管理。
--->如果我们要传入一个序列数,只要使用:seqpersonid.nextval 即可,如:
insert into student(sid, name, gender, birthday)
values(seqpersonid.nextval, 'lisi', 1, to_date('1999-12-22', 'yyyy-MM-dd'));
1.10 Scott用户下的表结构[了解]
Oracle 默认安装中,已经创建了一个 scott 用户,默认密码是:tiger,该用户下有四张表分别是:雇员
表(EMP),部门表(DEPT),工资等级表(SALGRADE)和奖金表(BONUS)。
部门表:dept
1 DEPTNO NUMBER(2) 表示部门编号,由两位数字所组成
2 DNAME VARCHAR2(14) 部门名称,最多由14个字符所组成
3 LOC VARCHAR2(13) 部门所在的位置
雇员表:emp
1 EMPNO NUMBER(4) 雇员的编号,由四位数字所组成
2 ENAME VARCHAR2(10) 雇员的姓名,由10位字符所组成
3 JOB VARCHAR2(9) 雇员的职位
4 MGR NUMBER(4) 雇员对应的领导编号,领导也是雇员
5 HIREDATE DATE 雇员的雇佣日期
6 SAL NUMBER(7,2) 基本工资,其中有两位小数,五位整数,一共是七位
7 COMM NUMBER(7,2) 奖金,佣金
8 DEPTNO NUMBER(2) 雇员所在的部门编号
工资等级表:salgrade
1 GRADE NUMBER 工资的等级
2 LOSAL NUMBER 此等级的最低工资
3 HISAL NUMBER 此等级的最高工资
工资表:bonus
1 ENAME VARCHAR2(10) 雇员姓名
2 JOB VARCHAR2(9) 雇员职位
3 SAL NUMBER 雇员的工资
4 COMM NUMBER 雇员的奖金
1.11函数
Oracle SQL 提供了用于执行特定操作的专用函数。这些函数大大增强了 SQL 语言的功能。函数可以接
受零个或者多个输入参数,并返回一个输出结果。Oracle 数据库中主要使用两种类型的函数:
- 单行函数:对每一个函数应用在表的记录中时,只能输入一行结果,返回一个结果,比如:
MOD(x,y)返回 x 除以 y 的余数(x 和 y 可以是两个整数,也可以是表中的整数列)。常用的单行
函数有: - 字符函数:对字符串操作。
- 数字函数:对数字进行计算,返回一个数字。
- 转换函数:可以将一种数据类型转换为另外一种数据类型。
- 日期函数:对日期和时间进行处理。
- 聚合函数:聚合函数同时可以对多行数据进行操作,并返回一个结果。比如 SUM(x)
返回结果集中 x 列的总合。
1.11.1 单行函数
1.11.1.1字符函数:对字符串操作。
字符函数接受字符参数,这些参数可以是表中的列,也可以是一个字符串表达式。下表
列出了常用的字符函数。
函数 | 说明 |
---|---|
ASCII(x) | 返回字符x的ASCII码。 |
CONCAT(x,y) | 连接字符串x和y。 |
INSTR(x, str [,start] [,n]) | 在x中查找str,可以指定从start开始,也可以指定从第n次开始 |
LENGTH(x) | 返回x的长度。 |
LOWER(x) | x转换为小写。 |
UPPER(x) | x转换为大写 |
LTRIM(x[,trim_str]) | 把x的左边截去trim_str字符串,缺省截去空格。 |
RTRIM(x[,trim_str]) | 把x的右边截去trim_str字符串,缺省截去空格。 |
TRIM([trim_str FROM] x) | 把x的两边截去trim_str字符串,缺省截去空格。 |
REPLACE(x,old,new) | 在x中查找old,并替换为new |
SUBSTR(x,start[,length]) | 返回x的字串,从staart处开始,截取length个字符,缺省length, 默认到结尾。 |
initcap(str) | 首字母大写 |
1.11.1.2数字函数:对数字进行计算,返回一个数字。
函数 | 说明 | 示例 |
---|---|---|
ABS(x) | x绝对值 | ABS(-3)=3 |
CEIL(x) | 大于或等于x的最小值 | CEIL(5.4)=6 |
FLOOR(x) | 小于或等于x的最大值 | FLOOR(5.8)=5 |
MOD(x,y) | x除以y的余数 | MOD(8,3)=2 |
POWER(x,y) | x的y次幂 | POWER(2,3)=8 |
ROUND(x[,y]) | x在第y位四舍五入 | ROUND(3.456,2)=3.46 |
SQRT(x) | x的平方根 | SQRT(4)=2 |
TRUNC(x[,y]) | x在第y位截断 | TRUNC(3.456,2)=3.45 |
1.11.1.3日期函数:对日期和时间进行处理。
- ADD_MONTHS(d,n) ,在某一个日期 d 上,加上指定的月数 n,返回计算后的新日期。d 表示日
期,n 表示要加的月数。 - LAST_DAY(d) ,返回指定日期当月的最后一天。
- ROUND(d[,fmt]) ,返回一个以 fmt 为格式的四舍五入日期值, d 是日期, fmt 是格式模型。默
认 fmt 为 DDD,即月中的某一天。
- 如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日,即前半年舍去,后半年作为下一年。
- 如果 fmt 为“MONTH”则舍入到某月的 1 日,即前月舍去,后半月作为下一月。
- 默认为“DDD”,即月中的某一天,最靠近的天,前半天舍去,后半天作为第二天。
- 如果 fmt 为“DAY”则舍入到最近的周的周日,即上半周舍去,下半周作为下一周周日。
与 ROUND 对应的函数时 TRUNC(d[,fmt])对日期的操作, TRUNC 与 ROUND 非常相似,只是不对日
期进行舍入,直接截取到对应格式的第一天。
- EXTRACT(fmt FROM d) ,提取日期中的特定部分fmt 为:YEAR、MONTH、DAY、HOUR、
MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配,也可以与 TIMESTAMP
类型匹配;但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。
HOUR 匹配的结果中没有加上时区,因此在中国运行的结果小 8 小时。
作业中存在许多乱七八糟的函数没有搞清楚
1. 写出计算明天此刻的sql
2. 查询scott用户下所有员工入职距离现在有多少天
3. 查询scott用户下所有员工入职距离现在有多少月
4. 查询scott用户下所有员工入职距离现在有多少年
5. 查询scott用户下所有员工入职距离现在有多少周
--1. 写出计算明天此刻的sql
select sysdate + 1 from dual;
--2. 查询scott用户下所有员工入职距离现在有多少天
select round(sysdate - hiredate) from emp;
--3. 查询scott用户下所有员工入职距离现在有多少月
select ceil(months_between(sysdate, hiredate)) from emp;
--4. 查询scott用户下所有员工入职距离现在有多少年
select extract(year from sysdate) - extract(year from hiredate) from emp;
select round(ceil(months_between(sysdate, hiredate))/12) from emp;
--5. 查询scott用户下所有员工入职距离现在有多少周
select ceil(round(sysdate - hiredate)/7) from emp;
1.11.1.4转换函数:可以将一种数据类型转换为另外一种数据类型。
转换函数将值从一种数据类型转换为另外一种数据类型
- TO_CHAR(d|n[,fmt]) 把日期和数字转换为制定格式的字符串。fmt 是格式化字符串,日期的格
式化字符串前面已经学习过。
SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS') "date"
FROM DUAL;
在格式化字符串中,使用双引号对非格式化字符进行引用。结果中 10 以下的月前面被被补了前导零,
可以使用 fm 去掉前导零.
针对数字的格式化,格式化字符有:
参数 示例 说明
9 999 指定位置处显示数字。
. 9.9 指定位置返回小数点
, 99,99 指定位置返回一个逗号
$ $999 数字开头返回一个美元符号
EEEE 9.99EEEE 科学计数法表示
L L999 数字前加一个本地货币符号
PR 999PR 如果数字式负数则用尖括号进行表示
SELECT TO_CHAR(-123123.45,'L9.9EEEEPR') "date"
FROM DUAL
- TO_DATE(x [,fmt]) 把一个字符串以 fmt 格式转换为一个日期类型,
- TO_NUMBER(x[,fmt]) 把一个字符串以 fmt 格式转换为一个数字。fmt 格式字符参考
1.11.2多行函数(聚合函数)
聚合函数同时对一组数据进行操作,返回一行结果,比如计算一组数据的总和,平均值
等。
名称 | 作用 | 语法 |
---|---|---|
AVG | 平均值 | AVG(表达式) |
SUM | 求和 | SUM(表达式) |
MIN、MAX | 最小值、最大值 | MIN(表达式)、MAX(表达式) |
COUNT | 数据统计 | COUNT(表达式) |
1.12 分组统计
分组统计需要使用 GROUP BY 来分组
语法:SELECT * |列名 FROM 表名 [WEHRE 查询条件] [GROUP BY 分组字段] ORDER BY 列名 1
ASC|DESC,列名 2...ASC|DESC
1.查询每个部门的人数
2.查询出每个部门的平均工资
3.如果我们想查询出来部门编号,和部门下的人数
注意:
1. 如果使用聚合函数,SQL 只可以把 GROUP BY 分组条件字段和聚合函数查询出来,不能有其他字
段。
2. 如果使用聚合函数,不使用 GROUP BY 只可以查询出来分组函数的值
4. 按部门分组,查询出部门名称和部门的员工数量
思考:
1. 查询出部门人数大于 5 人的部门?
2. 查询出部门平均工资大于 2000 的部门?
(having)