Common operations for 3Mysql

[Email protected] ~]# 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

#切换了库以后, how can we confirm which library we are currently under?

Mysql> Select Database (); # view commands for current databases

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 (); # View software versions

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)

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

----table-----Fields

Mysql> Show tables; # Check the Library

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 | #下略, too many

| Pre_common_admincp_perm |

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

View the fields of a table

mysql> desc Pre_ucenter_vars; # View fields of a table

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

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

Create a library manually

mysql> CREATE DATABASE Obird; # Create a library name

mysql> use Obird; # Go to create library

Mysql> CREATE TABLE TB1 (' id ' int (4), ' name ' char (+) ') Engine=myisam DEFAULT CHARSET=GBK;

#创建表名 tb1, the field int in the table (length is 4 bits), char (max.), ENGINE, character set =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 (+) DEFAULT NULL

) Engine=myisam DEFAULT CHARSET=GBK

1 row in Set (0.00 sec)

ERROR:

No query specified

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

Inserting data inside a table

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; # query

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)

Continue inserting

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)

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

Insert only one field

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 |# inserts only one field id,name here is empty

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

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 |# only insert name, ID is empty

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

4 rows in Set (0.00 sec)

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

can also be used in the interpolation method, Id&name interchange, but to each corresponding

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 |

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

5 rows in Set (0.00 sec)

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

Update One piece of data

mysql> Update tb1 set id=8 where name = ' 77 '; #name = 77 Only one row, changed to "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)

Delete a row

Mysql> Delete from tb1 where name= ' 77 '; # deletes the specified row 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 |

+------+-------+ # query less than 77 rows

4 rows in Set (0.00 sec)

Clear a table

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; # The table is empty #

Empty Set (0.00 sec)

Kill the whole table.

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 #报错表已经不存在

Kill Obird, this library.

mysql> show databases; #查询, Obird, this library is there.

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; # Kill the Library syntax

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 is gone.

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

4 rows in Set (0.00 sec)

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

This article is from the "Cbo#boy_linux Road" blog, make sure to keep this source http://20151213start.blog.51cto.com/9472657/1858621

Common operations for 3Mysql