MYSQL 数据表的基础知识-03

数据表的基础知识

表的定义

• 表是包含数据库中所有数据的数据库对象。

• 在表中,数据成二维行列格式,每一行代表一个唯一的记录,每一列代表一个域。

列名

列名是用来访问表中具体域的标识符,列名必须遵循下列规则:

• 列名是可以含有从1到128的asc码字符,它的组成包括字母、下划线、符号以及数字

• 不要给列名命名为与sql关键字相同的名字,比如 select, in, desc等。

• 列名应该反映数据的属性。

数据类型

数值类型

• 整数数据类型

  • 整数数据类型包括 bigint、int、 mediumint、 smallint和 tinyint

  数据类型	取值范围	说明
  bigint	-2^63 - 2^63-1	一位为 8 个字节
  int	-2^31 - 2^31 - 1	一位大小为 4 个字节
  mediumint	-2^23 - 2^23 - 1	一位大小为 2 个字节
  smallint	-2^15 - 2^15 - 1	一位大小为 2 个字节
  tinyint	-2^7 - 2^7- 1	一位大小为 1 个字节

• 小数数据类型

  • 小数数据类型包括 decimal、 numeric两类。从功能上说两者完全等价,两者的唯一区别在于 decimal不能用于带有 indentity 关键字的列

  • 声明小数型数据的格式是 numeric | decimal(p[,s]),其中p 为精度,s为小数位数,s的默认值为0

• 浮点数据类型

• 浮点型也称近似类型。这种类型不能提供精确表示数据的精度,使用这种类型来存储某些数值时,有可能会损失些精度。

字符串类型

常用的字符串类型主要包括char、 varchar、 binary、 varbinary 等类型。

日期时间类型

在 mysql中,日期时间类型包括 datetime、time、timestamp、date等。

举个栗子：管理网上书城系统数据库

创建数据表

语法格式

CREATE TABLE <表名> ( [表定义选项] )[表选项][分区选项];

知识点

• 表名：两种写法，可以直接写数据表的名字 tbl_name ，也可以 db_name.tbl_name ，先指定数据库再指定数据表名；后者写法是为了：无论是否在当前数据库，也能通过这种方式在指定数据库下创建表

• 表定义选项：一般都是 列名、列定义、默认值、约束、索引组成

创建如下数据表-客户信息表customers

实际代码

-- 如果存在就删除, 防止重复建表而报错
drop table if exists customers; 
create table customers(
    cid char(6) not null primary key,
    ctruename varchar (30) not null,
    cpassword varchar (30) not null,
    csex char(2)not null default '男',
    caddress varchar (50) null,
    cmobile varchar (11) not null unique,
    cemail varchar(50) null,
    cregisterdate datetime not null
);

附：Django中使用ORM操作数据库

from django.db import models

class Customer(models.Model):
    cid = models.CharField(max_length=6, primary_key=True)
    ctruename = models.CharField(max_length=30)
    cpassword = models.CharField(max_length=30)
    csex = models.CharField(max_length=2, default='男')
    caddress = models.CharField(max_length=50, null=True)
    cmobile = models.CharField(max_length=11, unique=True)
    cemail = models.EmailField(max_length=50, null=True)
    cregisterdate = models.DateTimeField()

    class Meta:
        db_table = 'customers'

约束

在 mysql中可以通过 primary key约束、foreign key约束、 unique、空值约束和默认值约束等来实施数据完整性

主健约束

主键(primary key)约束是使用最为频繁的约束 它的作用是唯一标记一条数据

1. 主键不能为null

2. 主键不能重复

3. 用过的主键, 即便删除来这条记录, 最好也不要再用

4. 主键不能修改

单字段主键

『示例』顾客信息表 customers中需要以“顾客编号”作为顾客的唯一标识,在创建数据表 customers时,为cid列设置primary key约束,由于单列组成主键,故该主键可以定义为列级主键。

多字段主键

『示例』如果在订单详情表中不设置编号,则可以将表中“订单编号 + 商品编号”作为订单详情的唯一标识,在创建数据表 orderdetails时, 为oid和gid的组合设置 primary key约束。

创建如下数据表-订单详情表orderdetails

实际代码

create table orderdetails(
    oid char(14) not null,
    gid char(6) not null,
    odprice float not null,
    odnumber int not null,
    primary key (oid, gid)
);

附：Django中使用ORM操作数据库

from django.db import models

class OrderDetails(models.Model):
    oid = models.CharField(max_length=14)
    gid = models.CharField(max_length=6)
    odprice = models.FloatField()
    odnumber = models.IntegerField()

    class Meta:
        db_table = 'orderdetails'
        unique_together = (('oid', 'gid'),)

外健约束

外键( foreign key )约束标识表之间的关系,用于强制参照完整性,为表中一列或者多列数据提供参照完整性。

简单说, 外键是连接另一个表的锚点

创建外键的基本语法如下:

[constraint<外键名>] foreign key 列名1 [, 列名2, ...] references <主键表> 主键列1 [主键列2...]

看着挺唬人, 其实很简单, 看个例子

『示例』在创建数据表订单表 orders时,表中的cid(客户编号)引用了客户表 customers中的客户编号,需要建立 orders表和 customers 表之间的关系。其中cid为关联列, customers 表为主键表, orders表为外键表。

创建如下数据表-订单信息表orders

实际代码

-- 如果存在就删除, 防止重复建表而报错
drop table if exists orders; 
create table orders(
    oid char(14) not null primary key,
    cid char(6) not null,
    odate datetime not null,
    osum float not null,
    ostatus char(1) not null,
    foreign key(cid) references customers(cid) on delete cascade on update cascade
)engine=innodb;

附：Django中使用ORM操作数据库

from django.db import models
from customers.models import Customer

class Orders(models.Model):
    oid = models.CharField(max_length=14, primary_key=True)
    cid = models.ForeignKey(Customer, on_delete=models.CASCADE)
    odate = models.DateTimeField()
    osum = models.FloatField()
    ostatus = models.CharField(max_length=1)

    class Meta:
        db_table = 'orders'

补充：

on update cascade 和on delete cascade 的区别

这是数据库外键定义的一个可选项，用来设置当主键表中的被参考列的数据发生变化时，外键表中响应字段的变换规则的。

update 则是主键表中被参考字段的值更新，delete是指在主键表中删除一条记录：
on update 和 on delete 后面可以跟的词语有四个

no action ， set null ， set default ，cascade

• no action 表示 不做任何操作，

• set null 表示在外键表中将相应字段设置为null

• set default 表示设置为默认值

• cascade 表示级联操作，就是说，如果主键表中被参考字段更新，外键表中也更新，主键表中的记录被删除，外键表中改行也相应删除。

级联更新时，依据的是之前匹配的数据，在主表更新关联的外键字段的值后，系统自动更新从表的相应外键字段的值，而不是其他未设置为主外键关联的字段，不是主外键关联的字段不受影响。

唯一约束

『示例』为了保证客户信息表表 customers中的手机号不重复,在创建数据表 customers时,为 cmobile设置 unique约 束

-- 如果存在就删除, 防止重复建表而报错
drop table if exists customers; 

create table customers(
    cid char(6) not null primary key,
    ctruename varchar (30) not null,
    cpassword varchar (30) not null,
    csex char(2)not null,
    caddress varchar (50) null,
    cmobile varchar (11) not null unique,
    cemail varchar(50) null,
    cregisterdate datetime not null
);

默认值约束

『示例』在创建客户信息表 customers时,为用户性别csex 字段设置为默认值“男”。

create table customers(
    cid char(6) not null primary key,
    ctruename varchar (30) not null,
    cpassword varchar (30) not null,
    csex char(2)not null default '男',
    caddress varchar (50) null,
    cmobile varchar (11) not null unique,
    cemail varchar(50) null,
    cregisterdate datetime not null
);

非空约束

• 非空值约束限制一列或多个列的值不能为空(null) 空表示未定义或未知的值。

• 在默认情况下,所有列都接受空值, 若要某列不接受空值, 则可以在该列上设置not null约束。

• null值既不等价于数值型数据0, 也不等价于字符型数据中的空串, 只是表明字段值是未知的

• not null 约束不可以出现在表级定义中，not null约束只能在列级上定义。

• 一般情况下，我们使用列级定义即可。但是如果遇到定义复合主键（两列一起被定义为主键）时，需要用到表级定义。

数据练习举例来源于：猫哥的技术博客

查看表结构

desc shisuiyi.customers;

查看数据表的创建语句

show create table customers;

该命令不仅可以查看创建表时的详细语句，而且可以查看存储引擎和字符编码

复制数据表

-- 仅复制表结构
create table customers2 like customers;

-- 复制表结构和数据
create table customers3 as select  * from customers;

-- 仅复制表的指定字段结构
create table customers4 as select cid,ctruename,cpassword from customers where 1<>1;

-- 复制表的指定字段结构和数据
create table customers5 as select id,uname,sex from customers;

-- 查看表创建语句：没有包含主键和自增
show create table customers5;

知识点

• 仅复制全部字段的结构直接加 like

• 复制表不包含主键、索引、自增等