数据库原理实验二命令方式建立数据库和单表查询

                                       实验二，命令方式建立数据库和单表查询

1. 目的与要求：                                                                                                                            1>掌握用SQL命令建立数据库和表的方法                                                                                2>掌握基本数据查询方法
2. 实验准备：                                                                                                                             1>规划好所建立数据库ST（参见P52,P79,82,83）和SPJ（参见P70）                                   2>设计好相关的语句
3. 实验重点：                                                                                                                           1>SQL命令建立数据库和表的方法                                                                                         2>单数据表的查询方法
4. 实验难点：1>完整性约束对数据库的影响2>GROUP BY 子句的设计
5. 实验内容： 

5.1 用SQL定义数据库和验证性实验

1. 建立数据库ST及其中的三个数据表Student 、Course 、SC，并输入数据。
2. 建立数据库SPJ及其中的四个数据表S 、P、J 、SPJ，并输入数据。
3. 对数据库ST，验证教材中实例。

5.2 设计性实验，对数据库SPJ用SQL完成下列查询

1. 找出所有供应商的名称和所在城市
2. 找出所有零件的名称、颜色、重量
3. 找出使用供应商S1所提供的各种零件的工程项目代码
4. 查询供应商名称中带有“盛”字的供应商信息
5. 查询各种零件供应总量，按零件号升序列出
6. 查询各种零件供应总量，按供应量总量降序列出
7. 查询各种零件供应总量，列出供应总量超过1000的零件和总量，按供应总量降序列出            实验过程：(给出代码和有关截图) 

5.1 用SQL定义数据库和验证性实验

1. 建立数据库ST1及其中的三个数据表Student 1、Course1 、SC1，并输入数据。

   1建立新的数据库ST1,
   CREATE DATABASE ST1
   2新建一个数据表Student1
   CREATE TABLE Student1
   (Sno CHAR(9)PRIMARY KEY,
   Sname CHAR(20)UNIQUE,
   Ssex CHAR(2),
   Sage SMALLINT,
   Sdept CHAR(20),);
   3为数据表Student1输入数据
   INSERT INTO Student1 VALUES('201215121','李勇','男',20,'CS')
   INSERT INTO Student1 VALUES('201215122','刘晨','女',19,'CS')
   INSERT INTO Student1 VALUES
   ('201215123','王敏','女',18,'MA'),
   ('201215125','张立','男',19,'IS')
   4新建一个数据表Course1
   CREATE TABLE Course1
   (Cno CHAR(4)PRIMARY KEY,
   Cname CHAR(40)NOT NULL,
   Cpno CHAR(4),
   Ccredit SMALLINT,
   FOREIGN KEY(Cpno)REFERENCES Course1(Cno)
   );
   5为数据表Course1输入数据
   INSERT INTO Course1 VALUES('1','数据库',NULL,4),
   ('2','数学',NULL,2),
   ('3','信息系统',NULL,4),
   ('4','操作系统',NULL,3),
   ('5','数据结构',NULL,4),
   ('6','数据处理',NULL,2),
   ('7','PASCAL语言',NULL,4)
   6新建一个数据表SC1
   CREATE TABLE SC1
   (Sno CHAR(9),
   Cno CHAR(4),
   Grade SMALLINT,
   PRIMARY KEY(Sno,Cno),
   FOREIGN KEY(Sno)REFERENCES Student1(Sno),
   FOREIGN KEY(Cno)REFERENCES Course1(Cno));
   7INSERT INTO SC1 VALUES('201215121','1',92),
   ('201215121','2',85),
   ('201215121','3',88),
   ('201215122','2',90),
   ('201215122','3',80);

   

    

    

    

    

    

    

2. 建立数据库SPJ及其中的四个数据表S 、P、J 、SPJ，并输入数据。

   1>新建数据库SPJ
   CREATE DATABASE SPJ
   2>新建数据表S
   CREATE TABLE S
   (SNO CHAR(6)PRIMARY KEY,
   SNAME CHAR(21),
   TATUS CHAR(21),
   CITY   CHAR(21));
   给S 表输入数据
   INSERT INTO S VALUES('S1','精益','20','天津'),
   ('S2','盛锡','10','北京'),
   ('S3','东方红','30','北京'),
   ('S4','丰泰盛','20','天津'),
   ('S5','为民','10','上海')
   查询S表
   SELECT *FROM S
   3>新建表P
   CREATE TABLE P
   (PNO CHAR(6)PRIMARY KEY,
   PNAME CHAR(21),
   COLOR CHAR(21),
   WEIGH SMALLINT);
   给表P输入数据
   INSERT INTO P VALUES('P1','螺母','红',12),
   ('P2','螺栓','绿',17),
   ('P3','螺丝刀','蓝',14),
   ('P4','螺丝刀','红',14),
   ('P5','凸轮','蓝',40),
   ('P6','齿轮','红',30);
   查询表P
   SELECT *FROM P
   4>新建表J
   CREATE TABLE J
   (JNO CHAR(6) PRIMARY KEY,
   JNAME CHAR(21),
   JCITY CHAR(21));
   给J表输入数据
   INSERT INTO J VALUES('J1','三建','北京'),
   ('J2','一汽','长春'),
   ('J3','弹簧厂','天津'),
   ('J4','造船厂','天津'),
   ('J5','机车厂','唐山'),
   ('J6','无线电厂','常州'),
   ('J7','半导体厂','南京');
   查询J表
   SELECT *FROM J
   5>新建表SPJ
   CREATE TABLE SPJ
   (SNO CHAR(6),
   PNO CHAR(6),
   JNO CHAR(6),
   QTY SMALLINT,
   PRIMARY KEY(SNO,PNO,JNO),
   FOREIGN KEY(SNO)REFERENCES S(SNO),
   FOREIGN KEY(PNO)REFERENCES P(PNO),
   FOREIGN KEY(JNO)REFERENCES J(JNO));
   给SPJ表输入数据
   INSERT INTO SPJ VALUES ('S1','P1','J1',200),
   ('S1','P1','J3',100),('S1','P1','J4',700),
   ('S1','P2','J2',100),('S2','P3','J1',400),
   ('S2','P3','J2',200),('S2','P3','J4',500),
   ('S2','P3','J5',400),('S2','P5','J1',400),
   ('S2','P5','J2',100),('S3','P1','J1',200),
   ('S3','P3','J1',200),('S4','P5','J1',100),
   ('S4','P6','J3',300),('S4','P6','J4',200),
   ('S5','P2','J4',100),('S5','P3','J1',200),
   ('S5','P6','J2',200),('S5','P6','J4',500);
   查询SPJ表
   SELECT *FROM SPJ

   

    

    

    

    

3. ）对数据库ST1，验证教材中实例

   1>查询全体学生的姓名，学号，所在系
   SELECT Sno,Sname,Sdept 
   FROM Student1 
   2>查询选修了全部课程的学生
   SELECT DISTINCT Sno
   FROM SC1;
   3>查询选修了课程的学生人数
   SELECT COUNT(DISTINCT Sno)
   FROM SC1;
   4>查询选修了及以上以上课程的学生学号
   SELECT Sno
   FROM SC1
   GROUP BY Sno
   HAVING COUNT(*)>=3;

    

    

   5.2 设计性实验，对数据库SPJ用SQL完成下列查询                                                               1  >找出所有供应商的名称和所在城市

   SELECT SNAME,CITY           FROM  

     2>找出所有零件的名称、颜色、重量

4. SELECT PNAME,COLOR,WEIGHT
   FROM P;

   3>找出使用供应商S1所提供的各种零件的工程项目代码

   SELECT JNO
   
   FROM SPJ
   
   WHERE SNO='S1';

   4>查询供应商名称中带有“盛”字的供应商信息

   SELECT *FROM S
   WHERE SNAME LIKE'%盛%';

   5>查询各种零件供应总量，按零件号升序列出

   SELECT PNO,SUM(QTY)
   FROM SPJ
   
   GROUP BY PNO
   
   ORDER BY PNO ASC;

   6>查询各种零件供应总量，按供应量总量降序列出

   SELECT PNO,SUM(QTY)
   FROM SPJ
   
   GROUP BY PNO
   
   ORDER BY SUM(QTY) DESC;

   7>查询各种零件供应总量，列出供应总量超过1000的零件和供应总量，按供应总量降序列出

5. SELECT PNO,SUM(QTY)
   
   FROM SPJ
   
   GROUP BY PNO
   
   HAVING SUM(QTY)>1000
   
   ORDER BY SUM(QTY) DESC;

6.实验总结

    1> 数据库名重复，新建表，目标数据库未选的话，达不到任务要求，失败

      2>实验过程中进行查询“找出使用供应商S1所提供的各种零件的工程项目代码”时由于输入法问题导致单引号出现问题，产生了语法错误，后及时更正继续实验。

     3>通过这次实验更加清晰地了解了数据库的运行和命令查询，自己亲手去做也更具有趣味性，比绕老绕去的逻辑更有说服力，也更容易理解。

     4>总结，今后在数据库的实验中，要注意输入法需为英文输入法，有些需要大写的地方也要注意。