MySQL数据库技术 第二版 章末 答案—单元7
以下是我个人所尝试过得答案,若有不正确的地方请告知,谢谢!
由于实在是太多内容了,原谅我省略题目内容
单元7 索引与数据完整性约束的创建 ---- P131-P132
实训7
1、按要求对yggl库建立相关索引
USE yggl;
-- 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);
-- 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);
-- 3、
CREATE TABLE cpk(
产品编号 CHAR(20) NOT NULL,
产品名称 CHAR(20) NOT NULL,
单价 INT(10) ,
库存量 INT(20) ,
PRIMARY KEY(产品编号),
INDEX cpk_fh(库存量, 单价)
);
2、显示employees表的索引情况
USE yggl;
-- 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*/
-- 2
CREATE TABLE EMP(
工号 CHAR(6) NOT NULL,
性别 CHAR(2) NOT NULL CHECK(性别 IN("男", "女") )
);
-- 3
CREATE TABLE EMP_1(
工号 CHAR(6) NOT NULL,
出生日期 DATE NOT NULL CHECK(出生日期 > "1980-01-01")
);
-- 4
CREATE TABLE EMP_2(
工号 CHAR(6) NOT NULL,
性别 CHAR(2) NOT NULL CHECK(SELECT 性别 FROM EMP)
);
-- 5
CREATE TABLE EMP(
工号 CHAR(6) NOT NULL,
工资 INT(1) NULL,
扣款 INT(1) NULL,
CHECK( 工资>扣款)
);
思考7
1、简答题
1、
添加索引的话,首先去索引列表中查询,而我们的索引列表是B类树的数据结构,
查询的时间复杂度为O(log2N),定位到特定值得行就会非常快,所以其查询速度就会非常快。
2、
影响: 减少查询的记录数,达到提高查询速度的目的。
原因: 如果查询时不使用索引,那么查询语句将查询表中的所有字段。
这样查询的速度会很慢。使用索引进行查询,查询语句不必读完表中的所有记录,而只查询索引字段。
弊端: 需要增加IO和调整索引所计算的量。
原因: 索引占用存储空间,随着table数据里增长,索引数据量也会增长,带来存储空间的消耗。
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,
);
2、写SQL语句
USE xscj;
/*************
* 备份
*/
/*复制表数据与结构时,不会复制主键*/
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、*/
CREATE INDEX XH_XS/*索引名*/
ON xs_copy(学号(5) ASC);
/*2、*/
CREATE INDEX XSKC_IN
ON xs_kc_copy(学号, 课程号);
/*3、非唯一索引:就是 普通索引*/
ALTER TABLE xs_copy
ADD INDEX NAME(姓名) ;
/*4、*/
ALTER TABLE xs_copy
ADD PRIMARY KEY(学号),/*添加主键,若主键存在,则报错*/
ADD INDEX mark(学号);/*与第6题配合*/
/*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(成绩)
);
/*6、*/
ALTER TABLE xs_copy
DROP PRIMARY KEY,
DROP INDEX mark;/*与第4题配合*/
/*7、*/
DELETE FROM xs_copy
WHERE (姓名="王林" AND 专业名="通讯工程"); /*删除重复的王林行,因为表中王林名字不唯一*/
ALTER TABLE xs_copy
ADD UNIQUE (姓名);
/*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;
/*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
);