知我知行

安装环境：
操作系统版本：RHEL 6.5
安装版本：MYSQL 5.1
升级版本：MYSQL 5.6

一、默认库介绍
安装完成之后，mysql会自动创建以下三个默认的库.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.07 sec)

二、数据存放简述
存放在数据库服务器上的库和表，是以文件的形式保存在数据库目录下的，如果直接到存放数据库的目录下把文件删除，那么对应的数据库或者表也随之删除。

[[email protected] var]# ls
doc     linech  mysql-bin.index  niaoyun.pid         qfpigment  wiki
lianst  mysql   niaoyun.err      performance_schema  regdb
[[email protected] var]# cd mysql
[[email protected] mysql]# ls
columns_priv.frm   help_topic.frm        slow_log.CSM
columns_priv.MYD   help_topic.MYD        slow_log.CSV
columns_priv.MYI   help_topic.MYI        slow_log.frm
db.frm             host.frm              tables_priv.frm
db.MYD             host.MYD              tables_priv.MYD
db.MYI             host.MYI              tables_priv.MYI
event.frm          ndb_binlog_index.frm  time_zone.frm
event.MYD          ndb_binlog_index.MYD  time_zone_leap_second.frm
event.MYI          ndb_binlog_index.MYI  time_zone_leap_second.MYD
func.frm           plugin.frm            time_zone_leap_second.MYI
func.MYD           plugin.MYD            time_zone.MYD
func.MYI           plugin.MYI            time_zone.MYI
general_log.CSM    proc.frm              time_zone_name.frm
general_log.CSV    proc.MYD              time_zone_name.MYD
general_log.frm    proc.MYI              time_zone_name.MYI
help_category.frm  procs_priv.frm        time_zone_transition.frm
help_category.MYD  procs_priv.MYD        time_zone_transition.MYD
help_category.MYI  procs_priv.MYI        time_zone_transition.MYI
help_keyword.frm   proxies_priv.frm      time_zone_transition_type.frm
help_keyword.MYD   proxies_priv.MYD      time_zone_transition_type.MYD
help_keyword.MYI   proxies_priv.MYI      time_zone_transition_type.MYI
help_relation.frm  servers.frm           user.frm
help_relation.MYD  servers.MYD           user.MYD
help_relation.MYI  servers.MYI           user.MYI

三、mysql基本管理命令
1、登陆数据库

[[email protected] mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 75
Server version: 5.5.48 Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

2、登陆到指定的数据库

[[email protected] mysql]# mysql -uroot -p123456 ceshi
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 76
Server version: 5.5.48 Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

3、查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| doc                |
| lianst             |
| linech             |
| mysql              |
| performance_schema |
| qfpigment          |
| regdb              |
| wiki               |
+--------------------+
9 rows in set (0.00 sec)

2、切换库(进入数据库)

mysql> use mysql;
Database changed

4、查看当前所在库

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

5、查看当前库中的表

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

6、创建库

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

7、删除库

mysql> drop database dbname;
Query OK, 0 rows affected (0.00 sec)

8、创建表

mysql> create table t1(name varchar(20),age int(2));
Query OK, 0 rows affected (0.00 sec)

9、向表中插入数据

mysql> insert into t1 values("tom",12);
Query OK, 1 row affected (0.00 sec)

10、查看表中记录

mysql> select * from t1;
+------+------+
| name | age  |
+------+------+
| tom  |   12 |
+------+------+
1 row in set (0.00 sec)

11、删除表

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

12、查看表结构

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(2)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)