MySQL数据库(二)：基本管理
安装环境： 操作系统版本：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)information_schema:虚拟库，保存当前数据库服务器已有库和表的信息,数据存放在系统内存里,此库数据不占用系统磁盘空间 mysql:授权库,保存用户的授权信息,此库数据占有系统磁盘空间 performance_schema:参数库,数据库服务器运行时的参数信息,此库数据占有系统磁盘空间 test:公共库,任意一个能够连接数据库服务器的用户，就对此库拥有完全权限,此库数据占有系统磁盘空间
二、数据存放简述
存放在数据库服务器上的库和表，是以文件的形式保存在数据库目录下的，如果直接到存放数据库的目录下把文件删除，那么对应的数据库或者表也随之删除。
[root@niaoyun var]# ls
doc linech mysql-bin.index niaoyun.pid qfpigment wiki
lianst mysql niaoyun.err performance_schema regdb
[root@niaoyun var]# cd mysql
[root@niaoyun 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、登陆数据库
[root@niaoyun 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、登陆到指定的数据库
[root@niaoyun 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 changed4、查看当前所在库
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)本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。
腾讯云开发者
