从SQLServer数据库转换到DB2的学习和笔记
1. 在安装的时候最好是把数据库仓库选上,这样对后面的设置有很好的帮助
2. 由于在DB2数据库的查询中需要指定模式名称,因此最好是先在操作系统得用户组上创建登陆的用户名,然后在DB2的控制中心添加相同名字的用户和模式名称,在创建表时使用登陆名的模式,这样在使用这个登陆名查询相关的数据时就不需要指定模式名称,系统会自动帮你添加。
3. 在SQLServer中有Top 10 这个功能在DB2上可以通过以下实现:
1、选择前面n条记录,如从employee表中选择工资最低的10个人:
select * from employee order by salary fetch first 10 rows only
如选择工资最高的10个人,可以:
select * from employee order by salary desc fetch first 10 rows only
2、从结果集中利用rownumber()函数根据行号选择一部分数据,如从employee表中选择工资位于第21到30位的员工记录:
select * from
(select EMPNO, FIRSTNME, LASTNAME, SALARY , rownumber() over (order by salary desc)
AS rn FROM employee)
AS tr WHERE rn between 21 and 30
(select EMPNO, FIRSTNME, LASTNAME, SALARY , rownumber() over (order by salary desc)
AS rn FROM employee)
AS tr WHERE rn between 21 and 30
4. 创建表脚本的比较:
SQL Server
|
DB2 UDB
|
CREATE TABLE [employee](
[empid] INT IDENTITY
PRIMARY KEY
,
[name] VARCHAR(40) NOT NULL,
[job] VARCHAR(15) NOT NULL,
[hire_date] DATETIME NOT NULL,
[department] INT NULL,
[basic salary] DECIMAL(8,2) NULL,
[commission] DECIMAL(8,2) NULL)
[prize] AS ([basic salary] * 0.5))
|
CREATE TABLE employee(
empid INT GENERATED ALWAYS AS IDENTITY
NOT NULL PRIMARY KEY
,
name VARCHAR(40) NOT NULL,
job VARCHAR(15) NOT NULL,
hire_date TIMESTAMP NOT NULL,
department INT,
basic_salary DECIMAL(8,2),
commission DECIMAL(8,2))
Prize GENERATED ALWAYS AS
(basic_salary * 0.5))
|
CREATE CLUSTERED INDEX [PK_author_id] ON [authors] ( [author_id] ASC )
|
CREATE INDEX PK_author_id ON authors ( author_id ASC) CLUSTER
|
CREATE NONCLUSTERED INDEX
[idx_authors] ON [authors]
([name] ASC, [firstname] ASC )
|
CREATE INDEX idx_authors ON authors
(name ASC, firstname ASC)
|
CREATE VIEW v_redbook
AS
SELECT title,
Authors=authors.name,
brand_name,
price,
release_date
FROM authors, redbooks, brands
WHERE authors.author_id =
redbooks.author_id AND
redbooks.brand_id =
brands.brand_id
|
CREATE VIEW v_redbook
AS
SELECT title,
authors.name AS Authors,
brand_name,
price,
release_date
FROM authors, redbooks, brands
WHERE authors.author_id =
redbooks.author_id AND
redbooks.brand_id =
brands.brand_id
|
DECLARE @varname datatype =
defaultvalue
|
DECLARE varname datatype DEFAULT
defaultvalue;
|
SELECT @var1=value
|
SET var1 = value;
|
SELECT @var1=colname FROM table
WHERE…
|
SET var1 = ( SELECT colname FROM table
WHERE…);
|
SELECT @v1=col1,@v2=col2,@v3=col3
FROM table...
|
SELECT col1,col2,col3 INTO v1,v2,v3
FROM table...
|
SELECT column_alias = column_name,
...
|
SELECT column_name AS column_alias,
...
|
WHILE expression BEGIN … END
|
WHILE expression DO … END WHILE;
|
CONTINUE
|
ITERATE
|
BREAK
|
LEAVE loop_label
|
IF (…) BEGIN … END ELSE …
|
IF (…) THEN … ELSE … END IF;
|
EXECUTE (‘INSERT INTO t1 VALUES(2)’)
|
INSERT INTO t1 VALUES (2); OR
EXECUTE IMMEDIATE (‘INSERT INTO t1
VALUES(2)’);
|
EXECUTE procname( parm1,parm2,...)
|
CALL procname( parm1,parm2,…);
|
EXECUTE @retval=procname(
parm1,parm2,…)
|
CALL procname( parm1,parm2,…);
GET DIAGNOSTICS retval =
RETURN_STATUS;
|
RETURN <int_value>
|
RETURN < int_expr>;
|
GOTO <label>
|
GOTO <lable>
|
|
|
|
|
|
|
5 获取随机记录的前N条记录
SELECT * FROM MYTABLE ORDER BY rand() DESC FETCH FIRST N ROWS ONLY