表相关操作-CSDN博客

本文链接：https://blog.csdn.net/cxtyabc/article/details/132670458

一、配置文件

1.查看MySQL相关信息

我们可以使用\s来查看MySQL相关信息

# 直接打\s即可，不需要加分号，加上分号也会输出，但是最后会报错
mysql> \s 
--------------
mysql  Ver 14.14 Distrib 5.6.48, for Win64 (x86_64)  # MySQL的版本是5.6.48，适应的操作系统是Win64

Connection id:          9  # 链接的id号
Current database:          # 所在数据库
Current user:           root@localhost  # 当前的用户
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.6.48 MySQL Community Server (GPL)  # MySQL的版本
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1  # 服务端字符编码
Db     characterset:    latin1  # 数据库字符编码
Client characterset:    gbk  # 客户端字符编码
Conn.  characterset:    gbk  # 联结第三方的客户端的字符编码
TCP port:               3306  # MySQL默认的端口号是3306，
Uptime:                 1 day 16 hours 10 min 39 sec

Threads: 1  Questions: 246  Slow queries: 0  Opens: 74  Flush tables: 1  Open tables: 63  Queries per second avg: 0.001
--------------

# \s加分号报错
ERROR:No query specified


# 不修改字符编码，添加中文不能显示
mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | kevin |
|    2 | jack  |
|    3 | tom   |
+------+-------+
3 rows in set (0.00 sec)

mysql> insert into t1 values(4,'哈哈哈'),(5,'嗯');
Query OK, 2 rows affected, 2 warnings (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 2
# 中文可以添加，但是会乱码

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | kevin |
|    2 | jack  |
|    3 | tom   |
|    4 | ???   |  # 但是显示问号
|    5 | ?     |
+------+-------+
5 rows in set (0.00 sec)

# mysql字符编码的默认配置是拉丁文，不能识别中文。想要识别，修改配置文件的内容

MySQL5.6及之前的版本编码内部默认是混乱的，会产生乱码问题。需要人为统一，之后的版本已经全部默认统一，默认是utf系列的

如果想要永久修改编码配置需要操作配置文件

2.修改配置文件

步骤：
1. 复制my-default.ini文件
2. 命名为my.ini
3. 把命令加入到my.ini后保存
4. 修改完配置文件中关于[mysqld]的配置之后，一定别忘重启服务端

修改字符编码的相关配置：

[mysqld]
	character-set-server=utf8 
	collation-server-utf8_general_ci
[client]
	default-character-set=utf8 
[mysql]
	default-character-set=utf8


# 可以直接拷贝上述代码
# [mysqld]:服务端
# [client]:MySQL自带的客户端
# [mysql]:第三方客户端

ps:
    1.utf8mb4能够存储表情 功能更强大，MySQL8.0的版本使用utf8mb4
    2.utf8与utf-8是有区别的，MySQL中只有utf8

重启服务端

C:\WINDOWS\system32>net stop mysql
MySQL 服务正在停止.
MySQL 服务已成功停止。


C:\WINDOWS\system32>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

# 就可以登录客户端了
C:\WINDOWS\system32>mysql -u root -p

之后使用一定要新建库，新建表，因为之前的库和表还是原来的格式。

mysql> show databases;  # 展示数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.03 sec)

mysql> create database db2;  # 创建新数据库db2
Query OK, 1 row affected (0.00 sec)

mysql> use db2;  # 切换db2数据库
Database changed
mysql> create table t1(id int,name varchar(16));  # 创建表t1
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t1 values(1,'kevin'),(2,'哈哈哈');  # 添加记录
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;  # 查看所有记录
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | kevin     |
|    2 | 哈哈哈     |  # 此时，就能够正常显示中文了
+------+-----------+
2 rows in set (0.00 sec)

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.48, for Win64 (x86_64)

Connection id:          1
Current database:       db2
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.6.48 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 15 min 5 sec

Threads: 1  Questions: 14  Slow queries: 0  Opens: 68  Flush tables: 1  Open tables: 61  Queries per second avg: 0.015
--------------
# 此时的所有编码都是utf8，插入数据就不会出现乱码情况了

在配置文件中由个偷懒操作，但是不建议使用。

"""
利用配置文件我们可以偷懒
	可以将管理员登录的账号密码直接写在配置文件中，之后直接使用mysql登录即可
	[mysql]
		user='root'
		password=123
"""

二、存储引擎介绍

存储引擎即表类型，mysql根据不同的表类型会有不同的处理机制

1.什么是存储引擎

mysql中建立的库 ===> 文件夹
库中建立的表 ===> 文件

现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：比如处理文本用txt类型，处理表格用excel，处理图片用png等。数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎

综上所述，存储引擎就是数据库针对数据采取的多种存储方式

2.mysql支持的存储引擎

show engines;  # 查看所有支持的存储引擎，后面还可以加(\G)

show variables like 'storage_engine%'; # 查看正在使用的存储引擎

MySQL一定支持9种存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |  # 引擎名|是否支持|对引擎的描述|
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

MySQL存储引擎介绍

# MyISAM(重点)
    MyISAM storage engine                       
    MySQL5.5及之前的版本默认的存储引擎
    存取数据的速度快，但是功能较少，安全性较低
    它不支持事务，行锁，外键;支持表锁

# InnoDB(重点)
    Supports transactions, row-level locking, and foreign keys
    MySQL5.6及之后的版本默认的存储引擎
    存取速度没有MyISAM快，但是相对MyISAM安全性更高
    它支持事务，行锁，外键;

# MEMORY
    Hash based, stored in memory, useful for temporary tables
    数据存放在内存中，一旦断电，数据立马丢失，重启服务端数据就没了，不能长期保存数据，仅用于临时表数据存取
    
# BlackHole
	任何写入进去的数据都会立刻丢失，使用很少

了解不同存储引擎底层文件个数：

1. MyISAM引擎 产生3个文件
    .frm  >>> 表结构
    .MYD  >>> 存数据    d-> data
    .MYI  >>> 存索引   >>> 看成是目录  i-> index 
2. InnoDB 产生2个文件
    .frm  >>> 表结构
    .ibd  >>> 表结构+数据
3. MEMORY 产生1个文件
    .frm  >>> 表结构
    数据是保存在内存中，所以磁盘中没有。
4. BlackHole 产生1个文件
    .frm  >>> 表结构
    数据给了我之后，下一秒就删除了

3.使用存储引擎

方法1：建表时指定
create table 表名(字段名1 字段类型,字段名2 字段类型) engine=存储引擎;


方法2：在配置文件中指定默认的存储引擎
[mysqld]
default-storage-engine=INNODB
innodb_file_per_table=1

创建四个表，分别使用innodb，myisam，memory，blackhole存储引擎，进行插入数据测试

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

mysql> use db2;
Database changed
mysql> create table t1(id int)engine=innodb;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t2(id int) engine=myisam;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t3(id int) engine=memory;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t4(id int) engine=blackhole;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| t1            |
| t2            |
| t3            |
| t4            |
+---------------+
4 rows in set (0.00 sec)

mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t2;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t3;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `id` int(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t4;
+-------+-----------------------------------------------------------------------------------------+
| Table | Create Table                                                                            |
+-------+-----------------------------------------------------------------------------------------+
| t4    | CREATE TABLE `t4` (
  `id` int(11) DEFAULT NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查看内部数据，主要是看存储引擎是memory、blackhole的数据

mysql> insert into t1 values(1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(1),(2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t3 values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t4 values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select * from t3;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select * from t4;
Empty set (0.00 sec)  # t4的直接显示没有数据

存储引擎是memory的表t3数据是保存在内存中，服务器关掉重启后数据也会消失

mysql> exit;  # 退出客户端，也可以使用：quit(加不加分号都行)
Bye
C:\WINDOWS\system32>net stop mysql  # 关闭服务端
C:\WINDOWS\system32>net start mysql  # 启动服务端
C:\WINDOWS\system32>mysql -u root -p  # 登录客户端

再次查看四个表中的数据

mysql> use db2;
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.01 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select * from t3;
Empty set (0.00 sec)  # 此时t3就没有数据了

mysql> select * from t4;
Empty set (0.00 sec)

三、创建表的完整语法

create table 表名(
	字段名 字段类型(数字) 约束条件 约束条件 约束条件,
	字段名 字段类型(数字) 约束条件,
 	字段名 字段类型(数字) 约束条件
);


1.字段名和字段类型是必须的
2.数字和约束条件是可选的
3.约束条件也可以写多个,空格隔开即可
4.最后一行结尾不能加逗号(易犯错的)

ps:编写SQL语句报错之后不要慌,仔细查看提示,会很快解决 
	near ')' at line 7
        

# 创建表useinfo
create table useinfo(
  id int,
  name varchar(16),
  gender varchar(16),
  addr varchar(16),
  email varchar(16)
);

四、查看表结构

mysql> describe useinfo; # 查看表结构，可简写为desc 表名
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(16) | YES  |     | NULL    |       |
| gender | varchar(16) | YES  |     | NULL    |       |
| addr   | varchar(16) | YES  |     | NULL    |       |
| email  | varchar(16) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.03 sec)


mysql> show create table useinfo; #查看表详细结构，可加\G
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                             |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| useinfo | CREATE TABLE `useinfo` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(16) DEFAULT NULL,
  `gender` varchar(16) DEFAULT NULL,
  `addr` varchar(16) DEFAULT NULL,
  `email` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

五、数据类型

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

mysql常用数据类型

#1. 数字：
    整型：tinyinit  int  bigint
    小数：
        float ：在位数比较短的情况下就不精准
        double ：在位数比较长的情况下不精准
            0.000001230123123123
            存成：0.000001230000

        decimal：（如果用小数，则用推荐使用decimal）
            精准
            内部原理是以字符串形式去存

#2. 字符：
    char（10）：简单粗暴，浪费空间，存取速度快
        root内部会存成root000000
    varchar：精准，节省空间，存取速度慢

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

    >255个字符，超了就把文件路径存放到数据库中。
            比如图片，视频等找一个文件服务器，数据库中只存路径或url。



#3. 时间类型：
    datetime  date  time  year
    最常用：datetime


#4. 枚举类型与集合类型
字段的值只能在给定范围中选择，如单选框，多选框 ，
enum 单选 只能在给定的范围内选一个值，如性别,sex:男male/女female 
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3…）

枚举类型（enum）
一个enum字段最多可以有65535个不同的元素。(实际限制不超过3000。)
集合类型（set）
一个SET字段最多可以有64个不同的元素。

1.整型

tinyint smallint int bigint

他们的存储数据大小范围不一样
范围的比较：tinyint < smallint < int < bigint

tinyint: 1bytes --> 8bit --> 正负号(占1bit) --> 有符号-128-127,无符号0-255
smallint: 2bytes --> 16bit --> 正负号(占1bit) --> 有符号-32768-32767,无符号0-65535
int： 4bytes
bigint： 8bytes

存储手机号不能使用int,手机号是11位，int不够，可以使用bigint,或者字符串

# 1.查看整型的存储个数
mysql> create table intinfo(
    -> id1 tinyint,
    -> id2 smallint,
    -> id3 mediumint,
    -> id4 int,
    -> id5 bigint
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc intinfo;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id1   | tinyint(4)   | YES  |     | NULL    |       |
| id2   | smallint(6)  | YES  |     | NULL    |       |
| id3   | mediumint(9) | YES  |     | NULL    |       |
| id4   | int(11)      | YES  |     | NULL    |       |
| id5   | bigint(20)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.03 sec)

# 2.验证整型默认是否携带正负号
mysql> create table t5(id tinyint);
Query OK, 0 rows affected (0.05 sec)

mysql> desc t5;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into t5 values(-129),(128);
Query OK, 2 rows affected, 2 warnings (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> select * from t5;
+------+
| id   |
+------+
| -128 |
|  127 |  # 结果是-128和127 也就意味着默认自带正负号
+------+
2 rows in set (0.00 sec)


# 3.取消默认的正负号
mysql> create table t6(id tinyint unsigned);  # 不要正负号，是一个约束条件
Query OK, 0 rows affected (0.04 sec)

mysql> desc t6;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.03 sec)

mysql> insert t6 values(-129),(128),(1000);
Query OK, 3 rows affected, 2 warnings (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 2

mysql> select * from t6;
+------+
| id   |
+------+
|    0 |
|  128 |
|  255 |
+------+
3 rows in set (0.00 sec)

严格模式
当我们在使用数据库存储数据的时候,如果数据不符合规范,应该直接报错而不是擅自修改数据这样会导致数据的失真(没有实际意义)
正常5.6版本后都应该报错,但是我们之前不小心改了配置文件

# 模糊查询，找出所有带mode的信息
show variables like '%mode%';

1.临时修改
	set session sql_mode='strict_trans_tables';  # 严格表结构
    	在当前客户端有效
 	set global sql_mode='strict_trans_tables';
    	在当前服务端有效，只要服务端不重启，就都有效
2.永久修改
	直接修改配置文件
    放在mysqld下：
    sql_mode=STRICT_TRANS_TABLES

具体展示如下：

# 模糊查询，找出所有带mode的信息

mysql> show variables like '%mode%';
+----------------------------+------------------------+
| Variable_name              | Value                  |
+----------------------------+------------------------+
| binlogging_impossible_mode | IGNORE_ERROR           |
| block_encryption_mode      | aes-128-ecb            |
| gtid_mode                  | OFF                    |
| innodb_autoinc_lock_mode   | 1                      |
| innodb_strict_mode         | OFF                    |
| pseudo_slave_mode          | OFF                    |
| slave_exec_mode            | STRICT                 |
| sql_mode                   | NO_ENGINE_SUBSTITUTION |  # 这一行就显示数据的模式，此时没有约束
+----------------------------+------------------------+
8 rows in set (0.00 sec)

临时修改服务端的配置

set global sql_mode='strict_trans_tables';
show variables like '%mode%';  # 还是之前的配置
exit;

修改服务端的配置后，必须退出客户端重新链接服务端后才会生效，因为这个客户端链接的服务端还是之前的配置。

重新链接服务端

mysql -u root -p

# 修改后的配置
mysql> show variables like '%mode%';
+----------------------------+---------------------+
| Variable_name              | Value               |
+----------------------------+---------------------+
| binlogging_impossible_mode | IGNORE_ERROR        |
| block_encryption_mode      | aes-128-ecb         |
| gtid_mode                  | OFF                 |
| innodb_autoinc_lock_mode   | 1                   |
| innodb_strict_mode         | OFF                 |
| pseudo_slave_mode          | OFF                 |
| slave_exec_mode            | STRICT              |
| sql_mode                   | STRICT_TRANS_TABLES |
+----------------------------+---------------------+
8 rows in set (0.00 sec)

mysql> use db2;
Database changed
mysql> desc t6;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> insert into t6 values(1000);  # 数据不合格会报错
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t6 values(1),(-129),(25);  # 插入的数据只要一个不符合就会直接报错
ERROR 1264 (22003): Out of range value for column 'id' at row 2
mysql> insert into t6 values(33);  # 数据正常就会插入这条记录
Query OK, 1 row affected (0.01 sec)

mysql> select * from t6;
+------+
| id   |
+------+
|    0 |
|  128 |
|  255 |
|   33 |
+------+
4 rows in set (0.00 sec)

2.浮点型

float(255, 30)
总共存储255位数小数点后面占30
double(255, 30)
总共存储255位数小数点后面占30
decimal(65, 30)
总共存储65位数小数点后面占30

三者的核心区别在于精确度不同,绝大部分来说，都选decimal
float < double < decimal

flot只精确到小数点后7、8位，double精确到小数点后15、16位数，decimal精确度最高

mysql> create table t7(id float(60,20));
Query OK, 0 rows affected (0.04 sec)

mysql> create table t8(id double(60,20));
Query OK, 0 rows affected (0.04 sec)

mysql> create table t9(id decimal(60,20));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t7 values(1.11111111111111111111);
Query OK, 1 row affected (0.01 sec)

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

mysql> insert into t9 values(1.11111111111111111111);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t7;
+------------------------+
| id                     |
+------------------------+
| 1.11111116409301760000 |
+------------------------+
1 row in set (0.00 sec)

mysql> select * from t8;
+------------------------+
| id                     |
+------------------------+
| 1.11111111111111120000 |
+------------------------+
1 row in set (0.00 sec)

mysql> select * from t9;
+------------------------+
| id                     |
+------------------------+
| 1.11111111111111111111 |
+------------------------+
1 row in set (0.00 sec)

3.字符类型

char
定长
char(4) 最多存储四个字符，超出就报错，不够四个空格填充至四个
varchar
变长
varchar(4) 最多存储四个字符，超出就报错，不够则有几位存几位

2.char_length()获取字段存储的数据长度
默认情况下MySQL针对char的存储会自动填充空格和删除空格

3.验证方式，设置一个参数,取消自动的机制

set global sql_mode='strict_trans_tables,pad_char_to_full_length';
# 此命令是对sql_mode做替换，要加上之前的报错配置
# 平时使用不需要加上pad_char_to_full_length这个命令
show variables like '%mode%'; # 模糊查询

两者的区别：

char VS varchar
    char
        优势:整存整取，速度快
        劣势:浪费存储空间
	 varchar
    	  优势:节省存储空间 
       	 劣势:存取数据的速度较char慢


jacktonyjasonkevintomjerry
硬盘上的数据是连在一起的，
char有固定的长度，可以直接分析出数据的间隔
varchar是可变长，不清楚当初存储时一个名字是几位，当你不知道你要拿到手的数据有多长，提前发个报头，先取出报头，解析数据的长度，所以存取速度较慢。
1bytes+jack1bytes+tony1bytes+jason1bytes+kevin1bytes+tom1bytes+jerry
"""
char与varchar的使用需要结合具体应用场景
"""

验证定长和可变长

mysql> create table t10(id int,name char(4));
Query OK, 0 rows affected (0.05 sec)

mysql> create table t11(id int,name varchar(4));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t10 values(1,'jason1');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into t11 values(1,'jason1');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into t10 values(1,'tony');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t11 values(1,'jack');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t10 values(2,'k');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t11 values(2,'f');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t10;
+------+------+
| id   | name |
+------+------+
|    1 | tony |
|    2 | k    |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from t11;
+------+------+
| id   | name |
+------+------+
|    1 | jack |
|    2 | f    |
+------+------+
2 rows in set (0.00 sec)

char_length()获取字段存储的数据长度

mysql> select char_length(name) from t10;  # char_length()获取字段存储的数据长度
+-------------------+
| char_length(name) |
+-------------------+
|                 4 |
|                 1 |  # 默认情况下MySQL针对char的存储会自动填充空格和取出会自动删除空格
+-------------------+
2 rows in set (0.01 sec)

mysql> select char_length(name) from t11;
+-------------------+
| char_length(name) |
+-------------------+
|                 4 |
|                 1 |
+-------------------+
2 rows in set (0.00 sec)

验证char不够四个空格填充至四个
默认情况下MySQL针对char的存储会自动填充空格和取出会自动删除空格，需要先取消自动的机制。

set global sql_mode='strict_trans_tables,pad_char_to_full_length';  # 此命令是对sql_mode做替换，要加上之前的报错配置
# 平时使用不需要加上pad_char_to_full_length这个命令
exit;
mysql -u root -p


#######
mysql> show variables like '%mode%';
+----------------------------+---------------------------------------------+
| Variable_name              | Value                                       |
+----------------------------+---------------------------------------------+
| binlogging_impossible_mode | IGNORE_ERROR                                |
| block_encryption_mode      | aes-128-ecb                                 |
| gtid_mode                  | OFF                                         |
| innodb_autoinc_lock_mode   | 1                                           |
| innodb_strict_mode         | OFF                                         |
| pseudo_slave_mode          | OFF                                         |
| slave_exec_mode            | STRICT                                      |
| sql_mode                   | STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH |
+----------------------------+---------------------------------------------+
8 rows in set (0.00 sec)

mysql> use db2;
Database changed
mysql> select char_length(name) from t10;
+-------------------+
| char_length(name) |
+-------------------+
|                 4 |
|                 4 |  # 参数改变后，定长char不够四个空格填充至四个
+-------------------+
2 rows in set (0.00 sec)

mysql> select char_length(name) from t11;
+-------------------+
| char_length(name) |
+-------------------+
|                 4 |
|                 1 |  # 可变长varchar是有几个填充几个
+-------------------+
2 rows in set (0.00 sec)

数字的含义

数字在很多地方都是用来表示限制存储数据的长度
但是在整型中数字却不是用来限制存储长度，而是用来控制展示的位数

create table t12(id int(3));  不是用来限制长度
insert into t12 values(999999);

create table t13(id int(5) zerofill);  而是用来控制展示的长度
# 不够5位，用0填充到5位，够5位直接展示。
insert into t13 values(123),(123456789);


create table t14(id int);
"""以后写整型无需添加数字"""

# MySQL整型会自动添加数字，想要用0填充可以在数据结构后面加入约束条件

反向验证

mysql> create table t12(id int(3));
Query OK, 0 rows affected (0.04 sec)

mysql> desc t12;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | int(3) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> insert into t12 values(12345);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t12 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t12;
+-------+
| id    |
+-------+
| 12345 |
|     2 |
+-------+
2 rows in set (0.00 sec)

括号内的整数是用来控制展示的长度

mysql> create table t13(id int(5) zerofill);  # 不够5位，用0填充到5位，够5位直接展示。
Query OK, 0 rows affected (0.04 sec)

mysql> desc t13;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id    | int(5) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
1 row in set (0.03 sec)

mysql> insert into t13 values(123),(123456789);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t13;
+-----------+
| id        |
+-----------+
|     00123 |
| 123456789 |
+-----------+
2 rows in set (0.00 sec)

MySQL整型会自动添加数字，想要用0填充可以在数据结构后面加入约束条件

mysql> create table t14(id int zerofill);
Query OK, 0 rows affected (0.04 sec)

mysql> desc t14;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id    | int(10) unsigned zerofill | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
1 row in set (0.02 sec)

mysql> insert into t14 values(123),(123456789);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t14;
+------------+
| id         |
+------------+
| 0000000123 |
| 0123456789 |
+------------+
2 rows in set (0.00 sec)

4.日期类型

datetime		年月日时分秒
date			年月日
time			时分秒
year			年
# 用的最多的就是datetime

create table t17(
	id int,
  	name varchar(32),
 	register_time datetime,
 	birthday date,
 	study_time time,
 	work_time year
);
desc t17;
insert into t17 values(1,'jason','2023-04-04 11:11:11','1998-01-21','11:11:11','2000');
select * from t17;
ps:以后涉及到日期相关字段一般都是系统自动获取，无需我们操作

代码：

mysql> create table t17(
    -> id int,
    -> name varchar(32),
    -> register_time datetime,
    -> birthday date,
    -> study_time time,
    -> work_time year
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc t17;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id            | int(11)     | YES  |     | NULL    |       |
| name          | varchar(32) | YES  |     | NULL    |       |
| register_time | datetime    | YES  |     | NULL    |       |
| birthday      | date        | YES  |     | NULL    |       |
| study_time    | time        | YES  |     | NULL    |       |
| work_time     | year(4)     | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

mysql> insert into t17 values(1,'jason','2000-12-12 11:11:11','1999-01-01','13:13:13','2000');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t17;
+------+-------+---------------------+------------+------------+-----------+
| id   | name  | register_time       | birthday   | study_time | work_time |
+------+-------+---------------------+------------+------------+-----------+
|    1 | jason | 2000-12-12 11:11:11 | 1999-01-01 | 13:13:13   |      2000 |
+------+-------+---------------------+------------+------------+-----------+
1 row in set (0.00 sec)

5.枚举与集合

枚举
	多选一
	create table t15(
    	id int,
      	name varchar(32),
       gender enum('male','female','others')
    );
 	insert into t15 values(1,'tony','aaa');
  	insert into t15 values(2,'jason','male');
 	insert into t15 values(3,'kevin','others');

集合
	多选多(多选一)
	create table t16(
    	id int,
      	name varchar(16),
       hobbies set('basketabll','football','doublecolorball')
    );
 	insert into t16 values(1,'jason','study');
 	insert into t16 values(2,'tony','doublecolorball');
	insert into t16 values(3,'kevin','doublecolorball,football');

代码验证：
1.枚举

mysql> create table t15(
    -> id int,
    -> name varchar(32),
    -> gender enum('male','female','others')
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> desc t15;
+--------+--------------------------------+------+-----+---------+-------+
| Field  | Type                           | Null | Key | Default | Extra |
+--------+--------------------------------+------+-----+---------+-------+
| id     | int(11)                        | YES  |     | NULL    |       |
| name   | varchar(32)                    | YES  |     | NULL    |       |
| gender | enum('male','female','others') | YES  |     | NULL    |       |
+--------+--------------------------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

mysql> insert into t15 values(1,'tony','aaa');  # 不是3种类型之一会报错
ERROR 1265 (01000): Data truncated for column 'gender' at row 1  
mysql> insert into t15 values(2,'jason','male');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t15 values(3,'kevin','others');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t15;
+------+-------+--------+
| id   | name  | gender |
+------+-------+--------+
|    2 | jason | male   |
|    3 | kevin | others |
+------+-------+--------+
2 rows in set (0.00 sec)

2.集合

mysql> create table t16(
    -> id int,
    -> name varchar(16),
    -> hobby set('basketball','music','read')
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc t16;
+-------+----------------------------------+------+-----+---------+-------+
| Field | Type                             | Null | Key | Default | Extra |
+-------+----------------------------------+------+-----+---------+-------+
| id    | int(11)                          | YES  |     | NULL    |       |
| name  | varchar(16)                      | YES  |     | NULL    |       |
| hobby | set('basketball','music','read') | YES  |     | NULL    |       |
+-------+----------------------------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

mysql> insert into t16 values(1,'jason','study');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1  # 不是其中之一会报错
mysql> insert into t16 values(1,'jason','music');  # 可以多选一
Query OK, 1 row affected (0.01 sec)

mysql> insert into t16 values(2,'tony','music,basketball');  # 可以多选多
Query OK, 1 row affected (0.01 sec)

mysql> select * from t16;
+------+-------+------------------+
| id   | name  | hobby            |
+------+-------+------------------+
|    1 | jason | music            |
|    2 | tony  | basketball,music |
+------+-------+------------------+
2 rows in set (0.00 sec)

六、表完整性约束

1.介绍

约束条件就是在数据类型的基础上在添加额外的限制

约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性主要分为：

unsigned            无符号
zerofill            使用0填充

not null            标识该字段不能为空
default             为该字段设置默认值
unique              标识该字段的值是唯一的
primary key         标识该字段为该表的主键，可以唯一的标识记录
auto_increment      标识该字段的值自动增长（整数类型，而且为主键）
foreign key         标识该字段为该表的外键

说明

1. Null栏
是否允许为空，默认NULL(YES)，可设置NOT NULL(NO)，字段不允许为空，必须赋值

2. Default栏
字段是否有默认值，空缺的默认值是NULL。设置后，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20

3. Key栏
唯一 unique           (UNI)
主键 primary key      (PRI)
外键 foreign          (MUL)
索引 (index,unique...)

4. Extra栏
自增 auto_increment   (auto_increment)

2.unsigned、zerofill

无符号(unsigned):只能写非负数

零填充:当插入的数值比定义的属性长度小的时候,会在数值前面进行补值操作，用0来补充。

验证：
1.unsigned

mysql> create database db3;
Query OK, 1 row affected (0.01 sec)

mysql> use db3;
Database changed

mysql> create table t0(id int unsigned);
Query OK, 0 rows affected (0.04 sec)

mysql> desc t0;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.03 sec)

mysql> insert into t0 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t0 values(129);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t0;
+------+
| id   |
+------+
|    1 |
|  129 |
+------+
2 rows in set (0.00 sec)

mysql> insert into t0 values(-3);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

2.zerofill

3. not null

是否可空，null表示空

create table t1(
	id int,
 	name varchar(16)
);
insert into t1(id) values(1);
insert into t1(name) values('jason');
insert into t1(name,id) values('kevin',2);
insert into t1 values(null,null);  # 可以这样插入记录但是没有意义
ps:所有字段类型不加约束条件的情况下默认都可以为空
    
 
create table t2(
	id int,
 	name varchar(16) not null  # 不能为空，是一个约束条件，但是两个字母中间要加空格
);
insert into t2(id) values(1);  # 会报错
insert into t2(name) values('jason');  
insert into t2 values(1,''); 
insert into t2 values(2,null);  # 报错

'''在sql中，NULL和''(空字符)不一样'''

验证

mysql> create table t1(
    -> id int,
    -> name varchar(16)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1(id) values(1);  # 所有字段类型不加约束条件的情况下默认都可以为空
Query OK, 1 row affected (0.02 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> insert into t1(name)values('jason');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | NULL  |
| NULL | jason |
+------+-------+
2 rows in set (0.00 sec)

mysql> insert into t1(name,id) values('kevin',2);  # 可以换位置，值只要符合表括号后面的顺序就行
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | NULL  |
| NULL | jason |
|    2 | kevin |
+------+-------+
3 rows in set (0.00 sec)

mysql> insert into t1 values(null,null);  # 可以这样插入记录但是没有意义
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | NULL  |
| NULL | jason |
|    2 | kevin |
| NULL | NULL  |
+------+-------+
4 rows in set (0.00 sec)

加个约束条件，非空

mysql> create table t2(
    -> id int,
    -> name varchar(16) not null  # 不能为空，是一个约束条件，但是两个字母中间要加空格
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | NO   |     | NULL    |       |  # Null栏发生了变化，变成了NO
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> insert into t2(id) values(1);  # 会报错，因为name设置了非空
ERROR 1364 (HY000): Field 'name' doesn't have a default value                        '
mysql> insert into t2(name) values('jason');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values(1,'');  # 这个比较特殊，只要不是null，空字符也可以
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values(2,null);  # null就会报错
ERROR 1048 (23000): Column 'name' cannot be null

4.default

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值。

默认值后面设置的值也要符合该字段结构。

create table t3(
	id int default 666,
	name varchar(16) default '匿名'
);
insert into t3(id) values(1);  # 没有添加name,就会用默认的值
insert into t3(name) values('jason');
insert into t3 values(2,'kevin');  # 两个值都添加，就直接用添加的值

默认值配合非空一起使用：

create table group1(
    id int not null default 666,
	name varchar(16) default '匿名' not null
);
insert into group1(id) values(1);
insert into group1(name) values('jason');
insert into group1 values(2, 'kevin');
insert into group1 values(3,null);  # 填入空会报错
insert into group1 values();

验证：

mysql> create table t3(
    -> id int default 666,
    -> name varchar(16) default '匿名'
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | 666     |       |  
| name  | varchar(16) | YES  |     | 匿名    |       |  # Default栏都设置了默认值
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> insert into t3(id) values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t3;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 匿名   |  # 只添加了id,没有添加对应name,name就会用默认的值
+------+--------+
1 row in set (0.00 sec)

mysql> insert into t3(name) values('jason');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 匿名   |
|  666 | jason  |
+------+--------+
2 rows in set (0.00 sec)

mysql> insert into t3 values(2, 'kevin');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t3;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 匿名   |
|  666 | jason  |
|    2 | kevin  |  # 两个值都添加，就直接用添加的值
+------+--------+
3 rows in set (0.00 sec)

默认值配合非空一起使用

mysql> create table group1(
    -> id int not null default 666,
    -> name varchar(16) default '匿名' not null
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc group1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | 666     |       |
| name  | varchar(16) | NO   |     | 匿名    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> insert into group1(id) values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from group1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 匿名   |
+----+--------+
1 row in set (0.00 sec)


mysql> insert into group1(name) values('jason');
Query OK, 1 row affected (0.00 sec)

mysql> insert into group1 values(2,'kevin');
Query OK, 1 row affected (0.01 sec)

mysql> insert into group1 values(3,null);  # 填入空会报错
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into group1 values();
Query OK, 1 row affected (0.01 sec)

mysql> select * from group1;
+-----+--------+
| id  | name   |
+-----+--------+
|   1 | 匿名   |
| 666 | jason  |
|   2 | kevin  |
| 666 | 匿名   |
+-----+--------+
4 rows in set (0.00 sec)

5.unique

唯一值，也叫唯一键。

1.单列唯一：对每个字段进行约束,单个不能重复
create table t4(
	id int,
	name varchar(32) unique
);
insert into t4 values(1,'jason'),(2,'jason');  # 会报错
insert into t4 values(1,'jason');
insert into t4 values(2,'tom'); 
insert into t4 values(1,'oscar');  # id没有设置唯一，id可以重复


2.联合唯一：几个字段组合(拼)在一起的结果必须是唯一的，单个字段可以重复
create table t5(
	id int,
 	ip varchar(32),
 	port int,
 	unique(ip,port)
);
desc t5;
show create table t5;  # 可以直接看到唯一的约束条件
insert into t5 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.2',8080);
insert into t5 values(4,'127.0.0.1',8080);  # ip-port一致了，报错

验证：
1.单列唯一：对每个字段进行约束,单个不能重复

mysql> create table t4(
    -> id int,
    -> name varchar(32) unique
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc t4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> insert into t4 values(1,'jason'),(2,'jason');  # name设置的是唯一，输入两个相同的name值就会报错
ERROR 1062 (23000): Duplicate entry 'jason' for key 'name'

mysql> insert into t4 values(1,'jason');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t4 values(2,'tom');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values(1,'oscar');  # id没有设置唯一条件，就可以重复
Query OK, 1 row affected (0.01 sec)

mysql> select * from t4;
+------+-------+
| id   | name  |
+------+-------+
|    1 | jason |
|    2 | tom   |
|    1 | oscar |
+------+-------+
3 rows in set (0.00 sec)

2.联合唯一：几个字段组合(拼)在一起的结果必须是唯一的，单个字段可以重复

mysql> create table t5(
    -> id int,
    -> ip varchar(32),
    -> port int,
    -> unique(ip,port)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> desc t5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| ip    | varchar(32) | YES  | MUL | NULL    |       |
| port  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> show create table t5;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t5    | CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL,
  `ip` varchar(32) DEFAULT NULL,
  `port` int(11) DEFAULT NULL,
  UNIQUE KEY `ip` (`ip`,`port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> insert into t5 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.2',8080);  # ip可以重复，port可以重复，只要ip-port不会一样就不会报错
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t5;
+------+-----------+------+
| id   | ip        | port |
+------+-----------+------+
|    1 | 127.0.0.1 | 8080 |
|    2 | 127.0.0.1 | 8081 |
|    3 | 127.0.0.2 | 8080 |
+------+-----------+------+
3 rows in set (0.00 sec)

mysql> insert into t5 values(4,'127.0.0.1',8080);  # ip-port一致了，报错
ERROR 1062 (23000): Duplicate entry '127.0.0.1-8080' for key 'ip'

6.primary key

1.单从约束层面上而言主键相当于not null + unique(非空且唯一)
主键可以加快查询速度!!!因为主键本质上也是一种索引!!!

	create table t6(
    	id int primary key,
    	name varchar(32)
    );
	insert into t6(name) values('jason');  # 会报错
 	insert into t6 values(1,'kevin'); 
 	insert into t6 values(1,'jerry');  # 报错

2.InnoDB存储引擎规定了所有的表都必须有且只有一个主键(主键是组织数据的重要条件并且主键可以加快数据的查询速度)
可以看成是新华字典的目录

当表中没有主键也没有其他非空切唯一的字段的情况下

InnoDB存储引擎内部有一个隐藏的字段，作为了主键，我们看不到，并且，这个隐藏的主键不能加快查询速度，就是因为InnoDB存储引擎建表就是有主键来构成的，加这个隐藏的字段作为主键，仅仅是为了把表创建成功。

隐藏意味着无法使用，基于该表的数据查询只能一行行查找，速度很慢
当表中没有主键但是有其他非空且唯一的字段，那么会从上往下将第一个该字段自动升级为主键

create table t7(
  id int,
  age int not null unique,  # age会自动变成主键
  phone bigint not null unique,
  birth int not null unique,
  height int not null unique
);
        
'''
  我们在创建表的时候应该有一个字段用来标识数据的唯一性，并且该字段通常情况下就是'id'(编号)字段
  字段名可以是：id nid sid pid gid uid
'''

如何给字段添加主键

create table userinfo(
    uid int primary key,  # 这个id字段就已经有了主键的特性
    name varchar(32)
);

验证：

约束层面,主键是：not null + unique(非空且唯一)

mysql> create table t6(
    -> id int primary key,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc t6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |  # Null是NO,Key栏变成PRI
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> insert into t6(name) values('jason');  # 没有输入id就会报错
ERROR 1364 (HY000): Field 'id' doesn't have a default value                                          '
mysql> insert into t6 values(1,'kevin');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t6 values(1,'jerry');  # id是唯一的，输入第二次就会报错
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from t6;
+----+-------+
| id | name  |
+----+-------+
|  1 | kevin |
+----+-------+
1 row in set (0.00 sec)

表中没有主键,将第一个该字段自动升级为主键

mysql> create table t7(
    -> id int,
    -> age int not null unique,
    -> birth int not null unique,
    -> height int not null unique
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc t7;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | YES  |     | NULL    |       |
| age    | int(11) | NO   | PRI | NULL    |       |  # age会自动变成主键PRI
| birth  | int(11) | NO   | UNI | NULL    |       |
| height | int(11) | NO   | UNI | NULL    |       |
+--------+---------+------+-----+---------+-------+
4 rows in set (0.03 sec)

7.auto_increment

自增，该约束条件不能单独出现，并且一张表中只能出现一次，主要就是配合主键一起用

1.自增含义：在原来的基础上，每次加1

create table t8(
	id int primary key,
 	name varchar(32)
); 
# 此时的编号录入要唯一，但是我们可能会忘记录入到了几号，所以就有了自增

create table t9(
	id int primary key auto_increment,  # 自增
 	name varchar(32)
);
isert into t9(name) values ('jason'),('kevin'),('tom'),('jack'),('tony')
select * from t9;  # 以后插入数据每次需要写上字段名

2.自增特性
自增不会因为数据的删除而回退，永远自增往前
如果自己设置了更大的数，则之后按照更大的往前自增

如果想重置某张表的主键值，可以使用
	delete t9; 删除表数据
	truncate t9;  清空表数据并重置主键

3.以后我们在创建id字段的时候，固定语法结构：

id int primary key auto_increment
# 主键字段在添加值的时候，就不用在单独添加了，而是自动生成.
'''一张表中，我们可以通过主键字段来唯一确定一条记录'''

验证：

mysql> create table t8(
    -> id int primary key,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> desc t8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

创建自增

mysql> create table t9(
    -> id int primary key auto_increment,
    -> name varchar(32)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> desc t9;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |  # Extra栏增加了自增
| name  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)

mysql> insert into t9(name) values('jason'),('kevin'),('tom'),('jack'),('tony');  # 编号就不需要写了，但是这样表名后的括号内就需要把剩下的字段都写上
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t9;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | kevin |
|  3 | tom   |
|  4 | jack  |
|  5 | tony  |
+----+-------+
5 rows in set (0.00 sec)

mysql> insert into t9(name) values('jason'),('kevin'),('tom'),('jack'),('tony');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> insert into t9(name) values('jason'),('kevin'),('tom'),('jack'),('tony');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t9;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | kevin |
|  3 | tom   |
|  4 | jack  |
|  5 | tony  |
|  6 | jason |
|  7 | kevin |
|  8 | tom   |
|  9 | jack  |
| 10 | tony  |
| 11 | jason |
| 12 | kevin |
| 13 | tom   |
| 14 | jack  |
| 15 | tony  |
+----+-------+
15 rows in set (0.00 sec)

删除记录后，编号还是在原来编号顺序往后加

mysql> delete from t9 where id>3;
Query OK, 12 rows affected (0.02 sec)

mysql> select * from t9;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | kevin |
|  3 | tom   |
+----+-------+
3 rows in set (0.00 sec)

mysql> insert into t9(name) values('lili');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | kevin |
|  3 | tom   |
| 16 | lili  |
+----+-------+
4 rows in set (0.00 sec)

mysql> insert into t9(id,name) values(4,'haha');  # 可以自己操作编号
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | kevin |
|  3 | tom   |
|  4 | haha  |
| 16 | lili  |
+----+-------+
5 rows in set (0.00 sec)

mysql> insert into t9(name) values('tong');  # 只要自己没有操作编号，就是按照之前的编号顺序往后加
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | kevin |
|  3 | tom   |
|  4 | haha  |
| 16 | lili  |
| 17 | tong  |
+----+-------+
6 rows in set (0.00 sec)

如果自己设置了更大的数，则之后按照更大的往前自增

mysql> insert into t9(id,name) values(2000,'乌拉拉');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | jason     |
|    2 | kevin     |
|    3 | tom       |
|    4 | haha      |
|   16 | lili      |
|   17 | tong      |
| 2000 | 乌拉拉    |
+------+-----------+
7 rows in set (0.00 sec)

mysql> insert into t9(name) values('程程程');   # 会从2000之后往后加
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | jason     |
|    2 | kevin     |
|    3 | tom       |
|    4 | haha      |
|   16 | lili      |
|   17 | tong      |
| 2000 | 乌拉拉    |
| 2001 | 程程程    |
+------+-----------+
8 rows in set (0.00 sec)

delete t9; 删除表数据

mysql> delete from t9;
Query OK, 8 rows affected (0.01 sec)

mysql> select * from t9;
Empty set (0.00 sec)

mysql> insert into t9(name) values('楚楚楚');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+------+-----------+
| id   | name      |
+------+-----------+
| 2002 | 楚楚楚    |  # 编号仍然是往后自增，delete删除数据没有用
+------+-----------+
1 row in set (0.00 sec)

truncate t9; 清空表数据并重置主键

mysql> truncate t9;  # 清空表数据并重置主键,相当于格式化操作
Query OK, 0 rows affected (0.04 sec)

mysql> select * from t9;
Empty set (0.00 sec)

mysql> insert into t9(name) values('呵呵呵');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t9(name) values('呵呵呵');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t9(name) values('呵呵呵');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t9;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 呵呵呵    |
|  2 | 呵呵呵    |
|  3 | 呵呵呵    |
+----+-----------+
3 rows in set (0.00 sec)

8.foreign key

外键字段:其实就是用来标识表与表之间的数据关系的字段。简单的理解为该字段可以让你去到其他表中查找数据，是有约束的，只能出现被关联的字段的编号出现的值。

表与表之间建关系

关系总共有四种：

一对多
多对多
一对一
没有关系
关系的判断可以采用’换位思考’原则。换位思考就是站在双方的角度考虑问题。

外键字段建立

# 建立
小技巧:先定义出含有普通字段的表,之后再考虑外键字段的添加

# 外键的约束
1.在创建表的时候,需要先创建被关联表(没有外键字段的表)
2.在插入新数据的时候,应该先确保被关联表中有数据
3.在插入新数据的时候,外键字段只能填写被关联表中已经存在的数据
4.在修改和删除被关联表中的数据的时候,无法直接操作

# 如果想要数据之间自动修改和删除需要添加额外的配置 >>> :级联更新,级联删除
on update cascade:修改时，关联的数据会自动修改.
on delete cascade:删除时，关联的数据会自动删除.


"""
由于外键有实质性的诸多约束，当表特别多的时候外键的增多反而会增加耦合程度
	所以在实际开发项目中 有时候并不会使用外键创建表关系
	而是通过SQL语句层面 建立逻辑意义上的表关系
		eg:操作员工表的sql执行完毕之后,立刻跟着执行操作部门的sql
"""

一对多关系

以员工表和部门表为例
	1.先站在员工表的角度
    	问:一名员工能否对应多个部门
         答:不可以
 	2.再站在部门表的角度
    	问:一个部门能否对应多名员工
     	答:可以	
	结论:一个可以一个不可以,那么关系就是'一对多'
        员工表是多 部门表是一
	针对'一对多'关系,外键字段建在'多'的一方
    # 表关系没有'多对一'一说 都是'一对多'

代码验证：

# 创建部门表
create table dep(
	id int primary key auto_increment,
 	dep_name varchar(32),
  	dep_desc varchar(64)
);

# 创建员工表
create table emp(
	id int primary key auto_increment,
 	name varchar(32),  # 括号内的数字尽量不要超过255，写16的倍数
 	age int,
 	dep_id int,
	foreign key(dep_id) references dep(id)  # 这个是外键字段，references是关联 表名(关联字段名)
);

# 展示两个表的结构
mysql> desc dep;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| dep_name | varchar(32) | YES  |     | NULL    |                |
| dep_desc | varchar(64) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> desc emp;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(32) | YES  |     | NULL    |                |
| age    | int(11)     | YES  |     | NULL    |                |
| dep_id | int(11)     | YES  | MUL | NULL    |                |  # Key栏显示MUL，是外键
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> insert into dep(dep_name,dep_desc) values('讲师部','教书育人'),('财务部','发放工资');  # 必须先添加部门表数据
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from dep;
+----+-----------+--------------+
| id | dep_name  | dep_desc     |
+----+-----------+--------------+
|  1 | 讲师部    | 教书育人     |
|  2 | 财务部    | 发放工资     |
+----+-----------+--------------+
2 rows in set (0.00 sec)

mysql> insert into emp(name,age,dep_id) values('jason',18,1),('kevin',20,2),('tom',22,2);  # 然后才可以添加员工表数据(有外键的表)
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+----+-------+------+--------+
| id | name  | age  | dep_id |
+----+-------+------+--------+
|  1 | jason |   18 |      1 |
|  2 | kevin |   20 |      2 |
|  3 | tom   |   22 |      2 |
+----+-------+------+--------+
3 rows in set (0.00 sec)

mysql> insert into emp(name,age,dep_id) values('ocase',18,3);  # 被关联表中id不存在就会报错
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db3`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

修改数据的时候外键字段无法修改和删除

mysql> delete from dep where id=1;  # 不能删除，报错
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db3`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))
mysql> update dep set id=200 where id=1;  # 不能修改，报错
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db3`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))

级联更新,级联删除

on update cascade:修改时，关联的数据会自动修改.
on delete cascade:删除时，关联的数据会自动删除.

create table dep1(
	id int primary key auto_increment,
 	dep_name varchar(32),
  	dep_desc varchar(64)
);

create table emp1(
	 id int primary key auto_increment,
 	 name varchar(32),
 	 age int,
 	 dep_id int,
	 foreign key(dep_id) references dep1(id) 
    on update cascade  # 级联更新
    on delete cascade  # 级联删除 
);


# 查看两个表的结构
mysql> desc dep1;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| dep_name | varchar(32) | YES  |     | NULL    |                |
| dep_desc | varchar(64) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> desc emp1;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(32) | YES  |     | NULL    |                |
| age    | int(11)     | YES  |     | NULL    |                |
| dep_id | int(11)     | YES  | MUL | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)


mysql> insert into dep1(dep_name, dep_desc) values('讲师部','讲课'),('财务部','发放工资'),('安保','防护');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from dep1;
+----+-----------+--------------+
| id | dep_name  | dep_desc     |
+----+-----------+--------------+
|  1 | 讲师部    | 讲课         |
|  2 | 财务部    | 发放工资     |
|  3 | 安保      | 防护         |
+----+-----------+--------------+
3 rows in set (0.00 sec)

mysql> insert into emp1(name,age,dep_id) values('jason',18,1),('kevin',20,2),('oscar',32,2),('jarry',35,3);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from emp1;
+----+-------+------+--------+
| id | name  | age  | dep_id |
+----+-------+------+--------+
|  1 | jason |   18 |      1 |
|  2 | kevin |   20 |      2 |
|  3 | oscar |   32 |      2 |
|  4 | jarry |   35 |      3 |
+----+-------+------+--------+
4 rows in set (0.00 sec)

mysql> delete from dep1 where id=3;  # 此时就可以删除了
Query OK, 1 row affected (0.01 sec)

mysql> select * from dep1;  # 部门表第三行删除了
+----+-----------+--------------+
| id | dep_name  | dep_desc     |
+----+-----------+--------------+
|  1 | 讲师部    | 讲课         |
|  2 | 财务部    | 发放工资     |
+----+-----------+--------------+
2 rows in set (0.00 sec)

mysql> select * from emp1;  # dep_id为3的也随之删除了
+----+-------+------+--------+
| id | name  | age  | dep_id |
+----+-------+------+--------+
|  1 | jason |   18 |      1 |
|  2 | kevin |   20 |      2 |
|  3 | oscar |   32 |      2 |
+----+-------+------+--------+
3 rows in set (0.00 sec)

mysql> update dep1 set id=666 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dep1;
+-----+-----------+--------------+
| id  | dep_name  | dep_desc     |
+-----+-----------+--------------+
|   2 | 财务部    | 发放工资     |
| 666 | 讲师部    | 讲课         |
+-----+-----------+--------------+
2 rows in set (0.00 sec)

mysql> select * from emp1;
+----+-------+------+--------+
| id | name  | age  | dep_id |
+----+-------+------+--------+
|  1 | jason |   18 |    666 |
|  2 | kevin |   20 |      2 |
|  3 | oscar |   32 |      2 |
+----+-------+------+--------+
3 rows in set (0.00 sec)

多对多关系

以书籍表与作者表为例
	1.先站在书籍表的角度
  		问:一本书能否对应多个作者
      	答:可以
 	2.再站在作者表的角度
    	问:一个作者能否对应多本书
      	答:可以
   结论:两个都可以，关系就是'多对多'	
 	针对'多对多'不能在表中直接创建，需要新建第三张关系表

代码操作：

create table book(  # 书籍表
	id int primary key auto_increment,
 	title varchar(32),
  	price float(7,2)
);
create table author(  # 作者表
	id int primary key auto_increment,
 	name varchar(32),
  	phone bigint
);
create table book2author(  # 第三张表，用这张表专门存储两张表中间的关系，把外键字段都写在这张表中
	id int primary key auto_increment,
   author_id int,  # 被关联表是作者表
 	foreign key(author_id) references author(id)
  	on update cascade
  	on delete cascade,
   book_id int,  # 被关联表是书表，book_id是几就表示书的id是几
  	foreign key(book_id) references book(id)
  	on update cascade
  	on delete cascade
);


# 展示第三张表的结构
mysql> desc book2author;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| author_id | int(11) | YES  | MUL | NULL    |                |
| book_id   | int(11) | YES  | MUL | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> insert into book(title,price) values('aaa',11.11),('bbb',22.22),('ccc',33.33),('ddd',44.44),('eee',55.55),('fff',66.66);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into author(name,phone) values('jason',100),('kevin',200),('tom',300);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
            
mysql> select * from book;
+----+-------+-------+
| id | title | price |
+----+-------+-------+
|  1 | aaa   | 11.11 |
|  2 | bbb   | 22.22 |
|  3 | ccc   | 33.33 |
|  4 | ddd   | 44.44 |
|  5 | eee   | 55.55 |
|  6 | fff   | 66.66 |
+----+-------+-------+
6 rows in set (0.00 sec)

mysql> select * from author;
+----+-------+-------+
| id | name  | phone |
+----+-------+-------+
|  1 | jason |   100 |
|  2 | kevin |   200 |
|  3 | tom   |   300 |
+----+-------+-------+
3 rows in set (0.00 sec)

mysql> insert into book2author(author_id,book_id) values(1,1),(1,2),(1,3),(2,1),(2,4),(2,5),(3,5),(3,6);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0
            
mysql> select * from book2author;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         1 |       2 |
|  3 |         1 |       3 |
|  4 |         2 |       1 |
|  5 |         2 |       4 |
|  6 |         2 |       5 |
|  7 |         3 |       5 |
|  8 |         3 |       6 |
+----+-----------+---------+
8 rows in set (0.00 sec)

mysql> select * from book2author where author_id=1;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         1 |       2 |
|  3 |         1 |       3 |
+----+-----------+---------+
3 rows in set (0.01 sec)

mysql> select * from book2author where book_id=5;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  6 |         2 |       5 |
|  7 |         3 |       5 |
+----+-----------+---------+
2 rows in set (0.00 sec)

一对一关系

以用户表与用户详情表为例
	1.先站在用户表的角度
  		问:一个用户能否对应多个用户详情
      答:不可以
 	2.再站在用户详情表的角度
    	问:一个用户详情能否对应多个用户
      	答:不可以
   结论:两个都可以,关系就是'一对一'或者没有关系	
 	针对'一对一'外键字段建在任何一方都可以，但是推荐建在查询频率较高(热数据)的表中。冷数据：不经常使用的数据。冷热分离。

代码操作：

create table userdetail(
	id int primary key auto_increment,
  	phone bigint
);

create table user(
	id int primary key auto_increment,
  	name varchar(32),
 	detail_id int unique,  # 需要加个唯一，出现后，下面就不能使用
  	foreign key(detail_id) references userdetail(id)
  	on update cascade
  	on delete cascade
);

mysql> desc user;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(32) | YES  |     | NULL    |                |
| detail_id | int(11)     | YES  | UNI | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> desc userdetail;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(11)    | NO   | PRI | NULL    | auto_increment |
| phone | bigint(20) | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into userdetail(phone) values(100),(200);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into user(name,detail_id) values('kevin',1),('jerry',2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+-------+-----------+
| id | name  | detail_id |
+----+-------+-----------+
|  1 | kevin |         1 |
|  2 | jerry |         2 |
+----+-------+-----------+
2 rows in set (0.00 sec)

mysql> select * from userdetail;
+----+-------+
| id | phone |
+----+-------+
|  1 |   100 |
|  2 |   200 |
+----+-------+
2 rows in set (0.00 sec)

总结：

一对多的外键字段------>多的一方
一对一的外键字段------> 两边都可以，一般建在查询频率较高的一方
多对多的外键字段------>第三张表中

七、修改表ALTER TABLE

1. 修改表名  
      ALTER TABLE 表名 
                          RENAME 新表名;
2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;  # 增加该字段到第一个位置
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名1;  # 增加该字段到字段名1 后面
3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;
4. 修改字段  # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

示例：

1. 修改存储引擎
mysql> alter table service 
    -> engine=innodb;

2. 添加字段
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;

mysql> alter table student10
    -> add stu_num varchar(10) not null after name;                # 添加name字段之后

mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first;          # 添加到最前面

3. 删除字段
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

4. 修改字段类型modify
mysql> alter table student10
    -> modify age int(3);  # # modify只能改字段数据类型完整约束，不能改字段名
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment;    # 修改为主键

5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        

7. 增加主键
mysql> alter table student1
    -> modify name varchar(10) not null primary key;

8. 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null; 

b. 删除主键
mysql> alter table student10                                 
    -> drop primary key;