What are the common grammars of HANA
What are the common syntax of HANA? For this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more small partners who want to solve this problem find a simpler and easier way.
SQL--1. View current date SELECT CURRENT_DATE "DATE" FROM DUMMY; --View current date 0:00 SELECT TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP,'Y-MM-DD')) FROM DUMMY;SELECT CURRENT_DATE FROM DUMMY;--2. Find employee name and employee annual salary. P1526659201 is SCHEMA SELECT ENAME,SAL*12 FROM P1526659201.EMP; --3. Like ORACLE, any mathematical expression containing a null value is null: SELECT ENAME,SAL*12+COMM FROM P1526659201.EMP;--4. Where|| This is equivalent to converting SAL to SELECT ENAME.|| SAL FROM EMP; --5. Method of representing string SELECT ENAME|| ' ENAME' FROM EMP; --6. If the string contains 'replace it with' SELECT ENAME|| '' ENAME' FROM EMP; --7. Select TOP 5 * FROM EMP; --8. Select ALL * FROM EMP; --9. Remove duplicate data in two fields SELECT DISTINCT DEPTNO,JOB FROM EMP;--10. Select ASCII ('ANT') "ASCII" FROM DUMMY;SCHEMA--Create SCHEMA.-- Syntax: CREATE SCHEMA [OWNED BY ]/*OWNED BY: Specifies the owner of SCHEMA if omitted. The current user will be the owner of this SHEMA */CREATE SCHEMA MY_SCHEMA;CREATE SCHEMA MY_SCHEMA OWNED BY SYSTEM.-- Delete SCHEMA:--Syntax: DROP SCHEMA []DROP_OPTION: CASCADE| RESTRICT/* The default DROP_OPTION is: RESTRICT(constraint constraint)RESTRICT: directly delete objects without dependencies, if the object has dependencies, an error message will be thrown. CASCADE: Delete all objects directly. */ CREATE SCHEMA MY_SCHEMA; CREATE TABLE MY_SCHEMA.T (A INT);DROP SCHEMA MY_SCHEMA CASCADE;SELECT * FROM TABLES WHERE SCHEMA_NAME ='P1526659201 ' --Query all tables under SCHEMA:P1526659201 TABLE/* rows Tables are suitable for some scenarios: * Processing one record at a time * The app needs access to a complete record or most of a record (i.e. all or most of the fields in a record) * No compression ratio required * No or very little aggregation, grouping, etc. complex operations * The number of rows of records in the table is not many */--Here are some columns to create a row table: --Example 1 CREATE TABLE ACCOUNTS ( ACT_NO CHAR(18), ACT_NAME NVARCHAR(100), BALANCE DECIMAL(15,2), PRIMARY KEY (ACT_NO) );--Example 2 CREATE TABLE R (A INT PRIMARY KEY, B NVARCHAR(10) );CREATE TABLE F (FK INT, B NVARCHAR(10), UNIQUE (FK, B), FOREIGN KEY(FK) REFERENCES R ON UPDATE CASCADE NO LOGGING;/* Create a new table based on an existing table. HANA SQL provides two types of methods */: --Method 1: Create tables with exactly the same data types and constraints as the source tables. For example: CREATE TABLE T_ACCOUNTS LIKE ACCOUNTS WITH NO DATA--Method 2: The table field type created is the same as the NULL/NOT NULL attribute CREATE TABLE T_ACCOUNTS 1 AS (SELECT * FROM ACCOUNTS) WITH DATA/* list is suitable for the following scenarios: * Computational operations are usually performed on only one or a few columns * Tables are usually searched based on values on a small number of columns * Tables have many columns * Tables have many rows and are typically column-based (such as aggregate calculations and field value lookups in WHERE). * High compression ratio required. */-- Example 1 CREATE COLUMN TABLE TPCH.NATION (N_NATIONKEY INT NOT NULL, N_NAME CHAR(25), N_REGIONKEY INT, N_COMMENT VARCHAR(152), PRIMARY KEY(N_NATIONKEY) );--Example 2(partition, only for lists, partition key must be part of primary key)CREATE COLUMN TABLE TPCH.LINEITEM (L_ORDERKEY INT NOT NULL,L_PARTKEY INT, L_SUPPKEY INT,L_LINENUMBER INT NOT NULL, L_QUANTITY DECIMAL(10,2),L_EXTENDEDPRICE DECIMAL(10,2), L_DISCOUNT DECIMAL(10,2),L_TAX DECIMAL(10,2), L_RETURNFLAG CHAR(1),L_LINESTATUS CHAR(1), L_SHIPDATE DATE,L_COMMITDATE DATE,L_RECEIPTDATE DATE, L_SHIPINSTRUCT CHAR(25),L_SHIPMODE CHAR(10), L_COMMENT VARCHAR(44), PRIMARY KEY INVERTED VALUE (L_ORDERKEY,L_LINENUMBER) ) PARTITION BY HASH (L_ORDERKEY,L_LINENUMBER) PARTITIONS 4, RANGE (L_SHIPDATE) ( PARTITION '2011/01/01'
