CREATE TABLE lovoStudent(
id INT PRIMARY KEY AUTO_INCREMENT,
studentName VARCHAR(20),
SUBJECT VARCHAR(20),
grade INT
)DEFAULT CHARSET =utf8;INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('張三','java基礎(chǔ)',97);
INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('張三','數(shù)據(jù)庫(kù)',80);
INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('張三','java web',96);
INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('李四','數(shù)據(jù)庫(kù)',95);
INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('李四','java基礎(chǔ)',94);
INSERT INTO lovoStudent(studentName,SUBJECT,grade) VALUES ('王五','java基礎(chǔ)',85);-- 查詢java基礎(chǔ)最高分及其最高分的得主
SELECT * FROM lovoStudent t WHERE grade=(SELECT MAX(grade) FROM lovoStudent t1 WHERE SUBJECT='java基礎(chǔ)' AND t1.SUBJECT=t.SUBJECT)-- 查詢各科目最高分及其得主
SELECT * FROM lovoStudent t WHERE grade=(SELECT MAX(grade) FROM lovoStudent t1 WHERE t.SUBJECT =t1.SUBJECT )
SELECT * FROM lovoStudent t WHERE grade IN(SELECT MAX(grade) FROM lovoStudent t1 WHERE t.SUBJECT =t1.SUBJECT GROUP BY SUBJECT)CREATE TABLE t_lovoClass(
id INT PRIMARY KEY AUTO_INCREMENT,
className VARCHAR(20)
)DEFAULT CHARSET =utf8;INSERT INTO t_lovoClass(className) VALUES('AT01');
INSERT INTO t_lovoClass(className) VALUES('AT02');
INSERT INTO t_lovoClass(className) VALUES('AT03');
INSERT INTO t_lovoClass(className) VALUES('AT04')
ALTER TABLE lovoStudent ADD classId INT;
UPDATE lovoStudent SET classId=1 WHERE id<3;
UPDATE lovoStudent SET classId=2 WHERE id>5;
UPDATE lovoStudent SET classId=3 WHERE id>=3 AND id<=5;-- 查詢學(xué)生表所有內(nèi)容并加上對(duì)應(yīng)班級(jí)信息
SELECT e.*,(SELECT className FROM t_lovoClass c WHERE e.classId=c.id) className FROM lovoStudent e;-- 查詢所有沒有學(xué)生的班級(jí)
SELECT className FROM t_lovoClass WHERE id NOT IN(SELECT classId FROM lovoStudent)
