安卓开发日记36
数据库表格创建
create table student(
sno CHAR(8) NOT NULL PRIMARY KEY ,--学号
sname VARCHAR(20) ,--姓名
sex CHAR(2) ,--性别
sdept VARCHAR(20) --所在系
);
create table course(
cno CHAR(6) NOT NULL primary key,--课程号
cname varchar(20) --课程名
);
create table sc(
sno char(8) not null references student(sno),--学号
cno char(6) not null primary key(sno,cno) references course(cno),--课程号
grade int not null --成绩
);
insert into student values('20050101','王飞','男','计算机系');
insert into student values('20050102','李丽','女','信息系');
insert into course values('01','数据结构');
insert into course values('02','数据库原理');
insert into sc values('20050101','01',70);
insert into sc values('20050101','02',80);
insert into sc values('20050102','01',90);
insert into sc values('20050102','02',70);
CREATE TABLE average (
sno INT PRIMARY KEY,
average_grade FLOAT
);
INSERT INTO average(sno, average_grade)
SELECT sno, AVG(grade) AS average_grade FROM sc GROUP BY sno;
UPDATE sc SET grade = 85 WHERE sno = 20050102 AND cno = 02;
DELETE FROM sc WHERE sno = 20050102 AND cno = 02;
UPDATE sc SET grade = 0 WHERE cno = 02;
DELETE FROM sc WHERE sno = (SELECT sno FROM student WHERE sname = '李丽');
DELETE FROM sc; DELETE FROM student; DELETE FROM course;
DROP TABLE sc; DROP TABLE student; DROP TABLE course;
DROP DATABASE stumanage;