MySQL快速入门——库的操作
1. 创建数据库1. 语法：
CREATE DATABASE db_name ...]
create_specification:
CHARACTER SET charset_name
COLLATE collation_name
2. 说明：
[*]大写的表现关键字；
[*][]：是可选项；
[*]CHARACTER SET：指定数据库接纳的字符集；
[*]COLLATE：指定数据库字符集的校验规则。
2. 创建数据库案例
1. 创建名为 db1 的数据库
https://i-blog.csdnimg.cn/direct/1c43aab42a554c5189b3098d48de6246.png
[*]创建数据库的本质，是在/var/lib/mysql这个目录下，创建一个名为db1的文件夹（差别的配置下，这个路径可能差别）：
https://i-blog.csdnimg.cn/direct/041c08bd0e33419987ab9b03942e5b59.png
[*]说明：当我们创建数据库没有指定字符集和校验规则时，体系使用默认字符集utf8，校验规则是utf8_ general_ ci。
2. 创建一个使用utf8字符集的 db2 数据库
create database db2 charset=utf8;
[*]或：
create database db2 character set utf8;
3. 创建一个使用utf8字符集，并带校对规则的 db3 数据库。
create database db3 charset=utf8 collate utf8_general_ci;
3. 字符集和校验规则
3.1 查察字符集和校验规则
1. 什么是字符集和校验规则？
[*]字符集：指定数据按照什么样的格式存入；
[*]校验规则：数据按照什么样的格式读取。
2. 查察体系默认字符集以及校验规则
[*]体系中默认的字符集和校验规则都是utf8。
https://i-blog.csdnimg.cn/direct/aac4904245c444bd9af2c65363f3a62e.png
3. 查察数据库支持的所有字符集 show charset;
mysql> show charset;
+----------+---------------------------------+---------------------+--------+
| 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 |
| utf16le| UTF-16LE Unicode | utf16le_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 |
| gb18030| China National Standard GB18030 | gb18030_chinese_ci| 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.01 sec)
4. 查察数据库支持的所有校验规则 show collation;
mysql> show collation;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset| Id| Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 |84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 |69 | | Yes | 1 |
| cp850_general_ci | cp850 | 4 | Yes | Yes | 1 |
| cp850_bin | cp850 |80 | | Yes | 1 |
| hp8_english_ci | hp8 | 6 | Yes | Yes | 1 |
| hp8_bin | hp8 |72 | | Yes | 1 |
| koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 |
| koi8r_bin | koi8r |74 | | Yes | 1 |
| latin1_german1_ci | latin1 | 5 | | Yes | 1 |
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 |
| latin1_danish_ci | latin1 |15 | | Yes | 1 |
| latin1_german2_ci | latin1 |31 | | Yes | 2 |
| latin1_bin | latin1 |47 | | Yes | 1 |
| latin1_general_ci | latin1 |48 | | Yes | 1 |
| latin1_general_cs | latin1 |49 | | Yes | 1 |
| latin1_spanish_ci | latin1 |94 | | Yes | 1 |
| latin2_czech_cs | latin2 | 2 | | Yes | 4 |
| latin2_general_ci | latin2 | 9 | Yes | Yes | 1 |
| latin2_hungarian_ci | latin2 |21 | | Yes | 1 |
| latin2_croatian_ci | latin2 |27 | | Yes | 1 |
| latin2_bin | latin2 |77 | | Yes | 1 |
| swe7_swedish_ci | swe7 |10 | Yes | Yes | 1 |
| swe7_bin | swe7 |82 | | Yes | 1 |
| ascii_general_ci | ascii |11 | Yes | Yes | 1 |
| ascii_bin | ascii |65 | | Yes | 1 |
| ujis_japanese_ci | ujis |12 | Yes | Yes | 1 |
| ujis_bin | ujis |91 | | Yes | 1 |
| sjis_japanese_ci | sjis |13 | Yes | Yes | 1 |
| sjis_bin | sjis |88 | | Yes | 1 |
| hebrew_general_ci | hebrew |16 | Yes | Yes | 1 |
| hebrew_bin | hebrew |71 | | Yes | 1 |
| tis620_thai_ci | tis620 |18 | Yes | Yes | 4 |
| tis620_bin | tis620 |89 | | Yes | 1 |
| euckr_korean_ci | euckr |19 | Yes | Yes | 1 |
| euckr_bin | euckr |85 | | Yes | 1 |
| koi8u_general_ci | koi8u |22 | Yes | Yes | 1 |
| koi8u_bin | koi8u |75 | | Yes | 1 |
| gb2312_chinese_ci | gb2312 |24 | Yes | Yes | 1 |
| gb2312_bin | gb2312 |86 | | Yes | 1 |
| greek_general_ci | greek |25 | Yes | Yes | 1 |
| greek_bin | greek |70 | | Yes | 1 |
| cp1250_general_ci | cp1250 |26 | Yes | Yes | 1 |
| cp1250_czech_cs | cp1250 |34 | | Yes | 2 |
| cp1250_croatian_ci | cp1250 |44 | | Yes | 1 |
| cp1250_bin | cp1250 |66 | | Yes | 1 |
| cp1250_polish_ci | cp1250 |99 | | Yes | 1 |
| gbk_chinese_ci | gbk |28 | Yes | Yes | 1 |
| gbk_bin | gbk |87 | | Yes | 1 |
| latin5_turkish_ci | latin5 |30 | Yes | Yes | 1 |
| latin5_bin | latin5 |78 | | Yes | 1 |
| armscii8_general_ci | armscii8 |32 | Yes | Yes | 1 |
| armscii8_bin | armscii8 |64 | | Yes | 1 |
| 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_german2_ci | utf8 | 212 | | Yes | 8 |
| utf8_croatian_ci | utf8 | 213 | | Yes | 8 |
| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |
| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
| ucs2_general_ci | ucs2 |35 | Yes | Yes | 1 |
| ucs2_bin | ucs2 |90 | | Yes | 1 |
| ucs2_unicode_ci | ucs2 | 128 | | Yes | 8 |
| ucs2_icelandic_ci | ucs2 | 129 | | Yes | 8 |
| ucs2_latvian_ci | ucs2 | 130 | | Yes | 8 |
| ucs2_romanian_ci | ucs2 | 131 | | Yes | 8 |
| ucs2_slovenian_ci | ucs2 | 132 | | Yes | 8 |
| ucs2_polish_ci | ucs2 | 133 | | Yes | 8 |
| ucs2_estonian_ci | ucs2 | 134 | | Yes | 8 |
| ucs2_spanish_ci | ucs2 | 135 | | Yes | 8 |
| ucs2_swedish_ci | ucs2 | 136 | | Yes | 8 |
| ucs2_turkish_ci | ucs2 | 137 | | Yes | 8 |
| ucs2_czech_ci | ucs2 | 138 | | Yes | 8 |
| ucs2_danish_ci | ucs2 | 139 | | Yes | 8 |
| ucs2_lithuanian_ci | ucs2 | 140 | | Yes | 8 |
| ucs2_slovak_ci | ucs2 | 141 | | Yes | 8 |
| ucs2_spanish2_ci | ucs2 | 142 | | Yes | 8 |
| ucs2_roman_ci | ucs2 | 143 | | Yes | 8 |
| ucs2_persian_ci | ucs2 | 144 | | Yes | 8 |
| ucs2_esperanto_ci | ucs2 | 145 | | Yes | 8 |
| ucs2_hungarian_ci | ucs2 | 146 | | Yes | 8 |
| ucs2_sinhala_ci | ucs2 | 147 | | Yes | 8 |
| ucs2_german2_ci | ucs2 | 148 | | Yes | 8 |
| ucs2_croatian_ci | ucs2 | 149 | | Yes | 8 |
| ucs2_unicode_520_ci | ucs2 | 150 | | Yes | 8 |
| ucs2_vietnamese_ci | ucs2 | 151 | | Yes | 8 |
| ucs2_general_mysql500_ci | ucs2 | 159 | | Yes | 1 |
| cp866_general_ci | cp866 |36 | Yes | Yes | 1 |
| cp866_bin | cp866 |68 | | Yes | 1 |
| keybcs2_general_ci | keybcs2|37 | Yes | Yes | 1 |
| keybcs2_bin | keybcs2|73 | | Yes | 1 |
| macce_general_ci | macce |38 | Yes | Yes | 1 |
| macce_bin | macce |43 | | Yes | 1 |
| macroman_general_ci | macroman |39 | Yes | Yes | 1 |
| macroman_bin | macroman |53 | | Yes | 1 |
| cp852_general_ci | cp852 |40 | Yes | Yes | 1 |
| cp852_bin | cp852 |81 | | Yes | 1 |
| latin7_estonian_cs | latin7 |20 | | Yes | 1 |
| latin7_general_ci | latin7 |41 | Yes | Yes | 1 |
| latin7_general_cs | latin7 |42 | | Yes | 1 |
| latin7_bin | latin7 |79 | | 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 |
| utf8mb4_german2_ci | utf8mb4| 244 | | Yes | 8 |
| utf8mb4_croatian_ci | utf8mb4| 245 | | Yes | 8 |
| utf8mb4_unicode_520_ci | utf8mb4| 246 | | Yes | 8 |
| utf8mb4_vietnamese_ci | utf8mb4| 247 | | Yes | 8 |
| cp1251_bulgarian_ci | cp1251 |14 | | Yes | 1 |
| cp1251_ukrainian_ci | cp1251 |23 | | Yes | 1 |
| cp1251_bin | cp1251 |50 | | Yes | 1 |
| cp1251_general_ci | cp1251 |51 | Yes | Yes | 1 |
| cp1251_general_cs | cp1251 |52 | | Yes | 1 |
| utf16_general_ci | utf16 |54 | Yes | Yes | 1 |
| utf16_bin | utf16 |55 | | Yes | 1 |
| utf16_unicode_ci | utf16 | 101 | | Yes | 8 |
| utf16_icelandic_ci | utf16 | 102 | | Yes | 8 |
| utf16_latvian_ci | utf16 | 103 | | Yes | 8 |
| utf16_romanian_ci | utf16 | 104 | | Yes | 8 |
| utf16_slovenian_ci | utf16 | 105 | | Yes | 8 |
| utf16_polish_ci | utf16 | 106 | | Yes | 8 |
| utf16_estonian_ci | utf16 | 107 | | Yes | 8 |
| utf16_spanish_ci | utf16 | 108 | | Yes | 8 |
| utf16_swedish_ci | utf16 | 109 | | Yes | 8 |
| utf16_turkish_ci | utf16 | 110 | | Yes | 8 |
| utf16_czech_ci | utf16 | 111 | | Yes | 8 |
| utf16_danish_ci | utf16 | 112 | | Yes | 8 |
| utf16_lithuanian_ci | utf16 | 113 | | Yes | 8 |
| utf16_slovak_ci | utf16 | 114 | | Yes | 8 |
| utf16_spanish2_ci | utf16 | 115 | | Yes | 8 |
| utf16_roman_ci | utf16 | 116 | | Yes | 8 |
| utf16_persian_ci | utf16 | 117 | | Yes | 8 |
| utf16_esperanto_ci | utf16 | 118 | | Yes | 8 |
| utf16_hungarian_ci | utf16 | 119 | | Yes | 8 |
| utf16_sinhala_ci | utf16 | 120 | | Yes | 8 |
| utf16_german2_ci | utf16 | 121 | | Yes | 8 |
| utf16_croatian_ci | utf16 | 122 | | Yes | 8 |
| utf16_unicode_520_ci | utf16 | 123 | | Yes | 8 |
| utf16_vietnamese_ci | utf16 | 124 | | Yes | 8 |
| utf16le_general_ci | utf16le|56 | Yes | Yes | 1 |
| utf16le_bin | utf16le|62 | | Yes | 1 |
| cp1256_general_ci | cp1256 |57 | Yes | Yes | 1 |
| cp1256_bin | cp1256 |67 | | Yes | 1 |
| cp1257_lithuanian_ci | cp1257 |29 | | Yes | 1 |
| cp1257_bin | cp1257 |58 | | Yes | 1 |
| cp1257_general_ci | cp1257 |59 | Yes | Yes | 1 |
| utf32_general_ci | utf32 |60 | Yes | Yes | 1 |
| utf32_bin | utf32 |61 | | Yes | 1 |
| utf32_unicode_ci | utf32 | 160 | | Yes | 8 |
| utf32_icelandic_ci | utf32 | 161 | | Yes | 8 |
| utf32_latvian_ci | utf32 | 162 | | Yes | 8 |
| utf32_romanian_ci | utf32 | 163 | | Yes | 8 |
| utf32_slovenian_ci | utf32 | 164 | | Yes | 8 |
| utf32_polish_ci | utf32 | 165 | | Yes | 8 |
| utf32_estonian_ci | utf32 | 166 | | Yes | 8 |
| utf32_spanish_ci | utf32 | 167 | | Yes | 8 |
| utf32_swedish_ci | utf32 | 168 | | Yes | 8 |
| utf32_turkish_ci | utf32 | 169 | | Yes | 8 |
| utf32_czech_ci | utf32 | 170 | | Yes | 8 |
| utf32_danish_ci | utf32 | 171 | | Yes | 8 |
| utf32_lithuanian_ci | utf32 | 172 | | Yes | 8 |
| utf32_slovak_ci | utf32 | 173 | | Yes | 8 |
| utf32_spanish2_ci | utf32 | 174 | | Yes | 8 |
| utf32_roman_ci | utf32 | 175 | | Yes | 8 |
| utf32_persian_ci | utf32 | 176 | | Yes | 8 |
| utf32_esperanto_ci | utf32 | 177 | | Yes | 8 |
| utf32_hungarian_ci | utf32 | 178 | | Yes | 8 |
| utf32_sinhala_ci | utf32 | 179 | | Yes | 8 |
| utf32_german2_ci | utf32 | 180 | | Yes | 8 |
| utf32_croatian_ci | utf32 | 181 | | Yes | 8 |
| utf32_unicode_520_ci | utf32 | 182 | | Yes | 8 |
| utf32_vietnamese_ci | utf32 | 183 | | Yes | 8 |
| binary | binary |63 | Yes | Yes | 1 |
| geostd8_general_ci | geostd8|92 | Yes | Yes | 1 |
| geostd8_bin | geostd8|93 | | Yes | 1 |
| cp932_japanese_ci | cp932 |95 | Yes | Yes | 1 |
| cp932_bin | cp932 |96 | | Yes | 1 |
| eucjpms_japanese_ci | eucjpms|97 | Yes | Yes | 1 |
| eucjpms_bin | eucjpms|98 | | Yes | 1 |
| gb18030_chinese_ci | gb18030| 248 | Yes | Yes | 2 |
| gb18030_bin | gb18030| 249 | | Yes | 1 |
| gb18030_unicode_520_ci | gb18030| 250 | | Yes | 8 |
+--------------------------+----------+-----+---------+----------+---------+
222 rows in set (0.00 sec)
5. 查察某一个详细的数据库，对应的字符集和校验规则
[*]这个信息一般在配置文件db.opt中，直接cat查察即可：
https://i-blog.csdnimg.cn/direct/04fd12fcb82c4ec3a3144b6d6072591e.png
3.2 验证差别校验编码的影响
1. 不区分大小写
[*]创建一个数据库test1，校验规则使用utf8_ general_ ci（不区分大小写），同时建立person表，插入一些数据（表操作之后会详细介绍，这里只必要了解即可）：
https://i-blog.csdnimg.cn/direct/f6a6a4eb6cc24f5aa909c7d0af0d76d1.png
[*]查察person表：
https://i-blog.csdnimg.cn/direct/9b4507f1b76d46be9c6ba5da80c9b72a.png
[*]筛选出name为a的字段：
https://i-blog.csdnimg.cn/direct/4c90286e92f34bacb0a796bc455516aa.png
[*]发现在不区分大小写的情况下，name为a和A的字段都被筛选出来了。
2. 区分大小写
[*]创建一个数据库test2，校验规则使用utf8_ bin（区分大小写）：
https://i-blog.csdnimg.cn/direct/63552dd2fef845a4b987aa3718ac9a71.png
[*]筛选出name为a的字段：
https://i-blog.csdnimg.cn/direct/613b5b4aa9fb4d45affddfa96c14be94.png
[*]发现只有name为a的字段被筛选了出来。
4. 操作数据库
4.1 查察数据库
1. 查察所有数据库 show databases
[*]查察体系中所有的数据库（test1是我们之前创建的）：
https://i-blog.csdnimg.cn/direct/8f883a8cf090432498ce91fb6d98b659.png
2. 显示创建语句
[*]show create databases 数据库名。也可以通过该下令，查察该数据库的字符集和校验方式（字符集和校验方式都是配套的）：
https://i-blog.csdnimg.cn/direct/9bc8682597624fc590932ba4fe5ea61b.png
[*]说明：
[*]MySQL发起我们关键字使用大写，但是不是必须的；
[*]数据库名字的反引号，是为了防止使用的数据库名刚好是关键字；
[*]/*!40100 default.... */这个不是解释，表现当前mysql版本大于4.01版本，就执行这句话。
3. 查察当前所使用的数据库 select database();
https://i-blog.csdnimg.cn/direct/045f356dc09d4c73bf04c3b0cf5d7cc4.png
4.2 修改数据库
1. 语法
ALTER DATABASE db_name ...]
alter_spacification:
CHARACTER SET charset_name
COLLATE collation_name
2. 说明
[*]对数据库的修改主要指的是修改数据库的字符集，校验规则。
3. 实例
[*]将test1数据库字符集修改为gbk：
https://i-blog.csdnimg.cn/direct/5b9ba9aae64640338a9daa820dd5ee1a.png
[*]可以发现，我们固然只改了字符集，但是校验规则也跟着改变了：
https://i-blog.csdnimg.cn/direct/1c08b7737ac94d03a6e07ec050d7bd5e.png
4.3 数据库删除
1. 语法
DROP DATABASE db_ name;
2. 执行删除之后的结果:
[*]数据库内部看不到对应的数据库；
[*]对应的数据库文件夹被级联删除，内里的数据表全部被删。
注意：不要随意删除数据库。
4.4 备份和恢复
1. 备份
[*]语法：
[*]注意，这是在操作体系终端执行的指令。
mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径
[*]示例：将test1库备份到文件。
https://i-blog.csdnimg.cn/direct/e9491c3ca5194408a879a626c08c9ce5.png
[*]这时，可以打开看看 mytest.sql 文件里的内容，实在是把我们整个创建数据库，建表，导入数据的语句都装载进了这个文件中。
https://i-blog.csdnimg.cn/direct/39bfdb6c32c84b429e5700aa02cdd3c3.png
2. 恢复
[*]连接mysql，输入指令source .sql文件路径;：
https://i-blog.csdnimg.cn/direct/990884d5ba684abdaa56844d8aec986e.png
[*]查察恢复的数据库：
https://i-blog.csdnimg.cn/direct/c06b3a854bba4a01ac20ae8c73212e69.png
3. 注意事项
[*]假如备份的不是整个数据库，而是此中的一张表，怎么做？
mysqldump -u root -p 数据库名 表名1 表名2 ... > 文件路径
[*]同时备份多个数据库：
mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径
[*]假如备份一个数据库时，没有带上-B参数，在恢复数据库时，必要先创建空数据库，然后use数据库，再使用source来还原。
5. 查察连接情况
1. 语法
show processlist;
2. 示例
https://i-blog.csdnimg.cn/direct/4ba6e3d0a246469e934d1b59656411fe.png
可以告诉我们当前有哪些用户连接到我们的MySQL，假如查出某个用户不是你正常登陆的，很有可能你的数据库被人入侵了。以后各人发现自己数据库比较慢时，可以用这个指令来查察数据库连接情况。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]