欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

《SQL CookBook 》笔记-准备工作

程序员文章站 2022-06-30 13:47:43
[TOC] 准备 1.建立员工表——EMP 字段说明: |EMP(员工表)字段|说明| | | | EMPNO|工号 ENAME|姓名 JOB|工种 MGR|上级编号 HIREDATE|雇佣日期 SAL|工资 COMM|奖金 DEPTNO|部门编号 2.建立部门表——DEPT 字段说明: DEPT( ......

准备

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