SQL数据库_邝志康

CREATE DATABASE IF NOT EXISTS inof DEFAULT CHARSET utf8;
USE inof;
CREATE TABLE IF NOT EXISTS we(sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
gender VARCHAR(20),
class_id INT
);
INSERT INTO we(sname,gender,class_id)VALUES
(“张三”,“男”,1),
(“李四”,“男”,1),
(“王五”,“男”,2);

CREATE TABLE IF NOT EXISTS wd(cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20),
teachaer_id INT
);
INSERT INTO wd(cname,teachaer_id)VALUES
(“语文”,1),
(“数学”,2),
(“英语”,3);

CREATE TABLE IF NOT EXISTS ws(sid INT,
student_id INT,
corse_id INT,
NUMBER INT
);
INSERT INTO ws(sid,student_id,corse_id,NUMBER)VALUES
(1,1,1,79),(1,1,2,80),(1,1,3,70),(2,2,1,66),(2,2,2,80),
(2,2,3,87),(3,3,1,97),(3,3,2,69),(3,3,3,89);

#删除
DELETE FROM we WHERE sid=1;
#改
UPDATE we SET sname=‘老六’,gender=‘女’ WHERE sid=3;
#差
SELECT * FROM we;

SELECT we.,wd.,ws.* FROM we we
INNER JOIN ws ws ON we.sid=ws.sid
LEFT JOIN wd wd ON wd.cid=ws.corse_id;

SELECT * FROM we WHERE sid IN(SELECT sid FROM we WHERE sname LIKE’李%’);