mysql - 即使插入列， 'WHERE' 子句中的 SQL 未知列

Question

我确保使用单引号而不是双引号，但由于某种原因，它说性别列未知。

我尝试将“AND”语句更改为小写，尝试使用各种引号，并且还修复了表内容和值中设置值的顺序。

CREATE DATABASE MYSQLEXERCISE5;
USE MYSQLEXERCISE5;
CREATE TABLE myemployees (
gender VARCHAR(255),
first_name VARCHAR(255),
last_name VARCHAR(255),
birth_place VARCHAR(255),
citizen_ship VARCHAR(255),
yearly_wage INT(7) NOT NULL,
age INT(3) NOT NULL,
hours_worked INT(5) NOT NULL,
years_employed INT(6) NOT NULL
);

INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'Filip', 'Lazarevic', 'Montenegro', 'Australia', 30000, 19, 8000, 4);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'Daniel', 'Craig', 'USA', 'England', 75000, 37, 26000, 13);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'John', 'Jovovich', 'Serbia', 'USA', 55000, 27, 8000, 4);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'Stefan', 'Stojic', 'Croatia', 'Croatia', 'Spain', 100000, 24, 12000, 6);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'David', 'Longbottom', 'Ireland', 'UK', 67000, 43, 10000, 5);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'Riley', 'Reynolds', 'Canada', 'Canada', 90000, 30, 20000, 10);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('F', 'Riley', 'Raymond', 'Iceland', 'Greenland', 22000, 19, 6000, 3);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'Jade', 'Michaels', 'Egypt', 'France', 100000, 55, 30000, 15);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('F', 'Jade', 'Smith', 'Syria', 'India', 10000, 50, 10000, 5);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'Sam', 'Wilson', 'Scotland', 'Ireland', 34000, 25, 4000, 2);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('F', 'Sam', 'Wade', 'Russia', 'Finland', 55000, 32, 8000, 4);

SELECT * FROM myemployees WHERE first_name = 'Sam' AND gender = 'M';
SELECT * FROM myemployees WHERE first_name = 'Jade' AND gender = 'F';

我希望它显示性别为 M 的叫 Sam 的人以及性别为 F 的叫 Jade 的人

Answer 1

您可以尝试使用或

架构 (MySQL v5.7)

CREATE TABLE myemployees (
gender VARCHAR(255),
first_name VARCHAR(255),
last_name VARCHAR(255),
birth_place VARCHAR(255),
citizen_ship VARCHAR(255),
yearly_wage INT(7) NOT NULL,
age INT(3) NOT NULL,
hours_worked INT(5) NOT NULL,
years_employed INT(6) NOT NULL
);

INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'Filip', 'Lazarevic', 'Montenegro', 'Australia', 30000, 19, 8000, 4);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'Daniel', 'Craig', 'USA', 'England', 75000, 37, 26000, 13);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'John', 'Jovovich', 'Serbia', 'USA', 55000, 27, 8000, 4);

INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'David', 'Longbottom', 'Ireland', 'UK', 67000, 43, 10000, 5);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'Riley', 'Reynolds', 'Canada', 'Canada', 90000, 30, 20000, 10);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('F', 'Riley', 'Raymond', 'Iceland', 'Greenland', 22000, 19, 6000, 3);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'Jade', 'Michaels', 'Egypt', 'France', 100000, 55, 30000, 15);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('F', 'Jade', 'Smith', 'Syria', 'India', 10000, 50, 10000, 5);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('M', 'Sam', 'Wilson', 'Scotland', 'Ireland', 34000, 25, 4000, 2);
INSERT INTO myemployees (gender, first_name, last_name, birth_place, citizen_ship, yearly_wage, age, hours_worked, years_employed)
VALUES ('F', 'Sam', 'Wade', 'Russia', 'Finland', 55000, 32, 8000, 4);

<小时/>

查询#1

SELECT * 
FROM myemployees 
WHERE (first_name = 'Sam' AND gender = 'M') or (first_name = 'Jade' AND gender = 'F');

| gender | first_name | last_name | birth_place | citizen_ship | yearly_wage | age | hours_worked | years_employed |
| ------ | ---------- | --------- | ----------- | ------------ | ----------- | --- | ------------ | -------------- |
| F      | Jade       | Smith     | Syria       | India        | 10000       | 50  | 10000        | 5              |
| M      | Sam        | Wilson    | Scotland    | Ireland      | 34000       | 25  | 4000         | 2              |

<小时/>

View on DB Fiddle