HANA SQL基础

简介

1. 这篇记录的是关于SQL基础的一些知识，是其中的一个基础培训，因为培训材料是英文的，所以下面的差不多的就直接英文了，中文的是我自己的话。
2. 后面的SQL语句可能只适应一些特定的DBMS,如本文中HANA。
3. 这篇是一些点，关于较详细SQL，可以参考：http://www.w3school.com.cn/sql/

基本概念

1. Database：structured collection of “records”
2. Database System：DBMS + specific database
3. Database Management System：DBMS(比如HANA,DB2,MYSQL,ORACLE等等)
   -Every access to the db(create,read,insert,update,delete) goes exclusively（唯一的） through the DBMS
   
   • The DBMS exercises complete control over the db
   • 待补图
4. 三层两映射
   
   • 三层：Internal Level（INDEX）, Conceptual Level(TABLE), External Level(VIEW)
   • 两映射：即内，概念，外之间的映射
   • 待补图
5. 关系型数据库：笛卡儿积（R是 A * B * C的子集，详细的可以自行查资料，这个还是很见单的）
6. Relational Languages
   
   • Relational Algebra: join,union等
   • Relational Calculus
   • SQL: 常用
7. SQL Language Elements: DML,DDL,DCL
   
   • DML: Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE)
   • DDL : Data Definition Language (CREATE, ALTER, DROP, RENAME)
   • DCL : Data Control Language (GRANT,REVOKE)
8. 主键；候选键；外键
   
   • 主键：1 key is selected as Primary Key;主键可以包含多个列;A table with a primary key does not contain duplicates
   • 外键：set of columns, which is a (primary) key in an(other) table，说的通俗点就是一个表的主键在另外的一个表中维护两个表的一对多或者多对多的关系；外键可以为NULL，可以指向自己表

表基本操作

1. 基本模式：

SELECT Column, Column, Count(*)FROM Table [AS] TWHERE ConditionGROUP BY Column, ColumnHAVING Group ConditionORDER BY Column ASC[默认], Column DESC;

1. 字母，数字带单引号，双引号等区别（HANA）

SELECT a, 'b', "c", 1, '2', "3" FROM "4"

Clause EN CN a Existing column named “A” 默认转化成大写 ‘b’ Artificial result column with string “b” as value in each row 正常的字符串 “c” Existing column named “c” 不会转换成大写，表中有”c” 栏 1 Artificial result column with 1 as numeric value 就是普通的数值1 ‘2’ Artificial result column with string ‘2’ as value 就是普通的字符串1 “3” Existing column named “3” 表中有栏”3” “4” Existing Table named “4” 表”4”

3. HANA Functions

Function Explanation YEAR(Date) year ADD_YEARs(Date,n) n years later DAYNAME(Date) weekday(English) CURRENT_DATE current date ABS(Number) absolute value ROUND(Number) rounding(四舍五入) SQRT(Number) square root UPPER(String) convert to upper case SUBSTR(String,Start,Length) cut out of a string(substring) LENGTH(String) length of a string

4. Tuple Variables

//[AS]：有隐示和显示两种，下面是隐士SELECT A.Name FROM A a;//False,注意这个SELECT a.Name FROM A a;//TrueSELECT Name FROM A a;//True

`
5. 在SELECT语句中可以使用CASE语法对一个列种的数据进行数学操作,DEMO如下

SELECT *,    CASE        WHEN A < 120 THEN 'LOW'        WHEN A >= 120 AND A < 180 THEN 'MEDIUM'        ELSE 'HIGH'    END AS RATINGFROM TABLE;

1. DISTINCT:去重复
   
   • 同样作用于NULL
   • if a projection list contains multiple columns, DISTINCT always refers to the combination of all these columns：通俗点说就是当DISTINCT在多个栏前面时，当所有的栏目都相同时才表示重复
2. ORDER BY
   
   • ASC = ascend 默认ASC
   • DESC = descend
   • ORDER BY后面可以跟列名，也可以跟SELECT中的列名索引
3. Top N
4. LIMIT N [OFFSET N]
5. LIKE:
   
   • %: anything
   • _: single character
   • If you want to search for percentage sign(%) or underscore(_) itself,you have to an ESCAPE character in front.如：LIKE '$%%' ESCAPE '$'
6. Operator Precedence

Precedence Operator Explanation Highest () parentheses - unary Minus *,/ multiplication, division +,- addition,subtraction =,<,<=,>,>=,<>,IS NULL,LIKE,BETWEEN comparison NOT logical negation AND conjunction Lowest OR disjunction

Aggregate Data

1. HANA aggregate expressions

Aggregate Name Description COUNT Count MIN Minimum MAX Maximun SUM Sum AVG Average STDDEV Standard Deviation(标准差) VAR Variance(方差)

2. GROUP BY … HAVING(就是关于分组后的条件语句) …

Multiple Tables

1. UNION [ALL]
   
   • The individual results tables must have the same number of columns
   • The corresponding result columns must have compatible data types.
   • The columns names of the resulting output table are based on the first SELECT statement.
2. UNION和UNION ALL的区别在于，UNION会去重复，而后者不会
3. JOIN：Implicit,Explicit(默认是INNER JOIN)
   
   • CROSS JOIN：Each row of left table is connected to each row of the right table.

//:~ImplicitSELECT Column,Column,ColumnFROM Table,TableWHERE Condition;//:~ExplicitSELECT Column,Column,ColumnFROM Table CROSS JOIN TableWHERE Condition;

• INNER JOIN：One row of the table and one row of the table are always joined to a common result row - provided that the JOIN condition is fulfilled.(JOIN … ON …)

//:~ImplicitSELECT Column,Column,ColumnFROM Table,TableWHERE JOIN Condition    AND supplementary condition;//:~ExplicitSELECT Column,Column,ColumnFROM Table JOIN Table ON JOIN conditionWHERE supplementary condition;

• OUTER JOIN：LEFT,RIGHT,FULL(For all these sub types of OUTER JOIN SAP HANA only provides the explicit syntax variant.)

//:~LEFT OUTER JOINSELECT Column,Column,ColumnFROM Table LEFT OUTER JOIN Table ON JOIN conditionWHERE additional condition;//:~RIGHT OUTER JOINSELECT Column,Column,ColumnFROM Table RIGHT OUTER JOIN Table ON JOIN conditionWHERE additional condition;//:~FULL OUTER JOINSELECT Column,Column,ColumnFROM Table FULL OUTER JOIN Table ON JOIN conditionWHERE additional condition;

SUB QUERY

1. 相关子查询
   
   • A correlated sub query refers to the outer query.And use EXISTS

SELECT Column,Column,ColumnFROM Table Tuple-VariableWHERE EXISTS (    SELECT *    FROM Table    WHERE Condition);

有个ALL可以替代这个，作用和下面的ANY意思整合相反
2. 无相关子查询
- A uncorrelated sub query make no reference to the outer query.

SELECT Column,Column,ColumnFROM TableWHERE Column IN (     SELECT Column    FROM Table    WHERE condition;)

You can use = ANY instead of IN,and you can use other comparison operators :
| = , < , <= , > , >= , <> |

NULL VALUE

1. If the corresponding value exists in principle, but is unknow(such as a birthday of a person).
2. If the corrsponding value does not exists.
   
   1. Some trivalent logic： X = 'unknow'
      
      • NOT : NOT X = 'unknow'
      • AND : TRUE AND X = 'unknow'; FALSE AND X = FALSE; X AND X = 'unknow'
      • OR : TRUE OR X = TRUE; FALSE OR X = 'unknow'; X OR X = 'unknow'

Changing Data Stored In Tables

1. INSERT INTO Table VALUES (Value,Value,Value);
2. INSERT INTO Table (Column,Column) VALUES (Value,Value);
3. INSERT INTO Table SELECT ... FROM ... WHERE ...;(你可以从另外的表中直接插入)
4. UPDATE Table SET Column = Value, Column = Value, Column = Value WHERE condition;
5. DELETE FROM Table WHERE Condition;

Data AND Access Control

1. SAP HANA provides the follwing data types:
   
   • Numeric types
     
     • TINYINT : 0-255
     • SMALLINT : 2B
     • INTERER : 4B
     • BIGINT : 8B
     • SMALLDECIMAL(p,s)
     • DECIMAL(p,s) : The precision is the total number of significant digits.The scale is the number of digits after the decimal point.
     • REAL : 4B
     • DOUBLE : 8B
   • Character string types
     
     • VARCHAR : ASCII character string with maximum length n(n <= 5000)
     • NVARCHAR : Unicode character string with maximum length n(n <= 5000)
     • ALPHANUM : Alphanumeric character string with maximum length n(n<=127)
     • SHORTTEXT : same as nvarchar.but support text- and string- search features
   • Date time types
     
     • DATE : year,month,day
     • TIME : hour,minute,second
     • SECONDDATE : combination of date and time
     • TIMESTAMP : ten millionth of a second
   • Binary types
     
     • VARBINARY
   • Larger object types
     
     • BLOB
     • CLOB
     • NCLOB
     • TEXT
2. DATA definition
   
   • CREATE COLUMN TABLE
     
     • HANA 默认是列存储的，关于列存储和行存储请查看我博客的另外一篇文章
   • ALTER TABLE
   • RENAME TABLE
   • RENAME COLUMN
   • DROP TABLE

VIEW For Data Access

1. Advantage of views
   
   • Decoupling the user from lower levels(View is relative to External Level)
   • Tailored views, individually customized for the user and their tasks
   • Simplification of queries
   • Possibility of access restriction
2. WITH CHECK OPTION should be explicitly specified when you create view.

CREATE VIEW View ASSELECT Column,Column,ColumnFROM TableWHERE ConditionWITH CHECK OPTION;//用于INSERT,UPDATE时验证作用

Defining Data Access

1. The name of the database object implicit contain a schema name as prefix
2. To specify who can access which data, you can use the following two options.
   
   • Create views that represent the portion of the data.
   • Grant specific access permissions to selects user.
     
     • GRANT
       
GRANT Privilege,Privilege
ON Database Object
TO Grantee
WITH GRANT OPTION;

     • REVOKE
3. HANA中存储时时列储存的，在检索的时候运用了倒排索引的方法，可以加快查询速度
4. 创建索引
   
   • CREATE INDEX INDEX_NAME ON Table(Column ASC,Column DESC);

Transactions

1. ACID
   
   • Atomicity(A) : A transaction is either executed completely or not at all.
   • Consistency(C) : A transaction will bring the database from one consistency state to an other consistency state.
   • Isolation(I) : The database changes performed within a transaction shall only be visible to the outside after the completion of the transaction.
   • Durability(D) : If a transaction is successfully completed(COMMIT), all changes from the transaction must permanently stay even in cases of failures, or can be restore automatically.
2. SAP HANA does not provide a SQL statement to explicitly statrt a transaction.

结尾

以上是关于SQL培训的一部分内容，很多都是概念级的内容，想要深入的话还是需要不停的阅读和实践。