oracle新建数据表和插入数据

admin

1. --新建表空间
   
2. create tablespace itsvse
   
3. datafile 'd:\itsvse.dbf'
   
4. size 10m;
   
5. --新建用户
   
6. create user itsvse
   
7. identified by 123;
   
8. --授权用户itsvse连接
   
9. grant connect to itsvse;
   
10. --授权用户使用资源
    
11. grant resource to itsvse;
    
12. 
    
13. 
    
14. 
    
15. 
    
16. --新建学生信息表
    
17. create table stuInfo
    
18. (
    
19.        stuid number primary key,
    
20.        stuname varchar(10) not null,
    
21.        stusex varchar(2) check (stusex='男' or stusex='女'),
    
22.        stubir date
    
23. );
    
24. 
    
25. --新建课程信息表
    
26. create table subInfo
    
27. (
    
28.        subid number primary key,
    
29.        subname varchar(20) not null
    
30. );
    
31. --新建分类信息表
    
32. create table scoreInfo
    
33. (
    
34.        scoreid number primary key,
    
35.        scorexh number references stuinfo(stuid),
    
36.        scoresubid number references subInfo(subid),
    
37.        score number check (score>=0 or score <=100)
    
38. );
    
39. 
    
40. 
    
41. --向学生表插入数据
    
42. insert into stuInfo values(1,'张山','男',to_date('2005-1-2','yyyy-mm-dd'));
    
43. insert into stuInfo values(2,'历史','男',to_date('2001-3-6','yyyy-mm-dd'));
    
44. insert into stuInfo values(3,'技术','女',to_date('1995-8-2','yyyy-mm-dd'));
    
45. insert into stuInfo values(4,'张山','男',to_date('2006-1-22','yyyy-mm-dd'));
    
46. insert into stuInfo values(5,'说道','女',to_date('2013-10-2','yyyy-mm-dd'));
    
47. insert into stuInfo values(6,'虽然','男',to_date('1996-10-20','yyyy-mm-dd'));
    
48. insert into stuInfo values(7,'谈一谈','女',to_date('2005-1-2','yyyy-mm-dd'));
    
49. insert into stuInfo values(8,'一样','女',to_date('2005-1-2','yyyy-mm-dd'));
    
50. insert into stuInfo values(9,'方法','男',to_date('2006-6-6','yyyy-mm-dd'));
    
51. insert into stuInfo values(10,'尺寸','男',to_date('2008-8-8','yyyy-mm-dd'));
    
52. --向课程信息表插入数据
    
53. insert into subinfo values(1,'数据库');
    
54. insert into subinfo values(2,'java');
    
55. insert into subinfo values(3,'web开发');
    
56. insert into subinfo values(4,'linux维护');
    
57. insert into subinfo values(5,'安全维护');
    
58. --向分类信息表插入数据
    
59. insert into scoreInfo values(1,1,2,50);
    
60. insert into scoreInfo values(2,1,3,70);
    
61. insert into scoreInfo values(3,2,1,100);
    
62. insert into scoreInfo values(4,3,1,80);
    
63. insert into scoreInfo values(5,1,1,60);

复制代码

moxuan

已复制