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)

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>