sql语言增删改及索引基础操作

--创建学生表
create table Student
	(Sno Char(7) not null unique,
	 Sname VarChar(20) Not Null,
	 Ssex Char(2) Not Null,
	 Sage Smallint,
	 Clno Char(5) Not Null);

--创建课程表
create table Course
	(Cno char(1) not null unique,
		Cname VarChar(20) Not Null,
		Credit Smallint);

--创建班级表
create table Class
	(Clno Char(5) not null unique,
		Speciality VarChar(20) Not Null,
		Inyear Char(4) Not Null,
		Number Integer,
		Monitor Char(7));

--创建成绩表
create table Grade
	(Sno Char(7) Not Null,
		Cno Char(1) Not Null,
		Gmark Numeric(4,1));

--查询
Select *from Student;
Select *from Course;
Select *from Grade;
Select *from Class;

--学生表中插入属性
alter table Student add nation varchar(20);

--学生表中删除属性
alter table Student drop column nation;

--成绩表插入数据
INSERT INTO Grade (Sno,Cno,Gmark) VALUES('2001110','3',80);

--修改成绩数据
UPDATE Grade SET Gmark = 70 WHERE Sno = '2001110';

--删除学生成绩记录
DELETE FROM Grade WHERE Sno = '2001110';

--学生表增加索引
CREATE INDEX IX_Class ON Student(Clno,Sno DESC);

--删除索引
DROP INDEX IX_Class ON Student;