MYSQL 寒假自学 2022 十一（九）_test1数据资料-CSDN博客

本文链接：https://blog.csdn.net/qq_51415695/article/details/122916757

多表查询练习的代码准备工作

-- 创建test1数据库
create database test1;
-- 选择使用test1数据库
use test1;
-- 创建部门表
create table dept(
	deptno int primary key comment '部门编号',
  dname varchar(14) comment '部门名称',
  loc varchar(13) comment '部门地址'
);

insert into dept values(10,'accounting','new york'),
(20,'research','dallas'),
(30,'sales','chicago'),
(40,'operations','boston');

-- 创建员工表
create table emp(
		empno int primary key comment '员工编号',
    ename varchar(10) comment '员工名',
    job varchar(9) comment '员工工作',
    mgr int comment '员工直属领导编号',
    hiredate date comment '入职时间',
    sal double comment '工资',
    comm double comment '奖金',
    deptno int comment '对应dept表的外键'
);
-- 添加部门和员工之间的外键关系
alter table emp add constraint foreign key emp(deptno) references dept (deptno);

-- 创建工资等级表
create table salgrade(
		grand int comment '等级',
    losal double comment '最低工资',
    hisal double comment '最高工资'
);
insert into salgrade values (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

insert into emp values
(7369,'smith','clerk' 7902,'1980-12-17',800,null,20),
(7499,'allen','salesman',7698,'1981-02-20',1600,300,30),
(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),
(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);