【MYSQL】MYSQL学习的一大重点：MYSQL库的操作
🎬 个人主页：艾莉丝努力练剑
❄专栏传送门：《C语言》《数据结构与算法》《C/C++干货分享&学习过程记录》
《Linux操作系统编程详解》《笔试/面试常见算法：从基础到进阶》《Python干货分享》
⭐️为天地立心，为生民立命，为往圣继绝学，为万世开太平
🎬 艾莉丝的简介：
文章目录
0 ~> 实际场景：创建和删除数据库
0.1 创建方式1
mysql> show databases; +--------------------+ | Database | +--------------------+ | helloworld || information_schema || mydb || mysql || performance_schema || sys | +--------------------+ 6 rows inset(0.00 sec) mysql> create database database1; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | database1 || helloworld || information_schema || mydb || mysql || performance_schema || sys | +--------------------+ 7 rows inset(0.00 sec) mysql> drop database database1; Query OK, 0 rows affected (0.01 sec) mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | helloworld || information_schema || mydb || mysql || performance_schema || sys | +--------------------+ 6 rows inset(0.00 sec)0.2 创建方式2
mysql> create database if not exists database1; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | database1 || helloworld || information_schema || mydb || mysql || performance_schema || sys | +--------------------+ 7 rows inset(0.00 sec) mysql> drop database database1; Query OK, 0 rows affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | helloworld || information_schema || mydb || mysql || performance_schema || sys | +--------------------+ 6 rows inset(0.00 sec)0.3 创建方式3
下面这种创建方式叫做：既指明了数据库的编码又指明了校验码——
mysql> create database d4 charset=utf8 collate utf8_general_ci; Query OK, 1 row affected, 2 warnings (0.01 sec)1 ~> 数据库的编码集
1.1 目前整个数据库支持的字符集
默认支持的字符集是UTF-8，我们可以验证一下——
mysql> show variables like 'character_set_database'; +------------------------+---------+ | Variable_name | Value | +------------------------+---------+ | character_set_database | utf8mb4 | +------------------------+---------+ 1 row inset(0.01 sec)1.2 目前整个数据库支持的字符集
它还有个校验集——
mysql> show variables like 'collation_database'; +--------------------+--------------------+ | Variable_name | Value | +--------------------+--------------------+ | collation_database | utf8mb4_0900_ai_ci | +--------------------+--------------------+ 1 row inset(0.00 sec)1.3 UTF-8需要设置配置文件
我们发现上面的编码规则并不是UTF-8，为什么呢？
其实是因为我们没有改对应的配置文件，如果要显示为UTF-8需要我们修改配置文件。
1.4 MySQL 中与字符集排序规则（collation）相关的系统变量
我们可以查看MySQL 中与字符集排序规则（collation）相关的系统变量：
mysql> show variables like 'collation_%'; +----------------------+--------------------+ | Variable_name | Value | +----------------------+--------------------+ | collation_connection | utf8mb4_0900_ai_ci || collation_database | utf8mb4_0900_ai_ci || collation_server | utf8mb4_0900_ai_ci | +----------------------+--------------------+ 3 rows inset(0.00 sec)如下图所示：
这里我们得到的三行分别代表三个不同级别的排序规则设置：
1.5 字符集和校验集是具有匹配关系的
字符集和校验集是具有匹配关系的，从下面两张我们可以查到的图就可以很明显的看出——
- 我们熟悉的字符集主要是
gbk和utf-8，我们通过这两个字符集就可以理解上面那句话：即【字符集和校验集是具有匹配关系的】。
1.5.1 整个数据库支持的字符集
我们可以查看目前整个数据库支持的字符集：
mysql> show charset; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci |1|| ascii | US ASCII | ascii_general_ci |1|| big5 | Big5 Traditional Chinese | big5_chinese_ci |2|| binary | Binary pseudo charset | binary |1|| cp1250 | Windows Central European | cp1250_general_ci |1|| cp1251 | Windows Cyrillic | cp1251_general_ci |1|| cp1256 | Windows Arabic | cp1256_general_ci |1|| cp1257 | Windows Baltic | cp1257_general_ci |1|| cp850 | DOS West European | cp850_general_ci |1|| cp852 | DOS Central European | cp852_general_ci |1|| cp866 | DOS Russian | cp866_general_ci |1|| cp932 | SJIS for Windows Japanese | cp932_japanese_ci |2|| dec8 | DEC West European | dec8_swedish_ci |1|| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci |3|| euckr | EUC-KR Korean | euckr_korean_ci |2|| gb18030 | China National Standard GB18030 | gb18030_chinese_ci |4|| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci |2|| gbk | GBK Simplified Chinese | gbk_chinese_ci |2|| geostd8 | GEOSTD8 Georgian | geostd8_general_ci |1|| greek | ISO 8859-7 Greek | greek_general_ci |1|| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci |1|| hp8 | HP West European | hp8_english_ci |1|| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci |1|| koi8r | KOI8-R Relcom Russian | koi8r_general_ci |1|| koi8u | KOI8-U Ukrainian | koi8u_general_ci |1|| latin1 | cp1252 West European | latin1_swedish_ci |1|| latin2 | ISO 8859-2 Central European | latin2_general_ci |1|| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci |1|| latin7 | ISO 8859-13 Baltic | latin7_general_ci |1|| macce | Mac Central European | macce_general_ci |1|| macroman | Mac West European | macroman_general_ci |1|| sjis | Shift-JIS Japanese | sjis_japanese_ci |2|| swe7 | 7bit Swedish | swe7_swedish_ci |1|| tis620 | TIS620 Thai | tis620_thai_ci |1|| ucs2 | UCS-2 Unicode | ucs2_general_ci |2|| ujis | EUC-JP Japanese | ujis_japanese_ci |3|| utf16 | UTF-16 Unicode | utf16_general_ci |4|| utf16le | UTF-16LE Unicode | utf16le_general_ci |4|| utf32 | UTF-32 Unicode | utf32_general_ci |4|| utf8mb3 | UTF-8 Unicode | utf8mb3_general_ci |3|| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci |4| +----------+---------------------------------+---------------------+--------+ 41 rows inset(0.00 sec)1.5.2 整个数据库支持的校验集
我们可以查看目前整个数据库支持的校验集：
mysql> show collation; +-----------------------------+----------+-----+---------+----------+---------+---------------+ | Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute | +-----------------------------+----------+-----+---------+----------+---------+---------------+ | armscii8_bin | armscii8 |64|| Yes |1| PAD SPACE || armscii8_general_ci | armscii8 |32| Yes | Yes |1| PAD SPACE || ascii_bin | ascii |65|| Yes |1| PAD SPACE || ascii_general_ci | ascii |11| Yes | Yes |1| PAD SPACE || big5_bin | big5 |84|| Yes |1| PAD SPACE || big5_chinese_ci | big5 |1| Yes | Yes |1| PAD SPACE || binary | binary |63| Yes | Yes |1| NO PAD || cp1250_bin | cp1250 |66|| Yes |1| PAD SPACE || cp1250_croatian_ci | cp1250 |44|| Yes |1| PAD SPACE || cp1250_czech_cs | cp1250 |34|| Yes |2| PAD SPACE || cp1250_general_ci | cp1250 |26| Yes | Yes |1| PAD SPACE || cp1250_polish_ci | cp1250 |99|| Yes |1| PAD SPACE || cp1251_bin | cp1251 |50|| Yes |1| PAD SPACE || cp1251_bulgarian_ci | cp1251 |14|| Yes |1| PAD SPACE || cp1251_general_ci | cp1251 |51| Yes | Yes |1| PAD SPACE || cp1251_general_cs | cp1251 |52|| Yes |1| PAD SPACE || cp1251_ukrainian_ci | cp1251 |23|| Yes |1| PAD SPACE || cp1256_bin | cp1256 |67|| Yes |1| PAD SPACE || cp1256_general_ci | cp1256 |57| Yes | Yes |1| PAD SPACE || cp1257_bin | cp1257 |58|| Yes |1| PAD SPACE || cp1257_general_ci | cp1257 |59| Yes | Yes |1| PAD SPACE || cp1257_lithuanian_ci | cp1257 |29|| Yes |1| PAD SPACE || cp850_bin | cp850 |80|| Yes |1| PAD SPACE || cp850_general_ci | cp850 |4| Yes | Yes |1| PAD SPACE || cp852_bin | cp852 |81|| Yes |1| PAD SPACE || cp852_general_ci | cp852 |40| Yes | Yes |1| PAD SPACE || cp866_bin | cp866 |68|| Yes |1| PAD SPACE || cp866_general_ci | cp866 |36| Yes | Yes |1| PAD SPACE || cp932_bin | cp932 |96|| Yes |1| PAD SPACE || cp932_japanese_ci | cp932 |95| Yes | Yes |1| PAD SPACE || dec8_bin | dec8 |69|| Yes |1| PAD SPACE || dec8_swedish_ci | dec8 |3| Yes | Yes |1| PAD SPACE || eucjpms_bin | eucjpms |98|| Yes |1| PAD SPACE || eucjpms_japanese_ci | eucjpms |97| Yes | Yes |1| PAD SPACE || euckr_bin | euckr |85|| Yes |1| PAD SPACE || euckr_korean_ci | euckr |19| Yes | Yes |1| PAD SPACE || gb18030_bin | gb18030 |249|| Yes |1| PAD SPACE || gb18030_chinese_ci | gb18030 |248| Yes | Yes |2| PAD SPACE || gb18030_unicode_520_ci | gb18030 |250|| Yes |8| PAD SPACE || gb2312_bin | gb2312 |86|| Yes |1| PAD SPACE || gb2312_chinese_ci | gb2312 |24| Yes | Yes |1| PAD SPACE || gbk_bin | gbk |87|| Yes |1| PAD SPACE || gbk_chinese_ci | gbk |28| Yes | Yes |1| PAD SPACE || geostd8_bin | geostd8 |93|| Yes |1| PAD SPACE || geostd8_general_ci | geostd8 |92| Yes | Yes |1| PAD SPACE || greek_bin | greek |70|| Yes |1| PAD SPACE || greek_general_ci | greek |25| Yes | Yes |1| PAD SPACE || hebrew_bin | hebrew |71|| Yes |1| PAD SPACE || hebrew_general_ci | hebrew |16| Yes | Yes |1| PAD SPACE || hp8_bin | hp8 |72|| Yes |1| PAD SPACE || hp8_english_ci | hp8 |6| Yes | Yes |1| PAD SPACE || keybcs2_bin | keybcs2 |73|| Yes |1| PAD SPACE || keybcs2_general_ci | keybcs2 |37| Yes | Yes |1| PAD SPACE || koi8r_bin | koi8r |74|| Yes |1| PAD SPACE || koi8r_general_ci | koi8r |7| Yes | Yes |1| PAD SPACE || koi8u_bin | koi8u |75|| Yes |1| PAD SPACE || koi8u_general_ci | koi8u |22| Yes | Yes |1| PAD SPACE || latin1_bin | latin1 |47|| Yes |1| PAD SPACE || latin1_danish_ci | latin1 |15|| Yes |1| PAD SPACE || latin1_general_ci | latin1 |48|| Yes |1| PAD SPACE || latin1_general_cs | latin1 |49|| Yes |1| PAD SPACE || latin1_german1_ci | latin1 |5|| Yes |1| PAD SPACE || latin1_german2_ci | latin1 |31|| Yes |2| PAD SPACE || latin1_spanish_ci | latin1 |94|| Yes |1| PAD SPACE || latin1_swedish_ci | latin1 |8| Yes | Yes |1| PAD SPACE || latin2_bin | latin2 |77|| Yes |1| PAD SPACE || latin2_croatian_ci | latin2 |27|| Yes |1| PAD SPACE || latin2_czech_cs | latin2 |2|| Yes |4| PAD SPACE || latin2_general_ci | latin2 |9| Yes | Yes |1| PAD SPACE || latin2_hungarian_ci | latin2 |21|| Yes |1| PAD SPACE || latin5_bin | latin5 |78|| Yes |1| PAD SPACE || latin5_turkish_ci | latin5 |30| Yes | Yes |1| PAD SPACE || latin7_bin | latin7 |79|| Yes |1| PAD SPACE || latin7_estonian_cs | latin7 |20|| Yes |1| PAD SPACE || latin7_general_ci | latin7 |41| Yes | Yes |1| PAD SPACE || latin7_general_cs | latin7 |42|| Yes |1| PAD SPACE || macce_bin | macce |43|| Yes |1| PAD SPACE || macce_general_ci | macce |38| Yes | Yes |1| PAD SPACE || macroman_bin | macroman |53|| Yes |1| PAD SPACE || macroman_general_ci | macroman |39| Yes | Yes |1| PAD SPACE || sjis_bin | sjis |88|| Yes |1| PAD SPACE || sjis_japanese_ci | sjis |13| Yes | Yes |1| PAD SPACE || swe7_bin | swe7 |82|| Yes |1| PAD SPACE || swe7_swedish_ci | swe7 |10| Yes | Yes |1| PAD SPACE || tis620_bin | tis620 |89|| Yes |1| PAD SPACE || tis620_thai_ci | tis620 |18| Yes | Yes |4| PAD SPACE || ucs2_bin | ucs2 |90|| Yes |1| PAD SPACE || ucs2_croatian_ci | ucs2 |149|| Yes |8| PAD SPACE || ucs2_czech_ci | ucs2 |138|| Yes |8| PAD SPACE || ucs2_danish_ci | ucs2 |139|| Yes |8| PAD SPACE || ucs2_esperanto_ci | ucs2 |145|| Yes |8| PAD SPACE || ucs2_estonian_ci | ucs2 |134|| Yes |8| PAD SPACE || ucs2_general_ci | ucs2 |35| Yes | Yes |1| PAD SPACE || ucs2_general_mysql500_ci | ucs2 |159|| Yes |1| PAD SPACE || ucs2_german2_ci | ucs2 |148|| Yes |8| PAD SPACE || ucs2_hungarian_ci | ucs2 |146|| Yes |8| PAD SPACE || ucs2_icelandic_ci | ucs2 |129|| Yes |8| PAD SPACE || ucs2_latvian_ci | ucs2 |130|| Yes |8| PAD SPACE || ucs2_lithuanian_ci | ucs2 |140|| Yes |8| PAD SPACE || ucs2_persian_ci | ucs2 |144|| Yes |8| PAD SPACE || ucs2_polish_ci | ucs2 |133|| Yes |8| PAD SPACE || ucs2_romanian_ci | ucs2 |131|| Yes |8| PAD SPACE || ucs2_roman_ci | ucs2 |143|| Yes |8| PAD SPACE || ucs2_sinhala_ci | ucs2 |147|| Yes |8| PAD SPACE || ucs2_slovak_ci | ucs2 |141|| Yes |8| PAD SPACE || ucs2_slovenian_ci | ucs2 |132|| Yes |8| PAD SPACE || ucs2_spanish2_ci | ucs2 |142|| Yes |8| PAD SPACE || ucs2_spanish_ci | ucs2 |135|| Yes |8| PAD SPACE || ucs2_swedish_ci | ucs2 |136|| Yes |8| PAD SPACE || ucs2_turkish_ci | ucs2 |137|| Yes |8| PAD SPACE || ucs2_unicode_520_ci | ucs2 |150|| Yes |8| PAD SPACE || ucs2_unicode_ci | ucs2 |128|| Yes |8| PAD SPACE || ucs2_vietnamese_ci | ucs2 |151|| Yes |8| PAD SPACE || ujis_bin | ujis |91|| Yes |1| PAD SPACE || ujis_japanese_ci | ujis |12| Yes | Yes |1| PAD SPACE || utf16le_bin | utf16le |62|| Yes |1| PAD SPACE || utf16le_general_ci | utf16le |56| Yes | Yes |1| PAD SPACE || utf16_bin | utf16 |55|| Yes |1| PAD SPACE || utf16_croatian_ci | utf16 |122|| Yes |8| PAD SPACE || utf16_czech_ci | utf16 |111|| Yes |8| PAD SPACE || utf16_danish_ci | utf16 |112|| Yes |8| PAD SPACE || utf16_esperanto_ci | utf16 |118|| Yes |8| PAD SPACE || utf16_estonian_ci | utf16 |107|| Yes |8| PAD SPACE || utf16_general_ci | utf16 |54| Yes | Yes |1| PAD SPACE || utf16_german2_ci | utf16 |121|| Yes |8| PAD SPACE || utf16_hungarian_ci | utf16 |119|| Yes |8| PAD SPACE || utf16_icelandic_ci | utf16 |102|| Yes |8| PAD SPACE || utf16_latvian_ci | utf16 |103|| Yes |8| PAD SPACE || utf16_lithuanian_ci | utf16 |113|| Yes |8| PAD SPACE || utf16_persian_ci | utf16 |117|| Yes |8| PAD SPACE || utf16_polish_ci | utf16 |106|| Yes |8| PAD SPACE || utf16_romanian_ci | utf16 |104|| Yes |8| PAD SPACE || utf16_roman_ci | utf16 |116|| Yes |8| PAD SPACE || utf16_sinhala_ci | utf16 |120|| Yes |8| PAD SPACE || utf16_slovak_ci | utf16 |114|| Yes |8| PAD SPACE || utf16_slovenian_ci | utf16 |105|| Yes |8| PAD SPACE || utf16_spanish2_ci | utf16 |115|| Yes |8| PAD SPACE || utf16_spanish_ci | utf16 |108|| Yes |8| PAD SPACE || utf16_swedish_ci | utf16 |109|| Yes |8| PAD SPACE || utf16_turkish_ci | utf16 |110|| Yes |8| PAD SPACE || utf16_unicode_520_ci | utf16 |123|| Yes |8| PAD SPACE || utf16_unicode_ci | utf16 |101|| Yes |8| PAD SPACE || utf16_vietnamese_ci | utf16 |124|| Yes |8| PAD SPACE || utf32_bin | utf32 |61|| Yes |1| PAD SPACE || utf32_croatian_ci | utf32 |181|| Yes |8| PAD SPACE || utf32_czech_ci | utf32 |170|| Yes |8| PAD SPACE || utf32_danish_ci | utf32 |171|| Yes |8| PAD SPACE || utf32_esperanto_ci | utf32 |177|| Yes |8| PAD SPACE || utf32_estonian_ci | utf32 |166|| Yes |8| PAD SPACE || utf32_general_ci | utf32 |60| Yes | Yes |1| PAD SPACE || utf32_german2_ci | utf32 |180|| Yes |8| PAD SPACE || utf32_hungarian_ci | utf32 |178|| Yes |8| PAD SPACE || utf32_icelandic_ci | utf32 |161|| Yes |8| PAD SPACE || utf32_latvian_ci | utf32 |162|| Yes |8| PAD SPACE || utf32_lithuanian_ci | utf32 |172|| Yes |8| PAD SPACE || utf32_persian_ci | utf32 |176|| Yes |8| PAD SPACE || utf32_polish_ci | utf32 |165|| Yes |8| PAD SPACE || utf32_romanian_ci | utf32 |163|| Yes |8| PAD SPACE || utf32_roman_ci | utf32 |175|| Yes |8| PAD SPACE || utf32_sinhala_ci | utf32 |179|| Yes |8| PAD SPACE || utf32_slovak_ci | utf32 |173|| Yes |8| PAD SPACE || utf32_slovenian_ci | utf32 |164|| Yes |8| PAD SPACE || utf32_spanish2_ci | utf32 |174|| Yes |8| PAD SPACE || utf32_spanish_ci | utf32 |167|| Yes |8| PAD SPACE || utf32_swedish_ci | utf32 |168|| Yes |8| PAD SPACE || utf32_turkish_ci | utf32 |169|| Yes |8| PAD SPACE || utf32_unicode_520_ci | utf32 |182|| Yes |8| PAD SPACE || utf32_unicode_ci | utf32 |160|| Yes |8| PAD SPACE || utf32_vietnamese_ci | utf32 |183|| Yes |8| PAD SPACE || utf8mb3_bin | utf8mb3 |83|| Yes |1| PAD SPACE || utf8mb3_croatian_ci | utf8mb3 |213|| Yes |8| PAD SPACE || utf8mb3_czech_ci | utf8mb3 |202|| Yes |8| PAD SPACE || utf8mb3_danish_ci | utf8mb3 |203|| Yes |8| PAD SPACE || utf8mb3_esperanto_ci | utf8mb3 |209|| Yes |8| PAD SPACE || utf8mb3_estonian_ci | utf8mb3 |198|| Yes |8| PAD SPACE || utf8mb3_general_ci | utf8mb3 |33| Yes | Yes |1| PAD SPACE || utf8mb3_general_mysql500_ci | utf8mb3 |223|| Yes |1| PAD SPACE || utf8mb3_german2_ci | utf8mb3 |212|| Yes |8| PAD SPACE || utf8mb3_hungarian_ci | utf8mb3 |210|| Yes |8| PAD SPACE || utf8mb3_icelandic_ci | utf8mb3 |193|| Yes |8| PAD SPACE || utf8mb3_latvian_ci | utf8mb3 |194|| Yes |8| PAD SPACE || utf8mb3_lithuanian_ci | utf8mb3 |204|| Yes |8| PAD SPACE || utf8mb3_persian_ci | utf8mb3 |208|| Yes |8| PAD SPACE || utf8mb3_polish_ci | utf8mb3 |197|| Yes |8| PAD SPACE || utf8mb3_romanian_ci | utf8mb3 |195|| Yes |8| PAD SPACE || utf8mb3_roman_ci | utf8mb3 |207|| Yes |8| PAD SPACE || utf8mb3_sinhala_ci | utf8mb3 |211|| Yes |8| PAD SPACE || utf8mb3_slovak_ci | utf8mb3 |205|| Yes |8| PAD SPACE || utf8mb3_slovenian_ci | utf8mb3 |196|| Yes |8| PAD SPACE || utf8mb3_spanish2_ci | utf8mb3 |206|| Yes |8| PAD SPACE || utf8mb3_spanish_ci | utf8mb3 |199|| Yes |8| PAD SPACE || utf8mb3_swedish_ci | utf8mb3 |200|| Yes |8| PAD SPACE || utf8mb3_tolower_ci | utf8mb3 |76|| Yes |1| PAD SPACE || utf8mb3_turkish_ci | utf8mb3 |201|| Yes |8| PAD SPACE || utf8mb3_unicode_520_ci | utf8mb3 |214|| Yes |8| PAD SPACE || utf8mb3_unicode_ci | utf8mb3 |192|| Yes |8| PAD SPACE || utf8mb3_vietnamese_ci | utf8mb3 |215|| Yes |8| PAD SPACE || utf8mb4_0900_ai_ci | utf8mb4 |255| Yes | Yes |0| NO PAD || utf8mb4_0900_as_ci | utf8mb4 |305|| Yes |0| NO PAD || utf8mb4_0900_as_cs | utf8mb4 |278|| Yes |0| NO PAD || utf8mb4_0900_bin | utf8mb4 |309|| Yes |1| NO PAD || utf8mb4_bg_0900_ai_ci | utf8mb4 |318|| Yes |0| NO PAD || utf8mb4_bg_0900_as_cs | utf8mb4 |319|| Yes |0| NO PAD || utf8mb4_bin | utf8mb4 |46|| Yes |1| PAD SPACE || utf8mb4_bs_0900_ai_ci | utf8mb4 |316|| Yes |0| NO PAD || utf8mb4_bs_0900_as_cs | utf8mb4 |317|| Yes |0| NO PAD || utf8mb4_croatian_ci | utf8mb4 |245|| Yes |8| PAD SPACE || utf8mb4_cs_0900_ai_ci | utf8mb4 |266|| Yes |0| NO PAD || utf8mb4_cs_0900_as_cs | utf8mb4 |289|| Yes |0| NO PAD || utf8mb4_czech_ci | utf8mb4 |234|| Yes |8| PAD SPACE || utf8mb4_danish_ci | utf8mb4 |235|| Yes |8| PAD SPACE || utf8mb4_da_0900_ai_ci | utf8mb4 |267|| Yes |0| NO PAD || utf8mb4_da_0900_as_cs | utf8mb4 |290|| Yes |0| NO PAD || utf8mb4_de_pb_0900_ai_ci | utf8mb4 |256|| Yes |0| NO PAD || utf8mb4_de_pb_0900_as_cs | utf8mb4 |279|| Yes |0| NO PAD || utf8mb4_eo_0900_ai_ci | utf8mb4 |273|| Yes |0| NO PAD || utf8mb4_eo_0900_as_cs | utf8mb4 |296|| Yes |0| NO PAD || utf8mb4_esperanto_ci | utf8mb4 |241|| Yes |8| PAD SPACE || utf8mb4_estonian_ci | utf8mb4 |230|| Yes |8| PAD SPACE || utf8mb4_es_0900_ai_ci | utf8mb4 |263|| Yes |0| NO PAD || utf8mb4_es_0900_as_cs | utf8mb4 |286|| Yes |0| NO PAD || utf8mb4_es_trad_0900_ai_ci | utf8mb4 |270|| Yes |0| NO PAD || utf8mb4_es_trad_0900_as_cs | utf8mb4 |293|| Yes |0| NO PAD || utf8mb4_et_0900_ai_ci | utf8mb4 |262|| Yes |0| NO PAD || utf8mb4_et_0900_as_cs | utf8mb4 |285|| Yes |0| NO PAD || utf8mb4_general_ci | utf8mb4 |45|| Yes |1| PAD SPACE || utf8mb4_german2_ci | utf8mb4 |244|| Yes |8| PAD SPACE || utf8mb4_gl_0900_ai_ci | utf8mb4 |320|| Yes |0| NO PAD || utf8mb4_gl_0900_as_cs | utf8mb4 |321|| Yes |0| NO PAD || utf8mb4_hr_0900_ai_ci | utf8mb4 |275|| Yes |0| NO PAD || utf8mb4_hr_0900_as_cs | utf8mb4 |298|| Yes |0| NO PAD || utf8mb4_hungarian_ci | utf8mb4 |242|| Yes |8| PAD SPACE || utf8mb4_hu_0900_ai_ci | utf8mb4 |274|| Yes |0| NO PAD || utf8mb4_hu_0900_as_cs | utf8mb4 |297|| Yes |0| NO PAD || utf8mb4_icelandic_ci | utf8mb4 |225|| Yes |8| PAD SPACE || utf8mb4_is_0900_ai_ci | utf8mb4 |257|| Yes |0| NO PAD || utf8mb4_is_0900_as_cs | utf8mb4 |280|| Yes |0| NO PAD || utf8mb4_ja_0900_as_cs | utf8mb4 |303|| Yes |0| NO PAD || utf8mb4_ja_0900_as_cs_ks | utf8mb4 |304|| Yes |24| NO PAD || utf8mb4_latvian_ci | utf8mb4 |226|| Yes |8| PAD SPACE || utf8mb4_la_0900_ai_ci | utf8mb4 |271|| Yes |0| NO PAD || utf8mb4_la_0900_as_cs | utf8mb4 |294|| Yes |0| NO PAD || utf8mb4_lithuanian_ci | utf8mb4 |236|| Yes |8| PAD SPACE || utf8mb4_lt_0900_ai_ci | utf8mb4 |268|| Yes |0| NO PAD || utf8mb4_lt_0900_as_cs | utf8mb4 |291|| Yes |0| NO PAD || utf8mb4_lv_0900_ai_ci | utf8mb4 |258|| Yes |0| NO PAD || utf8mb4_lv_0900_as_cs | utf8mb4 |281|| Yes |0| NO PAD || utf8mb4_mn_cyrl_0900_ai_ci | utf8mb4 |322|| Yes |0| NO PAD || utf8mb4_mn_cyrl_0900_as_cs | utf8mb4 |323|| Yes |0| NO PAD || utf8mb4_nb_0900_ai_ci | utf8mb4 |310|| Yes |0| NO PAD || utf8mb4_nb_0900_as_cs | utf8mb4 |311|| Yes |0| NO PAD || utf8mb4_nn_0900_ai_ci | utf8mb4 |312|| Yes |0| NO PAD || utf8mb4_nn_0900_as_cs | utf8mb4 |313|| Yes |0| NO PAD || utf8mb4_persian_ci | utf8mb4 |240|| Yes |8| PAD SPACE || utf8mb4_pl_0900_ai_ci | utf8mb4 |261|| Yes |0| NO PAD || utf8mb4_pl_0900_as_cs | utf8mb4 |284|| Yes |0| NO PAD || utf8mb4_polish_ci | utf8mb4 |229|| Yes |8| PAD SPACE || utf8mb4_romanian_ci | utf8mb4 |227|| Yes |8| PAD SPACE || utf8mb4_roman_ci | utf8mb4 |239|| Yes |8| PAD SPACE || utf8mb4_ro_0900_ai_ci | utf8mb4 |259|| Yes |0| NO PAD || utf8mb4_ro_0900_as_cs | utf8mb4 |282|| Yes |0| NO PAD || utf8mb4_ru_0900_ai_ci | utf8mb4 |306|| Yes |0| NO PAD || utf8mb4_ru_0900_as_cs | utf8mb4 |307|| Yes |0| NO PAD || utf8mb4_sinhala_ci | utf8mb4 |243|| Yes |8| PAD SPACE || utf8mb4_sk_0900_ai_ci | utf8mb4 |269|| Yes |0| NO PAD || utf8mb4_sk_0900_as_cs | utf8mb4 |292|| Yes |0| NO PAD || utf8mb4_slovak_ci | utf8mb4 |237|| Yes |8| PAD SPACE || utf8mb4_slovenian_ci | utf8mb4 |228|| Yes |8| PAD SPACE || utf8mb4_sl_0900_ai_ci | utf8mb4 |260|| Yes |0| NO PAD || utf8mb4_sl_0900_as_cs | utf8mb4 |283|| Yes |0| NO PAD || utf8mb4_spanish2_ci | utf8mb4 |238|| Yes |8| PAD SPACE || utf8mb4_spanish_ci | utf8mb4 |231|| Yes |8| PAD SPACE || utf8mb4_sr_latn_0900_ai_ci | utf8mb4 |314|| Yes |0| NO PAD || utf8mb4_sr_latn_0900_as_cs | utf8mb4 |315|| Yes |0| NO PAD || utf8mb4_sv_0900_ai_ci | utf8mb4 |264|| Yes |0| NO PAD || utf8mb4_sv_0900_as_cs | utf8mb4 |287|| Yes |0| NO PAD || utf8mb4_swedish_ci | utf8mb4 |232|| Yes |8| PAD SPACE || utf8mb4_tr_0900_ai_ci | utf8mb4 |265|| Yes |0| NO PAD || utf8mb4_tr_0900_as_cs | utf8mb4 |288|| Yes |0| NO PAD || utf8mb4_turkish_ci | utf8mb4 |233|| Yes |8| PAD SPACE || utf8mb4_unicode_520_ci | utf8mb4 |246|| Yes |8| PAD SPACE || utf8mb4_unicode_ci | utf8mb4 |224|| Yes |8| PAD SPACE || utf8mb4_vietnamese_ci | utf8mb4 |247|| Yes |8| PAD SPACE || utf8mb4_vi_0900_ai_ci | utf8mb4 |277|| Yes |0| NO PAD || utf8mb4_vi_0900_as_cs | utf8mb4 |300|| Yes |0| NO PAD || utf8mb4_zh_0900_as_cs | utf8mb4 |308|| Yes |0| NO PAD | +-----------------------------+----------+-----+---------+----------+---------+---------------+ 286 rows inset(0.01 sec)艾莉丝这里查到的有点多，哈哈，uu们自己使用show collation;可能没有艾莉丝这么多，这是正常的。
2 ~> 过渡
接下来，艾莉丝分别从创建数据库以及创建案例、字符集和校验规则、操纵数据库这几个方面来详细库的操作。
3 ~> 创建数据库（附带案例）
3.1 创建数据库：语法 + 理解
语法：
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification]...] create_specification: [DEFAULT] CHARACTER SET charset_name [DEFAULT] COLLATE collation_name 说明：
- 大写的表示关键字
[]是可选项CHARACTER SET：指定数据库采用的字符集COLLATE：指定数据库字符集的校验规则
3.2 创建数据库的案例
3.2.1 创建名为db1的数据库
create database db1;- 说明： 当我们创建数据库没有指定字符集和校验规则时，系统使用默认字符集：
utf8，校验规则是：utf8_ general_ ci。
3.2.2 创建一个使用utf8字符集的db2数据库
create database db2 charset=utf8;3.2.3 创建一个使用utf字符集，并带校对规则的 db3 数据库。
create database db3 charset=utf8 collate utf8_general_ci;4 ~> 字符集和校验规则
4.1 查看系统默认字符集以及校验规则
show variables like 'character_set_database'; show variables like 'collation_database';4.2 查看数据库支持的字符集
show charset;字符集主要是控制用什么语言。比如utf8就可以使用中文。
4.3 查看数据库支持的字符集校验规则
show collation;4.4 校验规则对数据库的影响
4.4.1 不区分大小写
创建一个数据库，校验规则使用utf8_ general_ ci[不区分大小写]。
create database test1 collate utf8_general_ci;use test1;create table person(name varchar(20));insert into person values('a'); insert into person values('A'); insert into person values('b'); insert into person values('B');4.4.2 区分大小写
创建一个数据库，校验规则使用utf8_ bin[区分大小写]。
create database test2 collate utf8_bin;use test2 create table person(name varchar(20));insert into person values('a'); insert into person values('A'); insert into person values('b'); insert into person values('B');4.4.3 进行查询
4.4.3.1 不区分大小写的查询以及结果
mysql> use test1; mysql>select * from person where name='a'; +------+ | name | +------+ | a || A | +------+ 2 rows inset(0.01 sec)4.4.3.2 区分大小写的查询以及结果
mysql> use test2; mysql>select * from person where name='a'; +------+ | name | +------+ | a | +------+ 2 rows inset(0.01 sec)4.4.4 结果排序
4.4.4.1 不区分大小写排序以及结果
mysql> use test1; mysql>select * from person order by name; +------+ | name | +------+ | a || A || b || B | +------+ 4.4.4.2 区分大小写排序以及结果
mysql> use test2; mysql>select * from person order by name; +------+ | name | +------+ | A || B || a || b | +------+ 5 ~> 操纵数据库
5.1 查看数据库
show databases;5.2 显示创建语句
语法格式：
show create database 数据库名； 示例：
mysql> show create database mytest; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | mysql | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+ 说明：
MySQL建议我们关键字使用大写，但是不是必须的。- 数据库名字的反引号``，是为了防止使用的数据库名刚好是关键字
/*!40100 default.... */这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话。
5.2 修改数据库
语法格式：
ALTER DATABASE db_name [alter_spacification [,alter_spacification]...] alter_spacification: [DEFAULT] CHARACTER SET charset_name [DEFAULT] COLLATE collation_name 说明：
- 对数据库的修改主要指的是修改数据库的字符集，校验规则。
**示例：**将mytest数据库字符集改成gbk。
mysql> alter database mytest charset=gbk; Query OK, 1 row affected (0.00 sec) mysql> show create database mytest; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | mytest | CREATE DATABASE `mytest` /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+----------------------------------------------------------------+ 5.3 数据库删除
DROP DATABASE [IF EXISTS] db_ name;执行删除之后的结果：
- 数据库内部看不到对应的数据库。
- 对应的数据库文件夹被删除，级联删除，里面的数据表全部被删。
注意： 不要随意删除数据库。
5.4 备份和恢复：放在最后
5.4.1 备份
语法格式：
# mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径示例： 将mytest库备份到文件（退出连接）
# mysqldump -P3306 -u root -p123456 -B mytest > D:/mytest.sql- 这时，可以打开看看
mytest.sql文件里的内容，其实把我们整个创建数据库，建表，导入数据的语句都装载这个文件中。
5.4.2 还原
mysql>source D:/mysql-5.7.22/mytest.sql;5.4.3 注意事项
- 如果备份的不是整个数据库，而是其中的一张表，怎么做？
# mysqldump -u root -p 数据库名 表名1 表名2 > D:/mytest.sql- 同时备份多个数据库
# mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径- 如果备份一个数据库时，没有带上
-B参数， 在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source来还原。
5.5 查看连接情况
语法格式：
show processlist 示例：
mysql> show processlist; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ |2| root | localhost |test| Sleep |1386|| NULL ||3| root | localhost | NULL | Query |0| NULL | show processlist | +----+------+-----------+------+---------+------+-------+------------------+ 【查看连接情况】 这个操作可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是你正常登陆的，很有可能你的数据库被人入侵了。以后大家发现自己数据库比较慢时，可以用这个指令来查看数据库连接情况。
6 ~> MYSQL操作库部分的思维导图
结尾
uu们，本文的内容到这里就全部结束了，艾莉丝在这里再次感谢您的阅读！
结语：希望对学习MYSQL相关内容的uu有所帮助，不要忘记给博主“一键四连”哦！
往期回顾：
🗡博主在这里放了一只小狗，大家看完了摸摸小狗放松一下吧！🗡૮₍ ˶ ˊ ᴥ ˋ˶₎ა
