《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 |
上一篇: 吃一个蛋糕就吃了一次满汉全席!
下一篇: 搜狗在清华大学建立人工智能研究院