首页 > 代码库 > Mysql管理表和索引
Mysql管理表和索引
1.创建数据库:
mysql> help create database;
Name: ‘CREATE DATABASE‘
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
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> show character set ;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.00 sec)
显示所有字符集与排序规则
mysql> show collation;
+--------------------------+----------+-----+---------+----------+---------+
| 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 database if not exists db1 character set utf8 collate=utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
2.修改数据库.
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
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
3.删除数据库
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
4.创建表:
4.1直接创建一个空表.
mysql> create table mytab1(id int unsigned auto_increment not null primary key,name varchar(20) not null,age tinyint not null);
Query OK, 0 rows affected (0.06 sec)
mysql> create table mytab2(id int unsigned auto_increment not null,name varchar(20) not null,age tinyint not null,primary key(id),unique key(name),index(age));
Query OK, 0 rows affected (0.01 sec)
mysql> show table status like ‘mytab1‘\G
*************************** 1. row ***************************
Name: mytab1
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: 10485760
Auto_increment: 1
Create_time: 2014-07-29 23:21:43
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> drop table mytab1;
Query OK, 0 rows affected (0.06 sec)
mysql> create table mytab1(id int,name char(20)) engine myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> show table status like ‘mytab1‘\G
*************************** 1. row ***************************
Name: mytab1
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 18295873486192639
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2014-07-29 23:54:05
Update_time: 2014-07-29 23:54:05
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> drop table mytab1;
Query OK, 0 rows affected (0.00 sec)
mysql> create table mytab1(id int,name varchar(20)) engine=myisam;
Query OK, 0 rows affected (0.02 sec)
mysql> show table stauts like ‘mytab1‘\G
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 ‘stauts like ‘mytab1‘‘ at line 1
mysql> show table status like ‘mytab1‘\G
*************************** 1. row ***************************
Name: mytab1
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: NULL
Create_time: 2014-07-29 23:55:08
Update_time: 2014-07-29 23:55:08
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
4.2从其他表中查询出数据,并以之创建出新表,该方法无法继承原表相关的约束与索引
mysql> desc mytab2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| age | tinyint(4) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> create table mytest4 select * from mytab2 where 1=2;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc mytest4;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | | 0 | |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(4) | NO | | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
4.3以其他的表为模板创建一个空表,该方法可以继承原表相关的约束与索引.
mysql> desc mytab2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| age | tinyint(4) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> create table mytest5 like mytab2;
dQuery OK, 0 rows affected (0.42 sec)
mysql> desc mytest5;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| age | tinyint(4) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
5.创建与显示索引
mysql> show indexes from mytab2;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mytab2 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| mytab2 | 0 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| mytab2 | 1 | age | 1 | age | A | 0 | NULL | NULL | | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> show indexes from mytab1;
Empty set (0.00 sec)
mysql> create index mytab1_idx01 on mytab1(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show indexes from mytab1;
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mytab1 | 1 | mytab1_idx01 | 1 | id | A | NULL | NULL | NULL | YES | BTREE | | |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
6.修改表:
mysql> alter table mytab1 add class varchar(20) after name;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc mytab1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| class | varchar(20) | YES | | NULL | |
| age | tinyint(4) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table mytab1 modify class varchar(20) not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc mytabl1;
ERROR 1146 (42S02): Table ‘mydb.mytabl1‘ doesn‘t exist
mysql> desc mytab1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| class | varchar(20) | NO | | NULL | |
| age | tinyint(4) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
删除表上索引:
mysql> desc mytab1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| class | varchar(20) | NO | | NULL | |
| age | tinyint(4) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show indexes from mytab1;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mytab1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| mytab1 | 0 | name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
| mytab1 | 1 | age | 1 | age | A | 0 | NULL | NULL | | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> alter table mytab1 drop index name;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc mytab1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| class | varchar(20) | NO | | NULL | |
| age | tinyint(4) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql的外键只能使用在Innodb上面.
mysql> desc mytab2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | UNI | NULL | |
| age | tinyint(4) | NO | MUL | NULL | |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> drop index name on mytab2;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建前N个字符的索引:
mysql> create index mytab2_idx01 on mytab2(name(5) desc);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index mytab2_idx02 on mytab2(name(6) desc);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show indexes from mytab2;
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mytab2 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| mytab2 | 1 | age | 1 | age | A | 0 | NULL | NULL | | BTREE | | |
| mytab2 | 1 | mytab2_idx01 | 1 | name | A | 0 | 5 | NULL | | BTREE | | |
| mytab2 | 1 | mytab2_idx02 | 1 | name | A | 0 | 6 | NULL | | BTREE | | |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
本文出自 “webseven” 博客，请务必保留此出处http://webseven.blog.51cto.com/4388012/1533447