表相关操作1
目录
一、配置文件
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)
