DB2数据库的部分接触-〉从SQLServer数据库转换到DB2的学习和笔记

从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

 

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