专题:MySQL(二) 数据库字符集/校对集/MySQL的数据类型/字段属性

淩亂°似流年 2022-05-21 04:24 291阅读 0赞

# 1.数据库字符集 #

## 1.1字符集 ##

字符集: 可见字符在保存和传输的时候对应的二进制代码集合.  
字符集在两个地方用到

1.保存数据
    2.数据传输

## 1.2在存续时使用字符集 ##

1. MySQl可以在服务器上, 数据库, 表, 字段上指定字符编码.
    2. 在服务器上指定字符编码是安装服务器的时候指定的.
    3. 数据库, 表, 字段上是创建时候指定的

create table test( -> name varchar(10) charset utf8 ->)charset=gbk;

### 1.3 gbk, gb2312, utf8的区别 ###

这三种都是常用的字符编码方式, gbk和gb2312(自带)是简体中文的编码, utf8是国际通用编码.  
Python, MySQL, jQuery等等都是开源的产品, 开源产品建议使用utf8编码  
gbk和gb2312都是简体中文, 但是内部编码方式是不一样的.  
在python中

在gbk或gb2312下，一个中文占用2个字节
    在utf8下，一个中文占用3个字节

> 注意:在数据库中如果字符编码是utf8的，一个汉字字符长度就是1，gbk一个汉字的字节长度是2。

create table `emp`( sex varchar(21844) )engine=myisam charset=utf8;
    
    65535/3-1
    
    
    create table `emp2`( sex varchar(32766) )engine=myisam charset=gbk;
    
    65535/2-1

> 注意;数据库如果是UTF8,那么你在此库中建立的’表’如果没有设定字符集,默认使用数据库的字符集  
> ![这里写图片描述][70]

## 1.4 在传输的时候字符编码 ##

![这里写图片描述][70 1]  
![这里写图片描述][70 2]  
mysql 服务器接收的编码

现在客户端发送的编码和服务器接收的编码不一致

解决:#windows默认的是GBKset character_set_client=gbk;字符编码不一样返回的就不一样返回的是utf8   windows不认识会乱码解决:set character_set_results=gbk;小技巧:set names gbk; ->set names 可以一次设置修改3个字符编码;mysql> show variables like 'character_%';+--------------------------+-----------------------------------------------+| Variable_name            | Value                                         |+--------------------------+-----------------------------------------------+| character_set_client | gbk || character_set_connection | gbk || character_set_database | gbk || character_set_filesystem | binary || character_set_results | gbk || character_set_server | utf8 || character_set_system | utf8 || character_sets_dir | C:\phpStudy\PHPTutorial\MySQL\share\charsets\ |+--------------------------+-----------------------------------------------+8 rows in set (0.00 sec)

# 2.校对集 #

在某种字符集下, 字符和字符的关系成为校对集. 比如(ASCII)a和B的大小关系, 如果区分大小写 a>B, 如果不区分大小写a

#utf8_general_ci  使用_ci这种校对集不区分大小写
    create table t1( name char(1) )charset=utf8 collate=utf8_general_ci;
    
    create table t2( name char(1) )charset=utf8 collate=utf8_bin;
    
    insert into t1 values ('a'),('B');
    insert into t2 values ('a'),('B');

mysql> select * from t1 order by name; +------+
    | name | +------+
    | a    |
    | B | +------+
    2 rows in set (0.00 sec)
    
    #排序查询以后没有区分大小写说明a和b不区分大小写;

mysql> select * from t2 order by name; +------+
    | name | +------+
    | B    |
    | a | +------+
    2 rows in set (0.00 sec)
    
    #按照正序排列,对照ASCLL编码,B<a,B在a前面那么这种二进制校对集区分大小写;

牢记:
    _bin：按二进制编码比较
    _ci：不区分大小写比较

#显示所有校对集
    mysql> show collation;

# 3.MySQL的数据类型–值类型 #

## (1)整型 ##

<table> 
 <thead> 
  <tr> 
   <th align="left">整型</th> 
   <th>占用字节</th> 
   <th align="left">范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">tinyint</td> 
   <td>1</td> 
   <td align="left">-2 ^7 ~ 2^7-1 (-128~127)</td> 
  </tr> 
  <tr> 
   <td align="left">smallint</td> 
   <td>2</td> 
   <td align="left">-2 ^15 ~ 2^15-1 (-32768~32765)</td> 
  </tr> 
  <tr> 
   <td align="left">mediumint</td> 
   <td>3</td> 
   <td align="left">-2 ^23 ~ 2^23-1 (-8388608~8388607)</td> 
  </tr> 
  <tr> 
   <td align="left">int</td> 
   <td>4</td> 
   <td align="left">-2 ^31~ 2^31-1 (-2147483648~2147483647)</td> 
  </tr> 
  <tr> 
   <td align="left">bigint</td> 
   <td>8</td> 
   <td align="left">-2 ^63 ~ 2^63-1 (太大了)</td> 
  </tr> 
 </tbody> 
</table>

## (2)unsigned(无符号) ##

一个数是无符号数, 那么这个数肯定是非负数  
`数据库mysql第一条记录一定是1(起码是),绝对不能是0`

无符号数的范围相当于是有符号数的两倍.

mysql> create table test1(
        -> age tinyint unsigned     -> ); Query OK, 0 rows affected (0.00 sec)
    
    mysql> insert into test1 values (128);
    Query OK, 1 row affected (0.00 sec)

## (3)显示宽度 ##

整形支持显示宽度, 显示宽度就是最小的显示位数, 比如int(11)表示最少用11  
位数字表示这个数, 如果不够用0来做前导. 默认情况下显示位数不起作用, 必须集合zerofill才起作用

create table stu(
    id int(5),
    age int(5) zerofill
    );
    
    #插入测试数据
    insert into stu values (1,23),(2,123456);
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from stu; +------+--------+
    | id | age | +------+--------+
    |    1 |  00023 |
    | 2 | 123456 | #超过显示宽度5就显示值本身 +------+--------+

显示宽度不决定整形的显示大小, 只是在值的位置不够的时候用前导0来填充, 如果超过显示位数, 就不加前导0, 显示值本身

## (4)浮点型 ##

<table> 
 <thead> 
  <tr> 
   <th>浮点型</th> 
   <th>占用字节</th> 
   <th>范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>float</td> 
   <td>4</td> 
   <td>-3.4E+38 ~ 3.4E+38</td> 
  </tr> 
  <tr> 
   <td>double</td> 
   <td>8</td> 
   <td>-1.8E+308 ~ 1.8E+308</td> 
  </tr> 
 </tbody> 
</table>

create table test2( num1 float, num2 double );
    insert into test2 values(23.123,2.123);
    
    #float理论上只保留小数点一位,根据实际版本
    #double理论上只保留小数点两位, 根据实际版本
    
    insert into test2 values(2.2E2,2.123);
    #也支持科学计数法
    
    insert into test2 values(999.999999999999999999999999999,2.123456);
    #丢失精度

python中我们没有双精度这一类型, float代表浮点数.

## (5)定点数 ##

decimal(M,D),M的最大值是65,D的最大值是30,默认是(10,0)
    
    create table test3(
     num1 float(10,2),
     num2 decimal(10,2)
     );
    
    insert into test3 values(123.57,1234567.00);
    mysql> select * from test3; +-------------+-------------+
    | num1 | num2 | +-------------+-------------+
    | 12345679.00 |      123.57 |
    | 12345679.00 | 12346623.57 | +-------------+-------------+
    2 rows in set (0.00 sec)
    
    
    #定点数可以保留多个小数点位,它在有些情况下也会失去精度.浮点数的执行效率要高于定点数.
    #浮点数和定点数都支持 无符号.

# 4.MySQL的数据类型–字符类型 #

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>char</td> 
   <td>定长(255)</td> 
  </tr> 
  <tr> 
   <td>varchar</td> 
   <td>可变长度(65535)</td> 
  </tr> 
  <tr> 
   <td>tinytext</td> 
   <td>2^8-1 (255)</td> 
  </tr> 
  <tr> 
   <td>text</td> 
   <td>2^16-1 (65535)</td> 
  </tr> 
  <tr> 
   <td>mediumtext</td> 
   <td>2^24-1</td> 
  </tr> 
  <tr> 
   <td>longtext</td> 
   <td>2^32-1</td> 
  </tr> 
 </tbody> 
</table>

char(10)和varchar(10)
    
        相同点:都是不能超过10个字符
        不同点:char你输入5个字符,它还是占用了10个字符
            varchar你输入5个字符,他会返还多于的空间
            char的最大长度是255
            varchar的理论长度是65535
            实际上达不到,我使用的是gbk(mysql中占用2个字符),那么65535/2.

create table test( name varchar(32766) )charset=gbk;
    
    
    #utf8在创建的时候报错了,最多支持21845个字符,因为utf8一个汉字要使用3个字符
    
    create table test( name varchar(65535) )charset=gbk #utf8在创建的时候报错了,最多支持32767个字符,因为gbk一个汉字要使用2个字符

# 5.MySQL数据类型–枚举(enum) #

列出一些选项的, 单选.

create table test( name varchar(32), sex enum('男','女','保密') );
    
    insert into test values('贾乃亮','男');
    #插入正常
    insert into test values('PGONE','人妖');
    #插入报错

枚举型在数据库内部是通过整型来管理的,第一个值1,第二个值是2,依次向后推
    
    insert into test values('王宝强',1);
    insert into test values('刘运斌',3);
    
    
    枚举型的优点:
    1.限制值
    2.节省空间
    3.运行效率高
    思考:已知枚举型占用2个字节,请问最多可以设置多少个枚举值?
    
    2个字节16位,2^16-1,最多可保存65535/2个值

# 6.MySQL数据类型–集合(set) #

列出数据类型, 保存多选

create table test2( name varchar(32), hobby set('吃','睡觉','看书','鉴黄') );
    
    insert into test2 values('刘运斌','吃,睡觉,鉴黄');
    insert into test2 values('刘运斌-2','吃,睡觉,李小璐');

集合和枚举一样,为每个元素分配一个固定的值,分配方式不一样.
    它的管理也是使用整型
    集合是这么分配的:
    '吃','睡觉','看书','鉴黄'
    
    2^0   2^1    2^2   2^3  如果后面还有依次向后推
    保存的时候,把多个值(转换为整型了)加起来,这样值会边的很大(浪费资源)
    一个集合他的元素通过一个位表示,有几个集合元素就需要几个位.
    
    思考:已知集合占用8个字节,那么集合能保存多少个选项?
    只能保存64个选项.

# 7.MySQL数据类型–日期时间型 #

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>date</td> 
   <td>日期,占用8个字节</td> 
  </tr> 
  <tr> 
   <td>time</td> 
   <td>时间</td> 
  </tr> 
  <tr> 
   <td>datetime</td> 
   <td>日期时间</td> 
  </tr> 
  <tr> 
   <td>year</td> 
   <td>年份,占用一个字节</td> 
  </tr> 
  <tr> 
   <td>timestamp</td> 
   <td>时间戳,占用4个字节</td> 
  </tr> 
 </tbody> 
</table>

## (1)datetime ##

#格式 年-月-日 时:分:秒
    create table test4( create_at datetime );
    
    insert into test4 values('2018-01-12 15:00:53');
    insert into test4 values(now());#表示的是当前的时间
    insert into test4 values('10000-12-31 23:59:59'); #错误

## (2)date ##

仅表示时间的日期部分

## (3)time ##

表示时间部分, 也表示时间间隔, 范围是`-838:59:59 ~ 838:59:59`

create table test5( create_at time );
    
    insert into test5 values('12:12:12');
    insert into test5 values('212:12:12');
    insert into test5 values('-800:12:12');
    insert into test5 values('-839:59:59');  #错误的,超出范围
    
    #time支持以天的方式表示时间间隔
    insert into test5 values('10 12:59:59');

## (4)timestamp ##

从1970年1月1日 00:00:00秒的格林威治时间开始计算,在python中是特殊类型,但在mysql中显示为datatime格式

create table test6( create_at timestamp );
    
    insert into test6 values(now());
    insert into test6 values('2018-01-12 15:43:30');
    insert into test6 values('2038-01-19 11:14:07');#这里就是一个节点
    
    \
    insert into test6 values('2038-01-19 11:14:08');#超过了4个字节
    timestamp在格式上和datetime是一样的,它们的区别在于:
    datetime 从1到9999,而timestamp从1970年~2038年.
    timestamp占用4个字节,到2038年超过4个字节的长度了.

## (5)year ##

占用一个字节, 只能是255个数, 以1900年为基数, 范围1900+1 ~ 1900+255.

create table test7( create_at year );
    
    insert into test7 values(1900);#错误的
    insert into test7 values(1901);
    insert into test7 values(2155);
    insert into test7 values(2156);#错误的,不在范围内

# 8.布尔型(boolean) #

mysql不支持布尔型, 所以用1和0代替

create table test8( num boolean );
    
    insert into test8 values(True);
    insert into test8 values(1);
    insert into test8 values(False);
    insert into test8 values(0);
    insert into test8 values('A');#错误
    
    布尔型的使用是比较少的,可以用tinyint代替,或者用enum代替

# 9.列属性–是否为空(null|not null) #

create table test9( id int auto_increment primary key not null comment'主键', username varchar(64) not null, pwd char(64) null );
    
    insert into test9 values(null,'admin','');
    insert into test9 values(null,'','');
    insert into test9 values(null,null,'');
    insert into test9 values(null,'',null);
    
    #空字符串不等于null

# 10.列属性–(default) #

create table test10( id int auto_increment primary key not null comment'主键', username varchar(64) not null, pwd char(64) null default '123456' );
    
    insert into test10(username) values('admin2');#推荐的SQL语句
    
    
    create table test11( age int default '123456' );
    insert into test11 values(null);
    #输入null加插入null,不写是默认

# 11.自动增长列 #

auto_increment
    字段值默认从1开始,每次递增1,特点:不会有重复的值,主键常用.
    
    友情提示:在mysql中自动增长的列必须作为主键.
    自动增长的烈在插入的时候可以输入(null)
    
    被删除的id不能被再次使用:
    如果中间的摸个ID值比较大,那么后面插入的id根据最大值依次+1

# 12.列属性–主键 #

primary key
    主键:主键是唯一的
    特点:不能为空,也不能重复
    一个表只有一个主键
    
    CREATE TABLE `test12` ( `id` int(11) NOT NULL AUTO_INCREMENT, `num` int(11) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`,`num`,`age`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    #以上的主键有'3'个,其实一个主键,这个3个字段组成(一个联合主键),在开发中几乎不会被用上
    #(教学使用)
    
    create table demo1( id int auto_increment primary key );
    
    create table demo2( id int auto_increment , primary key(id) );
    
    create table demo3( id int(11) );
    
    alter table demo3 add primary key(id);
    
    选择主键的原则:
    1.最少性:必须选择单个键作为主键
    2.稳定性:作为主键的这个字段最好不要被修改

# 13.列属性–唯一键(unique) #

特点:不能重复, 不能为空  
一个表可以有多个唯一键

create table test13( uid int auto_increment primary key, mobile char(11) unique, email char(32) unique, pwd char(32) );
    
    insert into test13 values(null,'13877776666','this_dog@qq.com','123456');
    
    insert into test13 values(null,'13877776665','this_do@qq.com','123456');
    
    #修改表属性方法添加唯一键
    alter table test13 add unique `my`(mobile);
    
    #删除唯一键还是使用删除的方法
    alter table test13 drop index mobile;#有别名删除别名,没别名删除字段名

# 14.列属性–备注(comment) #

备注用来给程序员相互交流使用的
    有点:起码你能看的动这个字段是用来干吗的.
    
    stauts tinyint comment'描述一个人的状态 1表示死了,2表示活的,0表示半死不活'

# 15.SQL注释 #

python的注释
    # 单行注释
    ''' ''' 多行注释
    
    mysql的注释
    # 单行注释
    --单行注释
    
    /*注释的内容*/ 多行注释

select \* from test13 where uname=” ” or 1=1 –” ” and pwd=” “;  
**练习**

#手机号码一般使用什么数据类型储存?
    字符型 char(11)
    #性别使用什么数据类型?
    字符型 (枚举型) 布尔型
    #年龄用什么?
    整型 tinyint unsigned
    #照片用什么?
    binary  字符(只保存路径,不保存资源)
    varchar()
    #工资用什么类型?
    decimal 定点型 
    #学员的姓名允许为空吗?
    不允许
    #家庭地址可以为空吗?
    最好不可以
    #电子邮箱可以为空吗?
    对于目前来说有一点分量,随意,最好不用
    #考试成绩可以为空?
    不允许 default 0 
    #在主键列输入的数值,能为空吗?
    肯定不能 null当作占位符
    #一个表可以有多个主键吗?
    不可以
    #在一个学校的数据库中,如果这个学校允许学员重名, 班级不允许有重名的,那么把班级和学生的姓名作为组合主键可以吗?
    primary key(id,name)
    可以,但是不合理
    #标识列允许使用字符串类型吗?
    可以,但不允许
    #表中没有合适的列(字段)作为主键怎么办?
    自己添加一个自增长的字段作为主键

[70]: /images/20220521/57c2df7426474a17b1c89f2a9391a3d7.png
[70 1]: /images/20220521/9c84a5c0df9047ed97c69dfb7c0163a2.png
[70 2]: /images/20220521/849c5fa603e741298ca0a3e9849267c5.png