韩顺平SqlServer数据库实例中的两张表

-- 创建数据库；
create database spdb1;
--切换数据库；
use spdb1;
--创建部门表；
create table dept (

deptno int primary key,

dname nvarchar(30),

loc nvarchar(30)
);
-- 插入部门数据；
insert into dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK');

insert into dept (deptno, dname, loc) values (20, 'RESEARCH', 'DALLAS');

insert into dept (deptno, dname, loc) values (30, 'SALES', 'CHICAGO');

insert into dept (deptno, dname, loc) values (40, 'OPERATIONS', 'BOSTON')

-- 创建雇员表；
create table emp (

empno int primary key,

ename nvarchar(30),

job nvarchar(30),

mgr int,

hiredate datetime,

sal numeric(9,2),

comm numeric(9,2),

deptno int foreign key references dept(deptno))

--因为deptno根据需要得做成外键

insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,20);

insert into emp values (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);

insert into emp values (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500.00,30);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7566,'JONES','MANAGER',7839,'1981-4-2',2975,null,20);

insert into emp values (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-4-19', 3000, NULL, 20);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);



select * from emp

select * from dept