SHOW DATABASES;

CREATE DATABASE IF NOT EXISTS xc2023;

USE xc2023;

ALTER DATABASE xc2023 CHARACTER SET UTF8;

SHOW TABLES;

CREATE TABLE`Student_table`

(

sid INT PRIMARY KEY,

sname VARCHAR(10) NOT NULL,

sage INT NOT NULL,

ssex CHAR(2) NOT NULL

)ENGINE=MyISAM DEFAULT CHARSET=UTF8;

CREATE TABLE `Teacher_table`

(

tid INT PRIMARY KEY,

tname VARCHAR(10) NOT NULL

)ENGINE=MyISAM DEFAULT CHARSET=UTF8;

CREATE TABLE`Course_table`

(

cid INT PRIMARY KEY,

cname VARCHAR(10) NOT NULL,

tid INT NOT NULL,

CONSTRAINT fk FOREIGN KEY (tid) REFERENCES Teacher_table(tid)

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

CREATE TABLE`Score_table`

(

sid INT NOT NULL,

cid INT NOT NULL,

score INT NOT NULL,

CONSTRAINT fk1 FOREIGN KEY (sid) REFERENCES Student_table(sid),

CONSTRAINT fk2 FOREIGN KEY (cid) REFERENCES Course_table(cid)

)ENGINE=MYISAM DEFAULT CHARSET=utf8;

INSERT INTO `score_table` VALUES (1001,2001,89),(1003,2001,30),(1004,2001,78),(1005,2001,68),(1006,2001,93),(1007,2001,62),(1001,2002,67),(1002,2002,86),(1003,2002,67),(1004,2002,77),(1005,2002,66),(1006,2002,84),(1007,2002,72),(1001,2003,82),(1002,2003,85),(1003,2003,32),(1004,2003,73),(1005,2003,64),(1006,2003,87),(1007,2003,77),(1008,2003,94),(1001,2004,39),(1002,2004,80),(1003,2004,80),(1004,2004,88),(1005,2004,68),(1006,2004,59),(1007,2004,42),(1008,2004,64),(1001,2005,89),(1002,2005,70),(1003,2005,60),(1004,2005,58),(1005,2005,38),(1006,2005,89),(1007,2005,72),(1008,2005,64),(1001,2006,49),(1002,2006,90),(1003,2006,70),(1004,2006,48),(1005,2006,98),(1006,2006,59),(1007,2006,72),(1008,2006,74),(1001,2007,49),(1002,2007,50),(1003,2007,70),(1004,2007,88),(1005,2007,78),(1006,2007,99),(1007,2007,82);

INSERT INTO `Student_table` VALUES (1001,'張三',10,'男'),

(1002,'李四',11,'男'),

(1003,'王五',12,'男');

INSERT INTO `teacher_table` VALUES(1,'李白'),

(2,'杜甫'),

(3,'白居易');

INSERT INTO `course_table` VALUES (2001,'PHP',1),

(2002,'C#',1),

(2003,'c++',2),

(2004,'JAVA',3);

INSERT INTO course_table VALUES (2008,'英語',1);

UPDATE teacher_table SET tname = '白居易' WHERE tid = 3;

SELECT tname AS '教師姓名' FROM teacher_table;

SELECT tname AS '課程名稱' FROM course_table;

DELETE FROM course_table WHERE cid = 2008;

SELECT b.cname AS '課程名稱',a.score AS '成績'

FROM score_table AS a,course_table AS b

WHERE a.cid = b.cid AND a.sid = (

SELECT sid

FROM student_table

WHERE sname = '馬六');

SELECT 字段 FROM <表名> [條件/限制];

SELECT sid,score FROM score_table

WHERE cid = (

SELECT cid FROM course_table

WHERE cname = 'PHP') AND score < 60;

SELECT cid FROM course_table WHERE cname = 'PHP';

SELECT sname

FROM student_table

WHERE sid IN(

SELECT sid

FROM score_table

WHERE cid = (

SELECT cid

FROM course_table

WHERE cname = 'PHP') AND score >= 60);

SELECT score FROM score_table WHERE cid = 2001;

SELECT score FROM score_table WHERE cid IN （2001，2002，2003）；

INSE INTO student_table(sid,sname,ssex) VALUES(1009,'羅翔'，'男');

SELECT * FROM student_table WHERE NOT(sname IS NULL);