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 条评论
登录 后参与评论

相关文章

来自专栏用户2442861的专栏

基于Spring + Spring MVC + Mybatis 高性能web构建

http://blog.csdn.net/zoutongyuan/article/details/41379851#comments

431
来自专栏数据库新发现

字符集问题的初步探讨(七)----关于字符集更改的内部操作

原文链接: http://www.eygle.com/special/NLS_CHARACTER_SET_07.htm 前面我们提到,通过修改props$...

532
来自专栏数据和云

12c RMAN新特性之Recover Table

周玉其 云和恩墨南区技术顾问,曾在电信、物流等行业从事多年数据库开发、管理工作。

592
来自专栏互联网研发闲思录

mybaties 的一些点

resultMap resutType mybaties缓存 待续 mybaties对应关系是bean和数据库字段的对应。 1、mybaties 的返回值是对象...

1628
来自专栏乐沙弥的世界

Oracle expdp 时遭遇ORA-39125 ORA-04063

    数据库在使用DataPump导出时碰到了ORA-39125与ORA-04063。完整的ORA-39125提示是Worker unexpected fat...

571
来自专栏Aloys的开发之路

DB2常用语句

CREATE TABLE STAFF_BAK LIKE STAFF; INSERT INTO STAFF_BAK SELECT * FROM STAFF; S...

1755
来自专栏乐沙弥的世界

使用导出导入(datapump)方式将普通表切换为分区表

      随着数据库数据量的不断增长,有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作,诸如导出表数据,然后创建分区表再导入数据到分区...

471
来自专栏乐沙弥的世界

ORA-31623: a job is not attached to this session via the specified handle

    在使用Oracel Datapump API时碰到ORA-31623(a job is not attached to this session via...

633
来自专栏乐沙弥的世界

导入导出 Oracle 分区表数据

导入导入Oracle 分区表数据是Oracle DBA 经常完成的任务之一。分区表的导入导出同样普通表的导入导出方式,只不过导入导出需要考

865
来自专栏数据库新发现

如何使用触发器实现数据库级守护,防止DDL操作

--对于重要对象,实施DDL拒绝,防止create,drop,truncate,alter等重要操作

745

扫描关注云+社区