《SQL CookBook 》笔记-准备工作
2024-08-29 10:53:24
第二章
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 |
最新文章
- Jenkins的一个bug-同时build一个项目两次导致失败
- 伟大的GCD和NSOperationQueue
- nexus搭建maven私服
- 在Chrome Console中加载jQuery
- 大数据系列修炼-Scala课程11
- PHPexcel数据导出
- 面向Unity程序员的Android快速上手教程
- Markdown语法你都会了吗?
- 一些日期的计算方式 PHP
- lr_java user协议脚本开发
- QT之uic、moc、rcc命令生成相应的cpp文件
- oracle数据库实例启动与关闭
- Gym101194J Mr.Panda and TubeMaster 二分图、费用流
- Android应用内嵌cocos2dx游戏项目
- RNA_seq GATK 最佳实践
- repo_folder
- c语言中strcpy与strlen函数对字符串最后的'\0'的处理
- linux如何删除行首的空格
- Sprint计划表
- myEclipse修改字体大小