《SQL CookBook 》笔记-准备工作

第二章
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;

结果:

DEPTNODNAMELOC
110ACCOUNTINGNEW YORK
220RESEARCHDALLAS
330SALESCHICAGO
440OPERATIONSBOSTON


EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
17369SMITHCLERK79021980-12-17 00:00:00.000800.00NULL20
27499ALLENSALESMAN76981981-02-20 00:00:00.0001600.00300.0030
37521WARDSALESMAN76981981-02-22 00:00:00.0001250.00500.0030
47566JONESMANAGER78391981-04-02 00:00:00.0002975.00NULL20
57654MARTINSALESMAN76981981-09-28 00:00:00.0001250.001400.0030
67698BLAKEMANAGER78391981-06-01 00:00:00.0002850.00NULL30
77782CLARKMANAGER78391981-06-09 00:00:00.0003260.95NULL10
87788SCOTTANALYST75661982-12-09 00:00:00.0003000NULL20
97839KINGPRESIDENTNULL1981-11-17 00:00:00.0006655.00NULL10
107844TURNERSALESMAN76981981-09-08 00:00:00.0001500.000.0030
117876ADAMSCLERK77881983-01-12 00:00:00.0001100NULL20
127900JAMESCLERK76981981-12-03 00:00:00.000950.00NULL30
137902FORDANALYST75661981-12-03 00:00:00.0003000.00NULL20
147934MILLERCLERK77821982-01-23 00:00:00.0001300.00NULL10


4.建立透视表T1,并插入数据

create table T1 (ID integer)

insert into T1 values(1);

查询数据

select ID from t1;

结果:

ID
11


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
11
22
33
44
55
66
77
88
99
1010


转载于:https://www.cnblogs.com/shanzhiming/p/10352818.html

  • 0
    点赞
  • 0
    收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值