CREATE DATABASE IF NOT EXISTS info11 DEFAULT CHARSET utf8;
USE info11;
#建学生表
CREATE TABLE IF NOT EXISTS stu
(
NO INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO stu(NAME)VALUES("张三"),("李四"),("王五"),("赵四儿"),("田老五");
SELECT * FROM stu;
#建科目表
CREATE TABLE IF NOT EXISTS sub
(
subno INT PRIMARY KEY AUTO_INCREMENT,
subname VARCHAR(25)
);
INSERT INTO sub(subname)VALUES("SSM"),("c++"),("python"),("hbase");
SELECT * FROM sub;
#建成绩表
CREATE TABLE IF NOT EXISTS score
(
NO INT,
score INT,
subno INT
);
INSERT INTO score(NO,score,subno)VALUES(1,99,1),(2,89,2),(3,96,3),(4,65,4);
SELECT * FROM score;
#三表关联
SELECT st.*,sc.* FROM stu st
LEFT JOIN score sc ON st.no=sc.no
LEFT JOIN sub su ON sc.subno=su.subno
WHERE sc.score > 60;
#增加语句
INSERT INTO stu(NAME)VALUES("啊哈");
#删除语句
DELETE FROM stu WHERE NAME='啊哈';
#修改语句
UPDATE stu SET NAME='阿里' WHERE NO=1;