mysql - MySQL Workbench 中的 View 始终返回 (0) 行

Question

drop database if exists RentaHouse;
create database RentaHouse;

use RentaHouse;

create table Staff(
staffNo char(5) not null primary key,
fName varchar(15),
lName varchar(15),
position varchar(15),
dob date,
salary decimal (7,2) unsigned
);

create table PropertyForRent(
propertyNo char(5) primary key,
street varchar(35) not null,
city varchar(15) not null,
pcode varchar(10),
type varchar(20) not null,
rooms tinyint unsigned not null,
rent decimal (6,2) unsigned,
staffNo char(5)
);

ALTER TABLE PropertyForRent
    ADD CONSTRAINT whatever foreign key(staffNo) references Staff (staffNo) ON DELETE CASCADE ON UPDATE CASCADE;


insert into staff values
('s1234','Mary','Jones', 'Sales', '1975-12-22',45000),
('s1834','Pat','Roche', 'IT', '1972-09-13',42000),
('s1998','Michael','Brown', 'Sales', '1980-12-09',43500);

insert into propertyForRent values
('p3296','21 Ash Street','Tramore','WD34-543', 'Bungalow',4,1200,'s1234'),
('p3299','William Street','Dungarvan','WD99-088', 'Terrace',3,1050,'s1234'),
('p3344','9 Mary Street','New Ross','WX99-044', 'Terrace',3,800,'s1998'),
('p3356','21 Mary Street','New Ross','WX99-076', '2 Storey',4,1100,null);



/*doesn't work!*/
CREATE VIEW anyView AS 
    select * from Staff;

/*work!*/
select * from Staff;

/*work!*/
select street, city, type, rent, concat(fName, lName) as 'Name' from 
PropertyForRent join Staff on PropertyForRent.staffNo = Staff.staffNo  where city = 'New Ross' order by rent;

/*doesn't work!*/
CREATE VIEW myView AS 
select street, city, type, rent, concat(fName, lName) as 'Name' from 
PropertyForRent join Staff on PropertyForRent.staffNo = Staff.staffNo  where city = 'New Ross' order by rent;

所有 View 都没有返回任何结果!我使用 MySQL Workbench 6.3.8 使用“sakila”作为默认架构。

我已经在网上搜索了 2 天的解决方案，但我认为是时候问问谁有专业知识了。

P.S View 不适用于我不仅为此架构创建的任何数据库!

Answer 1

CREATE VIEW anyView1 AS  select * from Staff;

then fetch data
select * from anyView1;


CREATE VIEW myViewss AS 
select street, city, type, rent, concat(fName, lName) as 'Name' from 
PropertyForRent join Staff on PropertyForRent.staffNo = Staff.staffNo            where city = 'New Ross' order by rent;


 select * from myViewss;