实验一oracle基本表定义创建查询插入

最新推荐文章于 2022-06-21 23:01:01 发布

球球耶

最新推荐文章于 2022-06-21 23:01:01 发布

阅读量621

点赞数

分类专栏： orcale 文章标签： oracle

本文链接：https://blog.youkuaiyun.com/zs_pnzzz/article/details/106082214

版权

orcale 专栏收录该内容

13 篇文章

订阅专栏

本文提供了一个SQL实验的教学案例，包括创建教学数据库和供应关系数据库的基本表，定义完整性约束，以及录入模拟数据的过程。实验覆盖了主码、外码、检查约束等关键概念。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

实验类型：验证性

实验学时：2学时

一、实验目的

掌握sql语句定义基本表，理解完整性约束定义方法。

二、实验内容

使用select语句完成以下操作，（1）或（2）二选一：

（1）建立教学数据库基本表,定义主码，姓名不能重复，成绩小于等于100，并录入模拟数据。

student(sno,sname,ssex,sage,sdept)

course(cno,cname,cpno,ccredit)

sc(sno,cno,grade)

（2）建立供应关系数据库基本表,定义外码，录入模拟数据。

s(sno,sname,status,city)

p(pno,pname,color,weight)

j(jno,jname,city)

spj(sno,pno,jno,qty)

三、实验结果参考

本指导书的实验参考均以ORACLE 为例。

（1）

create  table  student (

sno  char(12)  not null ,

sname  varchar2(20) unique,

ssex  char(3) not null check(ssex='男' or ssex='女'),

sage  number(3）not null,

sdept  varchar2(20) ,

primary  key(sno)  

);

create  table course (

cno varchar2(6) not null ,

cname varchar2(20) not null ,

cpno varchar2(6) ,

ccredit number(2） ,

primary  key (cno) 

    );



create table sc (

sno char(12)  not null ,

cno varchar2(6) not null ,

grade number(3) check(grade<=100) ,

primary key (sno,cno)

) ;

insert into student(sno, sname, ssex, sage, sdept)

values('200215121', '李勇', '男',20,'cs');

insert into student(sno, sname, ssex, sage, sdept)

values('200215122', '刘晨', '女',19,'cs');

insert into student(sno, sname, ssex, sage, sdept)

values('200215123', '王敏', '女',18,'ma');

insert into student(sno, sname, ssex, sage, sdept)

values('200215125', '张立', '男',19,'is');



insert into course(cno, cname, cpno ,ccredit)

values('1', '数据库',5,4);

insert into course(cno, cname, cpno ,ccredit)

values('2', '数学',null,2);

insert into course(cno, cname, cpno ,ccredit)

values('3', '信息系统',1,4);

insert into course(cno, cname, cpno ,ccredit)

values('4', '操作系统',6,3);

insert into course(cno, cname, cpno ,ccredit)

values('5', '数据结构',7,4);

insert into course(cno, cname, cpno ,ccredit)

values('6', '数据处理',null,2);

insert into course(cno, cname, cpno ,ccredit)

values('7', 'pascal语言',6,4);



insert into sc(sno, cno, grade)

values('200215121','1',92);

insert into sc(sno, cno, grade)

values('200215121','2',85);

insert into sc(sno, cno, grade)

values('200215121','3',88);

insert into sc(sno, cno, grade)

values('200215122','2',90);

insert into sc(sno, cno, grade)

values('200215122','3',80);

附加 SQL语句：

Conn system /密码；//以系统管理员身份登录

Select * from student;//查询学生表中所有的数据

Drop table student；//删除学生表，让学生表彻底消失

（2）

create table s (

sno varchar2(10) not null ,

sname varchar2(50)  ,

status number(4) ,

city varchar2(20)  ,

primary key (sno) 

) ;



create table p (

pno varchar2(10)  not null ,

pname varchar2(50),

color varchar2(10),

weight  number(4),

primary key (pno)

) ;



create table j (

jno varchar2(10)  not null ,

jname varchar2(50) ,

city varchar2(20)  ,

primary key(jno)

) ;

create table spj (

sno varchar2 (10)  not null ,

pno varchar2 (10)  not null ,

jno varchar2 (10)  not null ,

qty  number(8) ,

primary key (sno,pno,jno),

foreign key (sno) references s(sno), //外键约束

foreign key (pno) references p(pno), //外键约束

foreign key (jno) references j(jno) //外键约束

);



insert into s(sno, sname, status, city)

values('s1','精益',20,'天津');

insert into s(sno, sname, status, city)

values('s2','盛锡',10,'北京');

insert into s(sno, sname, status, city)

values('s3','东方红',30,'北京');

insert into s(sno, sname, status, city)

values('s4','丰泰盛',20,'天津');

insert into s(sno, sname, status, city)

values('s5','为民',30,'上海');

insert into p(pno, pname, color, weight)

values('p1','螺母','红',12);

insert into p(pno, pname, color, weight)

values('p2','螺栓','绿',17);

insert into p(pno, pname, color, weight)

values('p3','螺丝刀','蓝',14);

insert into p(pno, pname, color, weight)

values('p4','螺丝刀','红',14);

insert into p(pno, pname, color, weight)

values('p5','凸轮','蓝',40);

insert into p(pno, pname, color, weight)

values('p6','齿轮','红',30);

insert into j(jno, jname, city)

values('j1','三建','北京');

insert into j(jno, jname, city)

values('j2','一气','长春');

insert into j(jno, jname, city)

values('j3','弹簧厂','天津');

insert into j(jno, jname, city)

values('j4','造船厂','天津');

insert into j(jno, jname, city)

values('j5','机床厂','唐山');

insert into j(jno, jname, city)

values('j6','无线电厂','常州');

insert into j(jno, jname, city)

values('j7','半导体厂','南京');

insert into spj(sno, pno, jno, qty)

values('s1','p1','j1',200);

insert into spj(sno, pno, jno, qty)

values('s1','p1','j3',100);

insert into spj(sno, pno, jno, qty)

values('s1','p1','j4',700);

insert into spj(sno, pno, jno, qty)

values('s1','p2','j2',100);

insert into spj(sno, pno, jno, qty)

values('s2','p3','j1',400);

insert into spj(sno, pno, jno, qty)

values('s2','p3','j2',200);

insert into spj(sno, pno, jno, qty)

values('s2','p3','j4',500);

insert into spj(sno, pno, jno, qty)

values('s2','p3','j5',400);

insert into spj(sno, pno, jno, qty)

values('s2','p5','j1',400);

insert into spj(sno, pno, jno, qty)

values('s2','p5','j2',100);

insert into spj(sno, pno, jno, qty)

values('s3','p1','j1',200);

insert into spj(sno, pno, jno, qty)

values('s3','p3','j1',200);

insert into spj(sno, pno, jno, qty)

values('s4','p5','j1',100);

insert into spj(sno, pno, jno, qty)

values('s4','p6','j3',300);

insert into spj(sno, pno, jno, qty)

values('s4','p6','j4',200);

insert into spj(sno, pno, jno, qty)

values('s5','p2','j4',100);

insert into spj(sno, pno, jno, qty)

values('s5','p3','j1',200);

insert into spj(sno, pno, jno, qty)

values('s5','p6','j2',200);

insert into spj(sno, pno, jno, qty)

values('s5','p6','j4',500);

插入可以简化为

insert into spj values('s5','p6','j4',500);