1.前言
2.select简单查询
3.单值函数
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
前言:
1.认识oracle
oracle：商业运用第一的关系型数据库，实质上就是一种关系型数据库。
2.数据库与关系型数据库
数据库：数据存储的仓库
关系型数据库：数据库中保存的对象之间可以存在一定的关联关系，并非完全独立。主要反映到以后学习的主外键.
关系数据库是关系或二维表的集合：
orcale数据库
/ \
table_1 table_2
关系数据库是：
由关系型数据库对象组成的。
由关系操作管理。
受数据完整性约束控制。
3.三个名词
(1) sql:结构化的查询语句，操作oracle数据库的语言
(structured query language, sql)
第四代编程语言，之关心做什么，不关心怎么做，结构化查询，易学易用
(2) sqlplus:oracle软件自带的终端可以输入sql，且将sql执行结果显示
sql*plus
↓
sql*plus: 如同操作系统中的shell，sql——>oracle,就是说在oracle数据库执行sql语句之前，需要sql*plus进行编译。
(4) pl/sql:这是一种过程化语言，纯sql中没有逻辑的控制，而在pl/sql中存在逻辑控制
像存储过程和函数就是一种典型的pl/sql语言的实现
4.数据库中的对象
table：表格（实体or关系），由行和列组成，列又称字段，每一行内容为表格的一条完整的数据。
view: 视图，一张表或者多张表的部分或者完整的映射，好比表格照镜子，镜子里面的虚像就是view
除去常见的table和view两种对象以外，oracle数据库还支持如下四种对象
sequence：序列,生成主键值
index：索引，提高数据的访问效率
synonym：同义，方便对象的操作
program unit：程序单元，pl/sql操作的对象
5.五种分类
sql的五大分类:
数据查询（dql）：select
数据操作（dml）： insert, update, delete (增删改) 需要commit
数据定义（ddl）： create, alter, drop, rename, truncate
数据控制（dcl）： grant, revoke
事务控制（dtl）： commit（事务提交）, rollback（事务回滚）, savepoint（设置回滚点）
delete，truncate区别：
delete： 删除表中的一条或者多条记录，该操作需要提交事务
truncate：清空表格,该操作不需要提交事务
oracle数据库环境准备(一般现在使用mysql数据库的是一种趋势):
三种登录方式：
sqlplus "/as sysdba"
sqlplus system/*******
sqlplus username/password
了解三张表以及相互关系
s_emp 员工表
s_dept 部门表
s_region 地区表
建表语句附在文档最后
select查询
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
注意1：distinct关键词只能放在select关键词后面
注意2：如果distinct关键词后面如果出现多列，表示多列联合去重，即多列的值都相同的时候才会认为是重复的记录。
注意3：select语句永远不对原始数据进行修改。
注意4：加减乘除有优先级，加小括号可以改变运算的顺序
注意5：字符串拼接select id,first_name||' '||last_name||','||title as name from s_emp;
sqlplus命令
sqlplus 登录之后,可以使用buff(缓存)来存储/执行/修改要执行的sql语句
这里的buff的特点:
1.buff中只能存储一条sql语句(但是这条sql语句可能有很多行)
2.每次放入新的sql语句,会把之前的覆盖掉
3.每次执行sql语句,都会把这个sql语句放到buff里面
sqlplus基础操作：
sql1
result1
sql2
result2
...
spool off 关闭spool功能
exit：退出
结果不好看，通过column使我们的显示界面好看：column last_name format a15; 可以简写为:col last_name for a15;
排序和限制查询
1.排序:所谓排序，就是根据某个字段的值按照升序或者降序的情况将记录查询出来。排序使用order by字句，该子句只对查询记录显示调整，并不改变查询结果，所以执行权最低，即最后执行。排序关键词：
asc：升序（默认，默认的意思是不加关键词的时候默认为生序排序）
desc：降序
如果有多个列排序，后面的列排序的前提是前面的列排好序以后有重复（相同）的值。
2.限制查询，即指定查询条件进行查询
语法：
select col_name,...
from tb_name
where col_name 比较操作表达式
逻辑操作符and or not
col_name 比较操作表达式
...
注意：
1.限制查询条件，使用where子句
2.条件可以多个，使用逻辑操作符和()进行条件的逻辑整合
3.where子句的优先级别最高
4.比较操作表达式由操作符和值组成
常见的操作：
1》逻辑比较操作符 = > < >= <= !=
2》不等于:三个都表示不等于的意思(经常用的是!=) != <> ^=
select salary from s_emp where not salary=1100;
相当于
select salary from s_emp where salary!=1100;
3》sql比较操作符
between and：在什么范围之内
需求：查看员工工资在700 到 1500之间的员工id，和名字
select id,last_name,salary from s_emp where salary between 700 and 1500;
in(list):在一个列表中
需求：查看员工号1,3,5,7,9员工的工资
select id,last_name,salary from s_emp where id in (1,3,5,7,9);
like:模糊查询，即值不是精确的值的时候使用
通配符，即可以代替任何内容的符号
% ：通配0到多个字符
_ : 当且仅当通配一个字符
转义字符：默认为\,可以指定 指定的时候用escape 符号指明即可,转义字符只能转义后面的一个字符
需求：查看员工名字以c字母开头的员工的id，工资。
select id,last_name,salary from s_emp where last_name like 'c%';
练习：查看员工名字长度不小于5，且第四个字母为n字母的员工id和工资
select id,last_name,salary from s_emp where last_name like '___n_%';
需求：查看员工名字中包换一个_的员工id和工资
select id,last_name,salary from s_emp where last_name like '%\_%' escape '\';
is null：对null值操作特定义的操作符，不能使用=
需求：查看员工提成为为空的员工的id和名字
select id,last_name,commission_pct from s_emp where commission_pct is null;
3.逻辑操作符,当条件有多个的时候使用
and：且逻辑
or：或逻辑
注意：and逻辑比or逻辑要高
not：非逻辑
单值函数
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
函数分为:
1.单值函数
1.字符函数
2.日期函数
3.转换函数
4.数字函数
2.分组函数(后面的章节再做学习)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
哑表dual
dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。
例如:
显示1+1的结果,可以看出,dual很多时候是为了构成select的标准语法。
select 1+1 from dual;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
一.字符函数
lower converts to lowercase 把字符转为小写
upper converts to uppercase 把字符转换为大写
initcap converts to initial capitalization 把字符串首字母转换为大写
concat concatenates values 把俩个字符串连接在一起(类似之前的||的作用)
substr returns substring 截取字符串
length returns number of characters 获得字符串长度
nvl converts a null value 替换列中为null的值
concat 把俩个字符串连接在一起(类似之前的 || 的作用)
例如:把'hello'和'world'俩个字符串连接到一起,并且起个别名为msg
select concat('hello','world') msgfrom dual;
例如:把first_name和last_name俩个列的值连接到一起
select concat(first_name,last_name) as namefrom s_emp;
substr 截取字符串
例如:截取'hello'字符串,从第2个字符开始(包含第二个字符),截取后面连续的3个字符
select substr('hello',2,3)
from dual;
select last_name,substr(last_name,3) from s_emp;
第二个参数从1开始为正；（从前往后）
select last_name,substr(last_name,-1) from s_emp;
第二个参数从-1开始为负；（从后往前）
select last_name,substr(last_name,-3，2) from s_emp;
select last_name,substr(last_name,3，2) from s_emp;
第三个字符表示截取多少个；
注意：在oracle中的许多单值函数与java的数组计量方法不同，不是从0开始而是从-1或1开始。
length 获得字符串长度
例如:获得'world'字符串的长度
select length('world')
from dual;
例如:获得s_emp表中last_name列的每个值的字符长度
select length(last_name)
from s_emp;
nvl 替换列中为null的值，在前面的章节已经使用过了
select last_name,nvl(commission_pct,0)
from s_emp;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
二.数字函数
round rounds value to specified decimal 四舍五入
trunc truncates value to specified decimal 截取到某一位
mod returns remainder of division 取余
round 四舍五入：round(arg1,arg2)，第一个参数表示要进行四舍五入操作的数字，第二个参数表示保留到哪一位
例如:保留到小数点后面2位
select round(45.923,2)
from dual;
保留到个位 (个十百千万...)
select round(45.923,0)
from dual;
保留到十位 (个十百千万...)
select round(45.923,-1)
from dual;
trunc 截取到某一位 ，trunc(arg1,arg2)，和round的用法一样,但是trunc只舍去不进位
例如:截取到小数点后面2位
select trunc(45.929,2)
from dual;
截取到个位 (个十百千万...)
select trunc(45.923,0)
from dual;
截取到十位 (个十百千万...)
select trunc(45.923,-1)
from dual;
mod 取余，mod(arg1,arg2) ，第一个参数表示要进行取余操作的数字，第二个参数表示参数1和谁取余
例如:把10和3进行取余 (10除以3然后获取余数)
select mod(10,3)
from dual;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
三.日期函数
months_between number of months between two dates 俩个日期之间相差多少个月(单位是月)
add_months add calendar months to date 返回一个日期数据:表示一个时间点,往后推x月的日期
next_day next day of the date specified 返回一个日期数据:表示一个时间点后的下一个星期几在哪一天
last_day last day of the month 返回一个日期数据:表示一个日期所在月份的最后一天
round round to date at midnight 对日期进四舍五入,返回操作后的日期数据
trunc remove time portion from date 对日期进行截取 和round类似,但是只舍弃不进位
sysdate关键字,表示系统的当前时间
例如:显示时间:当前时间
select sysdate from dual;
注意:sysdate进行加减操作的时候,单位是天
例如:显示时间:明天的这个时候
select sysdate+1 from dual;
例如:显示时间:昨天的这个时候
select sysdate-1 from dual;
例如:显示时间:1小时之后的这个日期
select sysdate+1/24 from dual;
months_between 俩个日期之间相差多少个月(单位是月)
例如:30天之后和现在相差多少个月
select months_between(sysdate+30,sysdate)
from dual;
select months_between('01-2月-2019','01-2月-2019')
from dual;
add_months 返回一个日期数据:表示一个时间点,往后推x月的日期
例如:'01-2月-2019'往后推2个月
select add_months('01-2月-2019',2)
from dual;
例如:当前时间往后推4个月
select add_months(sysdate,4)
from dual;
注意:这个数字也可以是负数,表示往前推x月
next_day 返回一个日期数据:表示一个时间点后的下一个星期几在哪一天
例如:离当前时间最近的下一个星期5是哪一个天
select next_day(sysdate,'星期五')
from dual;
select next_day('01-2月-2019','星期五')
from dual;
注意:
如果要使用'friday',那么需要把当前会话的语言环境修改为英文
alter session set nls_language=english;
alter session set nls_language='simplified chinese';
last_day 返回一个日期数据:表示一个日期所在月份的最后一天
例如:当前日期所在月份的最后一天(月底)
select last_day(sysdate)
from dual;
round 对日期进四舍五入,返回操作后的日期数据
例如:把当前日期四舍五入到月
select round(sysdate,'month')
from dual;
测试:15号16号分别是舍弃还是进位
把当前日期四舍五入到年
select round(sysdate,'year')
from dual;
//这个写法是错误的
//数字函数也有一个round
//俩个ronnd函数有冲突
//所以这里不能使用默认的日期格式
select round('01-2月-2019','month')
from dual;
trunc 对日期进行截取 和round类似,但是只舍弃不进位
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
四.类型转换函数
to_char converts a number or date string to a character string. 把日期转换为字符
to_number converts a character string containing digits to a number. 把字符转换为数字
to_date converts a character string of a date to a date value. 把字符转换为日期
to_char 把日期转换为字符
例如:把当前日期按照指定格式转换为字符串
select to_char(sysdate,'yyyy')
from dual;
日期格式：
yyyy：四位数的年份
rrrr：四位数的年份
yy：两位数的年份
rr：两位数的年份
mm：两位数的月份（数字）
d：一周的星期几
dd：一月的第几天
ddd ：一年的第几天
year：英文的年份
month：英文全称的月份
mon：英文简写的月份
ddsp：英文的第几天(一个月的)
ddspth：英文序列数的第几天(一个月的)
day：全英文的星期
dy：简写的英文星期
hh：小时
mi：分钟
ss：秒
例如:测试常见的一些日期数据转换为字符串的格式
select to_char(sysdate,'yyyy mm d dd ddd year month ddsp ddspth day dy') from dual;
select to_char(sysdate,'dd-mm-yy') from dual;
select to_char(sysdate,'yy-mm-dd') from dual;
select to_char(sysdate,'dd-mm-yy hh24:mi:ss am') from dual;
千年虫:
在早期的计算机的程序中规定了的年份仅用两位数来表示。也就是说，假如是1971年，在计算机里就会被表示为71，但是到了2000年的时候这个情况就出现了问题，计算机就会将其年份表示为00。这样的话计算机内部对年份的计算就会出现问题。这个事情当时被称为千年虫
数据库中表示日期中年份的有俩种: yy和rr
之前一直使用的时候yy格式,后来才有的rr格式
yy表示使用一个俩位数表示当前年份:
1990 ---yy数据库格式---> 90
1968 ---yy数据库格式---> 68
1979 ---yy数据库格式---> 79
rr格式表示:
如果日期中的年份采用的格式为rr，并且只提供了最后2位年份，那么年份中的前两位数字就由两部分共同确定：提供年份的两位数字(指定年)，数据库服务器上当前日期中年份的后2位数字(当年)。确定指定年所在世纪的规则如下：
规则1 如果指定年在00~49之间，并且当前年份在00~49之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为15，而当前年份为2007，那么指定年就是2015。
规则2 如果指定年在50~99之间，并且当前年份在00~49之间，那么指定年的世纪就等于当前年份的世纪减去1。因此，指定年的前两位数字等于当前年份的前两位数字减去1。例如，如果指定年为75，而当前年份为2007，那么指定年就是1975。
规则3 如果指定年在00~49之间，并且当前年份在50~99之间，那么指定年的世纪就等于当前年份的世纪加上1。因此，指定年的前两位数字等于当前年份的前两位数字加上1。例如，如果指定年为15，而当前年份为2075，那么指定年就是2115。
规则4 如果指定年在50~99之间，并且当前年份在50~99之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为55，而当前年份为2075，那么指定年就是2055。
注意:rr格式并没有完全的解决俩位数年份保存的问题,思考里面还有哪些问题存在。
另外参照图片:rr日期格式规则.png
to_char 把数字转换为字符
例如:select to_char(salary,'$999,999.00') from s_emp;
fm表示去除结果显示中的开始的空格
select to_char(salary,'fm$999,999.00')
from s_emp;
l表示系统本地的货币符号
select to_char(salary,'fml999,999.00')
from s_emp;
select to_char(salary,'l999,999.00')
from s_emp;
to_number 把字符转换为数字
例如:select to_number('1000') from dual;
//这个写法是错的 abc不能转换为数字
select to_number('abc') from dual;
to_date 把字符转换为日期
例如:
select to_date('10-12-2019','dd-mm-yyyy')
from dual;
select to_date('25-5月-95','dd-month-yy')
from dual;
select to_date('95/5月/25','yy/month/dd')
from dual;
//session语言环境设置为英文下面可以运行
select to_date('25-may-95','dd-month-yy')
from dual;
alter session set nls_language=english;
alter session set nls_language='simplified chinese';
oracle数据库中表示一个日期数据的几种方式
1.使用sysdate
2.使用oracle默认的日期格式 例如:'25-may-95'
注意要看当前的语言环境
3.使用日期函数add_months/next_day/last_day/round/trunc
4.使用转换函数to_date
所以在日常的编程中若要向数据库中存一个日期，我们可以使用to_date函数，若要将一个日期数据以字符串形式从数据库中取出，我们可以使用to_char函数。
函数之间的嵌套
格式:f3(f2(f1(arg0,arg1),arg2),arg3)
例如:
先把'hello'和'world'连接起来,再转换为字母大写然后再从第4个字符开始,连着截取4个字符
select substr(upper(concat('hello','world')),4,4)
from dual;
建表语句
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
我要评论alter session set nls_language=english;
create table s_dept(
id number(7) constraint s_dept_id_nn not null,
name varchar2(25) constraint s_dept_name_nn not null,
region_id number(7),
constraint s_dept_id_pk primary key (id),
constraint s_dept_name_region_id_uk unique (name, region_id)
);
create table s_emp(
id number(7) constraint s_emp_id_nn not null,
last_name varchar2(25) constraint s_emp_last_name_nn not null,
first_name varchar2(25),
userid varchar2(8),
start_date date,
comments varchar2(255),
manager_id number(7),
title varchar2(25),
dept_id number(7),
salary number(11, 2),
commission_pct number(4, 2),
constraint s_emp_id_pk primary key (id),
constraint s_emp_userid_uk unique (userid),
constraint s_emp_commission_pct_ck
check (commission_pct in (10, 12.5, 15, 17.5, 20))
);
create table s_region(
id number(7) constraint s_region_id_nn not null,
name varchar2(50) constraint s_region_name_nn not null,
constraint s_region_id_pk primary key (id),
constraint s_region_name_uk unique (name)
);
insert into s_dept values (10, 'finance', 1);
insert into s_dept values (31, 'sales', 1);
insert into s_dept values (32, 'sales', 2);
insert into s_dept values (33, 'sales', 3);
insert into s_dept values (34, 'sales', 4);
insert into s_dept values (35, 'sales', 5);
insert into s_dept values (41, 'operations', 1);
insert into s_dept values (42, 'operations', 2);
insert into s_dept values (43, 'operations', 3);
insert into s_dept values (44, 'operations', 4);
insert into s_dept values (45, 'operations', 5);
insert into s_dept values (50, 'administration', 1);
insert into s_emp values (1, 'velasquez', 'carmen', 'cvelasqu',to_date('03-mar-90 8:30', 'dd-mon-yy hh24:mi'), null, null, 'president',50, 2500, null);
insert into s_emp values (2, 'ngao', 'ladoris', 'lngao','08-mar-90', null, 1, 'vp, operations',41, 1450, null);
insert into s_emp values (3, 'nagayama', 'midori', 'mnagayam','17-jun-91', null, 1, 'vp, sales',31, 1400, null);
insert into s_emp values (4, 'quick-to-see', 'mark', 'mquickto','07-apr-90', null, 1, 'vp, finance',10, 1450, null);
insert into s_emp values (5, 'ropeburn', 'audry', 'aropebur','04-mar-90', null, 1, 'vp, administration',50, 1550, null);
insert into s_emp values (6, 'urguhart', 'molly', 'murguhar','18-jan-91', null, 2, 'warehouse manager',41, 1200, null);
insert into s_emp values (7, 'menchu', 'roberta', 'rmenchu','14-may-90', null, 2, 'warehouse manager',42, 1250, null);
insert into s_emp values (8, 'biri', 'ben', 'bbiri','07-apr-90', null, 2, 'warehouse manager',43, 1100, null);
insert into s_emp values (9, 'catchpole', 'antoinette', 'acatchpo','09-feb-92', null, 2, 'warehouse manager',44, 1300, null);
insert into s_emp values (10, 'havel', 'marta', 'mhavel','27-feb-91', null, 2, 'warehouse manager',45, 1307, null);
insert into s_emp values (11, 'magee', 'colin', 'cmagee','14-may-90', null, 3, 'sales representative',31, 1400, 10);
insert into s_emp values (12, 'giljum', 'henry', 'hgiljum','18-jan-92', null, 3, 'sales representative',32, 1490, 12.5);
insert into s_emp values (13, 'sedeghi', 'yasmin', 'ysedeghi','18-feb-91', null, 3, 'sales representative',33, 1515, 10);
insert into s_emp values (14, 'nguyen', 'mai', 'mnguyen','22-jan-92', null, 3, 'sales representative',34, 1525, 15);
insert into s_emp values (15, 'dumas', 'andre', 'adumas','09-oct-91', null, 3, 'sales representative',35, 1450, 17.5);
insert into s_emp values (16, 'maduro', 'elena', 'emaduro','07-feb-92', null, 6, 'stock clerk',41, 1400, null);
insert into s_emp values (17, 'smith', 'george', 'gsmith','08-mar-90', null, 6, 'stock clerk',41, 940, null);
insert into s_emp values (18, 'nozaki', 'akira', 'anozaki','09-feb-91', null, 7, 'stock clerk',42, 1200, null);
insert into s_emp values (19, 'patel', 'vikram', 'vpatel','06-aug-91', null, 7, 'stock clerk',42, 795, null);
insert into s_emp values (20, 'newman', 'chad', 'cnewman','21-jul-91', null, 8, 'stock clerk',43, 750, null);
insert into s_emp values (21, 'markarian', 'alexander', 'amarkari','26-may-91', null, 8, 'stock clerk',43, 850, null);
insert into s_emp values (22, 'chang', 'eddie', 'echang','30-nov-90', null, 9, 'stock clerk',44, 800, null);
insert into s_emp values (23, 'patel', 'radha', 'rpatel','17-oct-90', null, 9, 'stock clerk',34, 795, null);
insert into s_emp values (24, 'dancs', 'bela', 'bdancs','17-mar-91', null, 10, 'stock clerk',45, 860, null);
insert into s_emp values (25, 'schwartz', 'sylvie', 'sschwart','09-may-91', null, 10, 'stock clerk',45, 1100, null);
insert into s_region values (1, 'north america');
insert into s_region values (2, 'south america');
insert into s_region values (3, 'africa / middle east');
insert into s_region values (4, 'asia');
insert into s_region values (5, 'europe');
commit;
如对本文有疑问， 点击进行留言回复！！
oracle:docker镜像部署11g 创建用户名，远程连接
网友评论