数据库 第二版 131 答案解析

1、按要求对yggl库建立相关索引
USE yggl; 
1
-- 1.1、
CREATE     INDEX     depart_ind
        ON employees(departmentID);

-- 1.2、
CREATE     INDEX     As_ind
        ON employees(NAME,  address);

-- 1.3、
CREATE     UNIQUE    INDEX     ones
        ON departments(departmentName);
1
2
3
4
5
6
7
8
9
10
11
-- 2.1、
ALTER     TABLE     employees
        ADD        UNIQUE     date_ind(birthday),
        ADD     INDEX        na_ind(NAME,     sex);

-- 2.2、
ALTER     TABLE     departments
        ADD     PRIMARY KEY(departmentID);
1
2
3
4
5
6
7
8
-- 3、
CREATE     TABLE     cpk(

        产品编号    CHAR(20)    NOT NULL,
        产品名称    CHAR(20)    NOT NULL,
        单价            INT(10)  ,
        库存量        INT(20)     ,

        PRIMARY KEY(产品编号),
        INDEX     cpk_fh(库存量,    单价)
);
1
2
3
4
5
6
7
8
9
10
11
2、显示employees表的索引情况
USE     yggl;
1
-- 1
CREATE TABLE     jj(
    employeeid    CHAR(6)        NOT NULL     PRIMARY KEY,
    je        INT(6)        NOT NULL,

    FOREIGN KEY ( employeeid )
    REFERENCES         employees(employeeid)
        ON         DELETE    CASCADE
        ON         UPDATE    CASCADE
)ENGINE=INNODB  DEFAULT CHARSET=utf8;        /*因为外键的编码为utf-8，所以设置为utf-8*/
1
2
3
4
5
6
7
8
9
10
-- 2
CREATE TABLE     EMP(
    工号    CHAR(6)        NOT NULL,
    性别    CHAR(2)        NOT NULL     CHECK(性别    IN("男", "女")  )
);

1
2
3
4
5
6
-- 3
CREATE TABLE     EMP_1(
    工号        CHAR(6)        NOT NULL,
    出生日期    DATE        NOT NULL     CHECK(出生日期    > "1980-01-01")
);
1
2
3
4
5
-- 4
CREATE TABLE     EMP_2(
    工号    CHAR(6)        NOT NULL,
    性别    CHAR(2)        NOT NULL     CHECK(SELECT  性别    FROM  EMP)
);
1
2
3
4
5
-- 5
CREATE TABLE     EMP(
    工号    CHAR(6)        NOT NULL,
    工资    INT(1)            NULL,
    扣款    INT(1)            NULL,
    
    CHECK( 工资>扣款)
);

1
2
3
4
5
6
7
8
9

思考7
1、简答题

底部
1、
添加索引的话，首先去索引列表中查询，而我们的索引列表是B类树的数据结构，
查询的时间复杂度为O(log2N)，定位到特定值得行就会非常快，所以其查询速度就会非常快。 
1
2
3
2、    
影响：    减少查询的记录数，达到提高查询速度的目的。
原因：    如果查询时不使用索引，那么查询语句将查询表中的所有字段。
        这样查询的速度会很慢。使用索引进行查询，查询语句不必读完表中的所有记录，而只查询索引字段。 
 
弊端：    需要增加IO和调整索引所计算的量。
原因：    索引占用存储空间，随着table数据里增长，索引数据量也会增长，带来存储空间的消耗。
1
2
3
4
5
6
7
3、    
数据完整性约束种类：
    主键约束、外键约束、唯一约束、非空约束、自增约束、默认值约束 
    
代码实现：
    a)    主键约束 primary key
        主键约束相当于   唯一约束 + 非空约束  的组合，主键约束列不允许重复，也不允许出现空值。
        每个表最多只允许一个主键。 

实现：
    基本模式：
        create table 表名(
                id int primary key,
                name char
        );
    b)    外键约束foreign key
        外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系.
实现：
    基本模式：
        create table 表2(
                id int,
                name varchar(20),
                classes_id int,
                foreign key(id) references 表1(id)
        );
    c)    唯一约束unique
        唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。
实现：
    创建表时设置
        create table 表名(
                id int not null ,
                name varchar(20),
                password varchar(10),
                unique(name,password)
        );
 

    d)    非空约束not null
        非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
实现：
    创建table表，ID 为非空约束，name 为非空约束 且默认值为abc
        create table 表名(
                id int not null,
                age int not null default 34
        )；
    e)    自增auto_increment
        当插入第一条记录时，自增字段没有给定一个具体值，可以写成DEFAULT/NULL，那么以后插入字段的时候，
        该自增字段就是从1开始，每插入一条记录，该自增字段的值增加1。
        当插入第一条记录时，给自增字段一个具体值，那么以后插入的记录在此自增字段上的值，
        就在第一条记录该自增字段的值的基础上每次增加1。
实现：
        create table 表名(
            id int primary key auto_increment,
        );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

2、写SQL语句

底部
USE     xscj;
1
/*************
 * 备份
 */
/*复制表数据与结构时，不会复制主键*/
CREATE TABLE     xs_kc_copy        SELECT * FROM xs_kc;

/*添加主键*/
ALTER     TABLE    xs_kc_copy
    ADD     PRIMARY KEY(学号, 课程号);

/*复制表数据与结构时，不会复制主键*/
CREATE TABLE     xs_copy     SELECT * FROM xs;

/*添加主键* /
ALTER     TABLE    xs_copy
    ADD     PRIMARY KEY(学号);/*题目后面需要添加主键* /
*/    

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*1、*/
CREATE     INDEX     XH_XS/*索引名*/
    ON xs_copy(学号(5) ASC);
1
2
3
/*2、*/
CREATE     INDEX     XSKC_IN
    ON xs_kc_copy(学号,  课程号);
1
2
3
/*3、非唯一索引：就是  普通索引*/
ALTER     TABLE     xs_copy
    ADD INDEX     NAME(姓名) ;
1
2
3
/*4、*/
ALTER     TABLE    xs_copy
    ADD     PRIMARY KEY(学号),/*添加主键，若主键存在，则报错*/
    ADD    INDEX     mark(学号);/*与第6题配合*/
1
2
3
4
/*5、*/
CREATE     TABLE     IF NOT EXISTS     XS_KC_DEMO(
    学号     CHAR(6)     NOT NULL,    
    课程号    CHAR(3)        NOT NULL,
    成绩    TINYINT(1)        NULL,
    学分    TINYINT(1)        NULL,
    
    PRIMARY KEY (学号, 课程号),
    INDEX     cj(成绩)
);
1
2
3
4
5
6
7
8
9
10
/*6、*/
ALTER     TABLE    xs_copy
    DROP     PRIMARY KEY,
    DROP    INDEX mark;/*与第4题配合*/
    
1
2
3
4
5
/*7、*/    
DELETE  FROM  xs_copy 
    WHERE     (姓名="王林"  AND 专业名="通讯工程");   /*删除重复的王林行，因为表中王林名字不唯一*/
    
ALTER     TABLE    xs_copy
    ADD    UNIQUE (姓名);

1
2
3
4
5
6
7
/*8、*/
ALTER     TABLE    xs_copy
    ADD     PRIMARY KEY(学号);/*若主键没有的情况下，可执行此语句，进行主键的添加*/
    
/*关联外键的数据结构必须一样，如：学号为主键，外键也必须为主键。
 * 否则创建下一行语句执行失败！*/
CREATE     TABLE     IF NOT EXISTS     xs_demo(
    学号     CHAR(6)     NOT NULL  PRIMARY KEY ,

    FOREIGN KEY (学号)
        REFERENCES     xs_copy (学号)
            ON     DELETE    RESTRICT
            ON     UPDATE     RESTRICT
)ENGINE = INNODB;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*9、*/
CREATE     TABLE     IF NOT EXISTS     XS_demo1(
    学号        CHAR(6)        NOT NULL ,
    姓名        CHAR(8)        NOT NULL,
    出生日期    DATE         NOT NULL,    
    PRIMARY KEY (学号),
    
    FOREIGN KEY (学号)
        REFERENCES     xs_copy (学号)
            ON     UPDATE     CASCADE
);