简介
这篇记录的是关于SQL基础的一些知识,是其中的一个基础培训,因为培训材料是英文的,所以下面的差不多的就直接英文了,中文的是我自己的话。
后面的SQL语句可能只适应一些特定的DBMS,如本文中HANA。
这篇是一些点,关于较详细SQL,可以参考:http://www.w3school.com.cn/sql/
基本概念
Database:structured collection of “records”
Database System:DBMS + specific database
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
待补图
三层两映射
三层:Internal Level(INDEX), Conceptual Level(TABLE), External Level(VIEW)
两映射:即内,概念,外之间的映射
待补图
关系型数据库:笛卡儿积(R是 A * B * C的子集,详细的可以自行查资料,这个还是很见单的)
Relational Languages
Relational Algebra: join,union等
Relational Calculus
SQL: 常用
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)
主键;候选键;外键
主键: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,可以指向自己表
表基本操作
基本模式:
SELECT Column , Column , Count (*)
FROM Table [AS ] T
WHERE Condition
GROUP BY Column , Column
HAVING Group Condition
ORDER BY Column ASC [默认], Column DESC ;
字母,数字带单引号,双引号等区别(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”