一、表的分类和设计

表的概述：组成数据库的基本元素，由若干列组成，优劣影响使用效率内存利用率数据查询率

分类：

表按照存储分为永久表和临时表

按照用途分类分为系统表用户表和分区表

设计：

表的数据库对象包含列、索引、触发器

数据在表中按行和列格式组织行表示记录，列表示字段

设计需要达到3NF规范

满足有效性完整性一致性的约束

MySQL 数据类型

整数类型：tinyint（1字节）,smallint（2）,mediumint（3）, integet/int（4），bigint（8）(整数范围越取越大)

浮点数和定点数类型:float（单精度浮点数）,double（双精度浮点数，精度更高）,declmal(定点数类型)

Mediumint unsigned是无符号数，不能存负数。

日期和时间类型：year（年）time（时分秒）,date（日期年月日）,

datetime（日期，年月日 时分秒）存储范围大用datetime,timestamp（时间时间戳）,默认情况下设置为当前时间。

字符串类型:char（长度固定）,varchar（长度不固定）,

Char(5)表示五个字节，不足自动空格补齐。Varchar（5）表示最大五个字节，不需要补齐。

Char  varchar和nchar nvarchar 的区别是前者用阿里的？，后者用utf-8，字符集不同，磁盘长度不同。同样字符集，字节长度后者是前者的一倍。但后者可国际化。

TEXT类型：长度较大的字符串的一种

ENUM（枚举类型）,

单选字符串数据类型，取值范围以列表形式指定：属性名 ENUM（‘值1’，‘值2’……‘值n’）

允许为空，not null则默认值为表的第一个元素

学历性别这种可以选枚举。

Set多选字符串数据类型。给定多个固定值，从中选择几个作为存储项。列表当中的多个值

二进制数据类型:

Binary（M）M是字节数，允许长度为0到M定长二进制的字符串

varbinary（M）字节数为值的长度加一, 允许长度为0到M变长二进制的字符串

bit(M),M位二进制数据，M最大值64

tinyblob：可变的二进制数据，最多2的8次方减1个字节

blob：可变的二进制数据，最多2的16次方减1个字节

mediumblob：可变的二进制数据，最多2的24次方减1个字节

longblob：可变的二进制数据，最多2的32次方减1个字节

Json类型:轻量级数据交换格式，字符串形式存储，无法设置默认值

存储有两种类型：创建JSON类型存储数组,包括在[]之间

[“abc”,10,null,true,false]

创建JSON存储对象，放在{}中。用键值对的形式，键值对都用逗号隔开。除数值外，键都加双引号

{“k1”:“value””k2”:10}

示例：JSON数组和对象嵌套值。

[10,{“id”:”NO1001”,”price”:32.45},[“active”,”stop”]]{“k1”:”value”,”k2”,[10.20]}

选择数据类型：在任何情况下均应使用最精确的类型，即在所有可以表示该列值的类型中，该类型使用的存储空间最少。

①整数和浮点数的选择，存储字节都4个字节，不包含小数部分则整数，包含小数部分则浮点数。

②浮点数和定点数的选择，长度一定情况下，浮点数能表示更大的数据范围。精度要求更高的金融建议使用decimal，浮点数容易产生误差。

③Char处理速度更快，但更浪费存储空间。    对于innoDB存储引擎，不分固定长度和可变长度，选择Varchar更省空间。

二、表的创建和管理

创建数据库表：

表的定义

create [temporary表示新建的表为临时表]table [if not exists表示此表不存在时才新建]<表名>

[(<字段名><数据类型>[完整性约束条件]

......

<字段名><数据类型>[完整性约束条件])]

{表的选项}表的选项用于描述存储引擎、字符集等选项

字段具体定义

字段名 数据类型 [not null |null ]是否为空值[default default_value]指定默认值

[auto_increment]值是否自增长[unique [key]唯一性约束|[primary]key]主键约束

[comment 'string']注释字段名,一般放在最后。[reference_definition]约束定义

例子：

表1

create table if not exists department(

deptno int(2) primary key comment '部门编号',

 

dname varchar(14) not null comment '部门名称',

loc varchar(13) comment '地址');

表2

create table if not exists employee(

empno int (4) not null auto_increment comment '员工编号',

ename char(8) not null comment'员工姓名',

job varchar(20) not null comment '职位',

mgr int(4) comment '经理',

hiredate date not null comment '雇佣日期',

sal float(7,2) not null comment '工资',

comm varchar(20) comment '备注',

deptno int (2) not null comment '部门编号',

constraint pk_emp primary key (empno),

constraint fk_emp_dept foreign key (deptno) references department (deptno)

)engine=innodb default charset=utf8mb4;

 

查看表

查看数据库中所有标的信息

Show tables;

 

查看表结构

{describe|desc}<表名>[字段名]

例：desc employee;

查看表结构的详细定义

Show create table employee;

 

修改表

 

修改表名，修改字段数据类型，修改字段名，增加字段，删除字段，修改字段排列位置，更改默认存储引擎，删除完整性约束等

例：

添加字段city,类型varchar，长度100

alter table department add city varchar(100);

 

删除表：

删除数据表中已存在的表

Drop table[if exists]<表名>[,<表名>…]

示例：drop table dept；

注：数据存在外键关联情况下，直接删除父表（即主表，外键的来源表）会失败。要先删除子表，比如员工表和部门表，要先删除员工表，再删除部门表。

 

临时表管理：临时表用于存储中间结果集，使用temporary关键词创建

例：查询结果集创建临时表create temporary table tmp_emp AS(

Select*from employee limit 0,100);

查询临时表：select *from tmp_emp;

只是在当前会话链接生效。默认自动销毁，也可使用drop table 表名；手动删除临时表。例：drop table tmp_emp;

 

3表的完整性约束

完整性约束分类、创建及管理

完整性约束条件包括：实体完整性，参照完整性和用户自定义完整性

完整性约束：

Not null(非空约束)：null（允许空值）表示数值未确定，并不是数字“0”或者字符“空格”，表中比较两个空值，或空值和任类型的值结果为空值。

Not null（不允许为空值）：为了确保数据列中必须包含有意义的值，输入空值将不会被收入表中。

Primary key(主键约束)：快速查找表中指定信息，自动产生主键索引，可定义在一列或多列，定义在多列叫复合主键，一个表中只定义一个主键约束，唯一的标识表中的每一行记录。主键约束中的列不允许取空值。

Unique（唯一性约束）: 唯一性是指所有记录中该字段的值不能重复出现，一个数据表可以有若干个unique约束，唯一性约束字段的值可取null，但是必须使用null或not null声明创建unique约束时，系统自动产生unique索引。

Foreign key(外键约束)：表与表之间父子关系。如：字段deptno是表employee的属性且依赖于表department的主键deptno。那么称表department为父表，表employee为子表。外键作用建立子表与其父表的关联联系，保证子表与父表关联的数据一致性。父表中更新或删除某条信息时，子表中与之对应的信息也必须有相应的改变。设置外键的原则：必须依赖于同一个数据库中已存在的父表的主键；外键可以为空值。

Check（检查约束）：检查约束用于对具体数据环境与应用环境设置的约束，反应具体应用中数据的语义要求，如，性别字段只能取值男或女。检查约束多用于一些不属于实体完整性和参照完整性的业务规则，是用户自定义完整性的实现方式之一。约束条件使用表达式表示，在表达式中引用相应的列，并且表达式结果必须是一个布尔值。

 

创建约束

根据创建形式分为：表级约束和列级约束

列级约束定义在字段类型后，用于指定此列的约束条件；表级约束定义在所有字段类型定义结束后，用于指定某列或多列的组和约束条件

非空约束和DEFAULT默认值只能用于列级约束，除此之外的其他约束即可用于列级约束也可用于表级约束，但外键约束列级无效

如果需要定义多列组合的约束，则只能定义为表级约束。如联合主键、多列间的检查性约束等

表的完整性约束可以在表创建时指定，也可以在表创建后，通过命令语句进行添加和维护

 

表级约束：

column [[not] null]|[unique]|[primary key ]|[check(condition)]

列级约束：

constraint 约束名称

[primary key(column1[,column2...])]

[unique(column1[,column2...])]|

[check(condition)]|

[foreign key (column1[,column2...])

references [tableName[(column[,column2...])]]]

例:职位表，添加列级约束

CREATE TABLE IF NOT EXISTS job (

    jobid int(2)primary key ,-- 主键约束

    jobname varchar(20) unique,-- 唯一性约束

    jobdesc varchar(255)  not null,-- 非空约束

    workplace varchar(20),

    minsalary float (9,2) check (minsalary >=0),-- 检查性约束

    maxsalary float (9,2) check (maxsalary>=0 and maxsalary<=1000000 ),

    deptno int (4) not  null ,-- 非空约束

-- 外键约束

    constraint fk_job_dept foreign key (deptno) references department (deptno)

    );

 

创建工资发放记录表，添加表级约束

    create table if not exists payroll (

    empno int (4),

    payrolldate date not null ,-- 非空约束

    deptno int (2),

    sal float (9,2),

    comm float (9,2),

-- ­­­­­复合主键约束

    constraint pk_payroll primary key (empno,payrolldate),

-- 外键约束

CONSTRAINT fk_empno foreign key (empno) references employee(empno),

    CONSTRAINT fk_dept foreign key (deptno) references employee(deptno)

    );

创建员工项目表，添加表级约束

Create table if not exists emp_project (

Empid int not null,

Projected int not null,

Primary key (rmpid,projrctid )—复合主键约束

)

主键如果是单一字段，可以使表级约束，也可以是列级约束。

当表中的主键为复合主键时，只能定义为表级约束

 

查看约束是否创建成功，

通过information_schema库的TABLE_CONSTRAINT表来查看

示例：

Select *from information_schrma .table_constaints

Where table_name =’job’ and table_schema=’mydb’；

 

维护约束

表创建完成后，可通过ALTER命令维护约束

示例：

添加主键约束

ALTER TABLE employee ADD primary key (empno);

添加外键约束               

ALTER TABLE job ADD CONSTRAINT fk_job_dept

FOREIGN KEY (deptno) REFERENCES department(deptno);

添加检查约束

ALTER TABLE job ADD CONSTRATINT ck_job_salary CHECK(maxsalary)>minsalary;

添加非空约束

ALTER TABLE job MODIFY jobname VARCHAR(20) NOT NULL;

使用ALTER命令修改约束

示例：修改主键约束，将主键由empno改为ename。先将主键删除

ALTER TABLE employee DROP PRIMARY KEY;

ALTER TABLE employee ADD PRIMARY KEY(ename);

对外键约束fk_job_dept重命名（需先删除再添加）

ALTER TABLE job

DROP FOREIGN KEY fk_job_dept,

ADD CONSTRAINT fk_jobdept FOREIGN KEY(deptno)

REFERENCES department(deptno);

使用ALTER命令删除约束

示例：删除主键约束

ALTER TABLE employee DROP PRIMARY KEY;

用过名称删除外键约束

ALTER TABLE job DROP CONSTRAINT fk_jobdept;

删除检查约束

ALTER table job DROP CONSTRAINT ck_job_salary;

通过名称删除唯一性约束

ALTER TABLE job ADD CONSTRAINT u_job_jobname UNIQUE KEY(jobname)

ALTER TABLE job DROP CONSTRAINT u_job_jobname;

外键约束对关联表的处理

对于外键约束，可通过其定义方式来指定外键来源表在执行记录删除、更新时，如何处理外键关联表的记录

语法：

ALTER TABLE tbl_name

ADD CONSTRAINT 约束名称 FOREIGN KEY(col_name)

REFERENCES tbl_name (col_name)

ON DELETE reference_option

ON UPDATE reference_option

其中，on delete reference_option用于指定在删除父表记录时如何处理子表记录

On update reference_option用于指定在更新父表记录时如何处理子表记录

Reference_option取值：restrict、no action、cascade、set null

ON DELETE条件的取值含义

restrict(约束)：当在父表(即外键的来源表)中删除对应记录时，首先检查该记

录是否有对应外键，如果有则不允许删除。

no action:意思同restrict，即如果存在从数据，不允许删除主数据。只有在个别的情况下会导致区别，no action是在其他约束的动作之后执行，restrict具有最高的优先权执行。

cascade(级联)：当在父表(即外键的来源表)中删除对应记录时，首先检查该记录是否有对应外键，如果有，则也删除外键在子表(即包含外键的表)中的记录。set null(空值)：当在父表(即外键的来源表)中删除对应记录时，首先检查该记录是否有对应外键，如果有，则设置子表中该外键值为null(要求该外键允许取null)。

ON UPDATE条件的取值含义
restrict(约束)：当在父表(即外键的来源表)中更新对应记录时，首先检查该
记录是否有对应外键，如果有则不允许更新。
no action:意思同restrict。只有在个别的情况下会导致区别，no action是在其他约束的动作之后执行，restrict具有最高的优先权执行。
cascade（级联）：当在父表（即外键的来源表）中更新对应记录时，首先检查该记录是否有对应外键，如果有，则也更新外键在子表（即包含外键的表）中的k记录。
set null(空值)：当在父表(即外键的来源表)中更新对应记录时，首先检查该记录是否有对应外键，如果有，则设置子表中该外键值为null(要求该外键允许取null)

外键约束默认定义方式：

ALTER TABLE job

ADD CONSTRAINT fk_job_dept FOREIGN KEY(depno)

REFERENCES department(deptno);

外键约束完整定义方式：

ALTER TABLE job

ADD CONSTRAINT fk_job_dept FOREIGN KEY(depno)

REFERENCES department(deptno)

ON DELETE RESTRICT

ON UPDATE RESTRICT;

设置外键约束在主表记录删除时设置子表中该外键值为null（执行成功的前提是deptno列设置为了允许为空）

ALTER TABLE job

ADD CONSTRAINT fk_job_dept FOREIGN KEY(depno)

REFERENCES department(deptno);

ON DELETE RESTRICT;

设置外键约束在主表记录更新时级联更新子表中该外键值

ALTER TABLE job

ADD CONSTRAINT fk_job_dept FOREIGN KEY(depno)

REFERENCES department(deptno);

ON UPDATE RESTRICT;