CentOS6.5下简单的MySQL数据库操作
1.登录成功之后退出的话，直接输入quit或者exit即可。
aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAGoAAAAnCAIAAABohUpyAAAFKElEQVRoge2Y30tabxzH93cc65RH6xyWQc1Fs01Kk3BFdLFhGO2yLhb9GIxVyGhJhaTdFLSbCpIarCAW5SJqdRFIPwjdBN2S1g/KgiVs/TDNk59dPGF+j6e0rGRffV35POfzPOecl+c85+F9D+JEwL1oX8C/TVxfRMT1RURcX0TE9UVEXF9ExPVFxB3p4/P5GIZhGGa32+/mjIF0dXUpFIrbmPnunj63283Qh2HY3Zx6enq6u7ub0Wk2myO/gGjqgzs0GMyN6ZuamsrJycnPz3/w4EFfXx9FUSUlJV6vd3V1lcPhbG5uolKv1ysQCAwGAwAcHx/X1taSJJmUlFRQUGCxWFDN4eFhZWUlQRBSqVSj0YhEIv+ZWPXBFQ3SNK3RaDIyMpKTk5VK5fb2NgAcHR2JRKL+/n5Uo9frs7Ky9vf3AWB8fBwtGoEv79raGvZfysrKrujtjDN9BEHYbLbnz5/n5eWtrq5mZmbOz88DQGFhYVdXFyqdnZ2lKMrj8QDA+/fvRSKRxWLZ2dkxGAxfv35FNY2NjVKp1Gq1Go1GgUAQjj64isHW1laZTGYymX7+/FlTU1NUVIT65+fnSZLc2NjY2toiSXJubi5wFOvad5NPn0QiAYC2trba2loAKC0tHRkZAYCenh6ZTIZKX7169fr1a/T73bt3wf+Yz+ejKGp0dBQ1W1pawtQH4Rn0eDw8Hm9hYQE1Dw4OOBzO+vo6ajY3Nz979kypVKpUKsbAW9cnl8sBQKfTvXnzBgBevHjx4cMHAPj16xeO43a7/eTkhKIoo9GIhplMJj6f//Tp0+bmZn+n0+nEMMxqtaLm0NBQ+PogDIPfv3/HglhaWvLP//jx4+zsbJfLxRgYBX2Dg4Ooory8XKvVTk1NCYXC09NT/0in0zk8PFxVVZWYmDg8PAwAe3t719YXzp0gfXt7e6xH19bWUlNTU1JSVlZWGIeiqW9kZEQsFldXV6vVatYpGhoaKisrAcDn85EkedHLS9M0h8Pxf2QCCfM2PB4PQRATExPBh2iaLi4uVqvVGo1GLpd7vd7Ao6z6LBYLhmGBD8Q1CK3P5XKlpKTgOO7/PgDAwMCAwWBwOBwWi0UikWg0GtRfX18vlUptNpvRaExPTw/UBwD5+fkqlWp3d/f379/+zis9Amq1WigUzszMbG9vz87Ovnz5EvV3dnaKxWK3231yciKRSHQ6XeAoVn1OpzMhIWFsbMzlcjF0h09ofQBQXV3NEDE0NCSRSHAcpyiqrq7u6OgI9R8cHFRUVHC5XOSUMWpxcfHRo0eB24irvj40TWu1WqFQmJiYmJ2d3dTUBABWq5UgiOXlZVRjNpu5XK7ZbAYAsVjMWCsdDod/ts7Ozvv370e6cQmJQqFob2+/6tSfPn1i6GMQxT3zTRFC358/fyYmJnAc9+8Pwiekvv8BIfTJ5XKSJHt7e68xdVxfnBDE9UVEXF9ExPVFxJm+tLQ0tC3i8XgKheLHjx/Rvax/hXN9HR0ddrvdZDKVl5c/efIkupf1r3CuT6/Xo9+Tk5MYhrnd7kviUtbYMgZh6kMxslQqRf0XxaUXxZaxxrk+HMeTk5MTEhIePnzoX/tY49LLY8uYgmXtq6mpyc3NPT4+hgvi0stjy5iCZe3zeDxJSUmfP39GzeC49PLYMqZg0UfTNEEQHz9+RM3guPSS2DLWYL683759e/v2bWDEwhqXXhRbxhrMbTOXyy0sLPzy5UtgUXBcyhpbxiC3GJfGArcYl8YCtxiXxgJ/Ae7iLz4HTiGSAAAAAElFTkSuQmCC" alt="" />
2.使用.tar.gz的包安装MySQL时，进入MySQL需要使用绝对路径
[root@winner ~]# /usr/local/mysql/bin/mysql -uroot
单独只是输入一个"mysql" 命令是不行的，因为"/usr/local/mysql/bin" 没有在 PATH 这个环境变量里(使用yum安装不需要)。如何把它加入环境变量PATH中？
[root@winner ~]# PATH=$PATH:/usr/local/mysql/bin
这样就可以了，但重启Linux后还会失效，所以需要让它开机加载:
[root@winner ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@winner ~]# source /etc/profile
[root@localhost ~]# mysql -uroot
-u 用来指定要登录的用户，后边可以有空格，也可以无空格。-p 后面可以直接跟密码，后面不可以有空格，不过密码最好用单引号括起来，不括也可以，但是密码中如果有特殊字符就会有问题了，所以最好是括起来。
连接数据库
通过使用 mysql -u root -p 可以连接数据库，但这只是连接的本地的数据库"localhost", 可是有很多时候都是去连接网络中的某一个主机上的mysql。
[root@winner ~]# mysql -uroot -p -h192.168.137. -P3306
Enter password:
其中后边的 -P(大写) 用来指定远程主机MySQL的绑定端口，默认都是3306, -h 用来指定远程主机的IP.
一些基本的MySQL操作命令
1. 查询当前的库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| winner |
+--------------------+
rows in set (0.07 sec)
2. 查询某个库的表
首先需要切换到某个库中
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed 显示该数据库中的表
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 |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
rows in set (0.00 sec)
3. 查看某个表的全部字段
mysql> desc host;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char() | NO | PRI | | |
| Db | char() | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
rows in set (0.00 sec)
也可以使用另一条命令，显示比这个更详细，而且可以把建表语句全部列出来:
mysql> show create table host\G;
*************************** . row ***************************
Table: host
Create Table: CREATE TABLE `host` (
`Host` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char() COLLATE utf8_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`Db`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Host privileges; Merged with database privileges'
row in set (0.00 sec)
4. 查看当前是哪个用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
row in set (0.00 sec)
5. 查看当前所使用数据库
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
row in set (0.00 sec)
6. 创建一个新库
mysql> create database db1;
Query OK, row affected (0.02 sec)
7. 创建一个新表
mysql> create table t1 (`id` int(), `name` char());
Query OK, rows affected (0.02 sec)
8. 查看当前数据库版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1. |
+-----------+
row in set (0.00 sec)
9. 查看当前MySQL状态
mysql> show status;
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | |
| Aborted_connects | |
| Binlog_cache_disk_use | |
| Binlog_cache_use | |
| Bytes_received | |
| Bytes_sent | |
| Com_admin_commands | |
| Com_assign_to_keycache | |
| Com_alter_db | |
| Com_alter_db_upgrade | |
| Com_alter_event | |
10. 查看MySQL的参数
mysql> show variables;
+-----------------------------------------+---------------------+
| Variable_name | Value |
+-----------------------------------------+---------------------+
| auto_increment_increment | |
| auto_increment_offset | |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | |
| basedir | /usr/local/mysql/ |
11. 修改MySQL的参数
mysql> show variables like 'max_connect%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | |
| max_connections | |
+--------------------+-------+
rows in set (0.00 sec) mysql> set global max_connect_errors = ;
Query OK, rows affected (0.00 sec) mysql> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | |
+--------------------+-------+
row in set (0.00 sec)
在MySQL命令行， "%"类似于shell下的 *, 表示万能匹配。使用 "set global" 可以临时修改某些参数，但是重启mysqld服务后还会变为原来的，所以要想恒久生效，需要在配置文件 my.cnf 中定义。
12. 查看当前MySQL服务器的队列
这个在日常的管理工作中使用最为频繁，因为使用它可以查看当前mysql在干什么，可以发现是否有锁表:
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| | root | localhost | db1 | Query | | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
row in set (0.00 sec)
13. 创建一个普通用户并授权
mysql> grant all on *.* to user1 identified by '';
Query OK, rows affected (0.01 sec)
all 表示所有的权限（读、写、查询、删除等等操作）， *.* 前面的 * 表示所有的数据库，后面的 * 表示所有的表，identified by 后面跟密码，用单引号括起来。这里的user1指的是localhost上的user1，如果是给网络上的其他机器上的某个用户授权则这样:
mysql> grant all on db1.* to 'user2'@'10.0.2.100' identified by '';
Query OK, rows affected (0.01 sec)
用户和主机的IP之间有一个@，另外主机IP那里可以用%替代，表示所有主机，例如:
mysql> grant all on db1.* to 'user3'@'%' identified by '';
Query OK, rows affected (0.00 sec)
MySQL数据库的备份与恢复
备份:
[root@winner ~]# mysqldump -uroot -p'root' mysql >/tmp/mysql.sql
不是在mysql>环境
使用 mysqldump 命令备份数据库，-u 和 -p 两个选项使用方法和前面说的 mysql 同样，而后面的 “mysql” 指的是库名，然后重定向到一个文本文档里。备份完后，你可以查看 /tmp/mysql.sql 这个文件里的内容。
恢复和备份正好相反:
[root@localhost ~]# mysql -uroot -p'yourpassword' mysql </tmp/mysql.sql
最新文章
- SQLite.Net-PCLUSING SQLITE IN WINDOWS 10 UNIVERSAL APPS
- ADO.NET数据访问模板整理
- web初学之MVC
- winform中ComboBox利用AutoComplete属性实现模糊查询（有缺陷）
- HTML 学习笔记 CSS3 (2D Matrix)
- SSAS：菜鸟摸门
- paper 77：[转载]ENDNOTE使用方法，常用！
- Oracle SQL 调优之 sqlhc
- 帝国cms后台不停的登录成功
- POJ2031Building a Space Station
- vbs文件共享变量与函数的方法
- mysql 表
- day15 函数的使用方法：递归函数
- Ajax进阶
- 2017CCPC秦皇岛 C题Crusaders Quest&&ZOJ3983【模拟+STL】
- ASIHTTPRequest 详解, http 请求终结者
- USB 转LAN AX88772B 模块驱动添加记录
- 设计模式之——visitor模式
- python 利用爬虫获取页面上下拉框里的所有国家
- 工作JS总结