2018-09-04 工作日志

Java 工作日志 Oracle 数据库

上

Oracle数据类型

ORACLE基本数据类型可以按类型分为：
字符串类型、数字类型、日期类型、LOB类型、LONG RAW& RAW类型、ROWID & UROWID类型。
常用到的有 数值数据类型，日期和时间类型以及字符串类型。

number:

create table tmp1(
    x number(4),
    y number(6)
);
create table tmp2(
    x number(5,1),
    y number(5,3),
    z number(5,2)
);
insert into tmp2 values(5.12,5.15,5.123);
select * from tmp2;

日期和时间类型：包括DATE和TIMESTAMP

DATE用来存储日期和时间，取值范围是公元前4712年到公元9999年12月31日
修改日期的格式：如果用户想按照指定的格式输入时间，需要修改时间的默认格式。例如输入格式为年-月-日，修改的SQL语句如下：
alter session set nls_date_format='yyyy-mm-dd';
TIMESTAMP(时间戳):用来存储日期和时间，与DATE类型的区别是显示日期和时间时更精确。Date类型的时间精确到秒，而TIMESTAMP数据类型可以精确到小数秒

字符串类型：

包括CHAR,VARCHAR2,NVARCHAR2,NCHAR和LONG五种.
定长字符串在存储时长度是固定的，而变长字符串的存储长度根据实际插入的数据而定。
varchar2与varchar是可变长的字符串，char是固定长度的字符串。

重要的Oracle比较运算符

=,>=,<=,is null,is not null,in,not in,beween and,like <>或!= 指不等于

create table tmp7(
num number
);
inset into tmp7 values(64);
selete * from tmp7;
select num,num+10,num+5-3,num*3,num/6 from tmp7;
-- 出生在1980-1990年之间的名
select name from students where birthday between '1980' and '1990';
-- 等价于上句
select name from students where birthday>='1980' and birthday<='1990';
-- 查询除了在(1980-1990)年段的学生
select name from students where birthday not between '1980' and '1990';
select name from students where age in(25,26);
select name from students where age not in(25,26);
-- 姓张的
select name from students where name like '张%';
select name from students where age=25 and name like '张%'

重要的Oracle逻辑运算符

not,and,or

1.NOT运算符

又称取反运算符，常常和IN,LIKE,BETWEEN...AND 和NULL等关键字一起使用

select name from student where age not in(25,26);

2.AND运算符

要求两边的表达式结果都为true,如果任何一方的返回结果为null或false,那么

逻辑运算的结果就为FALSE,也就是说记录不匹配WHERE子句的要求

select name from student where age=25 and name like '张%';

3.OR运算符

又称或运算符，也就是说只要左右两侧的布尔表达式任何一方为true,结果就为true.

select name from student where age=25 or name like '张%';

注:in操作符与or的用法功能相似。

Oracle——记录的插入

create table person(
id number(9) primary key,
name varchar2(40) not null,
age number(3) not null,
info varchar2(50)
);
insert into person (id,name,age,info) values(1,'Tom',21,'lawyer');
insert into person (age,name,id,info) values(11,'Marry',2,'dancer');
select * from person;
insert into person values(3,'Scott',23,'Musician');
insert into person(id,name,age) values(4,'Tiger',35);

更新与删除数据

-- 更新id=4的记录，将age改为15，name改为LiMing
update person set age=15,name='LiMing' where id=4;
-- 更新age为19-22记录，讲将info字段值都改为"student"
update person set info='student' where age between 19 and 22;
-- 在 person表中，删除id=4的记录
delete from person  where id=4;

下

Oracle函数

Oracle函数包括：数学函数，字符串函数，日期和时间函数，条件判断函数，系统信息函数等

数学函数

-- mod(x,y)求余
select mod(31,8),mod(234,10) from dual;
-- abs(求绝对值)
-- 获取整数的函数CEIL(X)和FLOOR(X)
-- 函数CEIL(X):返回不小于x的最小整数值。  
-- 函数FLOOR(X):返回不大于x的最大整数。                    
select ceil(-3.35),ceil(3.35) from dual;
select floor(-3.35),floor(3.35) from dual;
-- (3).四舍五入函数ROUND(X),ROUND(X,Y)和TRUNC(X,Y)
select round(-1.14),round(-1.54),round(1.14),round(1.54) from dual;
select round(1.14,1),round(1.38,0),round(234.14,-1),round(252.33,-2) from dual;
-- trunc函数,截取整数值
SELECT TRUNCATE
    ( 1.31, 1 ),
    TRUNCATE ( 1.99, 1 ),
TRUNCATE ( 1.99, 0 ),
TRUNCATE ( 19.99,-1 ) 
FROM
    DUAL;

Oracle字符串函数：

-- Oracle字符串函数：
SELECT LENGTH(`hello`),LENGTH(`asdfghjkl`) FROM DUAL;
SELECT CONCAT(`学习`,`数据库`,`知识`) FROM DUAL;
SELECT INSTR(`hello Oracle`,`e`) FROM DUAL;
SELECT LOWER(`ASDFGHTFRD`) FROM DUAL;
SELECT UPPER(`sdfghytrfdvbhjhygf`) FROM DUAL;
-- 字符串转数字
SELECT to_number('1999.123','9999.999')FROM DUAL;
-- 首字母大写
SELECT INITCAP(`dugf sdgfsh erhtwke rtkbvef oos`) FROM DUAL;
-- 下标从1开始
SELECT SUBSTR(`qasdfrdfr号drhgdnb`, 6 ,2),SUBSTR(`qasdfrdfr号drhgdnb`, 6 ,2)FROM DUAL;
SELECT REPLACE(`aaaaaasdfSDaaa1aabbbbbbbbbbbcccccccccccccdddddddddddd`,`a`,`A`)
SELECT
    LTRIM( `fsd` ),
    LTRIM( `  ergdfg erg` ),
    LTRIM( ` ` ) 
FROM
    DUAL;
SELECT
    RTRIM( ` fs  d   ` ),
    LTRIM( `        er      gd fg erg     ` ),
    LTRIM( ` ` ) 
FROM
    DUAL;
SELECT TRIM(BOTH `x` FROM `xyxbxyxyx`) FROM DUAL;
--系统时间（Oracle的sysdate不用括号）
SELECT SYSDATE() FROM DUAL;
SELECT to_char(SYSDATE(),`yyyy-MM-dd HH24:MI:SS`) FROM DUAL;
SELECT systimestamp FROM DUAL;
-- 提取部分
SELECT EXTRACT(YEAR FROM SYSDATE()) FROM DUAL;
-- 10.132
SELECT to_char(10.13245,`99.999`) FROM DUAL;
-- 转为ASCII码
SELECT ASCIISTR(`从入门到精通`) FROM DUAL;
SELECT USER() FROM DUAL;
mysql:SELECT USER FROM DUAL;

日期和时间函数

DATE和TIMESTAMP:

create table tmp3(
    d date
);
insert into tmp3 values('12-5月-2010');
select * from tmp3;
select sysdate from dual;
create table tmp4 (d date);
-- 修改默认格式，以指定格式输入时间
alter session set nls_date_format='yyyy-mm-dd';
insert into tmp4 values('2012-08-08');
selete * from tmp4;
delete from tmp4;
insert into tmp4 values(sysdate);
insert into tmp4 values('2005-01-26 13:14:20','yyyy-MM-dd HH24:mi:ss');
create table tmp5(
    ts timestamp
);
insert into tmp5 values(to_timestamp(' 17:03:00.9999','yyyy-MM-dd hh24:mi:ss:ff'));
create tsbsle tmp6(
ch char(4),
vch varchar2(4)
);
insert into tmp6 values('qw','qw');
select length(ch) from tmp6;
select length(vch) from tmp6;

Oracle查询

select * from fruits;
select f_id,f_name from fruits;
select f_name from fruits;
-- 查询价格为10.2元的水果的名称
select f_name,f_price from fruits where f_price=10.2;
-- 查找名称为apple的水果的价格
select f_name,f_price from fruits where f_name='apple';
-- 查询价格小于10元的水果的名称
select f_name,f_price from fruits where f_price<10;
-- s_id为101和102的记录
SELECT * FROM fruits WHERE s_id IN(101,102);
-- s_id不为101和102的记录
SELECT * FROM fruits WHERE s_id NOT IN(101,102);
-- 查询价格在2-10.2元之间的水果名称和价格
SELECT f_name,f_price FROM fruits WHERE f_price BETWEEN 2 AND 10.2;
-- 查询价格在2-10.2元之外的水果名称和价格
SELECT f_name,f_price FROM fruits WHERE f_price NOT BETWEEN 2 AND 10.2;
-- 查询所有以“b”字母开头的水果名称
SELECT f_name FROM fruits WHERE f_name LIKE 'b%';
-- 查询所有水果名称中包含“g”的记录
SELECT * FROM fruits WHERE f_name LIKE '%g%';
-- 查询名称以“b”开头，且以“y”结尾的水果名
SELECT f_name FROM fruits WHERE f_name LIKE 'b%y';
-- 查询名称以“y”结尾，且“y”前面只有4个字母的记录
SELECT * FROM fruits WHERE f_name LIKE '____y';

附

水果表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for fruits
-- ----------------------------
DROP TABLE IF EXISTS `fruits`;
CREATE TABLE `fruits`  (
  `f_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `s_id` int(6) NOT NULL,
  `f_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `f_price` decimal(8, 2) NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of fruits
-- ----------------------------
INSERT INTO `fruits` VALUES ('a1', 101, 'apple', 5.20);
INSERT INTO `fruits` VALUES ('b1', 101, 'blackberry', 10.20);
INSERT INTO `fruits` VALUES ('bs1', 102, 'orange', 11.20);
INSERT INTO `fruits` VALUES ('bs2', 105, 'melon', 8.20);
INSERT INTO `fruits` VALUES ('t1', 102, 'banana', 10.30);
INSERT INTO `fruits` VALUES ('t2', 102, 'grape', 5.30);
INSERT INTO `fruits` VALUES ('o2', 103, 'coconut', 9.20);
INSERT INTO `fruits` VALUES ('c0', 101, 'cherry', 3.20);
INSERT INTO `fruits` VALUES ('a2', 103, 'apricot', 2.20);
INSERT INTO `fruits` VALUES ('l2', 104, 'lemon', 6.40);
INSERT INTO `fruits` VALUES ('b2', 104, 'berry', 7.60);
INSERT INTO `fruits` VALUES ('m1', 106, 'mango', 15.60);
INSERT INTO `fruits` VALUES ('m2', 105, 'xbabay', 2.60);
INSERT INTO `fruits` VALUES ('t4', 107, 'xbababa', 3.60);
INSERT INTO `fruits` VALUES ('m3', 105, 'xxtt', 11.60);
INSERT INTO `fruits` VALUES ('b5', 107, 'xxxx', 3.60);
SET FOREIGN_KEY_CHECKS = 1;