热门标签
当前位置: article > 正文
MySQL的数据库操作和数据表操作
作者：我家自动化 | 2024-02-27 09:32:59
赞
踩
MySQL的数据库操作和数据表操作
1.连接 MySQL
MySQL 本质是一个网络服务，因此需要连接主机 IP 地址
mysql -h 127.0.0.1 -P 3306 -u root -p
- 1
回车输入密码或者设置免密。
-h：指明登录部署了MySQL服务的主机-P：指明需要访问的端口号-u：指明登录用户-p：指明需要输入的密码
而使用 quit 就可以退出数据库（使用 exit 也可以）。
连接上数据库后，可以使用类似编程语言的数据库语法来操作数据库。
2.数据库操作
前要：只是的数据库本身进行增删查改、备份、恢复等操作，而不是对数据库内的数据表做操作，还请您区分好这两点。
2.1.创建数据库
# 创建数据库的语法形式
CREATE DATABASE [IF NOT EXISTS] database_name [create_specification]
# 大写的是关键字
# [] 是可选项
# [create_specification]:设置数据库的创建规格
# (1)[charset=指定字符集]/[charset set 指定字符集]:数据库设置字符集
# (2)[collate 指定校验规则]:数据库设置校验集/校验规则
# IF NOT EXISTS:如果数据库不存在就创建，存在就不创建
# CHARACTER SET:指定数据库采用的字符集
# COLLATE:指定数据库字符集的校验规则
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
会在 /var/lib/mysql 里创建一个和数据库列表中同名的一个目录（实际上我们在该目录下创建一个目录时，使用 SHOW DATABASE 也会出现在数据库列表中，但是最好不要这么做）。
创建数据库的知识我们前面已经讲过了，这里只是做一些语法补充，其中最重要的补充就是字符编码和校验规则。
当我们创建数据库没有指定字符集和校验规则时，系统将会使用默认字符集 utf8 和校验规则 utf8_general_ci。
我们可以使用以下指令来查看系统默认的字符集和校验规则：
# 查看系统默认的字符集和校验规则 mysql> show variables like 'character_set_database'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | utf8 | +------------------------+-------+ 1 row in set (0.01 sec) mysql> show variables like 'collation_database'; +--------------------+-----------------+ | Variable_name | Value | +--------------------+-----------------+ | collation_database | utf8_general_ci | +--------------------+-----------------+ 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
这两个设置的值会被存放在数据库对应的文件目录下的 db.opt 文件中。
2.1.1.字符编码
字符集的设置，关系到数据库未来如何“存储”或“表示”数据。
补充：
utf8支持中文。
可以使用以下命令来查看数据库支持的字符集：
# 查看数据库支持的字符集 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.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
2.1.2.校验规则
校验规则定义了字符的比较规则、排序规则、字符串的大小写敏感性等，影响数据库的排序、搜索、比较操作。
可以使用以下命令来查看数据库支持的校验规则：
# 查看数据库支持的校验规则 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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
补充：这个校验规则的输出还是挺多的，在
MySQL中可不可以清屏呢？可以，使用system clear就可以了。
2.1.3.差别比较
不同的字符集和校验规则设置会带来一些差异，下面将带您深入了解这一差距的具体现象（本例使用不同的校验规则来查看这一现象）。
-
如下要求创建两个数据库，并且建表插入数据
(1)创建一个数据库，校验规则使用
utf8_general_ci[不区分大小写](2)创建一个数据库，校验规则使用
utf8_bin[区分大小写]# 按照要求建库和建表 mysql> create database test1 collate utf8_general_ci; Query OK, 1 row affected (0.01 sec) mysql> use test1; Database changed mysql> create table person(name varchar(20)); Query OK, 0 rows affected (0.04 sec) mysql> insert into person values('a'); Query OK, 1 row affected (0.00 sec) mysql> insert into person values('A'); Query OK, 1 row affected (0.01 sec) mysql> create database test2 collate utf8_bin; Query OK, 1 row affected (0.00 sec) mysql> use test2 Database changed mysql> create table person(name varchar(20)); Query OK, 0 rows affected (0.03 sec) mysql> insert into person values('a'); Query OK, 1 row affected (0.00 sec) mysql> insert into person values('A'); Query OK, 1 row affected (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
-
分别进行查询，观察结果
# 查询数据的结果 mysql> use test1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from person where name='a'; +------+ | name | +------+ | a | | A | +------+ 2 rows in set (0.01 sec) mysql> use test2; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from person where name='a'; +------+ | name | +------+ | a | +------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
-
分别进行排序，观察结果
# 排序数据的结果 mysql> use test1 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from person order by name; +------+ | name | +------+ | a | | A | | b | | B | +------+ 4 rows in set (0.00 sec) mysql> use test2 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from person order by name; +------+ | name | +------+ | A | | B | | a | | b | +------+ 4 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
可以看到，不同的校验规则的确会导致查询结果的不同（上述查询语法、排序语法我们后面学习，您先看着就行）。
2.2.选择数据库
后续有一些操作必须选中对应的数据库才可以进行，因此我们需要能选择一个具体的数据库的语法。
# 选择数据库的语法形式
USE database_nae;
- 1
- 2
2.3.查看数据库
可以查看当前存在的数据库。
# 查看数据库的语法形式
SHOW DATABASES
- 1
- 2
补充：数据库的所在地。
这些数据库都在哪里呢？我们可以查看
my.cnf的datadi字段，其值为MySQL的数据存放路径/var/lib/mysql。# 查询数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.05 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
# my.cnf 的文本片段 [root] # cat my.cnf # ... datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # ...
- 1
- 2
- 3
- 4
- 5
- 6
# 查看 /var/lib/mysql 目录 [root] # pwd /var/lib/mysql [root] # ls auto.cnf ib_buffer_pool mysql public_key.pem ca-key.pem ibdata1 mysql.sock server-cert.pem ca.pem ib_logfile0 mysql.sock.lock server-key.pem client-cert.pem ib_logfile1 performance_schema sys client-key.pem ibtmp1 private_key.pem
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
2.4.修改数据库
这里的修改数据库指的是对数据库本身的修改（修改已有数据库的字符集和校验规则），而不是插入或修改表格内的数据。
# 修改数据库的语法形式
ALTER DATABASE database_name charset=charset_name[, collate collation_name]
- 1
- 2
编码变化可以使用 show create database database_name; 来查看变化。
# 修改数据库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | my_database | | mysql | | performance_schema | | sys | | test1 | | test2 | +--------------------+ 7 rows in set (0.00 sec) mysql> use test1 Database changed mysql> show create database test1; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> alter database test1 charset=gbk collate gbk_chinese_ci; Query OK, 1 row affected (0.00 sec) mysql> show create database test1; +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | test1 | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET gbk */ | +----------+---------------------------------------------------------------+ 1 row in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
2.5.删除数据库
# 删除数据库的语法形式
DROP DATABASE [IF EXISTS] database_name;
- 1
- 2
会在 /var/lib/mysql 里删除一个和数据库列表中同名的一个目录，且包括内部的文件也被删除（实际上我们在该目录下删除一个目录时，使用 SHOW DATABASE 也会在数据库列表中去除，但是您最好不要这么做）。
2.6.备份数据库
最简单的数据库备份做法就是找到数据库对应的目录直接进行打包压缩拷贝存储起来，但是这种操作过于简单粗暴，容易出现各种问题（包括 MySQL 现在不支持重命名也是这个原因）。
我们可以使用下面 MySQL 自带的语法来进行备份：
# 备份数据库的语法形式
$ mysqldump -P3306 -u root -p -B 数据库名 > 备份文件的存储路径
- 1
- 2
注意：上述命令是在
Shell中执行的，不是在数据库内（安装数据库的时候，该工具会被默认安装），并且由于我们的数据库数免密登录的，所以不需要密码回车即可备份。
# 备份数据库命令
$ mysqldump -P3306 -u root -p -B test1 > ./test1.sql
Enter password:
$ ls
test1.sql
- 1
- 2
- 3
- 4
- 5
- 6
如果观察备份文件的内容，您就会发现：该文件备份的不仅仅是我们填入的数据，还有曾经对数据库进行的各种有效操作…
还可以进行一张表的备份：
$ mysqldump -u root -p 数据库名 表名1 表名2 > 备份文件的存储路径
- 1
或者多个数据库的备份：
$ mysqldump -u root -p -B 数据库名1 数据库名2 ... > 备份文件的存储路径
- 1
2.7.恢复数据库
如果我们拥有了一个数据库的备份文件，那么应该怎么恢复呢？使用下述语法：
# 恢复数据库语法形式
source 备份文件所处路径（包括文件名）;
- 1
- 2
补充：由于备份的时候备份的是操作和数据，因此恢复的时候实际上也差不多是执行原有的有效操作的过程（所以会弹出很多类似
Query OK, 0 rows affected (0.00 sec)的回显消息）。
# 恢复数据库 mysql> drop database test1; Query OK, 1 row affected (0.14 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | my_database | | mysql | | performance_schema | | sys | | test2 | +--------------------+ 6 rows in set (0.00 sec) mysql> source /home/test1.sql Query OK, 0 rows affected (0.00 sec) ... mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | my_database | | mysql | | performance_schema | | sys | | test1 | | test2 | +--------------------+ 7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
注意：如果备份一个数据库时，没有带上
-B参数，那么在恢复数据库时，需要先创建空的数据库，然后切换到该数据库，最后使用source语法来还原数据库。
补充：查看数据库连接情况的语法形式
# 查看数据库连接情况的语法形式 SHOW PROCESSLIST;
- 1
- 2
该操作可显示当前有哪些用户连接到您的
MySQL，一但查出非正常登陆的用户，则很有可能您的数据库已经被人入侵了（在数据库运行较慢时，可以尝试运行该指令查看）。
3.数据表操作
补充：以下如果涉及到“冲突”、“主键”、“自增”、“外键”等字眼，您可以先暂时略过，看完大概后，然后先阅读《MySQL 的约束》再来这里重新学习表的语法，尤其时查找语法。
3.1.创建数据表
# 创建数据表语法
CREATE TABLE [IF NOT EXISTS] table_name (
field1 datatype,
field2 datatype,
field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;
# 其中 fieldx 是列名，datatype 是列类型，而如果没有设定字符集就会以创建数据库时的字符集为标准（同理校验规则也一样），而存储引擎的默认选项早在我们配置 MySQL 的时候就有配置
- 1
- 2
- 3
- 4
- 5
- 6
- 7
补充：例如下面，就是一个创建数据表的例子。
# 尝试创建一个数据表 create table users1 ( id int, name varchar(20) comment '用户名', password char(32) comment '密码是32位的md5值', birthday date comment '生日' ) character set utf8 engine MyISAM;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
并且立马可以发现在
/var/lib/mysql里会多出三个文件：$ ls db.opt users1.frm users1.MYD users1.MYI
- 1
- 2
如果使用其他的存储引擎（例如
InnoDB）就会发现，文件的数量和后缀会根据搜索引擎的变化而变化。# ls db.opt users2.frm users2.ibd users1.frm users1.MYD users1.MYI
- 1
- 2
这些文件的细节我以后在谈及。
3.2.选择数据库
# 选择数据库的语法形式
USE table_name;
- 1
- 2
3.3.查看数据表
3.3.1.查看数据库内的数据表
如果需要列出一个数据库内的数据表，可以使用：
# 查看数据库内数据表的语法形式
USE database_name;
SHOW TABLES;
- 1
- 2
- 3
3.3.2.查看数据表的字段和描述
查找数据表的字段/描述时，可以使用：
# 查看数据表的字段
DESC 数据表名;
- 1
- 2
补充：关于数据表的字段我们可以简单了解一下。
# 字段解释 +----------------+--------------+------------------+--------------+----------------+------------+ | Field(字段名字) | Type(字段类型) | Null(是否允许为空) | Key(索引类型) | Default(默认值) | Extra(扩充) | +----------------+--------------+------------------+--------------+----------------+------------+ +----------------+--------------+------------------+--------------+----------------+------------+
- 1
- 2
- 3
- 4
- 5
如果需要查询在创建数据表时的创建细节（会标准、优化曾经我们书写的 SQL 语句），可以使用（如果打印结果不容易观看可以把 ; 换成 \G）：
# 查看数据表的创建细节
SHOW CREATE TABLE table_name;
- 1
- 2
3.3.3.查看数据库的内部数据
在查找某个数据表内部的数据时，需要进入该数据表才能进行查找：
# 查询数据库的语法形式
USE 数据库名;
SELECT 列名/可以使用 * 表示所有列 from 表格名 [where 匹配条件];
- 1
- 2
- 3
如果进入某个数据库后，忘记在哪个数据库了，则可以使用 select database();
3.4.修改数据表
3.4.1.结构修改
下面语法主要是用于修改数据表整体或部分结构的。
# 修改数据表语法(结构)
# (1)修改数据表名
TABLE table_name RENAME [TO] new_table_name;
# (2)修改数据表的列名称
ALTER TABLE table_name CHANGE old_name new_name DATATYPE;
# (3)新增某一列
ALTER TABLE table_name ADD new_column_name DATATYPE [COMMENT '列的描述'] [AFTER old_column_name]; # AFTER 表示该列增添在 old_column_name 之后，新增字段的值默认为 NULL
# (4)修改指定列的属性
ALTER TABLE table_name MODIfy column_name NEW_DATATYPE; # 默认会覆盖 comment 描述，这说明这里的修改是一种覆盖，该操作比较危险
# (5)删除某一列
ALTER TABLE table_name DROP column_name;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
可以观察到，如果修改数据表名，对于 /var/lib/mysql 路径下的文件名也会进行对应的修改。
3.4.2.插入修改
# 修改数据表语法(插入)
# (1)插入一行数据进入表格
INSERT INTO table_name (field_name[, ...]) VALUES (value[, ...]), (value[, ...]), ...;
# 如果插入的数据是全字段的，则可以省略 (field_name[, ...])，或者指定列插入时，可以省略部分字段插入，并且可以同时插入多个记录
# (2)插入，失败则更新（针对主键冲突、唯一键冲突）
INSERT INTO table_name (field_name[, ...]) VALUES (value[, ...])
ON DUPLICATE KEY UPDATE field_name = new_value, ...
# 上述的插入即使主键和唯一键产生冲突也能进行更新
-- 0 row affected：表中有冲突数据，但冲突数据的值和 update 的值相等
-- 1 row affected：表中没有冲突数据，数据被插入
-- 2 row affected：表中有冲突数据，并且数据已经被更新
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
3.4.3.替换修改
# 修改数据表语法(替换)
REPLACE INTO table_name (field_name[, ...]) VALUES (value[, ...]); # 如果和信息出现冲突，就把老信息删除再插入
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入
- 1
- 2
- 3
- 4
补充：失败则更新和替换的区别
INSERT INTO ... ON DUPLICATE KEY UPDATE：如果要插入的行包含自增主键列，并且由于存在冲突而触发更新操作，则自增主键值不会更改。REPLACE INTO：如果表定义了自增主键，插入操作将始终分配新的自增主键值，即使它删除了现有行并插入了新行。
3.5.删除数据表
# 删除数据的相关语法
# (1)删除数据表语法
DROP TABLE [IF EXISTS] table_name, ...
# (2)删除数据表的行
DELETE FROM table_name [WHERE condition];
# 尽管这个操作可以做到清空表中的数据，但它不会重置表的自增计数器（如果有的话）。如果表中有外键约束和触发器，DELETE 语句会触发这些约束和触发器。DELETE 操作通常比 TRUNCATE 操作慢，因为它逐行删除数据，并且记录了每一行的删除操作，以便可以回滚。
# (3)重置 table_name 表中的所有行，又被称为“截断表”
TRUNCATE [TABLE] table_name;
# 会快速移除表中的所有行，并且自动重置任何自增的计数器。相对于 DELETE，TRUNCATE 是一个更快的操作，因为它不逐行删除数据，而是直接重新初始化表。然而，TRUNCATE 不能触发表的删除触发器，并且在某些情况下可能不受外键约束的限制（该操作不会对数据做操作，并且也不会把自己包装成事务，操作痕迹不会记录到日志里，因此也无法回滚）。
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
注意：关于数据表的备份和恢复问题，在前面的数据库备份和恢复中有所提及…
补充：
MySQL有三个日志bin log、redo log、undo log(日志/内存级，便于事物回滚)，以后进行补充
- Bin log（二进制日志）：
Bin log包含了数据库中执行的所有写操作，以二进制格式记录。这包括对数据的插入、更新和删除操作。Bin log对于数据的备份、复制和恢复非常重要，因为它允许数据库管理员在不停机的情况下复制数据到其他服务器，或者在需要时进行恢复操作。它还用于基于时间点的恢复（Point-In-Time Recovery），默认情况下Bin log是被关闭的。- Redo Log（重做日志）：
Redo Log记录了数据页的物理修改，而不是逻辑修改。在MySQL中，数据被修改时，首先将修改记录到Redo Log中，然后再将修改应用到内存中的数据页上。这样做是为了提高性能，因为将修改记录到Redo Log比直接将其写入磁盘要快得多。Redo Log还有助于保证数据库的持久性：在发生崩溃时，可以通过重放Redo Log中的操作来恢复数据）。- Undo Log（撤销日志）：
Undo Log用于事务的回滚操作。当事务开始时，MySQL会将事务涉及的数据的旧值记录到Undo Log中。如果事务需要回滚，MySQL可以使用Undo Log中的数据将数据恢复到事务开始之前的状态。Undo Log还用于MVCC（多版本并发控制），在读取被其他事务修改但尚未提交的数据时，MySQL可以通过Undo Log获取到数据的旧值，以确保读取操作的一致性和隔离性。持久化说白了就是可以快速把数据从数据库中恢复出来，一般提高持久化的策略有 (1)记录历史
SQL语句 (2)记录数据本身
3.6.查找数据库
3.6.1.查找的基本语法
最经典的查找关键字就是 SELECT，使用 SELECT * FROM table_name; 可以查看一张数据表的所有具体内容。
# 查询数据库的相关语法
SELECT
[DISTINCT] # 确保返回的结果集每一行都是唯一的（去重）
* / field_name [[as] new table_name], ... # 筛选出 所有列/选定列 的数据
[FROM table_name] # 被检索数据的表格名称
[WHERE ...] # 过滤出 行的检索 结果
[ORDER BY field_name [ASC / DESC], ...] # 排序
[LIMIT line_size/line_begin, line_size] # 限制结果的行数，line_begin 从 0 开始，或者使用 line_size OFFSET line_begin 和使用 line_begin, line_size 等价
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
以上语法有些抽象，可以实践一下：
补充：一般不用
*查询，实际生产中的数据库通常都是远端的数据库，面对大型的数据库，会导致大量的网络传输需求。
# 使用 SELECT 来运算 mysql> select 1+1; +-----+ | 1+1 | +-----+ | 2 | +-----+ 1 row in set (0.00 sec) mysql> select 1*3; +-----+ | 1*3 | +-----+ | 3 | +-----+ 1 row in set (0.00 sec) mysql> select * from exam_result; +----+------+---------+------+---------+ | id | name | chinese | math | english | +----+------+---------+------+---------+ | 1 | tsz | 67 | 98 | 56 | | 2 | swk | 87 | 78 | 77 | | 3 | zwn | 88 | 98 | 90 | | 4 | cmd | 82 | 84 | 67 | | 5 | lxd | 55 | 85 | 45 | | 6 | sq | 70 | 73 | 78 | | 7 | sgm | 75 | 65 | 30 | +----+------+---------+------+---------+ 7 rows in set (0.00 sec) mysql> select chinese + math + english from exam_result; +--------------------------+ | chinese + math + english | +--------------------------+ | 221 | | 242 | | 276 | | 233 | | 185 | | 221 | | 170 | +--------------------------+ 7 rows in set (0.00 sec) mysql> select chinese+math+english as total from exam_result; +-------+ | total | +-------+ | 221 | | 242 | | 276 | | 233 | | 185 | | 221 | | 170 | +-------+ 7 rows in set (0.00 sec) mysql> select name, chinese+math+english as total from exam_result; # 这里的 as 是重命名，可以省略 +------+-------+ | name | total | +------+-------+ | tsz | 221 | | swk | 242 | | zwn | 276 | | cmd | 233 | | lxd | 185 | | sq | 221 | | sgm | 170 | +------+-------+ 7 rows in set (0.00 sec) mysql> select name 姓名, chinese+math+english 总分 from exam_result; +--------+--------+ | 姓名 | 总分 | +--------+--------+ | tsz | 221 | | swk | 242 | | zwn | 276 | | cmd | 233 | | lxd | 185 | | sq | 221 | | sgm | 170 | +--------+--------+ 7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
SELECT 除了查找还可以显示运算结果…究其原因，实际上是 SELECT 会显示后续式子的运算结果（无论是自己检索语法还是别的表达式语法）。
3.6.2.使用子句筛选行
而检索出的结果有可能还需要进一步筛选，这个使用就要使用 WHERE 条件子句，WHERE 子句可以增添运算符，例如：
-
>、>=、<、<=：不等于符号 -
=、<=>：等于号（前者不是赋值，就是一个等号表示等于）(1)前者
NULL不安全，无法参与比较(2)后者
NULL安全，返回结果为TRUE(1) -
!=、<>：不等于号（也是NULL不安全的） -
BETWEEN a0 AND a1：范围匹配，如果value∈[a0,a1]就返回TRUE(1) -
IN(option...)：如果是集合中元素option的任意一个，就返回TRUE(1) -
IS NULL、IS NOT NULL：专门用于比较是否为NULL -
LIKE：模糊匹配，%表示任意多个（包括0个）任意字符，_表示任意一个字符 -
NOT：逻辑取反操作
补充：其实在前面使用
SELECT筛选全字段的时候也可以使用运算符。
并且也可以使用逻辑运算符：AND、OR、NOT，用法和 C 语言类似
# 使用运算符 mysql> select * from exam_result; +----+------+---------+------+---------+ | id | name | chinese | math | english | +----+------+---------+------+---------+ | 1 | tsz | 67 | 98 | 56 | | 2 | swk | 87 | 78 | 77 | | 3 | zwn | 88 | 98 | 90 | | 4 | cmd | 82 | 84 | 67 | | 5 | lxd | 55 | 85 | 45 | | 6 | sq | 70 | 73 | 78 | | 7 | sgm | 75 | 65 | 30 | +----+------+---------+------+---------+ 7 rows in set (0.00 sec) mysql> select name, chinese, math, english from exam_result where english<60; +------+---------+------+---------+ | name | chinese | math | english | +------+---------+------+---------+ | tsz | 67 | 98 | 56 | | lxd | 55 | 85 | 45 | | sgm | 75 | 65 | 30 | +------+---------+------+---------+ 3 rows in set (0.00 sec) mysql> select name from exam_result where name like 's_'; +------+ | name | +------+ | sq | +------+ 1 row in set (0.00 sec) mysql> select name from exam_result where name like 's__'; +------+ | name | +------+ | swk | | sgm | +------+ 2 rows in set (0.00 sec) mysql> select name from exam_result where name like 's%'; +------+ | name | +------+ | swk | | sq | | sgm | +------+ 3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
补充：
SELECT后的筛选条件主要是筛选列，WHERE后的筛选条件主要是筛选行。
3.6.3.插入查找的数据
查找的结果可以被重新插入：
# 插入查找结果的语法形式
INSERT INTO table_name [field_name, ...] SELECT ...
- 1
- 2
# 尝试插入并且去重 mysql> create table duplicate_table (id int, name varchar(20)); Query OK, 0 rows affected (0.03 sec) mysql> insert into duplicate_table values -> (100, 'aaa'), -> (100, 'aaa'), -> (200, 'bbb'), -> (200, 'bbb'), -> (200, 'bbb'), -> (300, 'ccc'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from duplicate_table; +------+------+ | id | name | +------+------+ | 100 | aaa | | 100 | aaa | | 200 | bbb | | 200 | bbb | | 200 | bbb | | 300 | ccc | +------+------+ 6 rows in set (0.00 sec) mysql> create table no_duplicate_table like duplicate_table; # like 语法也可以使用在数据表上，只会复制表结构 Query OK, 0 rows affected (0.03 sec) mysql> insert into no_duplicate_table select distinct * from duplicate_table; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from no_duplicate_table; +------+------+ | id | name | +------+------+ | 100 | aaa | | 200 | bbb | | 300 | ccc | +------+------+ 3 rows in set (0.00 sec) mysql> rename table duplicate_table to old_no_duplicate_table; mysql> rename table no_duplicate_table to duplicate_table;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
3.6.4.分组查询数据表
GROUP BY 子句就可以根据 指定列的不同值 来分组（相同值为一组，也就是“聚合压缩”）查询，后面提及聚合函数的时候会详细解释使用。
# 分组查询数据表达的语法
SELECT field_name, ... FROM table_name GROUP BY field_name;
- 1
- 2
3.7.排序数据库
在 MySQL 里，ASC 为升序（从小到大），DESC 为降序（从大到小），默认一张表是 ASC。
没有使用 ORDER BY 情况下查询数据表结果的顺序是不固定的，不要依赖这个顺序。
# 对结果进行排序 mysql> select * from exam_result; +----+------+---------+------+---------+ | id | name | chinese | math | english | +----+------+---------+------+---------+ | 1 | tsz | 67 | 98 | 56 | | 2 | swk | 87 | 78 | 77 | | 3 | zwn | 88 | 98 | 90 | | 4 | cmd | 82 | 84 | 67 | | 5 | lxd | 55 | 85 | 45 | | 6 | sq | 70 | 73 | 78 | | 7 | sgm | 75 | 65 | 30 | +----+------+---------+------+---------+ 7 rows in set (0.00 sec) mysql> select name, chinese, math, english, chinese+math+english from exam_result where (chinese+math+english>200 and chinese<math and english>80) or (name like 's%') order by chinese+math+english desc; +------+---------+------+---------+----------------------+ | name | chinese | math | english | chinese+math+english | +------+---------+------+---------+----------------------+ | zwn | 88 | 98 | 90 | 276 | | swk | 87 | 78 | 77 | 242 | | sq | 70 | 73 | 78 | 221 | | sgm | 75 | 65 | 30 | 170 | +------+---------+------+---------+----------------------+ 4 rows in set (0.00 sec) mysql> select name, chinese, math, english, chinese+math+english from exam_result order by math desc, english desc; # 多种升序结合使用 +------+---------+------+---------+----------------------+ | name | chinese | math | english | chinese+math+english | +------+---------+------+---------+----------------------+ | zwn | 88 | 98 | 90 | 276 | | tsz | 67 | 98 | 56 | 221 | | lxd | 55 | 85 | 45 | 185 | | cmd | 82 | 84 | 67 | 233 | | swk | 87 | 78 | 77 | 242 | | sq | 70 | 73 | 78 | 221 | | sgm | 75 | 65 | 30 | 170 | +------+---------+------+---------+----------------------+ 7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
3.8.分页数据表
LIMIT line_size/line_begin, line_end 限制显示结果的行数，其作用是筛选结果，避免表数据过大导致数据库软件卡死。
# 尝试分页表数据 mysql> select * from exam_result; +----+------+---------+------+---------+ | id | name | chinese | math | english | +----+------+---------+------+---------+ | 1 | tsz | 67 | 98 | 56 | | 2 | swk | 87 | 78 | 77 | | 3 | zwn | 88 | 98 | 90 | | 4 | cmd | 82 | 84 | 67 | | 5 | lxd | 55 | 85 | 45 | | 6 | sq | 70 | 73 | 78 | | 7 | sgm | 75 | 65 | 30 | +----+------+---------+------+---------+ 7 rows in set (0.00 sec) mysql> select name, chinese, math, english, chinese+math+english as total from exam_result order by total asc; +------+---------+------+---------+-------+ | name | chinese | math | english | total | +------+---------+------+---------+-------+ | sgm | 75 | 65 | 30 | 170 | | lxd | 55 | 85 | 45 | 185 | | tsz | 67 | 98 | 56 | 221 | | sq | 70 | 73 | 78 | 221 | | cmd | 82 | 84 | 67 | 233 | | swk | 87 | 78 | 77 | 242 | | zwn | 88 | 98 | 90 | 276 | +------+---------+------+---------+-------+ 7 rows in set (0.00 sec) mysql> select name, chinese, math, english, chinese+math+english as total from exam_result order by total asc limit 0, 3; +------+---------+------+---------+-------+ | name | chinese | math | english | total | +------+---------+------+---------+-------+ | sgm | 75 | 65 | 30 | 170 | | lxd | 55 | 85 | 45 | 185 | | tsz | 67 | 98 | 56 | 221 | +------+---------+------+---------+-------+ 3 rows in set (0.00 sec) mysql> select name, chinese, math, english, chinese+math+english as total from exam_result order by total asc limit 2, 3; +------+---------+------+---------+-------+ | name | chinese | math | english | total | +------+---------+------+---------+-------+ | tsz | 67 | 98 | 56 | 221 | | sq | 70 | 73 | 78 | 221 | | cmd | 82 | 84 | 67 | 233 | +------+---------+------+---------+-------+ 3 rows in set (0.00 sec) mysql> select name, chinese, math, english, chinese+math+english as total from exam_result order by total asc limit 3 offset 2; +------+---------+------+---------+-------+ | name | chinese | math | english | total | +------+---------+------+---------+-------+ | tsz | 67 | 98 | 56 | 221 | | sq | 70 | 73 | 78 | 221 | | cmd | 82 | 84 | 67 | 233 | +------+---------+------+---------+-------+ 3 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
3.9.更新数据表
更新的语句又是会比较危险，非必要则不要使用。
# 查询数据库的相关语法
UPDATE table_name SET field_name = expression, ...
[WHERE ...] # 过滤出 行的检索 结果
[ORDER BY field_name [ASC / DESC], ...] # 排序
[LIMIT line_size/line_begin, line_size] # 限制结果的行数，line_begin 从 0 开始，或者使用 line_size OFFSET line_begin 和使用 line_begin, line_size 等价
- 1
- 2
- 3
- 4
- 5
# 尝试更新数据表的数据记录 mysql> select * from exam_result; +----+------+---------+------+---------+ | id | name | chinese | math | english | +----+------+---------+------+---------+ | 1 | tsz | 67 | 98 | 56 | | 2 | swk | 87 | 78 | 77 | | 3 | zwn | 88 | 98 | 90 | | 4 | cmd | 82 | 84 | 67 | | 5 | lxd | 55 | 85 | 45 | | 6 | sq | 70 | 73 | 78 | | 7 | sgm | 75 | 65 | 30 | +----+------+---------+------+---------+ 7 rows in set (0.00 sec) mysql> update exam_result set name='limou' where name='tsz'; # 这里使用 where 子句来查找行上 name 字段数据为 'tsz' 的记录 Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from exam_result; +----+-------+---------+------+---------+ | id | name | chinese | math | english | +----+-------+---------+------+---------+ | 1 | limou | 67 | 98 | 56 | | 2 | swk | 87 | 78 | 77 | | 3 | zwn | 88 | 98 | 90 | | 4 | cmd | 82 | 84 | 67 | | 5 | lxd | 55 | 85 | 45 | | 6 | sq | 70 | 73 | 78 | | 7 | sgm | 75 | 65 | 30 | +----+-------+---------+------+---------+ 7 rows in set (0.00 sec) mysql> select chinese, math, english, chinese+math+english as total from ex am_result order by chinese+math+english; +---------+------+---------+-------+ | chinese | math | english | total | +---------+------+---------+-------+ | 75 | 65 | 30 | 170 | | 55 | 85 | 45 | 185 | | 67 | 98 | 56 | 221 | | 70 | 73 | 78 | 221 | | 82 | 84 | 67 | 233 | | 87 | 78 | 77 | 242 | | 88 | 98 | 90 | 276 | +---------+------+---------+-------+ 7 rows in set (0.00 sec) mysql> update exam_result set math=math+30 order by chinese+math+english desc limit 3; Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> select chinese, math, english, chinese+math+english as total from exam_result order by chinese+math+english; # 给前三名的数学加上 30 分，注意 MySQL 不支持 += +---------+------+---------+-------+ | chinese | math | english | total | +---------+------+---------+-------+ | 75 | 65 | 30 | 170 | | 55 | 85 | 45 | 185 | | 67 | 98 | 56 | 221 | | 70 | 73 | 78 | 221 | | 82 | 114 | 67 | 263 | | 87 | 108 | 77 | 272 | | 88 | 128 | 90 | 306 | +---------+------+---------+-------+ 7 rows in set (0.00 sec) mysql> update exam_result set chinese=chinese*2, math=math*2, english=engli sh*2; Query OK, 7 rows affected (0.00 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> select * from exam_result; +----+-------+---------+------+---------+ | id | name | chinese | math | english | +----+-------+---------+------+---------+ | 1 | limou | 134 | 196 | 112 | | 2 | swk | 174 | 216 | 154 | | 3 | zwn | 176 | 256 | 180 | | 4 | cmd | 164 | 228 | 134 | | 5 | lxd | 110 | 170 | 90 | | 6 | sq | 140 | 146 | 156 | | 7 | sgm | 150 | 130 | 60 | +----+-------+---------+------+---------+ 7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
4.数据库原理
4.1.MySQL 数据库基本原理
根据文件的变化我们可以知道，对数据库的操作可以屏蔽底层的细节操作，程序员只需要通过客户端，就可以对数据进行增删查改，剩下的所有工作都交给数据库来处理。
我们还需要了解 C/S 之间的联系：
- 安装数据库，就是在机器上安装一个数据库管理系统软件（在后台以“守护进程”的模式运行），该程序可以管理多个数据库，一般一个应用使用一个数据库
- 为了保存应用中实体的数据，一般会在数据库中创建多个数据表
- 在逻辑结构上，数据库内的数据以二维表结构来存储数据（物理结构上不一定）。
4.2.MySQL 数据库基本架构
MySQL 是可以移植的库，可以在任意操作系统上运行（主流场景是 Linux），对于一个 MySQL 服务端来说，主要有下面的模块组成：
补充：这里的存储引擎有点类似于驱动程序，不同的存储引擎接受上层传达下来的经过转化的
SQL语句，然后进行解释做对应的操作。也就是说，“存储引擎是数据库管理系统如何存储数据、如何为存储数据建立索引、如何更新数据、如如何查询数据等技术的具体实现方法”。而针对不同的数据会采用不同的存储引擎方案，这在效率上有明显的差别（而
MySQL的核心就是插件式存储引擎，支持多种存储引擎）。我们可以通过语句来查看数据库中存储引擎
# 打印所有的存储引擎信息 mysql> show enginess; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'enginess' at line 1 mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
这样打印可能屏幕空间不够，可以使用
\G选项辅助打印# 使用 `\G` 选项辅助打印所有的存储引擎信息 mysql> show engines \G *************************** 1. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 5. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO *************************** 8. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO *************************** 9. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL 9 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
大部分情况下，常用的存储引擎也就两个：
InnoDB和MyISAM，并且前者比后者更加常用，我们以后再深入研究。
5.SQL 语句分类
- DDL 数据定义语句：用于维护存储数据的结构（建表、修改表结构…）
- DML 数据操作语句：用于数据操作（插入数据、删除数据…）
- DCL 数据控制语句：用于数据控制，主要是用于权限管理、事务管理（赋予权限、取消权限）
补充：需要及其注意的是，
MySQL的语句具有执行顺序，有时候会导致一些意外结果。# 查验语句顺序 mysql> select * from exam_result; +----+------+---------+------+---------+ | id | name | chinese | math | english | +----+------+---------+------+---------+ | 1 | tsz | 67 | 98 | 56 | | 2 | swk | 87 | 78 | 77 | | 3 | zwn | 88 | 98 | 90 | | 4 | cmd | 82 | 84 | 67 | | 5 | lxd | 55 | 85 | 45 | | 6 | sq | 70 | 73 | 78 | | 7 | sgm | 75 | 65 | 30 | +----+------+---------+------+---------+ 7 rows in set (0.00 sec) mysql> select chinese+math from exam_result where chinese+math>150; +--------------+ | chinese+math | +--------------+ | 165 | | 165 | | 186 | | 166 | +--------------+ 4 rows in set (0.00 sec) mysql> select chinese+math as total from exam_result where chinese+math>150; +-------+ | total | +-------+ | 165 | | 165 | | 186 | | 166 | +-------+ 4 rows in set (0.00 sec) mysql> select chinese+math as total from exam_result where total>150; ERROR 1054 (42S22): Unknown column 'total' in 'where clause' # 实际执行顺序为： # (1)from exam_result # (2)total<150 # (3)select chinese+math as total
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
根据执行顺序，还没重命名就执行
total<150，当然是失败了，因此在这个版本中的MySQL做不到这样的操作（语法上不支持）。而排序一般是对显示结果排序，如果还要加上排序，那重命名是可以成功的。
# 排序结果的执行顺序 mysql> select name, chinese, math, english, chinese+math+english as total f rom exam_result order by total asc; +------+---------+------+---------+-------+ | name | chinese | math | english | total | +------+---------+------+---------+-------+ | sgm | 75 | 65 | 30 | 170 | | lxd | 55 | 85 | 45 | 185 | | tsz | 67 | 98 | 56 | 221 | | sq | 70 | 73 | 78 | 221 | | cmd | 82 | 84 | 67 | 233 | | swk | 87 | 78 | 77 | 242 | | zwn | 88 | 98 | 90 | 276 | +------+---------+------+---------+-------+ 7 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家自动化/article/detail/151796
推荐阅读
相关标签
