数据库系统原理课程总结3——SQL语句，建表，主键外键，存储过程，批量输入百万级数据

一、 请将你在作业2中设计的模式变成关系数据库中的表，并完成以下任务。
按如下格式要求在实验报告中描述所有涉及到的表的结构
在本次实验中，我设计了六个表格。
表1：

表2：

表3：

表4：

表5：

表6：

2.根据以上定义，写出各表的建表语句，并在你选的关系型数据库平台上建立各个表，请将建表语句统一写在扩展名为sql的文件中，构建一个建库脚本文本，命名要求为：
DBLabScript_学号.sql
答：已完成SQL文件，可以再文件夹中查看（这里的文件我会上传资源，审核通过之后我会把链接放到评论出，如果有需要也可以直接私信我）

3.掌握选用的关系型数据库管理系统的控制台插入数据的不同方法（执行数据批量插入脚本、窗口界面表格式手工录入、命令行交互式录入），实际填入测试数据，以验证你所设计的数据模型的合理性和完整性，注意验证三种完整性约束。
答：第一种：执行批量插入脚本：

输入后结果如下（其中第一个是之前建立数据库的时候输入的）：

第二种：窗口界面表格式手工录入

在选定的表格中选择第一个——select Rows-limit 1000，出现该界面：

点击表格，就可以对数值进行修改，点击图中null部分的表格，就可以向数据库输入数据，在完成输入之后点击apply，这里我向表格中输入一组数据：

这里显示的是我们在窗口中进行的操作的SQL语言，点击apply开始执行

这里我们的操作符合要求，程序通过。

第三种：命令行交互式录入
通过命令行打开MYSQL，打开数据库，输入SQL语句向指定的表格输入数据，返回OK说明录入正常。
（果然还是这种黑白风格更对我的胃口啊）

通过select语句观察表格数据和输入效果：

4.请根据以上设计结果，重新完善与整理作业#2中所设计的模式，可重新修改并提交作业#2
5.请设计单表查询、多表连接查询语句，查询表中的内容，并截图证明
答：单表查询，查询所在行业是CS的用户的用户ID和用户名。

多表查询，查询超级会员的用户名，所在行业，注册时间。

上图为输入的SQL语句和输入后输出的结果。

6.请尝试练习在某些表上建立唯一索引和聚集索引的方法，并将建索引的语句写入建库脚本中。

上图为输入的建立user表中用户名索引的SQL语句。

上图为建立唯一索引的SQL语句，因为使用的是MySQL5.7，没有聚簇索引功能，这里简单说一下两者的区别，聚簇索引和唯一索引都是通过改变数据库的数据存储结构进而提高查询效率的方式，但是原理不同，唯一索引更多是使用B+树，通过指针指向其他数据节点，所以唯一索引不唯一，即可以对一个表的多个属性进行索引建立，而聚簇索引是把一个属性相同的数据放在一起，所以对于一个表来说聚簇索引是唯一个，只能建一个。

7.若某个表中涉及百万甚至千万级以上的数据，请提出仿真这些数据的方案，并在实验报告加以叙述。
答：在本次实验中，我先后使用了逐条输入和批量输入两种方案，并且对运行时间进行了对比，前者要接近一个小时，后者只需要18秒就可以完成百万级数据的输入。具体内容如下：
之前批量输入的时候使用的SQL语句是每条数据都写一条insert语句，但是这样的方法在数据规模较大的时候就不具备可行性，所以我使用存储过程的方法，将insert语句循环调用，再通过call调用存储过程，从而实现较大规模数据的输入。原理和编程语言中的函数类似。
这里我直接使用了workbench中专门的存储过程的窗口。

调用设计好的存储过程。

调用后的结果如下：

插入一千条数据需要的时间为3秒，插入十万条数据用时五分钟，插入百万级数据需要的时间约为半小时。以本次实验中主要使用的user表格为例，目前的数据仿真方案为通过字符串和用户ID的拼接来实现数据的仿真。
但是，这里我们对于时长是明显不满意的，百万级数据需要的时间太长了，而究其原因，就是insert语句每一行都要输入一次，这是对时间的极大浪费，而改变这一情况的方法也是非常简单，就是关闭事务自动提交模式，在MYSQL中是默认自动提交的，但是我们设置不提交的话，那么所有insert的数据都会存起来，在结束这一语句之后一起输入，可以极大地缓解和改善时间状况。

这里我们只填加了一句SQL语句，就可以完成之前的要求。再次调用这个存储过程。如下图所示（把上次输入的都先删掉）

这一次我们看到输入了一百万条数据只用了18秒的时间，这极大的减少了数据的时间耗费。

完成教材（数据库系统概论第五版）P.128上的习题4,5,9三道大题，第4题的建表语句中需包含主键和外键约束。
答：第四题建表SQL语言如下：

1. drop table SPJ;
2. drop table S;
3. drop table P;
4. drop table J;
5. USE zhihu_database;
6. CREATE TABLE S
7. (
8. SNO varchar(45),
9. SNAME varchar(45),
10. STATUS_ int,
11. CITY varchar(45),
12. PRIMARY KEY (`SNO`)
13. );
14. CREATE TABLE P
15. (
16. PNO varchar(45),
17. PNAME varchar(45),
18. COLOR varchar(45),
19. WEIGHT varchar(45),
20. PRIMARY KEY (`PNO`)
21. );
22. CREATE TABLE J
23. (
24. JNO varchar(45),
25. JNAME varchar(225),
26. CITY varchar(225),
27. PRIMARY KEY (`JNO`)
28. );
29. CREATE TABLE SPJ
30. (
31. SNO varchar(45) references S(SNO),
32. PNO varchar(45) references P(PNO),
33. JNO varchar(45) references J(JNO),
34. QTY INT
35. );
36. insert into S
37. value
38. ('S1','精益',20,'天津'),
39. ('S2','盛锡',10,'北京'),
40. ('S3','东方红',30,'北京'),
41. ('S4','丰泰盛',20,'天津'),
42. ('S5','为民',30,'上海');
44. insert into P
45. value
46. ('P1','螺母','红',12),
47. ('P2','螺栓','绿',17),
48. ('P3','螺丝刀','蓝',14),
49. ('P4','螺丝刀','红',14),
50. ('P5','凸轮','蓝',40),
51. ('P6','齿轮','红',30);
53. insert into J
54. value
55. ('J1','三建','北京'),
56. ('J2','一汽','长春'),
57. ('J3','弹簧厂','天津'),
58. ('J4','造船厂','天津'),
59. ('J5','机车厂','唐山'),
60. ('J6','无线电厂','常州'),
61. ('J7','半导体厂','南京');
63. insert into SPJ
64. value
65. ('S1','P1','J1',200),
66. ('S1','P1','J3',100),
67. ('S1','P1','J4',700),
68. ('S1','P2','J2',100),
69. ('S2','P3','J1',400),
70. ('S2','P3','J2',200),
71. ('S2','P3','J4',500),
72. ('S2','P3','J5',400),
73. ('S2','P5','J1',400),
74. ('S2','P5','J2',100),
75. ('S3','P1','J1',200),
76. ('S3','P3','J1',200),
77. ('S4','P5','J1',100),
78. ('S4','P6','J3',300),
79. ('S4','P6','J4',200),
80. ('S5','P2','J4',100),
81. ('S5','P3','J1',200),
82. ('S5','P6','J2',200),
83. ('S5','P6','J4',500);
85. select * from S;
86. select * from P;
87. select * from J;
88. select * from SPJ;

复制代码

1. (1): select DISTINCT SNO
2. from SPJ
3. WHERE JNO='J1';

复制代码

1. (2): select distinct SNO
2. from SPJ
3. where PNO='P1' AND JNO='J1';

复制代码

1. (3): select distinct SNO
2. from SPJ,P
3. where P.PNO=SPJ.PNO AND P.COLOR='红' AND SPJ.JNO='J1';

复制代码

1. (4): SELECT DISTINCT JNO
2. FROM SPJ
3. WHERE JNO NOT IN(
4. SELECT JNO
5. FROM S,P,SPJ
6. WHERE S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO AND COLOR='红' AND CITY='天津');

复制代码

1. (5)：
2. SELECT DISTINCT JNO
3. FROM SPJ SPJ1
4. WHERE NOT EXISTS
5. (SELECT *
6. FROM SPJ SPJ2
7. WHERE SPJ2.SNO='S1' AND NOT EXISTS
8. (SELECT *
9. FROM SPJ SPJ3
10. WHERE SPJ3.JNO=SPJ1.JNO AND SPJ3.PNO=SPJ2.PNO))

复制代码

第五题：
（1）:

1. SELECT SNAME,CITY
2. FROM S

复制代码

（2）：

1. SELECT PNAME,COLOR,WEIGHT
2. FROM P

复制代码

（3）：

1. SELECT JNO
2. FROM SPJ
3. WHERE SNO='S1'

复制代码

（4）：

1. SELECT PNAME,QTY
2. FROM SPJ,P
3. WHERE(P.PNO=SPJ.PNO AND JNO='J2')

复制代码

（5）：

1. SELECT distinct PNO
2. FROM SPJ,S
3. WHERE(S.SNO=SPJ.SNO AND S.CITY='上海' )

复制代码

（6）：

1. SELECT JNAME
2. FROM J,SPJ,S
3. WHERE(J.JNO=SPJ.JNO AND S.SNO=SPJ.SNO AND S.CITY='上海')

复制代码

（7）：

1. SELECT distinct JNO
2. FROM J
3. WHERE JNO NOT IN
4. (SELECT JNO
5. FROM S,SPJ
6. WHERE S.SNO=SPJ.SNO AND CITY='天津')

复制代码

（8）：

1. SET SQL_SAFE_UPDATES = 0;
2. UPDATE P
3. SET COLOR='蓝'
4. WHERE COLOR='红'

复制代码

（9）：

1. UPDATE SPJ
2. SET SNO='S3'
3. WHERE(SNO='S5' AND PNO='P6' AND JNO='J4');

复制代码

（10）：

1. DELETE
2. FROM S
3. WHERE SNO='S2';
4. DELETE
5. FROM SPJ
6. WHERE SNO='S2';

复制代码

（11）：

1. `INSERT INTO SPJ VALUES('S2','P4','J6',200);`

复制代码

第九题：
创建视图：

1. CREATE VIEW SHITU
2. AS SELECT SNO,PNO,QTY
3. FROM SPJ,J
4. WHERE J.JNAME='三建' AND J.JNO=SPJ.JNO;

复制代码

（1）：

1. SELECT PNO,QTY
2. FROM SHITU;

复制代码

（2）：

1. SELECT SNO,PNO,QTY
2. FROM SHITU
3. WHERE SNO='S1';

复制代码