Mysql常用DDL命令

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)

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)

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

mysql> show tables;

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

| tables_in_wison

| test |

1 row

in set (0.00 sec)

5.删除数据库

mysql> drop database wison;

query ok, 1 row

affected (0.06 sec)

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)

7.查看创建表的脚本

mysql> show create table

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

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)

8.删除表

mysql> drop table test;

query ok, 0 rows affected

(0.03 sec)

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

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

9.3删除列

mysql> alter table test drop column

address;

query ok, 0 rows affected (0.19 sec)

9.4重命名列名

mysql> alter table test change age nianling

int;

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

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

| field

| type | null | key | default | extra

| id |

| name | varchar(10) | yes

| | null | |

| nianling | int(11) | yes | | null |

| country | char(3) | yes | | null |

mysql> alter table test add birth date after name ;

query ok, 0 rows

affected (0.20 sec)

records: 0 duplicates: 0 warnings: 0

| birth | date | yes | | null |

| nianling | int(11) | yes | | null | |

| country

| char(3) | yes | | null |

5 rows in set

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

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

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

10.重命名表

mysql> alter table test rename

test_table;

query ok, 0 rows affected (0.04 sec)