Junior students met weird question about database queries-CodePudding

 USE SPJ. 
The CREATE TABLE S (SNO CHAR (4) NOT NULL, 
SNAME CHAR (20) NOT NULL, 
The STATUS CHAR (10), 
CITY CHAR (20), 
PRIMARY KEY (SNO)); 
The CREATE TABLE P (PNO CHAR (4) NOT NULL, 
PNAME CHAR (20) NOT NULL, 
COLOR CHAR (8), 
WEIGHT SMALLINT, 
PRIMARY KEY (PNO)); 
The CREATE TABLE J (JNO CHAR (4) NOT NULL, 
JNAME CHAR (20), 
CITY CHAR (20), 
PRIMARY KEY (JNO)); 
The CREATE TABLE SPJ (SNO CHAR (4) NOT NULL, 
PNO CHAR (4) NOT NULL, 
JNO CHAR (4) NOT NULL, 
QTY SMALLINT, 
PRIMARY KEY (SNO, PNO, JNO), 
The CONSTRAINT ` spj_ibfk_1 ` FOREIGN KEY REFERENCES (SNO) S (SNO) ON DELETE CASCADE ON the UPDATE CASCADE, 
The CONSTRAINT ` spj_ibfk_2 ` FOREIGN KEY REFERENCES (PNO) P (PNO) ON DELETE CASCADE ON the UPDATE CASCADE, 
The CONSTRAINT ` spj_ibfk_3 ` FOREIGN KEY (JNO) REFERENCES J (JNO) ON DELETE CASCADE ON the UPDATE CASCADE);

The above was written when I was building a table code
The contents of the table below

The topic of the query is -- -- -- -- -- -- -- & gt;>> Find the J1 parts supply project for the suppliers of red number SNO

I query code is

 USE SPJ. 
SELECT DISTINCT SNO FROM SPJ 
WHERE JNO='J1 AND PNO IN 
(SELECT PNO 
The FROM P 
WHERE COLOR='red');

But not all things, but if I use the

 USE SPJ. 
SELECT DISTINCT * FROM SPJ 
WHERE JNO='J1 AND PNO IN (' P1 AND P4,' P6)

Can successfully query, request, instruction

CodePudding user response:

Are you the SQL server database?

CodePudding user response:

Check the database character set, whether GBK or utf-8, whether does not support Chinese query,

CodePudding user response:

COLOR is CHAR (8), 'red' only two, is there a problem?

CodePudding user response:

 SELECT PNO 
The FROM P 
WHERE COLOR='red'

Now you use this statement did check data,
If not, then look for other problems,
For example, you use the char of long, remove the space again

CodePudding user response:

String field as far as possible don't use char, varchar2 (20 char) instead

CodePudding user response:

reference 4 floor jdsnhan response:

 SELECT PNO 
The FROM P 
WHERE COLOR='red' 
Now you use this statement did check data,
If not, then look for other problems,
For example, you use the char of long, remove the Spaces to compare

Uh huh! I have tried, in the use of the back to P1, P4, P6 this three values, and in the same
P in the tableThen I'll use the

 SELECT SNO 
The FROM SPJ 
WHERE JNO='J1 AND PNO IN (' P1 AND P4,' P6)

The result is right, but only with my original not

CodePudding user response:

refer to the second floor qq646748739 response:

check the database character set, whether GBK or utf-8, whether does not support Chinese query,

Yeah yeah I am set utf-8 ~