表相关操作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)

posted @ 2023-04-06 14:53  星空看海  阅读(15)  评论(0编辑  收藏  举报