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

posted @ 2020-12-05 12:19 yescarf 阅读(750) 评论(0) 编辑收藏举报

刷新页面返回顶部

yescarf