Mysql管理表和索引

比眉伴天荒 2022-01-15 04:35 219阅读 0赞

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 ;

\+----------+-----------------------------+---------------------+--------+

\+----------+-----------------------------+---------------------+--------+

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

| swe7 | 7bit Swedish | swe7\_swedish\_ci | 1 |

| tis620 | TIS620 Thai | tis620\_thai\_ci | 1 |

| gb2312 | GB2312 Simplified Chinese | gb2312\_chinese\_ci | 2 |

| cp1250 | Windows Central European | cp1250\_general\_ci | 1 |

| gbk | GBK Simplified Chinese | gbk\_chinese\_ci | 2 |

| utf8 | UTF-8 Unicode | utf8\_general\_ci | 3 |

| ucs2 | UCS-2 Unicode | ucs2\_general\_ci | 2 |

| cp866 | DOS Russian | cp866\_general\_ci | 1 |

| cp852 | DOS Central European | cp852\_general\_ci | 1 |

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

| cp932 | SJIS for Windows Japanese | cp932\_japanese\_ci | 2 |

\+----------+-----------------------------+---------------------+--------+

39 rows in set (0.00 sec)

显示所有字符集与排序规则

mysql> show collation;

\+--------------------------+----------+-----+---------+----------+---------+

\+--------------------------+----------+-----+---------+----------+---------+

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

\+-------+------------------+------+-----+---------+----------------+

\+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto\_increment |

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

\+-------+------------------+------+-----+---------+-------+

\+-------+------------------+------+-----+---------+-------+

| id | int(10) unsigned | NO | | 0 | |

| age | tinyint(4) | NO | | NULL | |

\+-------+------------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

4.3以其他的表为模板创建一个空表,该方法可以继承原表相关的约束与索引.

mysql> desc mytab2;

\+-------+------------------+------+-----+---------+----------------+

\+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto\_increment |

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

\+-------+------------------+------+-----+---------+----------------+

\+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto\_increment |

| age | tinyint(4) | NO | MUL | NULL | |

\+-------+------------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

5.创建与显示索引

mysql> show indexes from mytab2;

\+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

\+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| mytab2 | 0 | PRIMARY | 1 | id | 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;

\+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

\+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

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

\+-------+------------------+------+-----+---------+----------------+

\+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto\_increment |

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

\+-------+------------------+------+-----+---------+----------------+

\+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto\_increment |

| age | tinyint(4) | NO | MUL | NULL | |

\+-------+------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

删除表上索引:

mysql> desc mytab1;

\+-------+------------------+------+-----+---------+----------------+

\+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto\_increment |

| age | tinyint(4) | NO | MUL | NULL | |

\+-------+------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

mysql> show indexes from mytab1;

\+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

\+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| mytab1 | 0 | PRIMARY | 1 | id | 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;

\+-------+------------------+------+-----+---------+----------------+

\+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto\_increment |

| age | tinyint(4) | NO | MUL | NULL | |

\+-------+------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

mysql的外键只能使用在Innodb上面.

mysql> desc mytab2;

\+-------+------------------+------+-----+---------+----------------+

\+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto\_increment |

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

\+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

\+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

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

转载于:https://blog.51cto.com/webseven/1533447