SQL数据库系统建

cly1666888

于 2019-11-13 12:51:36 发布

阅读量136

点赞数

分类专栏： sql数据库系统概论文章标签： SQL

本文链接：https://blog.csdn.net/weixin_44048531/article/details/103047044

版权

sql数据库系统概论专栏收录该内容

19 篇文章 1 订阅

订阅专栏

数据库系统概论

一. 建学生表

代码如下

create database school
use school


create table Student
 (
    sno char(6),
    sn  char(10) not null,
    sd  char(6),
    sb  datetime,
    sex char(2),
    check(sex in('男','女')),
    primary key(sno)
  )


 create table Course
   (
      cno char(6) primary key,
      cn  char(10),
      pc  char(6),
     foreign key(pc) references Course(cno)
   )


create table SC
   (
     sno char(6) not null,
     cno char(6) not null,
     grade dec(4,1) default null,
     primary key(sno,cno),
     foreign key(sno) references Student(sno),
     foreign key(cno) references Course(cno),
     check(grade between 0.0 and 100.0)
   )


INSERT INTO student VALUES('s01','王玲','计算机','1986-03-01','男')
INSERT INTO student VALUES('s02','李想','计算机','1985-04-01','女')
INSERT INTO student VALUES('s03','罗军','数学','1986-03-01','男')
INSERT INTO student VALUES('s04','李爱民','英语','1987-06-01','女')
INSERT INTO student VALUES('s05','季然','英语','1986-02-01','女')
INSERT INTO student VALUES('s06','王明','数学','1987-06-01','男')


INSERT INTO course VALUES('c01','高等数学',null)
INSERT INTO course VALUES('c02','数据结构',null)
INSERT INTO course VALUES('c03','操作系统','c02')
INSERT INTO course VALUES('c04','数据库','c03')
INSERT INTO course VALUES('c05','作战指挥','c04')
INSERT INTO course VALUES('c06','离散数学','c01')
INSERT INTO course VALUES('c07','信息安全','c06')
INSERT INTO course VALUES('c08','大学英语',null)
INSERT INTO course VALUES('c09','商贸英语','c08')
INSERT INTO course VALUES('c10','大学物理',null)
INSERT INTO course VALUES('c11','网络',null)
INSERT INTO course VALUES('c12','C程序',null)



INSERT INTO sc VALUES('s01','c01',80.0)
INSERT INTO sc VALUES('s01','c02',98.0)
INSERT INTO sc VALUES('s01','c03',85.0)
INSERT INTO sc VALUES('s01','c04',80.0)
INSERT INTO sc VALUES('s02','c07',89.0)
INSERT INTO sc VALUES('s02','c05',88.0)
INSERT INTO sc VALUES('s02','c06',78.0)
INSERT INTO sc VALUES('s03','c04',89.0)
INSERT INTO sc VALUES('s03','c01',88.0)
INSERT INTO sc VALUES('s03','c03',78.0)
INSERT INTO sc VALUES('s04','c07',77.0)
INSERT INTO sc VALUES('s04','c02',null)
INSERT INTO sc VALUES('s04','c09',83.0)
INSERT INTO sc VALUES('s05','c10',75.0)
INSERT INTO sc VALUES('s05','c11',90.0)
INSERT INTO sc VALUES('s05','c03',94.0)
INSERT INTO sc VALUES('s06','c09',89.0)
INSERT INTO sc VALUES('s06','c10',88.0)
INSERT INTO sc VALUES('s06','c11',null)