CREATE DATAbase IF NOT EXISTS jcgf DEFAULT CHARSET utf8;
USE jcgf ;
#建学生表
CREATE TABLE IF NOT EXISTS stu
( NO INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAr(20)
);
INSERT INTO stu(NAME)VALUES("蔡彤"),("符超"),("建波"),("世民"),("霸天");
#建科目表
CREATE TABLE IF NOT EXISTS sub
(
subno INT PRIMARY KEY AUTO_INCREMENT,
subname VARCHAr(20)
);
INSERT INTO sub(subname)VALUES("java"),("c"),("python"),("mysql");
#建成绩表（中间表）
CREATE TABLE IF NOT EXISTS score
(
NO INT,
score INT,
subno INT
);
INSERT INTO score(NO,score,subno)VALUES(1,99,1),
(2,66,2),
(3,98,3),
(4,79,4);
#增加 宇轩
INSERT INTO stu(NAME)
VALUES("盛旺");
#删除 霸天
DELETe FROM stu WHERe NAME="霸天";
SELECt * FROM stu
#改
UPDATe stu SET NAME="野狗" WHERe NO="1";
SELECT * FROM stu
#三表关联
SELECt st.*,sc.*FROM stu st
LEFT JOIN score sc ON st.no=sc.no
LEFT JOIN sub su ON sc.subno=su.subno;
