openGauss使用练习
代码及运行结果
- 3.69
代码:
INSERT INTO student(sno,sname,ssex,sage,sdept) VALUES ('200215149','陈冬东','男',23,'CS');
运行结果
- 3.70
代码:
INSERT INTO student VALUES ('200215142','陈冬','男',22,'CT');
运行结果
- 3.71
代码:
INSERT INTO sc(sno,cno) VALUES ('200215149','0209');
运行结果
- 3.72
代码:
INSERT INTO dept_age (Sdept,avg_age) SELECT Sdept,AVG(Sage) FROM student GROUP BY sdept;
运行结果
- 3.73
代码:
UPDATE student SET sage=33 WHERE sno='200215137';
运行结果
- 3.74
代码:
UPDATE student set sage=sage+1;
运行结果
- 3.75
代码:
UPDATE sc SET grade=0 WHERE sno IN (SELECT sno FROM student WHERE sdept='IS');
运行结果
- 3.76
代码:
DELETE from student WHERE sno='200213147';
运行结果
- 3.77
代码:
DELETE FROM dept_age;
运行结果
- 3.78
代码:
DELETE from sc WHERE sno IN (SELECT sno FROM student WHERE sdept='IS');
运行结果
- 3.79
代码:
INSERT into sc (sno,cno,grade) VALUES('200213151','0223',NULL);
运行结果
- 3.80
代码:
UPDATE student SET sdept = NULL WHERE sno='200215137';
运行结果
- 3.81
代码:
SELECT * FROM student WHERE sname IS NULL OR sno IS NULL OR ssex IS NULL OR sdept IS NULL;
运行结果
- 3.82
代码:
SELECT sno FROM sc WHERE grade<60 AND cno='0207';
运行结果
- 3.83
代码:
SELECT sno FROM sc WHERE cno='0207' AND (grade<60 OR grade is NULL);
运行结果
- 3.84
代码:
CREATE VIEW IS_student AS SELECT sno,sname,sage FROM student where sdept='IS';
运行结果
- 3.85
代码:
CREATE VIEW IS_student1 AS SELECT sno,sname,sage FROM student WHERE sdept='IS' WITH CHECK OPTION;
运行结果
说明:openGauss不支持‘WITH CHECK OPTION’子句。 - 3.86
代码:
CREATE VIEW IS_s1 AS SELECT student.sno,sname,grade FROM student,sc WHERE sdept='IS' AND student.sno=sc.sno AND sc.cno='0204';
运行结果
- 3.87
代码:
CREATE VIEW IS_s2 AS SELECT sno,sname,grade FROM IS_s1 WHERE grade>=90;
运行结果
- 3.88
代码:
CREATE VIEW BT_s(sno,sname,sbirth) AS SELECT sno,sname,2014-sage FROM student;
运行结果
- 3.89
代码:
CREATE VIEW s_G(sno,Gavg) AS SELECT sno,AVG(grade) FROM sc GROUP BY sno;
运行结果
- 3.90
代码:
CREATE VIEW F_student(F_sno,name,sex,age,dept) AS SELECT * FROM student WHERE ssex='女';
运行结果
- 3.91
代码:
DROP VIEW is_s1 CASCADE;
运行结果
- 3.92
代码:
SELECT F_sno,age FROM f_student WHERE age<20;
运行结果
- 3.93
代码:
SELECT cs_student.sno,sname FROM cs_student,sc WHERE cs_student.sno=sc.sno AND sc.cno='0204';
运行结果
- 3.94
代码:
SELECT Sno,AVG(grade) FROM sc GROUP BY Sno HAVING AVG(grade) >= 90;
运行结果
- 3.95
代码:
UPDATE student SET sname='欧阳娜拉' WHERE sno='200213157' AND ssex='女' ;
运行结果
- 3.96
代码:
INSERT INTO student VALUES('200215199','张三','男',88,'CS');
运行结果