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

相关文章

来自专栏菩提树下的杨过

Flash/Flex学习笔记(23):运动学原理

先写一个公用的小球类Ball: package{ import flash.display.Sprite; //小球 类 public class B...

25310
来自专栏我和未来有约会

Kit 3D 更新

Kit3D is a 3D graphics engine written for Microsoft Silverlight. Kit3D was inita...

2576
来自专栏张善友的专栏

Silverlight + Model-View-ViewModel (MVVM)

     早在2005年,John Gossman写了一篇关于Model-View-ViewModel模式的博文,这种模式被他所在的微软的项目组用来创建Expr...

2978
来自专栏张善友的专栏

LINQ via C# 系列文章

LINQ via C# Recently I am giving a series of talk on LINQ. the name “LINQ via C...

2645
来自专栏pangguoming

Spring Boot集成JasperReports生成PDF文档

由于工作需要,要实现后端根据模板动态填充数据生成PDF文档,通过技术选型,使用Ireport5.6来设计模板,结合JasperReports5.6工具库来调用渲...

1.2K7
来自专栏张善友的专栏

Mix 10 上的asp.net mvc 2的相关Session

Beyond File | New Company: From Cheesy Sample to Social Platform Scott Hansel...

2587
来自专栏张善友的专栏

Miguel de Icaza 细说 Mix 07大会上的Silverlight和DLR

Mono之父Miguel de Icaza 详细报道微软Mix 07大会上的Silverlight和DLR ,上面还谈到了Mono and Silverligh...

2717
来自专栏我和未来有约会

Silverlight第三方控件专题

这里我收集整理了目前网上silverlight第三方控件的专题,若果有所遗漏请告知我一下。 名称 简介 截图 telerik 商 RadC...

4045
来自专栏魂祭心

原 canvas绘制clock

4144
来自专栏大内老A

The .NET of Tomorrow

Ed Charbeneau(http://developer.telerik.com/featured/the-net-of-tomorrow/) Exciti...

31810

扫码关注云+社区