欢迎各位兄弟 发布技术文章

这里的技术是共享的

You are here

马哥 33_02 _MySQL系列之六——MySQL管理表和索引 有大用

SQL语句

        数据库

        表

        索引

        视图

        DML


[root@mail ~]# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 23

Server version: 5.5.28-log Source distribution


Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> help create database;  # help 加 命令;获取帮助信息  创建数据库

Name: 'CREATE DATABASE'

Description:

Syntax:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

        # SCHEMA 一个方案;类似于一个项目,可能包含了数据库,数据,用户,账号,用户的权限指派等相关信息

    [create_specification] ...

      #   create_specification  指定额外的选项;


create_specification:

    [DEFAULT] CHARACTER SET [=] charset_name

  | [DEFAULT] COLLATE [=] collation_name

    # default 是可以省略的

    # = 等于号也可以省略的


CREATE DATABASE creates a database with the given name. To use this

statement, you need the CREATE privilege for the database. CREATE

SCHEMA is a synonym for CREATE DATABASE.


URL: http://dev.mysql.com/doc/refman/5.5/en/create-database.html



mysql> 


数据库

        创建:

                CREATE DATABASE | SCHEMA [IF NOT EXISTS] db_name  [CHARACTER SET=] [COLLATE=] 

        修改:

                ALTER DATABASE ..................

        删除:

                DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

                只有InnoDB才支持外键,

                 

数据库改名,,把服务器停了,把数据库的数据目录下的数据库的文件夹的名字改了就可以了,再重启数据库服务器;;;但是一般不这么做.        





[root@mail ~]# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 24

Server version: 5.5.28-log Source distribution


Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> 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>

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_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_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 |

| 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 |

| 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 |

| 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 |

+--------------------------+----------+-----+---------+----------+---------+

197 rows in set (0.00 sec)


mysql>

mysql> create schema if not exists students character set 'gbk' collate 'gbk_chinese_ci';

Query OK, 1 row affected (0.02 sec)


mysql>

mysql> help alter database;

Name: 'ALTER DATABASE'

Description:

Syntax:

ALTER {DATABASE | SCHEMA} [db_name]

    alter_specification ...

ALTER {DATABASE | SCHEMA} db_name

    UPGRADE DATA DIRECTORY NAME  # UPGRADE DATA DIRECTORY NAME 升级数据字典名称?;;比如数据库从老版本服务器到新版本服务器?  怎么感觉像是升级目录名称


alter_specification:

    [DEFAULT] CHARACTER SET [=] charset_name     # 可修改的,一般只有字符集 和 排序规则

  | [DEFAULT] COLLATE [=] collation_name #


ALTER DATABASE enables you to change the overall characteristics of a

database. These characteristics are stored in the db.opt file in the

database directory. To use ALTER DATABASE, you need the ALTER privilege

on the database. ALTER SCHEMA is a synonym for ALTER DATABASE.


The database name can be omitted from the first syntax, in which case

the statement applies to the default database.


National Language Characteristics


The CHARACTER SET clause changes the default database character set.

The COLLATE clause changes the default database collation.

http://dev.mysql.com/doc/refman/5.5/en/charset.html, discusses

character set and collation names.


You can see what character sets and collations are available using,

respectively, the SHOW CHARACTER SET and SHOW COLLATION statements. See

[HELP SHOW CHARACTER SET], and [HELP SHOW COLLATION], for more

information.


If you change the default character set or collation for a database,

stored routines that use the database defaults must be dropped and

recreated so that they use the new defaults. (In a stored routine,

variables with character data types use the database defaults if the

character set or collation are not specified explicitly. See [HELP

CREATE PROCEDURE].)


Upgrading from Versions Older than MySQL 5.1


The syntax that includes the UPGRADE DATA DIRECTORY NAME clause updates

the name of the directory associated with the database to use the

encoding implemented in MySQL 5.1 for mapping database names to

database directory names (see

http://dev.mysql.com/doc/refman/5.5/en/identifier-mapping.html). This

clause is for use under these conditions:


o It is intended when upgrading MySQL to 5.1 or later from older

  versions.


o It is intended to update a database directory name to the current

  encoding format if the name contains special characters that need

  encoding.


o The statement is used by mysqlcheck (as invoked by mysql_upgrade).


For example, if a database in MySQL 5.0 has the name a-b-c, the name

contains instances of the - (dash) character. In MySQL 5.0, the

database directory is also named a-b-c, which is not necessarily safe

for all file systems. In MySQL 5.1 and later, the same database name is

encoded as a@002db@002dc to produce a file system-neutral directory

name.


When a MySQL installation is upgraded to MySQL 5.1 or later from an

older version,the server displays a name such as a-b-c (which is in the

old format) as #mysql50#a-b-c, and you must refer to the name using the

#mysql50# prefix. Use UPGRADE DATA DIRECTORY NAME in this case to

explicitly tell the server to re-encode the database directory name to

the current encoding format:


ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;


After executing this statement, you can refer to the database as a-b-c

without the special #mysql50# prefix.


URL: http://dev.mysql.com/doc/refman/5.5/en/alter-database.html



mysql>

mysql> help drop database;

Name: 'DROP DATABASE'

Description:

Syntax:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name


DROP DATABASE drops all tables in the database and deletes the

database. Be very careful with this statement! To use DROP DATABASE,

you need the DROP privilege on the database. DROP SCHEMA is a synonym

for DROP DATABASE.


*Important*: When a database is dropped, user privileges on the

database are not automatically dropped. See [HELP GRANT].


IF EXISTS is used to prevent an error from occurring if the database

does not exist.


URL: http://dev.mysql.com/doc/refman/5.5/en/drop-database.html



mysql>



[root@mail ~]# cd /mydata/data

[root@mail data]# ls

hellodb              mysql-bin.000002  mysql-bin.000011  mysql-bin.000020

ibdata1              mysql-bin.000003  mysql-bin.000012  mysql-bin.000021

ib_logfile0          mysql-bin.000004  mysql-bin.000013  mysql-bin.index

ib_logfile1          mysql-bin.000005  mysql-bin.000014  performance_schema

mail.magedu.com.err  mysql-bin.000006  mysql-bin.000015  students

mail.magedu.com.pid  mysql-bin.000007  mysql-bin.000016  test

mydb                 mysql-bin.000008  mysql-bin.000017  testdb

mysql                mysql-bin.000009  mysql-bin.000018

mysql-bin.000001     mysql-bin.000010  mysql-bin.000019

[root@mail data]#

[root@mail data]# cd students/

[root@mail students]# ls

db.opt

[root@mail students]#



[root@mail students]# file db.opt

db.opt: ASCII text

[root@mail students]#


[root@mail students]# file db.opt    # 查看文件的类型,是 ascii 文本文件

db.opt: ASCII text

[root@mail students]# cat db.opt  # 当前数据库的默认的字符集和排序规则

default-character-set=gbk

default-collation=gbk_chinese_ci

[root@mail students]#



表格:

        创建:

                    1.直接定义一张空表

                            create table [if not exists] tb_name(col_name col_defination, ........constraint )

                    2.从其它表中查询出数据,并以之创建新表

                            例子:create table testcourses select * from courses where cid<=2;   

                    3.以其它表为模板创建一个空表

                            例子: create table test like courses;

        

任何一个自动增长的字段必须为主键?             primary key 这个key是可以省略的?

    例子:    create table tb1 (id int unsigned not null auto_increment  primary key, name char(20) not null,age tinyint not null)

                create table tb2 (id int unsigned not null auto_increment  , name char(20) not null,age tinyint not null , primary key(id))

                create table tb (id int unsigned not null auto_increment  , name char(20) not null,age tinyint not null , primary key(id,name))    #两个字段作为主键

                create table tb3 (id int unsigned not null auto_increment  , name char(20) not null,age tinyint not null , primary key(id),unique key(name),index (age))

                 create table tb1 (id int unsigned not null auto_increment  primary key, name char(20) not null,age tinyint not null)  Engine = engine_name #默认指定表的存储引擎,不指定的话,从数据库继承


单字段:  (直接定义在属性后面)

        primary key

        unique key

单字段或多字段:  (用逗号隔开,与字段并列单独定义的)

        primary key (col,.....)

        unique key (col,.....)

        index (col,.....)


键也称作约束,可用作索引,也是索引,只是一种特殊索引( 有特殊限定,比如唯一性 ):        B+tree索引 




show indexes from tb_name : 显示指定表上的索引

                


        修改:

            修改表定义(表结构):

                    alter table

                            添加,删除,修改字段

                            添加,删除,修改索引

                            改表名

                            修改表属性                   

                

        删除:

外键约束在一定程度上会极大的消耗系统资源的,不建议使用外键约束



create index 创建索引

        索引的主要目的是为了比较 排序(尤其是B树索引)吧

索引:

        创建

                create index index_name on tb_name (col,......)

                                col_name [(length)] [ASC | DESC]

                                         length 表示 只比较多长的字符 (越长,消耗资源越多,越短,可能无法比较排序)

                                         索引会载入内存,length越长,占内存越大

                                         索引建在 text类型的字段上,必须要指定长度的,因为它不能对text作全部索引(最长是 100个? 255 个?)


         删除


         查看



索引是不能修改的 只能先删除,后创建, 索引是表自动维护的一个数据结构,所以不能修改




[root@mail ~]# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 25

Server version: 5.5.28-log Source distribution


Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> help create table;

Name: 'CREATE TABLE'

Description:

Syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    (create_definition,...)

    [table_options]

    [partition_options]


Or:


CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    [(create_definition,...)]

    [table_options]

    [partition_options]

    select_statement


Or:


CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    { LIKE old_tbl_name | (LIKE old_tbl_name) }


create_definition:

    col_name column_definition

  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)     #主键定义在哪个字段上

      [index_option] ...

  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)

      [index_option] ...

  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]  # unique 定义唯一键索引     KEY可以省略

      [index_name] [index_type] (index_col_name,...)

      [index_option] ...

  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)    #FULLTEXT 定义全文索引 SPATIAL定义空间索引

      [index_option] ...

  | [CONSTRAINT [symbol]] FOREIGN KEY        # FOREIGN KEY 外键索引

      [index_name] (index_col_name,...) reference_definition

  | CHECK (expr)        # CHECK 检查型约束


column_definition:  #最核心的是字段类型 (数据类型)

    data_type [NOT NULL | NULL] [DEFAULT default_value]  

      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]     #主键即是唯一,又不能为空,,,唯一键跟主键一样,但可以为空    一表只有一个主键,可以有多个唯一键

      [COMMENT 'string']

      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]  # 定长,变长,默认

      [STORAGE {DISK|MEMORY|DEFAULT}] #存储位置:(磁盘,内存,默认?)

      [reference_definition]  #引用 (可译作参考)


data_type:

    BIT[(length)]

  | TINYINT[(length)] [UNSIGNED] [ZEROFILL]

  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]

  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]

  | INT[(length)] [UNSIGNED] [ZEROFILL]

  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]

  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]

  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]

  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]

  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]

  | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]

  | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]

  | DATE

  | TIME

  | TIMESTAMP

  | DATETIME

  | YEAR

  | CHAR[(length)]

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | VARCHAR(length)

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | BINARY[(length)]

  | VARBINARY(length)

  | TINYBLOB

  | BLOB

  | MEDIUMBLOB

  | LONGBLOB

  | TINYTEXT [BINARY]

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | TEXT [BINARY]

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | MEDIUMTEXT [BINARY]

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | LONGTEXT [BINARY]

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | ENUM(value1,value2,value3,...)

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | SET(value1,value2,value3,...)

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | spatial_type


index_col_name:

    col_name [(length)] [ASC | DESC]


index_type:    # 索引类型两种: BTREE(几乎所有数据库,所有字段都支持 )  HASH (未必都支持)

    USING {BTREE | HASH}


index_option:

    KEY_BLOCK_SIZE [=] value

  | index_type

  | WITH PARSER parser_name

  | COMMENT 'string'


reference_definition:

    REFERENCES tbl_name (index_col_name,...)    # 引用哪张表的哪个字段

      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]  # MATCH FULL 完全匹配;;# MATCH PARTIAL 部分匹配;;#MATCH SIMPLE 简单匹配

      [ON DELETE reference_option]    # 人家表删了,我咋办

      [ON UPDATE reference_option]   # 人家表改了,我咋办


reference_option:

    RESTRICT(不允许人家改?严格模式) | CASCADE (级联,跟着人家变动)| SET NULL (人家改了,设为空)| NO ACTION(人家改了,这边没动作)


table_options:

    table_option [[,] table_option] ...


table_option:  #表选项

    ENGINE [=] engine_name

  | AUTO_INCREMENT [=] value    # 自动增长开始值

  | AVG_ROW_LENGTH [=] value    # 平均行的长度?

  | [DEFAULT] CHARACTER SET [=] charset_name    #字符集

  | CHECKSUM [=] {0 | 1}    #是否启有校验核,每次更新表,都重新计算校验核

  | [DEFAULT] COLLATE [=] collation_name    #排序规则

  | COMMENT [=] 'string'    #表注释

  | CONNECTION [=] 'connect_string'

  | DATA DIRECTORY [=] 'absolute path to directory'    # 数据目录

  | DELAY_KEY_WRITE [=] {0 | 1}     #是否延迟键写入

  | INDEX DIRECTORY [=] 'absolute path to directory'    #索引目录

  | INSERT_METHOD [=] { NO | FIRST | LAST }    #插入方法

  | KEY_BLOCK_SIZE [=] value    #键块的大小

  | MAX_ROWS [=] value    #最多允许存储多少行

  | MIN_ROWS [=] value

  | PACK_KEYS [=] {0 | 1 | DEFAULT}

  | PASSWORD [=] 'string'

  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}    # 行格式

  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]    #表空间,若存储引擎支持表空间,明确指定存在哪个表空间当中

  | UNION [=] (tbl_name[,tbl_name]...)


partition_options:     #分区选项

    PARTITION BY

        { [LINEAR] HASH(expr)

        | [LINEAR] KEY(column_list)

        | RANGE{(expr) | COLUMNS(column_list)}

        | LIST{(expr) | COLUMNS(column_list)} }

    [PARTITIONS num]

    [SUBPARTITION BY

        { [LINEAR] HASH(expr)

        | [LINEAR] KEY(column_list) }

      [SUBPARTITIONS num]

    ]

    [(partition_definition [, partition_definition] ...)]


partition_definition:    #分区定义

    PARTITION partition_name

        [VALUES

            {LESS THAN {(expr | value_list) | MAXVALUE}

            |

            IN (value_list)}]

        [[STORAGE] ENGINE [=] engine_name]

        [COMMENT [=] 'comment_text' ]

        [DATA DIRECTORY [=] 'data_dir']

        [INDEX DIRECTORY [=] 'index_dir']

        [MAX_ROWS [=] max_number_of_rows]

        [MIN_ROWS [=] min_number_of_rows]

        [TABLESPACE [=] tablespace_name]

        [NODEGROUP [=] node_group_id]

        [(subpartition_definition [, subpartition_definition] ...)]


subpartition_definition:    #子分区定义

    SUBPARTITION logical_name

        [[STORAGE] ENGINE [=] engine_name]

        [COMMENT [=] 'comment_text' ]

        [DATA DIRECTORY [=] 'data_dir']

        [INDEX DIRECTORY [=] 'index_dir']

        [MAX_ROWS [=] max_number_of_rows]

        [MIN_ROWS [=] min_number_of_rows]

        [TABLESPACE [=] tablespace_name]

        [NODEGROUP [=] node_group_id]


select_statement:

    [IGNORE | REPLACE] [AS] SELECT ...   (Some valid select statement)


CREATE TABLE creates a table with the given name. You must have the

CREATE privilege for the table.


Rules for permissible table names are given in

http://dev.mysql.com/doc/refman/5.5/en/identifiers.html. By default,

the table is created in the default database, using the InnoDB storage

engine. An error occurs if the table exists, if there is no default

database, or if the database does not exist.


URL: http://dev.mysql.com/doc/refman/5.5/en/create-table.html



mysql>






[root@mail ~]# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 28

Server version: 5.5.28-log Source distribution


Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> use students;

Database changed

mysql> create table courses (cid tinyint unsigned not null auto_increment primary key,course varchar(50) not null);

Query OK, 0 rows affected (0.32 sec)


mysql>

mysql> show table status like 'courses';

+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+----------------+----------+----------------+---------+

| Name    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation      | Checksum | Create_options | Comment |

+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+----------------+----------+----------------+---------+

| courses | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2020-09-24 16:06:34 | NULL        | NULL       | gbk_chinese_ci |     NULL |                |         |

+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+----------------+----------+----------------+---------+

1 row in set (0.00 sec)


mysql>

mysql> show table status like 'courses'\G

*************************** 1. row ***************************

           Name: courses

         Engine: InnoDB

        Version: 10

     Row_format: Compact    #一种紧凑,压缩的格式

           Rows: 0

 Avg_row_length: 0

    Data_length: 16384        # 当前表的长度

Max_data_length: 0

   Index_length: 0

      Data_free: 0

 Auto_increment: 1

    Create_time: 2020-09-24 16:06:34    #创建时间

    Update_time: NULL    #更新时间

     Check_time: NULL      #检查时间

      Collation: gbk_chinese_ci        #排序规则,是从数据库继承的

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.00 sec)


mysql>

mysql> drop table courses;        # 先把它删了

Query OK, 0 rows affected (0.08 sec)


mysql>

mysql> create table courses (cid tinyint unsigned not null auto_increment primary key,course varchar(50) not null) engine=MyISAM;    # 这里指定了存储引擎

Query OK, 0 rows affected (0.03 sec)


mysql>


mysql> show table status like 'courses'\G

*************************** 1. row ***************************

           Name: courses

         Engine: MyISAM    #存储引擎,是 MyISAM    

        Version: 10

     Row_format: Dynamic    #行格式就改成了 Dynamic 动态的    

           Rows: 0

 Avg_row_length: 0

    Data_length: 0

Max_data_length: 281474976710655        # Max_data_length 也有变化

   Index_length: 1024

      Data_free: 0

 Auto_increment: 1

    Create_time: 2020-09-24 16:27:10

    Update_time: 2020-09-24 16:27:10

     Check_time: NULL

      Collation: gbk_chinese_ci

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.00 sec)


mysql>


mysql> insert into courses (Course) values ('Hamogong'),('Pixiejianfa'),('Kuihuabaodian');

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0


mysql>


mysql> select * from courses;

+-----+---------------+

| cid | course        |

+-----+---------------+

|   1 | Hamogong      |

|   2 | Pixiejianfa   |

|   3 | Kuihuabaodian |

+-----+---------------+

3 rows in set (0.00 sec)


mysql>

mysql> show index from courses;                # 显示表上的索引

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| courses |          0 | PRIMARY  |            1 | cid         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)


mysql>

mysql> show indexes from courses;          # indexes 或 index 都能吧

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table   | Non_unique (是不是 非唯一键) | Key_name (键名称)  | Seq_in_index (表上的第几个索引 在索引的中序列) | Column_name (索引所在的字段) | Collation (排序规则) | Cardinality | Sub_part | Packed | Null | Index_type(索引类型) | Comment | Index_comment |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| courses |          0 | PRIMARY  |            1 | cid         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)


mysql>

mysql> create table testcourses select * from courses where cid<=2;        # 以 select 原表 来创建一个新表

Query OK, 2 rows affected (0.08 sec)

Records: 2  Duplicates: 0  Warnings: 0


mysql>


mysql> show tables;

+--------------------+

| Tables_in_students |

+--------------------+

| courses            |

| testcourses        |

+--------------------+

2 rows in set (0.00 sec)


mysql>

mysql> select * from testcourses;

+-----+-------------+

| cid | course      |

+-----+-------------+

|   1 | Hamogong    |

|   2 | Pixiejianfa |

+-----+-------------+

2 rows in set (0.01 sec)


mysql>


mysql> desc courses;    # desc 显示表结构

+--------+---------------------+------+-----+---------+----------------+

| Field  | Type                | Null | Key | Default | Extra          |

+--------+---------------------+------+-----+---------+----------------+

| cid    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |

| course | varchar(50)         | NO   |     | NULL    |                |

+--------+---------------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)


mysql> desc testcourses; # desc 显示表结构  两个表结构是不完全一样的

+--------+---------------------+------+-----+---------+-------+

| Field  | Type                | Null | Key | Default | Extra |

+--------+---------------------+------+-----+---------+-------+

| cid    | tinyint(3) unsigned | NO   |     | 0       |       |

| course | varchar(50)         | NO   |     | NULL    |       |

+--------+---------------------+------+-----+---------+-------+

2 rows in set (0.00 sec)


mysql>


mysql> create table test like courses;    # 以 courses 表为模板创建一张空表test

Query OK, 0 rows affected (0.02 sec)


mysql>

mysql> desc test;        # 结构与 courses 表一样

+--------+---------------------+------+-----+---------+----------------+

| Field  | Type                | Null | Key | Default | Extra          |

+--------+---------------------+------+-----+---------+----------------+

| cid    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |

| course | varchar(50)         | NO   |     | NULL    |                |

+--------+---------------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)


mysql>


# 既要格式,又要数据:我们先仿结构弄张空表,再查询数据,导入到这个空表(后面 讲到 insert 命令时会提到)




[root@mail ~]# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 30

Server version: 5.5.28-log Source distribution


Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> help alter table

Name: 'ALTER TABLE'

Description:

Syntax:

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name

    [alter_specification [, alter_specification] ...]

    [partition_options]


alter_specification:

    table_options

  | ADD [COLUMN] col_name column_definition    #添加字段

        [FIRST | AFTER col_name ]    #字段添加后的位置

  | ADD [COLUMN] (col_name column_definition,...)

  | ADD {INDEX|KEY} [index_name]        #添加索引的

        [index_type] (index_col_name,...) [index_option] ...

  | ADD [CONSTRAINT [symbol]] PRIMARY KEY        #添加主键

        [index_type] (index_col_name,...) [index_option] ...

  | ADD [CONSTRAINT [symbol]]

        UNIQUE [INDEX|KEY] [index_name]        #添加唯一键

        [index_type] (index_col_name,...) [index_option] ...

  | ADD FULLTEXT [INDEX|KEY] [index_name]        #添加全文索引

        (index_col_name,...) [index_option] ...

  | ADD SPATIAL [INDEX|KEY] [index_name]      #添加空间索引

        (index_col_name,...) [index_option] ...

  | ADD [CONSTRAINT [symbol]]      #添加外键 

        FOREIGN KEY [index_name] (index_col_name,...)  # 外键约束 在哪个字段

        reference_definition    # 引 

  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

  | CHANGE [COLUMN] old_col_name new_col_name column_definition  # 可改字段名及定义,位置

        [FIRST|AFTER col_name]

  | MODIFY [COLUMN] col_name column_definition    #只能改字段定义,位置

        [FIRST | AFTER col_name]

  | DROP [COLUMN] col_name

  | DROP PRIMARY KEY

  | DROP {INDEX|KEY} index_name

  | DROP FOREIGN KEY fk_symbol

  | MAX_ROWS = rows

  | DISABLE KEYS

  | ENABLE KEYS

  | RENAME [TO|AS] new_tbl_name

  | ORDER BY col_name [, col_name] ...

  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] #改变字符集,改变排序规则

  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]

  | DISCARD TABLESPACE

  | IMPORT TABLESPACE

  | FORCE

  | ADD PARTITION (partition_definition)

  | DROP PARTITION partition_names

  | TRUNCATE PARTITION {partition_names | ALL}

  | COALESCE PARTITION number

  | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]

  | ANALYZE PARTITION {partition_names | ALL}

  | CHECK PARTITION {partition_names | ALL}

  | OPTIMIZE PARTITION {partition_names | ALL}

  | REBUILD PARTITION {partition_names | ALL}

  | REPAIR PARTITION {partition_names | ALL}

  | PARTITION BY partitioning_expression

  | REMOVE PARTITIONING


index_col_name:

    col_name [(length)] [ASC | DESC]


index_type:

    USING {BTREE | HASH}


index_option:

    KEY_BLOCK_SIZE [=] value

  | index_type

  | WITH PARSER parser_name

  | COMMENT 'string'


table_options:

    table_option [[,] table_option] ...  (see CREATE TABLE options)


partition_options:

    (see CREATE TABLE options)


ALTER TABLE changes the structure of a table. For example, you can add

or delete columns, create or destroy indexes, change the type of

existing columns, or rename columns or the table itself. You can also

change characteristics such as the storage engine used for the table or

the table comment.


Partitioning-related clauses for ALTER TABLE can be used with

partitioned tables for repartitioning, for adding, dropping, merging,

and splitting partitions, and for performing partitioning maintenance.

For more information, see

http://dev.mysql.com/doc/refman/5.5/en/alter-table-partition-operations

.html.


Following the table name, specify the alterations to be made. If none

are given, ALTER TABLE does nothing.


URL: http://dev.mysql.com/doc/refman/5.5/en/alter-table.html



mysql>




[root@mail ~]# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 31

Server version: 5.5.28-log Source distribution


Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> 

mysql> use students;

Database changed

mysql> 

mysql> desc test ;  # 查看表结构

+--------+---------------------+------+-----+---------+----------------+

| Field  | Type                | Null | Key | Default | Extra          |

+--------+---------------------+------+-----+---------+----------------+

| cid    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |

| course | varchar(50)         | NO   |     | NULL    |                |

+--------+---------------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)


mysql>

mysql> show indexes from test;            # 看看这张表的索引

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| test  |          0 | PRIMARY  |            1 | cid         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)


mysql>


mysql> show indexes from test\G

*************************** 1. row ***************************

        Table: test

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: cid

    Collation: A

  Cardinality: 0

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

1 row in set (0.00 sec)


mysql>



mysql> alter table test add unique key (course);        #给表添加唯一键索引

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql>


mysql> show indexes from test\G        #看到了 course 列的上唯一键索引

*************************** 1. row ***************************

        Table: test

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: cid

    Collation: A

  Cardinality: 0

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

*************************** 2. row ***************************

        Table: test

   Non_unique: 0

     Key_name: course

 Seq_in_index: 1

  Column_name: course

    Collation: A

  Cardinality: 0

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

2 rows in set (0.00 sec)


mysql>


mysql> alter table test change course couse varchar(50) not null; # 修改字段名,后面要加字段修饰符

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0



mysql> alter table test change couse course varchar(50) not null;# 修改字段名,后面要加字段修饰符

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql>

mysql> desc test;

+--------+---------------------+------+-----+---------+----------------+

| Field  | Type                | Null | Key | Default | Extra          |

+--------+---------------------+------+-----+---------+----------------+

| cid    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |

| course | varchar(50)         | NO   | UNI | NULL    |                |

+--------+---------------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)


mysql>


mysql> alter table test add starttime datetime default '2013-004-12';    #增加字段

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql>


mysql> desc test;

+-----------+---------------------+------+-----+---------------------+----------------+

| Field     | Type                | Null | Key | Default             | Extra          |

+-----------+---------------------+------+-----+---------------------+----------------+

| cid       | tinyint(3) unsigned | NO   | PRI | NULL                | auto_increment |

| course    | varchar(50)         | YES  | UNI | NULL                |                |

| starttime | datetime            | YES (不设null或not null 时,默认就是yes) |     | 2013-04-12 00:00:00 |                |

+-----------+---------------------+------+-----+---------------------+----------------+

3 rows in set (0.00 sec)


mysql>

mysql> help alter table

Name: 'ALTER TABLE'

Description:

Syntax:

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name

    [alter_specification [, alter_specification] ...]

    [partition_options]


alter_specification:

    table_options

  | ADD [COLUMN] col_name column_definition

        [FIRST | AFTER col_name ]

  | ADD [COLUMN] (col_name column_definition,...)

  | ADD {INDEX|KEY} [index_name]

        [index_type] (index_col_name,...) [index_option] ...

  | ADD [CONSTRAINT [symbol]] PRIMARY KEY

        [index_type] (index_col_name,...) [index_option] ...

  | ADD [CONSTRAINT [symbol]]

        UNIQUE [INDEX|KEY] [index_name]

        [index_type] (index_col_name,...) [index_option] ...

  | ADD FULLTEXT [INDEX|KEY] [index_name]

        (index_col_name,...) [index_option] ...

  | ADD SPATIAL [INDEX|KEY] [index_name]

        (index_col_name,...) [index_option] ...

  | ADD [CONSTRAINT [symbol]]

        FOREIGN KEY [index_name] (index_col_name,...)

        reference_definition

  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

  | CHANGE [COLUMN] old_col_name new_col_name column_definition

        [FIRST|AFTER col_name]

  | MODIFY [COLUMN] col_name column_definition

        [FIRST | AFTER col_name]

  | DROP [COLUMN] col_name

  | DROP PRIMARY KEY

  | DROP {INDEX|KEY} index_name

  | DROP FOREIGN KEY fk_symbol

  | MAX_ROWS = rows

  | DISABLE KEYS

  | ENABLE KEYS

  | RENAME [TO|AS] new_tbl_name        # 表改名

  | ORDER BY col_name [, col_name] ...

  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]

  | DISCARD TABLESPACE

  | IMPORT TABLESPACE

  | FORCE

  | ADD PARTITION (partition_definition)

  | DROP PARTITION partition_names

  | TRUNCATE PARTITION {partition_names | ALL}

  | COALESCE PARTITION number

  | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]

  | ANALYZE PARTITION {partition_names | ALL}

  | CHECK PARTITION {partition_names | ALL}

  | OPTIMIZE PARTITION {partition_names | ALL}

  | REBUILD PARTITION {partition_names | ALL}

  | REPAIR PARTITION {partition_names | ALL}

  | PARTITION BY partitioning_expression

  | REMOVE PARTITIONING


index_col_name:

    col_name [(length)] [ASC | DESC]


index_type:

    USING {BTREE | HASH}


index_option:

    KEY_BLOCK_SIZE [=] value

  | index_type

  | WITH PARSER parser_name

  | COMMENT 'string'


table_options:

    table_option [[,] table_option] ...  (see CREATE TABLE options)


partition_options:

    (see CREATE TABLE options)


ALTER TABLE changes the structure of a table. For example, you can add

or delete columns, create or destroy indexes, change the type of

existing columns, or rename columns or the table itself. You can also

change characteristics such as the storage engine used for the table or

the table comment.


Partitioning-related clauses for ALTER TABLE can be used with

partitioned tables for repartitioning, for adding, dropping, merging,

and splitting partitions, and for performing partitioning maintenance.

For more information, see

http://dev.mysql.com/doc/refman/5.5/en/alter-table-partition-operations

.html.


Following the table name, specify the alterations to be made. If none

are given, ALTER TABLE does nothing.


URL: http://dev.mysql.com/doc/refman/5.5/en/alter-table.html



mysql>


mysql> show tables;

+--------------------+

| Tables_in_students |

+--------------------+

| courses            |

| test               |

| testcourses        |

+--------------------+

3 rows in set (0.00 sec)


mysql>


mysql> drop tables testcourses;      ( # drop table testcourses; 这个命令也可以 )  # 删除表 testcourses

Query OK, 0 rows affected (0.04 sec)


mysql>


mysql> alter table test rename to testcourses;        # 改表名

Query OK, 0 rows affected (0.00 sec)


mysql>

mysql> show tables;

+--------------------+

| Tables_in_students |

+--------------------+

| courses            |

| testcourses        |

+--------------------+

2 rows in set (0.00 sec)


mysql>


mysql> help rename       

Many help items for your request exist.

To make a more specific request, please type 'help <item>',

where <item> is one of the following

topics:

   ALTER EVENT

   ALTER TABLE

   RENAME TABLE

   RENAME USER


mysql>

mysql> help rename table         # rename 命令可以直接改表名

Name: 'RENAME TABLE'

Description:

Syntax:

RENAME TABLE tbl_name TO new_tbl_name

    [, tbl_name2 TO new_tbl_name2] ...


This statement renames one or more tables.


The rename operation is done atomically, which means that no other

session can access any of the tables while the rename is running. For

example, if you have an existing table old_table, you can create

another table new_table that has the same structure but is empty, and

then replace the existing table with the empty one as follows (assuming

that backup_table does not already exist):


URL: http://dev.mysql.com/doc/refman/5.5/en/rename-table.html


Examples:

CREATE TABLE new_table (...);

RENAME TABLE old_table TO backup_table, new_table TO old_table;


mysql>


mysql> rename table testcourses to test;            # rename 也能够重命名表

Query OK, 0 rows affected (0.00 sec)


mysql>


mysql> show tables;

+--------------------+

| Tables_in_students |

+--------------------+

| courses            |

| test               |

+--------------------+

2 rows in set (0.00 sec)


mysql>


表重命名代价很大,一般是这样子的:以老表结构建一个新表,老表数据到新表,再删老表



mysql> help drop table;

Name: 'DROP TABLE'

Description:

Syntax:

DROP [TEMPORARY] TABLE [IF EXISTS]    #删除表

    tbl_name [, tbl_name] ...

    [RESTRICT | CASCADE]    # RESTRICT 严格模式;;;; CASCADE 级联,(删除引用表,被引用表也会删除,所以一般不用它)


DROP TABLE removes one or more tables. You must have the DROP privilege

for each table. All table data and the table definition are removed, so

be careful with this statement! If any of the tables named in the

argument list do not exist, MySQL returns an error indicating by name

which nonexisting tables it was unable to drop, but it also drops all

of the tables in the list that do exist.


*Important*: When a table is dropped, user privileges on the table are

not automatically dropped. See [HELP GRANT].


Note that for a partitioned table, DROP TABLE permanently removes the

table definition, all of its partitions, and all of the data which was

stored in those partitions. It also removes the partitioning definition

(.par) file associated with the dropped table.


Use IF EXISTS to prevent an error from occurring for tables that do not

exist. A NOTE is generated for each nonexistent table when using IF

EXISTS. See [HELP SHOW WARNINGS].


RESTRICT and CASCADE are permitted to make porting easier. In MySQL

5.5, they do nothing.


*Note*: DROP TABLE automatically commits the current active

transaction, unless you use the TEMPORARY keyword.


URL: http://dev.mysql.com/doc/refman/5.5/en/drop-table.html



mysql>

mysql> select database();

+------------+

| database() |

+------------+

| students   |

+------------+

1 row in set (0.00 sec)


mysql>

mysql> create table student ( sid int unsigned not null auto_increment primary key,name varchar(30),cid int not null );

Query OK, 0 rows affected (0.07 sec)


mysql>

mysql> insert into student (name,cid) values ('Yue Buqun',2),('Zhang Wuji',1);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0


mysql>

mysql> select * from student;

+-----+------------+-----+

| sid | name       | cid |

+-----+------------+-----+

|   1 | Yue Buqun  |   2 |

|   2 | Zhang Wuji |   1 |

+-----+------------+-----+

2 rows in set (0.00 sec)


mysql>


mysql> select * from courses;

+-----+---------------+

| cid | course        |

+-----+---------------+

|   1 | Hamogong      |

|   2 | Pixiejianfa   |

|   3 | Kuihuabaodian |

+-----+---------------+

3 rows in set (0.00 sec)


mysql>

mysql> select name,course from student,courses where student.cid=courses.cid;

+------------+-------------+

| name       | course      |

+------------+-------------+

| Yue Buqun  | Pixiejianfa |

| Zhang Wuji | Hamogong    |

+------------+-------------+

2 rows in set (0.00 sec)


mysql>


mysql> insert into student (name,cid) values ('Chen Jialuo',5);    #正常添加数据

Query OK, 1 row affected (0.00 sec)



mysql> delete from student where cid=5;        # 先删掉这行吧

Query OK, 1 row affected (0.00 sec)


mysql>


mysql> alter table student add foreign key (cid) references courses(cid);

ERROR 1005 (HY000): Can't create table 'students.#sql-5f4f_1f' (errno: 150)

mysql>

mysql> show table status\G

*************************** 1. row ***************************

           Name: courses

           Engine: MyISAM        #马哥讲MyISAM 不允许建外键(外键约束只能用在支持事务的存储引擎上) 所以把 courses 改为 InnoDB 的存储引擎

        Version: 10

     Row_format: Dynamic

           Rows: 3

 Avg_row_length: 20

    Data_length: 60

Max_data_length: 281474976710655

   Index_length: 2048

      Data_free: 0

 Auto_increment: 4

    Create_time: 2020-09-24 16:27:10

    Update_time: 2020-09-24 16:32:51

     Check_time: NULL

      Collation: gbk_chinese_ci

       Checksum: NULL

 Create_options:

        Comment:

*************************** 2. row ***************************

           Name: student

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 2

 Avg_row_length: 8192

    Data_length: 16384

Max_data_length: 0

   Index_length: 0

      Data_free: 0

 Auto_increment: 4

    Create_time: 2020-09-25 10:40:33

    Update_time: NULL

     Check_time: NULL

      Collation: gbk_chinese_ci

       Checksum: NULL

 Create_options:

        Comment:

*************************** 3. row ***************************

           Name: test

         Engine: MyISAM

        Version: 10

     Row_format: Dynamic

           Rows: 0

 Avg_row_length: 0

    Data_length: 0

Max_data_length: 281474976710655

   Index_length: 1024

      Data_free: 0

 Auto_increment: 1

    Create_time: 2020-09-25 09:43:19

    Update_time: 2020-09-25 09:43:19

     Check_time: NULL

      Collation: gbk_chinese_ci

       Checksum: NULL

 Create_options:

        Comment:

3 rows in set (0.00 sec)


mysql>



mysql> create table course like courses;        # 以 courses 建一个 空表 course,

Query OK, 0 rows affected (0.01 sec)


mysql>

mysql> show table status like 'course'\G        # course 的存储引擎仍然是 MyISAM

*************************** 1. row ***************************

           Name: course

         Engine: MyISAM

        Version: 10

     Row_format: Dynamic

           Rows: 0

 Avg_row_length: 0

    Data_length: 0

Max_data_length: 281474976710655

   Index_length: 1024

      Data_free: 0

 Auto_increment: 1

    Create_time: 2020-09-25 13:32:48

    Update_time: 2020-09-25 13:32:48

     Check_time: NULL

      Collation: gbk_chinese_ci

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.00 sec)


mysql>


mysql> drop table course;    # 先删掉 course 表

Query OK, 0 rows affected (0.00 sec)


mysql>


mysql> create table course like courses Engine=InnoDB;        # 没法改引擎

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 'Engine=InnoDB' at line 1

mysql>


mysql> alter table courses engine=innodb;        # 可以直接修改表的引擎吧;(意味着创建一个新表,导出老表数据到新表,删掉老表,,,这才是改表引擎,,改表名不是这样子弄的.)



mysql> show table status like 'courses'\G;

*************************** 1. row ***************************

           Name: courses

         Engine: InnoDB        # 现在是表引擎是 innoDB了

        Version: 10

     Row_format: Compact

           Rows: 3

 Avg_row_length: 5461

    Data_length: 16384

Max_data_length: 0

   Index_length: 0

      Data_free: 0

 Auto_increment: 4

    Create_time: 2020-09-25 13:45:38

    Update_time: NULL

     Check_time: NULL

      Collation: gbk_chinese_ci

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.00 sec)


ERROR:

No query specified


mysql>

mysql> alter table student add foreign key (cid) references courses(cid);

ERROR 1005 (HY000): Can't create table 'students.#sql-5f4f_1f' (errno: 150)

mysql>

mysql> alter table student modify cid tinyint unsigned not null;        # 修改下字符类型

Query OK, 2 rows affected (0.02 sec)

Records: 2  Duplicates: 0  Warnings: 0


mysql>


mysql> desc student;        # 看看 cid 字段类型 是否与 courses 的cid 一样

+-------+---------------------+------+-----+---------+----------------+

| Field | Type                | Null | Key | Default | Extra          |

+-------+---------------------+------+-----+---------+----------------+

| sid   | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |

| name  | varchar(30)         | YES  |     | NULL    |                |

| cid   | tinyint(3) unsigned | NO   |     | NULL    |                |

+-------+---------------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)


mysql> desc courses;

+--------+---------------------+------+-----+---------+----------------+

| Field  | Type                | Null | Key | Default | Extra          |

+--------+---------------------+------+-----+---------+----------------+

| cid    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |

| course | varchar(50)         | NO   |     | NULL    |                |

+--------+---------------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)


mysql>

mysql> alter table student add foreign key (cid) references courses(cid);        # 现在可以增加约束了

Query OK, 2 rows affected (0.09 sec)

Records: 2  Duplicates: 0  Warnings: 0

#  alter table student add foreign key  foreign_cid (cid) references courses(cid);   # 马哥这么用 那么外键名就是 foreign_cid 而不是如下默认的 Key_name 为 cid

mysql>


mysql> show indexes from student\G

*************************** 1. row ***************************

        Table: student

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: sid

    Collation: A

  Cardinality: 2

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

*************************** 2. row ***************************

        Table: student

   Non_unique: 1

     Key_name: cid        # 这就是外键索引名 cid

 Seq_in_index: 1

  Column_name: cid

    Collation: A

  Cardinality: 2

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

2 rows in set (0.00 sec)


mysql>


mysql> select * from courses;

+-----+---------------+

| cid | course        |

+-----+---------------+

|   1 | Hamogong      |

|   2 | Pixiejianfa   |

|   3 | Kuihuabaodian |

+-----+---------------+

3 rows in set (0.00 sec)


mysql>

mysql> insert into student (name,cid) value ('Chen Jialuo',5);        # 报外键约束错误了,因为 courses 表中 没有 cid 为5的值

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`students`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`))

mysql>

mysql> insert into student (name,cid) value ('Chen Jialuo',3);

Query OK, 1 row affected (0.01 sec)


mysql>


mysql> delete from courses where cid=3;    # 它不让你删,因为 外键有使有到 cid为3的值(students 表中的 cid 值有3)

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`students`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`))

mysql>


mysql> help delete        # 里面没有说明是如何产生级联删除的

Name: 'DELETE'

Description:

Syntax:

Single-table syntax:


DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

    [WHERE where_condition]

    [ORDER BY ...]

    [LIMIT row_count]


Multiple-table syntax:


DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

    tbl_name[.*] [, tbl_name[.*]] ...

    FROM table_references

    [WHERE where_condition]


Or:


DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

    FROM tbl_name[.*] [, tbl_name[.*]] ...

    USING table_references

    [WHERE where_condition]


For the single-table syntax, the DELETE statement deletes rows from

tbl_name and returns a count of the number of deleted rows. This count

can be obtained by calling the ROW_COUNT() function (see

http://dev.mysql.com/doc/refman/5.5/en/information-functions.html). The

WHERE clause, if given, specifies the conditions that identify which

rows to delete. With no WHERE clause, all rows are deleted. If the

ORDER BY clause is specified, the rows are deleted in the order that is

specified. The LIMIT clause places a limit on the number of rows that

can be deleted.


For the multiple-table syntax, DELETE deletes from each tbl_name the

rows that satisfy the conditions. In this case, ORDER BY and LIMIT

cannot be used.


where_condition is an expression that evaluates to true for each row to

be deleted. It is specified as described in

http://dev.mysql.com/doc/refman/5.5/en/select.html.


Currently, you cannot delete from a table and select from the same

table in a subquery.


You need the DELETE privilege on a table to delete rows from it. You

need only the SELECT privilege for any columns that are only read, such

as those named in the WHERE clause.


As stated, a DELETE statement with no WHERE clause deletes all rows. A

faster way to do this, when you do not need to know the number of

deleted rows, is to use TRUNCATE TABLE. However, within a transaction

or if you have a lock on the table, TRUNCATE TABLE cannot be used

whereas DELETE can. See [HELP TRUNCATE TABLE], and [HELP LOCK].


URL: http://dev.mysql.com/doc/refman/5.5/en/delete.html



mysql>


我们要想删除主表(被子表引用的表),,在子表(引用主表的表) 增加外键时,使用 CASCADE ,NO ACTION 等,就可以了

因为默认是RESTRICT,是不让人更改(删除)主表的



mysql> drop table courses;            # 也不让删,也是因为外键约束的原因

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

mysql>

mysql> help drop table;

Name: 'DROP TABLE'

Description:

Syntax:

DROP [TEMPORARY] TABLE [IF EXISTS]

    tbl_name [, tbl_name] ...

    [RESTRICT | CASCADE]    #删除表时,这里有严格模式,级联模式


DROP TABLE removes one or more tables. You must have the DROP privilege

for each table. All table data and the table definition are removed, so

be careful with this statement! If any of the tables named in the

argument list do not exist, MySQL returns an error indicating by name

which nonexisting tables it was unable to drop, but it also drops all

of the tables in the list that do exist.


*Important*: When a table is dropped, user privileges on the table are

not automatically dropped. See [HELP GRANT].


Note that for a partitioned table, DROP TABLE permanently removes the

table definition, all of its partitions, and all of the data which was

stored in those partitions. It also removes the partitioning definition

(.par) file associated with the dropped table.


Use IF EXISTS to prevent an error from occurring for tables that do not

exist. A NOTE is generated for each nonexistent table when using IF

EXISTS. See [HELP SHOW WARNINGS].


RESTRICT and CASCADE are permitted to make porting easier. In MySQL

5.5, they do nothing.


*Note*: DROP TABLE automatically commits the current active

transaction, unless you use the TEMPORARY keyword.


URL: http://dev.mysql.com/doc/refman/5.5/en/drop-table.html



mysql>

mysql> drop table courses cascade;        #加上cascade 还是不能删除,因为那边引用表(子表)默认是 restrict 模式

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

mysql>





[root@mail ~]# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 32

Server version: 5.5.28-log Source distribution


Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> help create index

Name: 'CREATE INDEX'

Description:

Syntax:

CREATE [ONLINE|OFFLINE] [UNIQUE(索引类型,唯一键)|FULLTEXT(索引类型,全文索引)|SPATIAL(索引类型,空间索引)] INDEX index_name      #要指定索引名称

    [index_type]

    ON tbl_name (index_col_name,...)    #  在哪表哪字段上创建

    [index_option] ...


index_col_name:

    col_name [(length)] [ASC | DESC]        # 可以在字段上指定长度,正序,倒序


index_type:

    USING {BTREE | HASH}        # 索引类型 (使用 using 关键字) (一般使用B树索引)


index_option:

    KEY_BLOCK_SIZE [=] value

  | index_type

  | WITH PARSER parser_name

  | COMMENT 'string'


CREATE INDEX is mapped to an ALTER TABLE statement to create indexes.

See [HELP ALTER TABLE]. CREATE INDEX cannot be used to create a PRIMARY

KEY; use ALTER TABLE instead. For more information about indexes, see

http://dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html.


URL: http://dev.mysql.com/doc/refman/5.5/en/create-index.html



mysql>

mysql> use students;

Database changed

mysql> 


mysql> show indexes from student\G

*************************** 1. row ***************************

        Table: student

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: sid

    Collation: A

  Cardinality: 3

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

*************************** 2. row ***************************

        Table: student

   Non_unique: 1

     Key_name: cid

 Seq_in_index: 1

  Column_name: cid

    Collation: A

  Cardinality: 3

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

2 rows in set (0.00 sec)


mysql>


mysql> create index name_on_student on student (name) using btree;    # 创建名为 name_on_student 的索引 , 其实不用指定索引类型,因为一般默认就是 B树索引  (这个好像没有执行)

Query OK, 0 rows affected (0.09 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql>

mysql> show indexes from student;

+---------+------------+----------+--------------+-------------+-----------+----                                      ---------+----------+--------+------+------------+---------+---------------+

| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Car                                      dinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+---------+------------+----------+--------------+-------------+-----------+----                                      ---------+----------+--------+------+------------+---------+---------------+

| student |          0 | PRIMARY  |            1 | sid         | A         |                                                 3 |     NULL | NULL   |      | BTREE      |         |               |

| student |          1 | cid      |            1 | cid         | A         |                                                 3 |     NULL | NULL   |      | BTREE      |         |               |

+---------+------------+----------+--------------+-------------+-----------+----                                      ---------+----------+--------+------+------------+---------+---------------+

2 rows in set (0.00 sec)


mysql> show indexes from student;

+---------+------------+----------+--------------+-------------+-----------+----                                      -----------+---------+---------------+

| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Car                                      Index_type | Comment | Index_comment |

+---------+------------+----------+--------------+-------------+-----------+----                                      -----------+---------+---------------+

| student |          0 | PRIMARY  |            1 | sid         | A         |                                          BTREE      |         |               |

| student |          1 | cid      |            1 | cid         | A         |                                          BTREE      |         |               |

+---------+------------+----------+--------------+-------------+-----------+----                                      -----------+---------+---------------+

2 rows in set (0.00 sec)


mysql> show indexes from student\G

*************************** 1. row ***************************

        Table: student

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: sid

    Collation: A

  Cardinality: 3

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

*************************** 2. row ***************************

        Table: student

   Non_unique: 1

     Key_name: cid

 Seq_in_index: 1

  Column_name: cid

    Collation: A

  Cardinality: 3

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

2 rows in set (0.00 sec)


mysql> create index name_on_student on student (name) using btree;

Query OK, 0 rows affected (0.09 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql> show indexes from student\G

*************************** 1. row ***************************

        Table: student

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: sid

    Collation: A

  Cardinality: 3

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

*************************** 2. row ***************************

        Table: student

   Non_unique: 1

     Key_name: cid

 Seq_in_index: 1

  Column_name: cid

    Collation: A

  Cardinality: 3

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

*************************** 3. row ***************************

        Table: student

   Non_unique: 1

     Key_name: name_on_student        # 这个索引就是刚刚创建的索引

 Seq_in_index: 1

  Column_name: name

    Collation: A

  Cardinality: 3

     Sub_part: NULL

       Packed: NULL

         Null: YES

   Index_type: BTREE

      Comment:

Index_comment:

3 rows in set (0.00 sec)


mysql>

mysql> help drop index

Name: 'DROP INDEX'

Description:

Syntax:

DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name


DROP INDEX drops the index named index_name from the table tbl_name.

This statement is mapped to an ALTER TABLE statement to drop the index.

See [HELP ALTER TABLE].


To drop a primary key, the index name is always PRIMARY, which must be

specified as a quoted identifier because PRIMARY is a reserved word:


DROP INDEX `PRIMARY` ON t;


URL: http://dev.mysql.com/doc/refman/5.5/en/drop-index.html




mysql> drop index name_on_student on student;        # 删除索引

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql>

mysql> show indexes from student\G            # 删除了 没有 name_on_student  索引了

*************************** 1. row ***************************

        Table: student

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: sid

    Collation: A

  Cardinality: 3

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

*************************** 2. row ***************************

        Table: student

   Non_unique: 1

     Key_name: cid

 Seq_in_index: 1

  Column_name: cid

    Collation: A

  Cardinality: 3

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

2 rows in set (0.00 sec)


mysql>



mysql> create index name_on_student on student (name(5) desc) using btree;    #创建索引,指定长度,指定降序  (这个好像未执行)

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql>

mysql> show indexes from student\G

*************************** 1. row ***************************

        Table: student

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: sid

    Collation: A

  Cardinality: 3

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

*************************** 2. row ***************************

        Table: student

   Non_unique: 1

     Key_name: cid

 Seq_in_index: 1

  Column_name: cid

    Collation: A

  Cardinality: 3

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

2 rows in set (0.00 sec)


mysql> create index name_on_student on student (name(5) desc) using btree;

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0


mysql> show indexes from student\G

*************************** 1. row ***************************

        Table: student

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: sid

    Collation: A

  Cardinality: 3

     Sub_part: NULL

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

*************************** 2. row ***************************

        Table: student

   Non_unique: 1

     Key_name: cid

 Seq_in_index: 1

  Column_name: cid

    Collation: A

  Cardinality: 3

     Sub_part: NULL    #为空表示所有的,

       Packed: NULL

         Null:

   Index_type: BTREE

      Comment:

Index_comment:

*************************** 3. row ***************************

        Table: student

   Non_unique: 1

     Key_name: name_on_student

 Seq_in_index: 1

  Column_name: name

    Collation: A

  Cardinality: 3

     Sub_part: 5    # 索引只建在前5个字符上

       Packed: NULL

         Null: YES

   Index_type: BTREE

      Comment:

Index_comment:

3 rows in set (0.00 sec)


mysql>


普通分类: