student.sql

《Java2程序设计实用教程(第2版)》课件

/*

CREATE TABLE stuinfo
   (stu#       varchar(10) NOT NULL UNIQUE,
    stu_name   varchar(10),
    sex        varchar(2),
    province   varchar(10),
    area       varchar(10),
    birthday   datetime,
    nation     varchar(10) DEFAULT '汉',
    member     bit,
  PRIMARY KEY(stu#))


CREATE TABLE course
   (course#       varchar(10) NOT NULL UNIQUE,
    course_name   varchar(40),
    credit_hour   numeric(5,1),
    period        integer,
  PRIMARY KEY(course#),
  CHECK(credit_hour>=0),
  CHECK(period>=0))


CREATE TABLE student_grade
   (stu#        varchar(10) NOT NULL,
    course#     varchar(10) NOT NULL,
    grade       numeric(5,2),
  PRIMARY KEY(stu#,course#),
  FOREIGN KEY(stu#) REFERENCES stuinfo(stu#),
  FOREIGN KEY(course#) REFERENCES course(course#),
  CHECK(grade BETWEEN 0 AND 100))


ALTER TABLE stuinfo
    ADD  department  varchar(20)


INSERT INTO stuinfo  
  VALUES ('98111001', '蔡尧强', '男', '浙江', '金华', '1980-1-20', '汉', 1, NULL)

INSERT INTO stuinfo
  VALUES ('98111002', '冯正亚', '女', '江苏', '江阴', '1979-1-9', '汉', 0, NULL)

INSERT INTO stuinfo
  VALUES ('98111003', '傅建玲', '女', '浙江', '绍兴', '1980-5-22', '汉', 1, NULL)

INSERT INTO stuinfo
  VALUES ('98111004', '韩家雄', '男', '江苏', '南通', '1979-4-30', '汉', 0, NULL)

INSERT INTO stuinfo  
  VALUES ('98111006', '江海山', '男', '安徽', '合肥', '1981-2-3', '汉', 0, NULL)

INSERT INTO stuinfo  
  VALUES ('98111007', '金明', '男', '安徽', '马鞍山', '1980-1-1', '汉', 0, NULL)

INSERT INTO stuinfo  
  VALUES ('98111008', '李翠', '女', '陕西', '延安', '1981-3-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111009', '李维华', '女', '青海', '西宁', '1979-11-6', '汉', 1, NULL)

INSERT INTO "stuinfo"  
  VALUES ('98111010', '梁小麟', '男', '广东', '茂名', '1980-3-5', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111011', '林水宝', '男', '福建', '泉州', '1981-2-10', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111012', '刘洪星', '男', '山东', '日照', '1979-10-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111013', '刘绍军', '男', '湖南', '常德', '1980-2-3', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111014', '吕修福', '男', '江苏', '连云港', '1981-11-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111015', '祁晶', '男', '甘肃', '白银', '1981-4-2', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111016', '钦海蓉', '男', '江苏', '江阴', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111017', '邵斯林', '男', '广西', '梧州', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111018', '沈瀚斐', '男', '江苏', '无锡', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111019', '盛梅兰', '女', '浙江', '金华', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo 
  VALUES ('98111020', '石健', '男', '贵州', '毕节', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111021', '王雄川', '男', '浙江', '金华', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111022', '魏晓宇', '男', '江苏', '扬州', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111023', '肖颖', '女', '福建', '南平', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111024', '谢吉吉', '男', '广东', '韶关', '1980-1-1', '汉', 0, NULL)

INSERT INTO stuinfo  
  VALUES ('98111025', '徐丹', '女', '江苏', '南通', '1980-1-1', '汉', 0, NULL)

INSERT INTO stuinfo  
  VALUES ('98111026', '徐远哲', '男', '安徽', '宿县', '1980-1-1', '汉', 0, NULL)

INSERT INTO stuinfo  
  VALUES ('98111027', '许明', '男', '山东', '日照', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111028', '严伟', '男', '浙江', '衢州', '1981-2-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111029', '杨旭', '男', '贵州', '铜仁', '1980-10-9', '土家', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111030', '杨训', '男', '安徽', '宿县', '1979-3-2', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111031', '姚远秀', '男', '陕西', '商洛', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111032', '易蝉鸣', '女', '广东', '湛江', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111033', '张建', '男', '江苏', '淮阴', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo  
  VALUES ('98111034', '张苏荣', '男', '安徽', '宣城', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo 
  VALUES ('98111037', '周雷', '男', '江苏', '徐州', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo
  VALUES ('98111038', '朱强光', '男', '江苏', '泗阳', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo
  VALUES ('98111040', '邹志敏', '男', '江西', '九江', '1980-1-1', '汉', 1, NULL)

INSERT INTO stuinfo (stu#, stu_name)
  VALUES ('98111041', '李伟')


UPDATE  stuinfo  
  SET   sex =  '男',
        province = '江苏',   
        area = '南京',   
        birthday = '1981-1-1',
        member = 1  
  WHERE stu# = '98111041'

UPDATE stuinfo  
  SET department = '计算机系'


DELETE FROM stuinfo  
  WHERE stu# = '98111041'


SELECT stu_name, sex, province, area
  FROM stuinfo  
  WHERE ( sex = '女' ) AND ( province = '江苏' )


SELECT stu_name, sex, province, area
  FROM stuinfo  
  WHERE ( sex = '女' ) AND ( province = '江苏' ) OR (province like '广%' )
  ORDER BY province

SELECT province, COUNT(stu_name)
  FROM stuinfo  
  GROUP BY province HAVING COUNT(stu_name)>4


INSERT INTO stuinfo (stu#)
  VALUES ('98111041')

SELECT COUNT(province)
  FROM stuinfo  

SELECT COUNT(*)
  FROM stuinfo  

SELECT COUNT(DISTINCT province)
  FROM stuinfo  


*/

/*
下列语句错，不能删除列
ALTER TABLE stuinfo
  DROP  department

下列语句执行未成功
修改已有列的数据类型？？
？？ALTER TABLE stuinfo
    MODIFY member integer

删除列的约束
??ALTER TABLE course
    DROP CHECK(period>0)

增加列的宽度
??ALTER TABLE stuinfo
  MODIFY  stu_name nchar(20)

*/