MySQL 控制台常用命令 | Mysql Console Commands
使用mysql二进制方式连接
您可以使用MySQL二进制方式进入到mysql命令提示符下来连接MySQL数据库。
以下是从命令行中连接mysql服务器的简单实例:
[root@host]# mysql -u root -p
Enter password:******
在登录成功后会出现 mysql> 命令提示窗口,你可以在上面执行任何 SQL 语句。
以上命令执行后,登录成功输出结果如下:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2854760 to server version: 5.0.9
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
在以上实例中,我们使用了root用户登录到mysql服务器,当然你也可以使用其他mysql用户登录。
如果用户权限足够,任何用户都可以在mysql的命令提示窗口中进行SQL操作。
1.显示数据库
show databases;
mysql> show databases;
Mysql刚安装完有两个数据库:mysql和test。mysql库非常重要,它里面有MySQL的系统信息,我们改密码和新增用户,实际上就是用这个库中的相关表进行操作。
2.显示数据库中的表
show tables;
mysql> use runoon;(打开库)
Database changed
mysql> show tables;
3.显示数据表的结构
describe tablename;
mysql> desc runoon_links;
+------------------+---------------------+------+-----+---------------------+--- -------------+
| Field | Type | Null | Key | Default | Ex tra |
+------------------+---------------------+------+-----+---------------------+--- -------------+
| link_id | bigint(20) unsigned | NO | PRI | NULL | au to_increment |
| link_url | varchar(255) | NO | | | |
| link_name | varchar(255) | NO | | | |
| link_image | varchar(255) | NO | | | |
| link_target | varchar(25) | NO | | | |
| link_description | varchar(255) | NO | | | |
| link_visible | varchar(20) | NO | MUL | Y | |
| link_owner | bigint(20) unsigned | NO | | 1 | |
| link_rating | int(11) | NO | | 0 | |
| link_updated | datetime | NO | | 0000-00-00 00:00:00 | |
| link_rel | varchar(255) | NO | | | |
| link_notes | mediumtext | NO | | NULL | |
| link_rss | varchar(255) | NO | | | |
+------------------+---------------------+------+-----+---------------------+--- -------------+
13 rows in set (0.00 sec)
4.显示表中的记录
select * from tablename;
mysql> select * from runoon_links;
+---------+--------------------------+-----------------+------------+-------------+------------------+--------------+------------+-------------+---------------------+----------+------------+----------+
| link_id | link_url | link_name | link_image | link_target | link_description | link_visible | link_owner | link_rating | link_updated | link_rel | link_notes | link_rss |
+---------+--------------------------+-----------------+------------+-------------+------------------+--------------+------------+-------------+---------------------+----------+------------+----------+
| 1 | http://www.newssoon.com/ | 环球资讯网 | | _blank | 环球资讯网 | Y | 1 | 0 | 0000-00-00 00:00:00 | | | |
| 2 | https://www.runoon.com | 奔月教程 | | _blank | 奔月教程 | Y | 1 | 0 | 0000-00-00 00:00:00 | | | |
+---------+--------------------------+-----------------+------------+-------------+------------------+--------------+------------+-------------+---------------------+----------+------------+----------+
2 rows in set (0.00 sec)
5.创建数据库
create database if not exists dbname default charset utf8 collate utf8_general_ci;
或
create database dbname;
mysql> create database mytest;
Query OK, 1 row affected (0.00 sec)
创建一个名字位mytest的库。
6.创建表
use dbname;
create table tablename;
在刚创建的mytest库中建立表name,表中有id(序号,自动增长),xm(姓名),xb(性别),csny(出身年月)四个字段。
mysql> use mytest;
Database changed
mysql> create table name (id int(3) auto_increment not null primary key, xm char(8),xb char(2),csny date);
Query OK, 0 rows affected (0.01 sec)
可以用describe命令察看刚建立的表结构。
mysql> describe name;
mysql> desc name;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| xm | char(8) | YES | | NULL | |
| xb | char(2) | YES | | NULL | |
| csny | date | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
7.在表中增加记录
insert into 表名 values(‘字段值’,’字段值’,’字段值’,’字段值’…);
mysql> insert into name values(1,'张三','男','1971-10-01');
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> insert into name values(2,'白云','女','1972-05-20');
Query OK, 1 row affected, 2 warnings (0.00 sec)
可用select命令来验证结果。
mysql> select * from name;
+----+--------+------+------------+
| id | xm | xb | csny |
+----+--------+------+------------+
| 1 | 张三 | 男 | 1971-10-01 |
| 2 | 白云 | 女 | 1972-05-20 |
+----+--------+------+------------+
2 rows in set (0.00 sec)
8.修改记录
update tablename set fieldname =’value’ where fieldname=’value’;
将张三的出生年月改为1971-01-10
mysql> update name set csny='1971-01-10' where xm='张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
9.删除记录
delete from tablename where filedname =’value’;
删除张三的纪录。
mysql> delete from name where xm='张三';
Query OK, 1 row affected (0.00 sec)
10.删数据库和删除表
drop database dbname;
mysql> drop database mytest;
Query OK, 0 rows affected (0.00 sec)
drop table tablename;
mysql> drop table name;
Query OK, 0 rows affected (0.00 sec)
11.数据库备份
mysqldump -u username -p password –opt tablename > filename
将上例创建的 mytest 库备份到文件 back_mytest.sql 中
mysqldump -u root -p –opt mytest > back_mytest.sql
mysqldump不是在msyql内执行,是在linux的控制台执行。
12.数据库恢复
mysql -u username -p password dbname < filename
mysql -uroot -p mytest < bak_mytest.sql
注意:MySQL中每个命令后都要以分号;结尾。
退出 mysql> 命令提示窗口可以使用 exit 命令,如下所示:
mysql> exit
Bye