本帖最后由 why 于 2020-9-21 12:20 编辑
第13章 MySQL数据表设计
数据表是数据库中的基本对象元素,以记录(行)和字段(列)组成的二维结构用于存储数据.
数据表由表结构和表内容两部分组成,先建立表结构,然后才能输入数据
数据表结构设计主要包括字段名称,字段类型和字段属性的设置
数据字段属性:
unsigned:该属性只能用于设置数值类型,不允许数据列出现负数
zerofill:该属性也只能用于设置数值类型,在数值之前(左端)自动用0补齐不足的位数
auto_increment:该属性用于设置字段的自动增量属性,当数值类型的字段设置为自动增量时,每增加一条新记录,该字段的值就自动增加1,且此字段不允许重复
NULL和not NULL:默认为NULL,即插入值时没有在此字段插入值,如果指定了not NULL,则必须在插入值时在此字段填入值
default:可以通过此属性来指定一个默认的值,如果没有在此列添加值,那么默认添加这个默认值作为值
创建一个用于存储用户信息表users
create table users (
id int(10) unsigned not NULL auto_increment,
username varchar(50) not NULL,
userpass varchar(50) not NULL,
telno varchar(20) not NULL,
sex enum('男','女')not NULL default '男',
brithday date not NULL default '2000-01-01', //'2000-01-01'这里在5.7版本的MySQL中值不能为0
primary key(id),
index user_username(username,userpass)
)ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
//ENGINE=MYISAM设置默认表类型为MYISAM
//CHARSET=utf8设置字符集为utf8,因为表里出现了中文
//COLLATE utf8_general_ci校对规则
//书上用的<>写的语句,但是在5.7版本的MySQL中这样写不行,换成()就可以了
desc命令查询结果:
为指定的数据表添加一个新字段,语法格式如下所示:
alter table 表名 add 字段名(建表语句)[first|after列名]; //在某列之前|之后添加一个新字符
为上表条件一个Email字段:
alter table users add email varchar(30) not null;
在第一列前面添加一个真实姓名(name)的新列,列类型为字符串,属性设置为非空
alter table users add name varchar(30) not null first;
在原有字段userpass之后添加一个身高(height)的新列,类型为double,属性为非空并设置默认值为0.00
alter table users add height double not null default '0.00' after userpass;
删除表的一个字段
alter table cats drop pid;
为指定的数据表更改原有字段的类型,可使用change或modify语句.如果原列的名字和新列的名字相同,则change和modify的作用相同
alter table change(modify)列名(建表语句)
将user表中的电话号码字段telNo类型varchar(20)更改为数值类型int,并将默认值设置为0
alter table users modify telno int unsigned default '0';
再用change将字段telNo类型int型修改回varchar(20)类型
alter table users change telno(原字段名) phone(新字段名) varchar(20)(类型); //change修改类型的时候需要连原字段名一起修改
为指定的数据表重新命名:
alter table 旧表名 rename as 新表名
为users重新命名为userss:
alter table uerss rename as userss;
当某个数据表不再需要时,可以使用drop table语句删除,语法如下:
drop table [if exists]表名
[if exists]当不能确定数据表是否存在,如果存在就删除它,不存在则在删除时也不希望出现错误,添加[if exists]语句后如果表不存在,脚本也会向下继续执行而不会抛出错误
创建一个新表的时候,可以通过engine或type选项决定数据表类型
主键索引(primary key)是关系数据库中最常见的额索引类型,主要作用是确定数据表里一条特定的数据记录的位置.数据表会根据主键的唯一性来唯一标识每条记录,任意两条记录里的主键字段不允许是同样的内容.一个表只能指定一个主键,且主键的值不能为空
分别创建两个数据表,并为每个表的id指定主键:
CREATE TABLE cats(
-> cid int not NULL auto_increment primary key,
-> catname varchar(15)not NULL
-> );
CREATE TABLE books(
-> bid int(10) unsigned not null auto_increment,
-> cid mediumint(8) not null,
-> bookname varchar(50) not null,
-> price double not null,
-> detail text not null,
-> primary key(bid)
-> );
唯一索引(unique)与主键索引一样,都可以防止创建重复的值.不同之处在于,每个数据表只能有一个主键索引,但可以有多个唯一索引,如果能确定某个数据列将只包含彼此不相同的值,在位这个数据创建索引时就应该使用关键字unique把它定义为一个唯一索引.这样在有新记录插入时,就会自动检查新记录的这个字段的值,是否已经存在某个现有记录的这个字段里出现过,如果是,MySQL将拒绝插入这条新记录,唯一索引的目的往往只是为了避免数据出现重复
常规索引(index)是关系数据查询中最重要的技术,如果要提升数据库的性能,索引优化是首先应该考虑的,因为它能使我们的数据库得到最大性能方面的提升.如果没有索引的数据表,就没有排序的数据集合,要查询数据,就需要全表扫描.有索引的表是一个在索引列上排序了的数据表,可以通过索引快速定位记录.在myisam数据表中数据行保存在数据文件中,索引保存在索引文件中
创建表carts时,随表一同为uid和bid创建一个名为ind的索引,如下:
CREATE TABLE carts(
cid int(10) not null auto_increment,
uid int(10) not null,
bid int(10) not null,
num int(10) not null,
primary key(cid),
key ind(uid,bid) //创建一个名为ind的常规索引,key通常是index的同义词
);
如果未给出索引名ind,系统会根据第一个索引列的名称自动选一个(一般使用"表名_列表"为索引命名).如果在创建表时没有创建索引,就需要使用create index命令来创建同样的常规索引,如下所示:
create index ind on carts(uid,bid); //创建名称为in对的索引为carts表的两个列
创建索引后,可以通过show index from carts命令为表carst生成一份索引的清单,
show index from carts; //索引生成命令也能查看索引情况
还可以使用drop index ind on carts命令删除索引
全文索引(fulltext)