数据库实验一

第1关 创建数据库

#代码开始

CREATE DATABASE demo;

 

show databases

 #代码结束

第2关 创建表

#代码开始

#1. 切换到demo数据库

use demo;

 

#2. 分别创建s、p、j和spj数据表

create table s(

    sno char(2),

    sname varchar(10),

    status int,

    city varchar(10)

);

create table p(

    pno char(2),

    pname varchar(10),

    color char(1),

    weight int

);

create table j(

    jno char(2),

    jname varchar(10),

    city varchar(10)

);

create table spj(

    sno char(2),

    pno char(2),

    jno char(2),

    qty int

);

#3. 查看s、p、j和spj数据表的详细结构

describe s;

describe p;

describe j;

describe spj;

 #代码结束

第3关 插入数据

use demo;

#代码开始

#插入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);

 

#分别查询s、p、j和spj表的所有数据

select * from s;

select * from p;

select * from j;

select * from spj;

 #代码结束

第4关 ​​​​​​数据查询-基础查询

use demo;

#代码开始

#1. 查询所有供应商的姓名和所在城市（按供应商姓名升序排列）。

select sname,city from s order by sname;

 

#2. 查询所有零件的名称、颜色、重量（按零件名称升序排列）。

select pname,color,weight from p order by pname;

 #代码结束

第5关 数据查询-条件查询

use demo;

#代码开始

#1. 查询供应工程J1零件的供应商号码(去除重复，按供应商号码升序排列)。

SELECT distinct spj.sno FROM spj  WHERE jno='J1';

#2. 查询供应工程J1零件P1的供应商号码（按供应商号码升序排列）。

SELECT  spj.sno FROM spj WHERE spj.jno='J1' AND spj.pno ='P1';

#3. 查询使用供应商S1所供应零件的工程号码（按工程号码升序排列）。

SELECT spj.jno FROM spj WHERE spj.sno="S1" order by spj.jno;

 

#代码结束

第6关 数据查询-连接查询

use demo;

#代码开始

#1. 查询工程项目J2使用的各种零件的名称及其数量（按零件名称升序排列。

select  p.pname,spj.qty from p,spj where p.pno=spj.pno and spj.jno='J2' order by p.pname;

#2. 查询上海厂商供应的所有零件号码（去除重复，按零件号码升序排列）。

SELECT spj.pno FROM spj WHERE spj.sno IN(SELECT s.`SNO` FROM s WHERE s.city='上海') GROUP BY spj.pno;

#3. 查询使用上海产的零件的工程名称（按工程名称升序排列,去除重复数据）。

SELECT j.jname FROM j WHERE j.jno IN(SELECT spj.jno FROM spj WHERE spj.sno IN(SELECT s.sno FROM s WHERE s.city='上海')) order by j.jname

#代码结束

第7关 数据查询-子查询

use demo;

#代码开始

#1. 查询供应工程J1零件为红色的供应商号码（按供应商号码升序排列）。

SELECT spj.sno FROM spj WHERE spj.jno='J1' and spj.pno in (SELECT p.pno from p where p.color='红');

#2. 查询没有使用天津供应商生产的红色零件的工程号码（去除重复数据，按工程号码升序排列）。

SELECT DISTINCT jno FROM spj WHERE jno NOT IN (SELECT DISTINCT jno FROM spj NATURAL JOIN p NATURAL JOIN s WHERE city='天津' AND color='红') order by jno asc;

 

#代码结束

第8关 修改数据

use demo;

#代码开始

#1. 把全部红色零件的颜色改成蓝色。

UPDATE p SET p.color='蓝' WHERE p.color='红';

#2. 由S5供给J4的零件P6改为由S3供应。

UPDATE spj SET spj.sno='S3'  WHERE spj.sno='S5' AND spj.pno='P6' and spj.jno='J4';

#3. 从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。

DELETE FROM s WHERE s.sno='S2';

DELETE FROM spj WHERE spj.sno='S2';

#4. 请将(S2，J6，P4，200)插入供应情况关系表。

INSERT INTO spj VALUE('S2','P4','J6',200);

#代码结束

#评测使用，不要删除下面代码

select * from s;

select * from p;

select * from spj;

第9关 

创建视图

​​​
use demo;
#代码开始
#1. 请为“三建”工程项目建立一个供应情况的视图(v_spj)，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。
CREATE VIEW v_spj(sno,pno,qty) AS SELECT sno,pno,qty FROM spj WHERE jno=(SELECT jno FROM j WHERE jname='三建');

#2. 查看视图(v_spj)信息。
desc v_spj;

#针对视图(v_spj)完成下列查询：
#(1) 查询“三建”工程项目使用的各种零件代码及其总数量(total)
#提示：利用聚合函数sum()和分组查询实现，总数量起别名total。
SELECT pno,SUM(qty) total  FROM v_spj   GROUP BY pno;

#(2) 查询供应商S1的供应情况。
sELECT sno,pno,qty FROM v_spj WHERE sno='S1';

#代码结束

第10关 定义索引

use demo;

#代码开始

# 1.在spj表定义索引IDX_SPJ，包括（sno,pno,jno）。

ALTER TABLE spj ADD INDEX IDX_SPJ(sno,pno,jno);

# 2. 查看spj表索引定义。

SHOW INDEX FROM spj;

#代码结束