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 性能相关常用脚本(SQL)

在缺乏的可视化工具来监控数据库性能的情形下,常用的脚本就派上用场了,下面提供几个关于Oracle性能相关的脚本供大家参考。以下脚本均在Oracle 10g测试通...

1252
来自专栏乐沙弥的世界

使用exchange方式切换普通表到分区表

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

731
来自专栏逸鹏说道

sql server之数据库语句优化

一切都是为了性能,一切都是为了业务 一、查询的逻辑执行顺序 (1) FROM left_table (3) join_type JOIN right_table...

3657
来自专栏Java学习123

ORACLE数据库日常维护

5778
来自专栏数据库新发现

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

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

1452
来自专栏乐沙弥的世界

MySQL可更新视图

可更新视图是指通过视图,来更新、插入、删除基本表中的数据。视图是一个虚拟表,即对视图的更新,实质上是更新基表。但是视图的构造很多时候是由多个表连接查询,以及结合...

1034
来自专栏me的随笔

T-SQL基础(五)之增删改

在前面的文章中对T-SQL的查询做了基本总结,接下来我们看下SQL中的另外一个常用操作——数据的修改。

892
来自专栏乐沙弥的世界

避免游标多次遍历

游标在数据库领域被广泛使用,尤其是对于需要将SQL语句返回的数据集进行逐行处理的时候。这为数据处理提供了极大的便利性,然游标的不当 使用从某种程度...

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

MySQL和Oracle对比学习之数据字典元数据(r4笔记第33天)

MySQL和Oracle虽然在架构上有很大的不同,但是如果从某些方面比较起来,它们有些方面也是相通的。 毕竟学习的主线是MySQL,所以会从MySQL的角度来对...

3026
来自专栏程序员宝库

Mysql - JOIN 详解

一个完整的SQL语句中会被拆分成多个子句,子句的执行过程中会产生虚拟表(vt),但是结果只返回最后一张虚拟表。从这个思路出发,我们试着理解一下JOIN查询的执行...

4003

扫码关注云+社区

领取腾讯云代金券