1.插入数据
插入指定列的数据
未设定的列默认用空值代替,如果可以为空
INSERT INTO Student(Sno, Sname, Sdept, Sage) VALUES('2222', '李四', '男', 'MA', 18);
插入所有列的数据
必须和原始数据列从左到右对应,空值用NULL
INSERT INTO Student VALUES('3333', '张三', '男' ,'MA', '19');
插入查询结果
CREATE TABLE Sdept_age(Sdept CHAR(15), avg_age SMALLINT);
INSERT INTO Sdept_age(Sdept, avg_age) (SELECT Sdept, AVG(Sage) FROM STUDENT GROUP BY Sage);
2.修改数据
修改某一个元组的值
UPDATE Student SET Sage=25 WHERE Sno='222';
修改多个元组的值
UPDATE Student SET Sage=Sage+1;
带子查询的修改语句
UPDATE SC SET Grade=0 WHERE Sno IN (SELECT SNO FROM Student WHERE Sage>19);
(更新和查询不能使用同一张表,
如:UPDATE Student SET Sdept='数学' WHERE Sno IN (SELECT Sno FROM Student WHERE Sage=20);
但是可以写成UPDATE Student SET Sdept='数学' WHERE Sno=20;)
3.删除数据
删除某一个元组的数据
DELETE FROM Student WHERE Sno='222';
删除多个元组的数据
DELETE FROM Student;
带子查询的删除语句
DELETE FROM SC WHERE SNO IN (SELECT SNO FROM Student WHERE SDEPT='cs');
级联删除
被删除的元素为某一子表的外键,直接删除会删除不成功,有两种处理方式:
先删除子表里对应的元素,如:DELETE FROM SC WHERE SNO=123;
然后再删除主表里对应的元素,DELETE FROM Student WHERE SNO=123;
另一种解决方式是,在创建子表时,设定级联删除:FOREIGN KEY(Sno) REFERENCES Student(Sno) ON DELETE CASCADE;