MySQL—多表等级查询综合练习

本文介绍: INSERT INTO `emp` VALUES(‘7499′,’刘博’,’售货员’,’7698′,’1992-02-20′,’1900′,’300′,’30’);INSERT INTO `emp` VALUES(‘7521′,’李兴’,’售货员’,’7698′,’1995-07-22′,’1250′,’500′,’30’);INSERT INTO `emp` VALUES(‘7844′,’马琪’,’售货员’,’7698′,’1996-09-08′,’1500′,’0′,’30’);

创建emp表

CREATE TABLE emp(

empno INT(4) NOT NULL COMMENT ‘员工编号’,

ename VARCHAR(10) COMMENT ‘员工名字’,

job VARCHAR(10) COMMENT ‘职位’,

mgr INT(4) COMMENT ‘上司’,

hiredate DATE COMMENT ‘入职时间’,

sal INT(7) COMMENT ‘基本工资’,

comm INT(7) COMMENT ‘补贴’,

deptno INT(2) COMMENT ‘所属部门编号’

);

emp表添加主键

mysql> alter table emp add primary key(empno);

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

emp表添加外键约束

在MySQL中，可以使用alter table 语句来添加外键约束。以下是基本的语法：

ALTER TABLE table_name
ADD FOREIGN KEY (column_name) REFERENCES other_table(other_column);
在这个语句中：

table_name是你想要添加外键的表的名称。

column_name是你想要设置为外键的列的名称。

other_table是包含外键引用的表的名称。

other_column是在other_table中的列的名称。

ALTER TABLE emp ADD CONSTRAINT f_ed_key FOREIGN KEY (deptno) REFERENCES dept(deptno);

emp表中插入数据

INSERT INTO `emp` VALUES(‘7369′,’张倩’,’办事员’,’7902′,’2002-12-17′,’820′,NULL,’20’);

INSERT INTO `emp` VALUES(‘7499′,’刘博’,’售货员’,’7698′,’1992-02-20′,’1900′,’300′,’30’);

INSERT INTO `emp` VALUES(‘7521′,’李兴’,’售货员’,’7698′,’1995-07-22′,’1250′,’500′,’30’);

INSERT INTO `emp` VALUES(‘7566′,’李雷’,’人事部长’,’7839′,’1991-04-02′,’975′,NULL,’20’);

INSERT INTO `emp` VALUES(‘7654′,’刘浩’,’售货员’,’7698′,’1991-09-28′,’1250′,’1400′,’30’);

INSERT INTO `emp` VALUES(‘7698′,’刘涛’,’销售部长’,’7839′,’1997-05-01′,’2850′,NULL,’30’);

INSERT INTO `emp` VALUES(‘7782′,’华仔’,’人事部长’,’7839′,’1995-06-09′,’2450′,NULL,’10’);

INSERT INTO `emp` VALUES(‘7788′,’张飞’,’人事专员’,’7566′,’1998-04-19′,’3000′,NULL,’20’);

INSERT INTO `emp` VALUES(‘7839′,’马晓云’,’董事长’,NULL,’1991-11-17′,’5000′,NULL,’10’);

INSERT INTO `emp` VALUES(‘7844′,’马琪’,’售货员’,’7698′,’1996-09-08′,’1500′,’0′,’30’);

INSERT INTO `emp` VALUES(‘7876′,’李涵’,’办事员’,’7788′,’1997-05-23′,’1100′,NULL,’20’);

INSERT INTO `emp` VALUES(‘7900′,’李小涵’,’销售员’,’7698′,’1993-2-13′,’950′,NULL,’30’);

INSERT INTO `emp` VALUES(‘7902′,’张三’,’人事组长’,’7566′,’1992-10-08′,’3000′,NULL,’20’);

INSERT INTO `emp` VALUES(‘7934′,’张三丰’,’人事长’,’7782′,’1997-06-23′,’1300′,NULL,’10’);

查询emp表的内容

mysql> select * from emp;

创建salgrade表

CREATE TABLE salgrade(

grade INT (10) COMMENT ‘工资等级’,

losal INT (10) COMMENT ‘最低限额’,

hisal INT (10) COMMENT ‘最高限额’

);

salgrade表中插入数据

INSERT INTO salgrade (grade, losal, hisal)VALUES (1, 700, 1200);

INSERT INTO salgrade (grade, losal, hisal)VALUES (2, 1201, 1400);

INSERT INTO salgrade (grade, losal, hisal)VALUES (3, 1401, 2000);

INSERT INTO salgrade (grade, losal, hisal)VALUES (4, 2001, 3000);

INSERT INTO salgrade (grade, losal, hisal)VALUES (5, 3001, 9999);