sql spj

一

select sno,snamevar
from student;

select sno,snamevar,sdeptvar
from student;

select *
from student;

select snamevar,2014-age
from student;

select snamevar,‘year of birth’,2014-age,lower(sdeptvar)
from student;

select snamevar
from student
where sdeptvar=‘1’;

select snamevar,age
from student
where age<20;

select all sno
from student;

select snamevar,sdeptvar,age
from student
where age between 20 and 30;

select snamevar,sdeptvar,age
from student
where age not between 20 and 30;

select snamevar,sex
from student
where sdeptvar not in (‘123’,‘1’,‘3’);

select *
from student
where sno like ‘8’;

select snamevar,sno,sex
from student
where sno=‘8’;

select snamevar,sno,sex
from student
where snamevar like’小_’;

select snamevar,sno,sex
from student
where snamevar like’_周’;

select snamevar,sno,sex
from student
where snamevar not like’_周’;

select cno,credit
from course
where cnamevar like’数据库’;

select *
from student
order by sdeptvar,age desc;

二

Market

select *
from Custemers

select *
from Custemers
where CITY=‘上海’;

use market;
create table Custemers (
CID char(10) not null unique,
CNAME varchar(20) not null,
ADDRESS varchar(20) not null,
CITY varchar(20) not null,
PHONE char(11) not null,
FIRM varchar(50) not null,
BIRTH date,
CTYPE varchar(20)
);
create table Goods(
GID char(10) not null unique,
GNAME varchar(20) not null,
PRIECE float not null,
DEPICT varchar(100),
NUM tinyint not null,
PROVIDE varchar(20) not null,
STATUS tinyint
);
create table Orders(
OID char(10) not null unique primary key,
GID char(10) not null,
CID char(10) not null,
NUM tinyint not null,
SUM float not null,
ODATE date not null,
foreign key(CID) references Custemers(CID),
foreign key(GID) references Goods(GID)
);
insert into Custemers(CID,CNAME,ADDRESS,CITY,PHONE,FIRM,BIRTH,CTYPE) value(1,‘小赵’,‘上海’,‘上海’,111111,‘国美’,‘1999.10.12’,1),
(2,‘小田’,‘西安’,‘西安’,222222,‘飞科’,‘1999.10.12’,2),(3,‘北京’,‘北京’,‘女’,333333,‘国美’,‘1999.10.12’,3),
(4,‘小李’,‘杭州’,‘杭州’,444444,‘国美’,‘1999.10.12’,‘computer’),(5,‘重庆’,‘重庆’,‘男’,555555,‘飞科’,‘1999.10.12’,23),
(6,‘小吴’,‘西安’,‘西安’,666666,‘飞科’,‘1999.10.12’,123),(7,‘重庆’,‘重庆’,‘男’,777777,‘国美’,‘1999.10.12’,123),
(8,‘小王’,‘上海’,‘上海’,888888,‘国美’,‘1999.10.12’,123),(9,‘西安’,‘西安’,‘男’,99999,‘飞科’,‘1999.10.12’,123);
insert into Goods(GID,GNAME,PRIECE,DEPICT,NUM,PROVIDE) value(1,‘computer’,‘5000’,‘computer’,100,‘国美’),
(2,‘手机’,‘4000’,‘phone’,100,‘国美’),(3,‘手机’,‘4000’,‘phone’,100,‘国美’),
(4,‘手机’,‘4000’,‘phone’,100,‘国美’),(5,‘glass’,‘4000’,‘眼镜’,100,‘国美’),
(6,‘手机’,‘4000’,‘phone’,100,‘国美’),(7,‘手机’,‘4000’,‘phone’,100,‘国美’),
(8,‘pencil’,‘4000’,‘笔’,100,‘国美’),(9,‘computer’,‘4000’,‘computer’,100,‘国美’);

select GID,GNAME,PRIECE
from Goods
where DEPICT=‘computer’;

select GID,GNAME,PRIECE
from Goods
where DEPICT=‘computer’;

select GNAME,NUM,PRIECE
from Goods
where NUM between 100 and 500;

select GNAME name,NUM,PRIECE*0.25 PRIECE,PRIECE
from Goods
where NUM between 100 and 500;

alter database jwgl default character set=utf8;
set sql_safe_updates=0;

三

找出所有供应商姓名和所在城市

use SPJ;
select Sname,City
from s

找出所有零件的名称，颜色，数量

SELECT Pname,Color,weight
from P

找出供应商s1所提供的零件的工程号码

SELECT jno from SPJ where Sno='S1'

找出工程项目J2 使用的各种零件的名称及数量

select P.Pname,SPJ.Qty
from P,SPJ
where P.Pno=SPJ.Pno
and SPJ.Jno='J2';

找出上海厂商供应的所有零件的号码

select distinct Pno
from spj
where sno in
(select sno
from s
where city='上海') 

找出使用上海产的零件的工程名称

select Jname
from J,SPJ,S
where J.Jno=SPJ.Jno
and SPJ.Sno=S.Sno
and S.City='上海'

找出没有使用天津产的零件的工程号码

select Jno
from J
WHERE NOT EXISTS
(select *
from SPJ
where SPJ.JNO=J.JNO
and SNO IN(
	select SNO
	FROM S
	WHERE CITY='天津'))

把全部红色零件的颜色改成蓝色

updata P
set Color='蓝'
where Color='红'

由s5供给J4的零件，P6改为由S3供应 请做必要修改

update
SPJ
set Sno=‘S3’
where Sno=‘s5’
and Jno=‘J4’
and Jno=‘J6’

从供应商关系中 删除S2的记录 并从供应情况关系中删除相应记录
delete
from SPJ
where Sno=‘S2’;
delete
from S
where Sno=“S2”

请将（S2 ，J6，P4，200）插入供应情况关系
insert into SPJ(Sno,Pno,Jno,Qty)
values(“S2”,“P6”,“J4”,200)

四

use TSGL;
drop table S;
drop table p;
drop table j;
create table S(
SNO char(10) not null unique,
SNAME varchar(20) not null,
STATUS tinyint not null,
CITY varchar(10) not null
);
create table P(
PNO char(10) not null unique,
PNAME varchar(20) not null,
COLOR varchar(2) not null,
WEIGHT tinyint not null
);
create table J(
JNO char(10) not null unique,
JNAME varchar(20) not null,
CITY varchar(10) not null
);
create table SPJ(
SNO char(10) not null,
PNO char(10) not null,
JNO char(10) not null,
QTY tinyint not null,
primary key(SNO,PNO,JNO),
foreign key(SNO) references S(SNO),
foreign key(PNO) references P(PNO),
foreign key(JNO) references J(JNO)
);
insert into S(SNO,SNAME,STATUS,CITY) value(‘s1’,‘精益’,20,‘天津’),
(‘s2’,‘盛锡’,21,‘北京’),(‘s3’,‘东方红’,19,‘北京’),
(‘s4’,‘丰泰盛’,20,‘天津’),(‘s5’,‘为民’,20,‘上海’);