操作MySQL的基本命令有哪些

本篇文章给大家主要讲的是关于操作MySQL的基本命令有哪些的内容，感兴趣的话就一起来看看这篇文章吧，相信看完操作MySQL的基本命令有哪些对大家多少有点参考价值吧。

创新互联-专业网站定制、快速模板网站建设、高性价比京口网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式京口网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖京口地区。费用合理售后完善，10年实体公司更值得信赖。

操作系统：CentOS

数据库系统：MySQL

[root@sky9896~]# mysql -h localhost  -P  3306 -uroot –p    #注意端口号前的P是大小

mysql>select version(),current_date;   #检索当前数据库版本和当前时间

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

|version()      | current_date |

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

|5.5.52-cll-lve | 2017-08-09   |

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

1row in set (0.00 sec)

mysql>use employees   #打开数据库

Databasechanged

mysql>select database();   #查询当前数据库

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

|database() |

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

|employees  |

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

1row in set (0.00 sec)

mysql>show tables;    #显示该数据库当中的所有表

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

|Tables_in_employees |

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

|departments         |

|dept_emp            |

|dept_manager        |

|employees           |

|salaries            |

|titles              |

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

6rows in set (0.00 sec)

 mysql>create table employees_2(        #建表

    -> emp_no int(11) NOT NULL,

    -> birth_date date NOT null,

    -> first_name varchar(14) not null,

    -> last_name varchar(16) not null,

    -> gender enum('M','F') not null,

    -> hire_date date not null,

    -> primary key(emp_no)

    -> )engine=innodb default charset=latin1;

QueryOK, 0 rows affected (0.19 sec)

mysql>desc employees_2;      #显示表结构

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

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

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

|emp_no     | int(11)       | NO  | PRI | NULL    |      |

|birth_date | date          | NO   |    | NULL    |       |

|first_name | varchar(14)   | NO   |    | NULL    |       |

|last_name  | varchar(16)   | NO  |     | NULL    |      |

|gender     | enum('M','F') | NO   |    | NULL    |       |

|hire_date  | date          | NO  |     | NULL    |      |

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

6rows in set (0.02 sec)

mysql>create table t1(a integer,b char(10));

QueryOK, 0 rows affected (0.11 sec)

mysql>show tables;

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

|Tables_in_employees |

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

|departments         |

|dept_emp            |

|dept_manager        |

|employees           |

|employees_2         |

|salaries            |

|t1                  |

|titles              |

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

8rows in set (0.00 sec)

mysql>alter table t1 rename t2;    #修改表名

QueryOK, 0 rows affected (0.05 sec)

mysql>show tables;

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

|Tables_in_employees |

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

|departments         |

|dept_emp            |

|dept_manager        |

|employees           |

|employees_2         |

|salaries            |

|t2                  |

|titles              |

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

8rows in set (0.00 sec)

mysql>desc t2;                                                     

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

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

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

|a     | int(11)  | YES |     | NULL    |      |

|b     | char(10) | YES  |     |NULL    |       |

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

2rows in set (0.00 sec)

mysql>  alter table t2 modify a tinyint notnull,change b c char(20);   #修改列表

QueryOK, 0 rows affected (0.25 sec)

Records:0  Duplicates: 0  Warnings: 0

mysql>desc t2;

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

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

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

|a     | tinyint(4) | NO   |    | NULL    |       |

|c     | char(20)   | YES |     | NULL    |      |

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

2rows in set (0.00 sec)

ysql>alter table t2 add d timestamp;

QueryOK, 0 rows affected (0.19 sec)

Records:0  Duplicates: 0  Warnings: 0

mysql>desc t2;

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

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

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

|a     | tinyint(4) | NO   |    | NULL              |                             |

|c     | char(20)   | YES |     | NULL              |                             |

|d     | timestamp  | NO  |     | CURRENT_TIMESTAMP | on updateCURRENT_TIMESTAMP |

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

3rows in set (0.00 sec)

mysql>alter table t2 add index(d),add index(a);   #添加索引

mysql>show keys from t2;    

mysql>insert into employees(emp_no,birth_date,first_name,last_name,gender,hire_date)values('1111112','2017-8-9','wu','haiming','M','2017-08-09');

QueryOK, 1 row affected (0.09 sec)

mysql>select * from employees where emp_no=1111112;

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

|emp_no  | birth_date | first_name |last_name | gender | hire_date  |

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

|1111112 | 2017-08-09 | wu         |haiming   | M      | 2017-08-09 |

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

1row in set (0.00 sec)

 以上关于操作MySQL的基本命令有哪些详细内容，对大家有帮助吗？如果想要了解更多相关，可以继续关注我们的行业资讯板块。