HANA Express Edition Learning

服务端：VMFusion，HXE虚拟机文件。
网络连接方式：桥接（注意：重启虚拟机或者重启路由器之后IP可能会变，登录linux执行/sbin/ifconfig查看最新IP）
客户端：MacOS，Eclipse Neon，安装hana插件。

HANA练习SQL：

--创建schema
create schem T4H

--设置默认schema
set schema T4H;

--删除表
DROP TABLE "Books";

--创建基于列存储的表
CREATE COLUMN TABLE "Books"
(
	Book_id VARCHAR(3) PRIMARY KEY,
	Title VARCHAR(12) NOT NULL,
	Category VARCHAR(10),
	MRP DECIMAL(10,2),
	Author VARCHAR(3),
	ISBN VARCHAR(13)	
);

--insert表
INSERT INTO "Books" (Book_id, Title, Category, MRP, Author, ISBN) VALUES ('B01', 'Chinese', 'Printed', 1120, 'A01', '99921-58-10-7');

--取T4H下的Books整表
select * from "T4H"."Books";

--取BOOK_ID, TITLE, ISBN
select Book_id, Title, Isbn from "Books";

--取两次Title列
select Title, Title from "Books";

--取Title不重复的值
select distinct Title from "Books";

--获取前5条
select top 5 * from "Books";

--从第三条后开始（不包含第三条）获取5条
select * from "Books" limit 5 offset 3;

--单行注释
select 
-- "BOOK_ID",
"TITLE",
"CATEGORY",
"MRP",
"AUTHOR",
"ISBN"  --international standard book number
 from "T4H"."Books"
 
 --多行注释
 select 
/*"BOOK_ID",
"TITLE",
"CATEGORY",
"MRP",*/
"AUTHOR",
"ISBN"
 from "T4H"."Books"
 
 --排序
select Author, MRP
from "T4H"."Books"
order by Author

select Author, MRP
from "T4H"."Books"
order by Author asc

select Author, MRP
from "T4H"."Books"
order by Author desc

select Author, MRP
from "T4H"."Books"
order by Author, MRP

select Author, MRP
from "T4H"."Books"
order by 2 asc

--filter data
--注意：表名用双引号，列值用单引号
select * from "Books"
where category = 'pdf'
order by MRP desc

/*
=		qeual
&lt;&gt;		not-equal
&lt;		less than
&gt;		more than
&lt;=		less than or qeual
&gt;=		greater then or equql
between	between lower_value and higher_value 
is null is a null value
*/

--null and not null
--null value is not zero or blank.
--null value is something the database don't understand.
--null value display as '?' in hana database table.

--null value.
select * from "Books" where Author is not null
select * from "Books" where Author is null

--blank value
select * from "Books" where category = ''

--anvanced filterting data.
--or
select * from "Books"
	where category = 'pdf' or
		  category = 'e-book'
order by category	

--括号的优先级最高
select * from "Books"
	where (category = 'pdf' or
		  category = 'e-book')
	and   MRP &gt; '100'
	order by category
	
--in
--in的效率比or高
select * from "Books"
where Author in ('A01','A03','A05')
order by Author

--dynamic select
select* from "Books"
where Author not in ('A01','A03','A05')


--like
select * from "Location"
where Street like 'C%'

--like包含特定字母的
select * from "Location"
where Street like '%C%i%'

--like和占位符
--尽量少用占位符！
select * from "Location"
where City like 'S__'

--dummy table in hana
--dummy与oracle中的dule类似
--查询系统中所有的表
select * from tables;

select * from tables
where TABLE_NAME = 'DUMMY'

select * from DUMMY;

select 'Good night' from DUMMY;

select CURRENT_DATE, 
	   CURRENT_TIME
	   from DUMMY;
	   
select rand() from DUMMY;

select rand()*5 from DUMMY;

select 25 * 4 from DUMMY;

--calculated column
select 'Publisher',
	    location_id,
	    'is located in city:',
	    city,
	    'country:',
	    '(',
	    country,
	    ')'
	    from "Location"

select 'Publisher'||
	    location_id||
	    'is located in city:'||
	    city||
	    'country:'||
	    '('||country||')'
	    as Publisher Address
	    from "Location"	   
	    
select MRP, MRP * 2 as MyDouble from "Books"

--case expression
select BOOK_ID, MRP,
	case
		when mrp &lt; 100 then '初级'
		when mrp &gt;=100 and mrp &lt; 200 then '中级'
		when mrp &gt;=200 and mrp &lt; 300 then '高级'
		else 'Plus高级'
	end as "等级"
	from "Books"
	
select BOOK_ID, ISBN,
	case
		when ISBN is not null then ISBN
		else 'This book is not numbered'
	end as "ISBN_NEW"
	from "Books"

--case某列，将满足条件的在本列进行运算后另存一列
select Book_id,
       Category,
       case Category
	      when 'pdf' then 'A'
	       when 'e-book' then 'B'
	       when 'Printed' then 'C'
	       when '' then 'X'
      end as "NewCat"
from "Books" 
order by "NewCat"

--case将一列不同的结果显示为多列
select Book_id,
	case Category when 'pdf' then 'X' else '' end as "PDF",
	case Category when 'e-book' then 'X' else '' end as "E-BOOK",
	case Category when 'Printed' then 'X' else '' end as "PRINTED"
from "Books"

--function
select * from "Location";

--string function
select "e-Mail", trim("e-Mail") from "Location";
select "e-Mail", upper("e-Mail") from "Location";
select "e-Mail", left("e-Mail",5) from "Location";
select "e-Mail", substr("e-Mail",5,10) from "Location";

--date and time function
select now() from DUMMY;
select CURRENT_DATE,CURRENT_TIME,CURRENT_USER,CURRENT_SCHEMA from DUMMY;

select author_id,
	   birthday,
	   current_date,
	   next_day(current_date),
	   years_between(birthday, current_date) as Age,
	   month(birthday) as BirMonth,
	   year(birthday) as  BirMonth,
	   add_years(birthday,18) as BirP18Years,
	   add_months(birthday,50) as BirP50Months
from "Author";

set schema T4H;

--select * from "Books";
select count(MRP),max(MRP),min(MRP),avg(MRP) from "Books";

--count()会忽略null值
select count(*) from "Books";  			--20
select count(Author) from "Books";		--18

--如果使用count，则所有select的字段都需要group by
select Category,count(*) from "Books"
group by Category;

select Author,Category,Count(*) from "Books"
group by Author,Category;

--group by后必须是底表字段名，不能使alias
select Author as Auth,Category,Count(*) from "Books"
group by Author,Category;

--having从查询结果中再筛选
select Author,Category,Count(*) from "Books"
group by Author,Category
having count(*) > 1;

--order by应该放在select语句的最末尾
--顺序：from - where - group by - having - order by
select
	 Author,
	 Category,
	 Count(*) 
from "Books" 
where category = 'Printed' 
group by Author,
	 Category having count(*) <> 0 
order by Author;

--having从查询结果中再筛选
select Author,Category,Count(*) from "Books"
group by Author,Category
having count(*) > 1;

--order by应该放在select语句的最末尾
--顺序：from - where - group by - having - order by
select
	 Author,
	 Category,
	 Count(*) 
from "Books" 
where category = 'Printed' 
group by Author,
	 Category having count(*) <> 0 
order by Author 
;

--子查询
select * from "Books";
select Book_id,Author from "Books" where Author in ('A01','A03','A08');

select
	 Name,
	birthday 
from "Author" 
where Author_id in(select
	 Author 
	from "Books" 
	where Category = 'pdf') 
;

select
	 name,
	 birthday,
	 (select
	 count(*) 
	from "Books" 
	where "T4H"."Books".Author = "T4H"."Author".Author_id) as "No. Author" 
from "Author" 
;

--union
--union中的所有sql语句中的select字段名及select顺序必须一致.
--union：查询出所有query中满足条件的不重复的值.
--union all:查询出所有query中满足条件的值的并集.

--无实际业务意义，仅仅练习语法.
select ID as S_ID,Name as S_NAME from "STUDENT" where course is not null
union
select ID as I_ID,Name as I_NAME from "INSTRUCTOR"
order by S_ID;

--intersect
--取所有sql的交集.
select ID as S_ID,Name as S_NAME from "STUDENT" --where course is not null
intersect
select ID as I_ID,Name as I_NAME from "INSTRUCTOR"
order by S_ID;

--except
--从查询2中排除查询1的结果作为整个查询的结果
select ID as S_ID,Name as S_NAME from "STUDENT" --where course is not null
except
select ID as I_ID,Name as I_NAME from "INSTRUCTOR"
order by S_ID;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

--创建schema

create schem T4H

--设置默认schema

set schema T4H;

--删除表

DROP TABLE "Books";

--创建基于列存储的表

CREATE COLUMN TABLE "Books"

(

Book_id VARCHAR(3) PRIMARY KEY,

Title VARCHAR(12) NOT NULL,

Category VARCHAR(10),

MRP DECIMAL(10,2),

Author VARCHAR(3),

ISBN VARCHAR(13)

);

--insert表

INSERT INTO "Books" (Book_id, Title, Category, MRP, Author, ISBN) VALUES ('B01', 'Chinese', 'Printed', 1120, 'A01', '99921-58-10-7');

--取T4H下的Books整表

select * from "T4H"."Books";

--取BOOK_ID, TITLE, ISBN

select Book_id, Title, Isbn from "Books";

--取两次Title列

select Title, Title from "Books";

--取Title不重复的值

select distinct Title from "Books";

--获取前5条

select top 5 * from "Books";

--从第三条后开始（不包含第三条）获取5条

select * from "Books" limit 5 offset 3;

--单行注释

select

-- "BOOK_ID",

"TITLE",

"CATEGORY",

"MRP",

"AUTHOR",

"ISBN" --international standard book number

from "T4H"."Books"

--多行注释

select

/*"BOOK_ID",

"TITLE",

"CATEGORY",

"MRP",*/

"AUTHOR",

"ISBN"

from "T4H"."Books"

--排序

select Author, MRP

from "T4H"."Books"

order by Author

select Author, MRP

from "T4H"."Books"

order by Author asc

select Author, MRP

from "T4H"."Books"

order by Author desc

select Author, MRP

from "T4H"."Books"

order by Author, MRP

select Author, MRP

from "T4H"."Books"

order by 2 asc

--filter data

--注意：表名用双引号，列值用单引号

select * from "Books"

where category = 'pdf'

order by MRP desc

= qeual

<> not-equal

< less than

> more than

<= less than or qeual

>= greater then or equql

between between lower_value and higher_value

is null is a null value

--null and not null

--null value is not zero or blank.

--null value is something the database don't understand.

--null value display as '?' in hana database table.

--null value.

select * from "Books" where Author is not null

select * from "Books" where Author is null

--blank value

select * from "Books" where category = ''

--anvanced filterting data.

--or

select * from "Books"

where category = 'pdf' or

category = 'e-book'

order by category

--括号的优先级最高

select * from "Books"

where (category = 'pdf' or

category = 'e-book')

and MRP > '100'

order by category

--in

--in的效率比or高

select * from "Books"

where Author in ('A01','A03','A05')

order by Author

--dynamic select

select* from "Books"

where Author not in ('A01','A03','A05')

--like

select * from "Location"

where Street like 'C%'

--like包含特定字母的

select * from "Location"

where Street like '%C%i%'

--like和占位符

--尽量少用占位符！

select * from "Location"

where City like 'S__'

--dummy table in hana

--dummy与oracle中的dule类似

--查询系统中所有的表

select * from tables;

select * from tables

where TABLE_NAME = 'DUMMY'

select * from DUMMY;

select 'Good night' from DUMMY;

select CURRENT_DATE,

CURRENT_TIME

from DUMMY;

select rand() from DUMMY;

select rand()*5 from DUMMY;

select 25 * 4 from DUMMY;

--calculated column

select 'Publisher',

location_id,

'is located in city:',

city,

'country:',

'(',

country,

')'

from "Location"

select 'Publisher'||

location_id||

'is located in city:'||

city||

'country:'||

'('||country||')'

as Publisher Address

from "Location"

select MRP, MRP * 2 as MyDouble from "Books"

--case expression

select BOOK_ID, MRP,

case

when mrp < 100 then '初级'

when mrp >=100 and mrp < 200 then '中级'

when mrp >=200 and mrp < 300 then '高级'

else 'Plus高级'

end as "等级"

from "Books"

select BOOK_ID, ISBN,

case

when ISBN is not null then ISBN

else 'This book is not numbered'

end as "ISBN_NEW"

from "Books"

--case某列，将满足条件的在本列进行运算后另存一列

select Book_id,

Category,

case Category

when 'pdf' then 'A'

when 'e-book' then 'B'

when 'Printed' then 'C'

when '' then 'X'

end as "NewCat"

from "Books"

order by "NewCat"

--case将一列不同的结果显示为多列

select Book_id,

case Category when 'pdf' then 'X' else '' end as "PDF",

case Category when 'e-book' then 'X' else '' end as "E-BOOK",

case Category when 'Printed' then 'X' else '' end as "PRINTED"

from "Books"

--function

select * from "Location";

--string function

select "e-Mail", trim("e-Mail") from "Location";

select "e-Mail", upper("e-Mail") from "Location";

select "e-Mail", left("e-Mail",5) from "Location";

select "e-Mail", substr("e-Mail",5,10) from "Location";

--date and time function

select now() from DUMMY;

select CURRENT_DATE,CURRENT_TIME,CURRENT_USER,CURRENT_SCHEMA from DUMMY;

select author_id,

birthday,

current_date,

next_day(current_date),

years_between(birthday, current_date) as Age,

month(birthday) as BirMonth,

year(birthday) as BirMonth,

add_years(birthday,18) as BirP18Years,

add_months(birthday,50) as BirP50Months

from "Author";

set schema T4H;

--select * from "Books";

select count(MRP),max(MRP),min(MRP),avg(MRP) from "Books";

--count()会忽略null值

select count(*) from "Books"; --20

select count(Author) from "Books"; --18

--如果使用count，则所有select的字段都需要group by

select Category,count(*) from "Books"

group by Category;

select Author,Category,Count(*) from "Books"

group by Author,Category;

--group by后必须是底表字段名，不能使alias

select Author as Auth,Category,Count(*) from "Books"

group by Author,Category;

--having从查询结果中再筛选

select Author,Category,Count(*) from "Books"

group by Author,Category

having count(*) > 1;

--order by应该放在select语句的最末尾

--顺序：from - where - group by - having - order by

select

Author,

Category,

Count(*)

from "Books"

where category = 'Printed'

group by Author,

Category having count(*) <> 0

order by Author;

--having从查询结果中再筛选

select Author,Category,Count(*) from "Books"

group by Author,Category

having count(*) > 1;

--order by应该放在select语句的最末尾

--顺序：from - where - group by - having - order by

select

Author,

Category,

Count(*)

from "Books"

where category = 'Printed'

group by Author,

Category having count(*) <> 0

order by Author

;

--子查询

select * from "Books";

select Book_id,Author from "Books" where Author in ('A01','A03','A08');

select

Name,

birthday

from "Author"

where Author_id in(select

Author

from "Books"

where Category = 'pdf')

;

select

name,

birthday,

(select

count(*)

from "Books"

where "T4H"."Books".Author = "T4H"."Author".Author_id) as "No. Author"

from "Author"

;

--union

--union中的所有sql语句中的select字段名及select顺序必须一致.

--union：查询出所有query中满足条件的不重复的值.

--union all:查询出所有query中满足条件的值的并集.

--无实际业务意义，仅仅练习语法.

select ID as S_ID,Name as S_NAME from "STUDENT" where course is not null

union

select ID as I_ID,Name as I_NAME from "INSTRUCTOR"

order by S_ID;

--intersect

--取所有sql的交集.

select ID as S_ID,Name as S_NAME from "STUDENT" --where course is not null

intersect

select ID as I_ID,Name as I_NAME from "INSTRUCTOR"

order by S_ID;

--except

--从查询2中排除查询1的结果作为整个查询的结果

select ID as S_ID,Name as S_NAME from "STUDENT" --where course is not null

except

select ID as I_ID,Name as I_NAME from "INSTRUCTOR"

order by S_ID;

本文作者: GavinDong

版权属于: GavinDong博客

文章链接: https://gavindong.com/1748.html

如果使用过程中遇到问题，可 **点击此处** 交流沟通。

发表回复

登录后才能评论

评论列表（4条）

ag 2018.03.10 09:47

默认SYSTEM用户是没有 Lifecycle Manager的权限的，需要授予中间包含lm关键字的所有角色。
Lifecycle Manager的的网址为（我的hana实例为90）：
http://hxehost:8090/sap/hana/xs/lm/

回复
ag 2018.03.10 09:14

SAP HANA Web-based Development Workbench是SAP HANA IDE Lite的完整版，涵盖了大多数hana studio的功能，使得hana的开发、管理可以基于web在任意地点和时间完成。
最新版的SAP HANA Web-based Development Workbench上可以跳转SAP HANA Application Lifecycle Manager。
SAP HANA Web-based Development Workbench的网址为：
http://hana_host_name:80hana_instance/sap/hana/ide/
例如我的hana实例为90，hana服务器的hostname为hxehost，我已经在本机配置好了hosts文件，则我访问 Web-based Development Workbench的网址为：
http://hxehost:8090/sap/hana/ide
用户和使用hana studio是一样的，例如可以使用SYSTEM用户测试。

回复
- ag 2018.03.10 09:15
  
  注意浏览器用谷歌浏览器或者firefox！
  
  回复
ag 2017.11.26 14:55

脚本中写有多条sql语句时，除了最后一条sql语句，前边的语句结束都要写分号（;），否则下一条sql语句会报错。
例如：set schema T4H;

回复

HANA Express Edition Learning

相关文章

密码保护：SAP信用管理增强控制

SMARTFORMS Print Privew ZOOM Button need in Enable mode

密码保护：S4 ACDOCA会计凭证表的几个奇异点

发表回复

评论列表（4条）