数据库创建emp和dept练习表

来源：互联网发布：女流和陈一发知乎编辑：程序博客网时间：2024/05/16 04:59

--新数据库
create database name1

--创建dept表
use name1

create table dept
(
deptno int primary key,
dname varchar(30),
loc varchar(30),
);

--创建emp表

create table emp
(empno int primary key,
ename varchar(30),
job varchar(30),
mgr int,
hiredate datetime,
sal numeric(10,2),
comm numeric(10,2),
deptno int foreign key references dept(deptno) --因为deptno我们根据需要做成外键

)

--针对外键,请注意
--①外键只能指向主键
--②外键和主键的数据类型要一致

--首先建立dept表,因为dept表与emp表存在约束

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')

--建立emp表
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.00,300.00,30)

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

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7566,'JONES','MANAGER',7839,'1981-4-2',2975.00,20)

insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250.00,1400.00,30)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850.00,30)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7782,'CLARK','MANAGER',7839,'1981-6-9',2450.00,10)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7788,'SCOTT','ANALYST',7566,'1987--4-19',3000.00,20)

insert into emp(EMPNO,ENAME,JOB,HIREDATE,SAL,DEPTNO) values(7839,'KING','PRESIDENT','1981-11-17',5000.00,10)

insert into emp values(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500.00,0.00,30)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7876,'ADAMS','CLERK',7788,'1987-5-23',1100.00,20)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7900,'JAMES','CLERK',7698,'1981-12-3',950,30)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7902,'FORD','ANALYST',7566,'1981-12-3',3000,20)

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7934,'MILLER','CLERK',7782,'1982-1-23',1300,10)

insert into emp values(9999,'SHUNPING','CLERK',7782,'1988-5-5',2456.34,55.66,10)

SELECT * FROM EMP

SELECT * FROM DEPT

1 0