如何用MySQL表达这个SQL语句:
SELECT s.Id
FROM student s
WHERE (
SELECT r.CrsCode
FROM transcript r
WHERE r.StudId=s.Id)
CONTAINS
(
SELECT c.CrsCode
FROM course c
WHERE c.CrsCode LIKE 'CS%')
)
)
请高手指教,小弟先谢过~
SELECT s.Id
FROM student s
WHERE (
SELECT r.CrsCode
FROM transcript r
WHERE r.StudId=s.Id)
CONTAINS
(
SELECT c.CrsCode
FROM course c
WHERE c.CrsCode LIKE 'CS%')
)
)
请高手指教,小弟先谢过~
事实上SQL没有这种CONTAINS,但是可以做如下的表达:
SELECT s.Id
FROM student s
WHERE NOT EXISTS (
(
SELECT c.CrsCode
FROM course c
WHERE c.CrsCode LIKE 'CS%')
EXCEPT
(
SELECT r.CrsCode
FROM transcript r
WHERE r.StudId=s.Id)
)
可是MySQL却不支持如上的语法。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE ROOT SYSTEM "1.dtd">
<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Id">23456789</field>
<field name="Name">Homer Simpson</field>
<field name="Address">Fox 5 TV</field>
<field name="Status">Senior</field>
</row>
<row>
<field name="Id">111111111</field>
<field name="Name">John Doe</field>
<field name="Address">123 Main St.</field>
<field name="Status">Freshman</field>
</row>
<row>
<field name="Id">111223344</field>
<field name="Name">Mary Smith</field>
<field name="Address">1 Lake St.</field>
<field name="Status">Freshman</field>
</row>
<row>
<field name="Id">123121235</field>
<field name="Name">Jane Doe</field>
<field name="Address" xsi:nil="true" />
<field name="Status">Freshman</field>
</row>
<row>
<field name="Id">123121236</field>
<field name="Name">Jim Doe</field>
<field name="Address" xsi:nil="true" />
<field name="Status">Freshman</field>
</row>
<row>
<field name="Id">123454321</field>
<field name="Name">Joe Blow</field>
<field name="Address">6 Yard Ct.</field>
<field name="Status">Junior</field>
</row>
<row>
<field name="Id">666666666</field>
<field name="Name">Joseph Public</field>
<field name="Address">666 Hollow Rd.</field>
<field name="Status">Sophomore</field>
</row>
<row>
<field name="Id">987654321</field>
<field name="Name">Bart Simpson</field>
<field name="Address">Fox 5 TV</field>
<field name="Status">Senior</field>
</row>
</ROOT>表course:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE ROOT SYSTEM "2.dtd">
<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="CrsCode">CS305</field>
<field name="DeptId">CS</field>
<field name="CrsName">Database Systems</field>
<field name="Descr">On the road to high-paying job</field>
</row>
<row>
<field name="CrsCode">EE101</field>
<field name="DeptId">EE</field>
<field name="CrsName">Electronic Circuits</field>
<field name="Descr">Build your own computer</field>
</row>
<row>
<field name="CrsCode">MAT123</field>
<field name="DeptId">MAT</field>
<field name="CrsName">Algebra</field>
<field name="Descr">The world where 2*2!=4</field>
</row>
<row>
<field name="CrsCode">MGT123</field>
<field name="DeptId">MGT</field>
<field name="CrsName">Market Analysis</field>
<field name="Descr">Get rich quick</field>
</row>
</ROOT>表transcript:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE ROOT SYSTEM "3.dtd">
<ROOT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="StudId">666666666</field>
<field name="CrsCode">MGT123</field>
<field name="Semester">F1994</field>
<field name="Grade">A</field>
</row>
<row>
<field name="StudId">666666666</field>
<field name="CrsCode">EE101</field>
<field name="Semester">S1991</field>
<field name="Grade">B</field>
</row>
<row>
<field name="StudId">666666666</field>
<field name="CrsCode">MAT123</field>
<field name="Semester">F1997</field>
<field name="Grade">B</field>
</row>
<row>
<field name="StudId">987654321</field>
<field name="CrsCode">CS305</field>
<field name="Semester">F1995</field>
<field name="Grade">C</field>
</row>
<row>
<field name="StudId">987654321</field>
<field name="CrsCode">MGT123</field>
<field name="Semester">F1994</field>
<field name="Grade">B</field>
</row>
<row>
<field name="StudId">123454321</field>
<field name="CrsCode">CS315</field>
<field name="Semester">S1997</field>
<field name="Grade">A</field>
</row>
<row>
<field name="StudId">123454321</field>
<field name="CrsCode">CS305</field>
<field name="Semester">S1996</field>
<field name="Grade">A</field>
</row>
<row>
<field name="StudId">123454321</field>
<field name="CrsCode">MAT123</field>
<field name="Semester">S1996</field>
<field name="Grade">C</field>
</row>
<row>
<field name="StudId">23456789</field>
<field name="CrsCode">EE101</field>
<field name="Semester">F1995</field>
<field name="Grade">B</field>
</row>
<row>
<field name="StudId">23456789</field>
<field name="CrsCode">CS305</field>
<field name="Semester">S1996</field>
<field name="Grade">A</field>
</row>
<row>
<field name="StudId">111111111</field>
<field name="CrsCode">EE101</field>
<field name="Semester">F1997</field>
<field name="Grade">A</field>
</row>
<row>
<field name="StudId">111111111</field>
<field name="CrsCode">MAT123</field>
<field name="Semester">F1997</field>
<field name="Grade">B</field>
</row>
<row>
<field name="StudId">111111111</field>
<field name="CrsCode">MGT123</field>
<field name="Semester">F1997</field>
<field name="Grade">B</field>
</row>
</ROOT>
预期结果可以根据表内容推断出。
请不吝赐教!
USE registration;DROP TABLE IF EXISTS Student;
CREATE TABLE Student ( Id INTEGER,
Name CHAR(20) NOT NULL,
Address CHAR(50),
Status CHAR(10) DEFAULT 'Freshman',
PRIMARY KEY (Id) );
DROP TABLE IF EXISTS Professor;
CREATE TABLE Professor ( Id INTEGER,
Name CHAR(20) NOT NULL,
DeptId CHAR(4),
PRIMARY KEY (Id) );
DROP TABLE IF EXISTS Course;
CREATE TABLE Course ( CrsCode CHAR(6),
DeptId CHAR(4),
CrsName CHAR(20),
Descr CHAR(100),
PRIMARY KEY (CrsCode),
UNIQUE (DeptId, CrsName) );DROP TABLE IF EXISTS Transcript;
CREATE TABLE Transcript ( StudId INTEGER,
CrsCode CHAR(6),
Semester CHAR(6),
Grade CHAR(1),
PRIMARY KEY (StudId, CrsCode, Semester)
);
DROP TABLE IF EXISTS Teaching;
CREATE TABLE Teaching ( ProfId INTEGER,
CrsCode CHAR(6),
Semester CHAR(6),
PRIMARY KEY (CrsCode, Semester)
);
DROP VIEW IF EXISTS ProfStud;
CREATE VIEW ProfStud AS
SELECT Teaching.ProfId, Transcript.StudId
FROM Transcript, Teaching
WHERE Transcript.CrsCode = Teaching.CrsCode
AND Transcript.Semester = Teaching.Semester;
INSERT INTO Student( Id, Name, Address, Status )
VALUES( 111111111, 'John Doe', '123 Main St.', 'Freshman' );
INSERT INTO Student( Id, Name, Address, Status )
VALUES( 666666666, 'Joseph Public', '666 Hollow Rd.', 'Sophomore' );
INSERT INTO Student( Id, Name, Address, Status )
VALUES( 111223344, 'Mary Smith', '1 Lake St.', 'Freshman' );
INSERT INTO Student( Id, Name, Address, Status )
VALUES( 987654321, 'Bart Simpson', 'Fox 5 TV', 'Senior' );
INSERT INTO Student( Id, Name, Address, Status )
VALUES( 023456789, 'Homer Simpson', 'Fox 5 TV', 'Senior' );
INSERT INTO Student( Id, Name, Address, Status )
VALUES( 123454321, 'Joe Blow', '6 Yard Ct.', 'Junior' ); INSERT INTO Student( Id, Name, Address )
VALUES( 123121235, 'Jane Doe', NULL );
INSERT INTO Student( Id, Status, Name, Address )
VALUES( 123121236, DEFAULT, 'Jim Doe', NULL );SELECT * FROM Student;
INSERT INTO Professor( Id, Name, DeptId )
VALUES( 101202303, 'John Smyth', 'CS' );
INSERT INTO Professor( Id, Name, DeptId )
VALUES( 783432188, 'Adrian Jones', 'MGT' );
INSERT INTO Professor( Id, Name, DeptId )
VALUES( 121232343, 'David Jones', 'EE' );
INSERT INTO Professor( Id, Name, DeptId )
VALUES( 864297531, 'Qi Chen', 'MAT' );
INSERT INTO Professor( Id, Name, DeptId )
VALUES( 555666777, 'Mary Doe', 'CS' );
INSERT INTO Professor( Id, Name, DeptId )
VALUES( 009406321, 'Jacob Taylor', 'MGT' );
INSERT INTO Professor( Id, Name, DeptId )
VALUES( 900120450, 'Ann White', 'MAT' );
SELECT * FROM Professor;
INSERT INTO Course( CrsCode, DeptId, CrsName, Descr )
VALUES( 'CS305', 'CS', 'Database Systems', 'On the road to high-paying job' );
INSERT INTO Course( CrsCode, DeptId, CrsName, Descr )
Values( 'MGT123', 'MGT', 'Market Analysis', 'Get rich quick' );
INSERT INTO Course( CrsCode, DeptId, CrsName, Descr )
Values( 'EE101', 'EE', 'Electronic Circuits', 'Build your own computer' );
INSERT INTO Course( CrsCode, DeptId, CrsName, Descr )
Values( 'MAT123', 'MAT', 'Algebra', 'The world where 2*2!=4' );
SELECT * FROM Course;
INSERT INTO Transcript( StudId, CrsCode, Semester, Grade )
VALUES( '666666666', 'MGT123', 'F1994', 'A' );
INSERT INTO Transcript( StudId, CrsCode, Semester, Grade )
VALUES( '666666666', 'EE101', 'S1991', 'B' );
INSERT INTO Transcript( StudId, CrsCode, Semester, Grade )
VALUES( '666666666', 'MAT123', 'F1997', 'B' );
INSERT INTO Transcript( StudId, CrsCode, Semester, Grade )
VALUES( '987654321', 'CS305', 'F1995', 'C' );
INSERT INTO Transcript( StudId, CrsCode, Semester, Grade )
VALUES( '987654321', 'MGT123', 'F1994', 'B' );
INSERT INTO Transcript( StudId, CrsCode, Semester, Grade )
VALUES( '123454321', 'CS315', 'S1997', 'A' );
INSERT INTO Transcript( StudId, CrsCode, Semester, Grade )
VALUES( '123454321', 'CS305', 'S1996', 'A' );
INSERT INTO Transcript( StudId, CrsCode, Semester, Grade )
VALUES( '123454321', 'MAT123', 'S1996', 'C' );
INSERT INTO Transcript( StudId, CrsCode, Semester, Grade )
VALUES( '023456789', 'EE101', 'F1995', 'B' );
INSERT INTO Transcript( StudId, CrsCode, Semester, Grade )
VALUES( '023456789', 'CS305', 'S1996', 'A' );
INSERT INTO Transcript( StudId, CrsCode, Semester, Grade )
VALUES( '111111111', 'EE101', 'F1997', 'A' );
INSERT INTO Transcript( StudId, CrsCode, Semester, Grade )
VALUES( '111111111', 'MAT123', 'F1997', 'B' );
INSERT INTO Transcript( StudId, CrsCode, Semester, Grade )
VALUES( '111111111', 'MGT123', 'F1997', 'B' );
SELECT * FROM Transcript; INSERT INTO Teaching( ProfId, CrsCode, Semester )
VALUES( '009406321', 'MGT123', 'F1994' );
INSERT INTO Teaching( ProfId, CrsCode, Semester )
VALUES( '121232343', 'EE101', 'S1991' );
INSERT INTO Teaching( ProfId, CrsCode, Semester )
VALUES( '555666777', 'CS305', 'F1995' );
INSERT INTO Teaching( ProfId, CrsCode, Semester )
VALUES( '101202303', 'CS315', 'S1997' );
INSERT INTO Teaching( ProfId, CrsCode, Semester )
VALUES( '900120450', 'MAT123', 'S1996' );
INSERT INTO Teaching( ProfId, CrsCode, Semester )
VALUES( '121232343', 'EE101', 'F1995' );
INSERT INTO Teaching( ProfId, CrsCode, Semester )
VALUES( '101202303', 'CS305', 'S1996' );
INSERT INTO Teaching( ProfId, CrsCode, Semester )
VALUES( '900120450', 'MAT123', 'F1997' );
INSERT INTO Teaching( ProfId, CrsCode, Semester )
VALUES( '783432188', 'MGT123', 'F1997' );SELECT * FROM Teaching;
SELECT * FROM ProfStud;