Oracle 教程

Oracle 教程

December 5, 2020
,
Oracle

Oracle 教程

select 语句

  • 语句文本的书写不区分大小写。(但字符串在作为值的时候要注意大小写)
  • 连接操作符:
select lastname || 'work in' || department_id from tablename;

过滤数据

DISTINCT

column_1column_2column_3 共同决定一条唯一的记录:

SELECT DISTINCT column_1, column_2, column_3 FROM table_name;

FETCH

Oracle 没有 LIMIT,使用 FETCH 实现相同效果:

SELECT product_name, quantity 
FROM inventories
INNER JOIN products USING (product_id)
ORDER BY quantity DESC
FETCH NEXT 5 ROWS ONLY;

更多语法:

  • FETCH FIRST 5 PERCENT ROWS ONLY:返回前 5% 百分比
  • OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY: 跳过前 10 条,返回下 10 条

IN

WHERE salesman_id IN (54, 55, 56)
WHERE status IN ('Pending', 'Canceled')
WHERE status NOT IN ('Shipped', 'Canceled')

BETWEEN

WHERE order_date BETWEEN DATE '2016-12-01' AND DATE '2016-12-31'
WHERE standard_cost NOT BETWEEN 500 AND 600

LIKE

  • %: 匹配字符串的 0 或者任意多字符
  • _: 匹配一个字符
WHERE last_name LIKE 'St%'

IS NULL

NULL''0 是不同的。

字符串函数:

  • UPPER( last_name ): 转为大写
  • LOWER( last_name ): 转为小写

JOIN

INNER JOIN

两张表都有相同的列:

SELECT * FROM orders INNER JOIN order_items ON
order_items.order_id = orders.order_id
ORDER BY order_date DESC;

也可以用 ON:

SELECT * FROM orders 
INNER JOIN order_items USING (order_id)
ORDER BY order_date DESC;

LEFT JOIN

SELECT order_id, status, first_name, last_name
FROM orders 
LEFT JOIN employees ON employee_id = salesman_id
ORDER BY order_date DESC;

修改数据

INSERT

INSERT INTO discounts(discount_name, amount, start_date, expired_date)
VALUES ('Summer Promotion', 9.5, DATE '2017-05-01', DATE '2017-08-31');

UPDATE

UPDATE parts
SET lead_time = 30, cost = 120, status = 1
WHERE part_id = 5;

DELETE

DELETE FROM sales WHERE order_id = 1;

删除所有行:

DELETE FROM sales;

表结构

CREATE TABLE

ot schema 中创建一个新的表 persons:

CREATE TABLE ot.persons(
    person_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    PRIMARY KEY(person_id)
);

GENERATED BY DEFAULT AS IDENTITY 告诉 Oracle 插入的时候,自动为 person_id 生成一个新的 ID

ALTER TABLE

新增列:

ALTER TABLE persons 
ADD birthdate DATE NOT NULL;

新增多个列:

ALTER TABLE persons 
ADD (
    phone VARCHAR(20),
    email VARCHAR(100)
);

修改列:

ALTER TABLE persons MODIFY birthdate DATE NULL;

删除列:

ALTER TABLE persons
DROP
  COLUMN birthdate;

删除多个列:

ALTER TABLE persons
DROP
  ( email, phone );

重命名列:

ALTER TABLE persons 
RENAME COLUMN first_name TO forename;

DROP TABLE

DROP TABLE schema_name.table_name
[CASCADE CONSTRAINTS | PURGE];
  • PURGE: 释放空间,无法回滚

TRUNCATE TABLE

DELETE FROM table_name 能够更快速的删除所有行的语句:

TRUNCATE TABLE schema_name.table_name
[CASCADE]
[[ PRESERVE | PURGE] MATERIALIZED VIEW LOG ]]
[[ DROP | REUSE]] STORAGE ]

数据类型

NUMBER

NUMBER 的定义:

NUMBER[(precision [, scale])]

precision 的范围:1 ~ 38,scale 的范围:-84 ~ 127。1234.56 的 precision 是 6,scale 是 2,所以存储这个数字需要定义为:NUMBER(6, 2),示例:

CREATE TABLE number_demo ( 
    number_value NUMERIC(6, 2) 
);

-- 抛出错误
INSERT INTO number_demo VALUES(-10000);

FLOAT

FLOAT 的最大精度是 126:

FLOAT(p)

示例:

CREATE TABLE float_demo (
    f1 FLOAT(1),
    f2 FLOAT(4),
    f3 FLOAT(7)
);

INSERT INTO float_demo(f1, f2, f3) VALUES(1 / 3, 1 / 3, 1 / 3);

CHAR

可以存储固定长度的,1 - 2000 字节的字符串,定义如下:

CHAR(length BYTE)
CHAR(length CHAR)

如果没有指定 BYTE 或者 CHAR,那么 Oracle 默认使用 BYTE

-- 10 字节
CREATE TABLE t (
    x CHAR(10),
    y VARCHAR2(10)
);

NCHAR

存储固定长度的 Unicode 字符串,NCHAR 的字符集只能是 AL16UTF16UTF8,示例:

CREATE TABLE nchar_demo (
    description NCHAR(10)
); 

NCHAR 最多存储 2000 字节。

VARCHAR2

存储可变长度的 1 - 4000 字节的字符串,定义:

VARCHAR2(max_size BYTE)
VARCHAR2(max_size CHAR)

默认使用 BYTE。在 Oracle 12C,VARCHAR2 最多指定的大小为 32767 。

NVARCHAR2

存储可变长度的 Unicode 字符串:

CREATE TABLE nvarchar2_demo (
    description NVARCHAR2(50)
);

能存储多少个字符,取决于当前国籍字符集。

DATE

Oracle 用固定的 7 字节存储 DATE 类型,精确到

默认日期格式化是 DD-MON-YY 格式,返回当前日期:

-- 01-AUG-17
SELECT sysdate FROM dual;

改变默认的日期格式:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
-- 2017-08-01
SELECT sysdate FROM dual;

DATE 转为字符串(格式化日期):

-- August 1, 2017
SELECT
  TO_CHAR( SYSDATE, 'FMMonth DD, YYYY' )
FROM
  dual;

字符串转为 DATE:

-- 01-AUG-17
SELECT
  TO_DATE( 'August 01, 2017', 'MONTH DD, YYYY' )
FROM
  dual;

除了使用 TO_DATE 函数,也可以使用 DATE '2017-08-01' 来转为 DATE literal,不过这个是不包含 TIME 的。

TIMESTAMP

存储带小数点的秒,默认是 6 位小数点,定义 2 位的小数点如下所示:

started_at TMESTAMP(2),

像下面这样指定 TIMESTAMP literal :

TIMESTAMP '1999-12-31 23:59:59.10'

插入当前时间戳:

INSERT INTO logs (
    message,
    logged_at
) VALUES (
    'User root logged in successfully',
    LOCALTIMESTAMP(2)
);

格式化时间戳:

SELECT message,
    TO_CHAR(logged_at, 'MONTH DD, YYYY "at" HH24:MI')
FROM logs;

提前 TIMESTAMP 的各个部分:

SELECT 
    message,
    EXTRACT(year FROM logged_at) year,
    EXTRACT(month FROM logged_at) month,
    EXTRACT(day FROM logged_at) day,
    EXTRACT(hour FROM logged_at) hour,
    EXTRACT(minute FROM logged_at) minute,
    EXTRACT(second FROM logged_at) second
FROM 
    logs;

TIMESTAMP WITH TIME ZONE

存储 TIMESTAMP 和 Timeout Zone 数据,示例:

TIMESTAMP '2017-08-09 07:00:00 -7:00'

创建含有 TIMESTAMP WITH TIME ZONE 的示例:

CREATE TABLE logs
(
	log_id      NUMBER GENERATED BY DEFAULT AS IDENTITY,
	log_message VARCHAR2(255) NOT NULL,
	created_at  TIMESTAMP WITH TIME ZONE NOT NULL,  
	PRIMARY KEY(log_id)
);

插入当前时间戳:

-- 数据库自动取出当前的 TIME ZONE
INSERT INTO logs(log_message, created_at)
VALUES('Insert a timestamp',TIMESTAMP '2017-08-08 2:00:00');

插入当前时间戳:

INSERT INTO logs(log_message, created_at)
VALUES('Use current_timestamp function',CURRENT_TIMESTAMP);

约束

主键

CREATE TABLE purchase_order_items (
    po_nr NUMBER NOT NULL,
    item_nr NUMBER NOT NULL,
    product_id NUMBER NOT NULL,  
    quantity NUMBER NOT NULL,
    purchase_unit NUMBER NOT NULL,
    buy_price NUMBER (9,2) NOT NULL,
    delivery_date DATE,
    PRIMARY KEY (po_nr, item_nr)
);

NOT NULL 约束

CREATE TABLE surcharges (
  surcharge_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
  surcharge_name VARCHAR2(255) NOT NULL,
  amount NUMBER(9,2),
  PRIMARY KEY (surcharge_id)
);

UNIQUE 约束

CREATE TABLE table_name (
    ...
    column_name data_type CONSTRAINT unique_constraint_name UNIQUE
    ...
);

多个列的 UNIQUE 约束:

CREATE TABLE table_name (
    ...
    column_name1 data_type,
    column_name2 data_type,
    ...,
    CONSTRAINT unique_constraint_name UNIQUE(column_name1, column_name2)
);

索引

创建索引

CREATE INDEX members_last_name_i 
ON members(last_name);

查询索引:

SELECT 
    index_name, 
    index_type, 
    visibility, 
    status 
FROM 
    all_indexes
WHERE 
    table_name = 'MEMBERS';

在多个列上创建索引:

CREATE INDEX members_name_i
ON members(last_name,first_name);

删除索引

DROP INDEX members_name_i;

创建唯一索引

CREATE UNIQUE INDEX members_email_i
ON members(email);

函数

Date 函数

Function Example Result Description
ADD_MONTHS ADD_MONTHS( DATE ‘2016-02-29’, 1 ) 31-MAR-16 Add a number of months (n) to a date and return the same day which is n of months away.
CURRENT_DATE SELECT CURRENT_DATE FROM dual 06-AUG-2017 19:43:44 Return the current date and time in the session time zone
CURRENT_TIMESTAMP SELECT CURRENT_TIMESTAMP FROM dual 06-AUG-17 08.26.52.742000000 PM -07:00 Return the current date and time with time zone in the session time zone
DBTIMEZONE SELECT DBTIMEZONE FROM dual; -07:00 Get the current database time zone
EXTRACT EXTRACT(YEAR FROM SYSDATE) 2017 Extract a value of a date time field e.g., YEAR, MONTH, DAY, … from a date time value.
FROM_TZ FROM_TZ(TIMESTAMP ‘2017-08-08 08:09:10’, ‘-09:00’) 08-AUG-17 08.09.10.000000000 AM -07:00 Convert a timestamp and a time zone to a TIMESTAMP WITH TIME ZONE value
LAST_DAY LAST_DAY(DATE ‘2016-02-01’) 29-FEB-16 Gets the last day of the month of a specified date.
LOCALTIMESTAMP SELECT LOCALTIMESTAMP FROM dual 06-AUG-17 08.26.52.742000000 PM
MONTHS_BETWEEN MONTHS_BETWEEN( DATE ‘2017-07-01’, DATE ‘2017-01-01’ ) 6 Return the number of months between two dates.
NEW_TIME NEW_TIME( TO_DATE( ‘08-07-2017 01:30:45’, ‘MM-DD-YYYY HH24:MI:SS’ ), ‘AST’, ‘PST’ ) 06-AUG-2017 21:30:45 Convert a date in one time zone to another
NEXT_DAY NEXT_DAY( DATE ‘2000-01-01’, ‘SUNDAY’ ) 02-JAN-00 Get the first weekday that is later than a specified date.
ROUND ROUND(DATE ‘2017-07-16’, ‘MM’) 01-AUG-17 Return a date rounded to a specific unit of measure.
SESSIONTIMEZONE SELECT SESSIONTIMEZONE FROM dual; -07:00 Get the session time zone
SYSDATE SYSDATE 01-AUG-17 Return the current system date and time of the operating system where the Oracle Database resides.
SYSTIMESTAMP SELECT SYSTIMESTAMP FROM dual; 01-AUG-17 01.33.57.929000000 PM -07:00 Return the system date and time that includes fractional seconds and time zone.
TO_CHAR TO_CHAR( DATE'2017-01-01', ‘DL’ ) Sunday, January 01, 2017 Convert a DATE or an INTERVAL value to a character string in a specified format.
TO_DATE TO_DATE( ‘01 Jan 2017’, ‘DD MON YYYY’ ) 01-JAN-17 Convert a date which is in the character string to a DATE value.
TRUNC TRUNC(DATE ‘2017-07-16’, ‘MM’) 01-JUL-17 Return a date truncated to a specific unit of measure.
TZ_OFFSET TZ_OFFSET( ‘Europe/London’ ) +01:00 Get time zone offset of a time zone name from UTC

String 函数

Function Example Result Purpose
ASCII ASCII(‘A’) 65 Returns an ASCII code value of a character.
CHR CHR(’65’) ‘A’ Converts a numeric value to its corresponding ASCII character.
CONCAT CONCAT(‘A’,’BC’) ‘ABC’ Concatenate two strings and return the combined string.
CONVERT CONVERT( ‘Ä Ê Í’, ‘US7ASCII’, ‘WE8ISO8859P1’ ) ‘A E I’ Convert a character string from one character set to another.
DUMP DUMP(‘A’) Typ=96 Len=1: 65 Return a string value (VARCHAR2) that includes the datatype code, length measured in bytes, and internal representation of a specified expression.
INITCAP INITCAP(‘hi there’) ‘Hi There’ Converts the first character in each word in a specified string to uppercase and the rest to lowercase.
INSTR INSTR( ‘This is a playlist’, ‘is’) 3 Search for a substring and return the location of the substring in a string
LENGTH LENGTH(‘ABC’) 3 Return the number of characters (or length) of a specified string
LOWER LOWER(‘Abc’) ‘abc’ Return a string with all characters converted to lowercase.
LPAD LPAD(‘ABC’,5,’*’) ‘**ABC’ Return a string that is left-padded with the specified characters to a certain length.
LTRIM LTRIM(‘ ABC ‘) ‘ABC ‘ Remove spaces or other specified characters in a set from the left end of a string.
REGEXP_COUNT REGEXP_COUNT(‘1 2 3 abc’,’\d’) 3 Return the number of times a pattern occurs in a string.
REGEXP_INSTR REGEXP_INSTR( ‘Y2K problem’,’\d+’) 2 Return the position of a pattern in a string.
REGEXP_LIKE REGEXP_LIKE( ‘Year of 2017′,’\d+’ ) true Match a string based on a regular expression pattern.
REGEXP_REPLACE REGEXP_REPLACE( ‘Year of 2017′,’\d+’, ‘Dragon’ ) ‘Year of Dragon’ Replace substring in a string by a new substring using a regular expression.
REGEXP_SUBSTR REGEXP_SUBSTR( ‘Number 10’, ‘\d+’ ) 10 Extract substrings from a string using a pattern of a regular expression.
REPLACE REPLACE(‘JACK AND JOND’,’J’,’BL’); ‘BLACK AND BLOND’ Replace all occurrences of a substring by another substring in a string.
RPAD RPAD(‘ABC’,5,’*’) ‘ABC**’ Return a string that is right-padded with the specified characters to a certain length.
RTRIM RTRIM(‘ ABC ‘) ‘ ABC’ Remove all spaces or specified character in a set from the right end of a string.
SOUNDEX SOUNDEX(‘sea’) ‘S000’ Return a phonetic representation of a specified string.
SUBSTR SUBSTR(‘Oracle Substring’, 1, 6 ) ‘Oracle’ Extract a substring from a string.
TRANSLATE TRANSLATE(‘12345’, ‘143’, ‘bx’) ‘b2x5’ Replace all occurrences of characters by other characters in a string.
TRIM TRIM(‘ ABC ‘) ‘ABC’ Remove the space character or other specified characters either from the start or end of a string.
UPPER UPPER(‘Abc’) ‘ABC’ Convert all characters in a specified string to uppercase.

参考