php复习 第十三天 mysql基础4

<?php # 三大数据类型--数值类型、日期类型、字符串类型。//整数类型有,tinyint一般存储年龄，smallint，mediumint,int,bigint.//创建数据表t1mysql> create table t1(id smallint(2) zerofill,age int);Query OK, 0 rows affected (0.31 sec)//插入测试数据mysql> insert into t1 values(1,16),(2,55),(3,22);Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0//查询插入数据mysql> select * from t1;+------+------+| id   | age  |+------+------+|   01 |   16 ||   02 |   55 ||   03 |   22 |+------+------+3 rows in set (0.00 sec)//此时查看数据结构: id指定为 smallint(2)属性为zerofill 表示当id存储的数值不足两位数时，前面自动补0.//unsigned 无符号，当指定了该属性后其范围会变大一倍。//auto_increment 自增长 要设置该属性必须指定为primary key 主键或者其他类型的索引。mysql> desc t1;+-------+-------------------------------+------+-----+---------+-------+| Field | Type                          | Null | Key | Default | Extra |+-------+-------------------------------+------+-----+---------+-------+| id    | smallint(2) unsigned zerofill | YES  |     | NULL    |       || age   | int(11)                       | YES  |     | NULL    |       |+-------+-------------------------------+------+-----+---------+-------+2 rows in set (0.01 sec)//对于float和double类型，如果用来储存关于货币信息，不建议使用，因为运算或存储时会影响精度。更多是用decimal(m,n)//日期类型 常用unix时间戳或datetime，其中now()函数是mysql提供的查看当前时间的函数。//字符串类型 char、varchar、tinytext、text、mediumtext、longtext//注意：varchar和char类型十分相似，但是varchar类型存储的空间是可变的，但是char是固定的。//char类型指定长度时，和字符编码没有关系，但是varchar有关系。例如utf8编码下一个中文字符占3个字节，但是在gbk下占2个字节。//枚举类型 enum 指定字符 create table test(sex enum('man','woman'));//枚举类型表示其字段中保存的值必须为预定义的字符串。#字符编码//查看可用的字符集。show character set;+----------+-----------------------------+---------------------+--------+| Charset  | Description                 | Default collation   | Maxlen |+----------+-----------------------------+---------------------+--------+| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 || dec8     | DEC West European           | dec8_swedish_ci     |      1 || cp850    | DOS West European           | cp850_general_ci    |      1 || hp8      | HP West European            | hp8_english_ci      |      1 || koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 || latin1   | cp1252 West European        | latin1_swedish_ci   |      1 || latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 || swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 || ascii    | US ASCII                    | ascii_general_ci    |      1 || ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 || sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 || hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 || tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 || euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 || koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 || gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 || greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 || cp1250   | Windows Central European    | cp1250_general_ci   |      1 || gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 || latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 || armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 || utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 || ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 || cp866    | DOS Russian                 | cp866_general_ci    |      1 || keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 || macce    | Mac Central European        | macce_general_ci    |      1 || macroman | Mac West European           | macroman_general_ci |      1 || cp852    | DOS Central European        | cp852_general_ci    |      1 || latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 || utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 || cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 || utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 || cp1256   | Windows Arabic              | cp1256_general_ci   |      1 || cp1257   | Windows Baltic              | cp1257_general_ci   |      1 || utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 || binary   | Binary pseudo charset       | binary              |      1 || geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 || cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 || eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |+----------+-----------------------------+---------------------+--------+39 rows in set (0.00 sec)//查看字符集的校对规则mysql> show collation like 'utf8%';+--------------------------+---------+-----+---------+----------+---------+| Collation                | Charset | Id  | Default | Compiled | Sortlen |+--------------------------+---------+-----+---------+----------+---------+| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 || utf8_bin                 | utf8    |  83 |         | Yes      |       1 || utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 || utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 || utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 || utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 || utf8_slovenian_ci        | utf8    | 196 |         | Yes      |       8 || utf8_polish_ci           | utf8    | 197 |         | Yes      |       8 || utf8_estonian_ci         | utf8    | 198 |         | Yes      |       8 || utf8_spanish_ci          | utf8    | 199 |         | Yes      |       8 || utf8_swedish_ci          | utf8    | 200 |         | Yes      |       8 || utf8_turkish_ci          | utf8    | 201 |         | Yes      |       8 || utf8_czech_ci            | utf8    | 202 |         | Yes      |       8 || utf8_danish_ci           | utf8    | 203 |         | Yes      |       8 || utf8_lithuanian_ci       | utf8    | 204 |         | Yes      |       8 || utf8_slovak_ci           | utf8    | 205 |         | Yes      |       8 || utf8_spanish2_ci         | utf8    | 206 |         | Yes      |       8 || utf8_roman_ci            | utf8    | 207 |         | Yes      |       8 || utf8_persian_ci          | utf8    | 208 |         | Yes      |       8 || utf8_esperanto_ci        | utf8    | 209 |         | Yes      |       8 || utf8_hungarian_ci        | utf8    | 210 |         | Yes      |       8 || utf8_sinhala_ci          | utf8    | 211 |         | Yes      |       8 || utf8_general_mysql500_ci | utf8    | 223 |         | Yes      |       1 || utf8mb4_general_ci       | utf8mb4 |  45 | Yes     | Yes      |       1 || utf8mb4_bin              | utf8mb4 |  46 |         | Yes      |       1 || utf8mb4_unicode_ci       | utf8mb4 | 224 |         | Yes      |       8 || utf8mb4_icelandic_ci     | utf8mb4 | 225 |         | Yes      |       8 || utf8mb4_latvian_ci       | utf8mb4 | 226 |         | Yes      |       8 || utf8mb4_romanian_ci      | utf8mb4 | 227 |         | Yes      |       8 || utf8mb4_slovenian_ci     | utf8mb4 | 228 |         | Yes      |       8 || utf8mb4_polish_ci        | utf8mb4 | 229 |         | Yes      |       8 || utf8mb4_estonian_ci      | utf8mb4 | 230 |         | Yes      |       8 || utf8mb4_spanish_ci       | utf8mb4 | 231 |         | Yes      |       8 || utf8mb4_swedish_ci       | utf8mb4 | 232 |         | Yes      |       8 || utf8mb4_turkish_ci       | utf8mb4 | 233 |         | Yes      |       8 || utf8mb4_czech_ci         | utf8mb4 | 234 |         | Yes      |       8 || utf8mb4_danish_ci        | utf8mb4 | 235 |         | Yes      |       8 || utf8mb4_lithuanian_ci    | utf8mb4 | 236 |         | Yes      |       8 || utf8mb4_slovak_ci        | utf8mb4 | 237 |         | Yes      |       8 || utf8mb4_spanish2_ci      | utf8mb4 | 238 |         | Yes      |       8 || utf8mb4_roman_ci         | utf8mb4 | 239 |         | Yes      |       8 || utf8mb4_persian_ci       | utf8mb4 | 240 |         | Yes      |       8 || utf8mb4_esperanto_ci     | utf8mb4 | 241 |         | Yes      |       8 || utf8mb4_hungarian_ci     | utf8mb4 | 242 |         | Yes      |       8 || utf8mb4_sinhala_ci       | utf8mb4 | 243 |         | Yes      |       8 |+--------------------------+---------+-----+---------+----------+---------+45 rows in set (0.00 sec)//查看当前数据库的字符集mysql> show variables like 'character_set_database';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| character_set_database | gbk   |+------------------------+-------+1 row in set (0.00 sec)//查看当前数据库的校对规则mysql> show variables like 'collation_database';+--------------------+----------------+| Variable_name      | Value          |+--------------------+----------------+| collation_database | gbk_chinese_ci |+--------------------+----------------+1 row in set (0.00 sec)//修改cmd字符集修改CMD命令行字符集：chcp 65001   #换成utf-8代码页（设置为utf-8之后最好手动修改显示字体 ）chcp 936     #换成默认的gbk?>