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] T
WHERE Condition
GROUP BY Column, Column
HAVING Group Condition
ORDER 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;//True
SELECT 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 RATING
FROM 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.

//:~Implicit
SELECT Column,Column,Column
FROM Table,Table
WHERE Condition;
//:~Explicit
SELECT Column,Column,Column
FROM Table CROSS JOIN Table
WHERE 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 …)

//:~Implicit
SELECT Column,Column,Column
FROM Table,Table
WHERE JOIN Condition
    AND supplementary condition;
//:~Explicit
SELECT Column,Column,Column
FROM Table JOIN Table ON JOIN condition
WHERE 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 JOIN
SELECT Column,Column,Column
FROM Table LEFT OUTER JOIN Table ON JOIN condition
WHERE additional condition;

//:~RIGHT OUTER JOIN
SELECT Column,Column,Column
FROM Table RIGHT OUTER JOIN Table ON JOIN condition
WHERE additional condition;

//:~FULL OUTER JOIN
SELECT Column,Column,Column
FROM Table FULL OUTER JOIN Table ON JOIN condition
WHERE additional condition;

SUB QUERY

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

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

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

SELECT Column,Column,Column
FROM Table
WHERE 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 AS
SELECT Column,Column,Column
FROM Table
WHERE Condition
WITH 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培训的一部分内容，很多都是概念级的内容，想要深入的话还是需要不停的阅读和实践。