实验三：数据库物理设计
实验任务：
- 创建数据分区表
- 体会主键、外键约束
- 练习更新、删除主表数据（针对主键属性且子表中可能有参照外键数据）；
- 练习更新、删除主表数据（针对非主键属性）；
- 练习先删除子表数据，再删除主表数据；
- 使用子查询方式更新、删除数据
- 以上均自拟题目
- 体会索引
- 查询计划（EXPLAIN）
- 建立索引的原因
- 权限管理
- 以不同身份用户登录数据库建立表
- 表名一样
- 以不同身份用户查询自己与其他用户建立的表
- 定义授权方案
- 对授权方案进行验证
- 以不同身份用户登录数据库建立表
实验内容：
实验题目：
- 找出学号为“341433”的同学，将她的学号更新为“100000”，并更新和参照外键数据。
- 学号更新完之后，删除学号为“100000”同学的相关信息。
- 寻找性别为“NULL”的数据，并将其赋值为“男”。
- 寻找出生日期为“NULL”的数据，删除这些同学的信息，以及他们的选课信息。
- 删除设计与艺术学院（zy）的相关信息，并把属于设计与艺术学院的同学，老师，授课信息以及同学的选课信息删除。
- 找出挂过科的同学（至少有一名课程成绩在小于60），将他们的班级信息更新为“08012048”。
- 找出挂过科的同学，并删除他们的对应数据；与此同时，对应的选课信息也被删除。
授权方案题目：
假设Nardack有两位她最得意的学员krenz以及rella，而Nardack可以允许krenz以及rella查看自己的部分但不是全部的数据，对自己的数据没有增删改的权限，而krenz，rella拥有对方某个表表全部数据的增加和查询权限，无删除和修改权限，而对于其他表没有任何权限。
在这个实验中，具体的方案如下：
-
用户Nardack拥有对krenz，rella全部表的增删改查权限；
-
用户krenz、rella拥有对Nardack中xs表中xm,bj字段的查询权限，但没有增删改的权限；
-
用户krenz拥有对rella中xs表的增加和查询权限，无删除和修改权限，对于其他表没有任何权限；
-
用户rella对于krenz的表同样拥有上述权限；
以下实验使用的相关软件和参考文档可以通过这个网盘地址下载：
链接: https://pan.baidu.com/s/1Utlmw6Gfrspybw-eV88eEw
提取码: 3g8z
实验步骤：
一：将数据库恢复到初始状态
由于实验二已经对数据库进行了一定的修改操作，因此在进行这个实验之前，我们需要将数据库恢复到我们插入特殊数据之后的状态。
为了方便调用这些语句，可以把这些语句建立为一个存储过程proc_restore_database()：
DROP PROCEDURE IF EXISTS proc_restore_database;
CREATE PROCEDURE proc_restore_database() AS
BEGIN
CREATE TABLE xyb(
ydh VARCHAR(2) NOT NULL,
ymc VARCHAR(30) NOT NULL,
CONSTRAINT xyb_pkey PRIMARY KEY (ydh)
);
CREATE TABLE xs(
xm VARCHAR(24) NOT NULL,
xh VARCHAR(10) NOT NULL,
ydh VARCHAR(2),
bj VARCHAR(8),
chrq DATE,
xb VARCHAR(6),
CONSTRAINT xs_pkey PRIMARY KEY (xh),
CONSTRAINT xs_fkey FOREIGN KEY (ydh) REFERENCES xyb(ydh)
);
CREATE TABLE kc(
kcbh VARCHAR(3) NOT NULL,
kc VARCHAR(48) NOT NULL,
lx VARCHAR(12),
xf DECIMAL(5,1),
CONSTRAINT kc_pkey PRIMARY KEY (kcbh)
);
CREATE TABLE js(
xm VARCHAR(24) NOT NULL,
jsbh VARCHAR(10) NOT NULL,
zc VARCHAR(18),
ydh VARCHAR(2),
CONSTRAINT js_pkey PRIMARY KEY (jsbh),
CONSTRAINT js_fkey FOREIGN KEY (ydh) REFERENCES xyb(ydh)
);
CREATE TABLE sk(
kcbh VARCHAR(3) NOT NULL,
bh VARCHAR(10) NOT NULL,
CONSTRAINT sk_pkey PRIMARY KEY (kcbh,bh),
CONSTRAINT sk_fkey_1 FOREIGN KEY (kcbh) REFERENCES kc(kcbh),
CONSTRAINT sk_fkey_2 FOREIGN KEY (bh) REFERENCES js(jsbh)
);
CREATE TABLE xk(
xh VARCHAR(10) NOT NULL,
kcbh VARCHAR(3) NOT NULL,
jsbh VARCHAR(10) NOT NULL,
cj DECIMAL(5,1),
CONSTRAINT xk_pkey PRIMARY KEY (xh,kcbh,jsbh),
CONSTRAINT xk_fkey_1 FOREIGN KEY (xh) REFERENCES xs(xh),
CONSTRAINT xk_fkey_2 FOREIGN KEY (kcbh,jsbh) REFERENCES sk(kcbh,bh)
);
/*
Create Index:
*/
DROP INDEX IF EXISTS xm_index;
DROP INDEX IF EXISTS xk_index;
CREATE INDEX xm_index ON xs(xm);
CREATE UNIQUE INDEX xk_index ON xk(xh,kcbh,jsbh);
/*
Insert Data:
*/
INSERT INTO xyb (ydh,ymc) VALUES ('zy','设计与艺术学院');
INSERT INTO xyb (ydh,ymc) VALUES ('js','计算机学院');
INSERT INTO xyb (ydh,ymc) VALUES ('zd','自动化学院');
INSERT INTO xyb (ydh,ymc) VALUES ('yh','宇航学院');
INSERT INTO xyb (ydh,ymc) VALUES ('gl','管理与经济学院');
INSERT INTO xyb (ydh,ymc) VALUES ('jd','机电学院');
INSERT INTO xyb (ydh,ymc) VALUES ('jx','机械与车辆学院');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('敏易文','男','1413974679','zy','08012003','10/05/1998');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('芒怀蕾','女','1685653695','zy','08012001','10/01/1983');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('战真一','男','1566662922','js','08012001','06/01/1997');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('管巧香','女','1233961404','js','08012004','03/22/1997');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Iris','女','1972587625','zy','08012004','01/01/1996');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('雪念双','女','1153697599','jd','08012002','02/18/1981');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Helen','女','1802616482','js','08012003','06/28/1980');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Dora','女','1511297874','zy','08012004','03/29/1988');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('邶怀慕',NULL,'1014462526','js','08012004','08/12/1997');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Yusuf','男','1309592697','gl','08012002','06/19/1998');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Linda','女','1360232256','zy','08012003','05/12/1982');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Yusuf','男','1437120165','zd','08012004','12/08/1987');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Robert','男','1433528624','jd','08012003','06/14/2001');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Sandy','女','1242910341','gl','08012001','05/02/1996');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('范森',NULL,'1778981777','gl','08012004','11/17/1987');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Peter','男','1370657918','jd','08012001','08/17/1997');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('芒怀蕾','女','1898230703','zd','08012001','01/02/1999');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('戚谷之','男','1062541594','yh','08012003','10/12/1994');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('戊尔槐','男','1181331372','gl','08012004','01/26/1981');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Maxwell','男','1139432773','zy','08012001','12/16/2001');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('昝依然','女','1139591738','yh','08012002','03/28/1991');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('乌孙素','男','1674025032','gl','08012002','06/07/1991');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Victor','男','1621741704','zy','08012001','10/15/1989');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('良乐蕊','女','1305089848','js','08012004','07/05/2001');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Maxwell','男','1482207116','jx','08012004','07/24/1981');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Bob','男','1380266671','gl','08012002','04/08/1999');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Eric',NULL,'1183109618','jd','08012004','02/03/1991');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Frank','男','1583270484','jx','08012001','12/13/1995');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('竭贞静',NULL,'1460856188','gl','08012003','06/17/1988');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('管巧香','女','1475176768','zd','08012001','04/05/1994');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('邶怀慕',NULL,'1163770916','gl','08012004','11/21/1987');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('乌孙素','男','1537273456','yh','08012003','12/26/2000');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('寇晨希','女','1966924167','zy','08012002','04/19/1998');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Peter','男','1223718463','yh','08012004','11/26/1991');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('William','男','1014159148','js','08012002','07/21/1997');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('郭语梦','女','1757816083','jx','08012004','10/02/1998');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('戊尔槐','男','1635046058','jd','08012001','03/29/1994');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('良乐蕊','女','1708102687','yh','08012004','12/07/1982');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Gina','女','1527047399','js','08012002','03/17/2001');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Iris','女','1452773186','jd','08012001','12/14/1995');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Frank','男','1658823100','zd','08012002','01/05/1988');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('巴妮娜','女','1977945439','js','08012002','09/28/1996');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('乌孙素','男','1887592443','gl','08012001','04/27/2001');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Gina','女','1438908157','yh','08012002','09/23/1998');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Uriel',NULL,'1591604897','js','08012001','07/17/1992');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Linda','女','1851600341','yh','08012003','05/09/1985');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('郭语梦','女','1737803536','yh','08012002','03/29/1986');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Kate','女','1604090165','jd','08012001','06/04/2000');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Gina','女','1243712858','yh','08012003','01/25/1985');
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Uriel',NULL,'1350749819','gl','08012003','02/12/1984');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('刘备','1025678504','蜀','zy');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('关羽','1630619579','蜀','js');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('张飞','1531525387','蜀','zd');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('诸葛亮','1056527777','蜀','yh');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('赵云','1854355842','蜀','gl');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('马超','1291237556','蜀','jd');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('曹操','2803009978','魏','jx');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('司马懿','2179967243','魏','zy');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('夏侯惇','2522715615','魏','js');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('张辽','2735290365','魏','zd');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('郭嘉','2880701633','魏','yh');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('甄姬','2546714455','魏','gl');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('孙权','3769660116','吴','jd');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('甘宁','3461937823','吴','jx');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('吕蒙','3460484429','吴','zy');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('黄盖','3256131973','吴','js');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('周瑜','3987920115','吴','zd');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('大乔','3251367723','吴','yh');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('陆逊','3348191361','吴','gl');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('孙尚香','3746249085','吴','yh');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('孙坚','3798374147','吴','gl');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('华佗','4057072620','群雄','zy');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('吕布','4155359299','群雄','js');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('貂蝉','4309044143','群雄','zd');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('袁绍','4314185094','群雄','yh');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('董卓','4550593619','群雄','gl');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('贾诩','4354369112','群雄','jd');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('庞德','4734180006','群雄','jx');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('左慈','4153167425','群雄','zy');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('张角','4548210474','群雄','js');
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('sm','设计构成','平台基础',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('zx','造型综合表现','平台基础',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('sc','色彩综合表现','平台基础',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('sjm','设计美学','平台基础',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('sjc','视觉传达设计','平台基础',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('cts','插图设计','平台基础',4.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('xms','视觉游牧——西方美术史','校公选课',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('sjs','视觉审美与设计赏析','校公选课',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('rbl','日本流行文化与社会','校公选课',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('djj','大学计算机基础','平台基础',3.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('dsa','数据结构与算法设计','平台基础',5.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('csb','程序设计方法实践','平台基础',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('web','互联网应用开发基础训练','平台基础',1.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('se','软件工程基础训练','平台基础',1.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('csa','程序设计基础','平台基础',3.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('vis','走进机器视觉','校公选课',0.5);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('xh','信号与系统','平台基础',3.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('gs','光学系统设计与工艺','平台基础',3.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('em','电动力学','平台基础',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('lla','理论力学A','平台基础',6.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('llx','理论力学C','平台基础',4.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('gl','工程力学','平台基础',4.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('zka','自动控制原理A','平台基础',4.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('fxq','飞行器系统优化设计','校公选课',1.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('mea','微观经济学','平台基础',3.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('mec','微观经济学','校公选课',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('syx','市场营销学','平台基础',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('cwg','财务管理概论','平台基础',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('cyg','创新创业管理','校公选课',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('gtr','沟通与人生','校公选课',2.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('zza','设计与制造基础 I','平台基础',4.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('zzb','设计与制造基础 II','平台基础',4.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('qca','汽车学 I','平台基础',3.0);
INSERT INTO kc (kcbh,kc,lx,xf) VALUES ('qcb','汽车学 II','平台基础',3.0);
INSERT INTO sk (kcbh,bh) VALUES ('sc','2880701633');
INSERT INTO sk (kcbh,bh) VALUES ('mea','4314185094');
INSERT INTO sk (kcbh,bh) VALUES ('gtr','3460484429');
INSERT INTO sk (kcbh,bh) VALUES ('em','1025678504');
INSERT INTO sk (kcbh,bh) VALUES ('sc','3798374147');
INSERT INTO sk (kcbh,bh) VALUES ('dsa','3769660116');
INSERT INTO sk (kcbh,bh) VALUES ('lla','1630619579');
INSERT INTO sk (kcbh,bh) VALUES ('web','4314185094');
INSERT INTO sk (kcbh,bh) VALUES ('sjs','4548210474');
INSERT INTO sk (kcbh,bh) VALUES ('xms','4354369112');
INSERT INTO sk (kcbh,bh) VALUES ('fxq','3987920115');
INSERT INTO sk (kcbh,bh) VALUES ('qca','1531525387');
INSERT INTO sk (kcbh,bh) VALUES ('csb','4314185094');
INSERT INTO sk (kcbh,bh) VALUES ('zka','3798374147');
INSERT INTO sk (kcbh,bh) VALUES ('fxq','3251367723');
INSERT INTO sk (kcbh,bh) VALUES ('djj','4155359299');
INSERT INTO sk (kcbh,bh) VALUES ('em','4155359299');
INSERT INTO sk (kcbh,bh) VALUES ('rbl','3798374147');
INSERT INTO sk (kcbh,bh) VALUES ('csa','2880701633');
INSERT INTO sk (kcbh,bh) VALUES ('mec','2179967243');
INSERT INTO sk (kcbh,bh) VALUES ('djj','4734180006');
INSERT INTO sk (kcbh,bh) VALUES ('rbl','3987920115');
INSERT INTO sk (kcbh,bh) VALUES ('sjm','2735290365');
INSERT INTO sk (kcbh,bh) VALUES ('zza','2546714455');
INSERT INTO sk (kcbh,bh) VALUES ('mec','1291237556');
INSERT INTO sk (kcbh,bh) VALUES ('qcb','3460484429');
INSERT INTO sk (kcbh,bh) VALUES ('gs','1630619579');
INSERT INTO sk (kcbh,bh) VALUES ('gtr','4057072620');
INSERT INTO sk (kcbh,bh) VALUES ('rbl','4550593619');
INSERT INTO sk (kcbh,bh) VALUES ('cwg','2880701633');
INSERT INTO sk (kcbh,bh) VALUES ('dsa','1291237556');
INSERT INTO sk (kcbh,bh) VALUES ('se','2803009978');
INSERT INTO sk (kcbh,bh) VALUES ('sc','4057072620');
INSERT INTO sk (kcbh,bh) VALUES ('zza','4550593619');
INSERT INTO sk (kcbh,bh) VALUES ('cyg','1025678504');
INSERT INTO sk (kcbh,bh) VALUES ('vis','4155359299');
INSERT INTO sk (kcbh,bh) VALUES ('cts','4057072620');
INSERT INTO sk (kcbh,bh) VALUES ('zka','4155359299');
INSERT INTO sk (kcbh,bh) VALUES ('sm','4057072620');
INSERT INTO sk (kcbh,bh) VALUES ('lla','4309044143');
INSERT INTO sk (kcbh,bh) VALUES ('mec','2803009978');
INSERT INTO sk (kcbh,bh) VALUES ('cwg','4309044143');
INSERT INTO sk (kcbh,bh) VALUES ('gtr','3769660116');
INSERT INTO sk (kcbh,bh) VALUES ('rbl','3746249085');
INSERT INTO sk (kcbh,bh) VALUES ('zzb','3987920115');
INSERT INTO sk (kcbh,bh) VALUES ('qcb','4153167425');
INSERT INTO sk (kcbh,bh) VALUES ('zzb','1531525387');
INSERT INTO sk (kcbh,bh) VALUES ('mec','4153167425');
INSERT INTO sk (kcbh,bh) VALUES ('lla','3798374147');
INSERT INTO sk (kcbh,bh) VALUES ('fxq','4734180006');
INSERT INTO sk (kcbh,bh) VALUES ('se','4057072620');
INSERT INTO sk (kcbh,bh) VALUES ('qcb','3769660116');
INSERT INTO sk (kcbh,bh) VALUES ('se','2735290365');
INSERT INTO sk (kcbh,bh) VALUES ('zka','1854355842');
INSERT INTO sk (kcbh,bh) VALUES ('sjs','3798374147');
INSERT INTO sk (kcbh,bh) VALUES ('web','4309044143');
INSERT INTO sk (kcbh,bh) VALUES ('mea','3251367723');
INSERT INTO sk (kcbh,bh) VALUES ('web','4734180006');
INSERT INTO sk (kcbh,bh) VALUES ('sjs','4354369112');
INSERT INTO sk (kcbh,bh) VALUES ('cyg','3251367723');
INSERT INTO sk (kcbh,bh) VALUES ('gtr','1056527777');
INSERT INTO sk (kcbh,bh) VALUES ('zka','3251367723');
INSERT INTO sk (kcbh,bh) VALUES ('csb','4153167425');
INSERT INTO sk (kcbh,bh) VALUES ('sc','2179967243');
INSERT INTO sk (kcbh,bh) VALUES ('zza','4548210474');
INSERT INTO sk (kcbh,bh) VALUES ('sjm','2880701633');
INSERT INTO sk (kcbh,bh) VALUES ('em','3251367723');
INSERT INTO sk (kcbh,bh) VALUES ('gl','3987920115');
INSERT INTO sk (kcbh,bh) VALUES ('gl','2735290365');
INSERT INTO sk (kcbh,bh) VALUES ('csb','3769660116');
INSERT INTO sk (kcbh,bh) VALUES ('zx','2522715615');
INSERT INTO sk (kcbh,bh) VALUES ('gtr','4354369112');
INSERT INTO sk (kcbh,bh) VALUES ('gl','3256131973');
INSERT INTO sk (kcbh,bh) VALUES ('zza','1531525387');
INSERT INTO sk (kcbh,bh) VALUES ('syx','2735290365');
INSERT INTO sk (kcbh,bh) VALUES ('qca','4548210474');
INSERT INTO sk (kcbh,bh) VALUES ('sjc','4354369112');
INSERT INTO sk (kcbh,bh) VALUES ('cts','3461937823');
INSERT INTO sk (kcbh,bh) VALUES ('cwg','2522715615');
INSERT INTO sk (kcbh,bh) VALUES ('se','3987920115');
INSERT INTO sk (kcbh,bh) VALUES ('web','3460484429');
INSERT INTO sk (kcbh,bh) VALUES ('sjc','3798374147');
INSERT INTO sk (kcbh,bh) VALUES ('lla','3256131973');
INSERT INTO sk (kcbh,bh) VALUES ('qca','3460484429');
INSERT INTO sk (kcbh,bh) VALUES ('llx','4314185094');
INSERT INTO sk (kcbh,bh) VALUES ('sjm','3461937823');
INSERT INTO sk (kcbh,bh) VALUES ('zka','2522715615');
INSERT INTO sk (kcbh,bh) VALUES ('gl','4734180006');
INSERT INTO sk (kcbh,bh) VALUES ('mec','1854355842');
INSERT INTO sk (kcbh,bh) VALUES ('cts','1056527777');
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1243712858','sjm','2735290365',66.8);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1591604897','se','3987920115',63.7);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1887592443','em','1025678504',81.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1966924167','lla','3256131973',66.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1014159148','dsa','3769660116',80.8);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1437120165','vis','4155359299',88.7);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1482207116','zza','4550593619',85.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1851600341','gtr','1056527777',96.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1583270484','rbl','3987920115',96.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1452773186','zza','1531525387',62.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1309592697','cts','4057072620',96.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1139591738','dsa','3769660116',70.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1674025032','rbl','3746249085',70.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1305089848','qcb','4153167425',64.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1737803536','em','4155359299',79.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1223718463','em','4155359299',76.7);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1309592697','mec','4153167425',60.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1139432773','em','1025678504',63.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1778981777','sc','2880701633',68.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1977945439','mec','4153167425',80.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1242910341','cts','4057072620',62.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1685653695','sjm','3461937823',77.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1635046058','gtr','3769660116',53.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1014159148','gtr','4057072620',93.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1475176768','cwg','2880701633',89.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1139432773','dsa','1291237556',98.7);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1139591738','sc','2880701633',99.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1242910341','mec','1854355842',71.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1635046058','se','3987920115',68.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1183109618','gl','2735290365',84.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1380266671','cts','1056527777',87.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1370657918','zx','2522715615',88.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1380266671','zza','4548210474',88.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1153697599','qca','4548210474',89.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1242910341','syx','2735290365',63.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1163770916','gtr','4057072620',93.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1737803536','lla','1630619579',92.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1437120165','sjs','4354369112',95.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1604090165','qcb','4153167425',89.8);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1360232256','mec','1291237556',83.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1370657918','se','2803009978',95.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1139591738','qca','4548210474',98.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1309592697','gtr','1056527777',87.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1153697599','cwg','4309044143',70.7);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1537273456','web','4314185094',95.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1685653695','dsa','1291237556',66.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1183109618','sjc','4354369112',93.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1062541594','cwg','2522715615',79.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1778981777','qca','3460484429',62.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1305089848','web','3460484429',63.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1475176768','csb','3769660116',96.8);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1604090165','qca','4548210474',98.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1977945439','zka','2522715615',61.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1163770916','cwg','4309044143',67.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1802616482','sc','4057072620',94.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1635046058','qca','4548210474',36.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1438908157','llx','4314185094',74.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1635046058','csb','3769660116',88.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1014462526','dsa','3769660116',77.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1737803536','lla','3798374147',97.7);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1635046058','qca','3460484429',83.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1604090165','syx','2735290365',50.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1802616482','cts','3461937823',98.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1778981777','lla','1630619579',85.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1460856188','mea','4314185094',65.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1566662922','qcb','3769660116',84.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1887592443','cwg','2880701633',84.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1708102687','cwg','2880701633',68.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1460856188','sc','2880701633',83.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1778981777','lla','3256131973',77.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1527047399','qcb','4153167425',86.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1452773186','csb','4314185094',99.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1527047399','zka','4155359299',54.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1708102687','zka','3251367723',68.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1604090165','cwg','2522715615',80.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1452773186','em','4155359299',14.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1635046058','sjm','2735290365',66.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1460856188','se','2803009978',75.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1635046058','fxq','4734180006',66.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1977945439','gl','2735290365',59.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1737803536','gs','1630619579',18.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1233961404','sc','3798374147',72.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1685653695','xms','4354369112',79.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1658823100','gtr','3769660116',98.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1243712858','rbl','3987920115',93.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1851600341','web','3460484429',98.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1305089848','web','4309044143',61.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1438908157','cts','4057072620',45.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1413974679','rbl','3798374147',85.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1887592443','zka','4155359299',63.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1305089848','gtr','3769660116',87.8);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1139432773','mea','4314185094',89.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1708102687','fxq','4734180006',77.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1242910341','mec','4153167425',92.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1482207116','dsa','1291237556',74.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1153697599','zza','4550593619',60.7);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1223718463','gtr','3769660116',80.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1604090165','lla','1630619579',84.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1433528624','sjs','4548210474',72.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1223718463','zka','1854355842',76.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1139591738','mec','2803009978',90.8);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1163770916','sjm','2735290365',16.8);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1163770916','sjs','3798374147',63.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1887592443','qca','3460484429',86.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1243712858','web','4314185094',73.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1511297874','mec','1291237556',25.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1604090165','csa','2880701633',94.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1802616482','csb','3769660116',78.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1014159148','gl','3256131973',97.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1153697599','em','1025678504',74.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1977945439','sjm','3461937823',24.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1583270484','gtr','3460484429',95.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1380266671','se','2803009978',98.7);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1621741704','qca','4548210474',98.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1139591738','zx','2522715615',86.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1223718463','qca','3460484429',82.7);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1851600341','qcb','3460484429',74.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1242910341','lla','3798374147',84.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1163770916','zza','2546714455',94.7);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1966924167','rbl','3746249085',68.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1233961404','em','4155359299',83.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1014462526','gl','3256131973',73.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1181331372','gl','3256131973',71.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1566662922','zx','2522715615',98.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1370657918','sc','2179967243',93.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1566662922','qca','1531525387',71.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1566662922','fxq','4734180006',96.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1438908157','gl','4734180006',87.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1887592443','sc','3798374147',1.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1139591738','gl','4734180006',66.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1482207116','sc','3798374147',18.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1887592443','rbl','3798374147',92.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1685653695','zza','1531525387',85.7);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1737803536','xms','4354369112',90.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1437120165','cts','4057072620',98.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1243712858','zza','4548210474',78.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1757816083','fxq','3251367723',46.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1183109618','web','4309044143',87.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1887592443','zza','2546714455',84.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1183109618','zzb','3987920115',72.8);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1433528624','gtr','3769660116',47.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1977945439','lla','3256131973',66.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1757816083','csb','4314185094',97.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1413974679','qcb','3769660116',97.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1233961404','qca','1531525387',92.8);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1413974679','gl','2735290365',68.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1591604897','fxq','4734180006',83.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1370657918','sjm','3461937823',91.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1360232256','sjs','3798374147',95.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1511297874','zka','3251367723',83.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1233961404','zka','4155359299',80.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1778981777','djj','4734180006',83.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1223718463','gtr','3460484429',98.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1898230703','zka','4155359299',67.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1511297874','zza','4548210474',80.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1482207116','gtr','3460484429',51.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1438908157','xms','4354369112',99.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1737803536','sjm','3461937823',89.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1233961404','gl','3987920115',87.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1658823100','zka','4155359299',69.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1658823100','zza','4550593619',94.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1737803536','vis','4155359299',63.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1380266671','rbl','3798374147',86.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1163770916','gs','1630619579',86.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1966924167','fxq','3987920115',90.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1433528624','fxq','3251367723',93.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1360232256','lla','4309044143',77.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1243712858','cwg','2522715615',77.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1537273456','cyg','3251367723',90.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1475176768','rbl','3987920115',66.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1139432773','syx','2735290365',85.6);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1802616482','gs','1630619579',62.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1482207116','mec','1291237556',60.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1223718463','qcb','3769660116',84.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1635046058','mec','4153167425',81.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1163770916','mec','2179967243',92.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1183109618','rbl','4550593619',12.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1433528624','vis','4155359299',8.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1309592697','cwg','4309044143',63.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1972587625','zza','2546714455',68.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1972587625','gtr','3769660116',89.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1413974679','dsa','3769660116',90.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1674025032','csb','4153167425',75.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1139432773','sc','2880701633',5.3);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1014159148','web','4309044143',82.4);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1350749819','em','4155359299',89.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1413974679','cts','1056527777',84.7);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1511297874','zka','3798374147',94.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1977945439','web','4309044143',82.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1452773186','zza','4548210474',66.2);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1778981777','vis','4155359299',50.1);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1685653695','cwg','4309044143',64.5);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1635046058','lla','3256131973',69.7);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1163770916','zka','3798374147',52.8);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1243712858','gl','2735290365',64.9);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1233961404','cts','3461937823',90.8);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1181331372','gs','1630619579',99.9);
/*
Null Values:
*/
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Alice','女','1122334455',NULL,'08012003','10/05/1998');
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('徐庶','123456789','蜀',NULL);
/*
Special Values:
*/
/*Nardack:*/
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Nardack','女','341433','zy','08012001','11/19/1987');
INSERT INTO sk (kcbh,bh) VALUES ('cts','1025678504');
INSERT INTO sk (kcbh,bh) VALUES ('cts','1630619579');
INSERT INTO sk (kcbh,bh) VALUES ('cts','1531525387');
INSERT INTO sk (kcbh,bh) VALUES ('cts','1854355842');
INSERT INTO sk (kcbh,bh) VALUES ('cts','1291237556');
INSERT INTO sk (kcbh,bh) VALUES ('cts','2803009978');
INSERT INTO sk (kcbh,bh) VALUES ('cts','2179967243');
INSERT INTO sk (kcbh,bh) VALUES ('cts','2522715615');
INSERT INTO sk (kcbh,bh) VALUES ('cts','2735290365');
INSERT INTO sk (kcbh,bh) VALUES ('cts','2880701633');
INSERT INTO sk (kcbh,bh) VALUES ('cts','2546714455');
INSERT INTO sk (kcbh,bh) VALUES ('cts','3769660116');
INSERT INTO sk (kcbh,bh) VALUES ('cts','3460484429');
INSERT INTO sk (kcbh,bh) VALUES ('cts','3256131973');
INSERT INTO sk (kcbh,bh) VALUES ('cts','3987920115');
INSERT INTO sk (kcbh,bh) VALUES ('cts','3251367723');
INSERT INTO sk (kcbh,bh) VALUES ('cts','3348191361');
INSERT INTO sk (kcbh,bh) VALUES ('cts','3746249085');
INSERT INTO sk (kcbh,bh) VALUES ('cts','3798374147');
INSERT INTO sk (kcbh,bh) VALUES ('cts','4155359299');
INSERT INTO sk (kcbh,bh) VALUES ('cts','4309044143');
INSERT INTO sk (kcbh,bh) VALUES ('cts','4314185094');
INSERT INTO sk (kcbh,bh) VALUES ('cts','4550593619');
INSERT INTO sk (kcbh,bh) VALUES ('cts','4354369112');
INSERT INTO sk (kcbh,bh) VALUES ('cts','4734180006');
INSERT INTO sk (kcbh,bh) VALUES ('cts','4153167425');
INSERT INTO sk (kcbh,bh) VALUES ('cts','4548210474');
INSERT INTO sk (kcbh,bh) VALUES ('cts','123456789');
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','sc','2880701633',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','sc','3798374147',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','sjs','4548210474',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','xms','4354369112',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','sjm','2735290365',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','sc','4057072620',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','sm','4057072620',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','sjs','3798374147',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','sjs','4354369112',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','sc','2179967243',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','sjm','2880701633',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','zx','2522715615',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','sjc','4354369112',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','sjc','3798374147',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','sjm','3461937823',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','1025678504',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','1630619579',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','1531525387',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','1056527777',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','1854355842',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','1291237556',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','2803009978',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','2179967243',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','2522715615',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','2735290365',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','2880701633',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','2546714455',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','3769660116',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','3461937823',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','3460484429',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','3256131973',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','3987920115',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','3251367723',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','3348191361',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','3746249085',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','3798374147',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','4057072620',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','4155359299',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','4309044143',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','4314185094',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','4550593619',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','4354369112',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','4734180006',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','4153167425',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','4548210474',100.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('341433','cts','123456789',100.0);
/*Krenz Cushart:*/
INSERT INTO js (xm,jsbh,zc,ydh) VALUES ('Krenz Cushart','0',NULL,NULL);
/*Alice\Vegetable:*/
INSERT INTO xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Alice\Vegetable','女','1120200000','js','08012002',NULL);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','cts','1056527777',0.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','sc','2179967243',0.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','sjc','3798374147',0.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','sjm','2735290365',0.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','sjs','3798374147',0.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','sm','4057072620',0.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','xms','4354369112',0.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','zx','2522715615',0.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','csa','2880701633',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','csb','3769660116',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','csb','4153167425',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','csb','4314185094',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','djj','4155359299',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','djj','4734180006',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','dsa','1291237556',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','dsa','3769660116',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','se','2735290365',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','se','2803009978',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','se','3987920115',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','se','4057072620',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','vis','4155359299',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','web','3460484429',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','web','4309044143',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','web','4314185094',60.0);
INSERT INTO xk (xh,kcbh,jsbh,cj) VALUES ('1120200000','web','4734180006',60.0);
END;
在DataStudio里面执行以下语句，就可以将数据库恢复为这个实验的初始状态。
CALL proc_restore_database();
二：创建数据分区表
先创建schema，然后创建表空间，之后创建分区表并插入数据。
CREATE SCHEMA tpcds;
CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';
CREATE TABLE tpcds.web_returns_p2
(
ca_address_sk integer NOT NULL ,
ca_address_id character(16) NOT NULL ,
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20)
)
TABLESPACE example1
PARTITION BY RANGE (ca_address_sk)
(
PARTITION P1 VALUES LESS THAN(5000),
PARTITION P2 VALUES LESS THAN(10000),
PARTITION P3 VALUES LESS THAN(15000),
PARTITION P4 VALUES LESS THAN(20000),
PARTITION P5 VALUES LESS THAN(25000),
PARTITION P6 VALUES LESS THAN(30000),
PARTITION P7 VALUES LESS THAN(40000),
PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;
运行完上述语句后，我们会发现我们多了一个schema——“tpcds”，然后多了example1~4的表空间。
之后我们插入以下数据：
INSERT INTO tpcds.web_returns_p2 VALUES(1, 'a', 1, 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 1.0, 'a'), (2, 'b', 2, 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 1.1, 'b'), (5050, 'c', 300, 'c', 'c', 'c', 'c', 'c', 'c', 'c', 'c', 1.2, 'c'), (14888, 'd', 400, 'd', 'd', 'd', 'd', 'd', 'd', 'd', 'd', 1.5, 'd');
然后我们查看tpcds.web_returns_p2这个表，可以发现四个数据都已插入进去，同时tpcds.web_returns_p2这个表分成了8个不同的分区：
我们还可以查询单个分区内的数据。例如，如果我们要查询P1这个分区，可以使用以下命令：
SELECT * FROM tpcds.web_returns_p2 PARTITION (P1);
运行结果如下：
如果我们要删除对应的分区表和表空间，运行以下几个命令即可：
DROP TABLE tpcds.web_returns_p2;
DROP TABLESPACE example1;
DROP TABLESPACE example2;
DROP TABLESPACE example3;
DROP TABLESPACE example4;
同时，我们记得把tpcds这个schema也删掉：
DROP SCHEMA tpcds;
创建数据分区表的实验完成。
三：体会主键、外键约束
执行以下每个步骤前，先把数据库恢复到初始状态。
1. 练习更新、删除主表数据（针对主键属性且子表中可能有参照外键数据）
P1. 找出学号为“341433”的同学，将他的学号更新为“100000”，并更新和参照外键数据。
问题分析：
如果直接更新或者删除这些信息，则会报以下错误：
这是因为外键约束的存在，并且子表中已经有参照外键数据。
如果要让系统自动删除相应的外键数据，则应该使用级联更新、删除的方式。这个时候，需要我们去更改各个表的外键约束，使其支持级联更新和删除。
ALTER TABLE xs DROP CONSTRAINT IF EXISTS xs_fkey;
ALTER TABLE xs ADD CONSTRAINT xs_fkey FOREIGN KEY (ydh) REFERENCES xyb(ydh)
ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE js DROP CONSTRAINT IF EXISTS js_fkey;
ALTER TABLE js ADD CONSTRAINT js_fkey FOREIGN KEY (ydh) REFERENCES xyb(ydh)
ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE xk DROP CONSTRAINT IF EXISTS xk_fkey_1;
ALTER TABLE xk ADD CONSTRAINT xk_fkey_1 FOREIGN KEY (xh) REFERENCES xs(xh)
ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE xk DROP CONSTRAINT IF EXISTS xk_fkey_2;
ALTER TABLE xk ADD CONSTRAINT xk_fkey_2 FOREIGN KEY (kcbh,jsbh) REFERENCES sk(kcbh,bh)
ON UPDATE CASCADE ON DELETE CASCADE;
按照上述方式修改完约束后，对于第一个问题，我们执行以下命令即可：
UPDATE xs SET xh = '100000' WHERE xh = '341433';
执行完这条命令后，可知学号信息以及课程表对应的学号信息均进行了同步更改：
P2. 学号更新完之后，删除学号为“100000”同学的相关信息。
对于第二个问题，如果我们要删除这个同学的信息以及她的选课信息，只需要执行：
DELETE xs WHERE xh = '100000';
可知这个同学对应的学号信息和课程信息均已从数据库中移除。
2. 练习更新、删除主表数据（针对非主键属性）
P3. 寻找性别为“NULL”的数据，并将其赋值为“男”。
UPDATE xs SET xb = '男' WHERE xb ISNULL;
注意判断性别为空不能使用xb = NULL，而是应该使用xb ISNULL。
运行完这个语句之后，可知性别为“NULL”的数据已经全部删除。
P4. 寻找出生日期为“NULL”的数据，删除这些同学的信息，以及他们的选课信息。
DELETE xs WHERE chrq ISNULL;
在这个例子里面只有Alice\Vegetable的出生日期为空，因此影响只会作用在这条数据。
可知Alice\Vegetable的学生信息以及其选课信息全部被删除。
3. 练习先删除子表数据，再删除主表数据
P5. 删除设计与艺术学院（zy）的相关信息，并把属于设计与艺术学院的同学，老师，授课信息以及同学的选课信息删除。
问题分析：
这个操作不便使用级联删除执行，因此使用先删除子表数据，再删除主表数据的方法。
删除的步骤如下：
-
选择学院代号为zy的教师编号，然后将这些数据从xk表中删除；
-
选择学院代号为zy的学生编号，然后将这些数据从xk表中删除；
-
选择学院代号为zy的教师编号，然后将这些数据从sk表中删除；
-
删除学院编号为zy的教师；
-
删除学院编号为zy的学生；
-
删除学院编号为zy的学院；
依次执行以下语句：
DELETE xk WHERE xk.jsbh IN (SELECT jsbh FROM js WHERE ydh = 'zy');
DELETE xk WHERE xk.xh IN (SELECT xh FROM xs WHERE ydh = 'zy');
DELETE sk WHERE sk.bh IN (SELECT jsbh FROM js WHERE ydh = 'zy');
DELETE js WHERE ydh = 'zy';
DELETE xs WHERE ydh = 'zy';
DELETE xyb WHERE ydh = 'zy';
接着查询相关数据，可以看到学院代号为zy的教师，学生和选课信息均被删除：
这个操作的优点在于我们不需要修改我们的外键约束使其支持级联删除，缺点为操作步骤较为繁琐。
4. 使用子查询方式更新、删除数据
以下步骤要求我们的外键支持级联删除。
P6. 找出挂过科的同学（至少有一名课程成绩在小于60），将他们的班级信息更新为“08012048”。
问题分析：
这个问题的解决需要在更新语句中使用子查询，先查询曾经挂过科的同学的学号。
实验源码如下：
UPDATE xs SET bj = '08012048'
FROM (SELECT DISTINCT xh FROM xk WHERE cj < 60) AS subquery
WHERE xs.xh = subquery.xh;
运行后的结果如下：
可知包括Alice\Vegetable的挂过科的同学的班级已经全部更改为“08012048”。
P7. 找出挂过科的同学，并删除他们的对应数据；与此同时，对应的选课信息也被删除。
问题分析：
和P6类似，我们需要在删除语句中使用子查询。
DELETE xs WHERE xh IN (SELECT DISTINCT xh FROM xk WHERE cj < 60);
运行完上述语句后，可以发现挂过科的同学和选课信息都已被删除（特别留意Alice\Vegetable的数据）：
至此，对数据的更改和删除，对于主键、外键约束的实验正式完成。
四：体会索引
1. 查询计划
我们先将数据库恢复到初始状态，然后利用查询计划对(Explain)实验二的查询语句进行分析。
例如，我们对实验二中第10题的查询语句进行分析，只需要在前面加入EXPLAIN关键字即可：
EXPLAIN SELECT xm,xh,(CASE WHEN zxf = 0 THEN 0 ELSE ROUND(zcj/zxf,3) END) AS cj FROM(
SELECT xm,xh,SUM(xf) AS zxf,SUM(xf*cj) AS zcj FROM (
SELECT xm,xh,kcbh,NVL(xf,0) AS xf,NVL(MAX(cj),0) AS cj FROM (
SELECT xs.xm,xs.xh,xk.kcbh,kc.xf,xk.cj FROM (
xs LEFT JOIN xk ON (xs.xh = xk.xh) LEFT JOIN kc ON (xk.kcbh = kc.kcbh)
)
) GROUP BY (xm,xh,kcbh,xf)
) GROUP BY (xm,xh)
) ORDER BY cj DESC
LIMIT 10;
结果也会返回一个表，代表着这个程序的执行过程，称为查询计划：
在上图中，我们可以看到每一步运行的时间，从而可以判断哪一步是最需要优化的步骤。
接下来，我们观察去掉所有约束和主键索引后查询效率发生的变化：
DROP INDEX IF EXISTS xk_index;
DROP INDEX IF EXISTS xm_index;
ALTER TABLE js DROP CONSTRAINT IF EXISTS js_pkey CASCADE;
ALTER TABLE kc DROP CONSTRAINT IF EXISTS kc_pkey CASCADE;
ALTER TABLE sk DROP CONSTRAINT IF EXISTS sk_pkey CASCADE;
ALTER TABLE xk DROP CONSTRAINT IF EXISTS xk_pkey CASCADE;
ALTER TABLE xs DROP CONSTRAINT IF EXISTS xs_pkey CASCADE;
ALTER TABLE xyb DROP CONSTRAINT IF EXISTS xyb_pkey CASCADE;
可知建立索引与不建立索引，查询效率确实会发生较大的差异。如果建立适当的索引，数据库查询效率将会大幅度提高。
而索引提高数据查找的效率，当数据量非常大时将会变得非常显著。我们用以下的例子进行讲解：
我们先建立一个用于添加测试表以及插入数据的存储过程：
CREATE OR REPLACE PROCEDURE test_index AS
BEGIN
DROP TABLE IF EXISTS test_table;
DROP TABLE IF EXISTS test_table_2;
CREATE TABLE test_table(
id INTEGER PRIMARY KEY,
x DECIMAL(5,4)
);
CREATE TABLE test_table_2(
id INTEGER,
x DECIMAL(5,4)
);
DROP INDEX IF EXISTS t_index;
CREATE INDEX t_index ON test_table(x);
DO $$
BEGIN
FOR cnt IN 1..100000 LOOP
INSERT INTO test_table(id,x) VALUES (cnt,RANDOM());
INSERT INTO test_table_2(id,x) VALUES (cnt,RANDOM());
END LOOP;
END;
$$;
END;
其中test_table为含有索引的表，在id（系统会对主键自动建立索引）和x上建立索引，而test_table_2为不含索引的表，插入数据的操作使用for循环实现，每张表插入10万条数据。
之后我们调用这个存储过程即可：
CALL test_index();
之后，我们测试两张表分别执行相同查询语句所需时间，为了方便比较，分别重复执行1000次：
含有索引的表运行时间：30ms
不含有索引的表运行时间：24000ms
可知建立索引能够在数据量非常大的表中大大提高查询的效率。
而通过查询计划可以得知查询过程中的细节和预计消耗时间：
EXPLAIN SELECT * FROM test_table WHERE x = 0.5;
EXPLAIN SELECT * FROM test_table_2 WHERE x = 0.5;
可知含有索引的表使用的查询方式是Bitmap Heap Scan，属于索引查询，预计消耗时间（最坏情况）为40.39，而不含有索引的表是Sqc Scan，也就是逐个查询，预计消耗时间为1791.00，可知索引查询能够大大缩短查询时间。
五：权限管理
1. 以不同身份用户登录数据库建立表（表名一样）
我们使用putty连接数据库，进入到postgres数据库，并通过sql语句创建新的用户名，这里设为anmi：
CREATE USER anmi PASSWORD 'anmi@123';
之后使用\q退出数据库，之后执行以下命令：
gsql -d db_nardack -U anmi -p 26000 -W anmi@123
可知我们以另一名用户的身份登录了db_nardack这个数据库。
现在，我们建立一个和之前表名相同的xs表：
CREATE TABLE xs(
id INTEGER PRIMARY KEY,
x DECIMAL(5,4)
);
我们还可以通过Data Studio以anmi的身份登录这个数据库，这里的连接名称设为nardack_server_anmi：
之后，我们可以在对象浏览器中观察到用户模式中多了一个anmi，其中含有名称为xs的普通表：
2. 以不同身份用户查询自己与其他用户建立的表
我们在Data Studio中，先使用anmi这个用户访问自己和其他用户建立的xs表。
我们现在自己的xs表中插入一条数据，之后查询这个表：
INSERT INTO xs(id,x) VALUES (1,5);
SELECT * FROM xs;
我们发现直接执行这个查询语句查询的是anmi这个用户下建立的表。
那么，如何去访问其他用户（这个例子里面为public）建立的xs表呢？
SELECT * FROM public.xs;
但是如果直接执行这个语句，会报出permission_denied这个错误：
根本原因是该用户没有访问其他数据库的权限。
这时候我们在nardack_server这个连接的终端，执行以下语句，将sysadmin权限授权给anmi：
GRANT ALL PRIVILEGES TO anmi;
这时候anmi这个用户就可以正常查询public这个模式下的xs表：
如果要撤销anmi的权限，只需要执行以下语句：
REVOKE ALL PRIVILEGES FROM anmi;
可知撤销权限后anmi用户就不能访问public模式下的xs表。
在实验完成后，我们删除anmi这个用户以及这个用户下的表即可：
DROP USER anmi CASCADE;
3. 定义授权方案
我们构想这样一个情景：
假设Nardack有两位她最得意的学员krenz以及rella，而Nardack可以允许krenz以及rella查看自己的部分但不是全部的数据，对自己的数据没有增删改的权限，而krenz，rella拥有对方某个表表全部数据的增加和查询权限，无删除和修改权限，而对于其他表没有任何权限。
在这个实验中，具体的方案如下：
-
用户Nardack拥有对krenz，rella全部表的增删改查权限；
-
用户krenz、rella拥有对Nardack中xs表中xm,bj字段的查询权限，但没有增删改的权限；
-
用户krenz拥有对rella中xs表的增加和查询权限，无删除和修改权限，对于其他表没有任何权限；
-
用户rella对于krenz的表同样拥有上述权限；
为了确立这样一个授权方案，我们先创建krenz和rella两个用户：
CREATE USER krenz PASSWORD 'Krenz@cushart';
CREATE USER rella PASSWORD 'Rella@kinoko';
为了方便测试调试结果，需要在两个用户上均创建“学籍与成绩管理系统”的表。为方便起见，我们同时在两个用户上建立并运行proc_restore_database()存储过程。
执行这个操作之前需要在ds_nardack用户执行以下语句，对两位用户授予sysadmin权限：
GRANT ALL PRIVILEGES TO krenz;
GRANT ALL PRIVILEGES TO rella;
然后再两个用户上均创建并执行proc_restore_database()存储过程。
之后，在ds_nardack这个用户下执行以下语句，撤销两位用户的sysadmin权限：
REVOKE ALL PRIVILEGES FROM krenz;
REVOKE ALL PRIVILEGES FROM rella;
再执行以下语句，为每个用户赋予题设中的权限。由于ds_nardack本身为系统管理员，因此不需要给他再修改任何权限：
GRANT SELECT (xm,bj) ON xs TO rella,krenz;
REVOKE INSERT,UPDATE,DELETE ON xs FROM rella,krenz;
GRANT USAGE ON SCHEMA rella TO krenz;
GRANT USAGE ON SCHEMA krenz TO rella;
GRANT SELECT,INSERT ON krenz.xs TO rella;
GRANT SELECT,INSERT ON rella.xs TO krenz;
4. 对授权方案进行验证：
我们先对krenz和rella对于ds_nardack的访问权限进行验证。两个用户都执行以下语句：
SELECT xm,bj FROM public.xs;
SELECT xh FROM public.xs;
可以发现两个用户均是只有第一个语句可以执行，而第二个语句因为没有对xh这一列的访问权限所以会报错：
之后，我们再检验一下对于public.xs的增删改权限。两个用户下都执行以下语句：
INSERT INTO public.xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Test','女','0','zy','0',NULL);
UPDATE public.xs SET xm = 'Nardack_2' WHERE xh = '341433';
DELETE FROM public.xs WHERE xh = '341433';
可知两个用户执行所有语句都会报错：
之后，我们验证rella与krenz之间的授权方案。
在krenz用户下运行以下语句：
SELECT * FROM rella.xs;
INSERT INTO rella.xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Test','女','0','zy','0',NULL);
UPDATE rella.xs SET xm = 'Nardack_2' WHERE xh = '341433';
DELETE FROM rella.xs WHERE xh = '341433';
可知第一二句正常运行，而第三四句出现permission denied错误：
在rella用户下运行以下语句：
SELECT * FROM krenz.xs;
INSERT INTO krenz.xs (xm,xb,xh,ydh,bj,chrq) VALUES ('Test','女','0','zy','0',NULL);
UPDATE krenz.xs SET xm = 'Nardack_2' WHERE xh = '341433';
DELETE FROM krenz.xs WHERE xh = '341433';
仍然得到了和在krenz用户下运行类似的结果：
最后，我们再在ds_nardack用户下运行以下语句，判断test数据是否添加到了rella.xs,krenz.xs这两张表中：
SELECT * FROM rella.xs;
SELECT * FROM krenz.xs;
根据运行结果可知插入已经成功，从而完成了对于上述授权方案的验证：
至此，权限管理，授权方案的建立和验证的实验正式完成。
1764
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
