mysql常用命令

这几天学习了一下mysql,对于mysql的命令总结如下,发现很多方面和oracle还是差别挺大的。 # mysql -uroot -p

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.14-enterprise-commercial-advanced
Copyright (c) 2000, 2013, 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> 

--列出相关的数据库

mysql> show databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

--修改密码

mysql> SET PASSWORD = PASSWORD('mysql');
Query OK, 0 rows affected (0.00 sec)

--创建数据库

mysql> create database test;
ERROR 1007 (HY000): Can't create database 'test'; database exists
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

--切换到所在的库

mysql> use test;
Database changed

--列出相关的tables

mysql> show tables;
Empty set (0.00 sec)

--创建table,原来在mysql中是varchar,不是varchar2

mysql> create table mytable(name varchar2(20),sex char(1));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar2(20),sex char(1))' at line 1
mysql> create table mytable(name varchar(20),sex char(1));
Query OK, 0 rows affected (0.13 sec)

--drop表

mysql> drop table mytable;
Query OK, 0 rows affected (0.05 sec)

--创建表

mysql> create table mytable(id int,name varchar(29));
Query OK, 0 rows affected (0.07 sec)

--列出表的信息,和oracle一样。 desc,describe都可以

mysql> desc mytable
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL   |       |
| name  | varchar(29) | YES  |     | NULL   |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

--插入数据

mysql> insert into mytable values(1,'aaa');
Query OK, 1 row affected (0.03 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
[mysql@oel2 app]$ pwd
/u02/app
[mysql@oel2 app]$ cat a.sql
 insert into mytable values(1,'aaa');
[mysql@oel2 app]$ 

--调用脚本内容,和oracle 中sqlplus 下 @的功能类似

mysql> load data local infile "/u02/app/a.sql" into table mytable;
Query OK, 1 row affected, 2 warnings (0.02 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 2
mysql> 

--查询数据

mysql> select *from mytable;
+------+------+
| id   | name |
+------+------+
|    1 | aaa  |
|    0 | NULL |
+------+------+
2 rows in set (0.00 sec)

--导出数据

导出数据库(库名test)的数据,查看dump内容,直接是sql语句。

[mysql@oel2 app]$ mysqldump -u mysql test>a.data
[mysql@oel2 app]$ ll
total 12
-rw-r--r-- 1 mysql dba 1878 Dec  8 23:38 a.data
-rw-r--r-- 1 mysql dba   38 Dec  8 23:33 a.sql
drwxr-xr-x 2 mysql dba 4096 Dec  8 12:21 db
[mysql@oel2 app]$ less a.data

--重新建一个库,建一张表,来解释和oracle中的不同。

mysql> use test;
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> create database test2;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test2              |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test2;
Database changed

--库2中创建的表test_table2可以直接引用库1的表 mytable.

mysql> create table test_table2 as select *from test.mytable;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| test_table2     |
+-----------------+
1 row in set (0.00 sec)
mysql> desc test_tables;
ERROR 1146 (42S02): Table 'test2.test_tables' doesn't exist
mysql> desc test_table2
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL   |       |
| name  | varchar(29) | YES  |     | NULL   |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> 

--列出enginue的信息,innoDB 还是默认的引擎。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment   | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                       | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                 | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                 | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                   | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                               | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                   | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| test_table2     |
+-----------------+
1 row in set (0.00 sec)

--列出创建表的DDL语句

mysql> show create table test_table2;
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table               |
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
| test_table2 | CREATE TABLE `test_table2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(29) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[mysql@oel2 ~]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.14-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2013, 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> 
->select version(),current_date;
+---------------------------------------+--------------+
| version()                             | current_date |
+---------------------------------------+--------------+
| 5.6.14-enterprise-commercial-advanced | 2013-12-09   |
+---------------------------------------+--------------+
1 row in set (0.04 sec)

--可以并行运行两个sql语句。

->select version();select now();
+---------------------------------------+
| version()                             |
+---------------------------------------+
| 5.6.14-enterprise-commercial-advanced |
+---------------------------------------+
1 row in set (0.01 sec)
+---------------------+
| now()               |
+---------------------+
| 2013-12-09 18:20:01 |
+---------------------+
1 row in set (0.00 sec)

--查出当前时间

->select curdate();
+------------+
| curdate()  |
+------------+
| 2013-12-09 |
+------------+
1 row in set (0.00 sec)

--列出变量参数,如下

->show varialbes;
 
| slave_checkpoint_period                   | 300                                       |
| slave_compressed_protocol                 | OFF                                       |
| slave_exec_mode                           | STRICT                                   |
| slave_load_tmpdir                         | /tmp                                     |
| slave_max_allowed_packet                 | 1073741824                               |
| slave_net_timeout                         | 3600  

--列出服务器运行的状态值

->show global status;
+-----------------------------------------------+-------------+
| Variable_name                             | Value       |
+-----------------------------------------------+-------------+
...
| Handler_read_rnd                         | 2           |
| Handler_read_rnd_next                     | 2689        |
| Handler_rollback                         | 0           |
| Handler_savepoint                         | 0           |
| Handler_savepoint_rollback               | 0           |
| Handler_update                           | 0           |
| Handler_write                             | 2636        |
| Innodb_buffer_pool_dump_status           | not started |
| Innodb_buffer_pool_load_status           | not started |
| Innodb_buffer_pool_pages_data             | 181         |
| Innodb_buffer_pool_bytes_data             | 2965504     |

--显示插件的信息

>SELECT * FROM information_schema.PLUGINS\G
*************************** 2. row ***************************
           PLUGIN_NAME: mysql_native_password
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: AUTHENTICATION
   PLUGIN_TYPE_VERSION: 1.0
        PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
         PLUGIN_AUTHOR: R.J.Silk, Sergei Golubchik
    PLUGIN_DESCRIPTION: Native MySQL authentication
        PLUGIN_LICENSE: PROPRIETARY
           LOAD_OPTION: FORCE
*************************** 3. row ***************************
           PLUGIN_NAME: mysql_old_password
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: AUTHENTICATION
   PLUGIN_TYPE_VERSION: 1.0
        PLUGIN_LIBRARY: NULL
PLUGIN_LIBRARY_VERSION: NULL
         PLUGIN_AUTHOR: R.J.Silk, Sergei Golubchik
    PLUGIN_DESCRIPTION: Old MySQL-4.0 authentication
        PLUGIN_LICENSE: PROPRIETARY
           LOAD_OPTION: FORCE

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-03-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Oracle 11g RAC CRS-4535/ORA-15077

    新安装了Oracle 11g rac之后,不知道是什么原因导致第二个节点上的crsd无法启动?其错误消息是CRS-4535: Cannot commun...

593
来自专栏开发与安全

win 7 + VC++ 2008 express + OpenCv 2.1.0安装

官方文档并注释: These instructions were tested with Visual C++ 9.0 (as part of Visual ...

18110
来自专栏杨建荣的学习笔记

通过shell得到数据库中权限的脚本(r2笔记77天)

有些时候想直接查看某个用户下对应的权限信息。自己每次从数据字典中查找有些太麻烦了。如果涉及的对象类型多一些,很容易遗漏。 一种方式就是通过exp直接导出对象的信...

2614
来自专栏乐沙弥的世界

Linux script 命令记录(数据库)操作步骤

    对DBA而言,经常碰到升级数据库或是apply patch,以及有些时候需要运行大量的脚本。对于这些操作我们希望现在在屏幕同时又输出的文件以备后续查询过...

1024
来自专栏数据和云

追本溯源:Oracle 只读表空间的探索实践

作者简介 ? 胡中豪 云和恩墨西区交付工程师,多年一线 DBA 经验,曾服务于运营商、电网、政府行业、银行等行业客户;擅长数据库故障处理、性能优化、实施升级 本...

2613
来自专栏乐沙弥的世界

Oracle 11G统计信息自动收集及调整

800
来自专栏杨建荣的学习笔记

关于exp/imp的总结学习(r4笔记第26天)

关于exp/imp,是很常用的数据导出导入工具,在10g开始推出的数据泵datapump相当于是exp/imp的补充和升级版本。在后续章节再做一个总结。 exp...

2907
来自专栏数据库新发现

Oracle9i新特性-使用DBMS_METADATA包获得对象DDL语句

从Oracle9i开始Oracle提供了一个新的系统包DBMS_METADATA,可以用于提取对象创建的DDL语句。

862
来自专栏进击的君君的前端之路

在sublime中安装sass编译插件

打开sublime,在preference选项下有没有package control这个选项,如果没有的话,就表示你没有Package Control 插件(一...

431
来自专栏xingoo, 一个梦想做发明家的程序员

windows程序设计-第四章 system1.c

/*---------------------------------------------------- SYSMETS1.C -- System M...

18310

扫码关注云+社区