SQLLine

CREATE TABLE

Creates a table.

CREATE TABLE MY_TABLE(id BIGINT not null primary key, name VARCHAR);

ALTER

Alters a table or a view.

ALTER TABLE MY_TABLE DROP COLUMN name;

DROP TABLE

Deletes a table.

DROP TABLE MY_TABLE;

UPSERT VALUES

Inserts or changes data.

UPSERT INTO MY_TABLE VALUES(1,'abc');

SELECT

Queries data.

SELECT * FROM MY_TABLE;

CREATE INDEX

Creates a global index.

CREATE INDEX MY_IDX ON MY_TABLE(name);

CREATE LOCAL INDEX

Creates a local index.

CREATE LOCAL INDEX MY_LOCAL_IDX ON MY_TABLE(id,name);

ALTER INDEX

Changes the index status.

ALTER INDEX MY_IDX ON MY_TABLE DISABLE;

DROP INDEX

Deletes an index.

DROP INDEX MY_IDX ON MY_TABLE;

EXPLAIN

Displays an execution plan.

EXPLAIN SELECT name FROM MY_TABLE;

CREATE SEQUENCE

Creates a sequence.

CREATE SEQUENCE MY_SEQUENCE;

DROP SEQUENCE

Deletes a sequence.

DROP SEQUENCE MY_SEQUENCE;

CREATE VIEW

Creates a view.

CREATE VIEW MY_VIEW AS SELECT * FROM MY_TABLE;

DROP VIEW

Deletes a view.

DROP VIEW MY_VIEW;

CREATE SCHEMA

Creates a schema.

CREATE SCHEMA MY_SCHEMA;

USE

Modifies the default schema.

USE MY_SCHEMA;

DROP SCHEMA

Deletes a schema.

DROP SCHEMA MY_SCHEMA;

String functions

SUBSTR

Extracts a part of a string.

SUBSTR('[Hello]', 2, 5)

INSTR

Queries the position of the first occurrence of a string in another string.

INSTR('Hello World', 'World')

TRIM

Removes whitespaces from both ends of a string.

TRIM(' Hello ')

LTRIM

Removes whitespaces found on the left-hand side of the string.

LTRIM(' Hello')

RTRIM

Removes whitespaces found on the right-hand side of the string.

RTRIM('Hello ')

LPAD

Left-pads a string with another string.

LPAD('John',30,'*')

LENGTH

Gets the length of a given string.

LENGTH('Hello')

UPPER

Converts all the characters in a string to uppercase characters.

UPPER('Hello')

LOWER

Converts all the characters in a string to lowercase characters.

LOWER('HELLO')

REVERSE

Reverses a string.

REVERSE('Hello')

REGEXP_SPLIT

Splits a string using a regular expression.

REGEXP_SPLIT('ONE,TWO,THREE', ',')

REGEXP_REPLACE

Returns a string by applying a regular expression and replacing the matches with the replacement string.

REGEXP_REPLACE('abc123ABC', '[0-9]+', '#')

REGEXP_SUBSTR

Returns a substring from a string based on a regular expression pattern.

REGEXP_SUBSTR('na1-appsrv35-sj35', '[^-]+')

Aggregate functions

AVG

Gets the average value.

AVG(X)

COUNT

Gets the number of data records.

COUNT(*)

MAX

Gets the maximum value.

MAX(NAME)

MIN

Gets the minimum value.

MIN(NAME)

SUM

Gets the sum of all values.

SUM(X)

STDDEV_POP

Gets the population standard deviation of all values.

STDDEV_POP( X )

STDDEV_SAMP

Gets the sample standard deviation of all values.

STDDEV_SAMP( X )

NTH_VALUE

Gets the Nth value in each distinct group ordered according to the ORDER BY specification.

NTH_VALUE( name, 2 ) WITHIN GROUP (ORDER BY salary DESC)

Time and date functions

NOW

Returns the current date.

NOW()

CURRENT_TIME

Returns the current time.

CURRENT_TIME()

CURRENT_DATE

Returns the current date.

CURRENT_DATE()

TO_DATE

Parses a string and returns a date.

TO_DATE('1970-01-01', 'yyyy-MM-dd', 'GMT+1')

TO_TIME

Converts the given string into a TIME instance.

TO_TIME('1970-01-01', 'yyyy-MM-dd', 'GMT+1')

TO_TIMESTAMP

Converts the given string into a TIMESTAMP instance.

TO_TIMESTAMP('1970-01-01', 'yyyy-MM-dd', 'GMT+1')

YEAR

Returns the year of the specified date.

YEAR(TO_DATE('2015-6-05'))

MONTH

Returns the month of the specified date.

MONTH(TO_TIMESTAMP('2015-6-05'))

WEEK

Returns the week of the specified date.

WEEK(TO_TIME('2010-6-15'))

HOUR

Returns the hour of the specified date.

HOUR(TO_TIMESTAMP('2015-6-05'))

MINUTE

Returns the minute of the specified date.

MINUTE(TO_TIME('2015-6-05'))

SECOND

Returns the second of the specified date.

SECOND(TO_DATE('2015-6-05'))

Numeric functions

ROUND

Rounds the numeric or timestamp expression to the nearest scale or time unit specified.

ROUND(2.56)

CEIL

Rounds any fractional value up to the next even multiple.

CEIL(2.34)

FLOOR

Rounds any fractional value down to the previous even multiple.

FLOOR(2.34)

TRUNC

Rounds any fractional value down to the previous even multiple (same as FLOOR).

TRUNC(2.34)

TO_NUMBER

Formats a string as a number.

TO_NUMBER('-123.33')

RAND

Returns a random number.

RAND()

Math functions

ABS

Returns the absolute value of the given numeric expression.

ABS(-1)

SQRT

SQRT(1.1)

EXP

EXP(-1)

POWER

POWER(2, 3)

LN

LN(3)

LOG

LOG(2, 3)

Array functions

ARRAY_ELEM

Uses the array subscript notation to access an array element.

ARRAY_ELEM(ARRAY[1,2,3], 1)

ARRAY_PREPEND

Appends the given element to the beginning of the array.

ARRAY_APPEND(ARRAY[1,2,3], 4)

ARRAY_CAT

Concatenates the input arrays and returns the result.

ARRAY_CAT(ARRAY[1,2], ARRAY[3,4])

ARRAY_FILL

Fills an array with values.

ARRAY_FILL(1, 3)

ARRAY_TO_STRING

Converts the elements of an array to a string and joins them using the specified delimiter.

ARRAY_TO_STRING(ARRAY['a','b','c'], ',')

ANY

Used on the right-hand side of a comparison expression to test that any array element satisfies the comparison expression against the left-hand side.

10 > ANY(my_array)

ALL

Used on the right-hand side of a comparison expression to test that all array elements satisfy the comparison expression against the left-hand side.

10 > ALL(my_array)

Other functions

MD5

Computes the MD5 hash of the argument.

MD5(my_column)

ENCODE

Encodes the expression according to the encoding format provided.

ENCODE(myNumber, 'BASE62')

DECODE

Decodes the expression according to the encoding format provided.

DECODE('000000008512af277ffffff8', 'HEX')