mysql42道练习题

创建dept表

create table dept( -- 部门表
deptno int(2) not null, -- 部门编号
dname varchar(14), -- 部门名称
loc varchar(13), -- 部门地址
constraint pk_dept primary key(depton)
)engine=innodb default charset=utf8;

添加dept数据

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

alter table dept rename column depton to deptno;
select * from dept;

创建emp表

create table emp( -- 员工表
empno int(4) not null primary key, -- 员工编号
ename varchar(10), -- 员工姓名
job varchar(9), -- 员工职位
mgr int(4), -- 上级编号
hiredate date, -- 入职时间
sal float(7,2), -- 月收入
comm float(7,2), -- 奖金
deptno int(2), -- 部门编号
constraint fk_depton foreign key(depton) references dept(depton)
)engine=innodb default charset=utf8;

添加emp数据

insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'MARTIN','SALESMAN',7