数据库1（MYSQL）大题_用户user3撤销user4对doctor表的查询权限

（一）实验目的： 

（二）实验内容：把创建数据库mySPJ，并建立S，P，J，和SPJ四个基本表的命令写在作业中。

（三）实验结果：可以是运行结果截图或其他形式的结果展示

（四）问题及解决：实验中遇到的问题及解决方法。

（五）回答思考题提出的问题。

CREATE DATABASE myspj CHARSET utf8;

Use myspj;

CREATE TABLE S (

   SNO VARCHAR(8) NOT NULL PRIMARY KEY,

   SNAME NVARCHAR(20) UNIQUE,

   STATUS INT ,

   CITY NVARCHAR(20)

) CHARSET utf8;

CREATE TABLE P (

   PNO VARCHAR(8) NOT NULL PRIMARY KEY,

   PNAME NVARCHAR(20),

   COLOR NVARCHAR(10),

   WEIGHT INT

)CHARSET utf8;

CREATE TABLE J(

   JNO VARCHAR(8) NOT NULL PRIMARY KEY,

   JNAME NVARCHAR(30),

   CITY NVARCHAR(20)

)CHARSET utf8;

CREATE TABLE SPJ (

   SNO VARCHAR(8),

   PNO VARCHAR(8),

   JNO VARCHAR(8),

   QTY INT ,

   PRIMARY KEY (SNO,PNO,JNO),

   FOREIGN KEY (SNO) REFERENCES S(SNO),

   FOREIGN KEY (PNO) REFERENCES P(PNO),

   FOREIGN KEY (JNO) REFERENCES J(JNO)

)CHARSET utf8;

（三）按照实验一中mySPJ数据库中列出的表1-4、1-5、1-6、1-7的基本表结构分别建立各表，并分别使用插入、删除、修改的方式更新基本表中的数据。

1、创建mySPJ数据库

2、分别输入创建S，P，J，和SPJ表命令

3、利用Insert 语句表1-4、1-5、1-6、1-7给出的数据记录插入各表。

4.利用Update更新表中的记录：

①将p表中的所有红色零件的重量增加5。

②将spj表中所有天津供应商的QTY属性值减少10。用子查询。

5.利用Delete语句删除p表中的所有红色零件的记录。

作业答案：

CREATE DATABASE myspj CHARSET utf8;

Use myspj;

CREATE TABLE S (

   SNO VARCHAR(8) NOT NULL PRIMARY KEY,

   SNAME NVARCHAR(20) UNIQUE,

   STATUS INT ,

   CITY NVARCHAR(20)

) CHARSET utf8;

CREATE TABLE P (

   PNO VARCHAR(8) NOT NULL PRIMARY KEY,

   PNAME NVARCHAR(20),

   COLOR NVARCHAR(10),

   WEIGHT INT

)CHARSET utf8;

CREATE TABLE J(

   JNO VARCHAR(8) NOT NULL PRIMARY KEY,

   JNAME NVARCHAR(30),

   CITY NVARCHAR(20)

)CHARSET utf8;

CREATE TABLE SPJ (

   SNO VARCHAR(8),

   PNO VARCHAR(8),

   JNO VARCHAR(8),

   QTY INT ,

   PRIMARY KEY (SNO,PNO,JNO),

   FOREIGN KEY (SNO) REFERENCES S(SNO),

   FOREIGN KEY (PNO) REFERENCES P(PNO),

   FOREIGN KEY (JNO) REFERENCES J(JNO)

)CHARSET utf8;

--S供应商表；

INSERT INTO s VALUES('S1','精益',20,'天津');

INSERT INTO s VALUES('S2','盛锡',10,'北京');

INSERT INTO s VALUES('S3','东方红',30,'北京');

INSERT INTO s VALUES('S4','丰泰盛',20,'天津');

INSERT INTO s VALUES('S5','为民',30,'上海');

--P零件表：

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);

--J工程项目表：

INSERT INTO J VALUES('J1','三建','北京');

INSERT INTO J VALUES('J2','一汽','长春');

INSERT INTO J VALUES('J3','弹 簧 厂','天津');

INSERT INTO J VALUES('J4','造 船 厂','天津');

INSERT INTO J VALUES('J5','机 车 厂','唐山');

INSERT INTO J VALUES('J6','无线电厂','常州');

INSERT INTO J VALUES('J7','半导体厂','南京');

--SPJ供应情况表：

INSERT INTO SPJ VALUES('S1','P1','J1',200);

INSERT INTO SPJ VALUES('S1','P1','J3',100);

INSERT INTO SPJ VALUES('S1','P1','J4',700);

INSERT INTO SPJ VALUES('S1','P2','J2',100);

INSERT INTO SPJ VALUES('S2','P3','J1',400);

INSERT INTO SPJ VALUES('S2','P3','J2',200);

INSERT INTO SPJ VALUES('S2','P3','J4',500);

INSERT INTO SPJ VALUES('S2','P3','J5',400);

INSERT INTO SPJ VALUES('S2','P5','J1',400);

INSERT INTO SPJ VALUES('S2','P5','J2',100);

INSERT INTO SPJ VALUES('S3','P1','J1',200);

INSERT INTO SPJ VALUES('S3','P3','J1',200);

INSERT INTO SPJ VALUES('S4','P5','J1',100);

INSERT INTO SPJ VALUES('S4','P6','J3',300);

INSERT INTO SPJ VALUES('S4','P6','J4',200);

INSERT INTO SPJ VALUES('S5','P2','J4',100);

INSERT INTO SPJ VALUES('S5','P3','J1',200);

INSERT INTO SPJ VALUES('S5','P6','J2',200);

INSERT INTO SPJ VALUES('S5','P6','J4',500);

INSERT INTO SPJ VALUES('S1','P1','J2',5000);

4.利用Update更新表中的记录：

①将p表中的所有红色零件的重量增加5。

UPDATE p SET WEIGHT=WEIGHT+5 WHERE color='红'

②将spj表中所有天津供应商的QTY属性值减少10。用子查询。

UPDATE spj SET  qty=qty-10

WHERE sno IN

( SELECT sno FROM s WHERE city='天津')

利用Delete语句删除p表中的所有红色零件的记录。
       DELETE FROM  p WHERE  color='红'

    但是受外码约束，改删除语句会被拒绝，因为spj表中有用到这些零件

（三）在实验二建立的mySPJ数据库及数据基础上完成如下简单查询。

（1）查询所有“天津”的供应商明细；

SELECT *

FROM s

WHERE city='天津';

（2）查询所有“红色”的14公斤以上的零件。

SELECT *

FROM p

WHERE color='红' AND weight>14;

（3）查询工程名称中含有“厂”字的工程明细。

SELECT *

FROM j

WHERE jname LIKE '%厂';

（四）在实验二建立的mySPJ数据库及数据基础上完成如下连接查询。

（1）等值连接：求s表和j表的相同城市的等值连接。

SELECT s.*,j.*

FROM s,j

WHERE s.`city`=j.`city`;

（2）自然连接：查询所有的供应明细，要求显示供应商、零件和工程的名称，并按照供应工 SELECT sname,jname,pname

FROM S,J,P,SPJ

WHERE SPJ.`sno`=S.`sno` AND SPJ.`jno`=j.`jno` AND spj.`pno`=p.`pno`

ORDER BY j.jno,p.pno

(3)笛卡尔积：求s和p表的笛卡尔积

 SELECT *

FROM s,p;

(4)左连接：求j表和spj表的左连接。

SELECT j.jno,jname,city,sno,pno,qty

FROM j

 LEFT JOIN spj ON(j.`jno`=spj.`jno`);

(5)右连接：求spj表和j表的右连接。

SELECT j.jno,jname,city,sno,pno,qty

FROM spj

 RIGHT JOIN j ON(j.`jno`=spj.`jno`);

（三）在实验二的建立的mySPJ数据库及数据基础上完成如下分组查询。

1.求各种颜色零件的平均重量。

SELECT COLOR,AVG(WEIGHT)

FROM p

GROUP BY color

2.求北京供应商和天津供应商的总个数。

SELECT city,COUNT(city) 供应商个数

FROM  s

WHERE city IN ('天津','北京')

GROUP BY city

求各供应商供应的零件总数。
SELECT s.SNO,SNAME,SUM(QTY) 零件总数

FROM  s,spj

WHERE s.sno=spj.sno

GROUP BY s.SNO,SNAME

求各供应商供应给各工程的零件总数。
SELECT s.SNO,j.jno,SUM(QTY) 零件总数

FROM  s,spj,j

WHERE s.sno=spj.sno   AND spj.jno=j.jno

GROUP BY s.SNO,j.jno

求使用了100个以上P1零件的工程名称。
SELECT j.jname

FROM  spj,j

WHERE  spj.jno=j.jno AND QTY>100 AND spj.PNO='p1'

求各工程使用的各城市供应的零件总数
SELECT j.jname,s.city,SUM(qty)

FROM  spj,j,s

WHERE spj.jno=j.jno AND s.sno=spj.sno

GROUP BY j.jname,s.city

（四）在实验二的建立的mySPJ数据库及数据基础上完成如下嵌套查询。

1、in连接