Mysql常用命令：

--在Mysql中，语句的结尾要么使用;要么使用\g或者\G作为结束符。

进入Mysql

(---其中Your MySQL connection id is 5表示到当前为止连接到Mysql数据库的次数，Server version: 5.5.37-log Source distribution表示Mysql数据库的版本)

[wh42@e3ddba11 data]$ mysql -uroot -p

Enter password:

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

Your MySQL connection id is 5

Server version: 5.5.37-log Source distribution

Copyright (c) 2000, 2014, 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>

1.创建数据库

mysql> create database wison;

Query OK, 1 row affected (0.00 sec)

2.显示目前Mysql中存在多少数据库

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| wison |

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

4 rows in set (0.00 sec)

mysql>

3.选择到某个数据库,之后创建表

mysql> use wison

Database changed

mysql> create table test(id int,name varchar(20),address nchar(10),age int)\G

Query OK, 0 rows affected (0.11 sec)

mysql>

4.显示某个数据库中有多少表

mysql> use wison

Database changed

mysql> show tables;

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

| Tables_in_wison |

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

| test |

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

1 row in set (0.00 sec)

mysql>

5.删除数据库

mysql> drop database wison;

Query OK, 1 row affected (0.06 sec)

mysql>

6.查看表结构

mysql> desc test;

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

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

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

| id | int(11) | YES | | NULL | |

| name | varchar(20) | YES | | NULL | |

| address | char(10) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

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

4 rows in set (0.00 sec)

mysql>

7.查看创建表的脚本

mysql> show create table test;

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

| Table | Create Table |

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

| test | CREATE TABLE `test` (

`id` int(11) DEFAULT NULL,

`name` varchar(20) DEFAULT NULL,

`address` char(10) DEFAULT NULL,

`age` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

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

1 row in set (0.00 sec)

mysql>

8.删除表

mysql> drop table test;

Query OK, 0 rows affected (0.03 sec)

mysql>

9修改表结构

9.1修改列类型

mysql> alter table test

-> modify name varchar(10);

Query OK, 0 rows affected (0.18 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>

9.2添加新列

mysql> alter table test add column country char(3);

Query OK, 0 rows affected (0.35 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>

9.3删除列

mysql> alter table test drop column address;

Query OK, 0 rows affected (0.19 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>

9.4重命名列名

mysql> alter table test change age nianling int;

Query OK, 0 rows affected (0.18 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql>

9.5更改列的顺序---该功能比较嗨---我们先看下当前表的结构

mysql> desc test;

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

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

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

| id | int(11) | YES | | NULL | |

| name | varchar(10) | YES | | NULL | |

| nianling | int(11) | YES | | NULL | |

| country | char(3) | YES | | NULL | |

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

4 rows in set (0.00 sec)

mysql> alter table test add birth date after name ;

Query OK, 0 rows affected (0.20 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

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

| id | int(11) | YES | | NULL | |

| name | varchar(10) | YES | | NULL | |

| birth | date | YES | | NULL | |

| nianling | int(11) | YES | | NULL | |

| country | char(3) | YES | | NULL | |

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

5 rows in set (0.00 sec)

mysql> alter table test modify name int(4) first;

Query OK, 0 rows affected (0.20 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

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

| name | int(4) | YES | | NULL | |

| id | int(11) | YES | | NULL | |

| birth | date | YES | | NULL | |

| nianling | int(11) | YES | | NULL | |

| country | char(3) | YES | | NULL | |

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

5 rows in set (0.00 sec)

mysql>

10.重命名表

mysql> alter table test rename test_Table;

Query OK, 0 rows affected (0.04 sec)

mysql>