insert_data.sql

可实现学生成绩管理系统的各种功能,无需登陆,直接查询学生的各种信息

--插入学生情况数据

  INSERT INTO STUDENT 
   (SNO,     SNAME,      SDEPT, SCLASS, SSEX, SAGE) VALUES 
   ('96001', '马小燕',   'CS',  '01',   '女', 18);

  INSERT INTO STUDENT 
   (SNO,     SNAME,      SDEPT, SCLASS, SSEX, SAGE) VALUES 
   ('96002', '黎明',     'CS',  '01',   '男', 21);

  INSERT INTO STUDENT 
   (SNO,     SNAME,      SDEPT, SCLASS, SSEX, SAGE) VALUES 
   ('96003', '刘东明',   'MA',  '01',   '男', 18);

  INSERT INTO STUDENT 
   (SNO,     SNAME,      SDEPT, SCLASS, SSEX, SAGE) VALUES 
   ('96004', '赵志勇',   'IS',  '02',   '男', 20);

  INSERT INTO STUDENT 
   (SNO,     SNAME,      SDEPT, SCLASS, SSEX, SAGE) VALUES 
   ('97001', '马蓉',     'MA',  '02',   '女', 19);

  INSERT INTO STUDENT 
   (SNO,     SNAME,      SDEPT, SCLASS, SSEX, SAGE) VALUES 
   ('97002', '李成功',   'CS',  '01',   '男', 18);

  INSERT INTO STUDENT 
   (SNO,     SNAME,      SDEPT, SCLASS, SSEX, SAGE) VALUES 
   ('97003', '黎明',     'IS',  '03',   '女', 19);

  INSERT INTO STUDENT 
   (SNO,     SNAME,      SDEPT, SCLASS, SSEX, SAGE) VALUES 
   ('97004', '李丽',     'CS',  '02',   '女', 19);

  INSERT INTO STUDENT 
   (SNO,     SNAME,      SDEPT, SCLASS, SSEX, SAGE) VALUES 
   ('96005', '司马志明', 'CS',  '02',   '男', 18);


--插入课程名称数据

  INSERT INTO COURSE 
   (CNO,    CNAME,          CTIME,  CCREDIT) VALUES 
   ('001',  '数学分析',     144,  9);

  INSERT INTO COURSE 
   (CNO,    CNAME,          CTIME,  CCREDIT) VALUES 
   ('002',  '普通物理',    144,  9);

  INSERT INTO COURSE 
   (CNO,    CNAME,          CTIME,  CCREDIT) VALUES 
   ('003',  '微机原理',     80,  5);

  INSERT INTO COURSE 
   (CNO,    CNAME,          CTIME,  CCREDIT) VALUES 
   ('004',  '数据结构',     72,  4.5);

  INSERT INTO COURSE 
   (CNO,    CNAME,          CTIME,  CCREDIT) VALUES 
   ('005',  '操作系统',     56,  3.5);

  INSERT INTO COURSE 
   (CNO,    CNAME,          CTIME,  CCREDIT) VALUES 
   ('006',  '数据库原理',   80,  5);

  INSERT INTO COURSE 
   (CNO,    CNAME,          CTIME,  CCREDIT) VALUES 
   ('007',  '编译原理',     64,  4);

  INSERT INTO COURSE 
   (CNO,    CNAME,          CTIME,  CCREDIT) VALUES 
   ('008',  '程序设计',     48,  3);

--插入教师授课数据

  INSERT INTO TEACH   
   (TNAME,      CNO,    TDATE,            TDEPT) VALUES 
   ('王成刚',   '004',  '05-10月-1999',   'CS');
 
  INSERT INTO TEACH   
   (TNAME,      CNO,    TDATE,            TDEPT) VALUES 
   ('李正科',   '003',  '05-10月-1999',   'CS');

  INSERT INTO TEACH   
   (TNAME,      CNO,    TDATE,            TDEPT) VALUES 
   ('严明',     '001',  '05-10月-1999',   'MA');

  INSERT INTO TEACH   
   (TNAME,      CNO,    TDATE,            TDEPT) VALUES 
   ('赵高',     '004',  '05-10月-1999',   'IS');

  INSERT INTO TEACH   
   (TNAME,      CNO,    TDATE,            TDEPT) VALUES 
   ('李正科',   '003',  '05-10月-1999',    'MA');

  INSERT INTO TEACH   
   (TNAME,      CNO,    TDATE,            TDEPT) VALUES 
   ('刘峰',     '006',  '23-2月-2000',    'CS');

  INSERT INTO TEACH   
   (TNAME,      CNO,    TDATE,            TDEPT) VALUES 
   ('王成刚',   '004',  '23-2月-2000',    'IS');

  INSERT INTO TEACH   
   (TNAME,      CNO,    TDATE,            TDEPT) VALUES 
   ('马光明',   '008',  '23-2月-2000',    'CS');


--插入建立成绩数据

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('96001',  '001',  77.5); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('96001',  '003',  89); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('96001',  '004',  86); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('96001',  '005',  82); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('96002',  '001',  88); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('96002',  '003',  92.5); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('96002',  '006',  90); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('96005',  '004',  92); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('96005',  '005',  90); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('96005',  '006',  89); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('96005',  '007',  76); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('96003',  '001',  69); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('97001',  '001',  96); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('97001',  '008',  95); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('96004',  '001',  87); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('96003',  '003',  91); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('97002',  '003',  91); 

  INSERT INTO SCORE  (SNO,      CNO  ) VALUES 
                     ('97002',  '004'); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('97002',  '006',  92); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('97004',  '005',  90); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('97004',  '006',  85); 

  INSERT INTO SCORE  (SNO,      CNO,    SCORE) VALUES 
                     ('97004',  '008',  75); 



-- 显示日期时，利用ORACLE提供的掩码格式

  SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL;


-- 显示日期时，利用ORACLE提供的掩码格式

  SELECT  SNAME FROM STUDENT
    WHERE SNAME LIKE '%马__';

  SELECT  SNAME FROM STUDENT
    WHERE RTRIM(SNAME) LIKE '%马__';