数据库:如何创建更新级联表和查询数据？

Question

我想要创建一个有两个表的数据库。我想在两个表之间创建一个更新级联关系。如果SNAME在EMPLOYEE中被更改，那么WORKREPORT也将更改他的名称。

EMPLOYEE:
SNO      SNAME           SPASSWORD     SEX      BDATE           HEIGHT    BTITLE
2014      boss            12345        male      1987-06-02       180       Manager
2015      Tom             1234567      male      1987-06-05       180       Employee


WORKREPORT
   SNO       SNAME             SDATA         SCHECKLIST       SIMAGE
   2014      boss            1987-06-02      abc               afafafaf
   2015      Tom             1987-06-05      affafa            afafafaf

我的代码运行得很好。我的问题是:假设有很多员工，我不知道如何从"WORKREPORT“查询员工的信息(而不是经理)。我该怎么办？关于"WORKREPORT“的设计，我说得对吗？这是我的密码：

CREATE database  if not exists cm360_cm360_1;
use cm360_cm360_1;
CREATE TABLE IF NOT EXISTS EMPLOYEE(SNO VARCHAR(7) NOT NULL,  SNAME VARCHAR(8) NOT NULL,  SPASSWORD VARCHAR(11) NOT NULL,SEX VARCHAR(8) NOT NULL,  BDATE DATETIME NOT NULL,  HEIGHT DEC(5,2) DEFAULT 000.00,BTitle VARCHAR(15) NOT NULL,  PRIMARY KEY(SNO), UNIQUE KEY (SNAME))ENGINE=InnoDB ;

SET SQL_SAFE_UPDATES=0;
INSERT INTO EMPLOYEE VALUES (2014,'boss','12345','male','2014-6-10 11:00:00',160.00,'Manager');
INSERT INTO EMPLOYEE VALUES (2015,'Tom','1234567','male','2014-6-10 12:00:00',160.00,'Employee');
SELECT * FROM EMPLOYEE;


CREATE TABLE IF NOT EXISTS WORKREPORT(SNO VARCHAR(7) NOT NULL,  SNAME VARCHAR(8) NOT NULL,SDATA DATETIME ,  SCHECKLIST VARCHAR(150),SIMAGE VARCHAR(20),FOREIGN KEY (SNO) REFERENCES EMPLOYEE (SNO) ON UPDATE CASCADE,FOREIGN KEY(SNAME) REFERENCES EMPLOYEE (SNAME) ON UPDATE CASCADE ) ENGINE=InnoDB;


INSERT INTO WORKREPORT VALUES (2014,'boss',' 2014-6-10 14:38:59','abc','afdsfdfds');
INSERT INTO WORKREPORT VALUES (2015,'Tom',' 2014-6-10 15:38:59','abc','afdsfdfds');
SELECT * FROM  WORKREPORT order by SDATA ASC;

UPDATE WORKREPORT SET SCHECKLIST='elevator;floor' WHERE SNAME='hanlu2';
delete from workreport WHERE  SIMAGE='N/A' AND SNAME='enlan';

Answer 1

如果您想获得员工的WORKREPORT信息，下面的查询应该可以工作。

SELECT WORKREPORT.* 
FROM WORKREPORT, EMPLOYEE 
WHERE EMPLOYEE.SNO = WORKREPORT.SNO 
AND EMPLOYEE.BTITLE = "Employee"

Answer 2

如果您只想获取所有员工信息，可以在JOIN和/或SNAME上对两个表进行筛选，因为在这些列上有FK关系，并根据BTITLE不应该是MANAGER的条件进行筛选

select e.*
from employee e
join workreport w on e.SNO = w.SNO
and e.SNAME = w.SNAME
and e.BTITLE <> 'Manager'