文章目录

  • MySQL表相关操作
  • 一 表相关操作基础
  • 1 表介绍
  • 2 创建表
  • 2.1 语法及注意事项
  • 2.2 代码示例:
  • 3 查看表结构
  • 4 修改表
  • 4.1 修改表名
  • 4.2 修改字段
  • 4.3 增加字段
  • 4.4 删除字段
  • 4.5 代码示例
  • 5 表字段的数据类型
  • 5.1 介绍
  • 5.2 数值类型
  • 5.2.1 整数类型
  • 5.2.2 浮点型
  • 5.3 日期类型
  • 5.3.1 datetime与timestamp的区别
  • 5.4 字符串类型
  • 5.4.1 基本情况
  • 5.4.2 基本使用验证
  • 5.4.3 总结
  • 5.5 枚举类型与集合类型
  • 6 复制表
  • 7 删除表
  • 二 表操作之完整性约束
  • 1 介绍
  • 1.1 约束条件
  • 1.2 主要分类及说明:
  • 2 not null与default
  • 3 unique
  • 3.1 单列唯一
  • 3.2 联合唯一
  • 4 primary key
  • 4.1 主键是什么
  • 4.2 特点:**
  • 4.3 代码验证
  • 5 auto_increment
  • 6 foreign key
  • 6.1 什么是foreign key
  • 6.2 如何找出两张表之间的关系
  • 6.3 建立表之间的关系
  • 6.3.1 一对多或称为多对一
  • 6.3.2 多对多
  • 6.3.3 一对一


MySQL表相关操作

一 表相关操作基础

1 表介绍

表相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段。

id

name

age

字段:一条记录对应的标题称为一个字段,如id,name,age

1

kiessling

37

记录:一行内容称为一条记录

2

lars

30

3

leno

28

2 创建表

2.1 语法及注意事项

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
## 注意:
    1).在同一张表中,字段名是不能相同的;

    2).宽度和约束条件可选;

    3).字段名和类型是必须的;

    4).表中的最后一个字段不要加逗号!!"

2.2 代码示例:

mysql> create database db1 charset utf8;  # 创建库并指定字符编码(文件夹) 
mysql> use db1;  # 切换文件夹
mysql> create table t1(    # 创建表,并指定字段,数据类型
    -> id int,             # 一般创建表必指定的字段
    -> name varchar(50),
    -> sex enum('male','female'),
    -> age int(3)    # 整型字段后面指定的是显示长度,不是数据实际的存储长度
    -> );
mysql> show tables; #查看db1库下所有表名
mysql> desc t1;  # 查看表的结构
mysql> select id,name,sex,age from t1;  # 查看指定字段的内容
mysql> select * from t1;  # 查看表的所有内容
mysql> insert into t1 values  # into可省略
    -> (1,'egon',18,'male'),
    -> (2,'alex',81,'female')
    -> ;
mysql> insert into t1(id) values  # 插入值全为NULL
    -> (3),
    -> (4);

3 查看表结构

MariaDB[db1]> describe t1;  # 查看表结构,可简写为desc 表名
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+

MariaDB [db1]> show create table t1\G; # 查看表详细结构,可加\G

4 修改表

4.1 修改表名

alter table 表名 rename 新表名;

4.2 修改字段

alter table 表名 modify 字段名 数据类型 [完整性约束条件];
alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件];
alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件];

4.3 增加字段

alter table 表名 add 字段名 数据类型 [完整性约束条件],
				add 字段名 数据类型 [完整性约束条件];
alter table 表名 add 字段名 数据类型 [完整性约束条件] first;
alter table 表名 add 字段名 数据类型 [完整性约束条件] after 字段名;

4.4 删除字段

alter table 表名 drop 字段名;

4.5 代码示例

1) 先创建库,在新建表
mysql> create database db1;
mysql> user db1
mysql> create table t1(id int,name char(4));
# mysql> alter table t1 rename tt1;  # 表名的修改

2) 修改存储引擎
mysql> alter table t1 engine=innodb;

3) 修改字段
mysql> alter table t1 modify id tinyint;  # 修改字段的数据类型
mysql> alter table t1 change id ID tinyint;  # 修改字段名及数据类型
mysql> alter table t1 change ID id tinyint,change name NAME char(4);  # 修改多个字段名及数据类型

4) 增加字段
mysql> alter table t1 add gender char(4);
mysql> desc t1;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id     | tinyint(4) | YES  |     | NULL    |       |
| NAME   | char(4)    | YES  |     | NULL    |       |
| gender | char(4)    | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
mysql> alter table t1 add gender char(4) first;
mysql> alter table t1 add level int after ID;

5) 删除字段
alter table t1 drop gender;
mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | tinyint(4) | YES  |     | NULL    |       |
| NAME  | char(4)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

5 表字段的数据类型

5.1 介绍

存储引擎决定了表的类型,而表内存放的数据也要有不同的类型,每种数据类型都有自己的宽度,但是宽度是可选的。

# 1.mysql常用数据类型概览:
#1. 数字:
    整型:tinyinit  int  bigint
    小数:
        float :在位数比较短的情况下不精准
        double :在位数比较长的情况下不精准
            0.000001230123123123
            存成:0.000001230000

        decimal:(如果用小数,则用推荐使用decimal) 
            精准
            内部原理是以字符串形式去存
#2. 字符串:
    char(10) :简单粗暴,浪费空间,存取速度快
        root存成root000000
    varchar:精准,节省空间,存取速度慢

    sql优化:创建表时,定长的类型往前放,变长的往后放
                    比如性别           比如地址或描述信息

    >255个字符,超了就把文件路径存放到数据库中。
            比如图片,视频等找一个文件服务器,数据库中只存路径或url。
#3. 时间类型:
    最常用:datetime
#4. 枚举类型与集合类型

5.2 数值类型

5.2.1 整数类型
整数类型: TINYINT SMALLINT MEDIUMINT INT BIGINT

	作用:存储年龄,等级,id,各种号码等。

	# 强调:"整型的宽度是显示宽度(如tinyint(4) ) ",无需设置,存储宽度是固定死的。默认的显示宽度,都是在最大值的基础上加1。
# 常用类型详解
========================================
        tinyint[(m)] [unsigned] [zerofill]
            小整数,数据类型用于保存一些范围的整数数值范围:
            有符号:
                -128 ~ 127
            无符号:
                0 ~ 255
            PS: MySQL中无布尔值,使用tinyint(1)构造。
========================================
        int[(m)][unsigned][zerofill]
            整数,数据类型用于保存一些范围的整数数值范围:
            有符号:
                    -2147483648 ~ 2147483647
            无符号:
                    0 ~ 4294967295
========================================
        bigint[(m)][unsigned][zerofill]
            大整数,数据类型用于保存一些范围的整数数值范围:
            有符号:
                    -9223372036854775808 ~ 9223372036854775807
            无符号:
                    0  ~  18446744073709551615
#	代码验证:
# 1、=====================表字段类型之整型=======================
# =========有符号和无符号tinyint==========
# tinyint默认为有符号
mysql> insert t4 values(128);  
ERROR 1264 (22003): Out of range value for column 'id' at row 1  # 严格模式,超出范围会报错,若不是严格模式,则会把数字存成最近的极值。
mysql> insert t4 values(127);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t4;
+------+
| id   |
+------+
|  127 |
+------+
1 row in set (0.00 sec)

“类型”

“大小”

"范围(有符号) "

"范围(无符号) "

“用途”

TINYINT

1 字节

(-128,127)

(0,255)

小整数值

SMALLINT

2 字节

(-32 768,32 767)

(0,65 535)

大整数值

MEDIUMINT

3 字节

(-8 388 608,8 388 607)

(0,16 777 215)

大整数值

INT或INTEGER

4 字节

(-2 147 483 648,2 147 483 647)

(0,4 294 967 295)

大整数值

BIGINT

8 字节

(-9 233 372 036 854 775 808,9 223 372 036 854 775 807)

(0,18 446 744 073 709 551 615)

极大整数值

FLOAT

4 字节

(-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 字节

(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的值

小数值

1)  int的存储宽度是4Bytes,即32个bit,即2"32;
2)  无符号最大值:4294967296-1;
3)  有符号最大值:2147483648-1;
4)  有符号和无符号的最大数字需要的显示宽度均为10,而针对有符号的最小值则需要11位才能完全显示,所以int类型默认的显示宽度为11是非常合理的。
5)  最后,整型类型,其实没有必要指定显示宽度,使用默认的就OK。
5.2.2 浮点型
定点数类型 DEC等同于DECIMAL

浮点类型:float double

作用:存储薪资、身高、体重、体质参数等
# 常用类型详解
======================================
float[(M,D) [unsigned] [zerofill]]
定义:
	单精度浮点数(非准确小数) ,m是数字总个数,d是小数点后个数。m最大值为255,d最大值为30。
有符号:
		-3.402823466E+38 to -1.175494351E-38,					1.175494351E-38 to 3.402823466E+38
无符号:
		1.175494351E-38 to 3.402823466E+38
精确度:
		"" 随着小数的增多,精度变得不准确 ""
======================================
double[(M,D) [unsigned] [zerofill]]
定义:
	双精度浮点数(非准确小数值) ,m是数字总个数,d是小数点后个数。m最大值为255,d最大值为30。
有符号:
		-1.7976931348623157E+308 to -2.2250738585072014E-308
		2.2250738585072014E-308 to 1.7976931348623157E+308
无符号:
		2.2250738585072014E-308 to 1.7976931348623157E+308
精确度:
		""随着小数的增多,精度比float要高,但也会变得不准确 ""
======================================
decimal[(M,D) [unsigned] [zerofill]]
定义:
		准确的小数值,m是数字总个数(符号不算) ,d是小数点后个数。m最大值为65,d最大值为30。
精确度:
		"" 随着小数的增多,精度始终准确 ""
        对于精确数值计算时需要用此类型
        decaimal能够存储精确值的原因在于其内部按照字符串存储。
# 2、=====================表字段类型之浮点类型=======================
mysql> create table t7(x float(255,30),y double(255,30),z decimal(65,30));
mysql> insert t7 values(1.11111111111111111111,1.11111111111111111111,1.11111111111111111111);
mysql> select * from t7;  # 显示的精度问题,decimal精度最高
+----------------------------------+----------------------------------+----------------------------------+
| x                                | y                                | z                                |
+----------------------------------+----------------------------------+----------------------------------+
| 1.111111164093017600000000000000 | 1.111111111111111200000000000000 | 1.111111111111111111110000000000 |
+----------------------------------+----------------------------------+----------------------------------+
1 row in set (0.00 sec)

5.3 日期类型

date time datetime timestamp year

	作用:存储用户注册时间,文章发布时间,员工入职时间,出生时间,过期时间等等。
year(1901/2155) 

time 时:分:秒 ('-838:59:59'/'838:59:59') 

date 年:月:日 (1000-01-01/9999-12-31) 

datetime 年:月:日 时:分:秒 1000-01-01 00:00:00/9999-12-31 23:59:59

timestamp 年:月:日 时:分:秒 1970-01-01 00:00:00/2037
# 3、=====================表字段类型之日期类型======================
# 使用函数now()
mysql> create table t8(y year,t time,d date,dt datetime,ts timestamp);
Query OK, 0 rows affected (0.03 sec)

mysql> insert t8 values(now(),now(),now(),now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t8;
+------+----------+------------+---------------------+---------------------+
| y    | t        | d          | dt                  | ts                  |
+------+----------+------------+---------------------+---------------------+
| 2020 | 11:09:30 | 2020-09-02 | 2020-09-02 11:09:30 | 2020-09-02 11:09:30 |
+------+----------+------------+---------------------+---------------------+

# 手动传时间参数
mysql> create table student(id int,name char(10),born_year year,birth date,reg_time datetime);
Query OK, 0 rows affected (0.03 sec)

mysql> insert student values(1,"xxx","1911","1911-11-11","1911-11-11 11:11:11");
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+------+------+-----------+------------+---------------------+
| id   | name | born_year | birth      | reg_time            |
+------+------+-----------+------------+---------------------+
|    1 | xxx  |      1911 | 1911-11-11 | 1911-11-11 11:11:11 |
+------+------+-----------+------------+---------------------+
1 row in set (0.00 sec)

============!!!注意!!!!!!!===========
1) 单独插入时间是,需要以字符串的显露出,按照对应的格式插入
2) 插入年份时,尽量使用4位值
3) 插入两位年份时,<=69,以20开头,比如50,结果2050      
				>=70,以19开头,比如71,结果1971
5.3.1 datetime与timestamp的区别
# 在实际应用的很多场景中,MySQL的这两种日期类型都能够满足我们的需要,存储精度都为秒,但在某些情况下,会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。
1) DATETIME的日期范围是1001——9999年,TIMESTAMP的时间范围是1970——2038年。
2) datetime存储时间与失去无关,timestamp存储时间与失去有关,显示的值也依赖于时区。在MySQL服务器,操作系统以及客户端连接都有时区的设置。
3) datetime使用8字节存储时间,timestamp的搓出空间为4字节。因此,timestamp比datetime的空间利用率更高。
4) datetime的默认值为null,timestamp的字段默认不为空(not null) ,默认值为当前时间(current_timestamp) ,如果不做特殊处理,并且update语句中没有指定该列的更新值,则默认更新至为当前时间。
# "!!!注意:"针对datetime或者timestamp如果是用作注册时间,那么指定not null default now()自动填充时间即可,如果是用作更新时间那么需要额外指定on update now(),该配置timestamp自带。
# 测试1:基础性质
mysql> create table t9(reg_time datetime not null default now());
mysql> create table t10(reg_time timestamp);

mysql> insert t9 values();
mysql> insert t10 values();

mysql> select * from t9;
+---------------------+
| reg_time            |
+---------------------+
| 2020-09-02 11:28:40 |
+---------------------+

mysql> select * from t10;
+---------------------+
| reg_time            |
+---------------------+
| 2020-09-02 11:28:49 |
+---------------------+

mysql> desc t9;  # 用于记录注册时间,
+----------+----------+------+-----+-------------------+-------+
| Field    | Type     | Null | Key | Default           | Extra |
+----------+----------+------+-----+-------------------+-------+
| reg_time | datetime | NO   |     | CURRENT_TIMESTAMP |       |
+----------+----------+------+-----+-------------------+-------+

mysql> desc t10;  # 用于记录更新时间
+----------+-----------+------+-----+-------------------+-----------------------------+
| Field    | Type      | Null | Key | Default           | Extra                       |
+----------+-----------+------+-----+-------------------+-----------------------------+
| reg_time | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------+-----------+------+-----+-------------------+-----------------------------+
# 测试2:
mysql> create table t9(
    ->     id int,
    ->     name varchar(16),
    ->     -- update_time datetime not null default now() on update now()  # --是注释的意思,
    ->     update_time timestamp,
    ->     reg_time datetime not null default now()
    -> );
mysql> insert into t9(id,name) values(1,"xxx");
# 测试效果
mysql> select * from t9;
+------+------+---------------------+---------------------+
| id   | name | update_time         | reg_time            |
+------+------+---------------------+---------------------+
|    1 | xxx  | 2020-09-02 11:37:21 | 2020-09-02 11:37:21 |
+------+------+---------------------+---------------------+

mysql> update t9 set name="XXXX" where id=1;
mysql> select * from t9;
+------+------+---------------------+---------------------+
| id   | name | update_time         | reg_time            |
+------+------+---------------------+---------------------+
|    1 | XXXX | 2020-09-02 11:39:13 | 2020-09-02 11:37:21 |
+------+------+---------------------+---------------------+

5.4 字符串类型

5.4.1 基本情况
# 注意:char和varchar括号内的参数指的都是字符的长度
1) char类型:定长,简单粗暴,浪费空间,存取速度快
	字符长度范围:0-255(一个波斯文是一个字符,是utf8编码的3个字节) 
	存储:
		存储char类型的值时,会往右填充空格来满足长度(底层逻辑,查询时查不到) 
		例如:指定长度为10,存>10个字符则报错,存<10个字符则用空格填充直到凑够10个存储字符。
	检索:
		在检索或者说查询时,查处的结果会自动删除尾部的空格,除非我们打开pad_char_to_full_length SQL模式(SET sql_mode='PAD_CHAR_TO_FULL_LENGTH';)。
		
2) varchar类型:变长,精准,节省空间,存取速度慢
	字符长度范围:0-65535(如果大于21845会提示用其他类型。MySQL行最大限制为65535字节,字符编码为utf-8:https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html) 
	存储
		varchar类型存储数据的真实内容,不会用空格填充,如果'ab  ',尾部空格也会被存起来;
		强调!!:varchar类型的会在真实数据前加1-2Bytes前缀,该前缀用来表示真实数据的Bytes字节数(1-2Bytes最大表示65535个数字,正好符合MySQL对roe的最大字节限制数,即已足够使用) ;
		如果真实数据<255Bytes则需要1Bytes的前缀(1Bytes=8bit 2"8最大表示的数字为255) ;
		如果真实数据>255Bytes则需要2Bytes的前缀(2Bytes=16bit 2"16最大表示的数字为65535) ;
	检索:尾部有空格会保存下来,在检索或者说查询时,也会正常显示包含空格在内的内容;
"示例:"以4字节为例,"对比char与varchar存储空间数量"。
"特殊情况:"在接近数据存储满的时候,varchar反而更费空间。

Value

CHAR(4)

Storage Required

VARCHAR(4)

Storage Required

''

' '

4 bytes

''

1 byte

'ab'

'ab '

4 bytes

'ab'

3 bytes

'abcd'

'abcd'

4 bytes

'abcd'

5 bytes

'abcdefgh'

'abcd'

4 bytes

'abcd'

5 bytes

5.4.2 基本使用验证
# 函数了解
length: 查看字节数
char_length: 查看字符数
# 验证
create table t11(x char(5));
create table t12(x varchar(5));
# 实际数字后面加一个空格
insert t11 values("我擦嘞 ");  -- "我擦嘞  "
insert t12 values("我擦嘞 ");  -- "我擦嘞 "

t11=>字符个数 5  字节个数 11  # 实际的字符、字节数
t11=>字符个数 3   # char显示的字符
t12=>字符个数 4  字节个数 10  # varchar可以显示实际的字符、字节数(字节数上添加了一个位数) varchar认为末尾的空格不是数据的一部分。

# 查询当前的模式
show variables like "sql_mode"

set sql_mode="pad_char_to_full_length";  # 严格模式下可以看见实际的字符和字节个数,默认的模式下不是这样显示
select char_length(x) from t11;
select char_length(x) from t12;


select length(x) from t11;
select length(x) from t12;

# 查询时默认把末尾的空格都去掉 like模糊匹配,=匹配
select * from 表名 where 字段 like "r%"  # like模糊匹配,r开头的数据,后面字符数不限
select * from 表名 where 字段 like "r_"  # r开头的数据,后面只有一个字符。
5.4.3 总结
# InnoDB存储引擎:建议使用VARCHAR数据类型
	单从数据类型的实现机制去考虑,char数据类型的处理速度更快,有时甚至可以超出varchar处理速度的50%。
但对于InnoDB数据表,内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针) ,因此在本质上,使用固定长度的CHAR列不一定比使用可变长度的VARCHAR列性能要好。因而,主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR,因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I\O是比较好的。

5.5 枚举类型与集合类型

字段的值只能"在给定范围中选择",如单选框,多选框;

	"enum" 单选,只能在给定的范围内选一个值,如性别 sex 男male/女female

	"set"  多选,在给定的范围内可以选择一个或一个以上的值(爱好1,爱好2,爱好3...)
# 示例enum:
mysql> create table shirts(
    -> name varchar(40),
    -> size enum('x-small', 'small', 'medium', 'large', 'x-large')
    -> );
# 正常插入值
mysql> insert shirts(name,size) values('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
mysql> select * from shirts;
+-------------+--------+
| name        | size   |
+-------------+--------+
| dress shirt | large  |
| t-shirt     | medium |
| polo shirt  | small  |
+-------------+--------+
# 非正常插入值
mysql> insert shirts values("abc shirts","xxxxxx");
mysql> select * from shirts;
+-------------+--------+
| name        | size   |
+-------------+--------+
| dress shirt | large  |
| t-shirt     | medium |
| polo shirt  | small  |
| abc shirts  |        |
+-------------+--------+  # 如果是严格模式,则会报错,不允许传入值;
# 示例:set
mysql> create table user(
    -> name varchar(16),
    -> hobbies set("read","chou","drink","tang")
    -> );
# 正常传值
mysql> insert user values("xxx","tang,chou");
mysql> select * from user;
+------+-----------+
| name | hobbies   |
+------+-----------+
| xxx  | chou,tang |
+------+-----------+
# 非正常传值
mysql> insert user values("qqq","tangchou");
mysql> select * from user;
+------+-----------+
| name | hobbies   |
+------+-----------+
| xxx  | chou,tang |
| qqq  |           |
+------+-----------+  # 非正常传值,空

6 复制表

# 复制表结构+记录(key不会复制:主键,外键和索引) , 
# select语句查询的内容保存到表t2  全部复制将字段改为*
create table t2 select user,host,password from mysql.user;

# 只复制表结构,
# select语句查询的表结构保存到表t3  全部复制使用*
create table t3 select user,host,password from mysql.user where 1!=1;
PS:关于"虚拟表":硬盘上不存在表,表存在于内存中,表是硬盘上数据的被select语句组织出来的。

7 删除表

drop table 表名;

二 表操作之完整性约束

1 介绍

1.1 约束条件

#### 1 什么是约束条件
	约束条件与数据类型的宽度一样,都是可选参数。

#### 2 作用:
	用于保证数据的完整性和一致性。

1.2 主要分类及说明:

PRIMARY KEY(PK)     # 标识该字段为该表的主键,可以唯一的标识记录
FOREIGN KEY(FK)     # 标识该字段为该表的外键
NOT NULL    # 标识该字段不能为空
UNIQUE KEY(UK)     # 标识该字段的值是唯一的
AUTO_INCREMENT    # 标识该字段的值自动增长(整数类型,而且为主键) 
DEFAULT    # 为该字段设置默认值

UNSIGNED    # 无符号
ZEROFILL    # 使用0填充
1) 是否允许为空,默认NULL,可设置NOT NULL,字段不允许为空,必须赋值;
2) 字段是否有默认值,缺省的默认值是NULL,如果插入记录时不给字段赋值,此字段使用默认值
	sex enum('male','female') not null default 'male'
	age int unsigned NOT NULL default 20  # 必须为正值(无符号)  不允许为空  默认是20
3) 是否是key
主键  primary key
外键  foreign key
索引  (index,unique...)

2 not null与default

是否可空,null表示空,非字符串。"not null"——>"不可空";"null"——>"可空"。

	default是默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值。
# 代码验证
==================not null====================
mysql> create table t1(x int not null);  # 设置字段id不为空
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert t1 values();  # 不能插入空
"""ERROR 1364 (HY000): Field 'x' doesn't have a default value"""

==================default====================
#设置id字段有默认值后,则无论id字段是null还是not null,都可以插入空,插入空默认填入default指定的默认值
mysql> create table t2(x int not null default 666);
mysql> insert t2 values();
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| x     | int(11) | NO   |     | 666     |       |
+-------+---------+------+-----+---------+-------+
mysql> select * from t2;
+-----+
| x   |
+-----+
| 666 |
+-----+

3 unique

unique是唯一约束,可以"单列唯一",也可以多个条件"联合唯一"。

3.1 单列唯一

mysql> create table t3(name varchar(10) unique);

mysql> insert t3 values("egon");
mysql> insert t3 values("DSB");

mysql> insert t2 values("DSB");
ERROR 1366 (HY000): Incorrect integer value: 'DSB' for column 'x' at row 1

3.2 联合唯一

mysql> create table server(
    -> id int,
    -> name varchar(10),
    -> ip varchar(15),
    -> port int,
    -> unique(ip,port),
    -> unique(name)
    -> );
mysql> desc server;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  | UNI | NULL    |       |
| ip    | varchar(15) | YES  | MUL | NULL    |       |
| port  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> insert server values (1,"web1","10.10.0.11",8080);
mysql> insert server values (2,"web2","10.10.0.11",8081);
mysql> insert server values(4,"web4","10.10.0.11",8081);
-- ERROR 1062 (23000): Duplicate entry '10.10.0.11-8081' for key 'ip'
mysql> insert server values(3,"网页3","10.10.0.11",8082);

mysql> select * from server;
+------+---------+------------+------+
| id   | name    | ip         | port |
+------+---------+------------+------+
|    1 | web1    | 10.10.0.11 | 8080 |
|    2 | web2    | 10.10.0.11 | 8081 |
|    3 | 网页3   | 10.10.0.11 | 8082 |
+------+---------+------------+------+

4 primary key

4.1 主键是什么

主键primary key是innodb存储引擎组织数据的依据,innodb称之为索引组织表。

4.2 特点:**

一张innodb表中必须有且只有一个主键,可以:"单列做主键",也可以"多列做主键(复合/联合主键) "。

	主键的约束效果是not null + unique。

4.3 代码验证

==================单列做主键==================
# 方法一:在某一个字段后用primary key
mysql> create table t6(
    -> id int primary key auto_increment, # 主键 # 不指定id,则自动增长
    -> name varchar(5)
    -> );
mysql> insert t6(name) values
    -> ("egon"),
    -> ("is"),
    -> ("DSB"),
    -> ("DDB");

mysql> desc t6;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(11)    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(5) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+

mysql> select * from t6;
+----+------+
| id | name |
+----+------+
|  1 | egon |
|  2 | is   |  # 尽量不要使用关键字作为表格的内容
|  3 | DSB  |
|  4 | DDB  |
+----+------+

# 方法二:not null+unique
mysql>  create table t6_2(
    -> id int not null unique,  # 主键
    -> name varchar(20) not null unique,
    -> comment varchar(100)
    -> );
mysql> desc t6_2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

# 方法三:在所有字段后单独定义primary key
mysql> create table t6_3(
    -> id int,
    -> name varchar(20),
    -> comment varchar(100),
    -> constraint pk_name primary key(id)  # 创建主键并为其命名pk_name
    -> );

mysql> desc t6_3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | 0       |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
==================多列做主键(了解) ==================
mysql> create table t7(
    -> id int,
    -> name varchar(5),
    -> primary key(id,name)
    -> );

mysql> desc t7;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | 0       |       |
| name  | varchar(5) | NO   | PRI |         |       |
+-------+------------+------+-----+---------+-------+

5 auto_increment

约束字段为自动增长,被约束的字段必须同时被key约束。

6 foreign key

6.1 什么是foreign key

foreign key是外键,用于关联多张表,对表进行解耦合,提高查询速度。
1) # 先创建被关联表
mysql> create table dep(
    -> id int primary key auto_increment,
    -> name varchar(6),
    -> comment varchar(60)
    -> );
    
2) # 再创建关联表
mysql> create table emp(
    -> id int primary key auto_increment,
    -> name varchar(10),
    -> gender varchar(5),
    -> dep_id int,
    -> foreign key(dep_id) references dep(id) on delete cascade on update cascade
    -> );
    
3) # 先往被关联表插入数据
mysql> insert dep(id,name) values
    -> (1,"技术部"),
    -> (2,"人力资源部"),
    -> (3,"销售部");
    
mysql> desc dep;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(6)  | YES  |     | NULL    |                |
| comment | varchar(60) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+

mysql> select * from dep;
+----+-----------------+---------+
| id | name            | comment |
+----+-----------------+---------+
|  1 | 技术部          | NULL    |
|  2 | 人力资源部      | NULL    |
|  3 | 销售部          | NULL    |
+----+-----------------+---------+

4) # 再往关联表插入数据
mysql> insert emp(name,gender,dep_id) values
    -> ('egon',"male",1),
    -> ('alex1',"male",2),
    -> ('alex2',"male",2),
    -> ('alex3',"male",2),
    -> ('李坦克',"male",3),
    -> ('刘飞机',"male",3),
    -> ('张火箭',"male",3),
    -> ('林子弹',"male",3),
    -> ('加特林',"male",3);

mysql> desc emp;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10) | YES  |     | NULL    |                |
| gender | varchar(5)  | YES  |     | NULL    |                |
| dep_id | int(11)     | YES  | MUL | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

mysql> select * from emp;
+----+-----------+--------+--------+
| id | name      | gender | dep_id |
+----+-----------+--------+--------+
|  1 | egon      | male   |      1 |
|  2 | alex1     | male   |      2 |
|  3 | alex2     | male   |      2 |
|  4 | alex3     | male   |      2 |
|  5 | 李坦克    | male   |      3 |
|  6 | 刘飞机    | male   |      3 |
|  7 | 张火箭    | male   |      3 |
|  8 | 林子弹    | male   |      3 |
|  9 | 加特林    | male   |      3 |
+----+-----------+--------+--------+

6.2 如何找出两张表之间的关系

# 分析步骤
1) 先站在左表的角度去找
	是否左表的多条记录可以对应右表的一条记录,如果是,则证明左表的一个字段foreign key 右表一个字段(通常是id) 。
    
2) 再站在右表的角度去找
	是否右表的多条记录可以对应左表的一条记录,如果是,则证明右表的一个字段foreign key 左表一个字段(通常是id) 。
    
3) 总结:
# 多对一
	如果只有步骤1成立,则是左表多对一右表;
    如果只是步骤2成立,则是右表多对一左表。
# 多对多
	如果步骤1和2同时成立,则证明这两张表同时一个双向的多对一,即多对多,需要定义一个这两张表的关系的关系表来专门存放二者的关系。
# 一对一
	如果1和2都不成立,而是左表的一条记录唯一对应右表的一条记录,反之亦然。这种情况很简单,就是在左表foreign key 右表的基础上,将左表的外键字段设置成unique即可。

6.3 建立表之间的关系

6.3.1 一对多或称为多对一
三张表:出版社,作者,书
一对多(或多对一) :一个出版社可以出版多本书
关联方式: foreign key
=====================多对一=====================
mysql> create table press(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );

mysql> create table book(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> press_id int not null,
    -> foreign key(press_id) references press(id) on delete cascade on update cascade
    -> );

mysql> insert press(name) values
    -> ('丑国の音乐不好听出版社'),
    -> ('忒浪噗是个DSB出版社'),
    -> ('啊丑离家出版社');

mysql> insert book(name,press_id) values
    -> ('九阳神功',1),
    -> ('九阴真经',2),
    -> ('九阴白骨爪',2),
    -> ('独孤九剑',3),
    -> ('降龙十巴掌',2),
    -> ('葵花宝典',3);

mysql> select * from press;
+----+--------------------------------+
| id | name                           |
+----+--------------------------------+
|  1 | 丑国の音乐不好听出版社           |
|  2 | 忒浪噗是个DSB出版社              |
|  3 | 啊丑离家出版社             |
+----+--------------------------------+

mysql> select * from book;
+----+-----------------+----------+
| id | name            | press_id |
+----+-----------------+----------+
|  1 | 九阳神功        |        1 |
|  2 | 九阴真经        |        2 |
|  3 | 九阴白骨爪      |        2 |
|  4 | 独孤九剑        |        3 |
|  5 | 降龙十巴掌      |        2 |
|  6 | 葵花宝典        |        3 |
+----+-----------------+----------+
6.3.2 多对多
三张表:出版社,作者信息,数
多对多:一个作者可以写多本书,一本书也可以有多个作者,双向的一对多,即多对多
关联方式: foreign key + 一张新的表
=====================多对多=====================
mysql> create table author(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );

# 这张表就存放作者表与书表的关系,即查询二者的关系查这表就可以了
mysql> create table author2book(
    -> id int not null unique auto_increment,
    -> author_id int not null,
    -> book_id int not null,
    -> constraint fk_auther foreign key(author_id) references author(id) on delete cascade on update cascade,
    -> constraint fk_book foreign key(book_id) references book(id) on delete cascade on update cascade,
    -> primary key(auther_id,book_id)
    -> );

# 插入四个作者,id依次排开
mysql> insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

# 每个作者与自己的代表作如下:
1 egon: 
      1 九阳神功;2 九阴真经;3 九阴白骨爪;4 独孤九剑;5 降龙十巴掌;6 葵花宝典;
2 alex: 
      1 九阳神功;6 葵花宝典;
3 yuanhao:
      4 独孤九剑;5 降龙十巴掌;6 葵花宝典;
4 wpq:
      1 九阳神功;

mysql> insert into author2book(author_id,book_id) values
    -> (1,1),
    -> (1,2),
    -> (1,3),
    -> (1,4),
    -> (1,5),
    -> (1,6),
    -> (2,1),
    -> (2,6),
    -> (3,4),
    -> (3,5),
    -> (3,6),
    -> (4,1);
    
mysql> desc author2book;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | UNI | NULL    | auto_increment |
| author_id | int(11) | NO   | PRI | NULL    |                |
| book_id   | int(11) | NO   | PRI | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
mysql> select * from author2book;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         1 |       2 |
|  3 |         1 |       3 |
|  4 |         1 |       4 |
|  5 |         1 |       5 |
|  6 |         1 |       6 |
|  7 |         2 |       1 |
|  8 |         2 |       6 |
|  9 |         3 |       4 |
| 10 |         3 |       5 |
| 11 |         3 |       6 |
| 12 |         4 |       1 |
+----+-----------+---------+
6.3.3 一对一
两张表:学生表和客户表
一对一:一个学上是一个客户,一个客户有可能变成一个学生,即一对一的关系;
关联方式: foreign key + unique
# 一定是student来foreign key表customer,这样就保证了:
# 1 学生一定是一个客户,
# 2 客户不一定是学生,但有可能成为一个学生
mysql> create table customer(
    -> id int primary key auto_increment,
    -> name varchar(20) not null,
    -> qq varchar(10) not null,
    -> phone char(16) not null
    -> );

mysql> create table student(
    -> id int primary key auto_increment,
    -> class_name varchar(20) not null,
    -> customer_id int unique,  # 该字段一定要是唯一的
    -> foreign key(customer_id) references customer(id) on delete cascade on update cascade  # 外键的字段一定要保证unique
    -> );
# 增加客户
mysql> insert customer(name,qq,phone) values
    -> ("x001",'100001','123123123'),
    -> ("x002",'100002','123123124'),
    -> ("x003",'100003','123123125');

# 增加学生
mysql> insert student(class_name,customer_id) values
    -> ("linux",3),
    -> ("go",1);
    
mysql> select * from customer;
+----+------+--------+-----------+
| id | name | qq     | phone     |
+----+------+--------+-----------+
|  1 | x001 | 100001 | 123123123 |
|  2 | x002 | 100002 | 123123124 |
|  3 | x003 | 100003 | 123123125 |
+----+------+--------+-----------+
mysql> select * from student;
+----+------------+-------------+
| id | class_name | customer_id |
+----+------------+-------------+
|  4 | linux      |           3 |
|  5 | go         |           1 |
+----+------------+-------------+