操作MySQL的基本命令有哪些

本篇文章給大家主要講的是關(guān)于操作MySQL的基本命令有哪些的內(nèi)容，感興趣的話就一起來看看這篇文章吧，相信看完操作MySQL的基本命令有哪些對大家多少有點參考價值吧。

操作系統(tǒng)：CentOS

數(shù)據(jù)庫系統(tǒng)：MySQL

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

mysql>select version(),current_date;   #檢索當前數(shù)據(jù)庫版本和當前時間

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

|version()      | current_date |

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

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

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

1row in set (0.00 sec)

mysql>use employees   #打開數(shù)據(jù)庫

Databasechanged

mysql>select database();   #查詢當前數(shù)據(jù)庫

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

|database() |

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

|employees  |

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

1row in set (0.00 sec)

mysql>show tables;    #顯示該數(shù)據(jù)庫當中的所有表

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

|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;      #顯示表結(jié)構(gòu)

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

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

 以上關(guān)于操作MySQL的基本命令有哪些詳細內(nèi)容，對大家有幫助嗎？如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。