mysql-4建表（数据类型、建表、主键外键）

4、建表

1.数据类型

​ 包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION),还有枚举类型enum 和集合类型set 以及布尔值、json

数值数据
型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 byte (-128，127) (0，255) 小整数值
SMALLINT 2 bytes (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值
DOUBLE 8 bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值
时间和日期类型
类型 大小 ( bytes) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

字符串类型

类型 大小 用途
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据
常用补充
bit[(M)]
            二进制位（101001），m表示二进制位的长度（1-64），默认m＝1

        tinyint[(m)] [unsigned] [zerofill]

            小整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                -128 ～ 127.
            无符号：
                0 ～ 255

            特别的： MySQL中无布尔值，使用tinyint(1)构造。

        int[(m)][unsigned][zerofill]

            整数，数据类型用于保存一些范围的整数数值范围：
                有符号：
                    -2147483648 ～ 2147483647
                无符号：
                    0 ～ 4294967295

            特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002

        bigint[(m)][unsigned][zerofill]
            大整数，数据类型用于保存一些范围的整数数值范围：
                有符号：
                    -9223372036854775808 ～ 9223372036854775807
                无符号：
                    0  ～  18446744073709551615

        decimal[(m[,d])] [unsigned] [zerofill]
            准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。

            特别的：对于精确数值计算时需要用此类型
                   decaimal能够存储精确值的原因在于其内部按照字符串存储。

        FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
            单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
                无符号：
                    -3.402823466E+38 to -1.175494351E-38,
                    0
                    1.175494351E-38 to 3.402823466E+38
                有符号：
                    0
                    1.175494351E-38 to 3.402823466E+38

            **** 数值越大，越不准确 ****

        DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
            双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。

                无符号：
                    -1.7976931348623157E+308 to -2.2250738585072014E-308
                    0
                    2.2250738585072014E-308 to 1.7976931348623157E+308
                有符号：
                    0
                    2.2250738585072014E-308 to 1.7976931348623157E+308
            **** 数值越大，越不准确 ****


        char (m)
            char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
            PS: 即使数据小于m长度，也会占用m长度
        varchar(m)
            varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。

            注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡

        text
            text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。

        mediumtext
            A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.

        longtext
            A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.


        enum
            枚举类型，
            An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
            示例：
                CREATE TABLE shirts (
                    name VARCHAR(40),
                    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
                );
                INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

        set
            集合类型
            A SET column can have a maximum of 64 distinct members.
            示例：
                CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
                INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

        DATE
            YYYY-MM-DD（1000-01-01/9999-12-31）

        TIME
            HH:MM:SS（'-838:59:59'/'838:59:59'）

        YEAR
            YYYY（1901/2155）

        DATETIME

            YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

        TIMESTAMP

            YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
            
        clob
        	字符大对象，最多可以存储4G的字符串
        	如：存储一篇文章
        blob
        	二进制大对象
        	存储图片、声音、视频等流媒体数据
        	往blob类型字段上出入数据需要使用io流才行

-- 新建to_movie 电影表（专门存储电影信息）
编号  	    名字				描述信息           上映日期          时长           海报
no(bigint)	name(varchar)	description(clob)  playtime(date)  time(double)  img(blog)

类型
type(char)

2.建表

1、基本用法
create      table      if   not  exists（判断是否存在）  表名(

表头1           数据类型 default a ,

表头2           数据类型

) engine=innodb    default charset='utf-8'；
注意说明
  • CREATE TABLE子句之后创建的表的名称。表名在数据库中必须是唯一的
  • if not exists（判断是否存在） 可选择用于校验
  • 表头+数据类型为一个组合，组合之间用','隔开，结尾用‘；’
  • engine 存储引擎：InnoDB，MyISAM，HEAP，EXAMPLE，CSV，ARCHIVE，MERGE， FEDERATED或NDBCLUSTER ，默认5.5版本后innodb(支持事务，原子性操作)
  • default charset 设置数据表的编码类型
  • default a 默认值为 DEFAULT值用于指定列的默认值

2.创建表中的约束

a、定义

​ 什么是约束(constraint) ，在建表的时候，可以给表中的字段加上一些约束，来报错这个表中数据的完整性、 有效性。

  • 约束直接添加到列后面的，叫做列级约束
  • 约束没有添加在列后，称为表级约束
分类：

​ 非空约束：not null

​ 唯一性约束：unique

​ 主键约束：primary key(简称 pk)

​ 外键约束：foreign key(简称fk)

​ 检查约束：check(mysql 不支持，oracle支持)

b、NULL 与NOT NULL

设置字段的属性为 NOT NULL， 在操作数据库时如果未输入该字段下数据，出现NULL ，就会报错

create      table      if   not  exists（判断是否存在）  表名(

表头1           数据类型      NOT NULL

表头2           数据类型     default  NULL   （表示默认为空）

) engine=innodb    default charset='utf-8'；
c、唯一性约束

不能重复，但是可以为null

unique

  • 约束直接添加到列后面的，叫做列级约束
  • 约束没有添加在列后，称为表级约束
    • 需要给多个字段联合起来添加一个约束时，需要使用表级约束
-- num 唯一
create table(
	id int
	num int unique
) 

-- 字段分别唯一
create table(
	id int
	num int unique
	name varchar(255)  unique
 
)




--表示两个字段联合唯一，两个组合在一起表示联合唯一
create table t1(
	id int ....,
	num int,
	xx int,
	unique 唯一索引名称 (字段,字段)
		)

在mysql 中如果一个字段同时被not null 和unique 约束的话，该字段自动变为主键（非空唯一）

d、主键约束

primary key 与auto_increment

  • 相关术语：

    • 主键约束:就是一种约束
    • 主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段
    • 主键值：主键字段中的每一个值都叫做：主键值

  • 特性：

    • 一张表，主键约束只能添加一个
      • 列级约束只能用一次
    • 主键，一种特殊的唯一索引，不允许有空值，
    • 如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。

  • 使用：

    • 建议使用int、bigint、char 等类型
    • 不建议使用varchar来做主键。主键一般都是数字，且一般都是定长的

  • 分类

    • 单一主键

    • 复合主键

      或者

    • 自然主键：主键值是一个自然数，和业务没关系

    • 业务主键：主键值和业务紧密管理，如拿银行卡号

      • 自然主键使用数量大，主键只要不重复就行，不需要有意义
      • 业务主键会因为业务变得会受到影响

  • 维护主键：自增

    关于自增与步长

-- 设置主键与自增
-- 主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。
-- 一个表只能有一个主键 单一主键 	（列级约束）
	create table t1(
		id int signed not null  auto_increment primary key,
		num decimal(10,5),
		name char(10)
	)engine=innodb default charset=utf8;

	create table t1(
		id int signed not null  auto_increment ,
		num decimal(10,5),
		name char(10),
        primary key(id)
	)engine=innodb default charset=utf8;

-- 主键可以由多列组成，复合主键（不建议使用）（表级约束）
CREATE TABLE t5 (
		nid int(11) NOT NULL AUTO_INCREMENT,
		pid int(11) not NULL,
		num int(11),
		primary key(nid,pid)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
F、外键（foreign key）约束 可为空，当时不能重复

  • 相关术语

    • 外键约束：一种约束
    • 外键字段：该字段上添加了外键约束
    • 外键值：外键字段当中的每一个值

  • 数据添加修改删除顺序

    • 删除表时，先删除子表（添加foreign key）再删除父表
    • 创建表时，先创建父表，再创建子表
    • 修改表时，先修改子表，在修改父表

  • 子表中引用的外键引用的父表中的某个字段，被引用的字段不一定为主键,但是字段具有唯一性（有约束）

  • 外键可以为null

 子表 constraint    命名(fk_..)    foreign   key   表头（当前表参数） references  父表(表头)
ON DELETE/ON UPDATE  NO ACTION或restrict(父表删除更新时检查是否有对应外键，有则禁止删除)
					cascade （父表删除更新时检查是否有对应外键，有则同步）
					set Null (父表删除更新时检查是否有对应外键，有则设为null)
  • CONSTRAINT子句允许您为外键约束定义约束名称。如果省略它，MySQL将自动生成一个名称
  • FOREIGN KEY子句指定子表中引用父表中主键列的列
创建外键
CREATE DATABASE IF NOT EXISTS dbdemo;

USE dbdemo;

CREATE TABLE categories(
   cat_id int not null auto_increment primary key, comment 'comment 表示注释'
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;

CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;
外键变种，多外链情况，及unique 合并使用

案例1普通用法.一对多的情况，如：一个角色多个头衔的情况

user
uid name department
1 n1 1
2 n2 1
3 n3 2
4 n4 2
department
did cont
1 A部门
2 B 部门

  • user表的department 需要外链至department表的did

  • 一个user对应一个department

案例2：一对一 ，不允许出现重复

user
uid name postcard_id
1 n1 1
2 n2 3
3 n3 2
4 n4 4
postcard
pid number
1 3101....
2 3201....
3 3301.....
4 3401......
  • 外键的值与表内id是一一对应的关系,每个user仅有一个postcard_id
  • 这里需要在user表中对postcard_id 进行unique
create table postcard(
				pid int auto_increment primary key,
				number tinyint,
			)engine=innodb default charset=utf8;

create table user(
		uid int not null auto_increment primary key,
		username varchar(64) not null,
		postcard_id  int not null,
		unique key uq_u1 (postcard_id), comment '这里对postcard_id限定唯一不重复'
		CONSTRAINT fk_user_pc FOREIGN key (postcard_id) REFERENCES postcard(pid)
			)engine=innodb default charset=utf8;

补充一对不足一：

ser 少部分权限
uid name password access
1 n1 1224 access2
2 n2 dasda access1
3 n3 544554
4 n4 dasdasd

针对这种情况

可以单独将用户与权限单独建表， 用以节省空间

ser 少部分权限
uid name access
1 n1 access2
2 n2 access1

案例3：多对多, 联合唯一的场景

c表内的表头A与表头B，分别为A表，B 表的主键，且表头A与B的数据count>1

由于表头A与表头B存在的这种对应关系，仅能出现一次，故需要两则联合唯一

若业务需求不做约束要求，则不需要使用联合唯一

A
aid username gender
1 张三 man
2 李四 woman
3 王五 man
B
bid store
1 store_a
2 store_b
3 store_c
c 人与商店的对应关系
cid username store
1 1 1
2 1 2
3 1 3
4 2 2
5 2 1
6 3 1 
create table if not exist table_A(
aid int auto_increment,
username varchar(10),
primary key(id)
)engine = innodb default charset=utf8;


create table if not exist table_B(
bid int  not null auto_increment,
store varchar(30)
primary key(bid)
)engine = innodbb default charset=utf8;


create table if not exist table_c(
cid int  not null auto_increment,
username  int not null,
store int not null ,
primary key(cid),
unique key  uq_username_store(username,store), commit '不允许二者一一对应关系再次出现需要使用联合唯一'
constraint fk_username_a foreign key(username) references to table_a(username),
constraint fk_username_b foreign key(store) references to table_b(store),
)engine = innodbb default charset=utf8;

3.存储引擎

  • mysql中特有的术语

  • 实际上存储引擎是一个表存储和组织数据的方式。

  • show create table 表名;       查看表的情况

  • 在建表的时候可以在最后小括号的‘’）‘’的右表使用

    • engine来指定存储引擎 默认innodb
    • charset来指定表的字符编码方式 utf8

  • 支持存储引擎

    • show engines G

    • mysql> show engines G;
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

  • 常用存储引擎：

    • myisam

      • 使用三个文件表示每个表

        • 格式文件 -- 存储表结构的定义(mytable.frm)
        • 数据文件 -- 存储表行的内容（mytable.MYD）
        • 索引文件 -- 存储表上的索引（mytable.MYI）
        • 对于一张表来说，只要是主键，或者加油unique约束的字段上会自动创建索引

      • 特点：

        • 可被转换为压缩、只读表来节省空间

    • innodb

      • 默认的存储引擎，同时也是一个重量级的存储引擎
      • innodb__支持事务__，支持数据库崩溃后自动恢复机制
      • innodb存储引擎最主要的特点是：非常安全,并不能很好节省空间
      • 管理的表具有下列特征
        • 每个innodb表再数据库目录中以.frm格式文件表示
        • innodb表空间tablespace被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据和索引）
        • 提供一组用来记录事务性活动的日志文件
        • 用commit (提交)、savepoint 及rollback(回滚)支持事务处理
        • 提供全ACID兼容
        • 在mysql服务器崩溃后提供自动恢复
        • 多版本（mvcc）和行级锁定
        • 支持外键及引用的完整性，包括级联删除和更新

    • memory 存储引擎

      • 优点：查询效率最高；缺点：不安全，关机后数据消失
      • 存储在内存中，行的长度固定
      • 存储引擎非常快
      • 特征
        • 每个表均以.frm格式的文件表示
        • 表数据及索引被存储在内存中
        • 表级锁机制
        • 不能包含text 或blog 字段
      • 以前被称为heap引擎
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/yescarf/p/14089095.html