1.连接数据库

mysql -h主机地址 -u用户名 －p用户密码

1.数据库操作

1.1 创建/查看数据库

创建数据库：mysql> create database 数据库名称

列出所有数据库：mysql> show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

1.2 删除数据库

格式：mysql> drop database 数据库名称；

mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

1.3 切换到一个数据库

格式：mysql> use 数据库名称

切换到test数据库

mysql> use test;
Database changed

执行以上命令后，你就已经成功选择了 test 数据库，在后续的操作中都会在 test 数据库中执行

2数据表的操作

2.1 创建表

格式：CREATE TABLE table_name (column_name column_type);

column_name：数据列名称

column_type：数据列的数据类型

mysql> create table test1(usename char(5));
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test1          |
+----------------+
1 row in set (0.00 sec)

2.2 修改表的名称

mysql> alter table test1 rename test2;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test2          |
+----------------+
1 row in set (0.00 sec)

2.3 数据表增加列以及查看表里面的具体信息

增加列：mysql> alter table 数据表名称 add 增加列的名称 数据类型;

查看表的列信息：mysql> desc 表的名称;或者mysql> describe 表的名称;

mysql> alter table test2 add passwd int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test2;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| usename | char(5) | YES  |     | NULL    |       |
| passwd  | int     | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe test2;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| usename | char(5) | YES  |     | NULL    |       |
| passwd  | int     | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2.4 删除表中的列

格式：mysql> alter table 表的名称 drop 要删除的列名称;

mysql> alter table test2 drop usename;
Query OK, 0 rows affected (1.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe test2;
+--------+------+------+-----+---------+-------+
| Field  | Type | Null | Key | Default | Extra |
+--------+------+------+-----+---------+-------+
| passwd | int  | YES  |     | NULL    |       |
+--------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

2.5 更改列的名称

格式：mysql> alter table 表名 change 旧的列名 新列名 新列数据类型;

mysql> alter table test2 change passwd username char(6);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe test2;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| username | char(6) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

2.6 插入数据

格式：INSERT INTO<表名> [ <列名1> [ , … <列名n>] ]VALUES(值1) [… , (值n) ];
查看表的具体内容：mysql> select 列1名,列名2 from 表名;

mysql> insert into test2 (username,passwd) value(123,123456);
Query OK, 1 row affected (0.00 sec)

mysql> select username,passwd from test2;
+----------+--------+
| username | passwd |
+----------+--------+
| 123      | 123456 |
+----------+--------+
1 row in set (0.00 sec)