DB2常用内置函数及使用实战

DB2常用内置函数使用实例

在学习下面内容前，请先在你的DB2数据库中执行本文档结尾处的sql语句，便于验证函数执行效果。

1、COUNT()

返回一组行或值的个数。 SELECT COUNT(*) FROM EMPLOYEE; 2、AVG()

返回一组数值的平均值。

SELECT AVG(SALARY) FROM EMPLOYEE; 3、MAX()

返回一组数值中的最大值。

SELECT MAX(SALARY) FROM EMPLOYEE; 4、MIN()

返回一组数值中的最小值。

SELECT MIN(SALARY) FROM EMPLOYEE; 5、SUM()

返回一组数据的和。

SELECT SUM(SALARY) FROM EMPLOYEE;

6、ABS(),ABSVAL() 返回参数的绝对值。

SELECT ABS(-3.4) FROM SYSIBM.SYSDUMMY1; SELECT ABSVAL (-3.4) FROM SYSIBM.SYSDUMMY1;

7、SQRT()

返回该参数的平方根。

SELECT SQRT(36) FROM SYSIBM.SYSDUMMY1; 8、EXP()

返回参数的指数函数。

SELECT EXP(2) FROM SYSIBM.SYSDUMMY1; 9、HEX()

返回一个表示为字符串的值的16进制表示。 SELECT HEX(16) FROM EMPLOYEE;

10、MOD(EXP1,EXP2) 返回EXP1除以EXP2的余数。 SELECT MOD(20,8) FROM EMPLOYEE

11、RAND()

返回0和1之间的随机浮点数。 SELECT RAND() FROM SYSIBM.SYSDUMMY1;

12、POWER(EXP1,EXP2) 返回EXP1的EXP2次幂。

SELECT POWER(2,5) FROM SYSIBM.SYSDUMMY1;

13、ASCII()

返回整数参数最左边的字符的ASCII码。 SELECT ASCII('R') FROM SYSIBM.SYSDUMMY1;

14、CEILING() OR CEIL()

返回比参数大或等于参数的最小的整数值（进一法，返回整数） SELECT CEILING(3.23) FROM SYSIBM.SYSDUMMY1; SELECT CEIL(3.23) FROM SYSIBM.SYSDUMMY1;

15、FLOOR()

返回小于或等于参数的最大整数。（去尾法）

SELECT FLOOR(88.93) FROM SYSIBM.SYSDUMMY1;

16、TRUNCATE() OR TRUNC()

从表达式小数点右边位置开始截断并返回该数值。

去尾法，返回小数（精确到小数点后面n位，n由参数2确定）。 SELECT TRUNCATE(SALARY) FROM EMPLOYEE; SELECT TRUNCATE(SALARY,-2) FROM EMPLOYEE; SELECT TRUNC(345.6789,2) FROM SYSIBM.SYSDUMMY1;

17、ROUND(EXP1,EXP2)

返回EXP1小数点右边的第EXP2位置处开始的四舍五入值。 SELECT ROUND(2345.6789,2) FROM SYSIBM.SYSDUMMY1; SELECT ROUND(2345.6789,-2) FROM SYSIBM.SYSDUMMY1;

18、CHAR()

返回日期时间型，字符串，整数，十进制或双精度浮点数的字符串表示。 SELECT CHAR(SALARY) FROM EMPLOYEE;

19、VARCHAR()

返回字符串，日期型，图形串的可变长度的字符串表示 SELECT VARCHAR(FIRSTNME,50) FROM EMPLOYEE;

20、BIGINT()

返回整型常量中的数字或字符串的64位整数表示（INT则返回32位，SMALLINT返回短整） SELECT BIGINT(EMPNO) FROM EMPLOYEE;

21、REAL()

返回一个数值的单精度浮点数表示。 SELECT REAL(10) FROM SYSIBM.SYSDUMMY1;

22、FLOAT()

返回一个数的浮点表示。

SELECT FLOAT(789) FROM EMPLOYEE;

23、DOUBLE()

如果参数是一个数字表达式,返回与其相对应的浮点数,如果参数是字符串表达式,则返回该数的字符串表达式。

SELECT DOUBLE('5678') FROM SYSIBM.SYSDUMMY1;

24、CHR()

返回具有由整形参数（0～255）指定的ASCII码的字符 SELECT CHAR(97) FROM SYSIBM.SYSDUMMY1;

25、CONCAT()

返回两个字符串的连接。

SELECT CONCAT(FIRSTNME,LASTNAME) FROM EMPLOYEE;

26、UCASE() OR UPPER() 返回字符串的大写。

SELECT UCASE (FIRSTNME) FROM EMPLOYEE; SELECT UPPER(FIRSTNME) FROM EMPLOYEE;

27、LCASE() OR LOWER() 返回字符串的小写。

SELECT LCASE (FIRSTNME) FROM EMPLOYEE; SELECT LOWER (FIRSTNME) FROM EMPLOYEE;

28、SPACE(LENGTH)

返回一个包含LENGTH个空格的字符串。 SELECT SPACE(10) FROM SYSIBM.SYSDUMMY1;

29、YEAR()

返回一个数值的年部分。

SELECT YEAR(CURRENT DATE) FROM SYSIBM.SYSDUMMY1; SELECT YEAR(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1; SELECT YEAR('2003-01-02') FROM SYSIBM.SYSDUMMY1;

SELECT YEAR(BIRTHDATE) FROM EMPLOYEE;

30、MONTH()

返回一个数值的月部分。

SELECT MONTH (CURRENT DATE) FROM SYSIBM.SYSDUMMY1; SELECT MONTH (CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1; SELECT MONTH('2003-10-20') FROM SYSIBM.SYSDUMMY1; SELECT MONTH (BIRTHDATE) FROM EMPLOYEE;

31、DAY()

返回一个数值的日的部分。

SELECT DAY(CURRENT DATE) FROM SYSIBM.SYSDUMMY1; SELECT DAY(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1; SELECT DAY('2003-10-20') FROM SYSIBM.SYSDUMMY1; SELECT DAY(BIRTHDATE) FROM EMPLOYEE;

32、HOUR()

返回一个数值的小时部分。

SELECT HOUR(CURRENT TIME) FROM SYSIBM.SYSDUMMY1; SELECT HOUR(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1; SELECT HOUR('18:34:23') FROM SYSIBM.SYSDUMMY1;

33、MINUTE()

返回一个数值的分钟部分。

SELECT MINUTE(CURRENT TIME) FROM SYSIBM.SYSDUMMY1; SELECT MINUTE(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1; SELECT MINUTE('18:34:23') FROM SYSIBM.SYSDUMMY1;

34、SECOND()

返回一个数值的秒部分。

SELECT SECOND(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1; SELECT SECOND(CURRENT TIME) FROM SYSIBM.SYSDUMMY1; SELECT SECOND('18:34:32') FROM SYSIBM.SYSDUMMY1;

35、MICROSECOND() 返回一个数值的秒部分。

SELECT MICROSECOND(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1;

36、DAYNAME

返回一个大小写混合的字符串，对于参数的日部分，用星期表示这一天的名称（例如，Friday）。 SELECT DAYNAME(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1; SELECT DAYNAME(CURRENT DATE) FROM SYSIBM.SYSDUMMY1; SELECT DAYNAME(BIRTHDATE) FROM EMPLOYEE;

37、DAYOFWEEK()

返回参数中的星期几，用范围在1-7 的整数值表示，其中1 代表星期日。 SELECT DAYOFWEEK(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1; SELECT DAYOFWEEK(CURRENT DATE) FROM SYSIBM.SYSDUMMY1; SELECT DAYOFWEEK(BIRTHDATE) FROM EMPLOYEE;

38、DAYOFWEEK_ISO()

返回参数中的星期几，用范围在1-7 的整数值表示，其中1 代表星期一。 SELECT DAYOFWEEK_ISO(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1; SELECT DAYOFWEEK_ISO(CURRENT DATE) FROM SYSIBM.SYSDUMMY1; SELECT DAYOFWEEK_ISO(BIRTHDATE) FROM EMPLOYEE;

39、DAYOFYEAR()

返回参数中一年中的第几天，用范围在1-366 的整数值表示。 SELECT DAYOFYEAR(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1; SELECT DAYOFYEAR(CURRENT DATE) FROM SYSIBM.SYSDUMMY1; SELECT DAYOFYEAR(BIRTHDATE) FROM EMPLOYEE;

40、DAYS()

返回日期的整数表示。

SELECT DAYS(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1; SELECT DAYS('1980-04-27') FROM SYSIBM.SYSDUMMY1;

可用来计算任意两天之间相隔的天数。

SELECT DAYS(CURRENT TIMESTAMP) - DAYS('1980-04-27') FROM SYSIBM.SYSDUMMY1; SELECT DAYS('1981-02-27') - DAYS('1980-02-27') FROM SYSIBM.SYSDUMMY1;

41、JULIAN_DAY()

返回从公元前4712 年1 月1 日（儒略日历的开始日期）到参数中指定日期值之间的天数，用整数值表示。

SELECT JULIAN_DAY(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1;

42、MIDNIGHT_SECONDS()

返回午夜和参数中指定的时间值之间的秒数，用范围在0 到86400 之间的整数值表示。 SELECT MIDNIGHT_SECONDS(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1; SELECT MIDNIGHT_SECONDS('23:59:59') FROM SYSIBM.SYSDUMMY1;

43、MONTHNAME()

对于参数的月部分的月份，返回一个大小写混合的字符串（例如，January）。 SELECT MONTHNAME(CURRENT DATE) FROM SYSIBM.SYSDUMMY1; SELECT MONTHNAME(BIRTHDATE) FROM EMPLOYEE;

44、TIMESTAMP_ISO()

根据日期、时间或时间戳记参数而返回一个时间戳记值，返回TIMESTAMP类型 SELECT TIMESTAMP_ISO(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1;

45、TIMESTAMP_FORMAT()

返回根据字符模板'YYYY-MM-DD HH24:MI:SS'格式化字符串解释的TIMESTAMP，等于TO_DATE，返回TIMESTAMP类型。

SELECT TIMESTAMP_FORMAT('2012-04-24:12:00:00','YYYY-MM-DD HH24:MI:SS') FROM SYSIBM.SYSDUMMY1;

46、TIMESTAMPDIFF

根据两个时间戳记之间的时差，返回由第一个参数定义的类型表示的估计时差。 SELECT TIMESTAMPDIFF(256,CHAR(TIMESTAMP('2012-11-30

00:00:00')-TIMESTAMP('2011-11-08 00:00:00'))) FROM SYSIBM.SYSDUMMY1;

第一个参数，可以使用以下各值来替代，以指出结果的时间单位： 1=秒的小数部分 2=秒 4=分 8=时 16=天 32=周 64=月 128=季度 256=年

47、WEEK()

返回参数中一年的第几周，用范围在1-54 的整数值表示。以星期日作为一周的开始。 SELECT WEEK(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1;

48、WEEK_ISO()

返回参数中一年的第几周，用范围在1-53 的整数值表示。 SELECT WEEK_ISO(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1;

WEEK和WEEK_ISO比较 比如'2011-01-01'为星期日

WEEK(TO_DATE('2011-01-01','YYYY-MM-DD'))返回1，作为2011年的第1周。 WEEK_ISO(TO_DATE('2011-01-01','YYYY-MM-DD'))返回52，作为2010年的第52周。

49、DATE()

返回一个格式化字符串或日期时间类型中的日期

SELECT DATE('2001-03-19 12:30:12.345')FROM SYSIBM.SYSDUMMY1; SELECT DATE(CURRENT TIMESTAMP)FROM SYSIBM.SYSDUMMY1;

50、TIME()

返回一个格式化字符串或日期时间类型中的时间

SELECT TIME('2001-03-19 12:30:12.345')FROM SYSIBM.SYSDUMMY1; SELECT TIME(CURRENT TIMESTAMP)FROM SYSIBM.SYSDUMMY1;

51、SUBSTR(STR,START,LENGTH)

返回STR串自START处开始、长度为LENGTH的子串。 SELECT SUBSTR('ABCDEFG',3,2) FROM SYSIBM.SYSDUMMY1;

52、VALUE(EXPRESSION1,EXPRESSION2)

返回第一个非空（这里的“空”指null，不是空字符串）的值，当其第一个参数非空，直接返回第一个参数的值，否则检查下一个，直到找到非空值。COALESCE为同义词，在返回类型上稍有区别。

SELECT VALUE(NULL, 'ABC', NULL, 'DEF') FROM SYSIBM.SYSDUMMY1;

53、LENGTH() 返回参数的长度。

SELECT LENGTH(LASTNAME) FROM EMPLOYEE;

54、LEFT(ARG,LENGTH)

LEFT函数返回ARG最左边的LENGTH个字符的字符串，ARG可以是CHAR或BINARY STRING。 SELECT LEFT('abcdefgh',5) FROM SYSIBM.SYSDUMMY1;

55、RIGHT(ARG,LENGTH)

RIGHT函数返回ARG最右边的LENGTH个字符的字符串，ARG可以是CHAR或BINARY STRING。 SELECT RIGHT('abcdefgh',5) FROM SYSIBM.SYSDUMMY1;

56、INSERT(ARG1,POS,SIZE,ARG2)

返回一个字符串，将ARG1从POS处删除SIZE个字符，将ARG2插入该位置。 SELECT INSERT('TEDDY AND JERRY',2,4, 'OM') FROM SYSIBM.SYSDUMMY1;

57、LOCATE(ARG1,ARG2,)

在ARG2中查找ARG1第一次出现的位置，如果指定POS，则从ARG2的POS处开始查找ARG1第一次出现的位置。

SELECT LOCATE('ERRY','TERRY AND JERRY') FROM SYSIBM.SYSDUMMY1; SELECT LOCATE('ERRY','TERRY AND JERRY',2) FROM SYSIBM.SYSDUMMY1; SELECT LOCATE('ERRY','TERRY AND JERRY',3) FROM SYSIBM.SYSDUMMY1;

58、RTRIM()

删除字符串尾部的所有空格。 SELECT RTRIM(JOB) FROM EMPLOYEE;

SELECT RTRIM('MIKE ') FROM SYSIBM.SYSDUMMY1;

59、LTRIM()

删除字符串头部的所有空格。 SELECT LTRIM (JOB) FROM EMPLOYEE;

SELECT LTRIM (' MIKE') FROM SYSIBM.SYSDUMMY1;

60、TRIM()

删除字符串头尾部的空格。

SELECT LTRIM (JOB) FROM EMPLOYEE;

SELECT LTRIM (' MIKE ') FROM SYSIBM.SYSDUMMY1;

61、REPLACE(EXP1,EXP2,EXP3) 用EXP3替代EXP1中所有的EXP2

SELECT CHAR(REPLACE('ROMANDD','NDD','CCB'),10) FROM EMPLOYEE;

62、REPEAT(EXP1,EXP2)

返回EXP1重复EXP2次后的字符串。

SELECT (REPEAT('REPEAT',3)) FROM SYSIBM.SYSDUMMY1;

63、POSSTR(EXP1,EXP2)

返回EXP2在EXP1中的位置，如不存在子串，则返回0。 SELECT POSSTR('ABCDEFGH','D') FROM SYSIBM.SYSDUMMY1;

64、NULLIF(EXP1,EXP2)

如果EXP1=EXP2,则为NULL,否则为EXP1

SELECT NULLIF('ABCDEFGH','ABCDEFGH') FROM SYSIBM.SYSDUMMY1;

65、TO_CHAR()

返回已用字符模板进行格式化的时间戳记的字符表示。VARCHAR_FORMAT 的同义词。 SELECT TO_CHAR(CURRENT TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') FROM SYSIBM.SYSDUMMY1; SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') FROM SYSIBM.SYSDUMMY1;

66、TO_DATE()

从已使用字符模板解释过的字符串返回时间戳记。TIMESTAMP_FORMAT 的同义词。 SELECT TO_DATE('2012-04-27 12:23:56','YYYY-MM-DD HH24:MI:SS') FROM SYSIBM.SYSDUMMY1;

SELECT TIMESTAMP_FORMAT('2012-04-27 12:23:56','YYYY-MM-DD HH24:MI:SS') FROM SYSIBM.SYSDUMMY1;

自定义函数及其使用方法： 定义函数：

CREATE FUNCTION \(T1 TIMESTAMP, T2 TIMESTAMP) RETURNS INT LANGUAGE SQL

SPECIFIC SECONDSDIFF RETURN (

(DAYS(T1)-DAYS(T2))*86400+(MIDNIGHT_SECONDS(T1)-MIDNIGHT_SECONDS(T2)) ) 使用函数

SELECT SECONDSDIFF(CURRENT TIMESTAMP,TO_DATE('2012-04-26 14:50:30','YYYY-MM-DD HH24:MI:SS')) FROM SYSIBM.SYSDUMMY1;

CREATE TABLE EMPLOYEE ( EMPNO CHARACTER(6) NOT NULL, FIRSTNME VARCHAR(12) NOT NULL, MIDINIT CHARACTER(1), LASTNAME VARCHAR(15) NOT NULL, WORKDEPT CHARACTER(3), PHONENO CHARACTER(4), HIREDATE DATE, JOB CHARACTER(8), EDLEVEL SMALLINT NOT NULL, SEX CHARACTER(1), BIRTHDATE DATE, SALARY DECIMAL(9,2), BONUS DECIMAL(9,2), COMM DECIMAL(9,2), CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPNO) );

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000010', 'CHRISTINE', 'I', 'HAAS', 'A00', '3978', '1995-01-01', 'PRES ', 18, 'F', '1963-08-24', 152750.00, 1000.00, 4220.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000020', 'MICHAEL', 'L', 'THOMPSON', 'B01', '3476', '2003-10-10', 'MANAGER ', 18, 'M', '1978-02-02', 94250.00, 800.00, 3300.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000030', 'SALLY', 'A', 'KWAN', 'C01', '4738', '2005-04-05', 'MANAGER ', 20, 'F', '1971-05-11', 98250.00, 800.00, 3060.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000050', 'JOHN', 'B', 'GEYER', 'E01', '6789', '1979-08-17', 'MANAGER ', 16, 'M', '1955-09-15', 80175.00, 800.00, 3214.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000060', 'IRVING', 'F', 'STERN', 'D11', '6423', '2003-09-14', 'MANAGER ', 16, 'M', '1975-07-07', 72250.00, 500.00, 2580.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000070', 'EVA', 'D', 'PULASKI', 'D21', '7831', '2005-09-30', 'MANAGER ', 16, 'F', '2003-05-26', 96170.00, 700.00, 2893.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000090', 'EILEEN', 'W', 'HENDERSON', 'E11', '5498', '2000-08-15', 'MANAGER ', 16, 'F', '1971-05-15', 89750.00, 600.00, 2380.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000100', 'THEODORE', 'Q', 'SPENSER', 'E21', '0972', '2000-06-19', 'MANAGER ', 14, 'M', '1980-12-18', 86150.00, 500.00, 2092.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000110', 'VINCENZO', 'G', 'LUCCHESSI', 'A00', '3490', '1988-05-16', 'SALESREP', 19, 'M', '1959-11-05', 66500.00, 900.00, 3720.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000120', 'SEAN', ' ', 'O''CONNELL', 'A00', '2167', '1993-12-05', 'CLERK ', 14, 'M', '1972-10-18', 49250.00, 600.00, 2340.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000130', 'DELORES', 'M', 'QUINTANA', 'C01', '4578', '2001-07-28', 'ANALYST ', 16, 'F', '1955-09-15', 73800.00, 500.00, 1904.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000140', 'HEATHER', 'A', 'NICHOLLS', 'C01', '1793', '2006-12-15', 'ANALYST ', 18, 'F', '1976-01-19', 68420.00, 600.00, 2274.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000150', 'BRUCE', ' ', 'ADAMSON', 'D11', '4510', '2002-02-12', 'DESIGNER', 16, 'M', '1977-05-17', 55280.00, 500.00, 2022.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000160', 'ELIZABETH', 'R', 'PIANKA', 'D11', '3782', '2006-10-11', 'DESIGNER', 17, 'F', '1980-04-12', 62250.00, 400.00, 1780.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000170', 'MASATOSHI', 'J', 'YOSHIMURA', 'D11', '2890', '1999-09-15', 'DESIGNER', 16, 'M', '1981-01-05', 44680.00, 500.00, 1974.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000180', 'MARILYN', 'S', 'SCOUTTEN', 'D11', '1682', '2003-07-07', 'DESIGNER', 17, 'F', '1979-02-21', 51340.00, 500.00, 1707.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000190', 'JAMES', 'H', 'WALKER', 'D11', '2986', '2004-07-26', 'DESIGNER', 16, 'M', '1982-06-25', 50450.00, 400.00, 1636.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000200', 'DAVID', ' ', 'BROWN', 'D11', '4501', '2002-03-03', 'DESIGNER', 16, 'M', '1971-05-29', 57740.00, 600.00, 2217.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000210', 'WILLIAM', 'T', 'JONES', 'D11', '0942', '1998-04-11', 'DESIGNER', 17, 'M', '2003-02-23', 68270.00, 400.00, 1462.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000220', 'JENNIFER', 'K', 'LUTZ', 'D11', '0672', '1998-08-29', 'DESIGNER', 18, 'F', '1978-03-19', 49840.00, 600.00, 2387.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000230', 'JAMES', 'J', 'JEFFERSON', 'D21', '2094', '1996-11-21', 'CLERK ', 14, 'M', '1980-05-30', 42180.00, 400.00, 1774.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000240', 'SALVATORE', 'M', 'MARINO', 'D21', '3780', '2004-12-05', 'CLERK ', 17, 'M', '2002-03-31', 48760.00, 600.00, 2301.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000250', 'DANIEL', 'S', 'SMITH', 'D21', '0961', '1999-10-30', 'CLERK ', 15, 'M', '1969-11-12', 49180.00, 400.00, 1534.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000260', 'SYBIL', 'P', 'JOHNSON', 'D21', '8953', '2005-09-11', 'CLERK ', 16, 'F', '1976-10-05', 47250.00, 300.00, 1380.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000270', 'MARIA', 'L', 'PEREZ', 'D21', '9001', '2006-09-30', 'CLERK ', 15, 'F', '2003-05-26', 37380.00, 500.00, 2190.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000280', 'ETHEL', 'R', 'SCHNEIDER', 'E11', '8997', '1997-03-24', 'OPERATOR', 17, 'F', '1976-03-28', 36250.00, 500.00, 2100.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000290', 'JOHN', 'R', 'PARKER', 'E11', '4502', '2006-05-30', 'OPERATOR', 12, 'M', '1985-07-09', 35340.00, 300.00, 1227.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000300', 'PHILIP', 'X', 'SMITH', 'E11', '2095', '2002-06-19', 'OPERATOR', 14, 'M', '1976-10-27', 37750.00, 400.00, 1420.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000310', 'MAUDE', 'F', 'SETRIGHT', 'E11', '3332', '1994-09-12', 'OPERATOR', 12, 'F', '1961-04-21', 35900.00, 300.00, 1272.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000320', 'RAMLAL', 'V', 'MEHTA', 'E21', '9990', '1995-07-07', 'FIELDREP', 16, 'M', '1962-08-11', 39950.00, 400.00, 1596.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000330', 'WING', ' ', 'LEE', 'E21', '2103', '2006-02-23', 'FIELDREP', 14, 'M', '1971-07-18', 45370.00, 500.00, 2030.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('000340', 'JASON', 'R', 'GOUNOT', 'E21', '5698', '1977-05-05', 'FIELDREP', 16, 'M', '1956-05-17', 43840.00, 500.00, 1907.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('200010', 'DIAN', 'J', 'HEMMINGER', 'A00', '3978', '1995-01-01', 'SALESREP', 18, 'F', '1973-08-14', 46500.00, 1000.00, 4220.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('200120', 'GREG', ' ', 'ORLANDO', 'A00', '2167', '2002-05-05', 'CLERK ', 14, 'M', '1972-10-18', 39250.00, 600.00, 2340.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('200140', 'KIM', 'N', 'NATZ', 'C01', '1793', '2006-12-15', 'ANALYST ', 18, 'F', '1976-01-19', 68420.00, 600.00, 2274.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('200170', 'KIYOSHI', ' ', 'YAMAMOTO', 'D11', '2890', '2005-09-15', 'DESIGNER', 16, 'M', '1981-01-05', 64680.00, 500.00, 1974.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('200220', 'REBA', 'K', 'JOHN', 'D11', '0672', '2005-08-29', 'DESIGNER', 18, 'F', '1978-03-19', 69840.00, 600.00, 2387.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('200240', 'ROBERT', 'M', 'MONTEVERDE', 'D21', '3780', '2004-12-05', 'CLERK ', 17, 'M', '1984-03-31', 37760.00, 600.00, 2301.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('200280', 'EILEEN', 'R', 'SCHWARTZ', 'E11', '8997', '1997-03-24', 'OPERATOR', 17, 'F', '1966-03-28', 46250.00, 500.00, 2100.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('200310', 'MICHELLE', 'F', 'SPRINGER', 'E11', '3332', '1994-09-12', 'OPERATOR', 12, 'F', '1961-04-21', 35900.00, 300.00, 1272.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('200330', 'HELENA', ' ', 'WONG', 'E21', '2103', '2006-02-23', 'FIELDREP', 14, 'F', '1971-07-18', 35370.00, 500.00, 2030.00);

insert into EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM) values ('200340', 'ROY', 'R', 'ALONZO', 'E21', '5698', '1997-07-05', 'FIELDREP', 16, 'M', '1956-05-17', 31840.00, 500.00, 1907.00);

本文来源：https://www.bwwdw.com/article/ufcw.html