3Mysql 的常用操作

root@OBird ~]# mysql -uroot -pzaq12wsx   #入库

mysql> show databases;  #查库

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    24

Current database: *** NONE ***

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

| Database           |

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

| information_schema |

| discuz             |

| mysql              |

| test               |

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

4 rows in set (0.04 sec)

mysql> use mysql   #切换库

No connection. Trying to reconnect...

Connection id:    25

Current database: *** NONE ***

Database changed

#切换了库以后，我们如怎么确认我们当前是在哪个库下

mysql> select database();# 查看当前数据库的命令

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    28

Current database: discuz

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

| database() |

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

| discuz     |

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

1 row in set (0.00 sec)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

mysql> select version();# 查看软件的版本

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    30

Current database: discuz

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

| version()  |

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

| 5.1.73-log |

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

1 row in set (0.00 sec)

——————————————————————————————————————————————

库--> 表--> 行 --> 字段

mysql> show tables; # 查库

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    31

Current database: discuz

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

| Tables_in_discuz                  |

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

| pre_common_admincp_cmenu          |

| pre_common_admincp_group          |

| pre_common_admincp_member         |#下略，太多

| pre_common_admincp_perm           |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

查看表的字段

mysql> desc  pre_ucenter_vars; #查看表的字段

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    32

Current database: discuz

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

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

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

| name  | char(32)  | NO   | PRI |         |       |

| value | char(255) | NO   |     |         |       |

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

2 rows in set (0.02 sec)

mysql> show create table pre_ucenter_vars\G;  #查询表的创建语句

*************************** 1. row ***************************

       Table: pre_ucenter_vars

Create Table: CREATE TABLE `pre_ucenter_vars` (

  `name` char(32) NOT NULL DEFAULT '',

  `value` char(255) NOT NULL DEFAULT '',

  PRIMARY KEY (`name`)

) ENGINE=MEMORY DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

ERROR: 

No query specified

##############################################################################################

手动创建一个库

mysql> create database obird; # 创建库名

mysql> use obird;             # 进入创建库

mysql> create table tb1 (`id` int(4), `name` char(40)) ENGINE=MyISAM DEFAULT CHARSET=gbk;

#创建表名  tb1,表内的字段   int(长度是4位)，char(最长40)，ENGINE ,字符集=gbk

mysql> show tables;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    41

Current database: obird

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

| Tables_in_obird |

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

| tb1             |

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

1 row in set (0.00 sec)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

mysql> desc tb1;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    42

Current database: obird

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

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

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

| id    | int(4)   | YES  |     | NULL    |       |

| name  | char(40) | YES  |     | NULL    |       |

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

2 rows in set (0.00 sec)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

mysql> show create table tb1\G;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    44

Current database: obird

*************************** 1. row ***************************

       Table: tb1

Create Table: CREATE TABLE `tb1` (

  `id` int(4) DEFAULT NULL,

  `name` char(40) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

ERROR: 

No query specified

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

在表内插入数据

mysql> insert into tb1 values(1,'aming');

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    45

Current database: obird

Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1; # 查询

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    46

Current database: obird

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

| id   | name  |

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

|    1 | aming |

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

1 row in set (0.00 sec)

继续插入

mysql> insert into tb1 values(2,'linux');

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    47

Current database: obird

Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;  #再次查询

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    48

Current database: obird

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

| id   | name  |

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

|    1 | aming |

|    2 | linux |

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

2 rows in set (0.00 sec)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

只插一个字段

mysql> insert into tb1 (`id`) values(2);

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    49

Current database: obird

Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;

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

| id   | name  |

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

|    1 | aming |

|    2 | linux |

|    2 | NULL  |# 只插入一个字段ID，name 此处是空的

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

3 rows in set (0.00 sec)

mysql> insert into tb1 (`name`) values('test');

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    50

Current database: obird

Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;

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

| id   | name  |

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

|    1 | aming |

|    2 | linux |

|    2 | NULL  |

| NULL | test  |# 只插入name ,ID 是空

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

4 rows in set (0.00 sec)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

也可以花式插法，id&name 互换，但是要一一对应

mysql> insert into tb1 (`name`,`id`) values('77',6);

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    51

Current database: obird

Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;

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

| id   | name  |

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

|    1 | aming |

|    2 | linux |

|    2 | NULL  |

| NULL | test  |

|    6 | 77    |

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

5 rows in set (0.00 sec)

##############################################################################################

更新一条数据

mysql> update tb1 set id=8 where name = '77'; #name = 77 的只有一行，改成了 “8”

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    52

Current database: obird

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb1;

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

| id   | name  |

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

|    1 | aming |

|    2 | linux |

|    2 | NULL  |

| NULL | test  |

|    8 | 77    |

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

5 rows in set (0.00 sec)

删除一行

mysql> delete from tb1 where name='77';# 删除指定的一行 77

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    54

Current database: obird

Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    57

Current database: obird

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

| id   | name  |

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

|    1 | aming |

|    2 | linux |

|    2 | NULL  |

| NULL | test  |

+------+-------+ # 查询不到 77 的行

4 rows in set (0.00 sec)

清空一张表

mysql> truncate table obird.tb1; #清空一张表

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    59

Current database: obird

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;  # 表已经空了

Empty set (0.00 sec)

干掉整张表

mysql> drop table tb1;  #语法

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    60

Current database: obird

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

ERROR 1146 (42S02): Table 'obird.tb1' doesn't exist  #报错表已经不存在

干掉 obird 这个库

mysql> show databases; #查询，obird 这个库是存在的

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    61

Current database: obird

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

| Database           |

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

| information_schema |

| discuz             |

| mysql              |

| obird              |

| test               |

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

5 rows in set (0.00 sec)

mysql> drop database obird; # 干掉库的语法

ERROR 2006 (HY000): MySQL server  has gone away

No connection. Trying to reconnect...

Connection id:    62

Current database: obird

Query OK, 0 rows affected (0.01 sec)

mysql> show databases;

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

| Database           |

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

| information_schema |

| discuz             |

| mysql              |

| test               |#再查询，obird 已经不在了。

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

4 rows in set (0.00 sec)

##############################################################################################