[TOC] 第二章 shanzm 準備 1.建立員工表——EMP 欄位說明： |EMP(員工表)欄位|說明| | | | EMPNO|工號 ENAME|姓名 JOB|工種 MGR|上級編號 HIREDATE|雇佣日期 SAL|工資 COMM|獎金 DEPTNO|部門編號 2.建立部門表——DEPT ...
目錄
第二章 shanzm準備
1.建立員工表——EMP
create table EMP
(
EMPNO int NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR int,
HIREDATE DATETIME,
SAL int,
COMM int,
DEPTNO int
);欄位說明：
| EMP(員工表)欄位 | 說明 |
|---|---|
| EMPNO | 工號 |
| ENAME | 姓名 |
| JOB | 工種 |
| MGR | 上級編號 |
| HIREDATE | 雇佣日期 |
| SAL | 工資 |
| COMM | 獎金 |
| DEPTNO | 部門編號 |
2.建立部門表——DEPT
create table DEPT
(
DEPTNO integer,
DNAME VARCHAR(14),
LOC VARCHAR(13)
);欄位說明：
| DEPT(部門表)欄位 | 說明 |
|---|---|
| depno | 部門編號 |
| dname | 部門名稱 |
| loc | 辦公地點 |
3.EMP表和DEPT表插入數據
給員工表EMP和部門表DEPT插入數據
begin transaction
INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, '12/17/1980', 800, NULL, 20)
INSERT INTO EMP VALUES
(7499, 'ALLEN', 'SALESMAN', 7698, '2/20/1981', 1600, 300, 30)
INSERT INTO EMP VALUES
(7521, 'WARD', 'SALESMAN', 7698, '2/22/1981', 1250, 500, 30)
INSERT INTO EMP VALUES
(7566, 'JONES', 'MANAGER', 7839, '4/2/1981', 2975, NULL, 20)
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, '9/28/1981', 1250, 1400, 30)
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839, '5/1/1981', 2850, NULL, 30)
INSERT INTO EMP VALUES
(7782, 'CLARK', 'MANAGER', 7839, '6/9/1981', 2450, NULL, 10)
INSERT INTO EMP VALUES
(7788, 'SCOTT', 'ANALYST', 7566, '12/9/1982', 3000, NULL, 20)
INSERT INTO EMP VALUES
(7839, 'KING', 'PRESIDENT', NULL, '11/17/1981', 5000, NULL, 10)
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698, '9/8/1981', 1500, 0, 30)
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788, '1/12/1983',1100, NULL, 20)
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698, '12/3/1981', 950, NULL, 30)
INSERT INTO EMP VALUES
(7902, 'FORD', 'ANALYST', 7566, '12/3/1981', 3000, NULL, 20)
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, '1/23/1982', 1300, NULL, 10)
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK')
INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS')
INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO')
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON')
commit transaction;查詢數據
select * from dept;
select * from emp;結果：
| DEPTNO | DNAME | LOC | |
|---|---|---|---|
| 1 | 10 | ACCOUNTING | NEW YORK |
| 2 | 20 | RESEARCH | DALLAS |
| 3 | 30 | SALES | CHICAGO |
| 4 | 40 | OPERATIONS | BOSTON |
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
|---|---|---|---|---|---|---|---|---|
| 1 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00.000 | 800.00 | NULL | 20 |
| 2 | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00.000 | 1600.00 | 300.00 | 30 |
| 3 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00.000 | 1250.00 | 500.00 | 30 |
| 4 | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00.000 | 2975.00 | NULL | 20 |
| 5 | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00.000 | 1250.00 | 1400.00 | 30 |
| 6 | 7698 | BLAKE | MANAGER | 7839 | 1981-06-01 00:00:00.000 | 2850.00 | NULL | 30 |
| 7 | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00.000 | 3260.95 | NULL | 10 |
| 8 | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00.000 | 3000 | NULL | 20 |
| 9 | 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00.000 | 6655.00 | NULL | 10 |
| 10 | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00.000 | 1500.00 | 0.00 | 30 |
| 11 | 7876 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00.000 | 1100 | NULL | 20 |
| 12 | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00.000 | 950.00 | NULL | 30 |
| 13 | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00.000 | 3000.00 | NULL | 20 |
| 14 | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00.000 | 1300.00 | NULL | 10 |
4.建立透視表T1，並插入數據
create table T1 （ID integer）
insert into T1 values(1);查詢數據
select ID from t1;結果：
| ID | |
|---|---|
| 1 | 1 |
5.建立透視表T10，並插入數據
CREATE TABLE T10 (ID INTEGER)
INSERT INTO T10 VALUES (1)
INSERT INTO T10 VALUES (2)
INSERT INTO T10 VALUES (3)
INSERT INTO T10 VALUES (4)
INSERT INTO T10 VALUES (5)
INSERT INTO T10 VALUES (6)
INSERT INTO T10 VALUES (7)
INSERT INTO T10 VALUES (8)
INSERT INTO T10 VALUES (9)
INSERT INTO T10 VALUES (10)查詢數據
select id from t10結果：
| ID | |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
