mysql 数据库常用操作

创建数据库

create database database_name

mysql> create database test_db;
Query OK, 1 row affected (0.01 sec)

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

删除数据库

mysql> drop database test_db;
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)

连接数据/切换数据库

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

mysql> use test_db;
Database changed

查看当前连接的数据库

mysql> select database();
+------------+
| database() |
+------------+
| test_db    |
+------------+
1 row in set (0.00 sec)

创建表

mysql> create table test (id int(4) not null,name char(20) not null);
Query OK, 0 rows affected, 1 warning (0.07 sec)

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

创建带主键的表

mysql> create table table1 (id int(4) not null primary key auto_increment,name char(20) not null,age char(33) not null);
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| table1            |
| test              |
+-------------------+
2 rows in set (0.00 sec)

插入数据

mysql> desc test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   |     | NULL    |       |
| name  | char(20) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into test (id,name) values (1,'mingongge');
Query OK, 1 row affected (0.00 sec)

查询数据

mysql> select * from test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | mingongge |
+----+-----------+
1 row in set (0.01 sec)

修改数据

mysql> update test set name='spf' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | spf  |
+----+------+
1 row in set (0.00 sec)

删除数据

mysql> delete from test where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
Empty set (0.00 sec)

增加表字段

mysql> desc test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   |     | NULL    |       |
| name  | char(20) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> alter table test add sex char(20);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   |     | NULL    |       |
| name  | char(20) | NO   |     | NULL    |       |
| sex   | char(20)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
#新增列默认在所有列后面

在指定列后面新增字段

mysql> desc test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   |     | NULL    |       |
| name  | char(20) | NO   |     | NULL    |       |
| sex   | char(1)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table test add age int(3) after name;
Query OK, 0 rows affected, 1 warning (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   |     | NULL    |       |
| name  | char(20) | NO   |     | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
| sex   | char(1)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

删除字段

mysql> alter table test drop sex;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int      | NO   |     | NULL    |       |
| name  | char(20) | NO   |     | NULL    |       |
| age   | int      | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

表更名

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| table1            |
| test              |
+-------------------+
2 rows in set (0.00 sec)

mysql> rename table test to test1;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| table1            |
| test1             |
+-------------------+
2 rows in set (0.00 sec)

删除表

mysql> drop table test1;
Query OK, 0 rows affected (0.01 sec)

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