11.sql

数据库的一些基础语句,适合初学者使用~~希望大家下载^代码经过验证,可以运行,在SQL Server2

create database 实验二
create table S
 (Sno char(10) primary key,  /*这里我一开始定义的SNAM，错误后，应删除该行，然后重新插入*/
  Status char(10),
  City char(20)
  );
create table P
 (Pno char(10) primary key,
  Pname char(20),
  Color char(20),
  Weight char(10)
  );
create table J
 (Jno char(10) primary key,
  Jname char(20),
  City char(20)
  );
create table SPJ
 (Sno char(10),
  Pno char(10),
  Jno char(10),
  Qty char(50)
  );

drop table S;    /*这里删除表，再重新建一个*/
create table S      
 (Sno char(10) primary key,
  Sname char(20) unique,
  Status char(10),
  City char(20)
  );

insert
into S(Sno,Sname,Status,City)
values('S1','精益',20,'天津');
insert
into S(Sno,Sname,Status,City)
values('S2','盛锡',10,'北京');
insert
into S
values('S3','东方红',30,'北京');
insert
into S
values('S4','丰泰盛',20,'天津');
insert
into S(Sno,Sname,Status,City)
values('S5','为民',30,'上海');

insert
into P
values('P1','螺母','红',12);
insert
into P
values('P2','螺栓','绿',17);
insert
into P
values('P3','螺丝刀','蓝',14);
insert
into P
values('P4','螺丝刀','红',14);
insert
into P
values('P5','凸轮','蓝',40);
insert
into P
values('P6','齿轮','红',30);

insert
into J
values('J1','三建','北京');
insert
into J
values('J2','一汽','长春');
insert
into J
values('J3','弹簧厂','天津');

insert
into SPJ
values('S1','P1','J1',200);
insert
into SPJ
values('S2','P3','J1',400);
insert
into SPJ
values('S5','P2','J3',300);
insert
into SPJ
values('S4','P1','J2',200);
insert
into SPJ
values('S3','P6','J3',200);
insert
into SPJ
values('S1','P4','J1',200);

select * from S;

alter table SPJ alter column Qty int;    /*这里MODIFY不能用改成ALTER COLUMN*/

alter table SPJ  add SPJ_mingzi char;

alter table S drop constraint unique(Sname);/*删除要加约束*/

update SPJ
set Jno=0
where 'S1'=(select Sno from S where S.Sno=SPJ.Sno);

delete 
from SPJ
where 'S1'=(select Sno from S where S.Sno=SPJ.Sno);