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引擎