Oracle sql

1.基本操作

• order_by
  • 排序:列名，升序还是降序，null排在开头还是结尾

    column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST],
    column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST],

• In

WHERE
    category_id IN(1, 4)
WHERE
    category_id LIKE "%aa%"
WHERE
    category_id Betweent 1 and 10

• DISTINCT
  • 使修饰的字段名能唯一

SELECT
    DISTINCT product_id,
    quantity
FROM
    ORDER_ITEMS
ORDER BY product_id;

• EXTRACT
  • 从日期中提取年、月、日、小时、分钟等数据

 EXTRACT(DAY FROM OrderDate) AS OrderDay

• TO_CHAR

  • 把日期或数字转换成字符串
    使用TO_CHAR函数处理数字：
    TO_CHAR(number, ‘格式‘) 例如：TO_CHAR(salary,’$99,999.99’)
    使用TO_CHAR函数处理日期：
    TO_CHAR(date,’格式’); TO_CHAR(newdate,’yyyy-mm-dd’)

• FETCH

  • offset

设置偏移量

• ONLY | WITH TIES选项

ONLY:仅返回FETCH NEXT(或FIRST)后的行数或行数的百分比。
WITH TIES返回与最后一行相同的排序键,只能在排序语句起到作用

• BETWEEN AND

#表达式和low有相同的类型（相等也会被获取）
expression [ NOT ] BETWEEN low AND high

# 对日期进行对比
order_date BETWEEN DATE '2016-12-01' AND DATE '2016-12-31'

• LIKE

#％(百分号)匹配零个或多个字符的任何字符串。_(下划线)匹配任何单个字符。
expresion [NOT] LIKE pattern [ ESCAPE escape_characters ]

2.事务

• commit
  • comment
  • write
    WAIT 或 NOWAIT (如果省略，WAIT是默认值)IMMEDIATE或BATCH(IMMEDIATE是省略时的默认值)
  • force
• ROLLBACK

ROLLBACK [ WORK ] [ TO [SAVEPOINT] savepoint_name  | FORCE 'string' ];
如果省略该子句，则所有更改都将被撤消。

键

• 外键

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (column1, column2, ... column_n)
);

# 新增
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
   FOREIGN KEY (column1, column2, ... column_n)
   REFERENCES parent_table (column1, column2, ... column_n);

• 删除外键

# 设置级联删除
ON DELETE CASCADE

#
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

• 禁用外键

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

• 启用外键

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

4.服务端

• 备份
• 索引
• 数据类型
• 创建表格