1.用数据定义语言创建教学管理数据库stu_db,在数据库中创建schema T,在架构T下创建Student、Course、SC三张数据表(参见教材),并建立相应索引。
创建student数据表
CREATE TABLE Student
(sno CHAR(9)PRIMARY KEY,
Sname CHAR(20)UNIQUE,
Saex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
创建course数据表
CREATE TABLE Course
(cno CHAR(4)PRIMARY KEY,
Cname CHAR(40) NOT NULL,
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno)REFERENCES Course(Cno)
);
创建sc数据表
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno)REFERENCES Student(Sno),
FOREIGN KEY (Cno)REFERENCES Course(Cno)
);
2. 在以上三张表中录入若干数据。
录入数据(表Student)
insert into dbo.Student(sno,Sname,Saex,Sage,Sdept )VALUES('201215121','李勇','男','20','CS');
insert into dbo.Student(sno,Sname,Saex,Sage,Sdept )VALUES('201215122','刘晨','女','22','CS');
insert into dbo.Student(sno,Sname,Saex,Sage,Sdept )VALUES('201215123','王敏','女','21','MA');
insert into dbo.Student(sno,Sname,Saex,Sage,Sdept )VALUES('201215125','张旭','男','24','IS');
录入数据(表course)
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('1','数据库',NULL,4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('2','数学',NULL,4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('3','信息系统',NULL,4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('4','操作系统',NULL,4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('5','数据结构',NULL,4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('6','数据处理',NULL,4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('7','Pascal语言',NULL,4);
录入数据(表SC)
INSERT INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','1',92);
INSERT INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','2',85);
INSERT INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','3',88);
INSERT INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','2',90);
INSERT INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','3',80);
3.查询计科系20-23岁男同学的学号,姓名和出生年月。文章来源:https://www.toymoban.com/news/detail-848682.html
SELECT * FROM Student WHERE Sage IN(20,23);
4.查询姓刘,且名字为二个汉字的学生。文章来源地址https://www.toymoban.com/news/detail-848682.html
SELECT * FROM Student WHERE Sname LIKE '刘_';
到了这里,关于数据库:实验一 SQL数据定义的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!