SPJ数据库Sql语句

1. 1. createdatabasespj
   2. go
   4. usespj
   5. go
   7. createtableS
   8. (
   9. Snochar(4)primarykey,
   10. Snamechar(20)unique,
   11. Statussmallint,
   12. Citychar(10)
   13. );
   15. createtableP
   16. (
   17. Pnochar(4)primarykey,
   18. Pnamechar(20),
   19. Colorchar(10),
   20. Weightsmallint,
   21. );
   23. createtableJ
   24. (
   25. Jnochar(4)primarykey,
   26. Jnamechar(20),
   27. Citychar(10)
   28. );
   30. createtableSPJ
   31. (
   32. Snochar(4),
   33. Pnochar(4),
   34. Jnochar(4),
   35. Qtysmallint,
   36. PRIMARYKEY(Sno,Pno,Jno),
   37. FOREIGNKEY(Sno)REFERENCESS(Sno),
   38. FOREIGNKEY(Pno)REFERENCESP(Pno),
   39. FOREIGNKEY(Jno)REFERENCESJ(Jno)
   40. );
   42. insertintoSvalues('S1','精益',20,'天津');
   43. insertintoSvalues('S2','盛锡',10,'北京');
   44. insertintoSvalues('S3','东方红',30,'北京');
   45. insertintoSvalues('S4','丰泰盛',20,'天津');
   46. insertintoSvalues('S5','为民',30,'上海');
   48. insertintoPvalues('P1','螺母','红',12);
   49. insertintoPvalues('P2','螺栓','绿',12);
   50. insertintoPvalues('P3','螺丝刀','蓝',12);
   51. insertintoPvalues('P4','螺丝刀','红',12);
   52. insertintoPvalues('P5','凸轮','蓝',12);
   53. insertintoPvalues('P6','齿轮','红',12);
   55. insertintoJvalues('J1','三建','北京');
   56. insertintoJvalues('J2','一汽','长春');
   57. insertintoJvalues('J3','弹簧厂','天津');
   58. insertintoJvalues('J4','造船厂','天津');
   59. insertintoJvalues('J5','机车厂','唐山');
   60. insertintoJvalues('J6','无线电厂','常州');
   61. insertintoJvalues('J7','半导体厂','南京');
   63. insertintoSPJvalues('S1','P1','J1',200);
   64. insertintoSPJvalues('S1','P1','J3',100);
   65. insertintoSPJvalues('S1','P1','J4',700);
   66. insertintoSPJvalues('S1','P2','J2',100);
   68. insertintoSPJvalues('S2','P3','J1',400);
   69. insertintoSPJvalues('S2','P3','J2',200);
   70. insertintoSPJvalues('S2','P3','J3',500);
   71. insertintoSPJvalues('S2','P3','J4',400);
   72. insertintoSPJvalues('S2','P5','J1',400);
   73. insertintoSPJvalues('S2','P5','J2',100);
   75. insertintoSPJvalues('S3','P1','J1',200);
   76. insertintoSPJvalues('S3','P3','J1',200);
   78. insertintoSPJvalues('S4','P5','J1',100);
   79. insertintoSPJvalues('S4','P6','J3',300);
   80. insertintoSPJvalues('S4','P6','J4',200);
   82. insertintoSPJvalues('S5','P2','J4',100);
   83. insertintoSPJvalues('S5','P3','J1',200);
   84. insertintoSPJvalues('S5','P6','J2',200);
   85. insertintoSPJvalues('S5','P6','J4',500);
   87. select*fromS
   88. select*fromP
   89. select*fromJ
   90. select*fromSPJ