MySQL字段类型、字符编码与配置文件

字符编码与配置文件

1.\s  查看数据库基本信息

2.my-default.ini  它是windows下MySQL默认的配置文件
	拷贝上述文件并且重命名为my.ini，必须是以my开头的mysql才能识别

3.由于5.6版本编码不统一 会造成乱码 我们需要统一修改>>>:utf8
	将my.ini里的内容修改为:
    '''
    [mysqld]
        character-set-server=utf8
        collation-server=utf8_general_ci
    [client]
        default-character-set=utf8
    [mysql]
        default-character-set=utf8 
    '''
	注意如果配置文件涉及到了mysqld相关的配置修改 那么需要重启服务端才可以生效
    
4.在配置文件中的mysql下提前写好用户名和密码，之后直接用mysql登录就好，不需要再输用户名和密码

存储引擎

1.针对相同的数据采用的不同的存取策略

2.show engines;

3.需要掌握的存储引擎
    MyISAM
        MySQL5.5及之前版本默认的存储引擎
        存取数据的速度 快 但是功能较少 安全性较低
    InnoDB
        MySQL5.5之后版本默认的存储引擎
        存取数据的速度没有MyISAM快 但是支持事务、行锁、外键等诸多功能 
        安全性较高
    Memory
        基于内存的存储引擎 存取数据极快 但是断电立刻丢失
    BlackHole
        黑洞 任何写进去的数据都会立刻丢失 类似于垃圾站
4.不同存储引擎之间底层文件的区别
    create table t1(id int) engine=innodb;
    create table t2(id int) engine=MyISAM;
    create table t3(id int) engine=Memory;
    create table t4(id int) engine=BlackHole;
    创建表其实就是在mysql文件夹下面的data文件夹里的某个数据库文件夹里创建了文件
    不同的存储引擎产生的文件也是不一样的
    InnoDB
    	.frm	表结构
        .ibd	表数据、表索引(加快数据查询)
    MyISAM
    	.frm	表结构
        .MYD	表数据
        .MYI	表索引(加快数据查询)
    Memory
    	.frm	表结构
    BlackHole
    	.frm	表结构

# t1表ENGINE=InnoDB
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)
# t2表ENGINE=MyISAM
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)
# t3表ENGINE=MEMORY
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)
# t4表ENGINE=BLACKHOLE
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)
# 分别向四个表里添加数据
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.02 sec)

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

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

mysql> insert into t4 values(1);
Query OK, 1 row affected (0.00 sec)
# 挨个查看表的内容
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
# 表t3存储引擎为Memory，它的特点就是基于内存的存储数据，重启后数据就会消失
mysql> select * from t3;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
mysql> use db1;
Database changed
mysql> select * from t3;
Empty set (0.00 sec)
# 表t4存储引擎为黑洞，存储进去就删掉，所以没有数据
mysql> select * from t4;
Empty set (0.00 sec)

创建表的完整语法

create table 表名(
    字段名1 字段类型(数字) 约束条件1 约束条件2,
    字段名2 字段类型(数字) 约束条件,
    字段名3 字段类型(数字) 约束条件,
    字段名4 字段类型(数字) 约束条件
)ENGINE=存储引擎 DEFAULT CHARSET=utf8;

1.字段名和字段类型是必须要写的(至少写一个)
2.数字跟约束条件是可选的(可有可无)
3.约束条件可以写多个，空格隔开
4.最后一个字段的结尾不能加逗号

字段类型之整形

1.验证整形是否自带负号
	-------------tinyint-------------
	create table t6(id tinyint);
 	insert into t6 values(-129),(256);
 	
 	mysql> desc t6;
    +-------+------------+------+-----+---------+-------+
    | Field | Type       | Null | Key | Default | Extra |
    +-------+------------+------+-----+---------+-------+
    | id    | tinyint(4) | YES  |     | NULL    |       |
    +-------+------------+------+-----+---------+-------+
    1 row in set (0.01 sec)

    mysql> select * from t6;
    +------+
    | id   |
    +------+
    | -128 |
    |  127 |
    +------+
    2 rows in set (0.00 sec)
    
    mysql> create table t7(id smallint);
	Query OK, 0 rows affected (0.02 sec)
	mysql> insert into t7 values(-32769),(32769);
    Query OK, 2 rows affected, 2 warnings (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 2
	-------------smallint----------
    mysql> desc t7;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | smallint(6) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    1 row in set (0.01 sec)

    mysql> select * from t7;
    +--------+
    | id     |
    +--------+
    | -32768 |
    |  32767 |
    +--------+
    2 rows in set (0.00 sec)
    验证结果：
    	发现自动填写为两个边界值 数据失真 没有实际意义
    	上述所有的整型类型默认都会带有负号

2.自定义移除负号
	'''使用约束条件：unsigned，作用是去掉负号'''
	create table t7(id tinyint unsigned)
	
    """
    插入的数据值超出了数据类型的范围 不应该让其插入并自动修改 没有意义
    数据库应该直接报错(这个特性其实是有的 只是被我们改了>>>:配置文件)

    方式1:命令临时修改
        set session sql_mode='strict_trans_tables'  当前客户端操作界面有效
        set global sql_mode='STRICT_TRANS_TABLES'  服务端不重启永久有效
    方式2:配置文件永久修改
        [mysqld]
            sql_mode='STRICT_TRANS_TABLES'
    """

字段类型之浮点型

float、double、decimal
'''三者都可以存储浮点型数据，但是各自的精确度不一致'''
使用方式：
	float(255,30)
	double(255,30)
	decimal(65,30)
	括号里第一个数字是总共有多少位，第二个数字表示的是小数占多少位
验证精确度问题
	create table t8(id float(255,30));
	create table t9(id double(255,30));
	create table t10(id decimal(65,30));
	insert into t8 values(1.111111111111111111);
	insert into t9 values(1.111111111111111111);
	insert into t10 values(1.111111111111111111);

	mysql> use db1;
	Database changed
	mysql> create table t8(id float(255,30));
	Query OK, 0 rows affected (0.03 sec)

	mysql> create table t9(id double(255,30));
	Query OK, 0 rows affected (0.01 sec)

	mysql> create table t10(id decimal(65,30));
	Query OK, 0 rows affected (0.02 sec)

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

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

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

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

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

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

    得出结论：
        float < double < decimal
        一般情况下float就够了，如果想要精确度完美一点，可以使用字符串来代替

字段类型之字符类型

char
	称之为定长
	char(4) 最大只能存储四个字符，如果超过范围则直接报错
	如果不超出范围，则用空格填充至四个字符
	例如：如果要存储 'a',那么它就会被存储为'a空格空格空格'，
varchar
	称之为变长
	varchar(4) 最大只能存储四个字符，如果超过范围则直接报错
	如果不超出范围，则有几位就存几位
1.验证俩者区别
	create table t11(id int, name char(4));
 	create table t12(id int, name varchar(4));	
 	
	mysql> insert into t11 values(1,'zhang');
	ERROR 1406 (22001): Data too long for column 'name' at row 1
	mysql> insert into t11 values(1,'tony');
	Query OK, 1 row affected (0.00 sec)

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

	mysql> insert into t12 values(1,'zhang');
	ERROR 1406 (22001): Data too long for column 'name' at row 1
	mysql> desc t12;
	结果验证:超出范围俩者都会报错
		注意sql_mode='STRICT_TRANS_TABLES'
2.验证定长与变长
	用到一个小知识：char_length(),返回的是字符串的长度
	mysql> select char_length(name) from t11;
	+-------------------+
	| char_length(name) |
	+-------------------+
	|                 4 |
	|                 1 |
	+-------------------+
	2 rows in set (0.00 sec)

	mysql> select * from t11;
	+------+------+
	| id   | name |
	+------+------+
	|    1 | tony |
	|    1 | a    |
	+------+------+
	2 rows in set (0.00 sec)
	使用char(4)的时候，存进去的时候确实是'a空格空格空格',但是在取出来的时候会自动删掉这三个空格
	然后在读取的时候又会自动将填充的空格移除 如果想取消该机制 需要sql_mode
	set global sql_mode='strict_trans_tables,pad_char_to_full_length';
	上述目录是替换 不是新增 所以之前的配置也要写上
	设置以后:
	mysql> use db1;
        Database changed
        mysql> select char_length(name) from t11;
        +-------------------+
        | char_length(name) |
        +-------------------+
        |                 4 |
        |                 4 |
        +-------------------+
        2 rows in set (0.00 sec)
3.char与varchar哪个会更好 
	char
		整存整取 速度快
		浪费存储空间
  	varchar
		节省存储空间
		存取数据的速度慢于char
 	"""
 	char(4)
 		在硬盘上存储：a空格空格空格son空格jacktom空格lili
 		取得时候就是4个4个拿
 	varchar(4)
 		在硬盘上存储：asonjacktomlili
 		拿的时候就不对了
 		
 		1bytes+a1bytes+son1bytes+jack1bytes+tom1bytes+lili
 		存取数据都需要操作报头(耗时)
 	
 	存储人的姓名>>>:varchar
 	"""
	两者使用频率都很高 现在默认很多时候是varchar

字段后面数字的含义

数字大部分情况下都是用来限制字段的存储长度 但是整型除外!!!
	不是用来限制存储的长度 而是展示的长度

create table t13(id int(3)); 
'''如果想要看到展示范围的话，那么就使用约束条件：zerofill，它的作用是位数不够的话就使用0填充，但是如果超了的话那就超了，全部展示出来'''
create table t14(id int(3) zerofill);

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

mysql> select * from t14;
+--------+
| id     |
+--------+
| 123456 |
|    001 |
+--------+
2 rows in set (0.00 sec)
总结
	以后涉及到整型字段的定义 类型后面不需要加括号写数字 除非有业务需求必须固定位数
    eg:
    	00000000123
    	00123123031

字段类型之枚举和集合

1.枚举
	多选一
    	eg:性别(男 女 其他)

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

mysql> desc t15;
+--------+-----------------------+------+-----+---------+-------+
| Field  | Type                  | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id     | int(11)               | YES  |     | NULL    |       |
| name   | varchar(32)           | YES  |     | NULL    |       |
| gender | enum('male','female') | YES  |     | NULL    |       |
+--------+-----------------------+------+-----+---------+-------+
3 rows in set (0.05 sec)
mysql> insert into t15 values(1,'zuzu','女');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

mysql> insert into t15 values(1,'zuzu','female');
Query OK, 1 row affected (0.04 sec)
总结：提前规定好到底将来可以插入哪些数据
	
2.集合
	多选多(包含多选一)
    	eg:爱好(唱 跳 rap)
create table t16(
	id int,
	name varchar(32),
	hobbies set('read','run','music','rap')
);

mysql> insert into t16 values(1,'titi','read,run');
Query OK, 1 row affected (0.04 sec)

mysql> insert into t16 values(1,'xixi','read');
Query OK, 1 row affected (0.04 sec)
mysql> select * from t16;
+------+------+----------+
| id   | name | hobbies  |
+------+------+----------+
|    1 | zuzu | read,run |
|    1 | titi | read,run |
|    1 | xixi | read     |
+------+------+----------+
3 rows in set (0.00 sec)
总结：提前规定好到底将来可以插入哪些数据，可以选择多个，但是只能是提前录入好的

字段类型之日期类型

字段类型	表示
date	年月日
datetime	年月日时分秒
time	时分秒
year	年份

mysql> create table t17(
    -> id int,
    -> name varchar(32),
    ->  birth date,
    ->  reg_time datetime,
    ->  study_time time,
    ->  join_time year
    -> );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t17 values(1,'jason','2022-8-15','1999-10-02 08:13:11','11:11:11','1995');
Query OK, 1 row affected (0.03 sec)

mysql> select * from t17;
+------+-------+------------+---------------------+------------+-----------+
| id   | name  | birth      | reg_time            | study_time | join_time |
+------+-------+------------+---------------------+------------+-----------+
|    1 | jason | 2022-08-15 | 1999-10-02 08:13:11 | 11:11:11   |      1995 |
+------+-------+------------+---------------------+------------+-----------+
1 row in set (0.00 sec)

字段约束条件

"""
insert into 表名 vlaues()  # 默认按照创建表的字段顺序添加
insert into 表名(字段) vlaues()  # 可以自定义字段顺序
"""
1.unsigned	无负号
	id int unsigned
	'作用于整形，去掉负数'
2.zerofill	零填充
	id int zerofill
	'作用于整形，不超出范围0填充'
3.not null	非空
	name varchar(32) not null
	'飞空不一定是有值，比如说空字符串'
4.default 	默认值
	name varchar(32) default 'jason'
	'发给一些字段提前设置一些默认值，如果后期我们添加数据的时候没有给值，那就使用默认值'
    mysql> create table t18(id int,name varchar(23) not null default 'tony');
    Query OK, 0 rows affected (0.08 sec)

    mysql> desc t18;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  |     | NULL    |       |
    | name  | varchar(23) | NO   |     | tony    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.06 sec)
    mysql> insert into t18(id) values (1);
    Query OK, 1 row affected (0.01 sec)

    mysql> desc t18;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  |     | NULL    |       |
    | name  | varchar(23) | NO   |     | tony    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.06 sec)
5.unique	唯一值
	id int unique   单列唯一

 	host varchar(32)
 	port int		
	unique(host,port)  联合唯一
	例子：
	mysql> create table t20(id int, host varchar(32),port int,unique(host,port));
	Query OK, 0 rows affected (0.04 sec)

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

	mysql> insert into t20 values(1, '127.0.0.1', 8080);
	Query OK, 1 row affected (0.02 sec)

	mysql> insert into t20 values(2, '127.0.0.1', 8080);
	ERROR 1062 (23000): Duplicate entry '127.0.0.1-8080' for key 'host'
	mysql> insert into t20 values(2, '127.0.0.1', 8081);
	Query OK, 1 row affected (0.02 sec)

	mysql> insert into t20 values(2, '127.0.0.2', 8080);
	Query OK, 1 row affected (0.02 sec)

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

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